[求助] 精通 Excel 的朋友請幫幫忙..

achen

這個問題, 也許會的人 30 秒就搞定了, 但是我已經搞了超過一個小時.... 我對 Excel 的 formula 實在是個白癡...

我需要做一個 Excel, 輸入以下資料:
(A) Start Time (hh:mm 格式)
(B) End Time (hh:mm 格式)
(C) Break Time (hh:mm 格式)

表尾計算 (B)-(A) 再減掉 (C) = work hours
比如說輸入:
(A) 9:00
(B) 16:00
(C) 1:30

計算出來這個 job 從 8:00到16:00 是 8 小時, 扣掉 (C) 休息一小時30分所以應該輸出 work time = 6:30

我搞不定的地方是在(A) 和 (B) 輸入的內容是實際時間點 (1:00 等於"凌晨 1:00"), 但是在 (C) 輸入的內容是小時數 (1:00 等於"1小時")

有朋友可以幫個忙嗎??

[[i] Last edited by achen on 2004-10-14 at 18:38 [/i]]

dgg

Originally posted by achen at 2004-10-14 11:30 PM:
這個問題, 也許會的人 30 秒就搞定了, 但是我已經搞了超過一個小時.... 我對 Excel 的 formula 實在是個白癡...

我需要做一個 Excel, 輸入以下資料:
(A) Start Time (hh:mm 格式)
(B) End Time (hh:mm 格式)
(C) Break Time (hh:mm 格式)

表尾計算 (B)-(A) 再減掉 (C) = work hours
比如說輸入:
(A) 9:00
(B) 16:00
(C) 1:30

計算出來這個 job 從 8:00到16:00 是 8 小時, 扣掉 (C) 休息一小時30分所以應該輸出 work time = 6:30

