."VLookup" "HLookup" 函數(shù)的使用今天我們繼續(xù). 今天我們要學(xué)習(xí)的是我個(gè)人認(rèn)為Excel 中最重要的一個(gè)函數(shù) (至少我的工作是這樣). 隆重介紹:VLookup & HLookup

."VLookup" "HLookup" 函數(shù)的使用

今天我們繼續(xù). 今天我們要學(xué)習(xí)的是我個(gè)人認(rèn)為Excel 中最重要的一個(gè)函數(shù) (至少我的工作是這樣). 隆重介紹:

VLookup & HLookup

大家看到"Lookup" 就應(yīng)該知道這個(gè)函數(shù)何查找有關(guān)系. 沒錯(cuò). 這個(gè)函數(shù)就是讓我們找東西的.

VLookup 中的 "V" 是的意思, 也就是說這個(gè)函數(shù)是豎著找; H是Horizontal, 就是橫著找. 我們下面就只講VLookup 函數(shù), 大家學(xué)會(huì)了自然就知道HLookup 怎么用了. 而且如果做數(shù)據(jù)分析, 我們大部分都是用VLookup.

好, 我們先來看看VLookup 函數(shù)有哪些參數(shù):

= Vlookup (參數(shù)1, 參數(shù)2, 參數(shù)3, 參數(shù)4)

例如: B2 = Vlookup(A2, E:F, 2, False)

參數(shù)1: (要找什么?) 你要查找的單元格或者內(nèi)容, 比如下面的A2單元格

參數(shù)2: (到哪里找?) 你要到哪里找出你想要的內(nèi)容, 比如上面的函數(shù)我們是希望從E 列中找到A2, 然后從F 列中把相應(yīng)的Offer 數(shù)取出來.

參數(shù)3: (列的間隔數(shù)) 你" 要找的數(shù)據(jù)" 和" 希望得出的數(shù)據(jù)" 相距的列數(shù), 比如上面的例子, 參數(shù)3是"2", 因?yàn)镋 和F 為間隔的兩列, 所以是2. 如果是E 列到G 列, 那么就是3了.

參數(shù)4: 有TRUE 和FALSE 兩個(gè)選擇, FALSE是精確匹配, 只有完全相同的內(nèi)容他才找出來; TRUE則可以找相似的內(nèi)容. (建議大家不要用TRUE, 因?yàn)椴恢浪降啄苷业绞裁?

所以上面的函數(shù)(Vlookup(A2, E:F, 2, False)) 的意思就是: 在B2單元格中, 我要把A2單元格的內(nèi)容拿出來, 到E 列里面找有沒有和A2中一樣的內(nèi)容, 如果有, 則把E 列的這個(gè)單元格(E2)所對(duì)應(yīng)的F 列(F2) (因?yàn)閰?shù)3是2) 取出來放在單元格B2中. 我們看看結(jié)果如下:

這么說估計(jì)大家還是不是很理解到底這個(gè)函數(shù)干嗎用. 好, 下面我們開始舉例子:

例子1: 公司進(jìn)行了一次考試, A14-A17是4個(gè)不及格的Agent 的名單, 老板叫你去把這四人的Team 查一查.

這個(gè)時(shí)候呢, 你當(dāng)然可以一個(gè)個(gè)看, 因?yàn)椴?個(gè)人, 但是當(dāng)人多的時(shí)候就要用到函數(shù)了. 所以你要先去找HR 或者是Reporting Team要一份Agent 和Team 的對(duì)應(yīng)表. 假設(shè)你從HR 那里拿到一份表單如E13:G22, 是所有員工的信息.

好, 這樣我們就可以開始在B 列找出這四個(gè)Agent 相應(yīng)的Team 了.

以A14(Jack)為例:

- 我們要找什么? 找"Jack", 就是找A14單元格 (參數(shù)1 = A14)

- 我們要到哪里找? 我們要從E13:E22里面找A14的內(nèi)容(Jack), 然后從G13:G22中取出相應(yīng)行的Team 名字 (參數(shù)2 = E13:G22)

- 參數(shù)2里面的" 要找的數(shù)據(jù)" 和" 希望得出的數(shù)據(jù)" 相距多少列? E/F/G, 三列 (參數(shù)3 = 3)

- 我們要精確匹配 (參數(shù)4 = FALSE)

所以B14可以寫成: = VLOOKUP(A14,E13:G21,2,FALSE) 大家可以看到結(jié)果如下:

好, 接下來我們只要把B14的函數(shù)直接往下拖就可以得到B15:B17的結(jié)果了. 如下:

大家注意到?jīng)]有? 前三個(gè)Agent 所對(duì)應(yīng)的Team 都找到了, 為什么第四個(gè)Agent - Dibort為什么出錯(cuò)了呢? 我們明明看到E15單元格里面是Dibort 啊???

