Excel 設定公式一問

公式思路:先假設是在當天的0:00上班, 以下班的時間去計算津貼, 再減去上班時間之前的津貼公式困難點: 太多時間及金額資料, 如全部放在公式內, 且要重複引用, 公式會相當長, 而有些時間值放在公式內亦不太方便, 如18:00可寫成0.75, 但19:00是要寫成0.7917, 故建議用輔助格列出時間及對應的金額方法:A1:B9是輔助格A1:A4分別輸入 0, 6:00, 13:00,19:00, 再在A5輸入A1 +1, 將A5下拉至A9B1:B9輸入: 0, 30, 60, 95,130, 160, 190, 225, 260D1是上班時間, E1是下班時間E1輸入公式: =LOOKUP(E1+(E1<D1),A$1:A$8,B$2:B$9)-LOOKUP(D1,A$1:A$8,B$1:B$8)公式可下拉注意: 06:02 ~ 06:01(通宵更), 雖然不足24小時, 但是跨了5個時段, 故津貼是160 請測試


2012-09-22 11:41:54 補充：
E1



2012-09-22 11:52:29 補充：
(E1





2012-09-22 11:54:54 補充：
(E1





2012-09-22 12:00:58 補充：
(E1＜D1)即代表通宵, 要加上一天
(E1＜D1), 如符合, 出True, 若否, 出False
如之前有運算符, 如+-*/, 是會將邏輯值激活為數字, True 為1, False 為0
如用if, 可能會較易明白, (E1＜D1)改為IF(E1＜D1,1,0)
最好在追問之前, 能明示答題的公式及方法及結果是否已OK

2012-09-22 22:50:42 補充：
經Sky兄提醒, 公式是在F1輸入, 上文寫成E1輸入公式是錯誤, 特此更正

可以用Excel VBA來解決
之前讀完個課程,課程有教到Excel VBA實用技巧
我覺得導師教得幾好
有免費試睇
由基礎到進階都有
詳細可以參考Excel VBA 程式編寫基礎班
http://www.systematic.com.hk/vba.htm

我上表所 講的 "上班時間為 06:00 - 12:59 ", 其實是假設員工番工時間是 06:00, 放工時間是12:59

簡單講, 其實只要 "中" 個 period, 就會有錢, 所以, 如果 番工時間只係 12:59, 放工時間是13:01 , 即只係番左 2 mins, 由於佢番工時間包括左 2 個 period, 所以係 可以 出到 30 + 35, 合共 65 的 錢, 所以,, 總數小於 6小時與否, 都不在計算之列

2012-09-17 10:04:09 補充：
故此, 回應番你問的:

上班時間為 06:00 - 12:59 ---> 有 $30, 無津貼 ( 答: 只有 $30, 因只中一個 period )
上班時間為 13:00 - 18:59 ---> 有 $35, 連津貼合共 $65 ( 答: 只有 $35, 因只中一個 period )

2012-09-17 10:04:23 補充：
上班時間為 09:00 - 23:59 ---> 有 $35, 連津貼合共 $100 ( 答: 有 $100, 因中了3個 period )

上班時間為 24:00 - 29:59 (即通宵) ---> 有 $30, 連津貼合共 $130 ( 答: 只有 $30, 因只中一個 period )

謝謝幫忙!!

2012-09-22 09:32:12 補充：
想問一下, 在公式入面 E1+(E1細過D1) 的作用是什麼? "<" 的作用為何?? 謝謝!!

2012-09-22 09:32:39 補充：
想問一下, 在公式入面 E1+(E1細過D1) 的作用是什麼? "細過" 的作用為何?? 謝謝!!

2012-09-24 10:10:49 補充：
回答者 漢鍾離 的答案 已是我想要的結果, 謝謝幫忙!!

根據你的兩個例子, 上班時間 12:59 - 13:02, 就會有 30 + 35 = 65, 上班時間 06:01 - 29:59, 就會有齊 30 + 35 + 35 + 30 = 130, 請問以下的計算是否正確?

上班時間為 06:00 - 12:59 ---> 有 $30, 無津貼
上班時間為 13:00 - 18:59 ---> 有 $35, 連津貼合共 $65
上班時間為 09:00 - 23:59 ---> 有 $35, 連津貼合共 $100
上班時間為 24:00 - 29:59 (即通宵) ---> 有 $30, 連津貼合共 $130

謝謝您儘快的回覆!

2012-09-16 16:15:44 補充：
另外, 如果每一組的時間範圍是少過所設定的總時數, 舉例: 06:00 - 12:59 總時數是不少於 6小時, 但該員工的返工時間為 07:00 - 12:59 / 05:50 - 12:40 / 08:00 - 13:50, 是否都給予 $30? 抑或如何處理? 謝謝您的回覆.

2012-09-22 11:22:36 補充：
請於 A1 至 A5 輸入 0:00, 6:00, 13:00, 9:00, 0:00,
B1 至 B5 輸入 5:59, 12:59, 18:59, 23:59, 23:59,
C1 輸入 =B1-A1, 再把C1 向下拉至 C5,
D1 至 D4 輸入 30, 30, 35, 35,
E1 至 E4 輸入 0, 0, 0, 65,
F1 輸入 =D1+E1, 再把F1 向下拉至 F4, F5 輸入 130.

A10 輸入上班時間 (如: 6:00), B10 輸入下班時間 (如: 23:59), C10 輸入 =B10-A10, F10 輸入以下公式, 得出100:

2012-09-22 11:26:11 補充：
=IF(AND(A10>=TIME(0,0,0),B10<=TIME(5,59,0)),F1,IF(AND(A10>=TIME(6,0,0),B10<=TIME(12,59,0)),F2,IF(AND(A10>=TIME(13,0,0),B10<=TIME(18,59,0)),F3,IF(AND(A10>=TIME(6,0,0),

2012-09-22 11:27:10 補充：
接續上條方程:

B10<=TIME(18,59,0)),F2+F3,IF(AND(A10>=TIME(6,0,0),B10<=TIME(23,59,0)),F4,IF(AND(A10>=TIME(0,0,0),B10<=TIME(23,59,0)),F5,"error"))))))

2012-09-22 11:28:23 補充：
2012-09-17 23:27:11 補充

我希望有其他網友高手能夠提供更好的解決方法, 謝謝各位!

2012-09-22 11:14:09 補充

漢鍾離:

您好! 你又出現啦! 謝謝您的幫忙!

2012-09-22 11:36:05 補充：
Alex:

上次因有網友選擇了一個不合適的答案, 令一位正確的解答者失去了分數, 我把我之前的解答搬了過來, 讓正確的答案保留在回答區內供網友們作參考.

2012-09-22 11:39:57 補充：
Alex:

請問你可否把時間延長, 讓解答者有充足的時間作分析, 謝謝您!

2012-09-22 17:24:43 補充：
Alex,

請問你之前在意見區內留下給我的資料的計法是否肯定是對的? 因為題目的設立如果是不清晰, 將會影響解答者的公式的寫法.

解答者希望你先肯定他的津貼計算結果是否正確, 如否, 你可以再更正題目讓解答者能在發問到期之前幫助你儘快解決問題, 給予正確的答案.

謝謝您的回覆.

2012-09-22 18:37:11 補充：
Alex,

請留意回答欄裡的解答內容:

D1 是上班時間, E1 是下班時間, "E1" 輸入公式的 "E1" 應更正為 "F1", A1:A9 的時間必須要順序由小至大排列, 位置不能交錯, 否則會出現錯誤的答案.

2012-09-23 18:42:04 補充：
兄?? Little Little boy!!

2012-09-24 20:45:04 補充：
Alex,

謝謝您的回覆! 我很高興您的問題終能被解答者解決得到了!