分段频次分布统计

题目:


即分箱问题,每10岁划分为一个年龄段,统计各分段的人数。

解法1:

看到题目第一反应可能就想到分组,但是表里原来没有分组的依据列,所以只需要添加一个年龄段的列,再根据这一列分组就可以了。分组完顺序是乱的,最后再加个排序。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    年龄段 = Table.AddColumn(源, "年龄段", each Text.Format("#[left]-#[right]",[left=Number.IntegerDivide([年龄]/10,1)*10,right=left+9])),
    分组 = Table.Group(年龄段, {"年龄段"}, {"人数",Table.RowCount}),
    排序 = Table.Sort(分组,{"年龄段", 0})
in
    排序

简单说下求年龄段的思路:先求出所属年龄段的最小值,也就是0,10,20,30这种,比如22→20,34→30,即去掉个位数。那么可以先除以10,然后用Number.IntegerDivide取整,相当于工作表函数的INT,得到的结果再乘以10。求出最小值再加9得到最大值,使用Text.Format合并以避免因数据类型不同导致的错误。

解法2:

刚才是根据已有数据添加列然后分组,但是有一个问题就是比如10-20年龄段是0人,如果按刚才分组的方法,整行就都不显示了,而实际中往往都是要的,那么可以换个方法。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    年龄段 = Table.FromColumns({List.Transform(List.Numbers(0,6,10),each Text.Format("#{0}-#{1}",{_,_+9}))},{"年龄段"}),
    分组 = Table.AddColumn(年龄段, "人数", each Table.RowCount(Table.SelectRows(源,(x)=>x[年龄]>=Number.From(Text.Split([年龄段],"-"){0}) and x[年龄]<Number.From(Text.Split([年龄段],"-"){1}))))
in
    分组

先把每个年龄段构建起来,然后对整张表筛选,筛选出大于等于左边的且小于右边的,再对筛选表进行行计数即可。

解法3:

与解法2类似,也是先构建年龄段,只不过这次不是筛选,而是用Table.Partition进行哈希分表,最后再对分表进行行计数。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    年龄段 = List.Transform(List.Numbers(0,6,10),each Text.Format("#{0}-#{1}",{_,_+9})),
    分表 = Table.Partition(源,"年龄",6,each Number.IntegerDivide(_/10,1)),
    合并 = Table.FromColumns({年龄段,List.Transform(分表,Table.RowCount)},{"年龄段","人数"})
in
    合并

替代解法:

以上给了三种解法,难度依次递增,用于解决小数据量的分段频次分布统计问题,以及练习M语言写法。
但是毕竟PQ不是主要用来计算的,所以在数据量较大的情况下效率不会太高,所幸在Excel和Power BI Desktop中都有直接的功能可以实现。

Excel:

插入-数据透视表,右击行标签字段,在弹出的窗口中点击"组合"。

设置起止值以及步长,确定即可。

但遗憾的是该功能仅普通透视表可用,而Power Pivot创建的透视表暂不支持。

Power BI Desktop:

在行字段点击箭头,选择新建组:

设置装箱大小或装箱数:

效果:

附件

9 Replies to “分段频次分布统计”

  1. 解法二对于在统计学生成绩时,当成绩分数有小数分时有错误。试过成绩分数有2位小数时出错。

    1. 解法二是筛选区间,只要分数在最小值和最大值之间就可以了,小数也没问题的。出错应该不是小数的问题,你把报错提示发出来看看?

  2. 解法2:分组 = Table.AddColumn(年龄段, "人数", each Table.RowCount(Table.SelectRows(源,(x)=>x[年龄]>=Number.From(Text.Split([年龄段],"-"){0}) and x[年龄]x[年龄]>=Number.From(Text.Split([年龄段],"-"){0}) and x[年龄]<=Number.From(Text.Split([年龄段],"-"){1}))))

    1. 应先提取数据源的[年龄]列的最大值与最小值的范围,然后在后续操作,这样会更科学习一些。

  3. 解法一中:{"人数",Table.RowCount} 这里看不懂。
    Table.RowCount(table as table) as number 我看说明里是这么用的。不参加数返回的是?
    想添加个AddColumn了解下实际值,不会写。

      1. 我想看看值,应该怎么写呢
        分组1 = Table.AddColumn(年龄段, "rowcount", each Table.RowCount(_)),

        Expression.Error: 无法将类型 Record 的值转换为类型 Table。
        详细信息:
        Value=Record
        Type=Type

发表回复

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