點擊關注 【秋葉 Excel】

發送【7】

免費領 1000+ 篇 Excel 精選教程！

作者：趙驕陽

編輯：竺蘭

大家好，這里是秋葉編輯部~

問你一個問題：

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

這，就是求余！在 Excel 里，一個 MOD 函數就可以搞定！公式如下：

MOD(12,5) =2

MOD 函數看起來很基礎很簡單，其實深藏不露！

用它還可以解決這么多常見問題：

?從日期中提取時間

?計算跨天工作時長

?根據身份證號判斷性別

?根據日期判斷周

?根據條件填充顏色

是不是超想學？

在正式學習之前，我們先來看看 MOD 函數的語法規則。

畢竟知己知彼百戰百勝！

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

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

MOD 函數的語法：

=MOD(number,divisor)

也就是：

=MOD(被除數,除數)

注：除數不能為 0，至于為什么……問你的小學老師去！

什么，你想系統提升 Excel 技能？

那么千萬別錯過秋葉專為 0 基礎小白打造的《Excel 3 天集訓營》課程！

現在掃碼報名，你還能免費領取函數手冊+快捷鍵大全+110 套 Excel 模板！

別再猶豫了，趕緊掃碼

搶占免費學習名額

助力自己在職場中脫穎而出吧！

用 MOD 函數計算時間

▌從日期中提取時間

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

在 C3 單元格輸入公式：

=MOD(B3,1)

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

在 Excel 中日期的本質是數值。日期為整數部分，時間為小數部分。

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

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

當我們用 MOD 函數求余數：

=MOD(43941.5,1)

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

敲重點：

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

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

▌計算跨天時長

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

在 D3 單元格輸入公式：

=MOD(C3-B3,1)*24

當天的 23 點~24 點（1 小時）+ 次日 0 點~9 點（9 小時）=10 小時。所以結果為 10 小時。

我們來看看用 MOD 函數是怎么來的。

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

公式為：

=MOD(日期時間,1)

用 MOD 函數公式：

=MOD(C3-B3,1)*24

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

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

用 MOD 函數判斷性別

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

在 C3 單元格輸入公式：

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

然后下拉填充。

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

小解析：

先用 MID 函數提取表示性別的第 17 位數字； 再用 MOD 函數判斷數字的奇偶：數字除以 2 得到余數是 0，則為偶數；如果余數是 1，則為奇數； 最后用 IF 函數判斷若為奇數，則為男，否則為女。

用 MOD 函數判斷周末

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

在 C3 單元格輸入公式：

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

然后下拉填充。

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

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

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

可以看出，數字 0-6 依次對應周六、周日、周一、周二、周三、周四、周五；

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

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

用 MOD 函數填充顏色

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

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

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

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

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

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

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

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

小解析：

? 判斷部門所在行的下一行與上行是否相同，如果不同就累計 1 次；N 函數將邏輯值 True 或 False 轉化成數值 1 或 0； ? 再用 SUM 函數累加求和； ? 最后用 Mod 函數對累計的和判斷奇偶，若是奇數則填充顏色。

最后我轉過頭問小王他們：你們現在還認為 MOD 函數很簡單嘛？

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

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

?當 MOD 函數的除數是 2時，可以判斷數字奇偶；

?當 MOD 函數的除數是 7時，能計算判斷日期的星期。

你學會了嗎？

只要找到正確的方法，即使是零基礎的小白，也可以掌握 Excel！

如果你想利用空閑時間提升 Excel 技能，讓工作更高效，那么一定要加入秋葉 Excel 3 天集訓營~

秋葉金牌講師 @拉登Dony 手把手教你 Excel，現在加入，還能獲得秋葉學員實戰用的10 套精選表格模板：

先給大家看看部分截圖 ↓

名額有限，學到就是賺到！

秋葉 Excel 3 天集訓營

現在僅需 0 元！

長按下圖掃碼免費報名

▲ 現在報名，即可免費領取超多福利資料

點擊關注【秋葉 Excel】

免費獲取更多 Excel 干貨、模板