隔壁小王、小爽和我是好朋友，我們經(jīng)常一起討論 Excel 相關(guān)的問題。

某一天，小爽指著桌上的蘋果，問我和小王：

如果把桌子上的 12 個蘋果分給 5 個小朋友，還剩下幾個呢？

小王立馬說：不就是求余嗎！一個 MOD 函數(shù)就可以搞定！公式如下。

• MOD(12,5) =2

MOD 函數(shù)看起來很基礎(chǔ)很簡單，其實深藏不露！

用 MOD 函數(shù)，還可以解決這么多常見問題：

? 從日期中提取時間

? 計算跨天工作時長

? 根據(jù)身份證號判斷性別

? 根據(jù)日期判斷周

? 根據(jù)條件填充顏色

……

是不是超想學(xué)？

在正式開學(xué)之前，我們先來看看 MOD 函數(shù)的語法規(guī)則。

畢竟知己知彼百戰(zhàn)百勝！

MOD 函數(shù)是一個求余函數(shù)，它可以返回兩數(shù)相除的余數(shù)。

不過它跟數(shù)學(xué)意義上的余數(shù)有所不同，數(shù)學(xué)意義上的余數(shù)是一個非負(fù)數(shù)，而這個函數(shù)求出來的余數(shù)可以是負(fù)數(shù)，并且符號與除數(shù)的符號相同。

MOD 函數(shù)的語法：

• =MOD(number,divisor)

也就是：=MOD(被除數(shù),除數(shù))

注：除數(shù)不能為 0，至于為什么……問你的小學(xué)老師去！

用 MOD 函數(shù)計算時間

▌從日期中提取時間

下圖中，我們需要提取 B 列日期中的時間。

在 C3 單元格輸入公式：

• =MOD(B3,1)

  
  

然后下拉填充，就搞定啦！

在 Excel 中日期的本質(zhì)是數(shù)值。日期為整數(shù)部分，時間為小數(shù)部分。

比如在單元格輸入「2020/4/20 12:00」，把單元格格式改為常規(guī)，就能得到數(shù)字 43941.5。

43941 是日期部分，0.5 是時間部分。

當(dāng)我們用 MOD 函數(shù)求余數(shù)：

• =MOD(43941.5,1)

得到結(jié)果為 0.5，把該單元格設(shè)置為時間格式，就得到 12:00，也就是時間部分。

敲重點：

• 公式：=MOD(日期時間,1)

用 MOD 函數(shù)第二參數(shù)為 1 時，求得的日期時間的余數(shù)，結(jié)果就是日期中的時間。

?

▌計算跨天時長

如下圖，根據(jù)上下班時間，計算工作了多少小時。

在 D3 單元格輸入公式：

• =MOD(C3-B3,1)*24

  
  

?

?

當(dāng)天的 23 點~24 點（1 小時）+ 次日 0 點~9 點（9 小時）=10 小時。所以結(jié)果為 10 小時。

我們來看看用 MOD 函數(shù)是怎么來的。

前面我們得知：MOD 函數(shù)可以得到日期時間中的時間，那它同樣可以得到時間間隔中的時間。

公式為：

• =MOD(日期時間,1)
  

?

用 MOD 函數(shù)公式：

• =MOD(C3-B3,1)*24
  

=MOD(C3-B3,1)求的是兩者之間相差的時間，是一個小數(shù)。

因為 1 天=24 小時，所以我們需要在后面乘以 24，將其轉(zhuǎn)換為小時。

用 MOD 函數(shù)判斷性別

?

如下圖，怎么根據(jù)身份證號得知性別。

在 C3 單元格輸入公式：

• =IF(MOD(MID(B3,17,1),2),"男","女")

然后下拉填充。

身份證第 17 位數(shù)字為奇數(shù)表示為男，偶數(shù)表示為女。

小解析：

先用 MID 函數(shù)提取表示性別的第 17 位數(shù)字；

再用 MOD 函數(shù)判斷數(shù)字的奇偶：

數(shù)字除以 2 得到余數(shù)是 0，則為偶數(shù)；如果余數(shù)是 1，則為奇數(shù)；

最后用 IF 函數(shù)判斷若為奇數(shù)，則為男，否則為女。

用 MOD 函數(shù)判斷周末

判斷下圖中 B 列的日期是否是周末，若是周六日則返回「是」，否則返回空字符。

在 C3 單元格輸入公式：

• =IF(MOD(B3,7)<2,"是","")

然后下拉填充。

首先我們列出一組連續(xù)日期，如下圖 B 列；

然后用 MOD 函數(shù)，日期除以 7 得到余數(shù)是一組 0 到 6 循環(huán)的整數(shù)，如下圖 C 列。

把 B 列日期格式自定義設(shè)置為「aaa」，顯示日期星期幾，如 D 列。

可以看出，數(shù)字 0-6 依次對應(yīng)周六、周日、周一、周二、周三、周四、周五；

• =IF(MOD(B3,7)<2,"是","")
  

用 IF 函數(shù)判斷循環(huán)數(shù)值是否小于 2，若小于 2（0 和 1）則是周末，否則返回空字符。

用 MOD 函數(shù)填充顏色

這個案例有點難，會涉及到數(shù)組公式，大家作為一個了解即可。

如下圖，當(dāng)我們美化表格時，可能需要把相同部門隔行用顏色填充：

如果一個個手動去設(shè)置顏色，只有幾個部門倒還好。

如果部門多、表格多，這樣操作很低效。

其實不用這么繁瑣，請看下面操作！

操作重點是條件格式公式：

• =MOD(SUM(N($B$2:$B2<>$B$3:$B3)),2)

• N($B$2:$B2<>$B$3:$B3))

小解析：

? 判斷部門所在行的下一行與上行是否相同，如果不同就累計 1 次；N 函數(shù)將邏輯值 True 或 False 轉(zhuǎn)化成數(shù)值 1 或 0；

? 再用 SUM 函數(shù)累加求和；

? 最后用 Mod 函數(shù)對累計的和判斷奇偶，若是奇數(shù)則填充顏色。

最后我轉(zhuǎn)過頭問小王他們：你們現(xiàn)在還認(rèn)為 MOD 函數(shù)很簡單嘛？

小王驚奇地說道：沒想到小小的 MOD，看起來很簡單，實際上真的不簡單??！

?當(dāng) MOD 函數(shù)的除數(shù)是 1 時，可以在日期和時間同時存在的時候提取時間，可以計算跨日時間差；

?當(dāng) MOD 函數(shù)的除數(shù)是 2 時，可以判斷數(shù)字奇偶；

?當(dāng) MOD 函數(shù)的除數(shù)是 7 時，能計算判斷日期的星期。

當(dāng)然 MOD 函數(shù)除了以上作用，它的用途遠(yuǎn)遠(yuǎn)不止這些~

想真正學(xué)好 Excel，掌握高效學(xué)習(xí)方法，小 E 建議大家入手《3天Excel集訓(xùn)營》課程，系統(tǒng)學(xué)習(xí)，提升自己！

一課搞定表格排版、數(shù)據(jù)整理、可視化圖表……

課程有直播和錄播，完整觀看老師操作過程，不用擔(dān)心按鈕找不到，有偏差；

遇到問題，有專屬學(xué)員群解答，再也不用一個人苦苦撓破頭！

?

小 E 還幫大家申請了特別福利！

原價?99 元?的課程

現(xiàn)在僅需 1 元！

買課還送《35個函數(shù)使用手冊》

在線學(xué)員答疑交流群

……

?

提升自己就現(xiàn)在

馬上掃描下方二維碼

給自己的職場能力充值吧！

??????

??

*廣告