內容
這個小教程解釋瞭如何製作一個函數 VPR (VLOOKUP) 區分大小寫，顯示 Excel 可以以區分大小寫的方式搜索的其他幾個公式，並指出每個函數的優缺點。
我想每個 Excel 用戶都知道執行垂直搜索的函數。 沒錯，就是函數 VPR. 然而，很少有人知道 VPR 不區分大小寫，即大小寫字符相同。
這是一個演示無能的快速示例 VPR 認註冊。 假設在一個單元格中 A1 包含值“bill”和單元格 A2 ——“比爾”，公式：
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... 將停止搜索“bill”，因為該值在列表中排在首位，並從單元格中提取該值 B1.
在本文後面，我將向您展示如何做 VPR 區分大小寫。 此外，我們將學習更多可以在 Excel 中執行區分大小寫搜索的函數。
我們將從最簡單的開始—— 查看 （查找）和 SUMPRODUCT （SUMPRODUCT），不幸的是，它有幾個重要的限制。 接下來，我們將仔細研究稍微複雜一點的公式 索引+匹配 (INDEX+MATCH)，在任何情況下和任何數據集下都能完美運行。
VLOOKUP 函數區分大小寫
如您所知，通常的功能 VPR 不區分大小寫。 但是，有一種方法可以使其區分大小寫。 為此，您需要向表中添加一個輔助列，如下例所示。
假設在一列 B 有產品標識符（項目），您想從列中提取產品的價格和相應的評論 C и D. 問題是標識符包含小寫和大寫字符。 例如，單元格值 B4 (001Tvci3u) 和 B5 (001Tvci3U) 僅在最後一個字符的情況下有所不同， u и U 。
可以想像，通常的搜索公式
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
將返回 $ 90, 因為值 001TVCI3u 在搜索範圍內早於 001TVCI3U. 但這不是我們需要的，不是嗎？
用函數搜索 VPR 在 Excel 區分大小寫的情況下，您必須添加一個幫助列並使用以下公式填充其單元格（其中 B 是查找列）：
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
此公式將所需值分解為單獨的字符，將每個字符替換為其代碼（例如，而不是 A 65歲，相反 a 代碼 97)，然後將這些代碼組合成一個唯一的數字字符串。
之後，我們使用一個簡單的函數 VPR 對於區分大小寫的搜索：
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
功能的正確操作 VPR 區分大小寫取決於兩個因素：
- 輔助列必須是可視範圍中最左邊的列。
- 您要查找的值必須包含字符代碼而不是實際值。
如何正確使用CODE功能
插入輔助列單元格的公式假定您所有的搜索值都具有相同的字符數。 如果沒有，那麼您需要知道最小和最大的數字並添加盡可能多的功能 錯誤 (IFERROR) 最短和最長搜索值之間的差值是多少個字符。
例如，如果最短搜索值為 3 個字符，最長為 5 個字符，則使用以下公式：
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
對於功能 PSTR (MID) 您提供以下參數：
- 第一個論點—— 文本 (text) 是包含要提取的字符的文本或單元格引用（在我們的例子中是 B2）
- 第一個論點—— 起始編號 (start_position) 是要提取的第一個字符的位置。 你輸入 1 在第一個函數中 PSTR, 2 – 在第二個函數中 PSTR 等。
- 第一個論點—— 數字字符 (number_of_characters) – 指定要從文本中提取的字符數。 由於我們一直只需要 1 個字符，因此在我們編寫的所有函數中 1.
局限性： 功能 VPR 不是 Excel 中區分大小寫搜索的最佳解決方案。 首先，需要添加輔助柱。 其次，只有當數據是同質的，或者搜索值中的確切字符數已知時，該公式才能很好地工作。 如果這不是您的情況，最好使用我們在下面顯示的解決方案之一。
用於區分大小寫搜索的 LOOKUP 函數
功能 查看 （查找）相關 VPR, 但是它的語法允許在不添加輔助列的情況下進行區分大小寫的搜索。 為此，請使用 查看 結合功能 精確 （精確的）。
如果我們從上一個示例中獲取數據（沒有輔助列），那麼以下公式將處理該任務：
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
範圍內的公式搜索 A2：A7 與單元格值完全匹配 F2 區分大小寫並返回同一行 B 列的值。
Like VPR功能 查看 與文本和數值同樣適用，如下面的屏幕截圖所示：
重要！ 為了功能 查看 正常工作時，查找列中的值應按升序排序，即從小到大。
讓我簡單解釋一下這個函數是如何工作的 精確 在上面顯示的公式中，因為這是關鍵點。
功能 精確 比較第一個和第二個參數中的兩個文本值，如果完全相同則返回 TRUE，否則返回 FALSE。 對我們來說重要的是函數 精確 區分大小寫。
讓我們看看我們的公式是如何工作的 查看+精確:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- 功能 精確 比較單元格值 F2 與列中的所有元素 A (A2:A7)。 如果找到完全匹配，則返回 TRUE，否則返回 FALSE。
- 既然你給出了第一個函數參數 查看 value TRUE，只有在找到完全匹配的情況下，它才會從指定的列（在我們的例子中為 B 列）中提取相應的值，區分大小寫。
我希望這個解釋很清楚，現在你明白了主要思想。 如果是這樣，那麼您將不會對我們將進一步分析的其他功能有任何困難，因為。 他們都按照相同的原則工作。
局限性： 查閱列中的數據必須按升序排序。
SUMPRODUCT – 查找文本值，區分大小寫，但只返回數字
正如你已經從標題中理解的那樣， SUMPRODUCT (SUMPRODUCT) 是另一個 Excel 函數，可幫助您進行區分大小寫的搜索，但只會返回數值。 如果此選項不適合您，那麼您可以立即進行捆綁 索引+匹配，它為任何情況和任何數據類型提供了解決方案。
首先，讓我簡單解釋一下這個函數的語法，這將有助於你更好地理解後面的區分大小寫的公式。
功能 SUMPRODUCT 將給定數組的元素相乘並返回結果的總和。 語法如下所示：
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
由於我們需要區分大小寫的搜索，我們使用函數 精確 (EXACT) 來自上一個示例作為乘數之一：
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
你記得 精確 比較單元格值 F2 與列中的所有元素 A. 如果找到完全匹配，則返回 TRUE，否則返回 FALSE。 在數學運算中，Excel 將 TRUE 視為 1, 和 FALSE 為 0進一步 SUMPRODUCT 將這些數字相乘並將結果相加。
零不計算在內，因為當它們相乘時，它們總是給出 0. 讓我們仔細看看當列中的完全匹配時會發生什麼 A 找到並返回 1… 功能 SUMPRODUCT 乘以列中的數字 B on 1 並返回結果——完全相同的數字！ 這是因為其他產品的結果為零，它們不影響結果總和。
不幸的是功能 SUMPRODUCT 不能與文本值和日期一起使用，因為它們不能相乘。 在這種情況下，您將收到一條錯誤消息 ＃值！ (#VALUE!) 在單元格中 F4 在下圖中：
局限性： 僅返回數值。
INDEX + MATCH – 區分大小寫搜索任何數據類型
最後，我們接近了適用於任何數據集的無限制且區分大小寫的搜索公式。
這個例子排在最後，不是因為甜點是最好的，而是因為從前面的例子中獲得的知識將幫助你更好更快地理解區分大小寫的公式。 索引+匹配 （索引+匹配）。
你可能已經猜到了，函數的組合 更多曝光 и INDEX 在 Excel 中用作更靈活、更強大的替代方案 VPR. 使用 INDEX 和 MATCH 代替 VLOOKUP 一文將完美解釋這些函數如何協同工作。
我只回顧一下關鍵點：
- 功能 更多曝光 (MATCH) 在給定範圍內搜索一個值並返回其相對位置，即行號和/或列號；
- 接下來，函數 INDEX (INDEX) 從指定的列和/或行返回一個值。
到公式 索引+匹配 可以區分大小寫搜索，只需要添加一個函數即可。 不難猜到它又是什麼 精確 （精確的）：
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
在這個公式中 精確 與函數結合使用的方式相同 查看，並給出相同的結果：
注意公式 索引+匹配 括在花括號中的是一個數組公式，您必須按 Ctrl + Shift + Enter.
為什麼 INDEX+MATCH 是區分大小寫搜索的最佳解決方案？
捆綁包的主要優點 INDEX и 更多曝光:
- 不需要添加輔助列，不像 VPR.
- 不需要對搜索列進行排序，不像 查看.
- 適用於所有類型的數據——數字、文本和日期。
這個公式看起來很完美，不是嗎？ 事實上，事實並非如此。 這就是為什麼。
假設與查找值關聯的返回值列中的單元格為空。 公式會返回什麼結果？ 不？ 讓我們看看公式實際返回的內容：
糟糕，公式返回零！ 如果您使用純文本值，這可能不是一個大問題。 但是，如果表格包含數字，包括“真正的”零，這就會成為問題。
事實上，我們之前討論的所有其他查找公式（VLOOKUP、LOOKUP 和 SUMPRODUCT）的行為方式都相同。 但你想要完美的配方，對吧？
使公式區分大小寫 索引+匹配 完美，把它放在一個函數中 IF (IF) 將使用返回值測試單元格，如果為空則返回空結果：
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
在這個公式中：
- B 是具有返回值的列
- 1+ 是一個數字，它轉換函數返回的單元格的相對位置 更多曝光, 到單元格的真實地址。 例如，在我們的函數中 更多曝光 給定的搜索數組 A2：A7，即細胞的相對位置 A2 將 1，因為它是數組中的第一個。 但是單元格的實際位置 A2 列中是 2，所以我們添加 1彌補差額並發揮作用 間接 (INDIRECT) 從所需的單元格中檢索到值。
下面的圖片顯示了更正後的區分大小寫的公式 索引+匹配 在行動。 如果返回的單元格為空，則返回空結果。
我將公式重寫為列 乙：丁以適合屏幕截圖上的公式欄。
公式返回 0如果返回的單元格包含零。
如果你想要鏈接 INDEX и 更多曝光 返回值為空時顯示一些信息，可以寫在公式的最後一個引號（“”）中，例如，像這樣：
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
