内容
这个小教程解释了如何制作一个函数 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;ЛОЖЬ)
将返回 易武，因为值 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 (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 列返回值。
喜欢 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 – 区分大小写搜索任何数据类型
最后，我们接近了适用于任何数据集的无限制且区分大小写的搜索公式。
这个例子排在最后，不是因为甜点是最好的，而是因为从前面的例子中获得的知识将帮助你更好更快地理解区分大小写的公式。 索引+匹配 （索引+匹配）。
你可能已经猜到了，函数的组合 更多曝光 и 指数 在 Excel 中用作更灵活、更强大的替代方案 VPR. 使用 INDEX 和 MATCH 代替 VLOOKUP 一文将完美地解释这些函数如何协同工作。
我只回顾一下关键点：
- 功能 更多曝光 (MATCH) 在给定范围内搜索一个值并返回其相对位置，即行号和/或列号；
- 接下来，函数 指数 (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 是区分大小写搜索的最佳解决方案？
捆绑包的主要优点 指数 и 更多曝光:
- 不需要添加辅助列，不像 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如果返回的单元格包含零。
如果你想要链接 指数 и 更多曝光 返回值为空时显示一些信息，可以写在公式的最后一个引号（“”）中，例如，像这样：
=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.")
