有網友問到：在 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))
【補充資料】
相關之詳細函數說明，請參閱微軟網站：
SUMPRODUCT：http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx
|
SUMPRODUCT：傳回各陣列中所有對應元素乘積的總和。 |
|
語法：SUMPRODUCT(array1, [array2], [array3], ...) array1：要求對應元素乘積和的第一個陣列引數。 array2, array3,...：要求對應元素乘積和的第 2 個到第 255 個陣列引數。 註：各陣列必須有相同的維度(相同的列數，相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。 |
留言列表