尧图网站建设 
一、数学函数向下取整 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使用INDEXIF循环1、使用INDEX引用表头INDEX函数返回的是行和列交叉处的值故应该是返回第一行的值2、第一个人应该返回的是第二行的值3、第三行是空着的但是也可以理解为在很后面的一行取了一个很大的值4、观察一下INDEX函数中“行”的参数可以看到是有规律的5、相当于1-3循环N次那那应该用到了MOD函数三个一循环则可得到120的循环。6、如果循环的结果等于1的话行参数即取1如果等于0行的参数则为999如果等于2则需要找规律即黄色部分的规律为(ROW()1)/31)7、将IF函数作为INDEX函数的第二个参数值即可然后再加上“”即可规避08、再使用格式刷即可制作成工资条附完整的公式为INDEX(A:A,IF(MOD(ROW(),3)1,1,IF(MOD(ROW(),3)0,999,(ROW()1)/31)))2方式2INDEXCHOOSE循环附完整公式INDEX(A:A,CHOOSE(MOD(ROW()-1,3)1,1,(ROW()1)/31,999))三、随机函数1、基础语法返回0-1之间的随机小数 RAND()返回介于数字之间的整数 RANDBETWEEN(bottom,top) top 最大值 bottom 最小值产生a-b之间的随机小数: RAND()*(b-a)a1产生0-50的随机小数RAND()*502产生15-30之间的随机小数RAND()*1515【如果需要保留两位小数ROUND(RAND()*1515,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个城市中找可以使用INDEXRANDBETWEEN【INDEX($G$2:$G$7,RANDBETWEEN(1,6))】3集团分公司使用VLOOKUP查找即可4金额RANDBETWEEN(20000,50000)四、综合案例1、 按指定数值重复应用场景任务分配如将某些顾客分配到某些销售上1方法一使用累加和排序① 通过累加的形式计算出每一个对象最终所到的单元格的位置SUM($B$2:B2)-ROW(A1) 【锁住前一个参数可以实现累加效果】②向下拖拽直到序列显示到0③因为含有公式的列不能进行排序需要复制粘贴一列新的作为辅助列粘贴为数字④筛选和排序的快捷键CtrlShiftL⑤ 在辅助列进行升序排列可以看到会在15这里多一个15第二个15以上就可以有16个单元格其余同理32-15之间有18个单元格⑥使用定位CtrlG→ 空值 → 确定 → 让单元格永远等于下一个单元格的值⑤填充【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,I11,I1)【该函数的内涵可以理解为IF里面当第一个判断值如总经总裁办出现第一次时返回上一个值011出现2、3、4……次时即≠第一次出现返回上一个值。那么当第二个判断值出现第一次销售部则返回上一个值112。以此类推】PS如果需要在表头加入辅助列这一文字可以运用到N这一函数N将不是数值形式的值转换为数值形式。日期转换成序列值TRUE 转换成1其他值转换成 0。②使用VLOOKUP查找【VLOOKUP(ROW(A1),I:J,2,FALSE)】PS如果需要不显示错误则使用IFERRORIFERROR(VLOOKUP(ROW(A1),I:J,2,FALSE),)3、一对多查找应用场景查找某一部分数据的全部信息1方法一高级筛选2方法二VLOOKUP①构造唯一值使用计数累计构造唯一值【B2COUNTIF($B$2:B2,B2)】因为VLOOKUP查找一般只能查找唯一值否则只会返回第一个查找的值②在提取内容区域前也构造出唯一值【$J$2ROW()-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))】②再使用高级筛选即可 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使用INDEXIF循环1、使用INDEX引用表头INDEX函数返回的是行和列交叉处的值故应该是返回第一行的值2、第一个人应该返回的是第二行的值3、第三行是空着的但是也可以理解为在很后面的一行取了一个很大的值4、观察一下INDEX函数中“行”的参数可以看到是有规律的5、相当于1-3循环N次那那应该用到了MOD函数三个一循环则可得到120的循环。6、如果循环的结果等于1的话行参数即取1如果等于0行的参数则为999如果等于2则需要找规律即黄色部分的规律为(ROW()1)/31)7、将IF函数作为INDEX函数的第二个参数值即可然后再加上“”即可规避08、再使用格式刷即可制作成工资条附完整的公式为INDEX(A:A,IF(MOD(ROW(),3)1,1,IF(MOD(ROW(),3)0,999,(ROW()1)/31)))2方式2INDEXCHOOSE循环附完整公式INDEX(A:A,CHOOSE(MOD(ROW()-1,3)1,1,(ROW()1)/31,999))三、随机函数1、基础语法返回0-1之间的随机小数 RAND()返回介于数字之间的整数 RANDBETWEEN(bottom,top) top 最大值 bottom 最小值产生a-b之间的随机小数: RAND()*(b-a)a1产生0-50的随机小数RAND()*502产生15-30之间的随机小数RAND()*1515【如果需要保留两位小数ROUND(RAND()*1515,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个城市中找可以使用INDEXRANDBETWEEN【INDEX($G$2:$G$7,RANDBETWEEN(1,6))】3集团分公司使用VLOOKUP查找即可4金额RANDBETWEEN(20000,50000)四、综合案例1、 按指定数值重复应用场景任务分配如将某些顾客分配到某些销售上1方法一使用累加和排序① 通过累加的形式计算出每一个对象最终所到的单元格的位置SUM($B$2:B2)-ROW(A1) 【锁住前一个参数可以实现累加效果】②向下拖拽直到序列显示到0③因为含有公式的列不能进行排序需要复制粘贴一列新的作为辅助列粘贴为数字④筛选和排序的快捷键CtrlShiftL⑤ 在辅助列进行升序排列可以看到会在15这里多一个15第二个15以上就可以有16个单元格其余同理32-15之间有18个单元格⑥使用定位CtrlG→ 空值 → 确定 → 让单元格永远等于下一个单元格的值⑤填充【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,I11,I1)【该函数的内涵可以理解为IF里面当第一个判断值如总经总裁办出现第一次时返回上一个值011出现2、3、4……次时即≠第一次出现返回上一个值。那么当第二个判断值出现第一次销售部则返回上一个值112。以此类推】PS如果需要在表头加入辅助列这一文字可以运用到N这一函数N将不是数值形式的值转换为数值形式。日期转换成序列值TRUE 转换成1其他值转换成 0。②使用VLOOKUP查找【VLOOKUP(ROW(A1),I:J,2,FALSE)】PS如果需要不显示错误则使用IFERRORIFERROR(VLOOKUP(ROW(A1),I:J,2,FALSE),)3、一对多查找应用场景查找某一部分数据的全部信息1方法一高级筛选2方法二VLOOKUP①构造唯一值使用计数累计构造唯一值【B2COUNTIF($B$2:B2,B2)】因为VLOOKUP查找一般只能查找唯一值否则只会返回第一个查找的值②在提取内容区域前也构造出唯一值【$J$2ROW()-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))】②再使用高级筛选即可