☰

如何进行以列和行标题为范围的二维查找？

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
如何进行以列和行标题为范围的二维查找？

我的电子表格中有一张结构与此类似的表格：

      | 1-2 | 3-4 | 5-6 | 7+
-------------------------------
18-20 |  7  |  2  |  9  |  4  |
21-25 |  1  |  0  |  6  |  3  |
26-30 |  1  |  4  |  6  |  9  |
31+   |  3  |  8  |  5  |  0  |

举例来说，我从用户那里得到的输入是2, 19，查找应该返回7。或者如果输入是9, 29，查找应该返回9。

我知道，当行和列标题中只有单个值时，可以通过组合VLOOKUP和进行二维查找MATCH。但是当标题是一系列值时，我该如何进行查找？

我是否只需要编写自定义 VBA 函数或者 Excel 是否有一些内置方法来执行此操作？

答案1

INDEX()可以进行二维查找。语法是INDEX(array,row_num,[col_num],[area_num])。它接受一个二维数组并返回 row_num 和 column_num 的交集。

由于标题中的范围，您的问题有点棘手，但一些快速的步法可以TRUNC()完成IF()工作。下面屏幕截图中从 D8 向下填充的公式给出了所示的结果：

=INDEX(B$2:E$5,IF(C8>31,4,TRUNC((C8-11)/5)),IF(B8>7,4,TRUNC((B8+1)/2)))

在此处输入图片描述

如果您想了解TRUNC()部分的工作原理，请将其放入=TRUNC((ROW()-11)/5)第 18 行或TRUNC((ROW()+1)/2)第 1 行并向下填写。

我希望这个帮助能祝你好运。

答案2

假设您查找的是互斥且完整的范围（至少对于 >=18 的值而言），则您的范围可以由每个单元格中的第一个值定义。例如，您提供的表格可以用以下等价方式描述：

在此处输入图片描述

然后，您可以通过组合 vlookup 和 match 来找到您的值。例如，如果您的表格和查找值的排列方式如下图所示：

在此处输入图片描述

您想要的值将位于突出显示的单元格中，其中包含以下内容：=VLOOKUP(B$8,$A$1:$E$5, MATCH($A8,$A$1:$E$1,1),TRUE)

答案3

由于标签是格式正确的，就标签而言，基本上意味着它们的排序方式就像是按照其首字符的字符代码从低到高排序一样，因此可以利用 VLOOKUP() 和 HLOOKUP() 中的最后一个参数，选择“近似匹配”。

您执行此操作是为了创建查找值和要查找的列，以便 VLOOKUP() 完成最终的工作。

最后，您有一个简单的 VLOOKUP()。在其中查找什么？假设您显示的表格是 A1:E5，其行标签在 A2:A5（不要包括第一行！），其列标签在 B1:E1（不要包括第一列！），其实际数据在 B2:E5。如果您包括那些第一行和第一列（A1），您将收到 #N/A 错误。假设您自己处理获取输入值，因此“2, 19”材料无论如何创建，无论如何提供（我将其称为“要查找的列”和“要查找的行”。）然后：

=HLOOKUP( "Row to look in" , A1:A5 , 1, TRUE )

将返回“18-20”，我们将直接使用它作为查找值，并且

=VLOOKUP( "Column to look in" , A1:E1 , 1 , TRUE )

将返回“1-2”，这就是“小技巧”的用武之地。

如果我们将最后一个函数包装在 MATCH() 函数中，它将使用“1-2”来查找最终 VLOOKUP() 所需的列号。（Excel 的 UserVoice 网站上的一位匿名人士向我展示了这一点。）

现在，您必须使用我们刚刚避开的范围 A1:E1 来直接获取列号。如果您以 B1 开头，则必须在结果中添加“1”，这样更易​​于阅读和理解：

=MATCH(  VLOOKUP( "Column to look in" , A1:E1 , 1 , TRUE )  , A1:E1 )

它为该列返回“2”（巧合的是，没有其他内容，与我们的输入相同，但不会混淆您的游戏！）。最后，您将把它们放在一起：

=VLOOKUP( HLOOKUP( "Row to look in" , A1:A5 , 1, TRUE ) , A1:E5 , MATCH( VLOOKUP( "Column to look in" , A1:E1 , 1 , TRUE ) , A1:E1 ) , FALSE )

使用“FALSE”是因为您有完全匹配的内容，或者出现了严重错误。

（如果只有列或只有行是合适的，您仍然可以在这里进行调整。您只是会遇到更困难的问题，因为只有一半的问题可以通过这种方式解决，但我发现这已经足够了。自从我学会了 MATCH() 的想法来重新获得行/列标题作为标签功能以来，我可能已经使用了 6-8 次，其中一半只有一个是“ruly”。）

相关内容