【Excel 传统函数搞死人】怎么实现多条件订单数的统计?

Power Query 回头便知 ⋅ 于 2018-10-23 11:15:37 ⋅ 最后回复由 焦棚子 2018-10-31 13:58:17 ⋅ 2226 阅读

之前我是以传统函数+VBA使用为主的人来说,个人觉得技术还是很不错的,很多问题都能搞定。但终于遇到一个瓶颈,即使用VBA来实现这种效果是比较费劲的,尤其是行数多的情况下会比较慢!所以现在在寻找新的技术解决方案,请问大家M语言能否高效实现这种效果的汇总统计?

先上图:file

说明:

1、图中左边2列是源数据,一个【订单号】可以对应多个【商品代码】,一个【商品代码】也可以对应多个【订单号】(其实就是销售记录表)

2、图中右边就是需要的结果:(我还是以传统Excel的操作来表达吧)
●首先对源数据中的【商品代码】列进行删除重复项
●然后把这些惟一的【商品代码】不重复地两两组合(不区分顺序,即谁是A,谁是B无所谓)
●这样就得出了图中右半区的【商品A】+【商品B】这2列内容,图中数据正好有28种商品组合
●再针对每一行的【商品A】+【商品B】,去汇总【包含有A的订单数】、【包含有B的订单数】,【同时包含A、B的订单数】,【总订单数】

请M大神们指点一下,这种效果应该怎么实现,感谢感谢~

【以下是图中左边2列的数据源】

订单号 商品代码
2543 1473
2543 1540
2544 1473
2544 1540
2544 1904
2545 1473
2545 1540
2545 1076
2545 1649
2546 1540
2547 1540
2547 1912
2547 1699
2547 1927
最佳答案
  • Alex MOD 社区清洁工
    2018-10-23 13:49:44

    楼上畅神只写了一半,我接着补全一下吧:

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
        分组 = Table.Group(源, {"订单号"}, {"组合", each [商品代码]}),
        商品代码 = List.Distinct(源[商品代码]),
        商品AB = Table.FromRows(List.TransformMany({1..List.Count(商品代码)}, each List.Range(商品代码,_),(x,y)=>{商品代码{x-1},y}),
    {"商品A","商品B"}),
        包含A的订单数 = Table.AddColumn(商品AB, "包含A的订单数", each Table.RowCount(Table.SelectRows(分组,(x)=>List.Contains(x[组合],[商品A])))),
        包含B的订单数 = Table.AddColumn(包含A的订单数, "包含B的订单数", each Table.RowCount(Table.SelectRows(分组,(x)=>List.Contains(x[组合],[商品B])))),
        同时包含AB的订单数 = Table.AddColumn(包含B的订单数, "同时包含A+B的订单数", each Table.RowCount(Table.SelectRows(分组,(x)=>List.ContainsAll(x[组合],{[商品A],[商品B]})))),
        总订单数 = Table.AddColumn(同时包含AB的订单数, "总订单数", each Table.RowCount(分组))
    in
        总订单数
