Google Sheets中获取区域全部最常见值及相关数据处理问题

一、拆分每行的数字与文本 #

假设每行数据在A列到C列（可自行调整范围），用以下公式提取数字和文本：

• 提取数字（用于计算平均值）：

  =ARRAYFORMULA(IFERROR(VALUE(REGEXEXTRACT(FLATTEN(A2:C2), "\d+")), ""))
  

• 提取文本（用于找高频值）：

  =ARRAYFORMULA(IFERROR(TRIM(REGEXEXTRACT(FLATTEN(A2:C2), "[^\d]+")), ""))
  

二、计算数字平均值 #

将提取数字的公式嵌套进AVERAGE，自动忽略空值和错误：

=AVERAGE(IFERROR(VALUE(REGEXEXTRACT(FLATTEN(A2:C2), "\d+")), ""))

三、提取最常见文本（解决MODE报错+单单元格显示） #

针对无重复值时的报错问题，用以下公式判断重复情况并合并结果到单个单元格：

=LET(
  texts, ARRAYFORMULA(IFERROR(TRIM(REGEXEXTRACT(FLATTEN(A2:C2), "[^\d]+")), "")),
  filtered_texts, FILTER(texts, texts<>""),
  counts, COUNTIF(filtered_texts, filtered_texts),
  max_count, MAX(counts),
  mode_values, UNIQUE(FILTER(filtered_texts, counts=max_count)),
  IF(COUNTA(mode_values)=COUNTA(filtered_texts), "所有文本出现次数相同", TEXTJOIN(", ", TRUE, mode_values))
)

公式说明： #

1. 先提取并清理所有文本，过滤空条目
2. 统计每个文本的出现次数，找到最高频次
3. 提取所有达到最高频次的文本并去重
4. 若所有文本均唯一，返回提示；否则用逗号分隔合并结果

四、整合平均值与高频文本（可选） #

如果要把两个结果放在同一个单元格，用TEXTJOIN合并：

=LET(
  nums, ARRAYFORMULA(IFERROR(VALUE(REGEXEXTRACT(FLATTEN(A2:C2), "\d+")), "")),
  avg_score, AVERAGE(nums),
  texts, ARRAYFORMULA(IFERROR(TRIM(REGEXEXTRACT(FLATTEN(A2:C2), "[^\d]+")), "")),
  filtered_texts, FILTER(texts, texts<>""),
  counts, COUNTIF(filtered_texts, filtered_texts),
  max_count, MAX(counts),
  mode_values, UNIQUE(FILTER(filtered_texts, counts=max_count)),
  text_result, IF(COUNTA(mode_values)=COUNTA(filtered_texts), "所有文本出现次数相同", TEXTJOIN(", ", TRUE, mode_values)),
  TEXTJOIN(" | ", TRUE, "平均值: "&ROUND(avg_score, 1), "高频文本: "&text_result)
)

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