2015年7月30日 星期四

運算時排除篩選掉或被隱藏的內容(STBTOTAL)

在執行 Excel 的各種運算工作時,常會遇到某些列可能會被隱藏掉或被篩選掉(沒有顯示出來),但是你只想要計算被顯示出來的資料,該如何獲得想要的結果呢?
例如下圖中,儲存格B17:=SUM(B2:B16),可以計算AAA的總和。
當有部分資料被隱藏時,如果只想計算被顯示的資料總和,原公式無法獲取正確的結果。例如:下圖中,第 6~9 列被「手動隱藏」了。
建議改用 SUBTOTAL 函數,其中參數 9 和 109 都是用以計算總和(SUM):
在 SUBTOTAL 函數中參數,分為:1~11和101~111兩部分。但是上式中使用 109,結果正確;而使用 9 時,結果錯誤。
 
其中,數字 1-11 或 101-111 指定要用於計算小計的函數。1-11 包括手動隱藏的列,但 101-111 會排除掉手動隱藏的列;篩掉的儲存格一律會排除。所以上式中,如果使用參數 9,則會包括隱藏的資料,所以結果不正確。
再來看看另一種狀況,如果資料被篩選掉而不想被併入計算。當然使用 SUM 函數必然是錯誤的。
再次使用 SUBTOATL 函數,不管參數 9 或 109,結果都是正確的。因為被篩選掉的資料都會被排除計算!
特別注意到:如果在篩選的狀況,再手動隱藏某些列的資料,不管參數 9 或 109,結果都是正確的。
最後要提醒,如果你想要快速看一下只計算顯示資料的和,不妨在選取資料後,直接檢視狀態列的加總項,因為這裡的總和計算,是預設排除被隱藏和被篩選掉的資料。(第 6~9 列為隱藏狀態)

沒有留言:

張貼留言

好康東東