用 Excel 解決計時問題

用 Excel 解決計時問題
秋葉Excel 2023/10/22 11:45:00 責編：夢澤評論：20
在日常工作中，通話時長的計算，或者是商業(yè)區(qū)停車時長的計算收費等，都是非常常見的。

它們有的是按分鐘收費。

比如：電信通話時長，不足 1 分鐘按 1 分鐘計算。

有的是按半小時或者 1 小時計算。

比如：商業(yè)區(qū)停車收費，不足半小時或者不足 1 小時就全部按半小時或者 1 小時來計算收費等等。

今天，我們就來分享下按時間長度計算的問題與解決思路。

1、問題要求
如下圖，是一張時長計算表：

用 Excel 解決計時問題

要求是：計算開始時間與結束時間之間的分鐘數(shù)。不足一分鐘按一分鐘計算。

比如：

開始時間是 10 時 30 分 35 秒；

結束時間是 10 時 38 分 59 秒；

之間的時間差是：8 分 24 秒；

不足一分鐘按一分鐘計算，最后結果即為：9 分鐘。

2、解題方法
從上面的要求來看，解題思路大概可以分為以下兩種：

▋方法 1
通常情況下，最為簡單的而且是最好理解的是使用時間函數(shù)來處理。

比如：Hour 取小時數(shù)，Minute 取分鐘數(shù)，Second 取秒數(shù)。

如下圖：我們可以在【D3：D6】單元格輸入如下公式：

用 Excel 解決計時問題

=HOUR(C3-B3)*60+MINUTE(C3-B3)+(SECOND(C3-B3>0)
公式解析：

? 用【C3】的結束時間減去【B3】的開始時間，然后用 Hour 函數(shù)取出小時數(shù)并乘以 60，得出分鐘數(shù)。

? 用【C3】的結束時間減去【B3】的開始時間，然后用 Minute 函數(shù)取出分鐘數(shù)，直接得出分鐘數(shù)。

? 用【C3】的結束時間減去【B3】的開始時間，然后用 Second 函數(shù)取出秒數(shù)并判斷是否大于 0，如果大于 0，就返回 TRUE，用加法讓其變成 1 分鐘。如果小于等于 0，返回 False，用加法讓其變成 0 分鐘。

從而實現(xiàn)了不足 1 分鐘，按 1 分鐘計算的目的。

▋方法 2
其實，方法 1 只是從原理上來計算時間長度。

我們也可以換用專門用于計算進位的函數(shù)（Ceiling 函數(shù)）來輕松解決此類問題。

它的語法如下：

=CEILING(number, significance)

=CEILING (要舍入的值，倍數(shù))

翻譯成中文就是：

將要舍入的值向上舍入到指定倍數(shù)。

那么，上面的計算可以改成：

用 Excel 解決計時問題

【D3】公式如下：

=CEILING(C3-B3,TIME(0,1,0))*1440
公式解析：

先用結束時間減去開始時間，之后按照一分鐘的倍數(shù)向上舍入，之后再乘以 1440 分鐘。

其中：Time 函數(shù)是表示時間的函數(shù)。

它的語法如下：

=Time (小時，分鐘，秒)

這里是要按 1 分鐘為倍數(shù)，所以小時和秒都為 0。

這里的 1440 是指：1 天為 24 小時，1 小時為 60 分鐘，即：24*60=1440

此處的公式還可以進一步簡化，直接改寫為如下圖：

用 Excel 解決計時問題

=CEILING((C3-B3)*1440,1)
公式解析：

先用結束時間減去開始時間，之后乘以 1440 分鐘數(shù)，然后按 1 的倍數(shù)向上舍入到整數(shù)。

兩種解決思路供大家參考。

3、知識擴展
?  在日常工作中，我們可能還會接觸到使用 Text 函數(shù)來獲取和處理時間。

如下圖：

用 Excel 解決計時問題

公式：

=TEXT(C3-B3,"[m]")
先用結束時間減去開始時間，之后用 Text 函數(shù)取出之間的分鐘數(shù)。

但是，小伙伴們要特別注意下：

這里用 Text 函數(shù)取出分鐘數(shù)，它是直接截尾取整數(shù)。

也就是它只管小時和分鐘數(shù)，而不管秒數(shù)有或者沒有，都將其忽略不計。

另外：大家在取分鐘數(shù)的時候千萬不要只寫一個 M。

如下圖：

用 Excel 解決計時問題

公式如下：

=TEXT(C3-B3,"m")
此時如果只寫一個字母 M 的話，它只會取出兩個時間相減的月份數(shù)，而不是分鐘數(shù)。

這里的時間對應的日期默認為 1900 年 1 月 0 日。

所以取其月份數(shù)，結果返回 1。

?  在使用 Ceiling 函數(shù)的時候，有可能會出現(xiàn)下面的錯誤結果。

如下圖：

用 Excel 解決計時問題

公式與上面一模一樣：

=CEILING((C3-B3)*1440,1)
結束時間減去開始時間應該為 10 分鐘，但是結果卻是錯誤的 11 分鐘。

我們可以看下 Ceiling 函數(shù)里面的第一參數(shù)的運算結果，如下圖：

用 Excel 解決計時問題

里面的運算結果產(chǎn)生了一個極小的尾差，所以導致向上舍入到 11 分鐘了。

解決方法可以用 Round 函數(shù)進行舍入處理。

用 Excel 解決計時問題

修正公式如下：

=CEILING(ROUND((C3-B3)*1440,6),1)
這里用 Round 函數(shù)保留 6 位小位，超過 6 位的，直接四舍五入。

最后結果顯示正確！

4、寫在最后
今天，我們分享了日常工作中計算時間長度的常用方法與思路。

? 可以使用最基礎的時間函數(shù)來處理時長問題。比如：Hour\Minute\Second 等。

? 也可以使用微軟專門針對此問題設計的專用函數(shù) Ceiling 函數(shù)來處理。

只不過在處理的時候，大家要特別注意上面提到的尾差問題，要不然很容易導致出錯。

本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：明鏡在心