[公告] 痞客邦「應用市集」新 App 上架-iFontCloud Professional[公告] 痞客邦後台發表文章提供插入多張圖片新功能[公告]痞客邦新服務上線 部落客商店聚集就在《痞市集》[公告] 部落格「快捷功能BAR」改版介紹[公告] 痞客邦「快捷功能BAR」6月4日改版通知
Note:
公告:
     ◎ 主人目前外出中,招待不周,敬請見諒。
     ◎ 如有急事,請至Plurk or FaceBook找尋主人行蹤。
     ◎ 原則上,文章寫完就寫完了,更新的機率不大,請大家在找資料的同時,確認一下資料的正確性喔。 Orz
=============================================================
使用規則:
     1. 文章轉載請來信/留言告知主人!
     2. 各篇文章歡迎大家留言討論,謝絕任何形式廣告文。
=============================================================


我個人覺得,這是一個很神奇的函式,而且宇宙無敵難懂
除了VLOOKUP以外,還有個雙胞胎函式HLOOKUP
差別只在於,VLOOKUP是查直式表格↓,HLOOKUP是查橫式表格→
不過,今天只簡單介紹VLOOKUP。

VLOOKUP_s.PNG  


VLOOKUP語法: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 
填寫方式:          VLOOKUP(                       ,                 ,                         ,                       )


lookup_value
在表格陣列的第一欄中搜尋的數值Lookup_value 可以是數值,也可以是參照位址。
當 lookup_value 小於 table_array 第一欄中的最小值時,VLOOKUP 將會傳回錯誤值 #N/A。
==> 註:簡單來說,就是你輸入的那個欄位,如上圖,我要在Keyin表格中的A3輸入查詢資料,
              那lookup_value 位置填空就填入 A3

填寫方式: VLOOKUP(  A3   ,                  ,                       ,                      )


Table_array
兩欄以上的資料。
請使用參照位址來指向某個範圍或範圍名稱。
table_array 第一欄中的值即為 lookup_value 所搜尋的值。
這些值可以是文字、數字或邏輯值 (不分大小寫)。
==> 註:簡單來說,就是你要查詢的表格位置,像我要查詢上圖中右邊的查詢表格裡的資料,
              他目前的欄位是從 D2~E4 ,所以 table_array 位置填空就要填入 D2:E4

填寫方式: VLOOKUP(  A3   ,  D2:E4  ,                       ,                      )

col_index_num
table_array 中的欄號;相符的值將從該欄中傳回。
如果 col_index_num 引數值為 1,則傳回 table_array 第一欄中的值;
如果 col_index_num 引數值為 2,則傳回 table_array 第二欄中的值,依此類推。
如果 col_index_num:
   小於 1,則 VLOOKUP 會傳回錯誤值 #VALUE!。
   大於 table_array 中的欄數,則 VLOOKUP 會傳回錯誤值 #REF!。
==> 簡單來說,就是你要查詢的表格D2~E4中,要"顯示"的資料
        拿圖例來看,我輸入統一編號(A3)後,要查出公司名稱,公司名稱在表格的第二欄,
        所以 col_index_num 位置填空就要填入 2

填寫方式: VLOOKUP(  A3   ,  D2:E4  ,  2   ,                      )

range_lookup
一個邏輯值,用來指定 VLOOKUP 應該要尋找完全符合還是部分符合的值:
如果此引數值為 TRUE 或被省略了,則傳回完全符合或部分符合的值。
如果找不到完全符合的值,將會傳回僅次於 lookup_value 的值。
table_array 第一欄中的值必須以遞增順序排序;否則,VLOOKUP 可能無法提供正確的值。
只要在 [資料] 功能表中選取 [排序] 指令,再選取 [遞增],即可將值以遞增順序排序。
如需詳細資訊,請參閱預設排序順序。

如果此引數值為 FALSE,則 VLOOKUP 函數只會尋找完全符合的值。
在此情況下,table_array 第一欄中的值便不需要排序。
如果 table_array 第一欄中有兩個以上的值與 lookup_value 相符,將會使用第一個找到的值。

如果找不到完全符合的值,則傳回錯誤值 #N/A。
==> 簡單來說,range_lookup 位置填空填入 1 or false 就好了。(這樣會不會太簡單?)

填寫方式: VLOOKUP(  A3   ,  D2:E4  ,  2  ,  1  )


