如何使用 Excel VBA 自動化 Vlookup

如何使用 Excel VBA 自動化 Vlookup

Vlookup是Excel中必備的功能,已成為資料處理的重要組成部分。它提供了許多可以與綜合資料庫結合使用的功能。

但是,如果您想自動執行此功能,則可以,特別是如果您知道如何使用 Excel 和 VBA 中提供的自動化語言。以下是如何在 Excel VBA 中自動執行 vlookup 函數

Excel中的Vlookup函數公式

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

公式中參數的意義:

  • lookup_value:您要在陣列中尋找的基礎值。
  • lookup_array:來源數據,lookup_value將作為參考。
  • column_index:列返回值。
  • exact_match/partial_match:使用0或1指定組合類型。

Excel中的VBA Vlookup函數

當您直接在 Excel 中或透過 VBA 使用 Vlookup 函數時,它的用法沒有太大差異。無論是資料分析師處理大量訊息,還是倉庫經理不斷處理新產品,vlookup 功能都很有用。

使用動態尋找範圍時,最好自動執行公式,以避免在 Excel 中多次操作相同公式。透過在VBA中自動化Vlookup函數,您可以一鍵執行多列計算。

插畫

此資料集有兩個部分:查找表和目標資料點。查找表包含 3 列,資料位於子類別和產品名稱兩個欄位:

如何使用 Excel VBA 自動化 Vlookup

目標表具有適當的列,沒有子資料夾或產品名稱資料。請注意,訂單 ID列中的值也出現在查找表的同一列中:

如何使用 Excel VBA 自動化 Vlookup

1. 進行必要的設置

首先在新的 Excel 工作簿中開啟程式碼編輯器(按Alt + F11或導航至「開發人員」標籤)並新增模組以開始編碼。在程式碼編輯器中,將以下行新增至程式設計視窗的頂部以建立子程式:

Sub vlookup_fn1()End Sub

子例程是 VBA 程式碼的容器,需要成功運行它。另一種選擇是建立 VBA 使用者窗體以使 UI 更具互動性。

2.宣告變數並建立引用範圍

首先,您需要使用 Dim 命令聲明變數的資料類型:

Dim i as integer, lastrow as long

接下來,建立一個變數lastrow,以儲存尋找範圍中最後填滿的行的值。lastrow函數使用end(xldown)函數來計算特定範圍內的最後一行參考。

在此範例中,lastrow變數包含尋找範圍中最後一個填充行的值,具體為 17。

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

在上面的範例中,目標表從 A2:C17 延伸,來源表從 F2:H17 延伸。vlookup 公式將循環遍歷 A 列中的每個值,在來源表中尋找它,並將組合條目貼到 B 列和 C 列。

但是,在進入循環之前,必須將範圍值儲存在新變數(my_range)中,如下所示:

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

您必須明確指定起始儲存格參考 (F3) 和結束列參考 (H)。VBA自動新增行值以完成尋找陣列。

如何使用 Excel VBA 自動化 Vlookup

3. 寫一個循環來循環遍歷每個查找值

在編寫循環之前,定義起始行值,2。處理動態元件時,必須為循環定義起始值。在目標表中,第 2 行是第一行資料。如果資料在不同的行中開始,請變更此值。

i = 2

您可以使用do-while循環來處理每一行,從第 2 行開始到第 17 行結束。與程式碼的其餘部分一樣,這方面很靈活;循環將一直運行,直到條件為假為止。使用條件測試目前行的第一個儲存格中的非空值。

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

在循環內部,您可以呼叫 Vlookup 函數來填充單元格:

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

這些指令分別設定目前行第2列和第3列單元格的值,它們使用WorksheetFunction物件呼叫Vlookup函數,將第1列中的對應值傳遞給搜尋。它們還傳遞您最初定義的範圍和相關列的索引來獲取最終值。

vlookup 程式碼已準備就緒,但您仍需要在每次循環中遞增行變數:

i = i + 1

每次循環運行時,它都會將值i增加1。請記住,起始行值為 2,每次循環運行時都會發生增量。使用loop關鍵字設定該程式碼區塊的結尾。

當整個程式碼運行時,它將根據來源表中的值用結果填入兩個欄位。

如何使用 Excel VBA 自動化 Vlookup

下面是完整的參考程式碼:

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. 建立一個按鈕來運行程式碼

您可以在 Excel 表格中建立一個按鈕來一鍵運行它。在空白 Excel 工作表上插入所需的形狀,右鍵單擊它,然後按一下「指派巨集」選項。

如何使用 Excel VBA 自動化 Vlookup

在對話方塊中,選擇子例程的名稱,然後按一下「確定」

如何使用 Excel VBA 自動化 Vlookup

當你想運行程式碼時,點擊按鈕即可立即看到資料是如何填寫的。

以上是如何使用Excel VBA實現自動化查找功能。希望這篇文章對您有用。


Excel中的IFERROR函數、公式及用法

Excel中的IFERROR函數、公式及用法

學習如何在 Excel 中使用非常常用的 IFERROR 函數,提升您的數據處理效率。

如何在 Excel 中使用 VSTACK 和 HSTACK 函數

如何在 Excel 中使用 VSTACK 和 HSTACK 函數

想知道如何在 Excel 中堆疊和附加資料嗎?讓我們一起學習如何使用 Microsoft Excel 中的 VSTACK 和 HSTACK 函數!

SUBTOTAL 函數:公式以及如何在 Excel 中使用 SUBTOTAL 函數

SUBTOTAL 函數:公式以及如何在 Excel 中使用 SUBTOTAL 函數

了解 Excel 中的 SUBTOTAL 函數及其用途,讓計算更簡單有效!

Excel中的除法函數:MOD函數(求餘數)與QUOTIENT函數(求整數部分)

Excel中的除法函數:MOD函數(求餘數)與QUOTIENT函數(求整數部分)

MOD函數是Excel中除法求餘的函數。學習如何輕鬆使用MOD函數和QUOTIENT函數來計算餘數和整數部分,提升您的Excel技能。

在 Excel 上玩“拼圖”

在 Excel 上玩“拼圖”

Reddit 用戶 MehmetSalihKoten 在 Microsoft Excel 中創建了俄羅斯方塊的全功能版本。

如何使用Excel中的轉換功能

如何使用Excel中的轉換功能

要轉換 Excel 中的測量單位,我們將使用 Convert 函數。

Excel中HLOOKUP函數的使用方法

Excel中HLOOKUP函數的使用方法

HLOOKUP 函數是什麼?如何在Excel中使用HLOOKUP函數?讓我們來和 LuckyTemplates 一起找出答案吧!

如何在 Excel 中使用 VALUE 函數

如何在 Excel 中使用 VALUE 函數

Excel中的VALUE函數是什麼?Excel中的數值公式是什麼?讓我們透過 LuckyTemplates.com 來了解一下吧!

Excel EOMONTH函數,EOMONTH函數如何使用

Excel EOMONTH函數,EOMONTH函數如何使用

Excel中的EOMONTH函數用於顯示給定月份的最後一天,實作非常簡單。然後,您將根據我們輸入的數據獲得特定月份的最後一天。

如何在 Excel VBA 中使用 do-while 循環

如何在 Excel VBA 中使用 do-while 循環

想要在 Excel VBA 中自動執行重複任務嗎?因此,讓我們學習如何使用 Do-While 迴圈來實作一系列重複操作,直到滿足條件。