2012年8月13日 星期一

Excel-SUMPRODUCT+COUNTIF應用

根據在 Excel 的一個資料表(如下圖左),要產生如下圖右的兩種不同資料型態((一)和(二)互為轉置),如何下公式?

首先,選取儲存格A1:E23,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、東、南、西、北。

【輸入公式】

1. 小計(一)

儲存格H2:=SUMPRODUCT(--(INDIRECT(H$1)=$G2))

複製儲存格H2,貼至儲存格H2:K5。

因為儲存格H2:K2的內容,剛好就是已定義好的名稱:東、南、西、北,所以使用 INDIRECT 函數,來將儲存格內容轉換為真實的位址。

藉由定義名稱 INDIRECT 函數轉換為位址的動作,即可讓公式的長度縮短,並且易於理解。

2. 小計表(二)

儲存格H8:=COUNTIF(INDIRECT($G8),H$7)

複製儲存格H8,貼至儲存格H8:K11。

原理同上。

 

【補充資料】

關於函數說明,請參閱微軟網站的說明:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

檢視其他文章

好康東東