帖子: 146
主題: 46
精華: 0
積分: 220
點名: 0
作業系統: 7
軟體版本: 2010
閱讀權限: 20
註冊時間: 2014-2-19
最後登錄: 2023-8-31

1^# 跳轉到 » 正序看帖

字體大小: tT

發表於 2021-10-22 22:55 | 只看該作者

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

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

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

以下是相關的細節：

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

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

最後，附上附檔：

新增 Microsoft Excel Worksheet.zip (7.9 KB)

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

樓主熱帖

gaishutsusuru

中學生 Rank: 2

帖子: 146
主題: 46
精華: 0
積分: 220
點名: 0
作業系統: 7
軟體版本: 2010
閱讀權限: 20
註冊時間: 2014-2-19
最後登錄: 2023-8-31

9^#

發表於 2021-10-26 23:08 | 只看該作者

回復 8# ML089

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

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

8^#

發表於 2021-10-26 22:42 | 只看該作者

回復 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

gaishutsusuru

中學生 Rank: 2

帖子: 146
主題: 46
精華: 0
積分: 220
點名: 0
作業系統: 7
軟體版本: 2010
閱讀權限: 20
註冊時間: 2014-2-19
最後登錄: 2023-8-31

7^#

發表於 2021-10-26 21:07 | 只看該作者

回復 6# ML089

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

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

謝謝您 :)

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2021-10-26 20:04 | 只看該作者

回復 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

gaishutsusuru

中學生 Rank: 2

帖子: 146
主題: 46
精華: 0
積分: 220
點名: 0
作業系統: 7
軟體版本: 2010
閱讀權限: 20
註冊時間: 2014-2-19
最後登錄: 2023-8-31

5^#

發表於 2021-10-26 15:44 | 只看該作者

回復 4# ML089

謝謝您的幫忙。

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

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

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

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

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

TOP

ML089