如何统计20列×150000行整表内商品名称的唯一计数

需求说明 #

需要对20列×约15万行的商品数据，统计所有有效商品名称的出现次数，最终输出「商品名称-计数」的两列结果表。
你之前用普通数据透视表无法得到结果，是因为普通透视表仅支持单列维度统计，无法直接跨20列合并计数，用下面两种方法都可以快速实现：

方案1：Excel Power Query（无需写代码）

• 选中所有源数据区域，点击顶部菜单栏「数据」→「从表格/区域」，将数据导入Power Query编辑器
• 按住Ctrl选中全部20列，右键点击「逆透视列」，此时所有列的内容会被合并为单独的「值」列，同时自动生成对应的列名、行号字段
• 筛选掉「值」列中等于n/a的无效记录
• 点击顶部「转换」→「分组依据」，分组字段选择「值」，新列名设置为Qty，聚合方式选择「计数行」
• 修改列名：将「值」列重命名为ItemName，点击「关闭并上载」即可导出结果到Excel工作表，15万行数据处理耗时不到1分钟

方案2：Python Pandas（适合批量处理场景）

你可以直接运行以下代码实现统计：

import pandas as pd

# 读取源数据，支持csv、xlsx等格式，按需替换读取方法和文件路径
df = pd.read_excel("你的源数据文件.xlsx")

# 合并所有列、过滤无效值、统计频次
count_result = (
    df.stack() # 将多列堆叠为单列
    .replace("n/a", pd.NA) # 替换无效值为缺失值
    .dropna() # 删掉缺失值
    .value_counts() # 统计每个商品的出现次数
    .reset_index() # 转为DataFrame格式
)
# 修改列名匹配要求
count_result.columns = ["ItemName", "Qty"]

# 导出结果到文件
count_result.to_excel("商品计数结果.xlsx", index=False)

参考数据样例 #

源数据表（DATA）

结果表（RESULT）

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