excel中subtotal函數的使用教程

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

下面分享一下excel中subtotal函數的使用方法

材料/工具

Excel

方法

打開Excel

subtotal　　SUBTOTAL，日常中最常見到的是在分類彙總中，不過SUBTOTAL還有三個

新建一個工作簿。

數字 1-11 或 101-111，用於指定要為分類彙總使用的函數。如果使用 1-11，將包括手

為了方便演示，輸入以下數據並求和。

一個求和，和SUM基本一樣，另一個是求隱藏的求和。對於手動隱藏的（主菜單，右鍵菜單及鼠標直接拖），1

現在需要求出建工系的總分，發現求和公式失效。

subtotal函數在excel分類彙總後的數據處理中起到了不可替代的作用。一、使用背景：使

利用subtotal函數，輸入函數=subtatol（9，C2：C5),9表示求和，c2：c5表示數據區域。）系統自動求出建工系的成績。

SUBTOTAL返回列表或數據庫中的分類彙總。通常，使用“數據”菜單中的“分類彙總”命令可以

勾選信工系。

方法/步驟使用“數據”菜單中的“分類彙總”命令可以做有分類彙總的列表，這個時候我們可以使

系統自動求出信工系的成績，如圖所示

sum，一旦公式輸入完成，就只是求和subtotal，分類彙總，公式完成後，可以根據參數的不同進

SUBTOTAL返回列表或數據庫中的分類彙總。通常，使用“數據”菜單中的“分類彙總”命令可以容易地創建帶有分類彙總的列表。一旦創建了分類彙總，就可以通過編輯 SUBTOTAL 函數對該列表進行修改。語法SUBTOTAL(function_num, ref1, ref2, ...)Function_num 為 1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）之間的數字，指定使用何種函數在列表中進行分類彙總計算。Function_num（包含隱藏值） Function_num（忽略隱藏值） 函數 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, 為要進行分類彙總計算的 1 到 29 個區域或引用。説明如果在 ref1, ref2,… 中有其他的分類彙總（嵌套分類彙總），將忽略這些嵌套分類彙總，以避免重複計算。 當 function_num 為從 1 到 11 的常數時，SUBTOTAL 函數將包括通過“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當您要分類彙總列表中的隱藏和非隱藏值時，請使用這些常數。當 function_num 為從 101 到 111 的常數時，SUBTOTAL 函數將忽略通過“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當您只分類彙總列表中的非隱藏數字時，使用這些常數。 SUBTOTAL 函數忽略任何不包括在篩選結果中的行，不論使用什麼 function_num 值。SUBTOTAL 函數適用於數據列或垂直區域。不e68a84e799bee5baa631333330323866適用於數據行或水平區域。例如，當 function_num 大於或等於 101 時需要分類彙總某個水平區域時，例如 SUBTOTAL(109,B2:G2)，則隱藏某一列不影響分類彙總。但是隱藏分類彙總的垂直區域中的某一行就會對其產生影響。如果所指定的某一引用為三維引用，函數 SUBTOTAL 將返回錯誤值 #VALUE!。 示例如果您將示例複製到空白工作表中，可能會更易於理解該示例。 操作方法創建空白工作簿或工作表。 請在“幫助”主題中選取示例。不要選取行或列標題。 從幫助中選取示例。按 Ctrl+C。 在工作表中，選中單元格 A1，再按 Ctrl+V。 若要在查看結果和查看返回結果的公式之間切換，請按 Ctrl+`（重音符），或在“工具”菜單上，指向“公式審核”，再單擊“公式審核模式”。 數據 120 10 150 23 公式 説明（結果） =SUBTOTAL(9,A2:A5) 對上面列使用 SUM 函數計算出的分類彙總 (303) =SUBTOTAL(1,A2:A5) 對上面列使用 AVERAGE 函數計算出的分類彙總 (75.75)，SUBTOTAL 全部顯示全部隱藏返回列表或數據庫中的分類彙總。通常，使用“數據”菜單中的“分類彙總”命令可以容易地創建帶有分類彙總的列表。一旦創建了分類彙總，就可以通e79fa5e98193e78988e69d8331333330323866過編輯 SUBTOTAL 函數對該列表進行修改。語法SUBTOTAL(function_num, ref1, ref2, ...)Function_num 為 1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）之間的數字，指定使用何種函數在列表中進行分類彙總計算。Function_num(包含隱藏值) Function_num（忽略隱藏值） 函數 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, 為要進行分類彙總計算的 1 到 29 個區域或引用。説明如果在 ref1, ref2,… 中有其他的分類彙總（嵌套分類彙總），將忽略這些嵌套分類彙總，以避免重複計算。 當 function_num 為從 1 到 11 的常數時，SUBTOTAL 函數將包括通過“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當您要分類彙總列表中的隱藏和非隱藏值時，請使用這些常數。當 function_num 為從 101 到 111 的常數時，SUBTOTAL 函數將忽略通過“格式”菜單的“行”子菜單下面的“隱藏”命令所隱藏的行中的值。當您只分類彙總列表中的非隱藏數字時，使用這些常數。 SUBTOTAL 函數忽略任何不包括在篩選結果中的行，不論使用什麼 function_num 值。 SUBTOTAL 函數適用於數據列或垂直區域。不適用於數據行或水平區域。例如，當 function_num 大於或等於 101 時需要分類彙總某個水平區域時，例如 SUBTOTAL(109,B2:G2)，則隱藏某一列不影響分類彙總。但是隱藏分類彙總的垂直區域中的某一行就會對其產生影響。 如果所指定的某一引用為三維引用，函數 SUBTOTAL 將返回錯誤值 #VALUE!，SUBTOTAL(XX,A1:A5) 表示分類彙總百A1：A5，如何彙總關鍵在XX。説明：XX為1 到 11（包度含隱藏值）或 101 到 111（忽略隱內藏值）之間容的數字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的幫助，這位老大説的太全了，我只是粗淺的用過，但很實用，比如SUBTOTAL(9,a1:a12)會合計A1到A12中的數字，但如中間有SUM公式，不會被合計上追問SUBTOTAL(9,a1:a12)這個公式中的9代表什麼呢，是求總和嗎？就這個函數是求什麼的呀？？？？追答看樓上老大説的，9是個參數，在這裏代表忽略SUM求和後的值，如A13=SUBTOTAL(9,a1:a12)求和，假如A5=SUM（A1：A4），那麼A13中不包括A5的值，你可以自己試試就明白了，方法/步驟使用“數據”菜單中的“分類彙總”命令可以做百有分類彙總的列表，這個時候我們可以使用subtotal這個函數這個函數SUBTOTAL(function_num, ref1, ref2, ...)，其實function_num對應的有一些數字，其實數字代表的數字的含義如圖所示。這裏需要注意的是：1 到 11（包含隱度藏值）或 101 到 111（忽略隱藏值）之間的數字我舉例説明一下我們要對一部分，數據進行求和，大家平時用得最多是SUM函數。但我們同樣可以使用=SUBTOTAL(9,B3:D3)這裏我們使用的是9，其實就相當於使用SUM函數，但那這個函數有什麼特殊的用途呢，我們今天舉一個分類彙總的例子。如圖我們對下列數據進行篩回選。我們看一下，如果使用SUM求的話，不因為篩選結果，都會對數據區域進行求和。我們再看答一下SUBTOTAL，我們可以發現他只對篩選數據結果數據進行求和。這個功能對我們的實際工作的作用是很大的內容來自www.51dongshi.com請勿採集。