《Excel数据处理与可视化》教案18——数据查询和匹配（二）

《Excel数据处理与可视化》课程教案授课题目数据查询和匹配二授课类型理实一体、掌握常用的数据查询和匹配的使用方法1教学目标、会根据应用场景选择合适的函数解决具体问题

2、常用数据查询和匹配的使用1重点、根据场景灵活选用函数2难点、根据场景灵活选用函数1一函数组合应用——提取整行和整列数据INDEX+MATCH公式“二INDEXB2:E10,MATCHA13,A2:A10,0,0，按Ctrl+Shift+Enter组合键执行计算，即可查找到指定姓名所有产品的销量B13▼I|X Vfx{=INDEXB2:E10,MATCHA13A2:A1000}/z/一A LB Ic E____F I J[i姓名鼠标键盘显示器音箱2203057373张二729260674林三11139625胡四20418779教学过程6吴五273394497章六424754738陆七354230969苏八3155261910韩九221655421112姓名；舄标键盘显示器音箱王一

20305737.

13114.I

二、利用函数在大量数据中查找指定数据OFFSET公式“二方丁讦尸尸口八」有〃,〃无‘，按201^05£1\5,0,0,500,50040Enter键执=1F8UHTIMOFFSETD5,0,口500,500,A4C，•有、无’B43输入ID所有ID单元格区域（有500巩500行）1判定ID号格区域有，元A D86254Kp234共500列5D862795K5D86254KD D86259Ko D862539KC D862789KD6D86255KD D86260KD D862590KD D862790KD7D86256KD D86261KD D862591KD D862791KDD86257KD D86262Ko D862592KD D862792KD8D86258KD D86263KD D862593KD D862793KD9D86259KD D86264Kp D862594KD D862794KD1011共500行三数值重复，如何提取前名销量3=IFCOUNTIFOFFSET$D$6,0,0,COUNTA$D:$D,公式〃有〃,〃无按键执行计算COUNTA$6:$6,A40,C”,EnterI仆=IFCOUHTIFOFFSET$D$6,

0.0,COUKTA3D:3D,COUBTA$6:16»A40/B4TTA B2____________3输入ID4[D86254KD_-S DB62795KD6D86254KD D86259KD D862589KD D862789KD178D86256KD D86261KD D862591KD D862791KD9D86257KD D86262KD D862592KD D862792KD10D86258KD D86263KD D862593KD D862793KD11D86259KD D86264KD D862594KD D862794KD四提取订货量对应的订货型号公式（购买记录表！（（购买记录表购买查“=INDEX B:B,SMALL IF!$B$2:$B$12=询（购买记录表）（购买记录表!））（）））!$B$1,ROW!$B$2:$B$12,ROWS B:B,ROW A1按组合键执行计算”:Ctrl+Shift+EnterB4{=INDEX购买记录表!B:B,SMALLIF购买记录表!$B$2:$B$12;购买查询!$B$1,ROW购买记录表!$B$2:$B$12,ROWS购买记录表!B:B,ROWA1““}A Bc.D_|1顾客姓名李四2/姓名;购买品价格3J序号，1|李四订书机542李四文件夹75李四5136五在大量信息中，快速查找哪些员工信息是不完整的）定位法1按组合键全选数据，再按组合键打开“定位”对话框单击“定位条Ctrl+A Ctrl+G件”按钮，在打开的“定位条件”对话框中，将“选择”设为“空值”项，单击“确定”按钮，将数据单元格区域内所有空单元格都选中单击“开始”一“填充颜色”，可以给空单元格填充一种醒目的颜色，然后再去补充数据公式法2在最后增加一列辅助列，在G2单元格中输入公式“二COUNTBLANKA2:F2，按键执行计算，再将公式向下填充，凡是有空单元格的数据行，公式返回的是空Enter单元格的个数然后利用筛选功能，筛选出辅助列中“不等于”的数据，也就筛选出了所有信息不完整的行六利用函数提取销量最大的整列信息OFFSET公式按JOFFSET$A$2,ROWA1-1,MATCHMAX$B$5:$K$5,$B$5:$K$5,0”,B9A BC庆E FG HI J K/产力生情况IWIA42部门泊售1部消害1郃/方白售】销售2部销售2部泊售2部植售2部镐曾3部销曾3部泊售3部部部门王管复二三/饯四濡六邹七刘八05九应一越二ttH3销售员姓名张二胡四登六陆七苏八韩九徐一项二费三4箱售量8%5/143873250093917506124735■月份1月1月2月2月2月3月3月3月3月678部门销售29=OFFSET$A$

2.ROWA1-

1.MATCHMAX$B$5$K$S,$8$5$K$

5.010部门王道历九11销售员姓名酢九12销售量184413月份3月键执行计算Enter七利用函数提取销量前名的整列信息OFFSET3最大销量相关信入公息式“二OFFSET$A$2,R0W$A1-1,MATCHLARGE$B$5:$K$5,COLUMNA1,$B$5:$K$5,0，按Enter键执行计算，B9AI=OFFSETSA$

2.ROWSAipifMATCHLARGE$B$5:$K$5,COLUMNAlD$B$5:$K$5,0A EC DE FG HIJK产品A偌1件情况1部n俏售1部销售1郃$自售1都询售2部的售2部销售2部徜售2郃销售3部洎售3部俏售3部3郃门王蕾曼二81£钱四究六邹七刎人05九S-越二饯三4销宫员姓名张二林三胡四整六陆七苏八韩九徐一项二费三5消缴896599732500939612473月份1月1月1月2月2月2月3月3月3月3月__________81_________.珀量第2销里第2网球第391部门福催2部1销售3部清传1/部门主黄国一钱四11销售员姓翎徐一第四123量；441750143813百份3月1月14八提取销量最大的月份公式“二OFFSET$A$1„MATCHMAXB2:M2,B2:M2,0，按Enter键执行计算N2▼A=OFFSET$A$1„MATCHMAXB2M2,B2:M2,0A BC DE FG H1JKL MN11月2月3月4月5月6月7月8月9月1月11月12月销量景大的月份2L产品1404409469八二172982124104582883093月3产品2421266448462263452二出37二二466300n月4产品3-4380138一七14826819921946844177929月5产品428836528319724622235二114348312月6产品588390345499443732901831251571864214月7产品634C196303二一2316346217362583142054月8产品736842567/37:5345310966936146942510110月9产品8二」35712二三268278314321293801323788月10产品9一434263273112248472400347372114651846月11产品1029C1861422942022430-17/2003211093068月12产品111992961911C91421462093763172481913728月13产品1226541134735334539415826433381694099月14_产品1330013614040C193461410412257251643086月九提取销量前名的月份3公式“二OFFSET$A$1„MATCHLARGE$B2:$M2,COLUMNA$1,$B2:$M2,0”,按组合键执行计算Ctrl+Shift+Enter1N2▼i=0FFSET$A$l MATCHLARGE$B2:$M2C0LUMNA$l$e2:$M2,0o1rA BC DE FG H1j KL MN0P11月循乜月5月6月7月8月9月10月11月12月错品第一月份箱呈第二月份错三月份|2|产品1404409萼3232982123月10月9月3产品

2421266448303462263452278.37262300□月5月7月4产品3145闲183148268199219肛77929月10月2月5产品42883652J319710224622，

2、12014312月8月2月6产品53903,巨443732901831251571864214月5月12月■7产品6340-1961以316346217362583142054月9月7月8产品7425673753106910110月2月2月9产品835712225268二731二m,38C-.132378B月10月12月10产品9263273112区二347372114651846月1月7月11产品

1029018614229420224.X2001098月7月10月12产品1119929619110914620931748191隹花18月12月9月13产品1226534739415826381699月2月12月o14产品133001136140400193ER257251643086月8月7月数据查询和匹配练习本节知识利用函数在大量数据中查找指定数据利用函数在动态单元格区域OFFSET OFFSET中查找指定数据跨表查询指定顾客的购买记录在大量信息中，快速查找哪些员工信息是不完整的利用函数提取销量最大的整OFFSET教学小结列信息利用函数提取销量前名的整列信息提取销量最大的月份提取销量前OFFSET33名的月份。