學不完．教不停．用不盡

本範例是延續前五篇文章的研習講義：

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-下拉式清單選取月份列出該月日期

 Excel-根據單條件和雙條件篩選資料(FILTER)

 Excel-列出指定星期幾的日期

 Excel-單一欄位篩選與跨欄位篩選

 Excel-計算分組最大值

 Excel-FILTER和OFFSET的動態陣列

 Excel-篩選資料並轉置資料

 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

 Excel-由資料清單中篩選一組

 Excel-利用FILTER函數模糊篩選

 Excel-從日期清單中區別平日和假日計算總和

 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

 Excel-FILTER函數與進階篩選

 Excel-2021版新增函數的使用