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

Question

情况

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

问题

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

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

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

但是，如果我使用 INDIRECT 函数检索那些引用的值，它们总是返回数组中第一个引用的值(“Planning!$E$11”)

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

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

如何检索正确的值？或者我应该以完全不同的方式解决问题？

截图

• The planning sheet
• The overview I want

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;;;"Planning"));"$U$19"))))

1. IF:如果找到该字符串，则创建包含对 Planning 工作表的引用的数组。如果未找到，请添加引用 $U$19。
2. 使用 INDIRECT，将所有引用替换为计划表中的值。 $U$19 包含值 0。
3. 然后使用 SUM 两次求和所有值。我不知道为什么，但看到
   • Is it possible to have array as an argument to INDIRECT(), so INDIRECT() returns array?
   • https://superuser.com/questions/1196243/simplify-a-sum-of-indirect-cell-values