如何在Excel中查找最常见的产品列组合及其出现频次

以下提供两种可直接落地的实现方式，可根据你的使用场景选择：

方法1：Excel内置Power Query实现（无需写代码，适合普通用户） #

适用Excel 2016及以后版本，操作步骤如下：

• 加载数据：选中你的原始数据表，点击「数据」选项卡→「从表格/区域」，确认勾选「我的表格有标题」后进入Power Query编辑器
• 数据清洗：选中「客户ID」列，右键选择「逆透视其他列」，此时生成的「属性」列对应产品名、「值」列对应购买标识；筛选「值」列仅保留+的行，删除「值」列
• 聚合单客户购买列表：点击「转换」选项卡→「分组依据」，分组列选「客户ID」，新列名设为购买产品，操作选「提取值」，值列选「属性」，分隔符用英文逗号即可
• 生成产品组合：添加两个自定义列，公式分别为：
  2产品组合：= List.Combinations(Text.Split([购买产品], ","),2)
  3产品组合：= List.Combinations(Text.Split([购买产品], ","),3)
• 统计频次：分别展开2产品、3产品组合列到新行，选中组合列后再次点击「分组依据」，操作选「计数行」，得到的结果按计数值降序排序，就能看到最高频的组合及对应次数

方法2：Python脚本实现（数据量大时效率更高） #

提前把原始表格存为data.xlsx，安装好pandas库后直接运行以下代码即可：

import pandas as pd
from itertools import combinations

# 读取原始数据
df = pd.read_excel("data.xlsx")
# 提取所有产品列名（自动排除客户ID列）
product_cols = [col for col in df.columns if col != "客户ID"]

com_count = {}
# 遍历每个客户的购买记录
for _, row in df.iterrows():
    # 提取当前客户已购买的产品列表
    bought_products = [p for p in product_cols if row[p] == "+"]
    # 统计2产品组合频次
    for comb in combinations(bought_products, 2):
        comb_name = " & ".join(comb)
        com_count[comb_name] = com_count.get(comb_name, 0) + 1
    # 统计3产品组合频次
    for comb in combinations(bought_products, 3):
        comb_name = " & ".join(comb)
        com_count[comb_name] = com_count.get(comb_name, 0) + 1

# 结果导出到Excel
result_df = pd.DataFrame(com_count.items(), columns=["产品组合", "出现次数"]).sort_values(by="出现次数", ascending=False)
result_df.to_excel("产品组合频次统计结果.xlsx", index=False)

运行后生成的产品组合频次统计结果.xlsx第一行即为最高频的产品组合。

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