我搞不定的地方是在(A) 和 (B) 輸入的內容是實際時間點 (1:00 等於"凌晨 1:00", 但是在 (C) 輸入的內容確是小時數 (1:00 等於"1小時"

有朋友可以幫個忙嗎??

=(b-a-c)就可得出答案了
很簡單呀
我在試算表上用你的數據算
答案就是6:30

[ Last edited by dgg on 2004-10-15 at 00:02 ]

polar168

Originally posted by dgg at 2004-10-14 11:59 PM:


=(b-a-c)就可得出答案了
很簡單呀
我在試算表上用你的數據算
答案就是6:30

[ Last edited by dgg on 2004-10-15 at 00:02 ]

呃......我想achen所說的是......
A:開始時間
B:結束時間
C:中間休息小時數 => 搞不定的是這一欄顯示的是時間點,而不是幾個小時
D:總工作小時數

achen

唉唉唉.. 我今天整個腦子都被這個 Excel 檔弄到不清楚了
明天到公司再拿出來繼續作...

LAkaiY

你是你這樣做沒有錯, 對excel 而言, 時間點和時間長度是一樣的. 上午八點就是指從零點開始八個小時. 所以八個小時也是一樣. 你不要在意上面資料欄內的時間. 你只要format 欄位內的格式就可以了. 除非你有特別的需要, 例如顯示出"200 小時"等等.

你目前的計算有任何錯誤嗎?

achen

Originally posted by LAkaiY at 2004-10-14 07:25 PM:
你是你這樣做沒有錯, 對excel 而言, 時間點和時間長度是一樣的. 上午八點就是指從零點開始八個小時. 所以八個小時也是一樣. 你不要在意上面資料欄內的時間. 你只要format 欄位內的格式就可以了. 除非你有特別的需要, 例如顯示出"200 小時"等等.

你目前的計算有任何錯誤嗎?

其實我上面的附檔是經過簡化之後(重新建立)的檔案, 我原本從 user 手上拿到的檔, 稍微複雜一點, 計算確實有錯誤 .. 我也沒有發現這個重頭做起的超級簡化檔, 居然可以計算無誤, 實在是擺了個大烏龍..  soli, soli...

不過既然第一層問題解決了, 我還有第二層的問題, 希望有 Excel 高手可以為我解答.

最後一格 Total 輸出的時間格式, 必須再轉換一次為數字格式, 以 0.25 為進位單位, 比如說:

8:00 -> 八小時 -> 8.00
7:30 -> 七小時半 -> 7.50
8:15 -> 八小時 15 分鐘 -> 8.25
7:45 -> 七小時 45 分鐘 -> 7.75

換句話說用四捨五入的說法,
分鐘數為 0 +- 7 的時候, 小數點後面是 .00
分鐘數為 15 +- 7 的時候, 小數點後面是 .25
分鐘數為 30 +- 7 的時候, 小數點後面是 .50
分鐘數為 45 +- 7 的時候, 小數點後面是 .75


感謝各位幫忙或給意見, 不要打我..

不是我機車, 以 1/4 小時進位是公司薪資部門要求的格式.. (可能是政府規定的)

不是我懶, 求救前我已經花很多時間真的搞不出來

也不是我擺爛, 而是我部門裡面一個精通 Excel 的人離職了, 結果這個差事落到我這個 Excel 白痴上面..

[ Last edited by achen on 2004-10-14 at 20:19 ]

Exelion

試試這個 formula :

=HOUR(B6)+ROUNDDOWN((MINUTE(B6)+7)/15,0)/4

B6 是 Total 輸出的時間格式
記得把欄位改成數字格式

achen

Originally posted by winwell at 2004-10-14 09:20 PM:
依附件中的位置, 試試以下的 fomula 吧....

=HOUR(B3)-HOUR(B2)-HOUR(B4)+IF(MINUTE(B3)-MINUTE(B2)-MINUTE(B4)<0,0,1)-INT((ABS(MINUTE(B3)-MINUTE(B2)-MINUTE(B4))+7)/15)*0.25

記得結果欄的格式要改成 2 位小數的數字 哦..

有點小小的問題..
當 B6 (Total) 是 6:00 的時候, 這個公式計算出 7.00
如果直接用你這個公式, 下去處理 B6 的時間, 會不會有可能結果比較正確??

achen

Originally posted by Exelion at 2004-10-14 10:04 PM:
試試這個 formula :

=HOUR(B6)+ROUNDDOWN((MINUTE(B6)+7)/15,0)/4

B6 是 Total 輸出的時間格式
記得把欄位改成數字格式

這也有點小小的問題..
當 B6 (Total) 是 6:30 的時候, 這個公式計算出 6.00 (應該是 6.50)
這個計算式文法沒錯, 可能小小調整一下就會正確了...

superman

我最近也有去學 Excel ，真佩服 winwell 及 Exelion 兩位，achen 他們兩位的答案應正確，或有其他問題，我也可代為問一下我 Excel 的老師

Exelion

Originally posted by achen at 2004-10-15 10:30 PM:


這也有點小小的問題..
當 B6 (Total) 是 6:30 的時候, 這個公式計算出 6.00 (應該是 6.50)
這個計算式文法沒錯, 可能小小調整一下就會正確了...

我又試了一下，給6:30 算出來的是 6.50 啊 ??
公式應該沒錯 :o

另外會用 ROUNDDOWN 是因為沒看到有 INT 這個函數
捨簡求繁，罪過罪過....

感謝 winwell 的指正。

achen

真是很感謝winwell 及 Exelion 兩位的解答, 也讓大家有機會再這邊討論 Excel 這個可簡單亦可高深的應用軟體

我想這是我交件出去之前的最後一個問題了...(請下載附件)

說明如下:

1. Row 13 和 14 會是隱藏的, 假設 Row 14 的 Data 是 X 的話
2. Row 16: 如果 X 大於 8 的話, 顯示 8, 如果小於 8, 就直接顯示 X
2. Row 17: 如果 X 大於 8, 就顯示比8多多少(X-8), 如果小於 8, 就空白

以上的敘述看起來好像很抽象, 但是如果有在上班打卡的朋友應該比較容易瞭解, 每日工時超過 8 小時的話就算加班費, 所以 Row 14, Row 16 和 Row 17 的關係是如下的:

7.50 -> 7.50, 空白
8.00 -> 8.00, 空白
8.75 -> 8.00, 0.75
9.00 -> 8.00, 1.00

Fcat

Originally posted by achen at 2004-10-17 08:00 AM:
1. Row 13 和 14 會是隱藏的, 假設 Row 14 的 Data 是 X 的話
2. Row 16: 如果 X 大於 8 的話, 顯示 8, 如果小於 8, 就直接顯示 X
2. Row 17: 如果 X 大於 8, 就顯示比8多多少(X-8), 如果小於 8, 就空白

Row 16 = IF(Row 14 >8,8,Row 14)

Row 17 = IF(Row 14 >8,X-8," ")

應該這樣就可以了

[ Last edited by Fcat on 2004-10-17 at 13:26 ]

achen

Originally posted by Fcat at 2004-10-16 09:20 PM:


Row 16 = IF(Row 14 >8,8,Row 14)

Row 17 = IF(Row 14 >8,X-8," "

應該這樣就可以了

[ Last edited by Fcat on 2004-10-17 at 13:26 ]

Thanks, that works!

但是上面附檔裡的 cell format 在 Row 16, 17 忘了改到 number, 只是通知有興趣下載來研究看看的朋友.

nekocat

對不起！想順便請教一下，
大家有推薦的...針對Excel巨集函數及VBA講的比較好的書嘛！？

謝謝您！