我要回覆

鐵支Chien

0分

樓主

鐵支Chien

個人積分：0分

文章編號：88286460

請教如何計算加班時數區間並各別加總？

自己在嘗試協助不同部門進行名單統計加班時數計算，但遇到一些問題想請教大家，主要問題還是因為開始計算加班時數的4個小時中，前2個小時跟後2個小時的計價方式不同，因此遇到一些問題。

「如何在每日表格內以前2後2區間判斷分別加總？」
請教如何計算加班時數區間並各別加總？

一
如上圖17號到28號進行分辯每日加班時數並以2為區分基準分別加總

例如：2小時以上-2.5小時判斷區分為 2與0.5；3 小時判斷區分為 2與1
並分別小計加總為，前兩小時共計4小時；後兩個小時共計1.5小時
主要是想要呈現這樣的計算方式

不知道什麼函數可以每日進行判斷區分加總？
還請各位大大不吝指教

2023-09-22 1:12 #1

文章關鍵字

時數區間

jaredsam

1505分

2樓

jaredsam

個人積分：1505分

文章編號：88286517

兩種做法
1. 把A工人單獨抓出來，日期用B3-B33，由上往下，就很好計算
2小時內 D3=IF(C3="","",IF((C3-2)>0,2,C3))
2-4小時 D3=IF(C3="","",IF((C3-2)>0,C3-2,""))

點我看大圖

2. 新增一個工作表，或是直接在右邊做2個1-31日，，1個1-31日是2小時內，1個是2-4小時，
2小時內 =IF(C3="","",IF((C3-2)>0,2,C3))，然後往右拉
2-4小時同上

點我看大圖

這個方式我在使用的時候會有一個盲區，就是我的表格會隨著月份，星期會不同，但我所要計價的方式只限制在周一到周五，分類加總會有問題

2023-09-22 14:02

Yaude Huang

5274分

3樓

Yaude Huang

個人積分：5274分

文章編號：88288008

給你參考

=SUM(FILTER(A:A, A:A<=2))

這樣就把所有小於等於2的加總起來了

要另外計算兩小時以上的更簡單
把所有時間加總起來減去前面計算那個數字就是答案了

=SUM(A:A)-B8

Yaude Huang

但是你的算法，總不能跟人解釋你有幾天加班超過兩小時，前兩小時怎麼計價，剩下的怎麼計價，然後大費周章算好一個月的數字給別人看，自找麻煩。

2023-09-24 6:21

Yaude Huang

所以我才說，其實我不怎麼愛怎麼跟你解釋如何產生你的需求，就算你是2016年版的EXCEL，換個角度開發，也是可以很輕鬆設計出來的。完全跳脫FILTER的使用，連別人教你那些複雜的運用都可以避免。

2023-09-24 6:26

et3x

30分

4樓

et3x

個人積分：30分

文章編號：88291048

判斷S2欄位星期幾,星期1~5就顯示1~5,星期6顯示6,星期日顯示7
=WEEKDAY(B1&"/"&E1&"/"&S2,2)

求2小時總共有幾個,算完後再乘回來((N29欄位為要計算的格子
=QUOTIENT(N29,2)*2

求除2剩下的值(N29欄位為要計算的格子
=MOD(N29,2)

------------------------------------
結論,綜上公式合體
判斷星期幾,只要小於星期6就開始計算除2後的商數再*2,這樣就求出2的倍數值
=IF(WEEKDAY(B1&"/"&E1&"/"&S2,2)<6,QUOTIENT(N29,2)*2,0)

同上,不過是求被2除不盡的餘數
=IF(WEEKDAY(B1&"/"&E1&"/"&S2,2)<6,MOD(N29,2),0)

以上淺見供參考

感謝大大分享我在試試看

2023-09-23 9:00

南風天

109分

5樓

南風天

個人積分：109分

文章編號：88292210

在後方加入2欄判斷前後各2小時的加班時間，
所有判斷都將六日去除。

前2小時：
=COUNTIFS(C4:AG4,">=2",$C$3:$AG$3,"<>六",$C$3:$AG$3,"<>日")*2+SUMIFS(C4:AG4,C4:AG4,"<2",$C$3:$AG$3,"<>六",$C$3:$AG$3,"<>日")
COUNTIFS是判斷有幾個2小時以上的個數(後面要乘2)，
SUMIFS是判斷不足2小時的時間。

後2小時：
=SUMIFS(C4:AG4,$C$3:$AG$3,"<>六",$C$3:$AG$3,"<>日")-AI4
將總時數相加扣除前兩小時即可。