大家注意看上面的圖, 注意一下C14 - C17幾個(gè)函數(shù)的區(qū)別! 大家看到嗎? 這四個(gè)函數(shù)的第二個(gè)參數(shù)隨著我們剛才拖動(dòng)函數(shù)而變化了. 從B14單元格中的"E13:G21"變成了B17中的"E16:G24". (這個(gè)是Excel 的自有功能, 函數(shù)中的參數(shù)會(huì)隨著我們的拖動(dòng)或者拷貝而自動(dòng)變化), 當(dāng)然參數(shù)1也是自動(dòng)變化的.

其中參數(shù)1的變化是我們需要的, 因?yàn)槲覀円业氖茿14-A17的內(nèi)容; 但是參數(shù)2我們并不希望他變化, 因?yàn)槲覀円臄?shù)據(jù)是應(yīng)該固定在E13:G21不變.

那么我們?nèi)绾喂潭▍?shù)2呢? 在Excel 中, 如果要讓參數(shù)固定, 則要用到"

鬼推磨, 我們就用" 美金" 讓參數(shù)固定. $" 這個(gè)符號(hào). 俗話說: 有錢能使

現(xiàn)在我們把B14改一下: B14 = VLOOKUP(A14,$E$13:$G$21,3,FALSE)

大家看到了. 我們?cè)谛枰潭ǖ牡胤郊由?$", 當(dāng)我們拖動(dòng)函數(shù)時(shí)這些參數(shù)就不會(huì)再變了. 對(duì)于這個(gè)例子, 其實(shí)我們可以看到其實(shí)只有行號(hào)在變, 列號(hào)(E,F)其實(shí)沒有變化, 所以這里我們寫成 " E$13:G$21 " 也是可以的.

*** 這里我們講個(gè)小技巧, 其實(shí)當(dāng)我們加"$"挺麻煩的, 因?yàn)樘厥庾址覀兌疾怀Ｓ? 這里我們可以點(diǎn)擊函數(shù)編輯框, 光標(biāo)移到相應(yīng)的位置, 然后按"F4", 就可以直接在行和列上加上$了, 再按"F4" 就會(huì)去掉列的$, 只保留行的$; 再按"F4", 則變成只有列有$; 再按"F4", 所有的$都消失了. (大家自己試一試就明白了)

例子2: 如下表, A列是所有Agent 的名單, B列需要找出每個(gè)人今年的Bonus. 老板今天和你說: 今年經(jīng)濟(jì)危機(jī)了, 只有4個(gè)人有Bonus(就是E30:E33這4個(gè)人), 讓你去把B 列填清楚. 對(duì)于有Bonus 的Agent 則顯示" 裁員廣進(jìn)" Bonus的數(shù)目, 沒有Bonus 的顯示" 薪餉四成".

那么我們一步步來. 先用Vlookup 函數(shù)找找看.

B30 = VLOOKUP(A30,$E$29:$F$33,2,FALSE)

結(jié)果如圖, 我們看到, 其中4個(gè)Agent 在E 列找到了, 所以顯示出了相應(yīng)的Bonus, 其余的Agent 因?yàn)闆]有找到所以得出了錯(cuò)誤值"#N/A".

但是我們題目的要求不是這么簡單, 還要顯示出相應(yīng)的文字, 因此我們要用到 ISNA 函數(shù)來判斷我們得來的值. 然后用 IF 函數(shù)顯示相應(yīng)的文字.

B30 = IF(ISNA(VLOOKUP(A30,$E$29:$F$33,2,FALSE)),"薪餉四成"," 裁員廣進(jìn)

"&VLOOKUP(A30,$E$29:$F$33,2,FALSE))

對(duì)于Vlookup 函數(shù)的應(yīng)用, 大家特別要注意的是要加上$以講查詢范圍固定, 經(jīng)常的錯(cuò)誤就是這個(gè)造成的. (當(dāng)然, 有的時(shí)候反而不需要固定, 要看具體需要而定).

例子3

: 其中"F15:G21"是每個(gè)Agent 的英文成績; "I15:J21"是法語成績, 請(qǐng)?jiān)贐 列中顯示: 如果總成績高于100, 則顯示Pass, 低于100顯示Fail

要計(jì)算英文和法語的成績總和, 則需要分別找到英文的成績和法語的成績. 所以函數(shù)就是:

查找英文成績: VLOOKUP(A16,$F$15:$G$21,2,FALSE)

查找法語成績: VLOOKUP(A16,$I$15:$J$21,2,FALSE)

后面就簡單了吧. 直接用IF 語句來判斷是否這兩個(gè)成績的和是否大于100. 因此整個(gè)函數(shù)就是: B16 =

