本範例是延續前五篇文章的研習講義：
1. Excel-SUMPRODUCT函數範例與說明(研習範例)
2. Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)
3. Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)
4. Excel-多人多項的金額統計(SUMPRODUCT練習)
5. Excel-SUMPRODUCT結合其他函數達成篩選及統計的功能(LEFT,INDIRECT,ROW)
這次來練習在 SUMPRODUCT 函數中以三維向度來統計各年的請假記錄統計。
下圖是一個由資料庫系統轉出的請假記錄表，其中『起日、迄日』的表示為年(三碼)＋月(二碼)+日(二碼)所組成。而『合計』欄位是以每小時 0.1 計，每滿 8 小時以 1 計。
【公式設計與解析】
選取所有請假資料的儲存格範圍，按 Ctrl+Shift+F3 鍵，定義名稱：假別、起日、起時、迄日、迄時、合計、換算、備註。
(1)『換算』欄位
儲存格H2：=INT(G2)*8+MOD(G2,1)*10
INT(G2)*8：將儲存格G2中的數值取出整數部分，再乘以8，換算成時數。
MOD(G2,1)*10：將儲存格G2中的數值取出小數部分，再乘以10，換算成時數。
複製儲存格H2，往下各列貼上。
(2) 『備註』欄位
儲存格I2：=INT(G2)&"天"&MOD(G2,1)*10&"時"
其中『&』為串接運算子，用以將文字和運算式串接。
複製儲存格I2，往下各列貼上。
(3) 設計下拉式清單選取年度
為了讓年度可以動態顯示，所以設計使用下拉式清單選取年度。
選取儲存格K1，設定「資料驗證」為：
準則：清單，來源：100,101,102,103,104
(4) 統計每個人分年之各類假別時數-1
儲存格L2：(第４式)
=SUMPRODUCT((VALUE(LEFT(起日,3))=$K$1)*(姓名=$K2)*(假別=L$1)*換算)
VALUE(LEFT(起日,3))：運用 LEFT 函數取出起日欄位中每儲存格前三個字，並透過 VALUE 函數轉換為數字。結果傳回起日欄位所有日期前三碼的陣列。
(VALUE(LEFT(起日,3))=$K$1)*(姓名=$K2)*(假別=L$1)：藉由『*』為運算三個條件執行邏輯 AND 運算，傳回 TRUE／FALSE 陣列。
執行公式中的『*換算』時，『*』為乘法運算，計算時會將 TRUE／FALSE 陣列轉換為 1／0 陣列。
複製儲存格L2，貼至儲存格L2:S12。
(5) 統計每個人分年之各類假別時數-2
儲存格L15：
=INT(『第４式』/8)&"天"&MOD(『第４式』,8)&"時"
將『第４式』的結果轉換為 n 天 m 小時格式。
完整公式，儲存格L15：
=INT(SUMPRODUCT((VALUE(LEFT(起日,3))=$K$14)*(姓名=$K2)*(假別=L$1)*
換算)/8)&"天"&MOD(SUMPRODUCT((VALUE(LEFT(起日,3))=$K$14)*(姓名=$K2)*
(假別=L$1)*換算),8)&"時"
【延伸閱讀：FILTER函數篩選應用】
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
留言列表