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請勿採集。