excel中subtotal函數(shù)的使用教程

subtotal函數(shù)e69da5e887aae799bee5baa6e997aee7ad9431333337613163在excel分類匯總后的數(shù)據(jù)處理中起到了不可替代的作用。一、使用背景：使用“數(shù)據(jù)”菜單中的“分類匯總”命令創(chuàng)建帶有分類匯總的列表，一旦創(chuàng)建了分類匯總，篩選出的數(shù)據(jù)用一般的函數(shù)運(yùn)算會(huì)發(fā)生錯(cuò)誤，此時(shí)可以通過(guò)編輯 SUBTOTAL 函數(shù)對(duì)該列表進(jìn)行運(yùn)算。二、使用方法：語(yǔ)法: SUBTOTAL(function_num,ref1,ref2, ...)Function_num 為 1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）之間的數(shù)字，指定使用何種函數(shù)在列表中進(jìn)行分類匯總計(jì)算。Function_num （包含隱藏值）為1到11之間的自然數(shù)，用來(lái)指定分類匯總計(jì)算使用的函數(shù)1 、1 AVERAGE2、 2 COUNT3、 3 COUNTA4 、4 MAX5 、5 MIN6 、6 PRODUCT7 、7 STDEV8 、8 STDEVP9、 9 SUM10 、10 VAR11 、11 VARPFunction_num （忽略隱藏值） 函數(shù)1 、101 AVERAGE2 、102 COUNT3 、103 COUNTA4 、104 MAX5、 105 MIN6 、106 PRODUCT7、 107 STDEV8、 108 STDEVP9 、109 SUM10 、110 VAR11 、111 VARP三、舉例：分類匯總后，需要對(duì)篩選出的數(shù)據(jù)進(jìn)行平均值的計(jì)算，此時(shí)只要輸入公式 =SUBTOTAL(1，數(shù)據(jù)選區(qū))即可，你直接輸入百那些函數(shù)，隱藏的值也就計(jì)算在內(nèi)了，而有的時(shí)候，只需要計(jì)算經(jīng)過(guò)篩選后剩下的值！追問(wèn)：那為什么還有101-111和1-11的區(qū)別度追答：它可以去除隱藏值內(nèi)進(jìn)行分類計(jì)算，也可以包含隱藏值進(jìn)行計(jì)算，兩種方式而已，你沒(méi)必要鉆這個(gè)牛角尖，難道EXCEL里面不能有容2個(gè)函數(shù)可以實(shí)現(xiàn)一樣的功能嗎？，在Excel幫助里是這樣說(shuō)明SUBTOTAL的：返回列表或數(shù)據(jù)庫(kù)中的分類匯總。通常，使用“數(shù)據(jù)”菜單中的“分類匯總”命令可以容易地創(chuàng)建抄帶有分類匯總的列表。一旦創(chuàng)建了分類匯總，就可以通過(guò)編輯 SUBTOTAL 函數(shù)對(duì)該列表進(jìn)行修改。第一個(gè)特點(diǎn)是：如果在 ref1, ref2,… 中有其他的分類匯總（嵌套分類匯總），將忽略這些嵌套分類匯總，以避免知重復(fù)計(jì)算。也就是在數(shù)據(jù)區(qū)域中有SUBTOTAL獲得的結(jié)果將被忽略！第二個(gè)特點(diǎn)是：當(dāng) function_num 為從 1 到 11 的常數(shù)時(shí)，SUBTOTAL 函數(shù)將包括通過(guò)“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當(dāng)您要分類匯總列表中的隱藏和非隱藏值時(shí)，請(qǐng)使用道這些常數(shù)。當(dāng)條件更具b1的變化而變化。此時(shí)，如果一個(gè)一個(gè)函數(shù)輸入的話，會(huì)非常麻煩。但使用subtotal函數(shù)則可以一個(gè)函數(shù)。第三個(gè)特點(diǎn)是：可以代替上面說(shuō)的11種函數(shù)，當(dāng)有上面說(shuō)的兩種特點(diǎn)情況時(shí)，就可以使用SUBTOTAL來(lái)完成。所以在需要處理隱藏?cái)?shù)據(jù)相關(guān)的應(yīng)用時(shí)，SUBTOTAL是其它函數(shù)無(wú)法代替的，也是SUBTOTAL最大最重要的特點(diǎn)，設(shè)你需要對(duì)A1:A10這個(gè)區(qū)域來(lái)進(jìn)行處理，當(dāng)條件更具b1的變化而變自化。此百時(shí)，如果一個(gè)一個(gè)函數(shù)輸入的話，會(huì)非常麻煩。但度使用subtotal函數(shù)則可知以一個(gè)函數(shù)就搞定了道。如：=SUBTOTAL(LOOKUP(B1,{"最大值","最小值"},{4,5}),A1:A10)，你直接輸入那些函數(shù)，隱藏的值也就計(jì)算在內(nèi)了，而有的時(shí)候，只需要計(jì)算經(jīng)過(guò)篩選后剩下的值！追問(wèn)那為什么還有101-111和1-11的區(qū)別追答它可以去除隱藏值進(jìn)行分類計(jì)算，也可以包含隱藏值進(jìn)行計(jì)算，兩種方式而已，你沒(méi)必要鉆這個(gè)牛角尖，難道EXCEL里面不能有2個(gè)函數(shù)可以實(shí)現(xiàn)一樣的功能嗎？本回答被提問(wèn)者采納m.gy2thfx.cn防采集。

