Excel 寫formula問題

2009-06-25 10:05 pm
想問下點寫條formula出黎(急)
e.g1) A1=16:16
B1=18:10
B1-A1=1:54
咁樣即係用左1個鐘頭54分鐘, 但係點樣將佢四捨五入做2個鐘呢?即係C1=2

e.g2) A1=8:00
B1=11:15
B1-A1=3:15
咁樣即係用左3個鐘頭15分鐘, 但係點樣將佢四捨五入做3.5個鐘呢?即係C1=3.5

有冇人知點寫條式出黎??
唔該!!

回答 (4)

2009-06-25 11:17 pm
✔ 最佳答案
如果理解沒有錯,你是以每半小時計四捨五入.Excel時實為數字,1=24小時.所以有下列公式:

=ROUND((B1-A1)*48,0)/48

2009-06-25 15:33:35 補充:
=ROUND((B1-A1)*48,0)/48 會出3:30(如以時間格式)
=ROUND((B1-A1)*48,0)/2 會出3.5(要以數字格式)

2009-06-27 09:02:30 補充:
其實=ROUND((B1-A1)*48,0)/2也相當易明:
B1-A1就是相差的時間是多少天
(B1-A1)*24是相差多少小時
(B1-A1)*48是相差多少個半小時時段
ROUND((B1-A1)*48,0)就是將多少個半小時時段作四捨五入
ROUND((B1-A1)*48,0)/2就是將半小時時段化會小時(如7個半小時時段是3.5小時).
2009-06-26 7:35 pm
我想到最簡單而又容易明白的方法是用 MROUND() 。

首先,如 nelsonywm2000 所說,時間在 Excel 內是以「日」為儲存單位 ,所以 1 在 Excel 內就等於 24 小時。

問題第二個例子中,B1 - A1 是 3 個小時 15 分鐘,在 Excel 內就是 0.135416667 日,將之乘以 24 ,就得出 3.25 ,亦即 3.25 個小時。

你要的四捨五入,即大於或等於半小時,進位成半小時,否則棄掉分鐘。這其實就是以半小時作四捨五入。

利用 MROUND 你選擇將某個數以某個倍數來作四捨五入,亦即是說 MROUND(X, Y) 就是將 X 四捨五入至最接近 Y 的倍數,如 MROUND(3.25, 0.5) = 3.5 因為最接近 3.25 而又是 0.5 的倍數的有 3.0 及 3.5 ,當中 3.5 就是四捨五入後的結果。

所以,以下公式可以做到你要的答案:

=MROUND((B1-A1)*24, 0.5)


2009-06-26 12:13:08 補充:
另外, MROUND 亦可以方便地更改以多少分鐘作四捨五入,例如如果以後情況有變,你要以 15 分鐘而不是半小時來作四捨五入,那麼你只要將 0.5 改成 15/60 或 0.25 即可,如 3 小時 10 分,以 15 分鐘作四捨五入,就是 3 小時 15 分鐘,即 3.25 :

MROUND(3+10/60, 15/60) = 3.25

而 3 小時 5 分鐘,因 5 分鐘不超過 15 分鐘的一半,就會「四捨」,答案即會變成 3

=MROUND(3+5/60, 15/60) = 3

2009-06-26 12:15:33 補充:
如你用 MROUND 時出現 #NAME? 錯誤,即表示你沒有載入 Analysis Toolpak (分析工具箱) ,可以根據以下方法解決:

http://office.microsoft.com/zh-tw/excel/HP011277241028.aspx?pid=CH062527761028
2009-06-26 9:44 am
Nelson 的 48思路相當好, 數感強
另一思路, 僅供參考:
=FLOOR((B1-A1+"0:15")*24,0.5)

2009-06-26 23:06:19 補充:
第一次見到此函數, 謝謝BiGLiN提供
MROUND: 傳回四捨五入至最接近的數字
既然Excel已有預設的函數, 當然是優先考慮
2009-06-25 11:23 pm
若 c1是豕時間的差, 則在 d1 輸入
=IF(MINUTE(C1)/60>=0.75, HOUR(C1)+1, IF( MINUTE(C1)>=0.25, HOUR(C1)+0.5, HOUR(C1)))
這樣,
1. 多過 45 分鐘作一小時計
2. 多過 15 分鐘 而又小於 45 分鐘 作 0.5 小時計
3. 小於 15 分鐘 不計.
記得將 d1 的格式設定為 G/通用.


收錄日期: 2021-04-23 23:18:44
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20090625000051KK00948

檢視 Wayback Machine 備份