excel 公式運算

1. 建議時間格式採用24小時制，一來方便計算，二來避免錯誤，試想補假由上午6:00至下午6:00，無法單憑數字計算出12小時，要檢查上下午，令公式複雜化。
2. 在D、E行出現的標籤「上午/下午」、F/I/L行出現的「小時」，應由自訂格式負責顯示，不用影響儲存格維持數值方便計算。
3. 在H行出現的「晚上」應統一為「下午」，同一時間下午6:00有時稱下午、有時稱晚上，或會輸入不一致令公式計算錯誤。
4. 建議H行及K行各自分拆成兩行，H行分成「超時工作開始時間」和「超時工作完成時間」，以避免過於複雜的公式；按同一理由K行亦分成「補假工作開始時間」和「補假工作完成時間」。
5. 建議修訂欄位如下：
項目名稱
A1 編號
B1 姓名
C1 上班日期
D1 上班時間
E1 下班時間
F1 全日上班工作小時
G1 超時工作日期
H1 超時工作開始時間
I1 超時工作完成時間
J1 超時工作時數
K1 補假工作日期
L1 補假工作開始時間
M1 補假工作完成時間
N1 補假工作時數
6. 建議D/E、H/I、L/M等行的自訂格式為「上午/下午 h":"mm」(不設「晚上」標籤)。
7. 建議F、J、N等行的自訂格式為「0.0"小""時"」(如改用較簡單的「0"小""時"」，將不能顯示半小時等不足一小時單位)。
8. 時間數值以24小時制輸入，在D2為「9:00」，在E2為「18:00」；在H/I、L/M等行亦相同。
9. 完成格式設定後可開始制定公式，在F2計算工作時數公式為「=(E2-D2)*24」，可複製至J2及N2。
10. 看是否需要添加一欄「O1 補假已逾3個月有效期」，O2公式為「=IF(DATEDIF(K2,TODAY(),"YM")>=3,"是","否")」。
11. 現在資料表是輸入每列是單日資料，若要計算「累計每週工作時數」，「累計兩週工作時數」，「累計超時補假時數」，須分開用另一累計表單顯示，每位員工一列，將其累計工作時數總結。

2015-04-26 21:26:11 補充：
當N2儲存格數值大於2就會出現紅色顯示，毋須設定公式，只需在N行套用條件格式>醒目提示儲存格規則>大於(G)...>輸入數值「2」並選擇格式「着紅色」即可。
至於要計算「累計每週工作時數」，「累計兩週工作時數」，「累計超時補假時數」，其中最重要一樣事情是先定義如何計算週數，一般有以下四種定義：
1. ISO 週：每週由星期一算起，年曆的第一個星期一定為第一週。(此定義或會令第一週開始前有數日沒有週數，例如2015年第一個星期一是在1月5日，這日起定為週一，那麼1月1日至1月4日便沒有週數) 。

2015-04-26 21:26:47 補充：
2) Excel WEEKNUM 功能伴隨可選第二引數數值為1 (缺省值) ：第一週由1月1日開始，第二週由緊隨其後的星期日開始。
3) Excel WEEKNUM功能伴隨可選第二引數數值為2：第一週由1月1日開始，第二週由緊隨其後的星期一開始。
4) 簡單週數：第一週由1月1日開始，第二週由1月8日開始，直至第五十三週只有一或兩天(如屬潤年)。
由於每個不同定義須套用不同計算公式，須視乎實際應用情況制定週數定義，才能製作合適公式。

2015-04-26 21:27:16 補充：
另外如「超時補假時數」超過3個月未補假便失效，似乎還要加上「P1 有效超時補假時數」來儲存仍然有效超時補假時數，其公式為「=IF(O2="是",0,N2)」，距今超過3個月失效補假時數設為零。

2015-04-27 19:22:10 補充：
按上班日期設定運算ISO週數的公定為：
=INT((C2-DATE(YEAR(C2-WEEKDAY(C2-1)+4),1,3)+WEEKDAY(DATE(YEAR(C2-WEEKDAY(C2-1)+4),1,3))+5)/7)
補假已逾三個月有效期是由G1 超時工作日期計起，可修改公式為：
=IF(DATEDIF(G2,TODAY(),"YM")>=3,"是","否")

2015-04-28 16:23:05 補充：
「N2儲存格數值大於2就會出現紅色顯示」以及「ISO週數」都是在工作表1的設定，在工作表1加添多一行來計算週數，才能方便工作表2憑藉週數來作出累計，例如用sumif、countif等。可考慮在工作表1加上「Q1 ISO週數」，放上公式來計算每個上班日所屬週數。

2015-04-29 10:49:38 補充：
第一列欄位設置為：
A1 編號
B1 姓名
C1週數
D1 累計一週工作時數
E1累計一週超時工作時數
F1累計一週補假工作時數
G1累計一週有效超時補假時數

2015-04-29 10:56:55 補充：
字數已滿