同事問我說: 如何用 Excel 計算電話時間費用、停車費? 如果時間跨到隔天的話,該怎麼計算?
讀者如果有上述問題的話,可以使用到簡單的數學公式就能解決此問題。
會使用到的函數 :
- HOUR
- MINUTE
- SECOND
- INT
- MOD
- IF
完成圖:
Excel 中的時間計算
在開始教學之前,讀者要先搞清楚在 Excel 中,除了正常的時間格式之外,還有其他的表示方式,
顯示時間的方式,除了「時間規格」之外,還有一種是「通用格式」的顯示方式。
假定,一整天 24 小時等於數字 1 的話,06 : 00 就是 0.25;12 : 00 就是 0.5 ....依此類推。
從上述得知,可以利用一般公式來實現跨天的通話、停車費用計算,公式 : =(1-X)+1-(1-Y)
《公式可配合下圖,更能了解公式來源。》
關於如何將時間更改成「通用格式」:
位置 : 常用功能區→數值→格式右邊的選單(倒三角)→選擇「一般」。
通話費計算
《計算通話費時,如遇跨天的通話紀錄,如: 通話時間為 23:50~ 00:10,一般計算方式會造成錯誤。》
為了避免錯誤產生,請參考下方步驟 :
1. 輸入完成「開始通話」、「結束通話」的時間。
2. C3 儲存格套用【Excel中的時間計算】公式 : =(1-A3)+(1-(1-B3)),結果為 0:05:09。
3. 將 C3 儲存格的時間 0:05:09,轉化成秒數,並設定格式為一般(通用格式)。
D3儲存格 : =HOUR(C3)*60*60+MINUTE(C3)*60+SECOND(C3),可得 309 秒。
- HOUR(C3)*60*60 : 小時單位乘 60 化成分鐘,再乘 60化成秒數。結果為 0 。
- MINUTE(C3)*60 : 分鐘單位乘 60 化成秒數。結果為 300 。
- SECOND(C3) : 呈現秒數單位數值。結果為 9 。
4. 計算通話【分鐘】
費用單位為【分鐘】/ 60秒。假定,超過 1 秒的話,仍以 1 分鐘做計算,換言之,無條件進位計算費用的意思。
計算通話分鐘公式 :
F3 儲存格 : =INT(D3/60)+IF(MOD(D3,60)=0,0,1)。結果為 6 。
- INT(D3/60) : 309 除以 60,只取整數,結果為 5 。
- IF(MOD(D3,60)=0,0,1) : 如果 309 除以 60 的餘數等於 0 ,顯示 0 ,否則顯示 1 。
- MOD(D3,60) : 309 除以 60 的餘數,結果為 9 。
注意: 上述的數字單位為【分鐘】。
5. 實際費用計算
G3儲存格 : =F3*$E$3。費用 相乘 分鐘,結果為 1.56 元。
停車費用計算
1. 計算【停車時間】
C11 儲存格 : =(1-A11)+(1-(1-B11)),結果為 8:29:25 。
2. 將【停車時間】轉化成秒數,並設定一般格式(通用格式)。
D11儲存格 : =HOUR(C11)*60*60+MINUTE(C11)*60+SECOND(C11),結果為 30565 。
- HOUR(C11)*60*60,結果為 28800 。
- MINUTE(C11)*60,結果為 1740 。
- SECOND(C11),結果為 25 。
3. 計算停車單位
F11儲存格 : =INT(D11/1800)+IF(MOD(D11,1800)=0,0,1)
- =INT(D11/1800) ,結果為 16 。
- IF(MOD(D11,1800)=0,0,1),結果為 1。
- MOD(D11,1800),餘數為 1765。
4. 實際費用計算
G11儲存格 : =F11*$E$9,結果為 680。
留言列表