在執行 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 列為隱藏狀態)

 

【延伸閱讀-FILTER函數篩選應用】

Excel-下拉式清單選取月份列出該月日期

圖片1 Excel-根據單條件和雙條件篩選資料(FILTER)

圖片1 Excel-列出指定星期幾的日期

圖片1 Excel-單一欄位篩選與跨欄位篩選

圖片1 Excel-計算分組最大值

圖片1 Excel-FILTER和OFFSET的動態陣列

圖片1 Excel-篩選資料並轉置資料

圖片1 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

圖片1 Excel-由資料清單中篩選一組

圖片1 Excel-利用FILTER函數模糊篩選

圖片1 Excel-從日期清單中區別平日和假日計算總和

圖片1 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

圖片1 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

圖片1 Excel-FILTER函數與進階篩選

圖片1 Excel-2021版新增函數的使用

 

學不完.教不停.用不盡文章列表

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(3) 人氣()