2011年11月17日 星期四

Excel-計算儲存格乘積的和(陣列)

在 Excel 中有一個資料表含有項目A和項目B的數列(參考下圖左),若要將A欄和B欄相乘的結果加總,該如何處理(參考下圖右)?

你可以仿照C欄,將A欄和B欄相乘,然後將C欄的資料加總。但是我們要練習的是以一個儲存格即要完成這個工作。參考以下的作法:

 

【準備工作】

選取儲存格A1:B22,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目A、項目B。

 

【計算:所有 AXB 的總和】

(1) 使用 SUMPRODUCT 函數可以方便的計算乘積:

儲存格G2:=SUMPRODUCT(項目A,項目B)

(2) 使用陣列公式(輸入完成,要按 Ctrl+Shift+Enter 鍵。)

儲存格G2:{=SUM(項目A*項目B)}

將 (1) 和 (2) 對照比較一下,很容易可以理解其用法。

 

【計算:所有 A/B 的總和】

如果要計算 A/B 的總和,就沒有像 SUMPRODUCT 這類的函數可以使用,不過,你可以使用陣列公式(輸入完成要按 Ctrl+Shift+Enter 鍵。)

儲存格G3:{=SUM(項目A/項目B)}

 

【計算:A>50 且 B>5 的個數】

(1) 儲存格G4:=SUMPRODUCT(--(項目A>50),--(項目B>5))

在 SUMPRODUCT 函數中加入條件判斷,其中的「--」,乃是將 True / False 陣列轉換成 1 / 0 的陣列。

(2) 儲存格G4:{=SUM((項目A>50)*(項目B>5))}

使用陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

將 (1) 和 (2) 對照比較一下,很容易可以理解其用法。

 

【計算:A>50 且 B>5 的 AXB 總和】

儲存格G5:{=SUM(IF(項目A>50,項目A)*IF(項目B>5,項目B))}

使用陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

 

【註記】

希望藉由這四個連續的不同運算,能對陣列公式的理解有一點幫助。如果真的無法使用陣列公式,也是要多一些輔助欄位,多一些運算式也是能完成的。能正確運算的方法都是好方法!並非一定要使用陣列公式才是比較好的。

沒有留言:

張貼留言

檢視其他文章

好康東東