IF(VLOOKUP(A16,$F$15:$G$21,2,FALSE) VLOOKUP(A16,$I$15:$J$21,2,FALSE)>=100,"Pass","Fail")

例子4: 讓我們來練習(xí)一下HLOOKUP 函數(shù)好了. 如下面的兩個(gè)表, 請(qǐng)用HLOOKUP 函數(shù)查找每個(gè)人的號(hào)碼.

這個(gè)就不多說了. 按照Vlookup 的用法直接套用就可以了.

B31 = HLOOKUP(A31,$E$30:$K$34,5,FALSE)

為什么第三個(gè)參數(shù)是5? 自己想了~~~ 呵呵

例子5: 下面的B46和B48分別可以選擇名字和語言, 請(qǐng)根據(jù)這兩項(xiàng)的選擇自動(dòng)在B50中顯示相應(yīng)的成績(成績?cè)谟疫叺谋碇?

好, 這題我們要分兩步走, 第一步, 我們可以假設(shè)沒有B48的條件, 而是單純查找法語成績, 這樣就簡單了. 函數(shù)可以寫成:

B50 = VLOOKUP(B46,$F$47:$H$52,3,FALSE)

很簡單, 第一步就完成了. 但是怎么根據(jù)B48來決定查找呢? 就是要通過B48來決定上面函數(shù)的第三個(gè)參數(shù) - 3???? 看到上面的表, 大家可以知道要查找英文則參數(shù)3等于2; 要查找法語則參數(shù)3等于3. 所以上面的參數(shù)3可以替換成:

= IF(VLOOKUP("Name",$F$46:$H$46,2,FALSE)=B48,2,3)

把兩個(gè)函數(shù)合在一起就是:

B50 =

VLOOKUP(B46,$F$47:$H$52,IF(VLOOKUP("Name",$F$46:$H$46,2,FALSE)=B48,2,3),FALSE)

結(jié)果如下

:

LOOKUP 查找函數(shù)（一）

2010年02月12日 8:25

含義：返回向量或數(shù)組中的數(shù)值。函數(shù) LOOKUP 有兩種語法形式：向量和數(shù)組。函數(shù) LOOKUP 的向量形式是在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值；函數(shù) LOOKUP 的數(shù)組形式在數(shù)組的第一行或第一列查找指定的數(shù)值，然后返回?cái)?shù)組的最后一行或最后一列中相同位置的數(shù)值。

函數(shù) LOOKUP 有兩種語法形式：向量和數(shù)組。

提示： LOOKUP_vector 的數(shù)值必須按升序排序：... 、-2、-1、0、1、2、... 、A-Z 、FALSE 、TRUE ；否則，函數(shù) LOOKUP 不能返回正確的結(jié)果。文本不區(qū)分大小寫。

一、語法 1（向量）

向量為只包含一行或一列的區(qū)域。函數(shù) LOOKUP 的向量形式是在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值。如果需要指定包含待查找數(shù)值的區(qū)域，則可以使用函數(shù) LOOKUP 的這種形式。函數(shù) LOOKUP 的另一種形式為自動(dòng)在第一列或第一行中查找數(shù)值。

LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup_value 為函數(shù) LOOKUP 在第一個(gè)向量中所要查找的數(shù)值。

lookup_value 可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。

Lookup_vector 為只包含一行或一列的區(qū)域。lookup_vector 的數(shù)值可以為文本、數(shù)字或邏輯值。

Result_vector 只包含一行或一列的區(qū)域，其大小必須與 lookup_vector 相

同。

說明

? 如果函數(shù) LOOKUP 找不到 lookup_value，則查找 lookup_vector 中小于或等于 lookup_value 的最大數(shù)值。

? 如果 lookup_value 小于 lookup_vector 中的最小值，函數(shù) LOOKUP 返回錯(cuò)誤值 #N/A。

示例

如果您將示例復(fù)制到空白工作表中，可能會(huì)更易于理解該示例。

A B

1 頻率 顏色

2 4.14 幽紅

3 4.91 輕橙

4 5.17 艷黃

5 5.77 湖青

6 6.39 醺藍(lán)

公式 說明（結(jié)果）

=LOOKUP(4.91,A2:A6,B2:B在A 列中查找4.91，并返回同一行B

6) 列的值(輕橙)

=LOOKUP(5.00,A2:A6,B2:B在A 列中查找5.00（最接近的下一個(gè)

6) 值為4.91），并返回同一行B 列的值(輕橙)

=LOOKUP(7.66,A2:A6,B2:B在A 列中查找7.66（最接近的下一個(gè)

6) 值為6.39），并返回同一行B 列的值(醺藍(lán))

