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

【準備工作】

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

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

 

【公式設計與解析】

要統計的四個表格,使用 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。

全站熱搜

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