VLOOKUP,HLOOKUP 查詢範例

範例一:
請利用圖中「級別對照表」來找出「考績級別」中的黃色區域的級別

image

首先,先決定使用的函數是VLOOKUP還是HLOOKUP?這需要由「級別對照表」來決定,因為圖中的分數與級別的欄位名稱在表格最上方,亦表示這個表的資料是垂直式排列的方式,所以我們使用VLOOKUP函數。

按我觀看此函數說明

VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)
VLOOKUP(查詢值,查詢表,傳回相對應第n欄的資料,查詢方式)

考績的「級別」是經由「級別對照表」來獲得的,所以是查詢值分數;查詢表是級別對照表,有了分數後,將分數放到級別對照表中的最左邊欄來比對,看比對的結果在那兩個分數的區間,找到符合的接近分數後,在分數的同一列上傳回與對應的欄位交叉的值。所以公式為

B3=VLOOKUP(A3,$A$12:$B$16,2,TRUE) 或
B3=VLOOKUP(A3,$A$12:$B$16,2,1) 或
B3=VLOOKUP(A3,$A$12:$B$16,2) <<= 最常採用省略最後一個引數的方式

知道為什麼$A$12:$B$16需要 $ 符號,按我看原因

因為要查詢的是分數的區間,所以分數的值不一定會完全相同,所以我們需要找的是接近的區間分數,所以最後一個引數查詢方式可以省略或輸入TRUE或數字1。表示要找的是接近的資料。

☆☆ 此類型的題目亦可使用 IF 函數來達到相同的查詢效果,您可以想想看如何用 IF 函數來完成。

===============================================================
如果您的級別對照表如下圖所示,因您的對照表的欄位名稱在表格最左邊,所以此時您的函數應為HLOOKUP。
image

則公式改為
B3=H LOOKUP(A3,$B$2:$F$3,2) <<= 最常採用省略最後一個引數的方式 


範例二: (跨工作表的對照查詢
請利用圖一文具品項工作表中的「文具品項」對照表來找出圖二登錄資料工作表中的「品名、售價」資料

image 圖一

image  圖二

同於上例,因文具品項工作表中的「文具品項」對照表亦為垂直式排列,故函數仍為VLOOKUP。

一樣的,您仍需藉由登錄資料中的編號到文具品項對照表中來取得品名,故您的查詢值為編號,查詢表為文具品項工作表中的「文具品項」對照表。

另外,最後一個引數查詢方式,在上一個例子中使用接近值的查詢,而本例中,因為各編號對應各品名,且品名為文字,故應使用完全符合值的查詢。

所以品名的公式為

B3=VLOOKUP(A3,文具品項!$A$3:$C$10,2,FALSE) 或
B3=VLOOKUP(A3,文具品項!$A$3:$C$10,2,0)

所以售價的公式為

C3=VLOOKUP(A3,文具品項!$A$3:$C$10,3,FALSE) 或
C3=VLOOKUP(A3,文具品項!$A$3:$C$10,3,0)

公式結構,按我看說明

☆☆ VLOOKUP和HLOOKUP函數應該多多練習,其實不難,只要了解各引數之間的關係就可以了

2 則留言:

  1. 寫的真棒! ~ :-D 謝謝喲!

    回覆刪除
  2. 請問我遇到一個問題,我的是用DDE輸出動態資料庫,而當使用百分比排列時
    例如

    原始table data 的數值是不同時 (如下)

    甲  90%
    乙  95%
    丙 100﹪
    丁  80%
    戊  85%

    其排列會依照預期 (利用 VLOOKUP 查表排列出)

    丙 100﹪
    乙  95%
    甲  90%
    戊  85%
    丁  80% 

    但是當有時 table data 出現 相同數值時 

    甲  90%
    乙  95%
    丙 100﹪
    丁 100%
    戊  85%
      
    其排列會不如預期 (利用 VLOOKUP 查表排列出)

    丙 100﹪
    丙 100% <----不會把丁排出來
    乙  95%
    甲  90%
    戊  85%
     
    請問大大這個問題有辦法解決嗎? 謝謝.

    回覆刪除

[ 訊息 ]
本站將持續加入新的教學文章,請給予支持。若覺得文章寫的不錯,可以多按 " FUNP推推王 " 與 " 黑米書籤 " 。
到訪本站的朋友們,請在問題研討區多多留言或針對各文章發表意見或回應。