Excel 指数/匹配公式,但指数范围每月都会变化

Excel 指数/匹配公式,但指数范围每月都会变化

我返回每家公司“总收入”值的公式是:

=IFERROR(INDEX(YTD2016!$H$19:$ZZ$19,MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

运行良好。完全没有问题。喜欢 Index/Match。

问题是,$H$19:$ZZ$19如果添加新行(每个月粘贴新报告,因此它不知道单元格已移动),索引范围引用可能会在下个月更改为第 20 行。如果我每个月都这样做,那么很容易找到并替换所有内容,但事实并非如此,其他用户也做不到。

因此,无论如何,我都试图确保它每个月都能正常工作。我确实找到了使用 返回行号的方法Match("Total Income"...),但是有没有办法能够查找文本字符串“总收入”,以获取要放入我的 Index 公式中的行号?

由于匹配字符串可以带回行号,我甚至尝试将该方程式放在索引范围内(...$H$MATCH(“总收入”,'Monthly by Class'!D:D,0)。这没有按预期工作,但我只是想让它把第 19 行放在那里。一定有办法。

在此处输入图片描述
索引文件

答案1

我甚至尝试将该方程式放在索引范围内(...$H$MATCH(“总收入”'Monthly by Class'!D:D,0)。这并没有按预期工作,但我只是想让它把第 19 行放在那里。一定有办法。

确实有,而且您基本上已经掌握了。您只需要INDIRECT在想要使用公式生成单元格引用时使用它,而不是直接将公式放在单元格引用的中间。太棒了!INDIRECT使用双引号将单元格引用构建为文本。

因此,如果这是您的整个有效公式:

=IFERROR(INDEX(YTD2016!$H$19:$ZZ$19,MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

您当前范围的硬编码单元格引用INDEX是:

YTD2016!$H$19:$ZZ$19

我们只想用简单的替换行号MATCH

让我们这样看

YTD2016!$H$ 19 :$ZZ$ 19

并替换MATCH行值的公式

YTD2016!$H$ MATCH("Total Income",'Monthly by Class'!D:D,0) :$ZZ$ MATCH("Total Income",'Monthly by Class'!D:D,0)

用引号和 & 符号包裹它,INDIRECT以构建文本字符串

INDIRECT( "YTD2016!$H$"& MATCH("Total Income",'Monthly by Class'!D:D,0) &":$ZZ$"& MATCH("Total Income",'Monthly by Class'!D:D,0) )

整合在一起

INDIRECT("YTD2016!$H$"&MATCH("Total Income",'Monthly by Class'!D:D,0)&":$ZZ$"&MATCH("Total Income",'Monthly by Class'!D:D,0))

并回到整体功能中的位置

=IFERROR(INDEX(INDIRECT("YTD2016!$H$"&MATCH("Total Income",'Monthly by Class'!D:D,0)&":$ZZ$"&MATCH("Total Income",'Monthly by Class'!D:D,0)),MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

..罗伯特是你妈妈的兄弟。

答案2

如果我理解你的问题正确的话,你可能需要看看地址除了您已经在运行的匹配语句之外的功能。

举个例子,

=ADDRESS(1,1)

将返回 $A$1

您可以将匹配作为第一个参数,将列号作为第二个参数,然后将所有这些放入索引调用中。

我希望它能有所帮助

相关内容