主页 / coding / 问题 / 78479124
Accepted
Mark S.
Asked: 2024-05-14 23:28:09 +0800 CST

跨多列溢出范围求和，使用溢出公式将前 x 和组保留在一起

  • 772

总之，我的目标是获得溢出公式的摘要输出，该公式生成前 x 行，但最后一行对所有其他行进行分组，以便总数将与数据源相关联。（第一张图显示了它的工作原理，第二张图显示了我想要的效果。包含用于测试的 Markdown 表。）如果有更好的方式来表达标题，请告诉我，我会进行调整。

源表是通过针对数据透视表的溢出公式生成的。我这样做是为了在开始组合字段之前获得我想要关联的总数，而数据透视表没有一个干净的解决方案（要对前 x 个字段进行分组，您需要手动选择它们，但我需要它是动态的）。

在图像中，第一个表（左侧）是所有溢出公式。生成顶部的部门编号和侧面下方的 ID，以及值。

第二个表试图取前 5 名，然后将前 5 名之后的所有内容汇总到“其他”中；然而，正如您所看到的，这是获取 ID 6 的数据，而不是大于 6 的所有数据。我尝试了几种方法，但无法让它在执行此操作的同时立即对所有列求和。一种解决方案要求我复制附加列的公式，或者在数量较少时将其删除，因为这不是静态的。

约束：第一个表是根据溢出公式生成的，行数和列数不是静态的，因此第二个表需要考虑到这一点。对于示例，我使用了Sequence()标题和行，以及值的随机公式以获得一些数据。

该公式适用于除“其他”行之外的所有行：

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--(A2#=INDEX(I2#,r))*--(C1#=INDEX(K1#,1,c)) )))

我尝试将 K2 中的公式修改为下面的结果，但结果是#N/A：

=MAKEARRAY( ROWS(I2#), COLUMNS(K1#), LAMBDA(r,c,SUM(C2#*--IF(I2#>5,(A2#>=INDEX(I2#,r)),(A2#=INDEX(I2#,r)))*--(C1#=INDEX(K1#,1,c)) )))

在此输入图像描述

这是所需的输出：

在此输入图像描述

（Markdown 用于测试）

编号/部门 姓名 1 2 3 4 5
1 汤姆 2.00 3.25 4.50 5.75 7点
2 乔 2.75 4.00 5.25 6.50 7.75
3 克里斯汀 3.50 4.75 6.00 7.25 8.50
4 标记 4.25 5.50 6.75 8.00 9.25
5 本 5.00 6.25 7.50 8.75 10:00
6 简 5.75 7点 8.25 9.50 10.75
7 凯里 6.50 7.75 9.00 10.25 11.50
8 杰克 7.25 8.50 9.75 11点 12.25
9 玫瑰 8.00 9.25 10.50 11.75 13:00
10 米歇尔 8.75 10:00 11.25 12.50 13.75
11 迈克尔 9.50 10.75 12:00 13.25 14.50
12 保罗 10.25 11.50 12.75 14:00 15.25
13 何塞 11点 12.25 13.50 14.75 16:00
14 珊达 11.75 13:00 14.25 15.50 16.75
15 埃里克 12.50 13.75 15:00 16.25 17.50

这是帮助我创建第一个公式的问题

excel

2 个回答

  1. Best Answer

    VSTACK()您也可以尝试使用以下内容以及+ TAKE()+的组合BYCOL()/MMULT()：

    在此输入图像描述

    =VSTACK(
    TAKE(A1:G16, 6),
    HSTACK(
        6,
        "Others",
        BYCOL(
            DROP(A1:G16, 6, 2),
            LAMBDA(x, SUM(x))
        )
    )
)

    或者，使用MMULT()

    =VSTACK(
    TAKE(A1:G16, 6),
    HSTACK(
        6,
        "Others",
        MMULT(
            SEQUENCE(
                ,
                ROWS(A1:G16) -
                    6
            ) ^ 0,
            DROP(A1:G16, 6, 2)
        )
    )
)

    或者，建议使用Structured References它，这Tables是比以下更好的选项，但是，公式替代方案以 A 列为基础并将其用作lastRow. 但我的钱已经到了Tables，我永远不会去追求以下，这只是一个替代方案。

    =LET(
     _LastRow, MATCH(2,1/(A:A<>"")),
     _Data, A1:INDEX(A:G, _LastRow, ),
     VSTACK(TAKE(_Data,6), HSTACK(6, "Others", BYCOL(DROP(_Data,6,2), LAMBDA(x,SUM(x))))))
    • 1

  2. 请尝试这个（_data指生成数据的单元格）：

    =LET(
_data,[cell containing the spill formula],
_data_below_top_5,DROP(_data#,6,2),
_others_row, HSTACK("","Others",BYCOL(DROP(_data#,6,2),LAMBDA(_col,SUM(_col)))),
_table_before_adding_total,VSTACK(TAKE(_data#,6),_others_row),
_totals_row,HSTACK("","Total",BYCOL(DROP(_table_before_adding_total,1,2),LAMBDA(_col,SUM(_col)))),
_empty_row,MAKEARRAY(1,COLUMNS(_data#),LAMBDA(a,b,"")),
VSTACK(_table_before_adding_total,_empty_row,_totals_row)
)

    顺便说一句，BYCOL(array,SUM)虽然可以在 Windows 下运行，但不能在 Mac Excel (Microsoft 365) 下运行。

    • 0

相关问题