☰

将范围组的最大和写入单个单元格

tag-icon • tag-icon microsoft-excel worksheet-function
将范围组的最大和写入单个单元格

考虑以下人为的例子，其中有一系列单元格代表多年来某事物的每月计数以及主要的摘要列，例如：

.|A    |B      |C    |D    |E    |F    |G    |H    |I    |J    |K    |...
-+-----+-------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
1|     |Most/3M|Jan19|Feb19|Mar19|Apr19|May19|Jun19|Jul19|Aug19|Sep19|...
2|Foo  |     14|    1|    3|    7|    3|    4|    2|    1|    3|    1|...
3|Bar  |     13|    3|    4|    1|    2|    2|    2|    9|    1|    1|...
4|Baz  |     18|    2|    3|    8|    7|    3|    2|    3|    7|    1|...

该摘要栏B:B代表任何三个月期间的最高计数，其计算方式为天真地使用：

=MAX(SUM($C2:$E2),SUM($D2:$F2),SUM($E2:$G2),
     SUM($F2:$H2),SUM($G2:$I2),SUM($H2:$J2),
     SUM($I2:$K2))

...然后向下填充，但是维护起来非常烦人——因为值被添加到最右边的部分，例如，，L:L公式M:M必须用SUM($J2:$L2)和SUM($K2:$M2)等进行更新。

一个替代方案是：

={LARGE(($C2:$I2 + $D2:$J2 + $E2:$K2), 1)}

...这不是相当非常糟糕，因为在三个月的时间范围内，将 改为 、 改为 和 改为 是合理的，而这本身可以通过对 的可怕:$I2使用:$J2得出:$J2，:$K2如下:$K2所示：:$L2INDIRECT

={LARGE(INDIRECT("R[0]C3:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000)),FALSE)+
        INDIRECT("R[0]C4:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+1,FALSE)+
        INDIRECT("R[0]C5:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+2,FALSE), 1)}

但是，随着窗口大小的增加（即必须添加越来越多的+子句），以及/或者如果窗口大小是可配置的，例如基于的值B1（实际上只是值3，具有自定义数字格式"Most/"0"M"） ，这会变得痛苦。

我确实想到了以下可能之一：

={MAX(SUM(OFFSET($C2:$I2,0,0,1,3)))}
={LARGE(SUM(OFFSET($C2:$I2,0,0,1,3)), 1)}

...我只需要更新给定的范围和组大小OFFSET，目的是最终动态地推导出这一点，例如使用另一个可怕的INDIRECT，例如：

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&
                           (COUNTA(OFFSET($C2,0,0,1,2000))+COLUMN($C:$C)-B$1),
                           FALSE),
                  0,0,1,B$1)),
       1)

使用时评估公式要完成上述可怕的普通的形式，我发现它确实最终解析为：

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+3-B$1),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(12-3),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C9",FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET($C$2:$I$2,0,0,1,3)),1)
=LARGE(SUM($C$2:$E$2),1)
=LARGE(11,1)
=11

...因此看起来给定的范围OFFSET没有扩展为数组，而是直接使用该范围的左上角单元格OFFSET。

在大批形式（即CTRLENTER），它最终解析为：

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+{3}-B$1),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&({12}-3),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT({"R[0]C3:R[0]C9"},FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,3)),1)
=LARGE(SUM({#VALUE!}),1)
=LARGE(1,1)
=1

...因此看起来数组扩展发生得太早（即的结果COLUMNS($C:$C)），这随后导致INDIRECT调用中断。

（LARGE和都MAX产生相同的值——我之所以使用是LARGE因为它的文档表明它需要一个数组，而MAX需要一个单个值的无界参数列表。）

假设偏好不使用 VBA 或更改工作表布局，则存在：

  1. 有什么技巧力量在正确的位置进行数组扩展，以便SUM(OFFSET)发出一个可以被使用的数组LARGE？
  2. 有没有其他替代方法，需要对公式进行零到最小程度的修改，因为数据被添加到了工作表的右侧？

（PS。我使用的是 Excel 2019，而不是 O365/Insiders，所以我还没有动态数组/SEQUENCE内容，这看起来可能会变得相关。依赖于此的答案可能对后人有用，但不能解决我的迫切需要......）

答案1

我想建议使用以下方法来获取一年中任意 3 个季度的最高总和，包括辅助数据和最大 3 个季度的总和值。

在此处输入图片描述

怎么运行的：

  • 输入相关月份的日期Row 1并应用单元格格式mmm-yy。
  • 在 Rnage 中插入季度的名称T9:T12。

  • 单元格中的公式U9，填写完毕。

    =IF(ROW(A1)>=5,"",(SUMPRODUCT((ROUNDUP(MONTH(T$1:AE$1)/3,0)=ROW(A1))*(T$2:AE$2))))

注意：

  • ROUNDUP(MONTH(T$1:AE$1)/3,0)=Row(A1)返回1并且0s，1是月份属于问题 1并且0当不等等时对于其他季度像2，3& 4。
  • SUMPRODUCT，毫不费力地处理所有这些范围的数据。
  • =ROW(A1))返回1，代表Q1以此类推2, 3 & 4。
  • =IF(ROW(A1)>=5,""Blank当公式无法获取 4 以后的季度值时返回。

获取每个季度的数据后，现在在单元格中输入公式U14来对 3 个最大值进行求和。

{=SUM(LARGE(U$9:U$12,{1,2,3}))}

注意：

  • 最好使用数组（CSE）形式的公式，因此需要以Ctrl+Shift+Enter，否则它也可以作为非数组公式使用。

根据需要调整公式中的单元格引用。

相关内容