Excel 中的 VLOOKUP 函數 - 初學者指南：語法和示例

今天我們開始發表一系列文章，描述 Excel 最有用的功能之一 - VPR （VLOOKUP）。 同時，此功能是最複雜和最不了解的功能之一。

在本教程中 VPR 我將嘗試盡可能簡單地列出基礎知識，以便讓沒有經驗的用戶盡可能清楚地了解學習過程。 此外，我們將研究幾個帶有 Excel 公式的示例，這些示例將演示該函數的最常見用例 VPR.

  • 一般描述和語法
  • VLOOKUP 示例
    • 如何使用 VLOOKUP 在另一個 Excel 工作表中進行搜索
    • 使用 VLOOKUP 在另一個工作簿中搜索
    • 如何使用 VLOOKUP 在公式中使用命名區域或表格
    • 在 VLOOKUP 公式中使用通配符
  • VLOOKUP 函數中的精確或近似匹配
  • Excel 中的 VLOOKUP - 您需要記住這一點！

Excel 中的 VLOOKUP 函數 – 一般描述和語法

那是什麼 VPR? 嗯，首先，它是一個 Excel 函數。 她在幹嘛？ 它查找您指定的值並從另一列返回相應的值。 從技術上講， VPR 在給定範圍的第一列中查找值，並從同一行的另一列返回結果。

在最常見的應用中，函數 VPR 在數據庫中搜索給定的唯一標識符，並從數據庫中提取一些與之相關的信息。

函數名中的第一個字母 VPR (VLOOKUP) 是指 В垂直的 （V垂直的）。 通過它你可以區分 VPR GPR （HLOOKUP），它在範圍的頂行中搜索一個值 - Г水平的 （H水平）。

功能 VPR 在 Excel 2013、Excel 2010、Excel 2007、Excel 2003、Excel XP 和 Excel 2000 中可用。

VLOOKUP 函數的語法

功能 VPR (VLOOKUP) 具有以下語法：

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

如您所見，一個函數 VPR 在 Microsoft Excel 中有 4 個選項（或參數）。 前三個是強制性的，最後一個是可選的。

  • Lookup_Array中 (lookup_value) – 要查找的值。 這可以是一個值（數字、日期、文本）或單元格引用（包含查找值），或者是某個其他 Excel 函數返回的值。 例如，此公式將查找值 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

如果查找值小於被查找範圍的第一列中的最小值，則函數 VPR 會報錯 ＃在 （#不適用）。

  • 表格數組 (table) – 兩列或多列數據。 記住，函數 VPR 總是在參數給定範圍的第一列中查找值 表格數組 （桌子）。 可視範圍可以包含各種數據，例如文本、日期、數字、布爾值。 該函數不區分大小寫，這意味著大小寫字符被認為是相同的。 所以我們的公式將尋找值 40 在細胞中 A2 至 A15，因為 A 是參數中給定的範圍 A2:B15 的第一列 表格數組 （桌子）：

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • Col_index_num為 (column_number) 是給定範圍內的列號，從該列號中返回找到的行中的值。 給定範圍內最左邊的列是 1，第二列是 2，第三列是 3 等等。 現在您可以閱讀整個公式：

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    尋找價值的公式 40 範圍中 A2：A15 並從 B 列返回相應的值（因為 B 是范圍 A2:B15 中的第二列）。

如果參數的值 Col_index_num為 (column_number) 小於 1然後 VPR 會報錯 ＃值！ （＃價值！）。 如果它超過範圍內的列數 表格數組 (table)，函數會返回錯誤 ＃REF！ （＃關聯！）。

  • 範圍查找 (range_lookup) – 確定要查找的內容：
    • 完全匹配，參數必須相等 假 （錯誤的）;
    • 近似匹配，參數等於 真實代碼 (TRUE) 或根本未指定。

    這個參數是可選的，但非常重要。 稍後在本教程中 VPR 我將向您展示一些示例，解釋如何編寫用於查找精確匹配和近似匹配的公式。

VLOOKUP 示例

希望功能 VPR 對你來說更清楚一點。 現在讓我們看一些用例 VPR 在具有真實數據的公式中。

如何使用 VLOOKUP 在另一個 Excel 工作表中進行搜索

在實踐中，具有函數的公式 VPR 很少用於在同一個工作表上搜索數據。 通常情況下，您會從另一張工作表中查找並檢索相應的值。

為了使用 VPR, 在另一個 Microsoft Excel 工作表中搜索，您必須在參數中 表格數組 （表）用感嘆號指定工作表名稱，後跟一系列單元格。 例如，下面的公式表明範圍 A2：B15 在一張名為 Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

當然，工作表名稱不必手動輸入。 只需開始輸入公式，當涉及到參數時 表格數組 （表格），切換到所需的工作表並用鼠標選擇所需的單元格範圍。

下面屏幕截圖中顯示的公式在工作表的 A 列（它是 A1:B1 範圍的第一列）中查找文本“產品 2” 價格.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

請記住，在搜索文本值時，必須將其括在引號 (“”) 中，這在 Excel 公式中通常是這樣做的。

