學不完．教不停．用不盡

在 Excel 中有一個常見的如下圖左的資料表，如果要建立如下圖右的四個統計表，要如何處理？

【準備工作】

為了方便說明，先來定義儲存格範圍的名稱。選取儲存格A1:D25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：年級、類別、級別、人數。

【公式設計與解析】

要統計的四個表格，使用 SUMIFS 函數+萬用字元「*」，即可解決，如果你使用的版本(2007以前)沒有 SUMIFS 函數，則可以改用 SUMPRODUCT 函數來設計。

(1)

儲存格H2：=SUMIFS(人數,年級,$F2,級別,$G2)

儲存格H2：=SUMPRODUCT((年級=$F2)*(級別=$G2)*人數)

複製儲存格H2，貼至儲存格H2:H5。

(2)

儲存格H8：=SUMIFS(人數,級別,F8,類別,"*"&LEFT(G8,2)&"*")

"*"&LEFT(G8,2)&"*"：LEFT(G8,2)取出類別的「電子」和「電腦」，因為在類別項目中，「電子」和「電腦」置於類別字串中的不同位置，所以使用萬用字元「*」，可以忽略其位置。

儲存格H8：
=SUMPRODUCT((級別=$F8)*ISNUMBER(FIND(LEFT($G8,2),類別))*人數)
 

LEFT($G8,2)：取出類別的「電子」和「電腦」。

FIND(LEFT($G8,2),類別)：使用 FIND 函數來搜尋「電子」和「電腦」在類別字串中的位置。如果搜尋的到字串，則傳回一個數字(位置)，否則傳回 #VALUE! 錯誤訊息。

ISNUMBER(FIND(LEFT($G8,2),類別))：判斷上式的傳回值是否為數值，傳回 TRUE／FALSE 陣列。

(X) 儲存格H8：=SUMPRODUCT((級別=$F8)*("*"&LEFT($G8,2)&"*"=類別)*人數)

這個式子所得到的結果全為 0，因為 "*"&LEFT($G8,2)&"*" 會傳回 "*電子*" 或是 "*電腦*" 字串。在此，「*」無法當為萬用字元使用。

複製儲存格H8，貼至儲存格H8:H11。

(3)

儲存格H14：=SUMIFS(人數,類別,"*"&LEFT(F14,2)&"*",年級,G14)

儲存格H14：
=SUMPRODUCT(ISNUMBER(FIND(LEFT($F14,2),類別))*(年級=$G14)*人數)

複製儲存格H14，貼至儲存格H14:H19。

(4)

儲存格H22：=SUMIFS(人數,類別,"*"&LEFT(F22,2)&"*",級別,G22)

儲存格H22：
=SUMPRODUCT(ISNUMBER(FIND(LEFT($F22,2),類別))*(級別=$G22)*人數)

複製儲存格H22，貼至儲存格H22:H25。