《Excel數(shù)據(jù)處理與可視化》教案19-數(shù)據(jù)查詢和匹配（三）

《Excel數(shù)據(jù)處理與可視化》課程教案授課題目數(shù)據(jù)查詢和匹配(三)授課類型理實一體教學(xué)目標(biāo)1、掌握常用的數(shù)據(jù)查詢和匹配的使用方法2、會根據(jù)應(yīng)用場景選擇合適的函數(shù)解決具體問題重點1、常用數(shù)據(jù)查詢和匹配的使用2、根據(jù)場景靈活選用函數(shù)難點1、根據(jù)場景靈活選用函數(shù)教學(xué)過程一如何給相同姓名添加相同編號公式“=IF(B3=B2,A2,A2+1)”，Enter執(zhí)行計算二、INDEX+SMALL函數(shù)組合完成一對多查找公式“=INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算三IFERROR函數(shù)修正VLOOKUP函數(shù)返回錯誤值公式“=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$12,2,0),"")””，按Enter鍵執(zhí)行計算四如何查找主單號對應(yīng)的子單號，且主單號與子單號同行顯示輔助列+VLOOKUP函數(shù)IFERROR$B$2:$C$13,2,0),"")”，按Enter鍵執(zhí)行計算2）VLOOKUP套用動態(tài)單元格區(qū)域在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組合鍵執(zhí)行計算，五利用公式從地址中提取省級行政區(qū)六利用VLOOKUP+IF函數(shù)組合、VLOOKUP+CHOOSE函數(shù)組合實現(xiàn)逆向查詢1）利用VLOOKUP+IF函數(shù)組合實現(xiàn)逆向查詢在E2單元格中輸入公式“=VLOOKUP(D2,IF({1,0},B1:B10,A1:A10),2,0)”，按Enter鍵執(zhí)行計算，即可完成查詢，如圖268所示。其中，IF({1,0},B1:B10,A1:A10)構(gòu)建出“姓名在前，部門在后”的新查詢單元格區(qū)域。2）利用VLOOKUP+IF函數(shù)組合實現(xiàn)逆向查詢也可在E2單元格中輸入公式“=VLOOKUP(D2,CHOOSE({1,2},B1:B10,A1:A10),2,0)”，按Enter鍵執(zhí)行計算，即可完成查詢，如圖270所示。其中，CHOOSE({1,2},B1:B10,A1:A10)可構(gòu)建出“姓名在前，部門在后”的新查詢單元格區(qū)域。七IF、VLOOKUP、LOOKUP、CHOOSE、INDEX函數(shù)都可完成等級評定在C2單元格中輸入公式“=IF(B2<60,"不合格",IF(B2<70,"合格",IF(B2<85,"良好","優(yōu)秀")))”，按Enter鍵執(zhí)行計算，再將公式向下填充，即可將所有成績轉(zhuǎn)換為相應(yīng)的等級。因為有4種不同的等級，所以使用IF函數(shù)的三重嵌套。IF函數(shù)實現(xiàn)等級評定。2）VLOOKUP函數(shù)在D2單元格中輸入公式“=VLOOKUP(B2,{0,"不合格";60,"合格";70,"良好";85,"優(yōu)秀"},2)”，按Enter鍵執(zhí)行計算，再將公式向下填充，即可將所有成績轉(zhuǎn)換為相應(yīng)的等級。VLOOKUP函數(shù)實現(xiàn)等級評定。八利用VLOOKUP函數(shù)查詢同部門多個員工信息（1）添加輔助列。在“部門”前增加一列，在A3單元格中輸入公式“=COUNTIF($B$3:B3,$I$1)”，按Enter鍵執(zhí)行計算，再將公式向下填充，即在輔助列內(nèi)增加編號。其中，“$B$3:B3”是一個起始位置（B3單元格）保持不變，結(jié)束位置隨著公式向下填充而增加的動態(tài)單元格區(qū)域；“$I$1”是要查詢的部門。要查詢的部門是“市場1部”，A3單元格中的公式“=COUNTIF($B$3:B3,$I$1)”向下填充的結(jié)果是：每遇到一個“市場1部”，該公式有計算結(jié)果加1，從而將“市場1部”用不同的序號區(qū)分，而且只有“市場1部”出現(xiàn)的行，序號才會發(fā)生變化。（2）公式實現(xiàn)。在I3單元格中輸入公式“=IFERROR(VLOOKUP(ROW(A1),$A:$F,COLUMN(C1),0),"")0),"")”，按Enter鍵執(zhí)行計算，再將公式向下、向右填充，即得查詢結(jié)果。九利用VLOOKUP函數(shù)查詢一種產(chǎn)品多次的進貨量公式“=IFERROR(VLOOKUP($B$12&ROW(B1),IF({1,0},$B$2:按Ctrl+Shift+Enter組合鍵結(jié)束作業(yè)數(shù)據(jù)查詢和匹配練習(xí)教學(xué)小結(jié)本節(jié)知識：如何給相同姓名添加相同編號INDEX+SMALL函數(shù)組合完成一對多查找IFERROR函數(shù)修正VLOOKUP函數(shù)返回錯誤值如何查找主單號對應(yīng)的子單號，且主單號與子單號同行顯示利用公式從地址中提取省級行政區(qū)利用VLOOKUP+IF函數(shù)組合、VLOOKUP+CHOO