2013年4月10日 星期三

Excel-資料分析表(SUMPRODUCT)

有網友問到:在 Excel 的相關文章中有一篇提到如下圖左的資料表,想要轉換成如下圖右的分析表。本篇再把做法清楚描述一下,你也可以使用樞紐分析工具來做,現在要練習的是使用 SUMPRODUCT 函數。

【準備工作】

選取所有資料範圍,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:部門、職務、年資。

【輸入公式】

(1) 計算各處室不同職務年資

儲存格H2:=SUMPRODUCT((部門=$G2)*(職務=H$1)*(年資>=INT(ROW(3:3)/3)*5-4)*(年資<=INT(ROW(3:3)/3)*5))

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

(年資>=INT(ROW(3:3)/3)*5-4):向下複製公式時可以產生 1, 6, 11, 16, 21, 26。

(年資<=INT(ROW(3:3)/3)*5):向下複製公式時可以產生 5, 10, 15, 20, 25, 30。

 

(2) 計算各處室不分職務年資

儲存格K2:=SUMPRODUCT((部門=$G2)*(年資>=INT(ROW(3:3)/3)*5-4)*(年資<=INT(ROW(3:3)/3)*5))

 

【補充資料】

相關之詳細函數說明,請參閱微軟網站:

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

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

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

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

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

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

沒有留言:

張貼留言

好康東東