☰

动态数组小计无需使用 VBA 或易失性函数即可更改其列大小

tag-icon • tag-icon microsoft-excel worksheet-function office365 array
动态数组小计无需使用 VBA 或易失性函数即可更改其列大小

我需要制作一个动态数组小计，通过另一个动态数组更改其列大小。示例表、挑战、条件和我所做的尝试如下。

[示例表]

挑战：

  • 使 D 列中的（部分）动态小计数组完全动态化，并通过另一个动态数组更改其列大小
  • 使用 D5#（辅助数组）修改动态小计数组中的范围部分 $C$10:$L$28
  • 它应该是一个通用公式，可以在 D 列中复制粘贴；A 列和 B 列中将有数千个条件 1 和 2
  • 避免使用 VBA、易失性函数（OFFSET、INDIRECT、INFO、CELL 等）或其他昂贵的方法

先决条件：

  • 项目开始/结束日期决定动态数组 D6#（月-年）的大小
  • D6# 用于动态数组 D7# (工作时间/月)
  • D5# 是一个辅助数组，用于确定动态数组小计的大小
  • A 列和 B 列中的条件 1 和 2 用于动态数组小计 - 添加或删除行时公式不应被破坏；用户将添加或删除行并编辑条件
  • 动态数组小计汇总了每个条件 1 和 2 的每月工作时间

尝试：

  • SUBTOTAL 只能动态显示 AVERAGE、COUNT、MAX、MIN、SUM 等。我想不出任何方法来利用此功能。
  • 公式：=SUBTOTAL(SEQUENCE(11),reference range)
  • 如果条件范围是动态的，则 SUMIFS 可以是动态的，但这不是我想要的方式；它需要使用动态数组来确定列大小。我拥有的是非动态公式，需要手动复制到其余列。(https://stackoverflow.com/questions/69655057/can-one-do-a-sumifs-on-a-dynamic-spilled-range-and-return-a-2d-array）
  • 公式：=SUMIFS(D:D,$A:$A,$A10,$B:$B,TEXTBEFORE($B10," Subtotal"))
  • MMULT、TRANSPOSE 和 FILTER 组合是我想到的最接近的解决方案，它是部分动态的，不会自动更改其列大小。如上所述，范围 $D$8:$L$26 是修改起来比较棘手的部分，它以某种方式引用了辅助数组 D5#，而无需使用 OFFSET、INDIRECT 或其他易失性函数。在此阶段，我需要手动设置要求和的范围的公式。（带有溢出动态数组的 Excel365 Sumif）
  • 公式：=LET(Criteria,($A$8:$A$26=A10)*($B$8:$B$26=TEXTBEFORE($B10," Subtotal")), MMULT(TRANSPOSE(FILTER(Criteria,Criteria=1)),--FILTER($D$8:$L$26,Criteria=1)))
  • 我尝试让模板不使用 VBA，因为用户可以发挥创造力来破坏我制作的旧模板中的宏。构建和维护代码也很耗时。

如果您能针对我所面临的困难提出解决方案，我将不胜感激。

答案1

这里有很多问题需要解决，但如果我理解正确的话，以下公式应该有效：

=LET(
arr, $D$7#:A10,
_c1, CHOOSECOLS(arr, 1),
_c2, CHOOSECOLS(arr, 2),
incl, (_c1=A10)*(_c2=TEXTBEFORE(B10, " Subtotal")),
BYCOL(FILTER(DROP(arr,, 3), incl), LAMBDA(c, SUM(c))))

此方法利用了动态数组的一个鲜为人知的技巧，即使用范围运算符（冒号）扩展 的维度，$D$7#以包含它与单元格 之间的所有内容A10。基本上，生成的数组引用从两个范围的最小列（A）和最小行（7）到两个范围的最大列（动态）和最大行（10）（A7:L10在此示例中）。笔记：我之所以对动态数组使用绝对范围引用，是为了能够将公式复制并粘贴到每个小计行的 D 列中。

CHOOSECOLS()用于识别每个条件列，并DROP()通过删除前 3 列来识别数据范围。BYCOL()然后用于动态地SUM()过滤数组的每一列（因此单元格中的“帮助数组”D5不再需要）。

结果：

分类:

我希望这就是你的意思。干杯！

相关内容