在Excel中，常用到的統(tǒng)計(jì)函數(shù)有Sum系列、Count系列、Average系列、Max系列、Min系列等，其功能非常的強(qiáng)大，使用起來(lái)也非常的簡(jiǎn)單，但函數(shù)較多，語(yǔ)法結(jié)構(gòu)也不完全相同，如果不經(jīng)常使用，對(duì)于部分函數(shù)的使用可能存在一定的困難，造成工作效率低下等……其實(shí)在Excel中，有這么一個(gè)函數(shù)，集成了了求和、計(jì)數(shù)、平均值、最值、成績(jī)等多種功能，它既是Subtotal函數(shù)。

一、Excel萬(wàn)能函數(shù)Subtotal：功能及語(yǔ)法結(jié)構(gòu)。

功能：根據(jù)指定代碼返回一個(gè)數(shù)據(jù)列表或數(shù)據(jù)的分類統(tǒng)計(jì)。

語(yǔ)法結(jié)構(gòu)：=Subtotal(功能代碼,數(shù)值區(qū)域)。

功能代碼有：

解讀：

從對(duì)應(yīng)函數(shù)中可以看出，代碼“1”和“101”的作用是相同的，“2”和“102”的作用是相同的……那有沒(méi)有區(qū)別呢？

二、Excel萬(wàn)能函數(shù)Subtotal：求和。

1、隱藏?cái)?shù)據(jù)求和。

目的：隱藏指定的數(shù)據(jù)后，求和。

方法：

在目標(biāo)單元格中輸入公式：=SUBTOTAL(9,C3:C9)、=SUBTOTAL(109,C3:C9)。

解讀：

1、未對(duì)數(shù)據(jù)進(jìn)行隱藏前，代碼“9”和“109”的計(jì)算結(jié)果相同，隱藏?cái)?shù)據(jù)后，代碼“9”的計(jì)算結(jié)果沒(méi)有變化，而代碼“109”的計(jì)算結(jié)果為當(dāng)前可見(jiàn)單元格的和。

2、如果從你自己的角度出發(fā)，你認(rèn)為代碼“9”和“109”的計(jì)算結(jié)果那個(gè)正確？

2、篩選數(shù)據(jù)求和。

目的：篩選數(shù)據(jù)后求和。

方法：

在目標(biāo)單元格中輸入公式：=SUBTOTAL(9,C3:C9)、=SUBTOTAL(109,C3:C9)。

解讀：

篩選數(shù)據(jù)后，代碼“9”和“109”的計(jì)算結(jié)果相同，但和Sum函數(shù)的計(jì)算結(jié)果不同，Sum函數(shù)仍然統(tǒng)計(jì)的為C3:C9區(qū)域的和，而“9”或“109”統(tǒng)計(jì)的為“可見(jiàn)”單元格的和，對(duì)于篩選后隱藏起來(lái)的值不在統(tǒng)計(jì)的范圍之內(nèi)。

三、Excel萬(wàn)能函數(shù)Subtotal：求平均值。

1、隱藏?cái)?shù)據(jù)求平均值。

目的：隱藏?cái)?shù)據(jù)后求平均值。

方法：

在目標(biāo)單元格中輸入公式：=SUBTOTAL(1,C3:C9)、=SUBTOTAL(101,C3:C9)。

解讀：

1、未對(duì)數(shù)據(jù)進(jìn)行隱藏前，代碼“1”和“101”的計(jì)算結(jié)果相同，隱藏?cái)?shù)據(jù)后，代碼“1”的計(jì)算結(jié)果沒(méi)有變化，而代碼“101”的計(jì)算結(jié)果為當(dāng)前可見(jiàn)單元格的和。

2、如果從你自己的角度出發(fā)，你認(rèn)為代碼“1”和“101”的計(jì)算結(jié)果那個(gè)正確？

2、篩選數(shù)據(jù)求平均值。

目的：篩選數(shù)據(jù)后計(jì)算平均值。

方法：

在目標(biāo)單元格中輸入公式：=SUBTOTAL(1,C3:C9)、=SUBTOTAL(101,C3:C9)。

解讀：

篩選數(shù)據(jù)后，代碼“1”和“101”的計(jì)算結(jié)果相同，但和Average函數(shù)的計(jì)算結(jié)果不同，Average函數(shù)仍然統(tǒng)計(jì)的為C3:C9區(qū)域的和，而“1”或“101”統(tǒng)計(jì)的為“可見(jiàn)”單元格的平均值，對(duì)于篩選后隱藏起來(lái)的值不在統(tǒng)計(jì)的范圍之內(nèi)。

四、Excel萬(wàn)能函數(shù)Subtotal：保持序號(hào)的連續(xù)性。

目的：隱藏或篩選數(shù)據(jù)后保持序號(hào)（NO）的連續(xù)性。

方法：

在目標(biāo)單元格中輸入公式：=SUBTOTAL(103,B$2:B2)。

解讀：

代碼“103”對(duì)應(yīng)的函數(shù)為Counta，暨統(tǒng)計(jì)非空單元格的個(gè)數(shù)。所在第二個(gè)參數(shù)的引用范圍為當(dāng)前單元格的上一行開(kāi)始，而且要絕對(duì)引用，

五、Excel萬(wàn)能函數(shù)Subtotal：統(tǒng)計(jì)“可見(jiàn)”單元格的最大值。

目的：統(tǒng)計(jì)隱藏或篩選后的最大值。

方法：

在目標(biāo)單元格中輸入公式：=SUBTOTAL(4,C3:C9)、=SUBTOTAL(104,C3:C9)。

解讀：

1、在沒(méi)有隱藏?cái)?shù)據(jù)的情況下，代碼“4”和“104”的統(tǒng)計(jì)結(jié)果相同，當(dāng)隱藏?cái)?shù)據(jù)后，代碼“104”統(tǒng)計(jì)的為當(dāng)前“可見(jiàn)”區(qū)域中的最大值。

2、篩選數(shù)據(jù)后，代碼“4”和“104”統(tǒng)計(jì)的都為“可見(jiàn)”區(qū)域中的最大值。

結(jié)束語(yǔ)：

從Subtotal函數(shù)的功能代碼中就可以看出其功能非常的強(qiáng)大，代碼“1-10”對(duì)隱藏?cái)?shù)據(jù)無(wú)效，代碼“101-111”對(duì)“可見(jiàn)”數(shù)據(jù)有效，無(wú)論是隱藏還是篩選，其數(shù)據(jù)都是對(duì)“可見(jiàn)”數(shù)據(jù)的統(tǒng)計(jì)。對(duì)于使用技巧，你Get到了嗎？

【精彩推薦