行列互换后，如何在Google Sheets/Numbers中统计高频商品组合？

你的数据结构为订单号为行、商品为表头（共15000+行），与原问题的行列结构完全相反，原公式无法直接套用，以下是适配后的公式及使用说明：

数据结构假设 #

• A列为订单号
• B~X列为商品名称（表头），对应单元格内用1标记购买，0或空值标记未购买

组合统计矩阵公式 #

假设你要生成的统计矩阵：

• 左侧Z列（Z3开始）为行商品名称
• 顶部AA行（AA2开始）为列商品名称
• 单元格AA3对应「行商品Z3」和「列商品AA2」的共同购买订单数

在AA3单元格输入以下公式，然后向右向下填充：

=SUMPRODUCT(--(INDEX($B$2:$X$15001,,MATCH($Z3,$B$1:$X$1,0))=1),--(INDEX($B$2:$X$15001,,MATCH(AA$2,$B$1:$X$1,0))=1))

公式逻辑说明 #

1. INDEX($B$2:$X$15001,,MATCH($Z3,$B$1:$X$1,0))：通过商品名称匹配，定位到当前行商品对应的整列订单购买数据
2. --(...)：将单元格的逻辑判断结果（TRUE/FALSE）转换为数值1/0，满足SUMPRODUCT的求和要求
3. SUMPRODUCT会计算两个商品列中同时为1的行数，即两个商品被共同购买的订单总数

优化方案（Excel 365/2021 适用） #

如果使用支持动态数组的Excel版本，可一次性生成完整统计矩阵，无需手动填充：

=LET(
    商品列表,$B$1:$X$1,
    数据区域,$B$2:$X$15001,
    组合矩阵,MAKEARRAY(COLUMNS(商品列表),COLUMNS(商品列表),
        LAMBDA(r,c,
            SUMPRODUCT(--(INDEX(数据区域,,r)=1),--(INDEX(数据区域,,c)=1))
        )
    ),
    HSTACK(VSTACK("",商品列表),VSTACK(商品列表,组合矩阵))
)

该公式会自动生成带商品名称表头的完整统计矩阵。

注意事项 #

• 若你的数据用其他标记（如"购买"文本）表示已购买，将公式中的=1替换为="购买"即可
• 15000行数据量较大，建议关闭Excel自动重算功能，完成公式填充后手动刷新计算

内容的提问来源于stack exchange，提问作者numbers garden