excel - 如何在 Excel 数组公式中使用 INDIRECT？

Question

情况

• 在“计划”表中，我有一个区域，其中包含相邻单元格中的成对会话（字符串）和小时数（数字）（例如 D11 和 E11、I12 和 J12 等）。一个会话可以发生多次。
  • D11:E11 是 | 福 | 8 |
  • I12:J12 是 | 福 | 4 |
• 在另一张表中，我想在计划表中找到一个会话并返回一个数组，其中包含该会话中预订的所有小时数（计算总数）
• 我使用带条件的数组公式，并打算使用 SMALL 函数从数组中检索结果

问题

以下公式返回对“Foo”预订小时数的所有正确引用，到目前为止一切都很好。

=IF(计划！$D$11:$CV$18="Foo";ADDRESS(行(计划！$D$11:$CV$18);COLUMN(计划！$D$11:$CV$18)+1;;;"规划”））

{"Planning!$E$11"\FALSE\FALSE\FALSE\FALSE\"Planning!$J$12"}

但是，如果我使用 INDIRECT 函数来检索这些引用的值，它们总是返回数组中第一个引用的值（“Planning！$E$11”）

=IF(计划!$D$11:$CV$18="Foo";INDIRECT(地址(行(计划!$D$11:$CV$18);列(计划!$D$11:$CV$18)+1;; ;“规划”）））

{8\FALSE\FALSE\FALSE\FALSE\8}

如何检索正确的值？还是我应该以完全不同的方式解决这个问题？

截图

• 规划表
• 我想要的概述

标签： excelarray-formulasexcel-indirect

Answer 1

由于我主要对计划的总小时数感兴趣，因此我最终使用了以下公式：

=SUM(SUM(INDIRECT(IF(Planning!$D$11:$CV$18="Foo";(ADDRESS(ROW(Planning!$D$11:$CV$18);COLUMN(Planning!$D$11:$CV$18) )+1;;;"计划"));"$19"))))

1. IF：如果找到字符串，则使用对规划表的引用创建数组。如果未找到，请添加参考 $U$19。
2. 使用 INDIRECT，将所有引用替换为规划表中的值。$U$19 包含值 0。
3. 然后使用 SUM 两次对所有值求和。不知道为什么，但是看看
   • 是否可以将数组作为 INDIRECT() 的参数，所以 INDIRECT() 返回数组？
   • https://superuser.com/questions/1196243/simplify-a-sum-of-indirect-cell-values