在A 列中查找0，由于0小于查找向量=LOOKUP(0,A2:A6,B2:B6) A2:A7中的最小值，所以返回錯(cuò)誤值(#N/A)

LOOKUP 查找函數(shù)（二）

2010年02月12日 8:29

二、語法 2（數(shù)組）

函數(shù) LOOKUP 的數(shù)組形式是在數(shù)組的第一行或第一列中查找指定數(shù)值，然后返回最后一行或最后一列中相同位置處的數(shù)值。如果需要查找的數(shù)值在數(shù)組的第一行或第一列，就可以使用函數(shù) LOOKUP 的這種形式。當(dāng)需要指定列或行的位置時(shí)，可以使用函數(shù) LOOKUP 的其他形式。

LOOKUP(lookup_value,array)

Lookup_value 為函數(shù) LOOKUP 在數(shù)組中所要查找的數(shù)值。LOOKUP_value 可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用

? 如果函數(shù) LOOKUP 找不到 lookup_value，則使用數(shù)組中小于或等于 lookup_value 的最大數(shù)值。

? 如果 lookup_value 小于第一行或第一列（取決于數(shù)組的維數(shù)）的最小值，函數(shù) LOOKUP 返回錯(cuò)誤值 #N/A。

Array 為包含文本、數(shù)字或邏輯值的單元格區(qū)域，它的值用于與

lookup_value 進(jìn)行比較。函數(shù) LOOKUP 的數(shù)組形式與函數(shù) HLOOKUP 和函數(shù) VLOOKUP 非常相似。不同之處在于函數(shù) HLOOKUP 在第一行查找

lookup_value，函數(shù) VLOOKUP 在第一列查找，而函數(shù) LOOKUP 則按照數(shù)組的維數(shù)查找。

? 如果數(shù)組所包含的區(qū)域?qū)挾却?，高度小（即列?shù)多于行數(shù)），函數(shù) LOOKUP 在第一行查找 lookup_value。

? 如果數(shù)組為正方形，或者所包含的區(qū)域高度大，寬度?。葱袛?shù)多于列數(shù)），函數(shù) LOOKUP 在第一列查找 lookup_value。

? 函數(shù) HLOOKUP 和函數(shù) VLOOKUP 允許按行或按列索引，而函數(shù) LOOKUP 總是選擇行或列的最后一個(gè)數(shù)值。

示例

如果您將示例復(fù)制到空白工作表中，可能會(huì)更易于理解該示例。

公式 說明（結(jié)果）

在數(shù)組的第一行中查找

=LOOKUP("C",{"a","b","c","d";1,2,3,4}) “C”，并返回同一列中最后一行

的值(3)

在數(shù)組的第一行中查找

=LOOKUP("bump",{"a",1;"b",2;"c",3}) “bump”，并返回同一行中最后一

列的值(2)

VLOOKUP 與HLOOKUP 都是查找函數(shù)，不過VLOOKUP 是按列查找的，HL OOKUP 是按行查找的。具體使用方法如下：

一、VLOOKUP 函數(shù)【按列查找】

VLOOKUP(lookup_value,table_array, col_index_num,range_lookup)

VLOOKUP（查找值，區(qū)域，列序號(hào)，邏輯值）

例如：=VLOOKUP($C5,記錄單!$C$3:$D$5000, 2,FALSE)

1）lookup_value（查找值）：為需要在表格數(shù)組第一列中查找的數(shù)值。Lookup_value可以為數(shù)值或引用。若lookup_value小于table_array第一列中的最小值，VLOOKUP 返回錯(cuò)誤值#N/A。

2）table_array（區(qū)域）：為兩列或多列數(shù)據(jù)。使用對(duì)區(qū)域或區(qū)域名稱的引用。table_array第一列中的值是由lookup_value搜索的值。這些值可以是文本、數(shù)字或邏輯值。文本不區(qū)分大小寫。

3）col_index_num（列序號(hào)）：為table_array中待返回的匹配值的列序號(hào)。col_index_num為1時(shí)，返回table_array第一列中的數(shù)值；col _index_num為2，返回table_array第二列中的數(shù)值，以此類推。如果co l_index_num小于1，VLOOKUP 返回錯(cuò)誤值#VALUE!。大于table_array的列數(shù)，VLOOKUP 返回錯(cuò)誤值#REF!。

4）range_lookup（邏輯值）：指定希望VLOOKUP 查找精確的匹配值還是近似匹配值。

如果為TRUE （或?yàn)?），則返回精確匹配值或近似匹配值。也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值。table_array 第一列中的值必須以升序排序；否則VLOOKUP 可能無法返回正確的值。有關(guān)詳細(xì)信息，請(qǐng)參閱排序數(shù)據(jù)。