03年3月
使用Excel的VLOOKUP查找數據的近似匹配
VLOOKUP函數如何工作
Excel的VLOOKUP 功能代表垂直查找 ，可用於查找位於數據表或數據庫表中的特定信息。
VLOOKUP通常返回單個數據字段作為其輸出。 它是如何做到的：
- 您提供了一個名稱或lookup_value ，它告訴VLOOKUP數據表的哪一行或哪條記錄查找所需的數據
- 您提供您所尋找的數據的列號（稱為col_index_num）
- 該函數在數據表的第一列中查找lookup_value
- VLOOKUP然後使用提供的列號找到並返回您從同一記錄的另一個字段查找的信息
首先對數據進行排序
雖然並不總是必需的，但通常最好先對 VLOOKUP正在使用排序鍵範圍的第一列以升序搜索的數據范圍進行排序。
如果數據未排序，則VLOOKUP可能會返回錯誤的結果。
VLOOKUP函數的語法和參數
VLOOKUP函數的語法是：
= VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）
lookup _value - （必填）要搜索的值 - 例如上圖中銷售的數量
table_array - （必填）這是VLOOKUP搜索以查找您之後的信息的數據表。
- table_array必須包含至少兩列數據
- 第一列通常包含lookup_value
col_index_num - （必填）要找到的值的列號。
- 編號以search_key列作為第1列開始
- 如果col_index_num被設置為一個大於table_array參數中選擇的列數的數字，則#REF！ 錯誤是由函數返回的
range_lookup - （可選）指示範圍是否按升序排序。
- 第一列中的數據用作排序關鍵字
- 布爾值 - TRUE或FALSE是唯一可接受的值
- 如果省略，默認情況下該值設置為TRUE
- 如果設置為TRUE或省略，並且範圍的第一列未按升序排序，則可能會出現錯誤的結果
- 如果設置為TRUE或省略，並且找不到查找_value的精確匹配項，則使用大小或值較小的最近匹配項作為search_key
- 如果設置為FALSE，則VLOOKUP僅接受查找_值的完全匹配。 如果有多個匹配值，則返回第一個匹配值
- 如果設置為FALSE並且找不到search_key的匹配值，則該函數會返回＃N / A錯誤
示例：找到購買數量的折扣率
上圖中的示例使用VLOOKUP函數來查找根據購買物品數量而變化的折扣率。
示例顯示購買19件商品的折扣為2％。 這是因為數量列包含值的範圍。 因此，VLOOKUP找不到完全匹配。 相反，必須找到近似匹配才能返回正確的折扣率。
尋找近似匹配：
- 按升序對table_array中的數據進行排序;
- 將range_lookup參數設置為TRUE
在該示例中，使用包含VLOOKUP函數的以下公式來查找所購商品數量的折扣。
= VLOOKUP（C2，$ C $ 5：$ d $ 8,2，TRUE）
即使這個公式只能被輸入到工作表單元格中，但與下面列出的步驟一起使用的另一個選項是使用函數的對話框輸入其參數。
- 使用對話框通常可以更容易地正確輸入函數的參數。
打開VLOOKUP對話框
用於將上圖中顯示的VLOOKUP函數輸入到單元格B2中的步驟如下：
02 03
輸入Excel的VLOOKUP函數的參數
指向單元格引用
VLOOKUP函數的參數被輸入到對話框的單獨行中，如上圖所示。
可以將用作參數的單元格引用輸入到正確的行中，或者，如以下步驟中所做的那樣，使用鼠標指針突出顯示期望範圍的單元格的指針可用於將它們輸入到對話框中。
使用指點的優點包括：
- 它比打字更快;
- 輸入正確的單元格引用的錯誤更少。
使用相對和絕對單元格引用和參數
使用VLOOKUP的多個副本從相同的數據表中返回不同的信息並不罕見。 為了簡化操作，VLOOKUP通常可以從一個單元復製到另一個單元。 當函數被複製到其他單元格時，必須注意確保函數的新位置產生的單元格引用是正確的。
在上面的圖片中，美元符號（ $ ）圍繞table_array參數的單元格引用，表示它們是絕對單元格引用 ，這意味著如果將該函數複製到另一個單元格 ，它們將不會更改。 這是可取的，因為VLOOKUP的多個副本都會引用與數據源相同的數據表。
另一方面，用於lookup_value的單元格引用不包含美元符號，這使得它成為相對單元格引用。 相對單元格引用會在復制時發生更改，以反映它們相對於所引用數據位置的新位置。
輸入函數參數
- 點擊VLOOKUP對話框中的Lookup _value行
- 單擊工作表中的單元格C2以輸入此單元格引用作為search_key參數
- 點擊對話框的Table_array行
- 突出顯示工作表中的單元格C5到D8，以便以Table_array參數的形式輸入此範圍 - 不包括表格標題
- 按下鍵盤上的F4鍵將範圍更改為絕對單元格引用
- 點擊對話框的Col_index_num行
- 在此行上輸入2作為Col_index_num參數，因為折扣率位於Table_array參數的第2列
- 點擊對話框的Range_lookup行
- 輸入True作為Range_lookup參數
- 按下鍵盤上的Enter鍵關閉對話框並返回工作表
- 答案2％（購買數量的折扣率）應出現在工作表的單元格D2中
- 當您單擊單元格D2時，完整函數= VLOOKUP（C2，$ C $ 5：$ D $ 8,2，TRUE）出現在工作表上方的公式欄中
為什麼VLOOKUP返回2％作為結果
- 在示例中，“ 數量”列不包含search_key值為19的精確匹配項。
- 由於is_sorted參數設置為TRUE，因此VLOOKUP將找到與search_key值大致匹配的值。
- 尺寸最小的值仍小於search_key值19，為11。
- 因此，VLOOKUP在包含11的行中查找折扣百分比，並因此返回2％的折扣率。
03年03月
Excel VLOOKUP無法正常工作：＃N / A和#REF錯誤
VLOOKUP錯誤消息
以下錯誤消息與VLOOKUP相關聯。
A＃N / A（“value not available”）顯示錯誤如果：
- 在range參數的第一列中找不到lookup _value
- Table_array參數不准確。 例如，參數可能包括範圍左側的空列
- Range_lookup參數設置為FALSE，並且無法在範圍的第一列中找到search_key參數的完全匹配
- Range_lookup參數設置為TRUE，並且範圍第一列中的所有值都大於search_key
#REF！ （“參考超出範圍”）顯示錯誤如果：
- Col_index_num參數大於Table_array中的列數