插入汇总行

题目:


现有每日明细数据,需要每7天插入一行汇总行,对最近7天数据求和。

解法1:

let
    源 = Excel.CurrentWorkbook(){[Name="案例"]}[Content],
    分组 = List.Transform({0..Number.RoundUp(Table.RowCount(源)/7)-1},each [a=Table.Range(源,_*7,7),b=a&#table({"日期","销量"},{{"汇总",List.Sum(a[销量])}})][b]),
    合并 = Table.Combine(分组)
in
    合并

表中共有39行,每7天一组,那就要先把表分成6份,前5张表每张7行,第6张表共4行。

第二步要对每张表在最后插入一行汇总行,插入行有两种方法,一种是使用Table.InsertRows,另一种是直接使用#table构建一张只有一行的表然后和原表连接,我们以后者为例:

这样就得到了6张带汇总行的表,再合并起来即可。
但是还有个问题是我们事先已经知道是39行分为6组,如果后面再增加行数还需要手动调参数,那么可以用表的总行数除以7,向上取整把6做成动态值。
不管第一列是连续日期还是其他任意值,此方法都适用,优先推荐解法1。

解法2:

let
    源 = Excel.CurrentWorkbook(){[Name="案例"]}[Content],
    分组 = Table.Group(源,"日期",{"a",each _&#table({"日期","销量"},{{"汇总",List.Sum([销量])}})},0,(x,y)=>Number.From(y-x>=#duration(7,0,0,0))),
    合并 = Table.Combine(分组[a])
in
    合并

首先使用Table.Group分组,非常常用的一个函数,每一天得到当天日期的一张表。

接着加第四参数0表示局部分组,第五参数自定义分组规则,这是group的非常规用法,之前有讲过,这样就把一列连续日期按照每7天分成一组。

接下来要做的就是对第三参数里的_进行改造,_表示每一个table,再构造一张只有一行汇总行的table,再和_连接起来就可以了。

最后一步深化出分组列[a],使用Table.Combine将所有分组表合并。
此方法仅当分组依据列为连续日期时才适用,讲这个方法主要是想介绍一下group的用法。

方法很多,前面讲的List.AccumulateList.Generate也都能做,但因为不是最优解就不介绍了。

附件

4 Replies to “插入汇总行”

  1. 如果汇总周期为11天或15天。整个公式是不是要重写?
    有没有灵活的做法随时调整汇总周期?

    1. 把公式里的7改成你要的数字就行了,要灵活的话就把7改成x做成自定义函数,想几天就几天

  2. 谢谢博主抛砖引玉!

    接着该案例探讨下:假设通过构建#table作为汇总行,数据列的数量比较多(上千列),在构建table的时候是否需要逐个对每一列数据定义汇总方式list.sum(构建table的字段名是相对容易的,可以通过table.columnname得到字段list)?有没有相对简便方法?

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注