Excel中MOD函数5种高阶用法

MOD函数介绍

功能：函数的主要作用是返回两数相除后的余数。结果的正负号与除数相同。

语法：=MOD(数值,除数)

基本用法：

要计算13 除以3的余数，可以使用以下公式：

=MOD(13,3)

该公式将返回1，因为13 除以3的商是4，余数是1。

进阶用法一：生成循环序列

如下图所示，我们要生成1-4的循环序列号。

只需在目标单元格中输入公式：

=MOD(ROW(A4),4)+1

然后点击回车，下拉填充公式即可

解读：

如果我们想生成1-5的循环序列，我们只需要将公式中的4改为5就可以了

=MOD(ROW(A5),5)+1

以此类推，我们可以生成任意的循环序列数。

进阶用法二：判断指定日期是否为周末/工作日

如下图所示，下面是值班名单和值班日期，我们要根据值班日期计算出是否是周末/工作日。

在目标单元格中输入公式：

=IF(MOD(D2,7)<2,“周末”,“工作日”)

点击回车，下拉数据即可

解读：

①MOD函数：MOD(D2,7)是计算D2单元格中的日期与7的余数。在Excel中，日期是以数字形式存储的，其中1代表1900年1月1日，2代表1900年1月2日，以此类推。所以，这个MOD函数实际上是在计算D2中的日期是这一周的第几天。

②IF函数：

IF(MOD(D2,7)<2,“周末”,“工作日”)是一个条件判断函数。

它首先检查MOD函数的结果是否小于2。如果小于2，意味着D2中的日期是周六（余数为0）或周日（余数为1），这时IF函数返回“周末”。如果MOD函数的结果不小于2，即日期是周一到周五，IF函数返回“工作日”。

当然，有的小伙伴可能要说了，我们公司只休周天，周六也算工作日，那么公式直接修改成：

=IF(MOD(D2,7)=1,“周末”,“工作日”)

然后点击回车，下拉数据即可

解读：

因为MOD(D2,7)日期与7的余数，周日对应的余数为1。

进阶用法三：隔行标记颜色

如下图所示，我们想对表格数据进行隔行标记颜色

方法：

第一步：首先选择要标记颜色的数据区域→然后点击【开始】-【条件格式】-【新建规则】调出新建格式规则”对话框

第二步、在弹出的“新建格式规则”对话框中，规则类型选择【使用公式确定要设置格式的单元格】，在设置格式里面输入公式：

=MOD(ROW(A2),2)=0

接着点击【格式】，在弹出的对话框中选择“图案”，选择黄色，点击确定即可，如下图所示

解读：

上面公式的含义就是如果为偶数行,符合条件标记成黄色，否则不标记颜色。

进阶用法四：隔行求和

如下图所示，左侧表格是商品1-3月对应的销售额和销售成本，我们要计算所有商品1-3月份的总销售额。

直接在目标单元格中输入公式：

=SUMPRODUCT((MOD(ROW(C2:E11),2)=0)*C2:E11)

然后点击回车即可

解读：

上面的公式其实就是使用SUMPRODUCT函数来计算一个区域（C2:E11）中偶数行的数值之和。

①ROW(C2:E11): 这个部分会生成一个数组，包含C2:E11区域内每个单元格的行号。例如，如果C2:E11是10行3列的区域，这个数组将是{2; 3; 4; 5; 6; 7; 8; 9; 10; 11}。

②MOD(ROW(C2:E11), 2)=0: MOD函数用于计算上述行号数组中每个数字除以2的余数。偶数行的行号除以2的余数将是0，也就是我们要求的销售额行，会返回一个逻辑值TRUE（或1）；否则奇数行返回的元素是FALSE（或0）。

③最后SUMPRODUCT函数将上一步骤得到的结果数组与C2:E11中的数值相乘进行求和，得到所有偶数行数值的总和，也就是销售额行的总和。

当然，如果我们想求销售成本行的总和，可以改成下面的公式：

=SUMPRODUCT((MOD(ROW(C2:E11),2)=1)*C2:E11)

进阶用法五：根据身份证号提取性别信息

如下图所示，需要根据B列身份证号码提取对应的性别信息。

直接在目标单元格中输入公式：

=IF(MOD(MID(B2,17,1),2),“男”,“女”)

然后点击回车，下拉填充数据即可