今天要來分享的函數,與前面分享的11篇Excel函數013~023有關,那就是「subtotal」函數。
Subtotal厲害的地方是它會跟著表單的顯示範圍去計算,簡言之,就是只計算篩選出來的值,不計算隱藏的值。
函數類別:數學與三角函數
[函數] =SUBTOTAL(101,A5:A13)
△ 題目:試算個位數的平均值。
步驟一
● 直接輸入函數:才能確認自己選的號碼是哪個函數
(1) 在A3儲存格輸入「=subtotal(」
(2) 選擇「101-AVERAGE」
(3) 輸入「,」
(4) 選取範圍「A5:A13」
(5) 輸入「)」
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiSj7vmeHvH7oxe9q-fJpxT877AxAUBPWaI_3vdmVLox0QqHlVFNwtDy4VpCKZ5jVpmJkBzyA_xSUg3z1BNZwSKD3ql5-SO8Oeb0z943hjnSI8L5e3IAoOhaKHcgzCRyOuOqSzUTV6dmPI/s1600/1.jpg)
● 確認函數是否正確,選取A5:A13儲存格,可以看到下排的「平均值: 5」,答案與A3儲存格相符。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjH0P16ps8gWqnymYWdZQsc74_zgwXcVqN2xLsOPOTBbrQC8TigLvdTc2qZ0vF70dmhiV3b3bhgtcXQciqLKOwYZ1QWRilrxVSgEK9AnfsW9z_YGFLSvBjnKUjnWhQHVl3yTNu0RYuDhEE0/s1600/2.jpg)
完成
KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG
補充說明:
一、subtotal裡共有22個函數可選擇
這個函數雖然方便,但還是有需要注意的地方。仔細看會發現1~11和101~111的函數是相同的(如下圖),那使用上有甚麼區別呢?
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYojZ6k94xnFtWKa1Ouc_38g-7HSvCB0uGQF6R69b0wF8ccvNY1GDcGNRv3m01xA9FOj9Q2BJM0hylAdUmMF8HKBPt8yhQFNlHHHQgnbN77tN88nAFltPL5xs5jtobbhSU6es8ZO53X2rB/s1600/8.jpg)
二、適用於哪些情況?
小本整理了表格給本友們參考:以下這三個都是average計算平均值的函數
(1) 「subtotal+101」只計算顯示在表單中的數值;能忽略隱藏的值(手動隱藏&篩選&手動隱藏+篩選)。
(2) 「subtotal+1」手動隱藏、篩選、手動隱藏+篩選後,可以計算顯示在表單的數值;(手動隱藏+篩選)。
單獨手動隱藏會無法計算。
(3) 「average」只適用於一般不須篩選和隱藏的情況使用。完全不能在這三種情況下計算(手動隱藏&篩選&手動隱藏+篩選)。
三、範例
● 小本用「subtotal+101」的表格試做一份範例,如下:
[圖1] 手動隱藏 >>不影響結果
[圖2] 篩選 >>不影響結果
[圖3] 手動隱藏+篩選 >>不影響結果
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTZ0RQm47RDTQWENG85O1VwfbBRlsmjRtwp5aPV_MvSQhNUggaFKvLsEG5kRkYJ-AHgHUuGtdUlSDSaDAygmz5sF50VEsKBjDzr6iRIDNiM2KD7g92khgKCHJci0J34Z1vUqjUDIf1vu0d/s1600/3.jpg)
●「subtotal+101」「subtotal+1」「average」分別做以下對照,可配合「二、適用於哪些情況裡的一覽表」確認。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqcalJUET_60Q7zF-9YLqV7RWiy-g7_52jICfPUUTyW9N7yt4E9ZXrR2qZRit636Qsyd8g-r9H7CnOY5qW4djEbd4zIOs2lBOoItg5hfsoS78T5CcevwEE1ZrEJ5WZIvQXXdnXqB1E6v4T/s1600/4.jpg)
●手動隱藏:正確的有「subtotal+101」
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_LW17y3b2D_NQa14TKeaRDf1YqVm7AELv-UacFzpBlQjG7VXcLyuJhsL57RrAB9yRMXNPnT7ARttj9bKnVzZaDQQ_fCLJwDRzM6NhzXMgewxjMjG_1garXesdGq0259GFUHE6npF3Gj8j/s1600/5.jpg)
●手動隱藏+篩選:正確的有「subtotal+101」「subtotal+1」
●篩選+手動隱藏:正確的有「subtotal+101」「subtotal+1」
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKvywEdptgt5nmFMnNRDRs7p_oV4S24j4gS2geSbkfT8mNTNLam9Shy7afIbNd3nSEbsZMa3lhC4gbplUCcZeJzz-iRwHc6hA9IyglpkV7XVMIIGb5ZnORVJD4FZ4BeMS_7eERkIRV03wG/s1600/7.jpg)
KNB碎碎唸:
以上這樣說明各位同學們了解了嗎?自己在摸索一下,就能約略了解KNB再說甚麼了。KNB建議使用subtotal函數盡量都選擇101~111的項目,可以避免上述提到的狀況。
這裡提供Excel供各位下載練習使用「1-函數/excel-024subtotal」。
連結:https://drive.google.com/drive/folders/1PIFuMwuVv678Cr2hJCIU0AAcfCRXvD1o
延伸閱讀:
1. 2019/05/07 Excel函數-013_average-統計
2. 2019/05/08 Excel函數-014_count-統計
3. 2019/05/09 Excel函數-015_counta-統計
4. 2019/05/10 Excel函數-016_max-統計
5. 2019/05/11 Excel函數-017_min-統計
6. 2019/05/14 Excel函數-018_product-數學與三角函數
7. 2019/05/16 Excel函數-019_stdev.s-統計
8. 2019/05/18 Excel函數-020_stdev.p-統計
9. 2019/05/20 Excel函數-021_sum-統計
10. 2019/05/25 Excel函數-022_var.s-統計
11. 2019/05/27 Excel函數-023_var.p-統計
Subtotal厲害的地方是它會跟著表單的顯示範圍去計算,簡言之,就是只計算篩選出來的值,不計算隱藏的值。
函數類別:數學與三角函數
[函數] =SUBTOTAL(101,A5:A13)
△ 題目:試算個位數的平均值。
步驟一
● 直接輸入函數:才能確認自己選的號碼是哪個函數
(1) 在A3儲存格輸入「=subtotal(」
(2) 選擇「101-AVERAGE」
(3) 輸入「,」
(4) 選取範圍「A5:A13」
(5) 輸入「)」
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiSj7vmeHvH7oxe9q-fJpxT877AxAUBPWaI_3vdmVLox0QqHlVFNwtDy4VpCKZ5jVpmJkBzyA_xSUg3z1BNZwSKD3ql5-SO8Oeb0z943hjnSI8L5e3IAoOhaKHcgzCRyOuOqSzUTV6dmPI/s1600/1.jpg)
● 確認函數是否正確,選取A5:A13儲存格,可以看到下排的「平均值: 5」,答案與A3儲存格相符。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjH0P16ps8gWqnymYWdZQsc74_zgwXcVqN2xLsOPOTBbrQC8TigLvdTc2qZ0vF70dmhiV3b3bhgtcXQciqLKOwYZ1QWRilrxVSgEK9AnfsW9z_YGFLSvBjnKUjnWhQHVl3yTNu0RYuDhEE0/s1600/2.jpg)
完成
KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG / KOMOTONEKOBOX / KNB BLOG
補充說明:
一、subtotal裡共有22個函數可選擇
這個函數雖然方便,但還是有需要注意的地方。仔細看會發現1~11和101~111的函數是相同的(如下圖),那使用上有甚麼區別呢?
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYojZ6k94xnFtWKa1Ouc_38g-7HSvCB0uGQF6R69b0wF8ccvNY1GDcGNRv3m01xA9FOj9Q2BJM0hylAdUmMF8HKBPt8yhQFNlHHHQgnbN77tN88nAFltPL5xs5jtobbhSU6es8ZO53X2rB/s1600/8.jpg)
二、適用於哪些情況?
小本整理了表格給本友們參考:以下這三個都是average計算平均值的函數
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguRPaR1Au7a-evh7pkR7PrXZloRUSErp3NpGjJP5fdehAqvunvCYtCzPzl4XmyhssIHo19O3LXZc90Rrz-R50LbdcmCoFoQZoDc5uBytA-3SYo8FjSWZ0PlwO8m71SQAa2Lkb_APyPTPAi/s1600/9.jpg)
(1) 「subtotal+101」只計算顯示在表單中的數值;能忽略隱藏的值(手動隱藏&篩選&手動隱藏+篩選)。
(2) 「subtotal+1」手動隱藏、篩選、手動隱藏+篩選後,可以計算顯示在表單的數值;(手動隱藏+篩選)。
單獨手動隱藏會無法計算。
(3) 「average」只適用於一般不須篩選和隱藏的情況使用。完全不能在這三種情況下計算(手動隱藏&篩選&手動隱藏+篩選)。
三、範例
● 小本用「subtotal+101」的表格試做一份範例,如下:
[圖1] 手動隱藏 >>不影響結果
[圖2] 篩選 >>不影響結果
[圖3] 手動隱藏+篩選 >>不影響結果
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTZ0RQm47RDTQWENG85O1VwfbBRlsmjRtwp5aPV_MvSQhNUggaFKvLsEG5kRkYJ-AHgHUuGtdUlSDSaDAygmz5sF50VEsKBjDzr6iRIDNiM2KD7g92khgKCHJci0J34Z1vUqjUDIf1vu0d/s1600/3.jpg)
●「subtotal+101」「subtotal+1」「average」分別做以下對照,可配合「二、適用於哪些情況裡的一覽表」確認。
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqcalJUET_60Q7zF-9YLqV7RWiy-g7_52jICfPUUTyW9N7yt4E9ZXrR2qZRit636Qsyd8g-r9H7CnOY5qW4djEbd4zIOs2lBOoItg5hfsoS78T5CcevwEE1ZrEJ5WZIvQXXdnXqB1E6v4T/s1600/4.jpg)
●手動隱藏:正確的有「subtotal+101」
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_LW17y3b2D_NQa14TKeaRDf1YqVm7AELv-UacFzpBlQjG7VXcLyuJhsL57RrAB9yRMXNPnT7ARttj9bKnVzZaDQQ_fCLJwDRzM6NhzXMgewxjMjG_1garXesdGq0259GFUHE6npF3Gj8j/s1600/5.jpg)
●手動隱藏+篩選:正確的有「subtotal+101」「subtotal+1」
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8KxHw7BvilxU41f081z5kLOyYURGPMP6cNgTqwOcXcQULIQWTRqM2OM_mfai076iD1yXreni-KM_sVsKI977iSbkVOsCiJzA9HUhrLkd4jn9WYn7wG_ExDg1TPY7ZSzOCsa8ctmZBDfoa/s1600/6.jpg)
●篩選+手動隱藏:正確的有「subtotal+101」「subtotal+1」
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKvywEdptgt5nmFMnNRDRs7p_oV4S24j4gS2geSbkfT8mNTNLam9Shy7afIbNd3nSEbsZMa3lhC4gbplUCcZeJzz-iRwHc6hA9IyglpkV7XVMIIGb5ZnORVJD4FZ4BeMS_7eERkIRV03wG/s1600/7.jpg)
KNB碎碎唸:
以上這樣說明各位同學們了解了嗎?自己在摸索一下,就能約略了解KNB再說甚麼了。KNB建議使用subtotal函數盡量都選擇101~111的項目,可以避免上述提到的狀況。
這裡提供Excel供各位下載練習使用「1-函數/excel-024subtotal」。
連結:https://drive.google.com/drive/folders/1PIFuMwuVv678Cr2hJCIU0AAcfCRXvD1o
延伸閱讀:
1. 2019/05/07 Excel函數-013_average-統計
2. 2019/05/08 Excel函數-014_count-統計
3. 2019/05/09 Excel函數-015_counta-統計
4. 2019/05/10 Excel函數-016_max-統計
5. 2019/05/11 Excel函數-017_min-統計
6. 2019/05/14 Excel函數-018_product-數學與三角函數
7. 2019/05/16 Excel函數-019_stdev.s-統計
8. 2019/05/18 Excel函數-020_stdev.p-統計
9. 2019/05/20 Excel函數-021_sum-統計
10. 2019/05/25 Excel函數-022_var.s-統計
11. 2019/05/27 Excel函數-023_var.p-統計