Vlookup函數除了查詢引用,它的VBA使用你會嗎

2020-12-22 螞蟻可可V課堂

在工作中，我們在處理大量數據時，會有這樣一種需求，即查詢某個人或某個商品的一個信息。如果我們通過滾動用肉眼去看難免非常的痛苦，畢竟數據太多，那麼聰明的人是怎麼做的呢？對，就是用vlookup函數來實現。本章就圍繞vlookup的查詢引用以及通過VBA返回多個值的方法進行講解。

01基礎知識

Vlookup函數在excel中稱之為垂直查詢函數，即在數據表中從上往下查詢滿足條件的值。

函數格式為：VLOOKUP(lookup_value，table_array，col_index_num，[range_lookup])

中文說明：VLOOKUP （你想要查找的內容，要查找的單元格區域，要返回的值的區域中的列號，查找方式）。

其中具體參數說明：

你想要查找的內容（Lookup_value）是必須的參數，指的是要在表格或區域的第1列中搜索到的值。

要查找的單元格區域（table_array）是必須的參數。即要查找的數據所在的單元格區域，單元格區域的第1列中的值就是你要查找的內容要搜索的值。

要返回的值的區域中的列號（col_index_num）也是必須的參數。指的是返回數據所在的列號。列號為1時，返回單元格區域中第1 列的值；列號為2時，返回單元格區域中第2列的值，以此類推。如果列號參數小於1，則VLOOKUP返回錯誤值#VALUE！；大於單元格區域的列數，則VLOOKUP返回錯誤值#REF！

查找方式（range_lookup）為可選的參數。是一個邏輯值，取值為1/TURE或0/FALSE，指定希望VLOOKUP查找精確匹配值還是近似匹配值；如果查找方式為TRUE或被省略，則返回近似匹配值。如果找不到精確匹配值，則返回小於你想要查找的內容的最大值。如果查找方式參數為FALSE，VLOOKUP將只查找精確匹配值。如果單元格區域的第1列中有兩個或更多值與你想要查找的內容匹配，則使用第一個找到的值。如果找不到精確匹配的值，則返回錯誤值#N/A。

比如我們要查詢姓名為張山的性別就可以這樣寫：=vlookup(「張山」,A2:D6,2,1)；vlookup函數就會在單元格區域中的第一列找到張山，然後去返回第二列中性別的值。到這裡vlookup查詢的理論就講完了，但是如果細心的同學會發現，我們的vlookup一次只能返回查找的一個值，如果我們要把張山的性別、學歷、是否畢業同時都查詢出來呢，這時我想很多同學都會想到，使用多個vlookup函數就可以了。但是如果要查的值很多呢？那麼多次書寫vlookup函數是不是就非常麻煩呢。好了，接下來就給大家介紹通過vlookup函數實現多列值的返回查詢。

02Vlookup函數的VBA操作

使用VBA來操作vlookup函數與在excel中一樣，即參數相同、功能相同、用法相同，但是唯一不同的是通過VBA能夠簡化VBA的書寫，同時實現更強大的功能。要通過VBA操作vlookup函數就必須得使用Application.WorksheetFunction屬性來操作excel常用函數。由於在剛才的例子中我們要查詢性別、學歷、是否畢業三個值，因此我們的vlookup函數要執行三次循環。為了簡化代碼書寫，第一步，我們先用Set Sheet1 = Application.ThisWorkbook.Worksheets("sheet1")給查詢表重命名一個變量為sheet1，然後通過sheet1去調用cells和range數據去指定查詢值單元格、查詢單元格區域以及返回數據存儲的單元格。

第二步，使用for循環去循環執行查詢，由於我們需要返回的值是3列，且是從第二列開始的，因此循環變量從2開始，循環三次結束，所以如果我麼返回的值比較多，就根據返回值的列數來控制for循環。第三步，使用Application.WorksheetFunction.VLookup(Sheet1.Cells(1, 8), Sheet1.Range("A2:D6"), i, 1)操作vlookup函數，用法和excel中一樣，照著操作即可。

代碼如下：

Private Sub CommandButton1_Click()

Dim i As Integer '定義變量

Set Sheet1 = Application.ThisWorkbook.Worksheets("sheet1") '設置工作表

For i = 2 To 4

Sheet1.Cells(i, 8) = Application.WorksheetFunction.VLookup(Sheet1.Cells(1, 8), Sheet1.Range("A2:D6"), i, 1)

'執行查詢，如果i為2，則返回第二列值，如果i為3，則返回第三列值，以此類推。

Next

End Sub

總結：vlookup函數是一個非常強大的函數，除了查詢使用，還能合併表格，多條件查詢等，因此掌握基礎用法和VBA操作excel常用函數非常重要。

