时间戳

首页 > 解决方案 > 如何在索引函数中嵌套替代公式？

excel - 如何在索引函数中嵌套替代公式？

问题描述

我正在使用索引匹配功能处理 Excel 工作表。因为我希望它在我复制公式时增加列，所以我使用了下面的公式：

=INDEX(CLASSIFCATION!SUBSTITUTE(ADDRESS(1,ROW(A2),4),1,""):SUBSTITUTE(ADDRESS(1,ROW(A2),4),1,""),MATCH(DISTRIBUTION!P$19,CLASSIFCATION!$A:$A,0))

提供更多细节：

粘贴到 P20 行时，公式应为：

=INDEX(CLASSIFCATION!B:B,MATCH(DISTRIBUTION!P$19,CLASSIFCATION!$A:$A,0))

在第 21 行它应该是

=INDEX(CLASSIFCATION!C:C,MATCH(DISTRIBUTION!P$19,CLASSIFCATION!$A:$A,0))

等等..

其中 CLASSIFICATION 和 DISTRIBUTION 是同一工作簿中的两张工作表。

我收到一条错误消息，提示“此公式有问题”。我无法使用评估公式来检查错误是什么。

标签： excelexcel-formula

解决方案

在 INDEX 中包含完整范围，然后使用第三个 Criterion 告诉 INDEX 哪个列：

=INDEX(CLASSIFCATION!A:XFD,MATCH(DISTRIBUTION!P$19,CLASSIFCATION!$A:$A,0),ROW(2:2))

现在，随着公式被复制/向下拖动，它将引用下一列。

另一种方法是使用 VLOOKUP：

=VLOOKUP(DISTRIBUTION!P$19,A:XFD,ROW(2:2),FALSE)

这将做同样的事情。

至于为什么你的公式不起作用ADDRESS()，它是易变的，在使用多个时应该避免，返回一个字符串。连接的字符串不是可行的范围引用。您需要将整个包装在 INDIRECT 中，这也是易变的：

INDIRECT("CLASSIFCATION!" & SUBSTITUTE(ADDRESS(1,ROW(A2),4),1,"") & ":" & SUBSTITUTE(ADDRESS(1,ROW(A2),4),1,""))

但除此之外，它还使用 volatile 函数，这些函数会在 Excel 每次重新计算时重新计算，而不仅仅是在基础数据发生变化时重新计算。

另一种较短的 volatile 方法是 OFFSET：

OFFSET(CLASSIFCATION!A:A,0,ROW(1:1))

虽然这比 INDEX 短，但它也是易变的，在多个公式中使用时应避免使用。

推荐阅读