✔ 最佳答案
第二列公式設置為(以B2姓名及C2輸入週數數值參考):
D2 =SUMIFS(工作表1!$F$2:$F$15000, 工作表1!$B$2:$B$15000,B2, 工作表1!$Q$2:$Q$15000,C2)
E2 =SUMIFS(工作表1!$J$2:$J$15000, 工作表1!$B$2:$B$15000,B2, 工作表1!$Q$2:$Q$15000,C2)
F2 =SUMIFS(工作表1!$N$2:$N$15000, 工作表1!$B$2:$B$15000,B2, 工作表1!$Q$2:$Q$15000,C2)
G2 =SUMIFS(工作表1!$P$2:$P$15000, 工作表1!$B$2:$B$15000,B2, 工作表1!$Q$2:$Q$15000,C2)
以上公式參考姓名和週數來將第2:15000列加總,須視乎員工數目和一年工作日數調整列數上限。
工作表1儲存格P2如須表達超時時數扣除補假時數淨值,公式應修改為「=IF(O2="是",0,N2-J2)」。
2015-04-30 11:05:09 補充:
核對公式結果有否出錯是對的,在DATEDIF公式中參考G4,如與今天相比超過3個月會傳回「是」,不超過3個月會傳回「否」。G4日期是4月25日,距離今天4月30日只有5日,所以傳回「否」是正常的。如試將G4改成25/1/2015,公式便會傳回「是」,可測試多個日期,例如30/1/2015、31/1/2015等,以判定心目中的計算邏輯是否相符,如少算了一天,可將公式中TODAY()改成TODAY()+1或G4改成G4-1,只有進行多個測試,才可驗證公式的正確性和有否盲點。
2015-04-30 14:05:34 補充:
相信並非理解錯誤,而是大家用了不同邏輯。我理解的「超過3個月」,是和今日相比,就像護照今天是否過期失效一樣,所以公式裡用了TODAY()來計算距離「超時工作日期」是否「超過3個月」。如果你希望以「補假工作日期」和「超時工作日期」相距來計算,那麼一日未輸入補假資料,一日都未過期,便顯示不出最新有效狀態,當然如果原意是要在輸入補假資料時計算是否過期亦可,只須修改公式將TODAY()換成K4便可,但須留意欄位標籤是否需要配合更改,以便反映該行資料真正的含意。沒有人會比你更清楚想做些甚麼,所以你要為自己把守最後一關,我只是按常理邏輯思維來設計公式,並不確保能夠百分百切合你的想法。
2015-04-30 14:14:24 補充:
還有一點要考慮的就是,原先希望定立規矩超過3個月不能補假,現在若果要到輸入補假資料時才計算出應該失效,是否會變成先斬後奏、米已成炊呢?所以我的想法是和今日比,實時顯示是否過期失效,是的話在申請補假時先檢查一下就會及早發覺,不是等違規放完補假才發現,你說是嗎?
2015-04-30 18:34:52 補充:
建議第一列欄位設置為:
A1 編號
B1 姓名
C1 年度
D1 首週週數
E1 次週週數
F1 累計兩週工作時數
G1 累計兩週超時工作時數
H1 累計兩週補假工作時數
I1 累計兩週有效超時補假時數
這裡假設了你會輸入「年度」和「首週週數」到C、D行,然後次週是首週加一週,由於到年尾第53週後,只有當潤年366日兼且1月1日是星期日才會出現第54週,因此E2視乎計算方法有兩種公式版本:
2015-04-30 18:35:45 補充:
1. E2公式只算到53週,不用出現第54週,因為12月31日撥入下一年度計算
=IF(D2=53,““,D2+1)
2. E2公式需出現第54週,因為要包含完整一個年度計算
=IF(D2=53, IF(AND(DATE(C2,12,31)-DATE(C2,1,1)=365,WEEKDAY(DATE(C2,1,1),2)=7),54,""), D2+1)
上面公式中AND是檢查年度是否潤年(頭尾相差365日)和1月1日是否星期日。
設定好週數後,便輪到累計公式(將首週、次週兩個週數的累計一週工作時數相加):
2015-04-30 18:36:58 補充:
F2 =SUMIFS(工作表1!$F$2:$F$15000, 工作表1!$B$2:$B$15000,B2, 工作表1!$Q$2:$Q$15000,D2)+ SUMIFS(工作表1!$F$2:$F$15000, 工作表1!$B$2:$B$15000,B2, 工作表1!$Q$2:$Q$15000,E2)
G2=SUMIFS(工作表1!$J$2:$J$15000, 工作表1!$B$2:$B$15000,B2, 工作表1!$Q$2:$Q$15000,D2)+ SUMIFS(工作表1!$J$2:$J$15000, 工作表1!$B$2:$B$15000,B2, 工作表1!$Q$2:$Q$15000,E2)
2015-04-30 18:39:43 補充:
H2和I2的公式頖似,差別在第一引數參考N和P行。
(字數已達上限)