我們知道，Excel函數(shù)的功能總是非常單一的。例如：sum函數(shù)用于求和；average函數(shù)用于求平均值；count函數(shù)用于數(shù)字計(jì)數(shù)；max函數(shù)、min函數(shù)也只用于求出最大值最小值……如果使用這些函數(shù)，想一條公式完成多種統(tǒng)計(jì)，往往會(huì)使用函數(shù)嵌套。然而，因?yàn)槎嗪瘮?shù)嵌套對(duì)于Excel小白非常的難，所以Excel為我們提供了一個(gè)簡單的“萬能統(tǒng)計(jì)”函數(shù)：Subtotal函數(shù)。這個(gè)函數(shù)使用非常簡單，但卻身兼數(shù)職：求和、平均值、數(shù)字計(jì)數(shù)、非空單元格計(jì)數(shù)、最大值、最小值等等足有11個(gè)功能之多。學(xué)好這個(gè)函數(shù)，你將輕松擁有完成11種簡單統(tǒng)計(jì)的技巧。本文將分為三個(gè)部分對(duì)此函數(shù)進(jìn)行介紹：

文：傲看今朝 圖片來自網(wǎng)絡(luò)

1、簡單實(shí)用的subtotal函數(shù)基本功能；

2、利用subtotal函數(shù)給表格自動(dòng)編號(hào)；

3、利用subtotal函數(shù)計(jì)算選手最后得分

一、簡單實(shí)用的subtotal函數(shù)基本功能

subtotal函數(shù)，顧名思義，sub表示替代，total表示總計(jì)總額，字面意思就是替代求和（sum）的函數(shù)。然而，subtotal絕遠(yuǎn)不止于此，實(shí)際上前面我們已經(jīng)提到，subtotal函數(shù)可以完成11種簡單的統(tǒng)計(jì)。因此subtotal函數(shù)又叫分類匯總函數(shù)。

1：函數(shù)名，subtotal，意即替代求和，是一個(gè)分類匯總函數(shù)；

2：功能代碼，為1到11或者101到111之間的數(shù)字；想使用什么功能，就用對(duì)應(yīng)的代碼即可。見后面的對(duì)照表；

3：引用區(qū)域，這個(gè)地方只能是引用而不能是其他。

4：主要功能是對(duì)數(shù)據(jù)表進(jìn)行求平均、求和、求最大最小、求方差等分類匯總；

1

下面是subtotal函數(shù)第一個(gè)參數(shù)的具體說明：

第一參數(shù)說明

大家注意到了這一點(diǎn)：第一列和第二列的數(shù)值對(duì)應(yīng)同一個(gè)函數(shù)功能。不同的只是，代碼1到11不忽略隱藏值，而101到111忽略了隱藏值。這之間到底有什么分別呢？ 下圖以最大值為例來說明這個(gè)區(qū)別。

第一參數(shù)說明

N8單元格為最高值，但被隱藏起來，使用不同的代碼卻得到了不同的結(jié)果，這就是區(qū)別。特別注意的一點(diǎn)就是，如果第二參數(shù)選擇的區(qū)域是橫向的，那么不管使用什么樣的代碼都將不會(huì)忽略隱藏值。

二、利用subtotal函數(shù)給表格自動(dòng)編號(hào)

請(qǐng)看下面的動(dòng)畫，我們?nèi)绾巫霾拍艿玫竭@樣自動(dòng)編號(hào)效果（當(dāng)單行或者多行被隱藏或者取消隱藏時(shí)，A列序號(hào)會(huì)自動(dòng)調(diào)整）呢？

當(dāng)單行或者多行被隱藏或者取消隱藏時(shí)，A列序號(hào)會(huì)自動(dòng)調(diào)整

我們仔細(xì)觀察，序號(hào)是從小排到大的，且A列每一個(gè)序號(hào)等于其對(duì)于B列單元格向上數(shù)非空單元格的個(gè)數(shù)，例如，A3單元格值為2，即是b2:B3單元格區(qū)域中非空單元格的個(gè)數(shù)；A7單元格的值為6，即是B2：B7單元格區(qū)域中非空單元格的個(gè)數(shù)。因此我們直接在A2單元格中輸入公式：=counta(B$2:B2),然后復(fù)制到整列即可。

counta函數(shù)

然而，當(dāng)我們隱藏某些行列時(shí)，我們發(fā)現(xiàn)行列時(shí)，A列的序號(hào)卻無法自動(dòng)更新調(diào)整。如下圖：

序號(hào)無法自動(dòng)更新

我們?nèi)绾谓鉀Q這個(gè)問題呢？很顯然，使用counta函數(shù)已經(jīng)無法得到我們想要的效果了，因此我們請(qǐng)出subtotal函數(shù)來幫忙，前面我們提到，subtotal函數(shù)在第一參數(shù)使用101到111代碼時(shí)，會(huì)自動(dòng)忽略隱藏值，subtotal中計(jì)算非空單元格數(shù)的代碼為103，因此我們可以寫出下面的公式：A2單元格中輸入公式：=subtotal(103,B$2:B2)，然后將此公式復(fù)制到全列即可。

無論怎么隱藏，A列的序號(hào)都將自動(dòng)調(diào)整

三、利用subtotal函數(shù)計(jì)算選手最后得分

看下圖，如何快速計(jì)算出選手的最后得分呢？

如何計(jì)算選手的最后得分呢

一般比賽的要求都是去掉一個(gè)最高分，再去掉一個(gè)最低分，然后再取剩余值的平均值。因此我們解題步驟通常如下：

1.求總分：=SUM(B2:J2)

2.減去最低分和最高分：=SUM(B2:J2)-max(B2:J2)-min(B2:J2)

3.剩余的值求平均值，本例子中共有9位評(píng)委，去掉最低和最高兩個(gè)評(píng)委，將剩下的7位評(píng)委的分?jǐn)?shù)求平均分，因此公式為：=(SUM(B2:J2)-max(B2:J2)-min(B2:J2))/7

在普通解法中，我們用到了三個(gè)函數(shù)，思路很簡單，但是還是比較麻煩的，如果我們使用subtotal函數(shù)，2個(gè)函數(shù)就可以搞定選手的最后得分了。公式如下：{=SUM(SUBTOTAL({9,4,5},B2:J2)*{1,-1,-1})/7}

這個(gè)公式相較于前面的公式理解稍微難一點(diǎn)，我解釋一下：

subtotal函數(shù)共2個(gè)參數(shù)，第一個(gè)參數(shù)為{9,4,5},9為求和，4為最大值，5為最小值，第二個(gè)參數(shù)為B2:J2；

在subtotal函數(shù)乘一個(gè)數(shù)組{1，-1，-1}，即得到sum(B2:J2)，-max(B2:J2),-min(B2:J2)，接下來利用sum函數(shù)將這幾個(gè)值加起來就相當(dāng)于公式：=SUM(B2:J2)-max(B2:J2)-min(B2:J2)；

將得到的結(jié)果除以7即得到了=SUM(SUBTOTAL({9,4,5},B2:J2)*{1,-1,-1})/7的公式，最后按下Ctrl+Shift+Enter完成數(shù)組公式的輸入。

最終結(jié)果

關(guān)于subtotal函數(shù)，今天的分享就到這里，感興趣的同學(xué)可以與我聯(lián)系，可以評(píng)論留言并寫下自己的練習(xí)方式，我會(huì)將練習(xí)材料發(fā)給大家學(xué)習(xí)。

你們給我點(diǎn)贊、關(guān)注我的賬號(hào)就是對(duì)我最大支持。