如何在Google Sheets中实现单单元格多变量按字母分类计数求和

需求说明 #

用户输入单元格遵循以下规则：

• 单条条目格式：数字+M/E/T/W/B + 空格 + SKU编号
• 多条条目之间以逗号分隔，无顺序、数量限制
• 需实现两个统计目标：每个字母的出现次数、对应条目前缀数字的分类累加

实现公式 #

一站式溢出公式（推荐） #

直接在空白单元格输入以下公式，即可自动输出5个字母的统计结果，第一列为对应字母的出现次数，第二列为数字总和，按M/E/T/W/B顺序排列：

=LET(
  // 拆分输入为单个条目，去除空值和多余空格
  entries, TOCOL(SPLIT(TRIM(A2), ",", 1, 1)),
  // 解析每个条目的前缀数字和对应字母
  parsed, BYROW(entries, LAMBDA(entry, HSTACK(
    IFERROR(VALUE(REGEXEXTRACT(entry, "^(\d+)[METWB]")), 0),
    IFERROR(REGEXEXTRACT(entry, "^\d+([METWB])"), "")
  ))),
  // 指定待统计的字母范围
  target_letters, {"M","E","T","W","B"},
  // 按字母分别统计次数和总和
  BYROW(target_letters, LAMBDA(letter, HSTACK(
    COUNTIF(INDEX(parsed,,2), letter),
    SUMIF(INDEX(parsed,,2), letter, INDEX(parsed,,1))
  )))
)

注意：将公式中的A2替换为你实际存放用户输入内容的单元格即可，支持Google Sheets 2022年及以后的版本，旧版本需要在公式外侧包裹ARRAYFORMULA函数。

单个字母统计公式 #

如果需要单独统计某个字母的结果，可使用以下两个公式，以统计字母T为例：

• 统计出现次数：
  =COUNTIF(BYROW(TOCOL(SPLIT(A2, ",", 1, 1)), LAMBDA(e, IFERROR(REGEXEXTRACT(e, "^\d+([METWB])"), ""))), "T")
• 统计数字总和：
  =SUMIF(BYROW(TOCOL(SPLIT(A2, ",", 1, 1)), LAMBDA(e, IFERROR(REGEXEXTRACT(e, "^\d+([METWB])"), ""))), "T", BYROW(TOCOL(SPLIT(A2, ",", 1, 1)), LAMBDA(e, IFERROR(VALUE(REGEXEXTRACT(e, "^(\d+)[METWB]")), 0))))

兼容说明 #

• 公式自动过滤输入内容中的多余空格、无效格式条目，不影响正常统计结果
• 仅识别指定的M/E/T/W/B五个字母，其他字母的条目不会被纳入统计
• 支持任意数量的输入条目，无上限限制

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