如何在 Excel 中使用 VLOOKUP

已發表: 2020-06-04

您是否曾經在 Excel 中使用過包含數據的大型電子表格，並且需要一種簡單的方法來從中過濾和提取特定信息？ 如果您了解如何在 Excel 中使用 VLOOKUP，則只需一個強大的 Excel 函數即可完成此查找。

Excel中的VLOOKUP函數讓很多人感到害怕，因為它有很多參數，並且有多種使用方法。 在本文中，您將了解在 Excel 中使用 VLOOKUP 的所有方法以及該功能為何如此強大的原因。

目錄

    Excel 中的 VLOOKUP 參數

    當您開始在 Excel 中的任何單元格中鍵入=VLOOKUP(時，您將看到一個顯示所有可用函數參數的彈出窗口。

    讓我們檢查每個參數及其含義。

    • lookup_value ：您要從電子表格中查找的值
    • table_array ：要搜索的工作表中的單元格範圍
    • col_index_num ：要從中提取結果的列
    • [range_lookup] ：匹配模式（TRUE = 近似，FALSE = 精確）

    這四個參數使您可以對非常大的數據集中的數據進行許多不同的、有用的搜索。

    一個簡單的 VLOOKUP Excel 示例

    VLOOKUP 不是您可能學過的基本 Excel 函數之一，所以讓我們看一個簡單的示例來開始。

    對於以下示例，我們將使用美國學校的 SAT 成績大型電子表格。 該電子表格包含 450 多所學校以及個人 SAT 閱讀、數學和寫作成績。 隨意下載跟隨。 有一個外部連接可以拉取數據，因此打開文件時會收到警告，但它是安全的。

    搜索如此龐大的數據集以找到您感興趣的學校將非常耗時。

    相反，您可以在表格一側的空白單元格中創建一個簡單的表單。 要進行此搜索，只需為學校創建一個字段，並為閱讀、數學和寫作分數創建三個附加​​字段。

    接下來，您需要使用 Excel 中的 VLOOKUP 函數來使這三個字段起作用。 在閱讀字段中，創建 VLOOKUP 函數，如下所示：

    1. 輸入 =VLOOKUP(
    2. 選擇 School 字段，在本例中為I2 。 鍵入逗號。
    3. 選擇包含要查找的數據的整個單元格區域。 鍵入逗號。

    選擇範圍時，您可以從用於查找的列（在本例中為學校名稱列）開始，然後選擇包含數據的所有其他列和行。

    注意：Excel 中的 VLOOKUP 功能只能通過搜索列右側的單元格進行搜索。 在此示例中，學校名稱列需要位於您正在查找的數據的左側。

    1. 接下來，要檢索閱讀分數，您需要從最左邊的選定列中選擇第三列。 因此，鍵入3 ，然後鍵入另一個逗號。
    2. 最後，鍵入FALSE以獲得完全匹配，並使用 a )關閉該函數。

    您最終的 VLOOKUP 函數應如下所示：

     =VLOOKUP(I2,B2:G461,3,FALSE)

    當您第一次按 Enter 並完成該功能時，您會注意到 Reading 字段將包含一個#N/A 。

    這是因為 School 字段是空白的，VLOOKUP 函數找不到任何內容。 但是，如果您輸入要查找的任何高中的名稱，您將在該行中看到閱讀分數的正確結果。

    如何處理 VLOOKUP 區分大小寫

    您可能會注意到，如果您鍵入的學校名稱的大小寫與它在數據集中的列出方式不同，您將看不到任何結果。

    這是因為 VLOOKUP 函數區分大小寫。 這可能很煩人，尤其是對於一個非常大的數據集，其中您正在搜索的列與事物的大寫方式不一致。

    為了解決這個問題，您可以在查找結果之前強制將要搜索的內容切換為小寫。 為此，請在您正在搜索的列旁邊創建一個新列。 鍵入函數：

     =修剪（降低（B2））

    這將小寫學校名稱並刪除可能位於名稱左側或右側的任何無關字符（空格）。

    按住 Shift 鍵並將鼠標光標放在第一個單元格的右下角，直到它變為兩條水平線。 雙擊鼠標自動填充整列。

    最後，由於 VLOOKUP 將嘗試使用公式而不是這些單元格中的文本，因此您只需將它們全部轉換為值。 為此，請複制整個列，右鍵單擊第一個單元格，然後僅粘貼值。

    現在您的所有數據都已在此新列中清除，稍微修改 Excel 中的 VLOOKUP 函數以使用此新列而不是前一列，方法是從 C2 而不是 B2 開始查找範圍。

     =VLOOKUP(I2,C2:G461,3,FALSE)

    現在您會注意到，如果您始終以小寫形式輸入搜索內容，您將始終獲得良好的搜索結果。

    這是一個方便的 Excel 技巧，可以克服 VLOOKUP 區分大小寫的事實。

    VLOOKUP 近似匹配

    雖然本文第一部分中描述的精確匹配 LOOKUP 示例非常簡單，但近似匹配稍微複雜一些。

    近似匹配最適合用於搜索數字範圍。 要正確執行此操作，需要對搜索範圍進行正確排序。 最好的例子是 VLOOKUP 函數，用於搜索與數字等級對應的字母等級。

    如果老師有一長串全年的學生作業成績，並帶有最終平均列，那麼自動顯示與該最終成績相對應的字母成績會很好。

    這可以通過 VLOOKUP 功能實現。 所需要的只是右側的查找表，其中包含每個數字分數範圍的適當字母等級。

    現在，使用 VLOOKUP 函數和近似匹配，您可以找到與正確數字範圍相對應的正確字母等級。

    在這個 VLOOKUP 函數中：

    • lookup_value : F2，最終平均成績
    • table_array : I2:J8，字母等級查找範圍
    • index_column : 2，查找表中的第二列
    • [range_lookup] ：真，近似匹配

    在 G2 中完成 VLOOKUP 功能並按 Enter 後，您可以使用上一節中描述的相同方法填寫其餘單元格。 您會看到正確填寫的所有字母等級。

    請注意，Excel 中的 VLOOKUP 函數從具有指定字母分數的成績範圍的底端搜索到下一個字母分數的範圍的頂端。

    所以，“C”需要是分配給較低範圍（75）的字母，而B被分配給它自己的字母範圍的底部（最小）。 VLOOKUP 將“找到” 60 (D) 的結果作為 60 到 75 之間任何值的最接近的近似值。

    Excel 中的 VLOOKUP 是一個非常強大的功能，已經使用了很長時間。 它對於在 Excel 工作簿中的任何位置查找匹配值也很有用。

    但是請記住，每月訂閱 Office 365 的 Microsoft 用戶現在可以訪問更新的 XLOOKUP 功能。 這個函數有更多的參數和額外的靈活性。 半年訂閱的用戶將需要等待更新在 2020 年 7 月推出。