請問如何統計班表的資料(正常日、加班、星期日)? - Excelㄧ般區

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

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

1^# 跳轉到 »

發表於 2021-10-23 08:07 | 顯示全部帖子

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

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

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

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

2^#

發表於 2021-10-23 16:38 | 顯示全部帖子

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

選擇 B2:AF3 設定格式化，星期日不同顏色
B2:AF3 直接設底色
B2:AF8 設定格式化，公式 =AND(WEEKDAY(B$2,2)=7,COUNTA($B2:$AF2)>0) ，填顏色

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

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

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

3^#

發表於 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 三鍵輸入公式

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

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

4^#

發表於 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 三鍵輸入公式