下面分享一下excel中subtotal函數(shù)的使用方法

材料/工具

Excel

方法

打開(kāi)Excel

subtotal　　SUBTOTAL，日常中最常見(jiàn)到的是在分類匯總中，不過(guò)SUBTOTAL還有三個(gè)

新建一個(gè)工作簿。

數(shù)字 1-11 或 101-111，用于指定要為分類匯總使用的函數(shù)。如果使用 1-11，將包括手

為了方便演示，輸入以下數(shù)據(jù)并求和。

一個(gè)求和，和SUM基本一樣，另一個(gè)是求隱藏的求和。對(duì)于手動(dòng)隱藏的（主菜單，右鍵菜單及鼠標(biāo)直接拖），1

現(xiàn)在需要求出建工系的總分，發(fā)現(xiàn)求和公式失效。

subtotal函數(shù)在excel分類匯總后的數(shù)據(jù)處理中起到了不可替代的作用。一、使用背景：使

利用subtotal函數(shù)，輸入函數(shù)=subtatol（9，C2：C5),9表示求和，c2：c5表示數(shù)據(jù)區(qū)域。）系統(tǒng)自動(dòng)求出建工系的成績(jī)。

SUBTOTAL返回列表或數(shù)據(jù)庫(kù)中的分類匯總。通常，使用“數(shù)據(jù)”菜單中的“分類匯總”命令可以

勾選信工系。

方法/步驟使用“數(shù)據(jù)”菜單中的“分類匯總”命令可以做有分類匯總的列表，這個(gè)時(shí)候我們可以使

系統(tǒng)自動(dòng)求出信工系的成績(jī)，如圖所示

sum，一旦公式輸入完成，就只是求和subtotal，分類匯總，公式完成后，可以根據(jù)參數(shù)的不同進(jìn)

