網友問到：在 Excel 裡如果要跨工作表計算指定項目的個數，該如何處理？
如下圖，想要在四個工作表裡相同位置儲存格範圍裡，計算指定項目的個數。
因為如果使用 COUNTIF 函數無法活跨工作表使用，如果為每工作表的計算使用一個 COUNTIF 函數，當引用的工作表愈多，公式總長度就會愈長，該如何處理？
【公式設計與解析】
假設工作表名稱是sheet1、sheet2、sheet3、sheet4，是具有連續編號的名稱。
資料在每個個工作表的儲存格範圍是A2:A16。
儲存格C2：
=SUMPRODUCT(COUNTIF(INDIRECT("sheet"&ROW($1:$4)&"!A$2:A$16"),B2))
複製儲存格C2，貼至儲存格C2:C7。
(1) INDIRECT("sheet"&ROW($1:$4)&"!A$2:A$16")
利用 ROW(1:4)在SUMPRODUCT函數中產生1,2,3,4陣列集。
"sheet"&ROW($1:$4)&"!A$2:A$16"藉以產生sheet1!A2:A16、sheet2!A2:A16、sheet3!A2:A16、sheet4!A2:A16、
INDIRECT 函數用以將名稱sheet1!A2:A16轉換為可用的位址。
(2) COUNTIF(第(1)式,B2)
利用 COUNTIF 函數將第(1)式的傳回值計算對應儲存格B2的內容相符的個數。
(3) SUMPRODUCT(第(2)式)
利用第(2)式的傳回值計算總合。在此的 SUMPRODUCT 函數用以執行陣列運算。
本例這樣處理，如果工作是sheet1~sheet20，則只要將公式裡的：
ROW($1:$4) 改為 ROW($1:$20)，公式長度不會改變。
本例也可以改以陣列公式設計：
儲存格C2：
{=SUM(COUNTIF(INDIRECT("sheet"&ROW($1:$4)&"!A$2:A$16"),B2))}
公式輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
留言列表