今天教大家一個(gè)非常接地氣的案例，根據(jù)員工每天考勤記錄中的實(shí)際上下班時(shí)間，計(jì)算加班或缺勤績(jī)效。

今天的案例，會(huì)用到兩個(gè)全新的函數(shù) CEILING 和 FLOOR，來吧，小板凳搬好，開始上課。

案例：

某公司實(shí)行人性化的彈性上下班制度，規(guī)則如下：

• 正常通勤時(shí)間為 9 點(diǎn)上班，18 點(diǎn)下班，如果彈性工作，總時(shí)長(zhǎng)與此相當(dāng)即可；

• 上下班時(shí)間都以半小時(shí)為單位四舍五入；

• 上班時(shí)間往后取整半小時(shí)；

• 下班時(shí)間向前取整半小時(shí)
  

請(qǐng)根據(jù)下圖 1 中的上下班時(shí)間，計(jì)算出各員工的加班或缺勤分鐘數(shù)，效果如下圖 2 所示。

解決方案：

1. 在 D2 單元格中輸入以下公式：

=CEILING(B2,1/48)

此時(shí) D 列的格式是數(shù)字，我們可以用格式刷將 B 或 C 列的時(shí)間格式復(fù)制到 D 列。

2. 下拉復(fù)制公式

按照以往慣例，此處應(yīng)有公式釋義，但今天我先把 E 列公式寫完，再一起解釋，因?yàn)?D、E 兩列的公式有共性。

3. 在 E2 單元格內(nèi)輸入以下公式：

=FLOOR(C2,1/48)

4. 用格式刷將 E2 單元格的格式變成時(shí)間

5. 下拉拖動(dòng)復(fù)制公式

D、E 兩列分別使用了 ceiling 和 floor 函數(shù)，以下是這兩個(gè)函數(shù)的說明。

CEILING 函數(shù)說明：

作用：

• 將參數(shù) number 沿絕對(duì)值增大的方向，向上舍入為最接近的 significance 倍數(shù)。

語法：

• CEILING(number, significance)

參數(shù)：

• number：必需，起始日期

• significance：必需，要舍入到的倍數(shù)

說明：

• 如果任何一個(gè)參數(shù)是非數(shù)值型，則 CEILING 返回 #VALUE! 錯(cuò)誤值；

• 如果 number 為正且 significance 為負(fù)，則返回 #NUM!  錯(cuò)誤值；
  

• 如果 number 和 significance 都為負(fù)，則對(duì)值按遠(yuǎn)離 0 的方向進(jìn)行向下舍入；

• 如果 number 為負(fù)，significance 為正，則對(duì)值按朝向 0 的方向進(jìn)行向上舍入。

FLOOR 函數(shù)說明：

作用：

• 將參數(shù) number 沿絕對(duì)值減小的方向，向下舍入為最接近的 significance 倍數(shù)。

語法：

• FLOOR(number, significance)
  

floor 函數(shù)除了舍入方向跟 ceiling 正好相反，其他的注意點(diǎn)都一樣。

現(xiàn)在我們?cè)倩剡^頭來解釋剛才的公式。

D 列公式釋義：

• CEILING(B2,1/48)：

• 將 B2 單元格的內(nèi)容，以 1/48 的倍數(shù)向上舍入；

• 為什么是 1/48 呢？因?yàn)闀r(shí)間每 24 小時(shí)循環(huán)一次，如果以半小時(shí)為單位的話，就是每 48 個(gè)半小時(shí)循環(huán)一次
  

E 列公式釋義：

• FLOOR(C2,1/48)：將 C2 單元格的內(nèi)容，以 1/48 的倍數(shù)向下舍入

6. 在 F2 單元格中輸入以下公式：

=(E2-D2)*24*60-9*60

公式釋義：

• (E2-D2)：通過兩列的差計(jì)算出上班總時(shí)長(zhǎng)；

• (...)*24*60：將以上時(shí)長(zhǎng)換算為分鐘；

• 9*60：正常的通勤時(shí)間為 9 小時(shí)，*60 后換算為分鐘；

• 將兩者相減即可得出以分鐘為單位的加班或缺勤時(shí)間

現(xiàn)在 F2 單元格顯示為時(shí)間格式，需要將它轉(zhuǎn)換為數(shù)值。

7. 選中 F2 單元格 --> 按 Ctrl+1 --> 在彈出的對(duì)話框中選擇“數(shù)字”選項(xiàng)卡 --> 選擇“數(shù)值”--> 按以下方式設(shè)置 --> 點(diǎn)擊“確定”：

• 小數(shù)位數(shù)：設(shè)置為“0”

• 取消勾選“使用千位分隔符”

• 選擇所需的負(fù)數(shù)格式
  

8. 下拉復(fù)制公式

這就是最終所需的結(jié)果。