EXCEL函数应用案例详解：从基础到实战
EXCEL函数应用案例详解：从基础到实战
本文详细介绍了Excel中一些常用函数的具体应用案例，包括数学函数、循环与重复、随机函数以及综合案例。通过具体的实例讲解，帮助读者掌握这些函数的使用方法，提高工作效率。
一、数学函数
- 向下取整：
INT(number)，其中number为需要取整的数字。 - 四舍五入：
ROUND(number, num_digits)，其中num_digits表示保留的小数位数。 - 向上舍入：
Roundup(number, num_digits) - 向下舍入：
Rounddown(number, num_digits) - 取余：
MOD(number, divisor)，其中number为被除数，divisor为除数。
1、日期提取
在Excel中，日期和时间是以数字形式存储的。例如，2024年4月1日19:48对应数字格式为45383.83，其中整数部分表示日期，小数部分表示时间。要提取日期部分，可以使用取整函数INT()。
2、时间提取
时间是带小数的数值，如果需要提取时间部分，可以使用取余函数MOD()。将日期除以1，然后取余数，就可以得到时间部分。
二、循环与重复
1、循环
循环的通用公式：MOD(ROW(循环随意一个倍数)/循环的个数)
2、重复
重复序列通用公式：INT(ROW(重复次数的行号)/重复次数)
（1）简单重复
（2）循环嵌套重复
3、案例：使用循环制作工资条
（1）方式1：使用INDEX+IF+循环
- 使用
INDEX函数引用表头，返回第一行的值。 - 第一个人应该返回第二行的值。
- 第三行是空的，可以理解为在很后面的一行取一个很大的值。
- 观察
INDEX函数中“行”的参数，可以看到是有规律的。 - 相当于1-3循环N次，用到了
MOD函数，三个一循环，则可得到1，2，0的循环。 - 如果循环的结果等于1的话，行参数即取1，如果等于0，行的参数则为999，如果等于2，则需要找规律，即黄色部分的规律，为
(ROW()+1)/3+1)。 - 将
IF函数作为INDEX函数的第二个参数值即可，然后再加上&""即可规避0。 - 再使用格式刷即可制作成工资条。
附完整的公式为：INDEX(A:A,IF(MOD(ROW(),3)=1,1,IF(MOD(ROW(),3)=0,999,(ROW()+1)/3+1)))&""
（2）方式2：INDEX+CHOOSE+循环
附完整公式：INDEX(A:A,CHOOSE(MOD(ROW()-1,3)+1,1,(ROW()+1)/3+1,999))&""
三、随机函数
1、基础语法
- 返回0-1之间的随机小数：
RAND() - 返回介于数字之间的整数：
RANDBETWEEN(bottom,top)，其中top是最大值，bottom是最小值。 - 产生a-b之间的随机小数：
RAND()*(b-a)+a
（1）产生0-50的随机小数：=RAND()*50
（2）产生15-30之间的随机小数：=RAND()*15+15
【如果需要保留两位小数：ROUND(RAND()*15+15,2)】
2、案例：使用随机数制作抽奖系统
（1）在员工序号中生成1-52之间的随机整数：=RANDBETWEEN(1,52)
（2）使用VLOOKUP查找员工序号对应的员工姓名：=VLOOKUP(B7,员工名单!A:B,2,FALSE)
3、案例：使用随机数模拟数据
（1）产生日期的随机数，可以使用RANDBETWEEN，因为在Excel中日期储存的形式是整数【附：=RANDBETWEEN(J2,K2)】，格式改为日期格式即可。
（2）城市需要在6个城市中找，可以使用INDEX+RANDBETWEEN【=INDEX($G$2:$G$7,RANDBETWEEN(1,6))】
（3）集团分公司使用VLOOKUP查找即可
（4）金额：=RANDBETWEEN(20000,50000)
四、综合案例
1、 按指定数值重复
应用场景：任务分配，如将某些顾客分配到某些销售上
（1）方法一：使用累加和排序
① 通过累加的形式，计算出每一个对象最终所到的单元格的位置=SUM($B$2:B2)-ROW(A1) 【锁住前一个参数可以实现累加效果】
②向下拖拽直到序列显示到0
③因为含有公式的列不能进行排序，需要复制粘贴一列新的作为辅助列（粘贴为数字）
④筛选和排序的快捷键：Ctrl+Shift+L
⑤ 在辅助列进行升序排列，可以看到会在15这里多一个15，第二个15以上就可以有16个单元格，其余同理，32-15之间有18个单元格
⑥使用定位（Ctrl+G）→ 空值 → 确定 → 让单元格永远等于下一个单元格的值
⑤填充【Ctrl+回车（Enter）】
⑥验证：A列中这些项目名称的个数：COUNTIF(A:A,F2)
（2）方法二：VLOOKUP的模糊匹配
① 【VLOOKUP的模糊匹配可以返回精确匹配值或近似匹配值,如果找不到精确匹配值，则返回小于lookup_value 的最大数值，目标区域的第一列必须以升序排序 】
②添加辅助列【SUM($K$1:K1)】，那么如果查找的是1，因为找不到精确匹配值，则返回小于1的最大值（在这里是0）所对应的值（即GY大厦），通过累加的效果计算返回的最大值
③函数实现：VLOOKUP(ROW()-2,$I$2:$J$6,2,1)【与行号挂钩，如果不减2的话，GY大厦会只生成14个而不是16个，因为ROW（）此时对应的是2】
2、动态提取唯一值
（1）方式一：【数据】-【删除重复值】
但是该方法不能实现动态提取
（2）方法二：【IF】+【COUNTIF】+【VLOOKUP】
①建造辅助列：IF(COUNTIF($J$2:J2,J2)=1,I1+1,I1)
【该函数的内涵可以理解为IF里面，当第一个判断值（如总经总裁办）出现第一次时，返回上一个值（0）+1=1，出现2、3、4……次时（即≠第一次出现），返回上一个值。那么当第二个判断值出现第一次（销售部），则返回上一个值（1）+1=2。以此类推】
PS：如果需要在表头加入辅助列这一文字，可以运用到N（）这一函数：
N（）：将不是数值形式的值转换为数值形式。日期转换成序列值，TRUE 转换成1，其他值转换成 0。
②使用VLOOKUP查找：【VLOOKUP(ROW(A1),I:J,2,FALSE)】
PS如果需要不显示错误，则使用IFERROR：IFERROR(VLOOKUP(ROW(A1),I:J,2,FALSE),"")
3、一对多查找
应用场景：查找某一部分数据的全部信息
（1）方法一：高级筛选
（2）方法二：VLOOKUP
①构造唯一值：使用计数累计构造唯一值：【B2&COUNTIF($B$2:B2,B2)】，因为VLOOKUP查找一般只能查找唯一值，否则只会返回第一个查找的值
②在提取内容区域前也构造出唯一值：【$J$2&ROW()-4】
③使用VLOOKUP进行查找：【IFERROR(VLOOKUP($I5,$A:$F,COLUMN(B1),0),"")】
4、高级筛选
应用场景：对两个有部分重合区域的表进行分析整理
（1）筛选A有B无得记录
① MATCH函数：【MATCH(B3,$G$3:$G$11,0)】，在表B中查找表A中的值，如果没有的话，就会返回错误，这样就可以找出A有B无得记录了，这是返回的是错误值
②高级筛选中只能识别TRUE和FALSE，所以我们使用ISERROR（）函数转换为TRUE和FALSE：【ISERROR(MATCH($B3,$G$3:$G$11,0))】
③ 将该公式作为筛选的条件，然后再进行高级筛选即可
（2）筛选出B有A无得记录
①同理，在表A中找是否有含有表B中的值：【ISERROR(MATCH($G3,$B$3:$B$11,0))】
②使用高级筛选
（3）筛选出A、B共有的记录
①恰好相反，因为MATCH返回的是查找值的相对位置，返回的是一个数值，即只要能查找到值，就可以返回一个数字，而我们希望得到的就是数字，因此应该用ISNUMBER（）来判断：【ISNUMBER(MATCH($B3,$G$3:$G$11,0))】
②再使用高级筛选即可