Excel - 如何计算列中具有指定条件的唯一记录？

Question

我有如下表格:

我想统计每家商店购买单件商品的账单有多少？有多少购买了 2 件和全套元素？结果应该如下所示

在商店“m3”中，账单 300 购买了 2 件商品 C，但在最终结果中只计为 1 件。这是我挣扎的地方，因为我尝试过COUNTIFS(A:A, "=m1", B:B, "=A") (然后为 B 和 C 添加计数)以获取商店 m1 的单个项目但无法弄清楚如何用唯一的账单号码来区分。

请不要介意询问是否需要更多说明，我确实更喜欢 excel 内置函数而不是 VBA。

Answer 1

使用 Office 365 公式，我们从查找单例的基本公式开始:

=BYROW(E2:E4,
    LAMBDA(z,SUM(--(
        BYROW(--(COUNTIFS(A:A,z,C:C,UNIQUE($C$2:$C$8),B:B,{"A","B","C"})>0),
            LAMBDA(a,SUM(a)))=1))))

这会给你一个结果:

其他都是该公式的变体:

甲乙

=BYROW(E2:E4,
    LAMBDA(z,SUM(--(
        BYROW(--(COUNTIFS(A:A,z,C:C,UNIQUE($C$2:$C$8),B:B,{"A","B"})>0),
            LAMBDA(a,SUM(a)))=2))))-J2#

B,C

=BYROW(E2:E4,
    LAMBDA(z,SUM(--(
        BYROW(--(COUNTIFS(A:A,z,C:C,UNIQUE($C$2:$C$8),B:B,{"C","B"})>0),
            LAMBDA(a,SUM(a)))=2))))-J2#

A,C

=BYROW(E2:E4,
    LAMBDA(z,SUM(--(
        BYROW(--(COUNTIFS(A:A,z,C:C,UNIQUE($C$2:$C$8),B:B,{"C","A"})>0),
            LAMBDA(a,SUM(a)))=2))))-J2#

全部

=BYROW(E2:E4,
    LAMBDA(z,SUM(--(
        BYROW(--(COUNTIFS(A:A,z,C:C,UNIQUE($C$2:$C$8),B:B,{"A","B","C"})>0),
            LAMBDA(a,SUM(a)))=3))))

我们必须将 -J2# 添加到 double 的末尾，以便在它具有所有三个时删除。它在两个地方都很重要。

对于旧版本的 Excel，我们需要对 SUMPRODUCT、MMULT、INDEX、MODE.MULT 等进行一些操作

=SUMPRODUCT(
    --(MMULT(
        --(COUNTIFS(A:A,E2,C:C,INDEX(C:C,N(IF({1},MODE.MULT(IF(MATCH($C$2:$C$8,C:C,0)=ROW($C$2:$C$8),ROW($C$2:$C$8)*{1,1}))))),B:B,{"A","B","C"})>0),
        {1;1;1})=1))

=SUMPRODUCT(
    --(MMULT(
        --(COUNTIFS(A:A,E2,C:C,INDEX(C:C,N(IF({1},MODE.MULT(IF(MATCH($C$2:$C$8,C:C,0)=ROW($C$2:$C$8),ROW($C$2:$C$8)*{1,1}))))),B:B,{"A","B"})>0),
        {1;1})=2))-J2

=SUMPRODUCT(
    --(MMULT(
        --(COUNTIFS(A:A,E2,C:C,INDEX(C:C,N(IF({1},MODE.MULT(IF(MATCH($C$2:$C$8,C:C,0)=ROW($C$2:$C$8),ROW($C$2:$C$8)*{1,1}))))),B:B,{"B","C"})>0),
        {1;1})=2))-J2

=SUMPRODUCT(
    --(MMULT(
        --(COUNTIFS(A:A,E2,C:C,INDEX(C:C,N(IF({1},MODE.MULT(IF(MATCH($C$2:$C$8,C:C,0)=ROW($C$2:$C$8),ROW($C$2:$C$8)*{1,1}))))),B:B,{"A","C"})>0),
        {1;1})=2))-J2

=SUMPRODUCT(
    --(MMULT(
        --(COUNTIFS(A:A,E2,C:C,INDEX(C:C,N(IF({1},MODE.MULT(IF(MATCH($C$2:$C$8,C:C,0)=ROW($C$2:$C$8),ROW($C$2:$C$8)*{1,1}))))),B:B,{"A","B","C"})>0),
        {1;1;1})=3))

这些中的每一个都将被放置在各自列的第一行，并在退出编辑模式时使用 Ctrl-Shift-Enter 而不是 Enter 确认为数组公式。

他们将被拖/复制到列中。