寫到這,我們已經把整串函式拼出來了,
完整寫法為:

VLOOKUP(A3,D2:E4,2,1)


如何,有沒有簡單一點點了?


欄位資料來源: Microsoft Office 函式搜尋 VLOOKUP




多欄參照補充:

有時候我們可能不只需要一個參照欄位,而是希望帶入公司統編後,可以將公司名稱、電話、聯絡人等資料一起帶入。
那這樣可以參考下列方式,不過,我只寫到第三欄,後續第四、五、六、七欄請以此類推。
不過建議且提醒大家,因為要方便解釋,所以我的Key in表格和參照表格在同一個Sheet,
真正要做到多欄參照,還是將Key in表格和參照表格分開放比較好喔。


2012-3-1 下午 05-54-31      


公司名稱 (B4) 的寫法如果是  =VLOOKUP(A4,E:G,2,0)  ,
那聯絡人 (C4) 的寫法就會變成  =VLOOKUP(A4,E:G,3,0)
如果有第五欄、第六欄,也是以此類推。
 


--

MURMUR:
特別寫下一篇,希望之後要用到查表不會再忘了   Orz


  

Posted by DropAngel at 痞客邦 PIXNET 留言(9) 引用(4) 人氣()


open trackbacks list Trackbacks (4)

  • nursing faculty jobs

    [EXCEL] 查表函式 VLOOKUP @ …漫‥遊… :: 痞客邦 PIXNET ::
  • welding jobs

    [EXCEL] 查表函式 VLOOKUP @ …漫‥遊… :: 痞客邦 PIXNET ::
  • welding job

    [EXCEL] 查表函式 VLOOKUP @ …漫‥遊… :: 痞客邦 PIXNET ::

留言列表 (9)

