Excel函数:间接地址列

Question

在过去读了很多书之后，第一次问问题。

我正在运行下面的数组函数excel：

INDEX('Available Options'!$A$1:$CM$137,$B$1,
SMALL(
IF(
INDIRECT("'Feasibility Options'!"&ADDRESS($B$1,COLUMN('Feasibility Options'!$G$1),1,1)&":"&ADDRESS($B$1,COLUMN('Feasibility Options'!$O$1),1,1))=2,
COLUMN(INDIRECT("'Feasibility Options'!"&ADDRESS($B$1,COLUMN('Feasibility Options'!$G$1),1,1)&":"&ADDRESS($B$1,COLUMN('Feasibility Options'!$O$1),1,1)))),
ROW('Available Options'!1:1)))  

这背后的想法是，在一个称为“可行性选项”的单独选项卡中，有许多单元格中有1，2或3个(1表示默认，2表示替代，3表示不活动)，这些选项的价格保存在“可用选项”选项卡中。$B$1包含通过单独计算产生的行号。

计算此公式会在解析函数的间接部分时产生一个值错误，但如果我将列号公式替换为列号，则该函数可以工作，如下所示：

INDEX('Available Options'!$A$1:$CM$137,$B$1,
SMALL(
IF(INDIRECT("'Feasibility Options'!"&ADDRESS($B$1,7,1,1)&":"&ADDRESS($B$1,15,1,1))=2,COLUMN(INDIRECT("'Feasibility Options'!"&ADDRESS($B$1,7,1,1)&":"&ADDRESS($B$1,15,1,1)))),ROW('Available Options'!1:1)))

谁能解释一下为什么会发生这种情况，以及如何修复它？理想情况下，我需要使用单元格引用，而不是硬编码的列号，因为以后可能需要添加更多列。

感谢你能给予的任何帮助，如果我错过了之前回答这类问题的帖子，我深表歉意。

Answer 1

我没有对此进行测试，但我怀疑问题是由COLUMN函数引起的，该函数返回一个数组(即使是单个值)，有时处理起来会出现问题：

通常可以通过将COLUMN包装在SUM或MAX函数中来使其工作，例如

MAX(COLUMN('Feasibility Options'!$G$1))

或者改用COLUMNS函数，例如

COLUMNS('Feasibility Options'!$G$1:G1)

可拖动以递增的

更好的做法是用INDEX替换整个INDIRECT/ADDRESS部分，它应该更简单、更短、更高效。如果你感兴趣，我可以给你更多细节