相關知識閱讀：

Excel VBA操作電子表格兩大重要屬性詳解

如何通過VBA的range、cells兩大屬性操作Excel單元格詳解

Excel VBA編程中必會的三大執行語句

相關焦點

  • EXCEL表格詳解vlookup第五彈-VBA自己寫myLookup函數
    我們在前四彈詳細介紹了vlookup函數，以及vlookup函數實現不了後，使用index為主函數加match，small，if等函數的組合拳來實現複雜的條件查找。由於大家從事的工作領域不同，有時候還需要實現更加複雜的查找，這時往往在幾個函數之間相互切換，相互套用，難免給一些相對固定的查找帶來不必要的麻煩。下面我就手把手地帶大家寫一個自己的函數myLookup() ，使用這一個函數來實現前四彈的所有功能。
  • EXCEL公式函數系列 之 查找與引用函數VLOOKUP
    大家好，我們已經學習完了EXCEL中的邏輯函數，從今天開始我們開始學習查找與引用這一系列的函數。今天的標題有點變動，是的，把「教學」去掉了。通過這幾天做文章發現EXCEL博大精深，大家共同學習進步吧。今天我們學習上次提到的VLOOKUP函數。
  • Excel表格vlookup函數搭配match函數詳解
    大家好，我是涼涼老師，今天給大家分享一下Excel表格vlookup函數搭配match函數的用法，首先看圖：vlookup用法上面的表格是右邊的數據，根據名稱在左邊數據裡面查詢對應的數據，=iferror(vlookup($f2,$a$1:$d$6,2,0),"")首先我們先來分析一下這個函數
  • 4個vlookup函數的高階用法,估計你一種都沒用過
    Hello，大家好，vlookup函數相信大家都非常的熟悉，今天就跟大家分享4個vlookup函數的高階用法，學會它們，你就是公司的大神了。amp;IFERROR("、"&VLOOKUP(A2,A3:$C$10,3,0),"")然後向下填充到倒數第二個單元格的位置也就是C9單元格，然後在最後一個單元格輸入=B10，就是最後一個單元格對應的姓名緊接著我們只需要在旁邊新建一班到三班的數據區域，輸入函數：=VLOOKUP(E3,A:C,3,0)，向下填充即可得到正確額結果，在這裡我們是將第一步查詢到的結果引用到我們新建的區域中
  • 先學完這個技巧,再理解Vlookup函數一對多查詢就簡單了
    我們模擬一個一對多查詢的例子：左邊是部門及員工姓名數據，我們需要根據部門，來查找出所有的員工姓名我們在使用VLOOKUP函數進行查找匹配的時候，如果源數據中有多個值時，它只會查找出第一個值，比如我們在E2輸入的公式：=VLOOKUP(D2,A:B,2,0)
  • 這篇通俗實用的Vlookup函數教程,5分鐘就可以包你一學就會
    如何利用Vlookup函數獲取學號中的班級信息。換言之，咱們源數據中放著姓名性別學號班級等信息，而在另一張表格中一定有學號信息，但其他信息就未必有，這需要我們將缺失的信息自動同步過去。使用vlookup函數的確非常簡單，今天我就再次來剖析一下這個函數吧。
  • INDEX+MATCH函數真的能替代VLOOKUP函數嗎?我看未必!
    ，如查詢範圍是A1:F16,而英語成績在引用範圍的中第4列，公式通常會這麼寫：=VLOOKUP(「韓嵩」,A1:F16,4,0),而題目要求變了，科目變成下拉菜單，成績需要根據選擇的科目顯示，那麼我們該如何處理呢？
  • excel中的經典查找引用函數之lookup函數的使用
    excel表格的眾多函數中，我們常用的查找引用函數大致有3個，分別是有縱向查找功能的vlookup函數、有橫向查找功能的hlookup函數和可以任意多條件查找引用的lookup函數。下面我們來介紹lookup函數的用法。
  • 學會VLOOKUP函數的一個隱藏參數 告別 N/A錯誤值
    二、VLOOKUP函數的來源vlookup函數是【查找】【引用函數】，而vlookup函數【第二參數】不好理解，是因為，第二參數有個隱藏的一個【要點】，想要把VLOOKUP函數學透徹，必學先了解這個函數是來源，最開始是沒有這個函數的，是match【查找函數】和Index【引用函數】這兩個函數簡化成了一個vlookup查找引用函數，所以在學vlookup之前，先簡單了解下
  • lookup函數的使用方法,含向量和數組形式實例及與vlookup的區別
    以下是就 Excel lookup函數的使用方法，列舉了向量形式和數組形式兩種實例，並且分享了 lookup 與 vlookup 的區別，實例中操作所用版本均為 Excel 2016。一、lookup函數向量形式使用方法lookup函數向量形式是在一行或一列中查找值，返回另一行或另一列對應位置的值。
  • 查找總是出錯:別為難vlookup函數了,還是讓lookup函數來吧
    有朋友提到在使用vlookup函數查找的時候結果為什麼是錯的？明明查找規格都是一樣的，並且沒有任何格式差別，怎麼會出錯了呢？看到朋友發來的文件才知道原來是通配符惹的禍？在excel中星號（*）是有特別意義的，如果你需要查找的內容中包含星號，那麼星號的本身是（~*），這樣才能正確查找！非通配符：~加在通配符*或？前，此時*或？
  • 查詢函數Choose、Lookup、Hlookup、Vlookup應用技巧解讀
    Excel中的查找和引用函數主要用於查找工作表中的所需內容，還可以獲得工作表中的單元格位置或表格大小等信息，如果將查找和引用函數配合其他的Excel函數使用，將會發揮更強大的功能。常用的查詢表中的數據函數有：引用表中數據的函數有：一、Choose函數。
  • vlookup一對多查詢的4種解法
    職場快與慢-第18期前幾天，財務突然QQ上找我說有要事相商，當時我內心恐懼，小劇場一幕幕上演，難道這個月要拖欠工資了，難道我的報銷填寫錯誤了，我懷揣著一顆不安定的心，來到財務辦公室，原來財務再處理一個髮票問題時遇到了麻煩，於是我協助財務一起解決了整個問題；那麼今天我們要講的職場小技巧就是Excel查詢1對多的4種解法關注：公眾號/職場快與慢
  • Excel中Vlookup函數不能做的,lookup函數輕輕鬆鬆完成!
    工作中，vlookup函數是大眾情人，但也有它完成不了的工作，這個時候用lookup函數便可以輕鬆的完成！1、查找最後一條記錄例如公司會持續進不同的物品，不同的數量，現在我們需要求出最後一次進貨的數量是多少？
  • VLOOKUP查詢時為什麼會出現 N/A?原來知道這6種解決方法這麼重要
    剛開始學VLOOKUP函數的小夥伴，經常會遇到錯誤值#N/A，什麼情況下會出現錯誤值？出現錯誤值我們應該如何去排查原因？很多小夥伴一直找不到根源。出現錯誤值#N/A，大概有下面這5個原因！一、查找值在數據源中不存在。
  • 一文講解vlookup函數的基本使用方法,正向逆向,多種方法教給你
    記得在初學Excel的時候，遇到兩大頭疼的問題，一是vlookup函數學不會，二是透視表不會用，而這兩大數據處理利器，在工作當中十分實用，可以說不會這兩個，那就不算會用Excel。今天就來說說如何快速入門vlookup函數！
  • 逆向查詢中IF函數和數組的用法,你理解嗎?這有原理了解一下!
    最近收到資深粉絲髮來的私信：廖老師，我對於VLOOKUP的逆向查詢中使用IF和數組用法不太理解，您能詳解一下嗎？當然，所以今天就抽時間來扒一下，隱藏在IF函數和數組用法裡的是什麼樣的原理呢？不過在回答問題之前，我還是先來重溫一下IF函數的基本用法。
  • vlookup常用套路,自動生成數據原來這麼簡單,一會百會
    今天分享一下在Excel中幾種常用的vlookup套路，自動生成數據快捷迅猛，再不會我才不信呢-。-有個好消息！為了方便大家更快的掌握技巧，尋找捷徑。幫幫準備了海量模板素材免費下載。百度「模板終結者」，三個W點2xx點vip，我等著你噢(老熟人都懂得)^^我們來看範例圖片，根據學號快速查詢成績，咱們記住vlookup函數查詢值（學號），一定要在被查詢區域的首列。
  • 不會vba編程,學會這些方法,少走許多彎路,函數式編程
    vba 可以實現你想實現的工作方式，比如它引入了對象，窗體對象、控制項對象、name對象、style對象，windows對象等等。實際上這是一種面向對象的編程，簡單來說就是了解各熟悉各種各樣的對象，然後這些對象有各自的屬性，可以自由地對這些屬性進行設置，再用邏輯語句將這些對象進行方法調用。
  • VBA基礎技能提升,如何快速引用函數公式
    在日常工作中，Excel裡面很多函數我們都用過，比如求和函數sum和sumif，計數函數count和countif。同樣在VBA裡面也有一些函數，我們稱之為VBA函數，我們經常在工作表裡面使用的函數稱之為工作表函數。