論據 表格數組 （表）最好總是使用絕對引用（帶有 $ 符號）。 在這種情況下，將公式複製到其他單元格時，搜索範圍將保持不變。

使用 VLOOKUP 在另一個工作簿中搜索

發揮作用 VPR 在兩個 Excel 工作簿之間工作，您需要在工作表名稱前的方括號中指定工作簿名稱。

例如，下面是一個查找值的公式 40 在單子上 Sheet2 在這本書中 數字.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

這是在 Excel 中創建公式的最簡單方法 VPR鏈接到另一個工作簿：

  1. 打開兩本書。 這不是必需的，但以這種方式創建公式更容易。 您不想手動輸入工作簿名稱，是嗎？ 此外，它將保護您免受意外的拼寫錯誤。
  2. 開始輸入函數 VPR當談到爭論時 表格數組 （表），切換到另一個工作簿並在其中選擇所需的搜索範圍。

下面的屏幕截圖顯示了將搜索設置為工作簿中某個範圍的公式 價格表.xlsx 在單子上 價格.

功能 VPR 即使您關閉搜索到的工作簿並且工作簿文件的完整路徑出現在編輯欄中，也可以工作，如下所示：

如果工作簿或工作表的名稱包含空格，則必須用撇號括起來：

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

如何使用 VLOOKUP 在公式中使用命名區域或表格

如果您計劃在多個函數中使用相同的搜索範圍 VPR，您可以創建一個命名範圍並將其名稱作為參數輸入到公式中 表格數組 （桌子）。

要創建命名範圍，只需選擇單元格並在字段中輸入適當的名稱 名字，在公式欄的左側。

現在您可以寫下以下公式來查找產品的價格 產品1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

大多數範圍名稱適用於整個 Excel 工作簿，因此無需為參數指定工作表名稱 表格數組 （表），即使公式和搜索範圍位於不同的工作表上。 如果它們在不同的工作簿中，則需要在範圍名稱之前指定工作簿的名稱，例如，像這樣：

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

所以公式看起來更清晰，同意嗎？ 此外，使用命名範圍是絕對引用的一個很好的替代方法，因為當您將公式複製到其他單元格時，命名範圍不會改變。 這意味著您可以確保公式中的搜索範圍始終保持正確。

如果您使用以下命令將一系列單元格轉換為完整的 Excel 電子表格 枱燈 （表）選項卡 插入 （插入），然後當您用鼠標選擇一個範圍時，Microsoft Excel 會自動將列名（如果您選擇整個表，則為表名）添加到公式中。

完成的公式將如下所示：

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

或者甚至可能是這樣的：

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

使用命名範圍時，無論您將函數複製到何處，鏈接都將指向相同的單元格 VPR 工作簿內。

在 VLOOKUP 公式中使用通配符

與許多其他功能一樣， VPR 您可以使用以下通配符：

  • 問號 (?) – 替換任何單個字符。
  • 星號 (*) – 替換任何字符序列。

在函數中使用通配符 VPR 在許多情況下可能很有用，例如：

  • 當您不記得確切需要查找的文本時。
  • 當您想查找屬於單元格內容的某個單詞時。 我知道 VPR 按整個單元格的內容進行搜索，就像啟用了該選項一樣 匹配整個單元格內容 （整個單元格）在標準 Excel 搜索中。
  • 當單元格在內容的開頭或結尾包含額外的空格時。 在這種情況下，你可以絞盡腦汁想辦法弄清楚為什麼公式不起作用。

示例 1：查找以某些字符開頭或結尾的文本

假設您要在如下所示的數據庫中搜索特定客戶。 您不記得他的姓氏，但您知道它以“ack”開頭。 這是一個可以很好地完成工作的公式：

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

現在您確定您找到了正確的名稱，您可以使用相同的公式來查找該客戶支付的金額。 為此，只需更改函數的第三個參數 VPR 到所需的列號。 在我們的例子中，這是 C 列（範圍內的第 3 列）：

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

以下是更多使用通配符的示例：

~ 查找以“man”結尾的名字：

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ 查找以“ad”開頭並以“son”結尾的名稱：

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ 我們在列表中找到第一個名字，由 5 個字符組成：

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

發揮作用 VPR 通配符可以正常工作，作為您應該始終使用的第四個參數 假 （錯誤的）。 如果搜索範圍包含多個值與通配符的搜索詞匹配，則將返回找到的第一個值。

示例 2：在 VLOOKUP 公式中組合通配符和單元格引用

現在讓我們看一個稍微複雜一點的例子，說明如何使用函數進行搜索 VPR 按單元格中的值。 假設 A 列是許可證密鑰列表，B 列是擁有許可證的名稱列表。 此外，您在單元格 C1 中有某種許可證密鑰的一部分（幾個字符），並且您想查找所有者的姓名。

這可以使用以下公式完成：

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

此公式從給定範圍內的單元格 C1 中查找值，並從列 B 中返回相應的值。請注意，在第一個參數中，我們在單元格引用前後使用與號 (&) 字符來鏈接文本字符串。

如下圖所示，函數 VPR 返回“Jeremy Hill”，因為他的許可證密鑰包含單元格 C1 中的字符序列。

