☰

在 Excel 中，您能否根据另一个单元格或公式的内容定义公式中要使用的单元格或范围？

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中，您能否根据另一个单元格或公式的内容定义公式中要使用的单元格或范围？

我想要做的是这样的：

  • 用一个单元格计算特定列中存在的项目数，例如 C1 = 129
  • 让第二个单元格使用该数字作为定义的一部分，例如 D1 = AVERAGE($A$1:$A$(C1))，意思是 ($A$1:$A$129)

我这样做的原因是：

我有一个从另一个网站抄袭来的公式，用于计算列中不同项目的数量。此公式不允许空白，并且（据我所知）也无法允许空白。我在每周报告中使用此公式，列中的项目数量每周都在变化。我不想像现在这样在多个使用位置手动更改值，而是直接放入新数据，让工作表计算项目数量，然后在抄袭的公式中使用该数字。

以下是所讨论的抄袭公式：

=SUM(IF(FREQUENCY(MATCH(Data!A2:A100,Data!A2:A100,0),MATCH(Data!A2:A100,Data!A2:A100,0))>0,1))

其中“A100”是本周数据填充列的底部范围。下周可能是 A200 或 A50。但是，对于本周，如果我将 A100 更改为 A101（其中不存在数据），公式将失败，并出现“值不可用错误”。

有什么方法可以满足我的需要吗？

答案1

使用 INDIRECT() 工作表函数。

简而言之，该公式=INDIRECT("A1")返回任何价值包含在单元格中A1。因此，您需要做的就是，用"A1"您用来计算单元格行/列的任何计算结果来替换该静态值，而不是替换该静态值。整个公式的结果将是价值包含在工作表函数参数中指定的单元格内INDIRECT()。

这是一个简单的例子

单元格 A1 的值 = 1
单元格 A2 的值 = 2
单元格 A3 的值 = 3
单元格 A4 的值 = 空白/空 单元
格 A5 的值 = =COUNT(A1:A3)（将其替换为您抄袭的公式）
单元格 A6 的值 ==AVERAGE($A$1:INDIRECT("$A$" & INDIRECT("A5")))

如果您想了解其工作原理，请转到公式 -> 计算公式并逐步执行计算。基本上，它将字符串“$A$”与值“3”连接起来。“3”来自价值单元格 A5 的值为 3，因为在本例中，计数 3 个单元格返回 3。然后我们INDIRECT()第二次调用，以获得价值，$A$3但实际上返回的是单元格引用！如果您不相信我，请评估公式并观察 ExcelINDIRECT("$A$3")在评估过程中将调用转换为什么。这很奇怪，但它有效——INDIRECT()根据它是输出到范围引用还是输出到值，其行为会有所不同。

答案2

可以修改原始公式以允许（但不计算）空白，即此版本

=SUM(IF(FREQUENCY(IF(Data!A2:A100<>"",MATCH(Data!A2:A100,Data!A2:A100,0)),ROW(Data!A2:A100)-ROW(Data!A2)),1))

使用 CTRL+SHIFT+ENTER 确认

.....但你可能也想尝试这个 - 更短，不需要“数组输入”

=SUMPRODUCT((Data!A2:A100<>"")/COUNTIF(Data!A2:A100,Data!A2:A100&""))

答案3

您也INDIRECT可以考虑使用INDEX。假设您的计数在 J3 中，数据在 A 列中，此公式将对所有元素求和：=SUM(A2:INDEX(A2:A10000,J3))

对于您的具体问题，这应该有效：

=SUM(IF(FREQUENCY(MATCH(数据！A2：INDEX(A2：A10000，J3)，数据！A2：INDEX(A2：A10000，J3)，0)，MATCH(数据！A2：INDEX(A2：A10000，J3)，数据！A2：INDEX(A2：A10000，J3)，0))>0,1))

INDEX相比而言，其巨大优势INDIRECT在于非挥发性并且通常计算速度更快。非易失性意味着 Excel 仅在任何前任发生更改时才需要计算公式。INDIRECT但是，将在工作簿中进行任何计算/更改时进行计算 - 因此，尤其是在大型模型中，这会显著减慢模型速度！

相关内容