Excel 按行统计显示不同值及其出现次数的实现方法

方法1：Excel 365/2021及以上版本（动态数组公式一键生成） #

• 假设原表存放区域为A1:D4（A列是name字段，B-D列为数据列，首行是表头）
• 在新表A2单元格输入以下公式，会自动溢出填充所有行列内容：

=LET(
  原数据区,A2:D4,
  姓名列,INDEX(原数据区,,1),
  数值区,INDEX(原数据区,,2):INDEX(原数据区,,4),
  逐行计算,BYROW(数值区,LAMBDA(r,
    有效数值,FILTER(r,r<>"空值"),
    去重标签,UNIQUE(有效数值),
    出现次数,MMULT(--(有效数值=TOROW(去重标签)),SEQUENCE(ROWS(有效数值),,1,0)),
    TOROW(HSTACK(去重标签,出现次数),,1)
  )),
  HSTACK(姓名列,逐行计算)
)

• 手动在新表第一行填写表头name、label、count、label、count即可。

方法2：全版本通用（含Excel 2019及更低版本） #

通过先转一维表再统计的方式实现：

1. 生成一维源数据表

• 按快捷键Alt+D+P调出「数据透视表和数据透视图向导」
• 依次选择「多重合并计算数据区域」→ 下一步 →「创建单页字段」→ 下一步
• 选定区域框选你的全部原表（含表头），点击「添加」→ 下一步 → 选择透视表存放的空白位置，点击「完成」
• 双击生成的透视表右下角的总计单元格，会自动弹出所有原始数据的一维表，筛选掉值为「空值」的行

2. 横向展开统计结果

• 对生成的一维表按name和值分组计数，得到每个name对应各个标签的出现次数
• 用INDEX+MATCH组合公式横向填充结果：假设处理后的统计数据存放在G:I列（G列为name、H列为label、I列为count），新表A列提前填好所有name，B2单元格公式为=INDEX(H:H,MATCH($A2,$G:$G,0)+INT((COLUMN(A1)-1)/2))，C2单元格公式为=INDEX(I:I,MATCH($A2,$G:$G,0)+INT((COLUMN(A1)-1)/2))，选中B2和C2向右拉到E列，再整体向下填充所有行即可。

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