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+循环

1、使用INDEX引用表头（INDEX函数返回的是行和列交叉处的值），故应该是返回第一行的值

2、第一个人应该返回的是第二行的值

3、第三行是空着的，但是也可以理解为在很后面的一行取了一个很大的值

4、观察一下INDEX函数中“行”的参数，可以看到是有规律的

5、相当于1-3循环N次，那那应该用到了MOD函数，三个一循环，则可得到1，2，0的循环。

6、如果循环的结果等于1的话，行参数即取1，如果

等于0，行的参数则为999，如果等于2，则需要找规律，即黄色部分的规律，为(ROW()+1)/3+1)

7、将IF函数作为INDEX函数的第二个参数值即可，然后再加上&“”即可规避0

8、再使用格式刷即可制作成工资条

附完整的公式为：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))】

②再使用高级筛选即可