2015年12月7日 星期一

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

本範例是延續前五篇文章的研習講義:
這次來練習在 SUMPRODUCT 函數中以三維向度來統計各年的請假記錄統計。
Excel-多年度請假記錄統計(SUMPRODUCT,INT,MOD)
下圖是一個由資料庫系統轉出的請假記錄表,其中『起日、迄日』的表示為年(三碼)+月(二碼)+日(二碼)所組成。而『合計』欄位是以每小時 0.1 計,每滿 8 小時以 1 計。
Excel-多年度請假記錄統計(SUMPRODUCT,INT,MOD)v

【公式設計與解析】
選取所有請假資料的儲存格範圍,按 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
Excel-多年度請假記錄統計(SUMPRODUCT,INT,MOD)

(4) 統計每個人分年之各類假別時數-1
Excel-多年度請假記錄統計(SUMPRODUCT,INT,MOD)
儲存格L2:(第4式)
=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
Excel-多年度請假記錄統計(SUMPRODUCT,INT,MOD)
儲存格L15:
=INT(『第4式』/8)&"天"&MOD(『第4式』,8)&"時"
將『第4式』的結果轉換為 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)&"時"

沒有留言:

張貼留言

檢視其他文章

好康東東