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

筛选

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

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

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

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

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
    总订单数

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

@畅心 感谢提供思路，非常感谢！

供参考：

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

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))

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

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

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

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

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

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

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

@回头便知

能力有限，仅供参考

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

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

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

@焦棚子 你的思路我认可，更有目的性是目前的一种妥协吧，适用部分场景。
不过你写的ＤＡＸ还是非常实用的，再次感谢你！我用４０多万数据，６０００多个商品，跑了１５分钟能跑完，效率很不错了，就是吃内存多，现在用３２Ｇ内存了

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

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

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

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

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

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

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

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

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

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

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

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

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