PQ-M及函数:数值计算(聚合函数与操作)

小勤:大海,在PowerQuery里面能不能对一列数求和、算个数、求最大、最小值之类的啊?

大海:你说的这些其实就是所谓的“聚合”计算,在Power Query里当然也是可以的,虽然Power Query的强项在于数据的接入、转换整理,而不在于统计分析,但毕竟在数据整理中也经常用到一些基本的计算,所以,这些功能都是有的。

小勤:啊,那怎么用?不是要写公式吧?

大海:基本的这些统计是不需要写公式的,通过简单的操作就能得到。我们先把数据丢进Power Query。

比如,我们要对一列数进行求和:

结果如下:

小勤:吐血,就只剩一个和了!

大海:对的,我们不要只看结果,看一下操作之后形成的公式,这里是通过List.Sum函数对“学分”那一列的所有数字进行了求和。在Power Query里往往不是为了得到这些统计结果,而是对统计的结果进行进一步的利用,所以,这里面关键是要对这些操作生成的公式进行理解,对一些常用的函数要学以致用。

小勤:原来这样。

大海:回到那个统计的菜单,我们看到还有最小值、最大值、中值、平均值等等,我们都试一下,可看到不同的统计方式对应的函数如下所示:

  • 求和:List.Sum()
  • 最小值:List.Min()
  • 最大值:List.Max()
  • 中值:List.Median()
  • 平均值:List.Average()
  • 标准偏差:List.StandardDeviation()
  • 值计数(非空数值的个数):List.NonNullCount()
  • 对非重复值进行计数:List.NonNullCount(List.Distinct(更改的类型[学分]))

大海:显然,前面的内容都是单一的函数使用,其中求和、最小值、最大值和平均值非常常用,所以,这几个函数最好都能记一下,实际上,这几个函数跟Excel里是一样的,只是在PowerQuery里要求在前面加上List而已。

小勤:嗯。对的,挺简单的。但最后那个好像比较复杂啊。

大海:最后那个是函数的嵌套,首先是用List.Distinct函数提取“学分列”里的不重复值,然后再用List.NonNullCount函数对前面提出来的不重复值进行计数。

小勤:嗯。理解。

大海:这里面List.Distinct函数也很重要,以后很多地方都会用到,所以最好也记一下。

小勤:好的。

大海:最后,你还记得咱们前面讲分组依据、透视的内容吗?里面的“操作”或”聚合“选项吗?我们先看分组的情况:

再看看透视里的聚合值函数:

这里面的内容跟前面的统计内容是不是差不多?你生成不同的分组操作或透视聚合看看,观察一下里面生成的函数,比如分组求和的:

形成的公式如下:

这里我们简单了解一下生成的Table.Group表分组函数的结构,可以理解,其中用List.Sum函数对按学员分组形成的各自对应的所有学分(列表)进行求和。

小勤:嗯,原来每一步操作和生成的公式内容基本就是一一对应的。

大海:对的。因为这样,所以以后在很多数据处理的过程中,就可以通过操作生成基本的公式,然后按需要进行修改,从而生成需要的结果。

小勤:怪不得前面那么多的案例都是通过操作实现结果,看来打好操作基础真的很重要,不然都通过自己写这些公式那就太难了。

大海:对的。后面我会给你更多的结合函数修改的内容去练,同时又可以学习更多的函数。

小勤:这样真是太好了。