回复数量: 29
  • Ntt docomo
    2018-10-23 11:45:08

    筛选

  • 回头便知
    2018-10-23 11:48:07

    @Ntt docomo 我是要遍历所有的商品组合……“筛选”2个字就能搞定?能展开讲讲?感谢·

  • 焦棚子 为你加状态
    2018-10-23 11:55:11

    是要做购物篮分析?还是怎么?
    你这只是有子单,还有父单呢。

  • 回头便知
    2018-10-23 11:58:25

    @焦棚子 目前就是题目中的这个需求,请问用M语言能搞定不?

  • 畅心 你的问题其实爆照就能解决
    2018-10-23 12:32:33

    file

  • Alex MOD 社区清洁工
    2018-10-23 13:49:44

    楼上畅神只写了一半,我接着补全一下吧:

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
        分组 = Table.Group(源, {"订单号"}, {"组合", each [商品代码]}),
        商品代码 = List.Distinct(源[商品代码]),
        商品AB = Table.FromRows(List.TransformMany({1..List.Count(商品代码)}, each List.Range(商品代码,_),(x,y)=>{商品代码{x-1},y}),
    {"商品A","商品B"}),
        包含A的订单数 = Table.AddColumn(商品AB, "包含A的订单数", each Table.RowCount(Table.SelectRows(分组,(x)=>List.Contains(x[组合],[商品A])))),
        包含B的订单数 = Table.AddColumn(包含A的订单数, "包含B的订单数", each Table.RowCount(Table.SelectRows(分组,(x)=>List.Contains(x[组合],[商品B])))),
        同时包含AB的订单数 = Table.AddColumn(包含B的订单数, "同时包含A+B的订单数", each Table.RowCount(Table.SelectRows(分组,(x)=>List.ContainsAll(x[组合],{[商品A],[商品B]})))),
        总订单数 = Table.AddColumn(同时包含AB的订单数, "总订单数", each Table.RowCount(分组))
    in
        总订单数
  • 回头便知
    2018-10-23 14:22:53

    @Alex 效果很好,非常感谢大神,对于我来说综合运用这些知识点目前还是难点,现在主要是碎片学习各知识点。还要多看论坛里的文章,感谢感谢解答!

  • 回头便知
    2018-10-23 14:23:10

    @畅心 感谢提供思路,非常感谢!

  • 飞天篮球 Talk is cheap, show me your code.
    2018-10-23 15:14:46

    供参考:

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    
         T = [ g1 = Table.ToRows(Table.Group(源, "商品代码", {"c", each Table.RowCount(_)})),
               g2 = Table.Group(源, "订单号", {"a", each [商品代码]})[a],
                r = #table( {"商品A","商品B","包含A","包含B","A+B","总单数"},
                            List.TransformMany( {1..List.Count(g1)},
                                                each List.Skip(g1,_),
                                                (x,y)=>let l=List.Zip({g1{x-1},y}) 
                                                        in List.Combine(l)&
                                                           List.Transform({List.Select(g2,(m)=>List.ContainsAll(m,l{0})),g2},List.Count)
                                             )
                           ) ] [r]
    in
        T
  • Ntt docomo
    2018-10-23 16:05:54
    let
    fx=(x as list,y as number) as list =>
                    let
                        L=Table.Buffer(Table.Group(表1, {"订单号"}, {"组合", each [商品代码]})),
                        K=List.Buffer(表1[商品代码]),
                        A=List.RemoveLastN(x,y-1),
                        fx2=(x1 as list,y1 as number)=>
                                    let
                                       V=if y1=y then x1 else @fx2(S,y1+1),
                                       S=List.TransformMany(x1,each List.Skip(List.RemoveFirstN(x,(n)=>n<>_),1),(g,u)=>[商品A=g,
                                                                                                                        商品B=u,
                                                                                                                        包含A的订单数=List.Count(List.PositionOf(K,g,2)),
                                                                                                                        包含B的订单数=List.Count(List.PositionOf(K,u,2)),
                                                                                                                        #"同时包含A+B的订单数"=Table.RowCount(Table.SelectRows(L,(x)=>List.ContainsAll(x[组合],{g,u}))),
                                                                                                                        总订单数=List.Count(List.Distinct(表1[订单号]))
    ])
    
                                    in
                                       V
                    in
                        fx2(A,1)
    in
            Table.FromRecords(fx(List.Distinct(表1[商品代码]),2))
    
  • 回头便知
    2018-10-23 17:17:55

    @飞天篮球 感谢回复指导,效果也很好!

  • 回头便知
    2018-10-23 17:18:45

    @Ntt docomo 感谢回复,感觉上你的代码效率运行更快~~膜拜!

  • Ntt docomo
    2018-10-23 19:34:51

    @回头便知 我感觉应该是很慢才对

  • 焦棚子 为你加状态
    2018-10-23 22:22:15

    @回头便知
    前面众多M老师,提供个DAX的。

    file

    DEFINE
        VAR T1 =
            DISTINCT ( SELECTCOLUMNS ( '表1', "商品A", '表1'[商品代码] ) )
        VAR T2 =
            DISTINCT ( SELECTCOLUMNS ( '表1', "商品B", '表1'[商品代码] ) )
        VAR T3 =
            FILTER ( GENERATEALL ( T1, T2 ), [商品A] <> [商品B] )
        VAR T4 =
            SUMMARIZE (
                FILTER (
                    ADDCOLUMNS ( T3, "AB", [商品A] & [商品B] ),
                    [AB]
                        IN DISTINCT (
                            SELECTCOLUMNS (
                                ADDCOLUMNS ( T3, "AB", IF ( [商品A] < [商品B], [商品A] & [商品B], [商品B] & [商品A] ) ),
                                "AB", [AB]
                            )
                        )
                ),
                [商品A],
                [商品B]
            )
        VAR T5 =
            SUMMARIZE (
                T4,
                [商品A],
                [商品B],
                "包含A订单", CALCULATE (
                    DISTINCTCOUNT ( '表1'[订单号] ),
                    FILTER ( ALL ( '表1' ), '表1'[商品代码] = [商品A] )
                ),
                "包含B订单", CALCULATE (
                    DISTINCTCOUNT ( '表1'[订单号] ),
                    FILTER ( ALL ( '表1' ), '表1'[商品代码] = [商品B] )
                ),
                "包含A+B订单", 0
                    + COUNTROWS (
                        INTERSECT (
                            CALCULATETABLE (
                                VALUES ( '表1'[订单号] ),
                                FILTER ( ALL ( '表1' ), '表1'[商品代码] = [商品A] )
                            ),
                            CALCULATETABLE (
                                VALUES ( '表1'[订单号] ),
                                FILTER ( ALL ( '表1' ), '表1'[商品代码] = [商品B] )
                            )
                        )
                    ),
                "总订单", CALCULATE ( DISTINCTCOUNT ( '表1'[订单号] ), ALL ( '表1' ) )
            )
    EVALUATE
    T5
    ORDER BY
        [商品A],
        [商品B] ASC
  • 回头便知
    2018-10-27 01:39:10

    @焦棚子 感谢感谢!我用7万多行的数据测试了,无论是M还是DAX,都无法运算成功,我是32G内存!无语了,所谓的大数据运算工具,有点失望了……however,thank you all guys

  • 飞天篮球 Talk is cheap, show me your code.
    2018-10-27 13:07:53

    @回头便知
    供参考!
    大概1448种不同商品的两两组合所生成的表的行数几乎能到excel的最大行数104万8千行,这个运算量是很大的。
    还有一点,你说的“不能运算成功”不知道是不是这个问题:在excel里做DAX查询,是不能带有var定义变量的,否则在后续刷新的时候excel就会进入未响应状态,原因未知。可以把所有VAR嵌套到最后的公式里,这样刷新的时候就不会不响应了。DAX查询对1448种商品生成笛卡尔积的速度还是能接受的,2G内存的电脑在1分钟之内,有时候刷新数据excel会自动退出。

  • 焦棚子 为你加状态
    2018-10-27 18:23:23

    @回头便知

    能力有限,仅供参考

    “所谓的大数据运算工具”,LZ提出的这工作场景其实在dax中其实不适用,dax本身更擅长的度量值的产出,如此大数量的笛卡尔积也好组合也好,dax是不行的,LZ可能把这个用法有些偏差了。

    另外测试了下
    2000条数据时间在31秒,个人觉得当前场景相关分析应该更具目的性。

    file

  • 回头便知
    2018-10-30 23:55:08

    @飞天篮球 1个月产生数十万行数据,几千个商品很正常,但是如果用M来算确实没法算,一天都出不来结果……真是没办法了……

  • 回头便知
    2018-10-30 23:57:08

    @焦棚子 你的思路我认可,更有目的性是目前的一种妥协吧,适用部分场景。
    不过你写的DAX还是非常实用的,再次感谢你!我用40多万数据,6000多个商品,跑了15分钟能跑完,效率很不错了,就是吃内存多,现在用32G内存了

  • 焦棚子 为你加状态
    2018-10-31 11:19:11

    @回头便知
    没听懂你的意思呢?
    &就是连字符呗。

  • 回头便知
    2018-10-31 11:30:21

    @焦棚子
    在你的DAX的T4的组合处理中,逻辑是把商品A和B组合在一起,然后取distinct,以便获取到不重复的组合。
    但是如果遇到一些特殊情况,请问如何处理:
    1、对于纯数字的商品代码:商品代码A=11127,商品代码B=107881;
    商品代码A=107881,商品代码B=11127
    以上这种情况A&B的结果都是11127107881,则导致组合数还存在重复的组合,请问还能优化吗?我现在是通过在商品代码前加个字母去解决的。

    2、请教一下你写的DAX,针对文本型的商品代码为什么也能起作用?
    我意思是你的“[商品A] < [商品B]”,为什么用在文本比较中也能起作用?

  • 焦棚子 为你加状态
    2018-10-31 11:38:12

    @回头便知
    数字字符都可以,这其实是sql里面的一个方法。
    其中原理可以简单理解为ASCII编码原理吧。
    T4是考虑这种情况了的呢。是从排列去重到组合呢。判断了一个大于小于的问题的的。

  • 回头便知
    2018-10-31 13:19:41

    @回头便知
    在你写的DAX的T4中
    VAR T4 =
    SUMMARIZE (
    FILTER (
    ADDCOLUMNS ( T3, "AB", [商品A] & [商品B] ),

    是否应该把:[商品A] & [商品B] ,改成“IF ( [商品A] < [商品B], [商品A] & [商品B], [商品B] & [商品A] )”
    这样与下面Distinct的列对应,你怎么看?

  • 回头便知
    2018-10-31 13:20:17

    @焦棚子
    在你写的DAX的T4中
    VAR T4 =
    SUMMARIZE (
    FILTER (
    ADDCOLUMNS ( T3, "AB", [商品A] & [商品B] ),

    是否应该把:[商品A] & [商品B] ,改成“IF ( [商品A] < [商品B], [商品A] & [商品B], [商品B] & [商品A] )”
    这样与下面Distinct的列对应,你怎么看?

  • 焦棚子 为你加状态
    2018-10-31 13:36:07

    @回头便知
    我写的不一定就是最优的,可以根据自己的实际情况更改。
    另外你可以分布看看呢。
    这里面涉及到一个原始列和衍生列的东西,详细可以参考圣经哈。

  • 回头便知
    2018-10-31 13:48:59

    @焦棚子
    好吧,说了半天,我也没表达清楚。
    主要是因为我用这个DAX做了个分析,但是得出的行数比正确的组合数要多,不知道代码问题出在哪里了,隐约是觉得问题就出在T4那里,是有些组合没去重成功……一上午没搞清楚……好吧,感谢了

  • 焦棚子 为你加状态
    2018-10-31 13:55:27

    @回头便知
    你用少点数据测试下呢,分布一步一步T的看。我用你的数据一步步看的没问题呢。

  • 焦棚子 为你加状态
    2018-10-31 13:56:41

    @回头便知
    行数应该是一个组合的行数,不是排列的函行数。

  • 焦棚子 为你加状态
    2018-10-31 13:58:17

    @回头便知
    https://zhuanlan.zhihu.com/p/47561066
    看看这个呢,应该是你要的了。

暂无评论~~
  • 请务必阅读并严格遵守《社区管理规范与使用说明》
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 发送表情,可用的 Emoji 见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板粘贴上传, 格式限制 - jpg, png, gif
  • 不支持上传附件,请尽可能用文字和图片将问题描述清楚,如实在需要上传附件,可上传到 共享网盘 后分享链接
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!
Ctrl+Enter