我們最近專門寫了一篇文章來介紹最有用的 Excel 函數之一，稱為 VPR 並展示瞭如何使用它從數據庫中提取所需信息到工作表單元格中。 我們還提到該函數有兩個用例 VPR 其中只有一個處理數據庫查詢。 在本文中，您將學習另一種鮮為人知的使用該函數的方法 VPR 在Excel中。
如果你還沒有這樣做，那麼一定要閱讀上一篇關於函數的文章 VPR，因為以下所有信息都假設您已經熟悉第一篇文章中描述的原理。
使用數據庫時，函數 VPR 傳遞一個唯一標識符，用於標識我們要查找的信息（例如，產品代碼或客戶識別號）。 此唯一代碼必須存在於數據庫中，否則 VPR 會報錯。 在本文中，我們將研究這種使用函數的方式 VPR當 id 根本不存在於數據庫中時。 好像函數 VPR 切換到近似模式，並選擇在我們想要查找某些內容時提供哪些數據。 在某些情況下，這正是需要的。
生活中的一個例子。 我們設置任務
讓我們用一個真實的例子來說明這篇文章——根據各種銷售指標計算佣金。 我們將從一個非常簡單的選項開始，然後逐漸將其複雜化，直到問題的唯一合理解決方案是使用函數 VPR. 我們虛構任務的初始場景如下：如果銷售人員一年的銷售額超過 30000 美元，那麼他的佣金是 30%。 否則，佣金只有20%。 讓我們以表格的形式來表示：
賣方在單元格 B1 中輸入他們的銷售數據，單元格 B2 中的公式確定賣方可以預期的正確佣金率。 反過來，得到的費率在單元格 B3 中用於計算賣方應收到的總佣金（只需將單元格 B1 和 B2 相乘）。
表格中最有趣的部分包含在單元格 B2 中——這是確定佣金率的公式。 此公式包含一個 Excel 函數，稱為 IF （如果）。 對於不熟悉這個功能的讀者，我將解釋它是如何工作的：
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
條件 是一個函數參數，它的值是 真實代碼 (真)，或 假 （錯誤的）。 在上面的例子中，表達式 B1
B1小於B5是真的嗎？
或者你可以換一種說法：
當年的總銷售額是否低於閾值？
如果我們回答這個問題 是 (TRUE)，然後函數返回 值為真 （如果為 TRUE 則為值）。 在我們的例子中，這將是單元格 B6 的值，即當總銷售額低於閾值時的佣金率。 如果我們回答這個問題 沒有 (FALSE) 然後返回 值為假 （如果為 FALSE 則為值）。 在我們的例子中，這是單元格 B7 的值，即當總銷售額高於閾值時的佣金率。
如您所見，如果我們的總銷售額為 20000 美元，我們將在單元格 B2 中獲得 20% 的佣金率。 如果我們輸入 40000 美元的值，那麼佣金率將改變 30%：
這就是我們的桌子的工作方式。
我們使任務複雜化
讓我們讓事情變得更困難一些。 讓我們設置另一個門檻：如果賣家的收入超過 40000 美元，那麼佣金率會增加到 40%：
一切似乎都簡單明了，但我們在單元格 B2 中的公式明顯變得更加複雜。 如果您仔細查看公式，您會看到函數的第三個參數 IF (IF) 變成另一個成熟的功能 IF （如果）。 這種構造稱為函數相互嵌套。 Excel 很高興地允許這些構造，它們甚至可以工作，但它們更難閱讀和理解。
我們不會深入研究技術細節——它為什麼以及如何工作，也不會深入探討編寫嵌套函數的細微差別。 畢竟這是一篇專門介紹功能的文章 VPR，而不是 Excel 的完整指南。
無論如何，公式變得更加複雜！ 如果我們為銷售額超過 50 美元的賣家引入另一種 50000% 的佣金率選項會怎樣。 如果有人賣出了超過 60000 美元，他們會支付 60% 的佣金嗎？
現在單元格 B2 中的公式，即使寫得沒有錯誤，也變得完全不可讀。 我認為很少有人想在他們的項目中使用具有 4 級嵌套的公式。 一定有更簡單的方法？！
還有這樣的方法！ 該功能將幫助我們 VPR.
我們應用VLOOKUP函數來解決問題
讓我們稍微改變一下桌子的設計。 我們將保留所有相同的字段和數據，但以一種新的、更緊湊的方式排列它們：
花點時間，確保新表 費率表 包含與之前的閾值表相同的數據。
主要思想是使用函數 VPR 根據表格確定所需的關稅稅率 費率表 取決於銷售量。 請注意，賣家可以以不等於表中五個閾值之一的金額出售商品。 例如，他可以賣出 34988 美元，但沒有這個數額。 讓我們看看功能如何 VPR 可以處理這樣的情況。
插入 VLOOKUP 函數
選擇單元格 B2（我們要插入公式的地方）並找到 VLOOKUP Excel 函數庫中的 (VLOOKUP)： 公式 （公式）> 函數庫 (函數庫) > 查找和參考 （引用和數組）。
出現一個對話框 功能參數 （函數參數）。 我們一一填寫參數的值，從 查找值 （查找值）。 在此示例中，這是單元格 B1 的總銷售額。 將光標放在字段中 查找值 (Lookup_value) 並選擇單元格 B1。
接下來，您需要指定功能 VPR在哪裡查找數據。 在我們的示例中，這是一個表 費率表. 將光標放在字段中 表格數組 （表）並選擇整個表 費率表除了標題。
接下來，我們需要指定使用我們的公式從哪一列中提取數據。 我們對佣金率感興趣，它位於表格的第二列。 因此，對於論證 列索引號 (Column_number) 輸入值 2。
最後，我們介紹最後一個論點—— 範圍查找 （間隔查找）。
重要事項： 正是這個參數的使用使得應用函數的兩種方式之間有所不同 VPR. 使用數據庫時，參數 範圍查找 (range_lookup) 必須始終有一個值 假 (FALSE) 搜索完全匹配。 在我們使用函數時 VPR，我們必須將此字段留空，或輸入一個值 真實代碼 （真的）。 正確選擇此選項非常重要。
為了更清楚，我們將介紹 真實代碼 （真）在該領域 範圍查找 （間隔查找）。 雖然，如果您將該字段留空，這不會是一個錯誤，因為 真實代碼 是它的默認值：
我們已經填寫了所有參數。 現在我們按 OK, Excel 用函數為我們創建了一個公式 VPR.
如果我們對總銷售額嘗試幾個不同的值，那麼我們將確保公式正確工作。
結論
當函數 VPR 使用數據庫，參數 範圍查找 (range_lookup) 必須接受 假 （錯誤的）。 輸入的值是 查找值 (Lookup_value) 必須存在於數據庫中。 換句話說，它正在尋找精確匹配。
在我們在本文中看到的示例中，不需要完全匹配。 這是函數時的情況 VPR 必須切換到近似模式才能返回所需的結果。
例如： 我們想確定銷售額為 34988 美元的銷售人員在佣金計算中使用的費率。 功能 VPR 返回我們一個 30% 的值，這是絕對正確的。 但是為什麼公式選擇正好包含 30% 而不是 20% 或 40% 的行呢？ 近似搜索是什麼意思？ 讓我們清楚一點。
當論據 範圍查找 (interval_lookup) 有一個值 真實代碼 (TRUE) 或省略，函數 VPR 遍歷第一列並選擇不超過查找值的最大值。
很重要的一點： 要使此方案起作用，表的第一列必須按升序排序。