SUBTOTAL返回列表或數(shù)據(jù)庫(kù)中的分類匯總。通常，使用“數(shù)據(jù)”菜單中的“分類匯總”命令可以容易地創(chuàng)建帶有分類匯總的列表。一旦創(chuàng)建了分類匯總，就可以通過(guò)編輯 SUBTOTAL 函數(shù)對(duì)該列表進(jìn)行修改。語(yǔ)法SUBTOTAL(function_num, ref1, ref2, ...)Function_num 為 1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）之間的數(shù)字，指定使用何種函數(shù)在列表中進(jìn)行分類匯總計(jì)算。Function_num（包含隱藏值） Function_num（忽略隱藏值） 函數(shù) 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP Ref1, ref2, 為要進(jìn)行分類匯總計(jì)算的 1 到 29 個(gè)區(qū)域或引用。說(shuō)明如果在 ref1, ref2,… 中有其他的分類匯總（嵌套分類匯總），將忽略這些嵌套分類匯總，以避免重復(fù)計(jì)算。 當(dāng) function_num 為從 1 到 11 的常數(shù)時(shí)，SUBTOTAL 函數(shù)將包括通過(guò)“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當(dāng)您要分類匯總列表中的隱藏和非隱藏值時(shí)，請(qǐng)使用這些常數(shù)。當(dāng) function_num 為從 101 到 111 的常數(shù)時(shí)，SUBTOTAL 函數(shù)將忽略通過(guò)“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當(dāng)您只分類匯總列表中的非隱藏?cái)?shù)字時(shí)，使用這些常數(shù)。 SUBTOTAL 函數(shù)忽略任何不包括在篩選結(jié)果中的行，不論使用什么 function_num 值。SUBTOTAL 函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域。不e68a84e799bee5baa631333330323866適用于數(shù)據(jù)行或水平區(qū)域。例如，當(dāng) function_num 大于或等于 101 時(shí)需要分類匯總某個(gè)水平區(qū)域時(shí)，例如 SUBTOTAL(109,B2:G2)，則隱藏某一列不影響分類匯總。但是隱藏分類匯總的垂直區(qū)域中的某一行就會(huì)對(duì)其產(chǎn)生影響。如果所指定的某一引用為三維引用，函數(shù) SUBTOTAL 將返回錯(cuò)誤值 #VALUE!。 示例如果您將示例復(fù)制到空白工作表中，可能會(huì)更易于理解該示例。 操作方法創(chuàng)建空白工作簿或工作表。 請(qǐng)?jiān)凇皫椭敝黝}中選取示例。不要選取行或列標(biāo)題。 從幫助中選取示例。按 Ctrl+C。 在工作表中，選中單元格 A1，再按 Ctrl+V。 若要在查看結(jié)果和查看返回結(jié)果的公式之間切換，請(qǐng)按 Ctrl+`（重音符），或在“工具”菜單上，指向“公式審核”，再單擊“公式審核模式”。 數(shù)據(jù) 120 10 150 23 公式 說(shuō)明（結(jié)果） =SUBTOTAL(9,A2:A5) 對(duì)上面列使用 SUM 函數(shù)計(jì)算出的分類匯總 (303) =SUBTOTAL(1,A2:A5) 對(duì)上面列使用 AVERAGE 函數(shù)計(jì)算出的分類匯總 (75.75)，SUBTOTAL 全部顯示全部隱藏返回列表或數(shù)據(jù)庫(kù)中的分類匯總。通常，使用“數(shù)據(jù)”菜單中的“分類匯總”命令可以容易地創(chuàng)建帶有分類匯總的列表。一旦創(chuàng)建了分類匯總，就可以通e79fa5e98193e78988e69d8331333330323866過(guò)編輯 SUBTOTAL 函數(shù)對(duì)該列表進(jìn)行修改。語(yǔ)法SUBTOTAL(function_num, ref1, ref2, ...)Function_num 為 1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）之間的數(shù)字，指定使用何種函數(shù)在列表中進(jìn)行分類匯總計(jì)算。Function_num(包含隱藏值) Function_num（忽略隱藏值） 函數(shù) 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP Ref1, ref2, 為要進(jìn)行分類匯總計(jì)算的 1 到 29 個(gè)區(qū)域或引用。說(shuō)明如果在 ref1, ref2,… 中有其他的分類匯總（嵌套分類匯總），將忽略這些嵌套分類匯總，以避免重復(fù)計(jì)算。 當(dāng) function_num 為從 1 到 11 的常數(shù)時(shí)，SUBTOTAL 函數(shù)將包括通過(guò)“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當(dāng)您要分類匯總列表中的隱藏和非隱藏值時(shí)，請(qǐng)使用這些常數(shù)。當(dāng) function_num 為從 101 到 111 的常數(shù)時(shí)，SUBTOTAL 函數(shù)將忽略通過(guò)“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當(dāng)您只分類匯總列表中的非隱藏?cái)?shù)字時(shí)，使用這些常數(shù)。 SUBTOTAL 函數(shù)忽略任何不包括在篩選結(jié)果中的行，不論使用什么 function_num 值。 SUBTOTAL 函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域。不適用于數(shù)據(jù)行或水平區(qū)域。例如，當(dāng) function_num 大于或等于 101 時(shí)需要分類匯總某個(gè)水平區(qū)域時(shí)，例如 SUBTOTAL(109,B2:G2)，則隱藏某一列不影響分類匯總。但是隱藏分類匯總的垂直區(qū)域中的某一行就會(huì)對(duì)其產(chǎn)生影響。 如果所指定的某一引用為三維引用，函數(shù) SUBTOTAL 將返回錯(cuò)誤值 #VALUE!，SUBTOTAL(XX,A1:A5) 表示分類匯總百A1：A5，如何匯總關(guān)鍵在XX。說(shuō)明：XX為1 到 11（包度含隱藏值）或 101 到 111（忽略隱內(nèi)藏值）之間容的數(shù)字1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP 可以查Excel的幫助，這位老大說(shuō)的太全了，我只是粗淺的用過(guò)，但很實(shí)用，比如SUBTOTAL(9,a1:a12)會(huì)合計(jì)A1到A12中的數(shù)字，但如中間有SUM公式，不會(huì)被合計(jì)上追問(wèn)SUBTOTAL(9,a1:a12)這個(gè)公式中的9代表什么呢，是求總和嗎？就這個(gè)函數(shù)是求什么的呀？？？？追答看樓上老大說(shuō)的，9是個(gè)參數(shù)，在這里代表忽略SUM求和后的值，如A13=SUBTOTAL(9,a1:a12)求和，假如A5=SUM（A1：A4），那么A13中不包括A5的值，你可以自己試試就明白了，方法/步驟使用“數(shù)據(jù)”菜單中的“分類匯總”命令可以做百有分類匯總的列表，這個(gè)時(shí)候我們可以使用subtotal這個(gè)函數(shù)這個(gè)函數(shù)SUBTOTAL(function_num, ref1, ref2, ...)，其實(shí)function_num對(duì)應(yīng)的有一些數(shù)字，其實(shí)數(shù)字代表的數(shù)字的含義如圖所示。這里需要注意的是：1 到 11（包含隱度藏值）或 101 到 111（忽略隱藏值）之間的數(shù)字我舉例說(shuō)明一下我們要對(duì)一部分，數(shù)據(jù)進(jìn)行求和，大家平時(shí)用得最多是SUM函數(shù)。但我們同樣可以使用=SUBTOTAL(9,B3:D3)這里我們使用的是9，其實(shí)就相當(dāng)于使用SUM函數(shù)，但那這個(gè)函數(shù)有什么特殊的用途呢，我們今天舉一個(gè)分類匯總的例子。如圖我們對(duì)下列數(shù)據(jù)進(jìn)行篩回選。我們看一下，如果使用SUM求的話，不因?yàn)楹Y選結(jié)果，都會(huì)對(duì)數(shù)據(jù)區(qū)域進(jìn)行求和。我們?cè)倏创鹨幌耂UBTOTAL，我們可以發(fā)現(xiàn)他只對(duì)篩選數(shù)據(jù)結(jié)果數(shù)據(jù)進(jìn)行求和。這個(gè)功能對(duì)我們的實(shí)際工作的作用是很大的內(nèi)容來(lái)自m.gy2thfx.cn請(qǐng)勿采集。

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com