返回列表 上一主題 發帖

[發問] 請問如何統計班表的資料(正常日、加班、星期日)?

[發問] 請問如何統計班表的資料(正常日、加班、星期日)?

本帖最後由 gaishutsusuru 於 2021-10-22 23:01 編輯

大家好,
我自製一張表,但在計算公式上一直試不出來,因此想請教大家如何計算班表的資料呢?

以下是相關的細節:
擷取1.PNG
2021-10-22 22:42

(1) 這張圖為了說明,因此只截圖1~10日 (完整31天的日期資料,再煩請見附檔操作)
(2) 符號說明:
早班:「S」
中班:「C」
夜班:「D」
「S+2」:早班,有加班2小時
「C+1」:中班,有加班1小時
排休:「休」
…其餘以此類推

擷取.PNG
2021-10-22 22:43

希望能把紅字這些數字,用公式計算出來。
(1) 出勤有分成:早班中班晚班 (註:出勤不計入星期日)
(2) 加班小時,有分成:平常日加班、星期日加班
(3) 星期日有分成:早班中班晚班

最後,附上附檔: 新增 Microsoft Excel Worksheet.zip (7.9 KB)

希望大家能抽空幫忙協助提供想法,非常感謝大家。(如對問題描述有任何不清楚,歡迎留言給我,謝謝)

上下班及加班統計.rar (12.56 KB)

20211023.jpg
2021-10-23 08:06
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 2# ML089


原來如此,真的非常謝謝ML089大大的幫忙,公式可以用哦。:)

TOP

回復 3# gaishutsusuru
補充一下,儲存格顏色設定方式

選擇 B2:AF3 設定格式化,星期日不同顏色                       
        B2:AF3 直接設底色               
        B2:AF8 設定格式化,公式 =AND(WEEKDAY(B$2,2)=7,COUNTA($B2:$AF2)>0) ,填顏色
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 4# ML089

謝謝您的幫忙。

但在實做上又遇到另一個問題,因此想再請教您:

若考慮到早退的情況,該如何處理呢?

擷取.PNG
2021-10-26 15:39

1個班都是8小時,情況舉例:(+代表加班,-代表早退)
(1) S-1/8:上早班,但早退1小時
(2) S+2-2/8:上早班,但早退2小時,但當天又跑來加班2小時  (實務上少見,但仍有可能出現)
因此,希望能把公式改成,出現像是「黃色螢光筆」的數字。
(我嘗試用了search來判斷,改寫公式,但一直試不出來)

希望您能抽空提供幫忙,謝謝您。


註:我是用您附的檔案進行編輯的,因此就未再附上excel檔了。

TOP

回復 5# gaishutsusuru

AH4 =SUMPRODUCT((LEFT($B4:$AF4,1)=MID(AH$3,4,1))*($B$3:$AF$3<>"日")) - SUMPRODUCT((LEFT($B4:$AF4,1)=MID(AH$3,4,1))*($B$3:$AF$3<>"日")*TEXT(MID($B4:$AF4,{2;4},2),"!0;0;;!0"))/8               
AK4 =SUMPRODUCT((LEFT($B4:$AF4,1)=MID(AH$3,4,1))*($B$3:$AF$3="日")) - SUMPRODUCT((LEFT($B4:$AF4,1)=MID(AH$3,4,1))*($B$3:$AF$3="日")*TEXT(MID($B4:$AF4,{2;4},2),"!0;0;;!0"))/8               
AN4 =SUMPRODUCT(TEXT(MID($B4:$AF4,{2;4},2),"0;!0;;!0")*($B$3:$AF$3<>"日"))               
Ao4 =SUMPRODUCT(TEXT(MID($B4:$AF4,{2;4},2),"0;!0;;!0")*($B$3:$AF$3="日"))               
AH4:AM5 儲存格格式 "# #/8"
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 6# ML089


真的非常謝謝ML089大大,公式可以用,真的幫助我很大。

另外,想請教您「TEXT(MID($B4:$AF4,{2;4},2),"0;!0;;!0"」這個公式裡面的「{2;4}」、「"0;!0;;!0"」的用意是什麼呢? 這個在書面好像幾乎沒有提到,不知道可否請您抽空解惑呢?


謝謝您 :)

TOP

回復 7# gaishutsusuru


    「TEXT(MID($B4:$AF4,{2;4},2),"0;!0;;!0"」這個公式裡面的「{2;4}」、「"0;!0;;!0"」的用意是什麼呢?
text( ... , "0;!0;;!0")   表示 TEXT 的格式  "正數; 負數; 0; 文字"  處理方式,  !0 表示 任一數都以 0 顯示,所以 "0;!0;;!0" 意思將正數留下,其他都設為 0

MID(... , {2;4},2) 連續取2個數字,列
S+2-2/8 會取出 {"+2" ; "-2")  ---> "+2" 視為 正數2 ; "-2"視為負數2
S-1/8 會取出 {"-1" ; "/8") ---> "-1" 視為 正數1 ; "/8"視為文字
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

回復 8# ML089


原來如此,謝謝ML089大大的解惑,謝謝您的抽空協助。

TOP

        靜思自在 : 吃苦了苦、苦盡廿來,享福了福、福盡悲來。
返回列表 上一主題