你知道如何用MOD函數提取身份證性別信息嗎？

06-20

提取身份證性別信息其實很簡單，就是判定身份證號碼第十七位數是單數還是雙數，單數即為男性，雙數幾位女性。那麼怎麼判定呢？本節介紹的MOD函數就是可以判定奇偶數的。

一、MOD函數的介紹

MOD函數是一個用來求餘數函數，返回兩數相除的餘數。結果的正負號與除數相同。一般不單獨使用，經常和其他函數組合起來使用。函數的格式是：

語法：MOD(number, divisor) 其中，Number是被除數。Divisor是除數。簡單理解為：MOD(被除數, 除數) ，函數求出的結果是「餘數」。比如：MOD(4,2) 結果就是0,4能被2整除，餘數是0。

注意：

①0不能作除數，所以，如果 divisor 為零，函數 MOD 返回錯誤值 #DIV/0!。

②結果的正負號與數學算式不同，該函數的結果的正負號是與除數相同的。

二、MOD函數的應用

1.隔行填充底紋

這個工作一般在財務做報表的時候數據比較多，可以給數據隔行標記以為了方便核對數據的。先看效果圖：

上圖是將單數月份所在行填充顏色，其實就是隔行填充。如何實現這個操作呢？

首先，選擇需要隔行填充的數據選中，即選中A1：E14。選擇開始-->條件格式-->新建規則。

然後，選擇「使用公式確定要設置格式的單元格」，輸入公式=MOD(ROW(),2)=0（公式的意思是找出行號除於2餘數等於0的那些行），ROW()返回的結果是行號。

最後點擊「格式（F)...」，在打開的對話框中選擇需要填充的顏色或者效果即可。

同樣的道理，如果你需要每隔兩行填充一行，就將除數改為3，將公式改為=MOD(ROW(),2)=0就可以了。

2.隔列填充底紋

按照上面的步驟，將公式改為=MOD(COLUMN(),2)=0（公式的意思是找出列號除於2餘數等於0的那些列），COLUMN()返回的結果是列號。如下圖：

最後點擊「格式（F)...」，在打開的對話框中選擇需要填充的顏色或者效果即可。效果如下圖：

同樣的道理，如果你需要每隔兩列填充一列，就將除數改為3，將公式改為"=MOD(COLUMN(),2)=0"就可以了。

3.提取身份證號碼的性別信息

提取身份證性別信息其實很簡單，就是判定身份證號碼第十七位數是單數還是雙數，單數即為男性，雙數幾位女性。看下圖：

輸入公式：=IF(MOD(RIGHT(LEFT(A1,17)),2)=1,"男","女")，公式中RIGHT(LEFT(A1,17))的意思是先用LEFT函數從左邊提取17位數，再用RIGHT函數從這17位數提取右邊第一位數，簡單的說就是提取第17位數字作為MOD函數的被除數。最後用IF函數分辨，如果MOD函數公式結果等於1，就是男性，否則就是女性。

如果身份證號碼是15位數的話，就提取最後一位，可以簡單地將上述公式中的17改為15就可以了。當然也可以簡化一下：=IF(MOD(RIGHT(A1,1),2)=1,"男","女")。意思是直接用RIGHT函數提取最後一位數作為MOD函數的被除數。

上述兩個公式還可以簡化為：

=IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女")

=IF(MOD(RIGHT(A1,1),2),"男","女")

為什麼可以這樣簡化呢？大家可以再認真學習IF函數，了解「1代表TURE」和「0代表FALSE」的原理就明白了。

4.高能應用：隔列求和和隔行求和

遇到需要對單列或者雙列進行求和的工作的時候，就可以使用MOD函數了。如下圖，如何求出每年的單月合計呢？

在B14輸入公式：=SUM(IF(MOD(ROW(B2:B13),2)=0,B2:B13))，因為這是數組公式，所以不能直接按回車結束，要同時按shift ctrl enter三鍵結束公式。

公式解釋：ROW是返回當前單元格的列數，然後用MOD函數看每列能否被2整除，然後對奇數行求和。

同樣的道理，隔列求和就是講公式中ROW函數換成COLUMN函數。F2填入公式：=SUM(IF(MOD(COLUMN(A2:E2),2)=0,A2:E2))，同時按shift ctrl enter三鍵結束公式。