內容
在我們關於函數的教程的第二部分 VPR （VLOOKUP）在Excel中，我們將分析幾個例子，這將幫助您指導所有的權力 VPR 解決最雄心勃勃的 Excel 任務。 這些示例假定您已經對該功能的工作原理有基本的了解。 如果沒有，您可能有興趣從本教程的第一部分開始，它解釋了語法和基本用法。 VPR. 好吧，讓我們開始吧。
按多個條件在 Excel 中搜索
功能 VPR in Excel 是一個非常強大的工具，用於在數據庫中執行特定值的搜索。 但是，有一個很大的限制——它的語法只允許你搜索一個值。 如果要按多個條件搜索怎麼辦？ 您將在下面找到解決方案。
示例 1：按 2 個不同的條件搜索
假設我們有一個訂單列表，我們想找到 商品數量 （數量），基於兩個標準—— 客戶名稱 （客戶） Названиепродукта （產品）。 由於每個買家都訂購了幾種類型的商品，因此事情變得複雜，如下表所示：
常規功能 VPR 在這種情況下將不起作用，因為它將返回它找到的與給定查找值匹配的第一個值。 例如，如果您想知道一個項目的數量 糖果'由買方訂購 傑里米·希爾，寫出如下公式：
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- 這個公式將返回結果 15對應產品 蘋果，因為它是第一個匹配的值。
有一個簡單的解決方法——創建一個額外的列來組合所有所需的標準。 在我們的示例中，這些是列 客戶名稱 （客戶） Названиепродукта （產品）。 不要忘記合併的列必須始終是搜索範圍中最左邊的列，因為函數是最左邊的列 VPR 查找值時查找。
因此，您向表中添加一個輔助列，並將以下公式複製到其所有單元格中： =B2&C2. 如果希望字符串更具可讀性，可以用空格分隔組合值： =B2&» «&C2. 之後，您可以使用以下公式：
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
細胞在哪裡 B1 包含參數的串聯值 Lookup_Array中 (lookup_value) 和 4 - 爭論 Col_index_num為 (column_number)，即包含要檢索的數據的列的編號。
示例 2：VLOOKUP 通過兩個標准在另一張紙上查看表格
如果您需要通過從位於另一個工作表或另一個 Excel 工作簿中的第二個表（查找表）添加數據來更新主表（主表），那麼您可以直接在插入的公式中收集所需的值進入主表。
與前面的示例一樣，您需要在 Lookup 表中添加一個包含組合值的輔助列。 此列必須是搜索範圍中最左側的列。
所以公式與 VPR 可能是這樣的：
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
此處，B 列和 C 列分別包含客戶名稱和產品名稱，鏈接 訂單！$A&$2:$D$2 定義要在另一個工作表中查找的表。
為了使公式更具可讀性，您可以給視圖範圍命名，然後公式看起來會簡單得多：
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
要使公式起作用，您正在查看的表格最左側列中的值必須以與搜索條件完全相同的方式組合。 在上圖中，我們將 u2bu2band 的值組合在一起，在它們之間放置一個空格，就像在函數的第一個參數中需要做的一樣（BXNUMX&“”&CXNUMX）。
記得！ 功能 VPR 限制為 255 個字符，它不能搜索長度超過 255 個字符的值。 請記住這一點，並確保所需值的長度不超過此限制。
我同意添加輔助列不是最優雅且並非總是可以接受的解決方案。 您可以在沒有幫助列的情況下執行相同的操作，但這需要一個更複雜的公式和函數組合 INDEX （索引）和 MATCH （更多暴露）。
我們使用VLOOKUP提取第2、3等值
你已經知道了 VPR 只能返回一個匹配值，更準確地說，是找到的第一個。 但是，如果這個值在查看的數組中重複多次，並且您想提取其中的第 2 個或第 3 個呢？ 如果所有值怎麼辦？ 問題看似複雜，但解決方案是存在的！
假設表的一列包含客戶的姓名（Customer Name），另一列包含他們購買的產品（Product）。 讓我們嘗試查找給定客戶購買的第 2、第 3 和第 4 件商品。
最簡單的方法是在列之前添加一個輔助列 顧客姓名 並用每個名稱的重複數填寫客戶名稱，例如， 約翰·杜1, 約翰·杜2 等等。我們將使用該函數進行編號 COUNTIF (COUNTIF)，假設客戶名稱在 B 列中：
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
之後就可以使用正常功能了 VPR找到所需的訂單。 例如：
- 發現 2次 客戶訂購的物品 丹·布朗:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - 發現 3次 客戶訂購的物品 丹·布朗:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
實際上，您可以輸入單元格引用作為查找值而不是文本，如下圖所示：
如果你只是在尋找 2-E 重複，你可以通過創建一個更複雜的公式來做到這一點，而不需要輔助列：
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
在這個公式中：
- $ F $ 2 – 包含買家姓名的單元格（未更改，請注意 – 鏈接是絕對的）；
- $B$ - 柱子 顧客姓名;
- Table4 - 你的桌子（這個地方也可以是一個常規範圍）；
- $ C16 – 表格或範圍的結束單元格。
此公式僅查找第二個匹配值。 如果您需要提取剩餘的重複，請使用之前的解決方案。
如果您需要所有匹配項的列表 - 函數 VPR 這不是一個助手，因為它一次只返回一個值——週期。 但是Excel有一個功能 INDEX (INDEX)，可以輕鬆應對這項任務。 這樣的公式看起來如何，您將在以下示例中學習。
檢索所需值的所有重複
正如剛才提到的 VPR 無法從掃描範圍中提取所有重複值。 為此，您需要一個稍微複雜一點的公式，由幾個 Excel 函數組成，例如 INDEX （指數）， 小型 （小）和 行 （線）
例如，下面的公式在區域 B2:B2 中查找單元格 F16 中值的所有重複項，並返回 C 列中相同行的結果。
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
將此數組公式輸入到多個相鄰單元格中，例如單元格 F4：F8如下圖所示。 單元格的數量必須等於或大於搜索值的最大可能重複次數。 不要忘記點擊 Ctrl + Shift + Enter正確輸入數組公式。
如果您有興趣了解它的工作原理，讓我們深入了解一下公式的細節：
部分1：
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – 將單元格 F2 中的值與範圍 B2:B16 中的每個值進行比較。 如果找到匹配項，則表達式 字符串(C2:C16)-1 返回對應行的編號（值 -1 允許您不包括標題行）。 如果沒有匹配，函數 IF (IF) 返回一個空字符串。
函數結果 IF (IF) 會有這樣一個水平數組： {1,"",3,"",5,"","","","","","",12,"","",""}
部分2：
ROW()-3
СТРОКА()-3
這裡的功能 行 (LINE) 用作附加計數器。 由於公式被複製到單元格 F4:F9 中，我們減去數字 3 從函數結果獲取值 1 在單元格中 F4 （第 4 行，減去 3）得到 2 在單元格中 F5 （第 5 行，減去 3）等等。
部分3：
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
功能 小型 （小）返回 哦哦 數據數組中的最小值。 在我們的例子中，返回哪個位置（從最小）由函數確定 行 (LINE)（見第 2 部分）。 所以，對於一個細胞 F4 功能 小（{數組}，1） 返回 1次 （最小）數組元素，即 1. 對於細胞 F5 返回 2次 數組中的最小元素，即 3等等。
部分4：
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
功能 INDEX (INDEX) 只返回數組中特定單元格的值 C2：C16. 對於細胞 F4 功能 指數($C$2:$C$16) 將返回 蘋果為 F5 功能 指數($C$2:$C$16) 將返回 糖果' 等等。
部分5：
IFERROR()
ЕСЛИОШИБКА()
最後，我們把公式放到函數里面 錯誤 (IFERROR)，因為您不太可能對錯誤消息感到滿意 ＃在 (#N/A) 如果公式複製到的單元格數小於正在查看的範圍內的重複值數。
按已知行和列搜索 XNUMXD
在 Excel 中執行 XNUMXD 搜索涉及按已知行號和列號搜索值。 換句話說，您正在提取特定行和列交叉處的單元格值。
所以，讓我們轉向我們的表格，用函數編寫一個公式 VPR，它將找到有關 XNUMX 月份銷售的檸檬成本的信息。
有幾種方法可以執行 XNUMXD 搜索。 查看選項並選擇最適合您的選項。
VLOOKUP 和 MATCH 函數
你可以使用一堆函數 VPR （VLOOKUP）和 更多曝光 (MATCH) 查找字段交叉處的值 Названиепродукта （字符串）和 每月 有問題的數組的（列）：
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
上面的公式是一個正則函數 VPR，它在單元格 A2 到 A9 中查找值“Lemons”的完全匹配。 但是由於您不知道三月份的銷售額在哪一列，您將無法為第三個函數參數設置列號。 VPR. 而是使用該函數 更多曝光定義此列。
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
翻譯成人類語言，這個公式意味著：
- 我們正在尋找字符“Mar”——參數 Lookup_Array中 （查找值）；
- 查看從 A1 到 I1 的單元格 – 參數 查找數組 （查找數組）；
- 返回完全匹配 - 參數 比賽類型 （比賽類型）。
運用 0 在第三個參數中，你說函數 更多曝光 查找與您要查找的值完全匹配的第一個值。 這相當於值 假 (FALSE) 用於第四個參數 VPR.
這是在 Excel 中創建雙向搜索公式的方法，也稱為二維搜索或雙向搜索。
SUMPRODUCT 函數
功能 SUMPRODUCT (SUMPRODUCT) 返回所選數組的乘積之和：
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
索引和匹配函數
在下一篇文章中，我將詳細解釋這些函數，所以現在你可以復制這個公式：
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
命名範圍和交集運算符
如果您不熟悉所有這些複雜的 Excel 公式，您可能會喜歡這種直觀且令人難忘的方式：
- 選擇表，打開選項卡 公式 （公式）並單擊 從選擇創建 （從選擇中創建）。
- 勾選複選框 第一排 （在上面的行）和 左欄 （在左側的列中）。 Microsoft Excel 將根據電子表格頂行和左列中的值為範圍分配名稱。 現在您可以直接使用這些名稱進行搜索，而無需創建公式。
- 在任何空單元格中，寫 =row_name 列名，例如像這樣：
=檸檬三月
… 或相反亦然：
=馬爾檸檬
請記住，行名和列名必須用空格隔開，在這種情況下，空格的作用類似於交集運算符。
當您輸入名稱時，Microsoft Excel 將顯示帶有匹配名稱列表的工具提示，就像您輸入公式時一樣。
- 媒體推薦 Enter 並檢查結果
一般來說，無論您選擇以上哪種方法，二維搜索的結果都是一樣的：
在一個公式中使用多個 VLOOKUP
碰巧主表和查找表沒有一個共同的列，這會阻止您使用通常的功能 VPR. 但是，還有一個表不包含我們感興趣的信息，但與主表和查找表有一個共同的列。
讓我們看一下下面的例子。 我們有一個帶有列的主表 SKU（新），您要在其中添加一列，其中包含另一個表中的相應價格。 此外，我們有 2 個查找表。 第一個（查找表 1）包含更新的數字 SKU（新） 和產品名稱，第二個（查找表 2）——產品名稱和舊編號 SKU（舊）.
要將第二個查找表中的價格添加到主表中，您必須執行一個稱為 double 的操作 VPR 或嵌套 VPR.
- 寫一個函數 VPR，在表中查找產品名稱 查找表 1運用 SKU，作為期望值：
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)這裡 新品_SKU – 命名範圍 $A：$B 在桌子上 查找表 1, 2 – 這是 B 列，其中包含商品名稱（見上圖）
- 編寫一個公式從表格中插入價格 查找表 2 基於知名產品名稱。 為此，請將您之前創建的公式粘貼為新函數的查找值 VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)這裡 價錢 – 命名範圍 $ A：$ C 在桌子上 查找表 2, 3 是包含價格的 C 列。
下圖顯示了我們創建的公式返回的結果：
使用 VLOOKUP 和 INDIRECT 動態替換不同表中的數據
首先，讓我們澄清一下“動態替換來自不同表的數據”這一表述的含義，以確保我們能夠正確理解彼此。
有些情況下，有多個表格具有相同格式的數據，並且有必要根據在給定單元格中輸入的值從某個表格中提取必要的信息。 我認為用一個例子來解釋這一點更容易。
想像一下，您有幾個地區的銷售報告，這些報告具有相同的產品和相同的格式。 您想查找特定地區的銷售數據：
如果你只有兩個這樣的報告，那麼你可以使用一個可恥的簡單公式和函數 VPR и IF (IF) 選擇要搜索的報告：
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
哪裡：
- $D$2 是包含產品名稱的單元格。 請注意，我們在此處使用絕對引用以避免在將公式複製到其他單元格時更改查找值。
- $ D3 是一個帶有區域名稱的單元格。 我們使用絕對列引用和相對行引用，因為我們計劃將公式複製到同一列中的其他單元格。
- FL_薩爾es и CA_銷售 – 包含相應銷售報告的表（或命名範圍）的名稱。 當然，您可以使用通常的工作表名稱和單元格範圍引用，例如 'FL 表'！$A$3:$B$10，但命名範圍更方便。
但是，當有很多這樣的表時，函數 IF 不是最好的解決方案。 相反，您可以使用該功能 間接 （間接）返回所需的搜索範圍。
你可能知道，函數 間接 用於返回由文本字符串給出的鏈接，這正是我們現在所需要的。 所以，大膽地將上面公式中的表達式替換為函數 IF 與功能聯繫 間接. 這是一個組合 VPR и 間接 適用於：
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
哪裡：
- $D$2 – 這是一個帶有產品名稱的單元格，由於絕對鏈接，它沒有變化。
- $ D3 是包含區域名稱第一部分的單元格。 在我們的示例中，這 FL.
- _銷售量 – 所有命名範圍或表名稱的公共部分。 當與單元格 D3 中的值組合時，它形成所需範圍的完全限定名稱。 以下是該功能新手的一些詳細信息 間接.
INDIRECT 和 VLOOKUP 如何工作
首先，讓我提醒你函數的語法 間接 （間接）：
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
第一個參數可以是單元格引用（A1 或 R1C1 樣式）、範圍名稱或文本字符串。 第二個參數確定第一個參數中包含的鏈接樣式：
- A1如果參數是 真實代碼 (TRUE) 或未指定；
- R1C1，如果 F作為電子 （錯誤的）。
在我們的例子中，鏈接具有樣式 A1，因此您可以省略第二個參數並專注於第一個參數。
讓我們回到我們的銷售報告。 如果您還記得，那麼每個報告都是位於單獨工作表上的單獨表格。 要使公式正常工作，您必須為表（或範圍）命名，並且所有名稱都必須有一個公共部分。 例如，像這樣： CA_銷售, FL_銷售, TX_銷售 等等。 如您所見，“_Sales”出現在所有名稱中。
功能 間接 連接 D 列中的值和文本字符串“_Sales”，從而告訴 VPR 在哪個表中搜索。 如果單元格 D3 包含值“FL”，則公式將搜索表格 FL_銷售, 如果“CA” – 在表中 CA_銷售 等等。
函數的結果 VPR и 間接 將如下：
如果數據位於不同的 Excel 書籍中，則需要在命名範圍前添加書籍名稱，例如：
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
如果功能 間接 指另一個工作簿，該工作簿必須打開。 如果關閉，函數會報錯。 ＃REF！ （#SSYL！）。