注意參數 表格數組 上面屏幕截圖中的 (table) 包含表 (Table7) 的名稱，而不是指定單元格範圍。 這就是我們在前面的例子中所做的。

VLOOKUP 函數中的精確或近似匹配

最後，讓我們仔細看看為函數指定的最後一個參數 VPR - 範圍查找 （間隔視圖）。 正如本課開始時提到的，這個論點非常重要。 你可以用它的值在同一個公式中得到完全不同的結果 真實代碼 (真) 或 假 （錯誤的）。

首先，讓我們了解一下 Microsoft Excel 中的精確匹配和近似匹配是什麼意思。

  • 如果論證 範圍查找 (range_lookup) 等於 假 (FALSE)，公式查找完全匹配，即與參數中給出的值完全相同 Lookup_Array中 （查找值）。 如果在範圍 t 的第一列能數組 (table) 遇到兩個或多個與參數匹配的值 Lookup_Array中 (search_value)，然後將選擇第一個。 如果沒有找到匹配，函數會報錯 ＃在 （#不適用）。 比如下面的公式會報錯 ＃在 (#N/A) 如果 A2:A15 範圍內沒有值 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • 如果論證 範圍查找 (range_lookup) 等於 真實代碼 (TRUE)，公式查找近似匹配。 更準確地說，首先是函數 VPR 查找完全匹配，如果沒有找到，則選擇一個近似匹配。 近似匹配是不超過參數中指定值的最大值。 Lookup_Array中 （查找值）。

如果論證 範圍查找 (range_lookup) 等於 真實代碼 (TRUE) 或不指定，則範圍第一列的值應按升序排列，即從小到大。 否則，函數 VPR 可能會返回錯誤的結果。

為了更好地理解選擇的重要性 真實代碼 （真相）或 假 (FALSE)，讓我們看看更多的公式與函數 VPR 並查看結果。

示例 1：使用 VLOOKUP 查找完全匹配

你還記得，要搜索完全匹配，函數的第四個參數 VPR 應該很重要 假 （錯誤的）。

讓我們從第一個例子回到表格，找出哪種動物可以快速移動 50 英里每小時。 我相信這個公式不會給你帶來任何困難：

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

請注意，我們的搜索範圍（A 列）包含兩個值 50 – 在細胞中 A5 и A6. 公式從單元格返回值 B5. 為什麼？ 因為在尋找完全匹配時，函數 VPR 使用找到的第一個與正在搜索的值匹配的值。

示例 2：使用 VLOOKUP 查找近似匹配

當您使用該功能時 VPR 搜索近似匹配，即當參數 範圍查找 (range_lookup) 等於 真實代碼 (TRUE) 或省略，您需要做的第一件事是按第一列升序對范圍進行排序。

這非常重要，因為函數 VPR 返回給定值之後的下一個最大值，然後搜索停止。 如果您忽略了正確的排序，您最終會得到非常奇怪的結果或錯誤消息。 ＃在 （#不適用）。

現在您可以使用以下公式之一：

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

如您所見，我想找出哪種動物的速度最接近 69 英里每小時。 這是函數返回給我的結果 VPR:

如您所見，公式返回了一個結果 羚羊 （羚羊），它的速度 61 每小時英里數，儘管該列表還包括 獵豹 （獵豹）奔跑的速度 70 英里每小時，70 比 69 更接近 61，不是嗎？ 為什麼會這樣？ 因為函數 VPR 搜索近似匹配時，返回不大於正在搜索的值的最大值。

我希望這些示例對使用該函數有所幫助 VPR 在 Excel 中，您不再將她視為局外人。 現在簡單地重複我們研究過的材料的關鍵點，以便更好地在記憶中固定它並沒有什麼壞處。

Excel 中的 VLOOKUP - 您需要記住這一點！

  1. 功能 VPR Excel 無法向左看。 它總是在參數給定範圍的最左列中查找值 表格數組 （桌子）。
  2. 在功能上 VPR 所有值都不區分大小寫，即大小寫字母是等價的。
  3. 如果您要查找的值小於要查找的範圍的第一列中的最小值，則函數 VPR 會報錯 ＃在 （#不適用）。
  4. 如果第三個參數 Col_index_num為 (column_number) 小於 1功能 VPR 會報錯 ＃值！ （＃價值！）。 如果它大於範圍內的列數 表格數組 (table)，函數會報錯 ＃REF！ （＃關聯！）。
  5. 在參數中使用絕對單元格引用 表格數組 （表），以便在復制公式時保留正確的搜索範圍。 嘗試在 Excel 中使用命名範圍或表格作為替代方法。
  6. 在進行近似匹配搜索時，請記住您要查找的範圍中的第一列必須按升序排序。
  7. 最後，記住第四個論點的重要性。 使用價值觀 真實代碼 （真相）或 假 (FALSE) 故意的，你會擺脫很多頭痛。

在我們功能教程的以下文章中 VPR 在 Excel 中，我們將學習更高級的示例，例如使用 VPR，從多列中提取值等等。 感謝您閱讀本教程，希望下週再次見到您！

發表評論