2010-09-23 05:22:21| 分類：
EXCEL學習 | 標簽：
函數(shù) 公式 查找 match vlookup |字號
訂閱第一部分 學習心得
本節(jié)課程，ychexcel 老師以VLOOKUP函數(shù)為主，著重介紹了VLOOKUP函數(shù)的參數(shù)和查詢方式、查詢技巧。
VLOOKUP函數(shù)
用途：使用 VLOOKUP 函數(shù)搜索某個單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何單元格中的值。
語法：VLOOKUP(查找值,查找區(qū)域,列數(shù),[查找方式])
參數(shù)：
查找值：必需。要在表格或區(qū)域的第一列中搜索的值。查找值 參數(shù)可以是值或引用。如果為查找值 參數(shù)提供的值小于查找區(qū)域 參數(shù)第一列中的最小值，則 VLOOKUP 將返回錯誤值 #N/A。
查找區(qū)域：必需。包含數(shù)據(jù)的單元格區(qū)域?？梢允褂脤^(qū)域（例如，A2:D8）或區(qū)域名稱的引用。查找區(qū)域 第一列中的值是由 查找值 搜索的值。這些值可以是文本、數(shù)字或邏輯值。文本不區(qū)分大小寫。
列號：必需。查找區(qū)域 參數(shù)中必須返回的匹配值的列號。列數(shù) 參數(shù)為 1 時，返回 查找區(qū)域 第一列中的值；列號 參數(shù)為 2 時，返回 查找區(qū)域第二列中的值，依此類推。
如果 列號 參數(shù)：
小于 1，則 VLOOKUP 返回錯誤值 #VALUE!。
大于 查找區(qū)域 的列數(shù)，則 VLOOKUP 返回錯誤值 #REF!。
查找方式：可選。一個邏輯值，指定希望 VLOOKUP 查找精確匹配值還是近似匹配值：
如果 查找方式 為 TRUE 或被省略，則返回精確匹配值或近似匹配值。如果找不到精確匹配值，則返回小于查找值 的最大值。
要點 如果 查找方式 為 TRUE 或被省略，則必須按升序排列 查找區(qū)域 第一列中的值；否則，VLOOKUP 可能無法返回正確的值。
如果 查找方式 為 FALSE，則不需要對查找區(qū)域 第一列中的值進行排序。
如果 查找方式 參數(shù)為 FALSE，VLOOKUP 將只查找精確匹配值。如果 查找區(qū)域 的第一列中有兩個或更多值與 查找值 匹配，則使用第一個找到的值。如果找不到精確匹配值，則返回錯誤值 #N/A。
注釋：
在 查找區(qū)域 的第一列中查找文本值時，要確保 查找區(qū)域 第一列中的數(shù)據(jù)不包含前空格、尾空格、非打印字符或者未使用不一致的直引號（' 或 "）與彎引號（‘ 或 “）。在搜索數(shù)字或日期值時，應(yīng)確保查找區(qū)域 第一列中的數(shù)據(jù)未存儲為文本值。否則，VLOOKUP 可能返回不正確或意外的值。
如果 查找方式 為 FALSE 且 查找值 為文本，則可以在查找值 中使用通配符 － 問號 (?) 和星號 (*)。問號匹配任意單個字符；星號匹配任意字符序列。如果要查找實際的問號或星號，要在問號（？）或星號（*）字符前鍵入波形符 (~)。
通過反復(fù)學習查找函數(shù)的幫助文件，本人深沉體會到：
1、學習EXCEL，幫助文件是離自己最近的老師。
2、弄懂函數(shù)參數(shù)的意義，是學好函數(shù)、熟練使用函數(shù)的先決條件，否則，在構(gòu)建函數(shù)時可能得不到正確的結(jié)果，特別是在函數(shù)嵌套時，往往出錯。
3、多在EH論壇上查看別人回答提問者帖子，拜讀賢哲們的公式構(gòu)建技巧、理解大師們的解題思路，是學好EXCEL函數(shù)的捷徑。
4、參與回答別人的提問，是幫助別人解決問題、提高自己EXCEL水平的兩利的行為。同時又是加深自己對函數(shù)的記憶和理解的最佳方法。
通過本次學習對查找引用函數(shù)參數(shù)重新加深了認識：
1、reference參數(shù)一般地為單元格區(qū)域的引用;也可以為INDIRECT函數(shù)或OFFSET函數(shù)構(gòu)建的單元格或單元格區(qū)域。
２、Array參數(shù)可以為 單元格區(qū)域、數(shù)組常量。
３、Lookup_value為查找值【可以為數(shù)值、引用或文本字符串】;table_array為查找區(qū)域【可以為區(qū)域、常量數(shù)組、區(qū)域名稱或數(shù)組名稱】;row_index_num為查找的行號【一般為數(shù)字】;col_index_num為查找的列號【一般為數(shù)字】、range_lookup為查找方式【一般為邏輯值】;row_num、col_num、index_num等這些含num的參數(shù)【一般為數(shù)值】。
對OFFSET函數(shù)的基本理解：
語法 OFFSET(reference, rows, cols, [height], [width])
① Reference
② Rows
③ Cols
④ Height
⑤ Width
②~⑤的數(shù)值設(shè)置一定不可越出EXCEL的邊界，否則，就會出現(xiàn)#REF!
第二部分 課后作業(yè)與課前練習解題思路
A、課后作業(yè)鑰匙思路
第一題：暫時做了六種解法
１、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A5),VLOOKUP($A5,數(shù)據(jù)區(qū)!$A:$G,COLUMN(),),"不存在")
采用VLOOKUP函數(shù)普通用法，用COLUMN()獲得動態(tài)列號是這一公式的核心，設(shè)置絕對引用和混合引用是關(guān)鍵。
２、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A$5),OFFSET(數(shù)據(jù)區(qū)!$A$1,MATCH($A$5,數(shù)據(jù)區(qū)!$A:$A,)-1,MATCH(B$4,數(shù)據(jù)區(qū)!1:1,)-1),"不存在")
采用OFFSET函數(shù)，用MATCH函數(shù)獲取偏移的動態(tài)行、列數(shù)。
３、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A5),INDEX(數(shù)據(jù)區(qū)!$A:$G,MATCH($A$5,數(shù)據(jù)區(qū)!$A:$A,),COLUMN()),"不存在")
采用INDEX函數(shù)數(shù)組形式，用MATCH函數(shù)獲得動態(tài)行數(shù)，用COLUMN()獲得動態(tài)列數(shù)。
４、=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A$5),INDIRECT("數(shù)據(jù)區(qū)!R"&MATCH($A$5,數(shù)據(jù)區(qū)!$A:$A,)&"C"&MATCH(B$4,數(shù)據(jù)區(qū)!1:1,),),"不存在")
采用INDIRECT函數(shù)的R1C1－樣式構(gòu)建公式，用MATCH函數(shù)獲取R1C1－樣式的動態(tài)數(shù)字。
５、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A5),VLOOKUP($A5,數(shù)據(jù)區(qū)!$A:$G,COLUMN(B:G),),"不存在")
采用了VLOOKUP函數(shù)的數(shù)組解法，而COLUMN(B:G)函數(shù)是構(gòu)成本數(shù)組函數(shù)的關(guān)鍵。
６、解題公式=IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A$5),HLOOKUP(B$4,數(shù)據(jù)區(qū)!$B:$G,MATCH($A5,數(shù)據(jù)區(qū)!$A:$A,),),"不存在")
采用HLOOKUP函數(shù)的普通解法，關(guān)鍵在于選對MATCH函數(shù)的Lookup_value參數(shù)的值。
以上公式的排錯方法避免了一直延用的IF(ISNA(公式),”不存在”,公式)的用法，而采用了IF(COUNTIF(數(shù)據(jù)區(qū)!$A:$A,$A$5),公式,"不存在")的方式，從而縮短了公式長度，減少了計算機內(nèi)存的使用，提高了EXCEL的運算速度。
第二題、暫時做了五種解法
１、解題公式=INDIRECT(ADDRESS(MATCH(A4,數(shù)據(jù)區(qū)!A:A,),MATCH(B4,數(shù)據(jù)區(qū)!$1:$1,),,,"數(shù)據(jù)區(qū)"))
用INDIRECT+ADDRESS+MATCH構(gòu)建公式，而兩個MATCH函數(shù)是核心公式。
２、解題公式=OFFSET(數(shù)據(jù)區(qū)!$A$1,MATCH(A4,數(shù)據(jù)區(qū)!A:A,)-1,MATCH(B4,數(shù)據(jù)區(qū)!$1:$1,)-1)
采用OFFSET函數(shù)，用兩個MATCH函數(shù)獲得動態(tài)的偏移量是關(guān)鍵。
３、鑰匙公式=INDEX(數(shù)據(jù)區(qū)!$B$2:$G$11,MATCH(A4,數(shù)據(jù)區(qū)!A:A,)-1,MATCH(B4,數(shù)據(jù)區(qū)!$1:$1,)-1)
采用INDEX函數(shù)的數(shù)組形式，用兩個MATCH函數(shù)獲得動態(tài)的行數(shù)和列數(shù)。
４、解題公式=HLOOKUP(B4,數(shù)據(jù)區(qū)!$B:$G,MATCH(A4,數(shù)據(jù)區(qū)!A:A,),)
采用HLOOKUP函數(shù)普通用法，用MATCH函數(shù)獲取動態(tài)行號是核心。
５、解題公式=VLOOKUP(A4,數(shù)據(jù)區(qū)!A:G,MATCH(B4,數(shù)據(jù)區(qū)!$1:$1,),)
采用VLOKOKUP函數(shù)普通用法，用MATCH函數(shù)獲取動態(tài)列號是本公式的核心公式。
從上面的公式來看，MATCH函數(shù)，在查找引用函數(shù)中有著非常重要的作用。
第三題、=CHOOSE(MOD(ROW()-3,3)+1," ",數(shù)據(jù)區(qū)!A$1,INDEX(數(shù)據(jù)區(qū)!A:A,ROUNDUP((ROW()-3)/3,)+1))
采用CHOOSE函數(shù)特性構(gòu)建公式，CHOOSE函數(shù)第一參數(shù)有“如果 index_num為小數(shù)，則在在使用前將被截尾取整”的特性，因而，在此用MOD(ROW()-3,3)+1計算出CHOOSE函數(shù)取數(shù)的位置，由于要求的結(jié)果表格位于第四行，故而MOD函數(shù)中用了ROW()-3與３[３是受工資條設(shè)置要求影響的]的余數(shù)，然后加１，加１的作用是為了排錯，因為如MOD(６,3)，其結(jié)果為0，將導致公式出錯[ 幫助文件中：如果 index_num 小于 1 或大于列表中最后一個值的序號，函數(shù) CHOOSE 返回錯誤值 #VALUE!]。
解決了第一參數(shù)的問題，剩余的就是設(shè)置CHOOSE函數(shù)的第二、三、四參數(shù)了。
第二參數(shù)設(shè)置了空行。
第三參數(shù)也非常簡單，就是為了求出工資條的表頭。
第四參數(shù)用INDEX函數(shù)構(gòu)建，其核心公式為ROUNDUP(ROW()-3)/3,)，ROUNDUP函數(shù)是按要求向上舍入的函數(shù)，從而取得INDEX函數(shù)的取值行號。
B、課前練習解題思路
練習一、
分析：
首先，等級轉(zhuǎn)換的標準是考試得分與本科目最高分的比值在權(quán)重的哪個范圍，
然后求出對應(yīng)的等級。
有了分析的結(jié)論，接下來就是要如何求出考試得分和科目最高分
單元格公式解讀：
1、
考試得分
091010138
28
28
75
44
20
27
32
91
76
421
因為結(jié)果區(qū)域的科目排列順序與成績登記順序不一，所以公式
VLOOKUP(M25,$A:$K,MATCH(N$6,$A$2:$K$2,),)公式中的關(guān)鍵是
取得結(jié)果區(qū)域科目在成績登記區(qū)域的列數(shù)字，這里采用match函數(shù)
取得列數(shù)值
也可以
28
28
75
44
20
27
32
91
76
421
2、
科目最高分
50
50
120
50
50
70
50
120
120
680
3、
計算比值
0.56
0.56
0.63
0.88
0.4
0.39
0.64
0.76
0.63
0.62
4、
等級轉(zhuǎn)換
C+
C+
B
A
C
C
B
B+
B
B
公式后半部分之所以采用人工編輯數(shù)組，是因為Lookup函數(shù)向量形式的特性決定的
（參見：LOOKUP向量形式幫助文件中 紅色字體部分）
如果等級轉(zhuǎn)換標準區(qū)域設(shè)置如藍色區(qū)域，則公式可簡化為：
C+
C+
B
A
C
C
B
B+
B
B
5、
組合公式
C+
C+
B
A
C
C
B
B+
B
B
多單元格數(shù)組公式解讀：
1、
考試得分
取得各科位于源數(shù)據(jù)中的列數(shù)值
7
10
4
5
6
9
8
2
3
11
取得各科的分數(shù)
28
28
75
44
20
27
32
91
76
421
2、
取得各科目最高分
50
50
120
50
50
70
50
120
120
680
3、
計算各科目考試得分與最高分的比值
0.56
0.56
0.63
0.9
0.4
0.39
0.64
0.76
0.63
0.619118
4、
等級轉(zhuǎn)換
C+
C+
B
A
C
C
B
B+
B
B
5、
公式重組
C+
C+
B
A
C
C
B
B+
B
B
練習二
本練習目的是要考察觀察源數(shù)據(jù)的仔細程度和重新構(gòu)建查找區(qū)域的能力
分析：
仔細觀察源數(shù)據(jù)[基礎(chǔ)數(shù)據(jù)]，與本要求結(jié)果對照，不難發(fā)現(xiàn)：
1、我們實際要查詢的是標件名稱的零件編碼、零件單價和零件產(chǎn)地。
2、而源數(shù)據(jù)中，標件名稱不在同一列。
3、認真觀察后發(fā)現(xiàn)，可以用標件類型在 基礎(chǔ)數(shù)據(jù) 工作表中第三行，確定標件類型的位置。
4、有了標件類型的位置，就給我們重新構(gòu)建查詢區(qū)域，提供了一種可能。
動手操作：
標件名稱
螺栓
=OFFSET(INDIRECT("基礎(chǔ)數(shù)據(jù)!"&CHAR(MATCH(J14,基礎(chǔ)數(shù)據(jù)!2:2,)+64)&3),,100,4)
1
基點確認
方法１、INDIRECT法
基礎(chǔ)數(shù)據(jù)!R3C2
5×8螺栓
基礎(chǔ)數(shù)據(jù)!B3
5×8螺栓
方法２、ADDRESS法
基礎(chǔ)數(shù)據(jù)!$B$3
5×8螺栓
方法３、OFFSET法
5×8螺栓
2
構(gòu)建查詢區(qū)域
基礎(chǔ)數(shù)據(jù)!B3:F102
3
建立查詢公式
90101001
0.08
杭州標件廠
4、排錯 參見F4公式
練習三
統(tǒng)計下列區(qū)域中B,D,F列含有A的個數(shù)(不連續(xù)區(qū)域統(tǒng)計)
A
W
A
W
A
結(jié)果
B
S
D
S
A
方法1
8
=SUM(COUNTIF(INDIRECT({"B4:B10","D4:D10","F4:F10"}),"A"))
D
A
G
F
A
方法2
8
{=SUM(N(CHOOSE({1,2,3},B4:B10,D4:D10,F4:F10)="A"))}
E
D
H
A
D
方法3
8
=SUM(COUNTIF(OFFSET(B4:B10,,{0,2,4}),"A"))
G
W
A
S
C
A
S
D
F
C
A
F
H
D
F
練習四
首先，我們應(yīng)該能夠確定的是，這是一個INDEX函數(shù)的數(shù)組形式的使用。
那么，在多單元格數(shù)組公式中如何用，如何構(gòu)建是理解這類公式的難點！
理解這類公式的方法有多種，下面我介紹一個常用的方法：
皰丁解牛法
先應(yīng)解決的問題是第幾行、第幾列
A
列的問題
1、
查找關(guān)鍵
1
2
2
1
2
1
2、
找相同、貼標簽：
1001
2002
2003
1004
2005
1006
貼標簽的作用是將符合相同條件的數(shù)據(jù)加以標注，方便后續(xù)使用。
這里我們將條件擴大1000倍，是先予后取，如果不先給予，直接將條件與列數(shù)相加，將出現(xiàn)列數(shù)超出array的區(qū)域范圍的情況！
3、
按圖索驥
1001
1001
1004
1004
1006
1006
2002
2002
2003
2003
2005
2005
上面的公式主要的作用是給符合需要轉(zhuǎn)換的數(shù)據(jù)打上標簽，并排出順序
4、
去偽存真
001
001
1
1
004
004
4
4
006
006
6
6
002
002
2
2
003
003
3
3
005
005
5
5
從上面的結(jié)果看我們?nèi)〉氖欠蠗l件列號列表
B
行的問題
1
2
C
對號入座
1
張三
1
趙二
1
孫六
2
李四
2
王五
2
錢一
D
結(jié)構(gòu)重組
結(jié)構(gòu)重組這是我個人的提法，意思就是將上述我們分步得到的公式進行組合，使之形成一個數(shù)組公式。
1
張三
1
趙二
1
孫六
2
李四
2
王五
2
錢一
EH培訓二〇一〇年九月二十三日
