Mr.Lazy愛偷懶先生

同事問我說: 如何用 Excel 計算電話時間費用、停車費? 如果時間跨到隔天的話，該怎麼計算?

讀者如果有上述問題的話，可以使用到簡單的數學公式就能解決此問題。

練習檔: 第169篇.練習檔下載。

會使用到的函數 :

• 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。