2013年11月25日 星期一

Excel-陣列公式(INDEX,SUMIF,SUMPRODUCT)

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

以下就使用不同的函數來求下圖右的運算。為了方便公式的使用與說明,先選取儲存格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 運算。

沒有留言:

張貼留言

檢視其他文章

好康東東