《Excel数据处理与可视化》教案19-数据查询和匹配（三）

《Excel数据处理与可视化》课程教案授课题目数据查询和匹配(三)授课类型理实一体教学目标1、掌握常用的数据查询和匹配的使用方法2、会根据应用场景选择合适的函数解决具体问题重点1、常用数据查询和匹配的使用2、根据场景灵活选用函数难点1、根据场景灵活选用函数教学过程一如何给相同姓名添加相同编号公式“=IF(B3=B2,A2,A2+1)”，Enter执行计算二、INDEX+SMALL函数组合完成一对多查找公式“=INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""”，按Ctrl+Shift+Enter组合键执行计算三IFERROR函数修正VLOOKUP函数返回错误值公式“=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$12,2,0),"")””，按Enter键执行计算四如何查找主单号对应的子单号，且主单号与子单号同行显示辅助列+VLOOKUP函数IFERROR$B$2:$C$13,2,0),"")”，按Enter键执行计算2）VLOOKUP套用动态单元格区域在E2单元格中输入公式“=IFERROR(VLOOKUP($D3&COLUMN(A$1),IF({1,0},$A$2:$A$13&COUNTIF(INDIRECT("A2:A"&ROW($2:$13)),$D3),$B$2:$B$13),2,0),"")”，按Ctrl+Shift+Enter组合键执行计算，五利用公式从地址中提取省级行政区六利用VLOOKUP+IF函数组合、VLOOKUP+CHOOSE函数组合实现逆向查询1）利用VLOOKUP+IF函数组合实现逆向查询在E2单元格中输入公式“=VLOOKUP(D2,IF({1,0},B1:B10,A1:A10),2,0)”，按Enter键执行计算，即可完成查询，如图268所示。其中，IF({1,0},B1:B10,A1:A10)构建出“姓名在前，部门在后”的新查询单元格区域。2）利用VLOOKUP+IF函数组合实现逆向查询也可在E2单元格中输入公式“=VLOOKUP(D2,CHOOSE({1,2},B1:B10,A1:A10),2,0)”，按Enter键执行计算，即可完成查询，如图270所示。其中，CHOOSE({1,2},B1:B10,A1:A10)可构建出“姓名在前，部门在后”的新查询单元格区域。七IF、VLOOKUP、LOOKUP、CHOOSE、INDEX函数都可完成等级评定在C2单元格中输入公式“=IF(B2<60,"不合格",IF(B2<70,"合格",IF(B2<85,"良好","优秀")))”，按Enter键执行计算，再将公式向下填充，即可将所有成绩转换为相应的等级。因为有4种不同的等级，所以使用IF函数的三重嵌套。IF函数实现等级评定。2）VLOOKUP函数在D2单元格中输入公式“=VLOOKUP(B2,{0,"不合格";60,"合格";70,"良好";85,"优秀"},2)”，按Enter键执行计算，再将公式向下填充，即可将所有成绩转换为相应的等级。VLOOKUP函数实现等级评定。八利用VLOOKUP函数查询同部门多个员工信息（1）添加辅助列。在“部门”前增加一列，在A3单元格中输入公式“=COUNTIF($B$3:B3,$I$1)”，按Enter键执行计算，再将公式向下填充，即在辅助列内增加编号。其中，“$B$3:B3”是一个起始位置（B3单元格）保持不变，结束位置随着公式向下填充而增加的动态单元格区域；“$I$1”是要查询的部门。要查询的部门是“市场1部”，A3单元格中的公式“=COUNTIF($B$3:B3,$I$1)”向下填充的结果是：每遇到一个“市场1部”，该公式有计算结果加1，从而将“市场1部”用不同的序号区分，而且只有“市场1部”出现的行，序号才会发生变化。（2）公式实现。在I3单元格中输入公式“=IFERROR(VLOOKUP(ROW(A1),$A:$F,COLUMN(C1),0),"")0),"")”，按Enter键执行计算，再将公式向下、向右填充，即得查询结果。九利用VLOOKUP函数查询一种产品多次的进货量公式“=IFERROR(VLOOKUP($B$12&ROW(B1),IF({1,0},$B$2:按Ctrl+Shift+Enter组合键结束作业数据查询和匹配练习教学小结本节知识：如何给相同姓名添加相同编号INDEX+SMALL函数组合完成一对多查找IFERROR函数修正VLOOKUP函数返回错误值如何查找主单号对应的子单号，且主单号与子单号同行显示利用公式从地址中提取省级行政区利用VLOOKUP+IF函数组合、VLOOKUP+CHOO