《Excel数据处理与可视化》教案14-查找与引用函数 (一)

《Excel数据处理与可视化》课程教案授课题目查找与引用函数（一）授课类型理实一体教学目标1、掌握常用的查找与引用函数的使用方法2、会根据应用场景选择合适的函数解决具体问题重点1、常用查找与引用函数的使用2、根据场景灵活选用函数难点1、根据场景灵活选用函数教学过程一VLOOKUP函数应用之基础—基本查找VLOOKUP函数功能：依据给定的查阅值，在一定的数据单元格区域中，返回与查阅值对应的想要查找的值。中文语法：=VLOOKUP(查阅值,包含查阅值和返回值的查找单元格区域,查找单元格区域中返回值的列号,精确查找或近似查找)。●

查阅值就是指定的查找关键值。在本示例中，查阅值是F3单元格“林三”，因为要在“姓名”一列中查找“林三”的得分，“林三”就是查找的关键值。●

包含查阅值和返回值的查找单元格区域。一定要记住，查阅值应该始终位于查找单元格区域的第1列，单元格“林三”所在的“姓名”B列就是该单元格区域的首列，而且该单元格区域还包括返回值“得分”所在的D列。●

查找单元格区域中返回值的列号。在本示例中，查找单元格区域是B2:D15，首列“姓名”是第1列，返回值“得分”是第3列，所以列号是“3”。●

精确查找或近似查找。如果要精确查找返回值，则指定FALSE或0；如果要近似查找返回值，则指定TRUE或1；如果该参数省略，则默认为近似匹配。本示例中指定的是0，即为精确查找。在公式中，第2个参数“查找单元格区域”使用的是绝对引用“$B$2:$D$15”。绝对引用的作用是：当公式填充到其他行、列时，该单元格区域不变。在本示例中，查找完“林三”的得分，将公式向下填充，再去查找“陆七”的得分，查找单元格区域始终不能改变，应该是包含所有姓名与得分的B2:D15单元格区域，所以该单元格区域是绝对引用。二、VLOOKUP函数应用之小成——多行多列查找公式“=VLOOKUP($B18,$C$2:$G$15,COLUMN(B1),0)”三、VLOOKUP函数应用之提升——区间查找、等级评定、模糊查找公式““=VLOOKUP(B2,$E$3:$F$6,2)””公式“=VLOOKUP(B2,{0,"不合格";60,"合格";70,"良好";85,"优秀"},2)”四、VLOOKUP函数应用之进阶——多条件查找、逆向查找公式“=VLOOKUP(D2,IF({1,0},B1:B10,A1:A10),2,0)”五、(INDIRECT("b2:b"&ROW($2:$9)),$B$12),$C$2:$C$9),2,0)”，按Ctrl+Shift+Enter组合键执行计算，再将公式向下填充，即得多次进货数量六、HLOOKUP函数实现行查找功能：最常用的查找和引用函数，依据给定的查阅值，在一定的查找单元格区域中，返回与查阅值对应的想要查找的值。查找单元格区域中查找值、返回值都是行分布的。中文语法：=HLOOKUP(查阅值,包含查阅值和返回值的查找单元格区域,查找单元格区域中返回值的行号,精确查找或近似查找)。●

查阅值：指定的查找关键值。在本示例中，查阅值是B2单元格“20”，要在“采购数量”列中查找“20”对应的折扣，“20”就是查找的关键值。●

单元格单元格单元格单元格单元格●

查找单元格区域中返回值的行号：在本示例中，对于F2:I3查找单元格区域，“采购数量”是第1行，返回值“折扣”是第2行，所以行号是“2”。●

精确查找或近似查找：如果要精确查找返回值，则指定FALSE或0；如果要近似查找返回值，则指定TRUE或1；如果该参数省略，则默认为近似匹配。在本示例中，比“20”小的值且最接近“20”的是0，所以返回0对应的区间值为“0%”；比“225”小的值且最接近“225”的是200，所以返回200对应的区间值为“8%”。区间查找有一个最重要的注意事项：查找单元格区域的区间值必须是从小到大排列，否则查找不到正确结果。在本示例中，区间值0、100、200、300就是从小到大依次排列的。七、LOOKUP函数查询的10种方法公式“=LOOKUP(1,0/(C2:C12=G2),E2:E12)”LOOKUP函数还有以下9种查询方法1）逆向查找2）多条件查找3）查找最后一条记录4）区间查找5）模糊查找6）查找最后一次进货日期7）关键字提取8）拆分单元格9）合并单元格的查询八、LOOKUP函数典型应用——根据抽样标准计算抽样数量公式“=LOOKUP(H3,$B$3:$B$15,$E$3:$E$15)”九、LOOKUP函数典型应用——合并单元格的拆分与查找公式“F3”作业查找与引用函数练习教学小结本节知识：VLOOKUP函数应用之基础—基本查找VLOOKUP函数应用之小成—多行多列查找VLOOKUP函数应用之提升—区间查找、等级评定、模糊查找VLOOKUP函数应用之进阶—多条件查找