具有变量行选择的表中值的索引

Question 1

一种基于 OFFSET 的可能解决方案，但如果您拥有非常大的数据集，它的易失性可能会减慢工作表的速度。

参见以下屏幕截图。N4 中的公式是

=INDEX($E$4:$E$8,MATCH(M4,OFFSET(E3,1,MATCH(L4,$F$3:$I$3,0)):OFFSET(E3,5,MATCH(L4,$F$3:$I$3,0)),0))

在公式中， ...:OFFSET(E3,5... 将 5 更改为表中实际的行数。

为了避免 OFFSET，您可以使用 INDEX 返回单元格引用而不是单元格值。仅使用 INDEX 的公式适用于与上述相同的示例表。

=INDEX($E$4:$E$8,MATCH(M4,INDEX($F$4:$I$4,MATCH(L4,$F$3:$I$3,0)):INDEX($F$8:$I$8,MATCH(L4,$F$3:$I$3,0)),0))

Answer

一种基于 OFFSET 的可能解决方案，但如果您拥有非常大的数据集，它的易失性可能会减慢工作表的速度。

参见以下屏幕截图。N4 中的公式是

=INDEX($E$4:$E$8,MATCH(M4,OFFSET(E3,1,MATCH(L4,$F$3:$I$3,0)):OFFSET(E3,5,MATCH(L4,$F$3:$I$3,0)),0))

在公式中， ...:OFFSET(E3,5... 将 5 更改为表中实际的行数。

为了避免 OFFSET，您可以使用 INDEX 返回单元格引用而不是单元格值。仅使用 INDEX 的公式适用于与上述相同的示例表。

=INDEX($E$4:$E$8,MATCH(M4,INDEX($F$4:$I$4,MATCH(L4,$F$3:$I$3,0)):INDEX($F$8:$I$8,MATCH(L4,$F$3:$I$3,0)),0))

Question 2

只是因为它很有趣，这里有一个数组公式可以直接获取行号：

=SUM(IF(B1:F1=col,(B2:F6=val)*ROW(A1:A5),FALSE))

在 Excel 365 之前的版本中，使用 CTRL+SHIFT+ENTER 输入以通知 Excel 将其视为数组公式。

Answer

只是因为它很有趣，这里有一个数组公式可以直接获取行号：

=SUM(IF(B1:F1=col,(B2:F6=val)*ROW(A1:A5),FALSE))

在 Excel 365 之前的版本中，使用 CTRL+SHIFT+ENTER 输入以通知 Excel 将其视为数组公式。

Question 3

您应该能够在不使用 OFFSET 的情况下做到这一点，因为 OFFSET 是不稳定的（需要重新计算很多次！）。

假设你已经定义了名称科尔和瓦尔，并且您的表格如图所示从 A1 开始。那么就是MATCH(col, B1:E1, 0)要搜索的正确列号。那么正确的行号是：

="Row " & MATCH(val, INDEX(B2:E6, 1, MATCH(col, B1:E1, 0)):INDEX(B2:E6, 5, MATCH(col, B1:E1, 0)),0)

或者如果你确实想返回 A 列中的文本：
=INDEX(A2:A6, MATCH(val, INDEX(B2:E6, 1, MATCH(col, B1:E1, 0)):INDEX(B2:E6, 5, MATCH(col, B1:E1, 0)),0))
另外如果您有 Office 365 和 LET 功能，您可以使其更高效：
=LET(colindex, MATCH(col, B1:E1, 0), INDEX(A2:A6, MATCH(val, INDEX(B2:E6, 1, colindex):INDEX(B2:E6, 5, colindex),0)))

Answer

您应该能够在不使用 OFFSET 的情况下做到这一点，因为 OFFSET 是不稳定的（需要重新计算很多次！）。

假设你已经定义了名称科尔和瓦尔，并且您的表格如图所示从 A1 开始。那么就是MATCH(col, B1:E1, 0)要搜索的正确列号。那么正确的行号是：

="Row " & MATCH(val, INDEX(B2:E6, 1, MATCH(col, B1:E1, 0)):INDEX(B2:E6, 5, MATCH(col, B1:E1, 0)),0)

或者如果你确实想返回 A 列中的文本：
=INDEX(A2:A6, MATCH(val, INDEX(B2:E6, 1, MATCH(col, B1:E1, 0)):INDEX(B2:E6, 5, MATCH(col, B1:E1, 0)),0))
另外如果您有 Office 365 和 LET 功能，您可以使其更高效：
=LET(colindex, MATCH(col, B1:E1, 0), INDEX(A2:A6, MATCH(val, INDEX(B2:E6, 1, colindex):INDEX(B2:E6, 5, colindex),0)))

Question 4

我假设您的行和列的命名方式实际上不太可预测，因此我已将其概括为考虑。如果它们确实按所示方式命名，则问题实际上非常简单。

因此，概括来说：

=INDEX(A2:A6,  MATCH(G2,  INDIRECT(  ADDRESS(2,  MATCH(G1,B1:E1,0)+1)  &":"&  ADDRESS(6,  MATCH(G1,B1:E1,0)+1)  )))

首先，您需要在列标题行中匹配列名称。实际上，需要匹配两次，因为您需要其中的行的开始和结束来搜索数据值。（另一种方法是使用简单的列引用，本质上类似于D:D每次计算搜索一百万行，但我不推荐这样做。）两次，因为您将在ADDRESS()函数中使用它来获取数据项搜索的起始地址和结束地址。对于您提供的数据，它将给出字符串D2:D6。

INDIRECT()将该字符串更改为真实引用。

外部的MATCH()工作方式与任何其他公式一样，在本例中INDEX/MATCH查找范围内的数据项并返回其索引值。3

最后，INDEX()使用...索引...返回与该索引匹配的第一列的值。ROW 3在本例中为行标题。

除非您拥有相当大的数据集（我的意思是很大），并且是使用过去 5-8 年前的计算机，否则使用像这样的易失性函数OFFSET()可能会产生明显的滞后。很大。很多公式，不仅仅是很多数据，而且这是唯一的公式。或者数据量非常大。您没有指定早期版本的 Excel，所以我猜您的计算机也是新的或较新的，所以这可能不会影响您。我的计算机已经四年多了，在这里的另一个问题上，我用 100,000 行静态数据填充了 30 列，而搜索它的公式实际上只导致大约一秒钟的延迟。因此，许多人发出的波动性警告实际上不再是问题。我只是提到它，以便其他人不会再提。

Answer