Excel-多年度請假記錄統計(SUMPRODUCT,INT,MOD)

這次來練習在 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)&"時"


-------------------------------------------------------------------------------------

作者：學不完．教不停．用不盡
來源：http://ibf.tw/F3ARL
備註：如果作者不希望被轉帖到此處，請告知會馬上刪帖。