2015年9月12日 星期六

Excel-公式的條件中使用萬用字元(SUMIFS,SUMPRODUCT)

在 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。

沒有留言:

張貼留言

檢視其他文章

好康東東