|
提取身份证性别信息其实很简单，就是判定身份证号码第十七位数是单数还是双数，单数即为男性，双数几位女性。那么怎么判定呢？本节介绍的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三键结束公式。
|
|
|
