學不完．教不停．用不盡

網友問到：在 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 會自動加上「｛｝」。