Post Comment
  • irenebaby525
  • 請問我一次想要帶出兩個欄位的值,以上述例子來說 我在公司後面新增一個連絡人欄位,我希望輸入統一編號 想帶出公司跟連絡人,這樣的公式該怎樣設定?
  • 帶出多欄值的方式有補充在文末了唷。 :)

    呃...如果寫的不清楚,請一定要告訴我。 Orz

    DropAngel replied in 2012/03/01 18:19

  • zack li
  • penny 好棒哦!加油~~~
  • 哇哈哈哈哈哈~~~


    其實真的只是不寫會忘記 Orz

    DropAngel replied in 2012/03/02 01:23

  • zack li
  • 那我可以教妳 SPC 嗎??下次幫我算SIGMA好了...YA YA YA~~~~
  • 啥!!? SPC是什麼?? 可以吃嗎??
    Orz
    你到底為什麼要算SIGMA啊....這個不是只有唸書的時候用到喔 (抱頭)

    DropAngel replied in 2012/03/02 01:23

  • irenebaby525
  • 很清楚的描述,幫助我清楚理解VLOOKUP的運用,真的很謝謝你!!!
  • : )

    DropAngel replied in 2012/03/06 02:28

  • 靖宏科技
  • 你好~~~ 我按你說的方式,但是帶出來的資料不對耶
    我的資料是一個檔案,多個活頁簿
    廠商資料是一個獨立的活頁簿
    另外做一個活頁簿標名為報價
    但是
    我在客戶名稱中鍵入A資料
    以該公式查找帶出該客戶的地址、電話、傳真、統編、E-mail等資料
    卻帶出B資料耶
    我的公式如下
    =VLOOKUP(G6,經銷商!A1:I70,9,1)
    是有那裡錯了嗎
  • 以公式來看,是正確的,再加上你其實有帶出資料,所以公式應該沒有下錯。
    不過,帶錯欄位的話,有可能是範圍設定錯誤,你可能要再檢查一下欄位的部分 @-@

    DropAngel replied in 2014/01/02 15:19

  • 皮皮
  • 這樣解說vlookup讓人清楚易懂,但如果我要參照的表格有2個(例員工資料(欄位較多A-N)、眷屬資料(欄位A-E)),該如何下公式呢?因為主辦公司旅遊,每次都為了key in資料浪費很多時間,如果事先作好員工及眷屬的資料,只要在出團的當梯表格key in員工編號或眷屬身份証號就能帶出姓名、出生年月日、電話,就能省很多時間,只用vlookup可以辦得到嗎?或是還要在搭配其他函數?
  • 可以 @-@ 如果你只是單純要帶資料的話,vlookup可以滿足你的需求。

    假設員工資料是SheetA,眷屬資料是SheetB
    需要資料為員工編號(A)(SheetA:A欄)、員工姓名(B)(SheetA:B欄)、員工生日(C)(SheetA:C欄)、電話(D)(SheetA:D欄)、眷屬身分證(E)(SheetB:A欄)、眷屬姓名(F)(SheetB:B欄)

    那這樣的話,員工編號為輸入值,就是要輸入的地方,員工姓名、生日、電話下vlookup參照SheetA的相關欄位資料。
    (EX: 員工編號(A1)輸入 12345,那員工姓名(B1)就要下vlookup 參照 Sheet A 員工姓名(B)
    =VLOOKUP(A1,SheetA!A:N,2,0) --> 員工姓名在B欄,是第2欄
    生日C1 =VLOOKUP(A1,SheetA!A:N,3,0) --> 員工生日在C欄,是第3欄 以此類推....)
    眷屬身分證為輸入值,相同的,眷屬姓名等資料,就參照Sheet B的相關欄位
    (EX: 眷屬身分證(E1)輸入 A123456789,那眷屬姓名(F1)就要下vlookup 參照 Sheet B 眷屬姓名(B)
    =VLOOKUP(E1,SheetB!A:E,2,0) --> 眷屬姓名在B欄,是第2欄 以此類推....)

    不過記得,輸入值要是唯一不會重複的,不然資料找出來很容易會錯掉喔 @-@
    (EX: 身分證、員工代號,這些不會有其他人重複的,就可以拿來當輸入值)

    以上,希望可以解決你的問題 :)

    DropAngel replied in 2014/08/03 02:49

  • 皮皮
  • 您好,謝謝您這麼清楚明瞭的回答,之前在出團的各個梯次表的B2(姓名)下這樣的公式=IF(F2" "," ",VLOOKUP(F2,員工!$A$2$N$2961,6,0)),我的問題是B2這個公式要如何改才能讓我在F2 KRY員工編號它就帶出員工姓名,KEY眷屬的身份証字號它就能帶眷屬的姓名,也就是說F2要能找完員工的資料找不到後,還能再去找眷屬的資料.....,期望您能為我解惑,感謝!
  • 實驗了很久,發現這樣的方法應該可行......

    =IF(IF(ISNA(VLOOKUP(F2,員工!$A$2$N$2961,6,0)),0,VLOOKUP(F2,員工!$A$2$N$2961,6,0))=0,VLOOKUP(F2,眷屬!$A$2$N$2961,6,0),IF(ISNA(VLOOKUP(F2,員工!$A$2$N$2961,6,0)),0,VLOOKUP(F2,員工!$A$2$N$2961,6,0)))
    解釋一下上面IF來VLOOKUP去的複雜公式

    我是用假設F2輸入的資料,是眷屬資料去反推的。

    IF ( _____ , _____ , ______ )

    首先先把F2欄位中,因為VLOOKUP查不到資料,會變成#N/A的這個部分,用ISNA函式判斷,改成0。
    簡單來說,就是只要是 #N/A 就會直接顯示 0。這是第一區邏輯判斷中輸入的資料。

    IF(ISNA(VLOOKUP(F2,員工!$A$2$N$2961,6,0)),0,VLOOKUP(F2,員工!$A$2$N$2961,6,0))=0

    好,再來,假設我輸入一筆的資料是眷屬資料,員工資料查不到變成0,所以帶入眷屬的資料

    VLOOKUP(F2,眷屬!$A$2$N$2961,6,0)

    最後,如果F2這筆資料,就是員工資料了,那也不會顯示0,所以就直接帶入員工資料。

    VLOOKUP(F2,員工!$A$2$N$2961,6,0)


    以上,你可以試試看這個方法適不適用。

    DropAngel replied in 2014/08/07 15:56

  • 皮皮
  • 大大地感謝您不吝賜教,解決了我多年的問題,真的謝謝您!
  • 哈哈,有解決你的問題就好了 :)
    也算是給我一個腦力激盪的機會啊 >///<

    DropAngel replied in 2014/08/11 13:43

  • Private Comment

You haven’t logged in yet, please use guest status to leave message. You can also log in with above service account and leave message

other options