2.1數(shù)據(jù)查詢和匹配[38頁]課件

1、第2部分?jǐn)?shù)據(jù)統(tǒng)計(jì)分析-2.1數(shù)據(jù)查詢和匹配2.1數(shù)據(jù)查詢和匹配2.1.1 查找得票最多的姓名公式“=IFERROR(INDEX(B$2:B$16,SMALL(MODE.MULT(MATCH(B$2:B$16,B$2:B$16,),ROW(A1),)”2.1數(shù)據(jù)查詢和匹配2.1.2 查找開獎號碼對應(yīng)的數(shù)字公式“=IF(COUNT(FIND(C$1,$A2),C$1,)”2.1數(shù)據(jù)查詢和匹配2.1.3 何提取前3名銷量數(shù)據(jù)重復(fù)公式“=LARGE(IF(FREQUENCY($B$2:$B$13,$B$2:$B$13),$B$2:$B$13),ROW(A1)”數(shù)據(jù)不重復(fù)：2.1數(shù)據(jù)查詢和匹配2.1.5

2、 去掉最后一個特殊符號及以后內(nèi)容第1種方法：快速填充。第2種方法：公式法公式“=MID(A2,1,LOOKUP(99,FIND(-,A2,ROW($1:$30)-1)”2.1數(shù)據(jù)查詢和匹配2.1.4 提取訂貨量對應(yīng)的訂貨型號公式“=INDIRECT(ADDRESS(2,MATCH(LARGE(D4:G4,1),4:4,0)”2.1數(shù)據(jù)查詢和匹配2.1.6 兩列商品型號排序不一，對應(yīng)數(shù)量如何相減公式“=B2-IFERROR(VLOOKUP(A2,$E$2:$F$9,2,0),0)2.1數(shù)據(jù)查詢和匹配2.1.7 利用VLOOKUP函數(shù)實(shí)現(xiàn)多表數(shù)據(jù)合并查詢公式“=VLOOKUP($B2,INDIRE

3、CT($A2&!a:d),COLUMN(B1),0)”2.1數(shù)據(jù)查詢和匹配2.1.8 VLOOKUP+MATCH，輕松查找數(shù)據(jù)公式“=VLOOKUP(A13,A1:E10,MATCH(B12,A1:E1,0),0)”2.1數(shù)據(jù)查詢和匹配 2.1.9 滿足條件的數(shù)據(jù)自動“跑”到其它工作表公式“=INDEX(全部!A:A,SMALL(IF(全部!$F:$F=已對,ROW(全部!A:A),ROWS(A:A),ROW(A1)&”2.1數(shù)據(jù)查詢和匹配2.1.10 INDEX+MATCH函數(shù)組合應(yīng)用查找業(yè)績前幾名員工姓名公式“=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0

4、)”2.1數(shù)據(jù)查詢和匹配2.1.11 INDEX+MATCH函數(shù)組合應(yīng)用提取行列交叉單元格數(shù)值公式“=INDEX(A1:E10,MATCH(A13,A1:A10,0),MATCH(B13,A1:E1,0)”2.1數(shù)據(jù)查詢和匹配2.1.12 INDEX+MATCH函數(shù)組合應(yīng)用提取整行整列數(shù)據(jù)公式“=INDEX(B2:E10,MATCH(A13,A2:A10,0),0)”，按Ctrl+Shift+Enter組合建執(zhí)行計(jì)算2.1數(shù)據(jù)查詢和匹配2.1.13 利用OFFSET函數(shù)在大量數(shù)據(jù)中查找指定數(shù)據(jù)公式“=IF(COUNTIF(OFFSET(D5,0,0,500,500),A4)0,有,無)”2.1

5、數(shù)據(jù)查詢和匹配2.1.14 利用OFFSET函數(shù)在動態(tài)區(qū)域中查找指定數(shù)據(jù)公式“=IF(COUNTIF(OFFSET($D$6,0,0,COUNTA($D:$D),COUNTA($6:$6),A4)0,有,無)”2.1數(shù)據(jù)查詢和匹配2.1.15 跨表查詢指定顧客的購買記錄公式“=INDEX(購買記錄表!B:B,SMALL(IF(購買記錄表!$B$2:$B$12=購買查詢!$B$1,ROW(購買記錄表!$B$2:$B$12),ROWS(購買記錄表!B:B),ROW(A1)&”，按Ctrl+Shift+Enter組合建執(zhí)行計(jì)算2.1數(shù)據(jù)查詢和匹配2.1.16 在大量信息中快速查找哪些員工信息不完整第

6、1種方法：定位法。第2種方法：公式法。2.1數(shù)據(jù)查詢和匹配2.1.17 利用OFFSET函數(shù)提取最大銷量整列信息公式“=OFFSET($A$2,ROW(A1)-1,MATCH(MAX($B$5:$K$5),$B$5:$K$5,0)”2.1數(shù)據(jù)查詢和匹配2.1.18 利用OFFSET函數(shù)提取前三位銷量整列信息公式“=OFFSET($A$2,ROW($A1)-1,MATCH(LARGE($B$5:$K$5,COLUMN(A1),$B$5:$K$5,0)”2.1數(shù)據(jù)查詢和匹配2.1.19 提取銷售量最大的月份公式“=OFFSET($A$1,MATCH(MAX(B2:M2),B2:M2,0)”2.1數(shù)

7、據(jù)查詢和匹配2.1.20 提取銷售量第一、二、三位的月份公式“=OFFSET($A$1,MATCH(LARGE($B2:$M2,COLUMN(A$1),$B2:$M2,0)”2.1數(shù)據(jù)查詢和匹配2.1.21 如何給相同姓名添加相同編號1相同姓名有序排列。第1步，在A2單元格中輸入編號1。第1步，在A3單元格中輸入公式“=IF(B3=B2,A2,A2+1)”，按Enter鍵執(zhí)行計(jì)算，再將公式向下填充，即得結(jié)果2.1數(shù)據(jù)查詢和匹配2.1.21 如何給相同姓名添加相同編號2姓名無序排列。公式“=IFERROR(VLOOKUP(G2,IF(1,0,G$1:G1,F$1:F1),2,0),N(F1)+1

8、)”2.1數(shù)據(jù)查詢和匹配2.1.22 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í)行計(jì)算2.1數(shù)據(jù)查詢和匹配2.1.23 VLOOKUP與IFERROR函數(shù)組合屏蔽查找錯誤值公式“=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$12,2,0),)”2.1數(shù)據(jù)查詢和匹配2.1.24 如何查找主單號對應(yīng)的子單號，且子單號同行顯示方法1：輔助列+VLOOKUP函數(shù)。2.1數(shù)據(jù)查詢和匹配2.1.24

9、如何查找主單號對應(yīng)的子單號，且子單號同行顯示方法2：公式“=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),)”2.1數(shù)據(jù)查詢和匹配2.1.25 利用公式從地址中取省級行政區(qū)公式“=LEFT(A2,MIN(FIND(省,市,區(qū),A2&省市區(qū))”2.1數(shù)據(jù)查詢和匹配2.1.26 Vlookup+IF函數(shù)、Vlookup+CHOOSE函數(shù)實(shí)現(xiàn)逆向查詢1VLOOKUP+IF函數(shù)實(shí)現(xiàn)逆向查詢2.1數(shù)據(jù)查詢和匹配2.1.26 Vlookup+

10、IF函數(shù)、Vlookup+CHOOSE函數(shù)實(shí)現(xiàn)逆向查詢2VLOOKUP+CHOOSE函數(shù)實(shí)現(xiàn)逆向查詢2.1數(shù)據(jù)查詢和匹配2.1.27 IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等級評定IF函數(shù)：公式“=IF(B260,不合格,IF(B270,合格,IF(B285,良好,優(yōu)秀)”2.1數(shù)據(jù)查詢和匹配2.1.27 IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等級評定VLOOKUP函數(shù)：公式“=VLOOKUP(B2,0,不合格;60,合格;70,良好;85,優(yōu)秀,2)”2.1數(shù)據(jù)查詢和匹配2.1.27 IF、VLOOKUP、LOOKUP、CHOOSE

11、、INDEX都可完成等級評定LOOKUP函數(shù)：公式“=LOOKUP(B2,0,60,70,85,不合格;合格;良好;優(yōu)秀)”2.1數(shù)據(jù)查詢和匹配2.1.27 IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等級評定CHOOSE+MATCH函數(shù)：公式“=CHOOSE(MATCH(B2,0,60,70,85,1),不合格,合格,良好,優(yōu)秀)”2.1數(shù)據(jù)查詢和匹配2.1.27 IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等級評定INDEX+MATCH函數(shù)：公式“=INDEX(不合格;合格;良好;優(yōu)秀,MATCH(B2,0,60,70,80,1)”2.1數(shù)據(jù)查詢和匹配2.1.28 利用VLOOKUP函數(shù)查詢同一部門多個員工信息第1步：建立輔助列。第2步：公式實(shí)現(xiàn)。2.1數(shù)據(jù)查詢和匹配2.1.29 利用V