學不完．教不停．用不盡

最近網友又問到了陣列的使用。使用陣列可以讓公式可以簡短一些，可以減少一些輔助欄位的使用，但也增加初學者理解上的難度。參考下圖，通常會在一堆資料中，透過陣列公式來取得符合某些條件的陣列(篩選)，再進一步進行運算。

以下就使用不同的函數來求下圖右的運算。為了方便公式的使用與說明，先選取儲存格C1:D16，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：類別、費用。

【陣列公式】

如果你要在公式中套用陣列的觀念，必須在輸入公式後，按一下 Ctrl+Shift+Enter 鍵，Excel 會自動幫你在公式上套用 {  XXX  } ，表示這個公式是陣列公式。

(1) 儲存格F2：{=SUM((類別="數位電子")*費用)}

(類別="數位電子")：會找出在「類別」陣列中符合「數位電子」的 True/False 陣列，其中相符者為 True，不符合者為 False。

(類別="數位電子")*費用：將上述的 True/False 陣列和「費用」相乘，在運算過程中 True 會被轉換為 1，而 False 會被轉換成 0。所以，其結果只會留下條件陣列為 True 者所對應的費用。透過 SUM 函數予以加總，即為所求。

(2) 儲存格F5：{=SUM((RIGHT(類別,2)="電子")*費用)}

透過 RIGHT 函數取出「類別」陣列中右邊二個字為「電子」者的陣列，再加以運算。

(3) 儲存格F8：{=SUM((LEFT(類別,2)="電腦")*費用)}

透過 LEFT 函數取出「類別」陣列中左邊二個字為「電腦」者的陣列，再加以運算。

有些函數本身就具有陣列運算的概念，所以輸入公式後不用再按 Ctrl+Shift+Enter 鍵。例如以下數種。

【SUMIF函數】

(1) 儲存格F2：=SUMIF(類別,"數位電子",費用)

(2) 儲存格F5：=SUMIF(類別,"*電子*",費用)

(3) 儲存格F8：=SUMIF(類別,"*電腦*",費用)

公式中使用「"*電子*"、"*電腦*"」，其中的「*」是萬用字元(可以代表0或1以上的字元)。在 SUMIF 函數中的篩選條件可以使用條件運算，例如：「">250"」、「"<>數位電子"」、「"<>*電子*"」。

【SUMPRODUCT函數】

(1) 儲存格F2：=SUMPRODUCT((類別="數位電子")*費用)

(2) 儲存格F5：=SUMPRODUCT((RIGHT(類別,2)="電子")*費用)

(1) 儲存格F8：=SUMPRODUCT((LEFT(類別,2)="電腦")*費用)

SUMPRODUCT 函數為使用「乘積和」的觀念。

【INDEX函數】

(1) 儲存格F2：=SUM(INDEX((類別="數位電子")*費用,))

(2) 儲存格F5：=SUM(INDEX((RIGHT(類別,2)="電子")*費用,))

(3) 儲存格F8：=SUM(INDEX((LEFT(類別,2)="電腦")*費用,))

透過 INDEX 函數以「查表」方式篩選出想要的陣列，再執行 SUM 運算。