Google Sheets查询重复关键词对应值分组求和方法

原有方案缺陷 #

原QUERY公式仅能匹配A列固定位置包含目标供应商、且B列对应指定国家的行数据，完全无法覆盖供应商名称出现在行内其他位置的记录，必然会出现货运量求和遗漏的问题，原公式如下：

=query($A$3:$C$6;"SELECT SUM(C) WHERE A matches '.*Walmart.*' and B='USA' ORDER BY SUM(C) DESC LABEL SUM(C) 'Kgs'";0)

可落地实现方案 #

方案1：全字段扁平化统计（适配中小数据集） #

不需要预先固定供应商所在列，先逐行提取该行所有出现的供应商名称，关联对应国家、单行对应的货运量拼成标准一维表，再做分组求和、按国家分组取货运量前4的供应商即可，参考公式：

=QUERY(
  REDUCE({"供应商","国家","货运量Kgs"},SEQUENCE(ROWS(A3:C)),LAMBDA(acc,row_idx,
    LET(
      cur_row,A3:INDEX(C:C,row_idx+2),
      country,INDEX(cur_row,1,2),
      weight,INDEX(cur_row,1,3),
      sup_list,TOCOL(TEXTSPLIT(TEXTJOIN("|",1,cur_row),,"|",1),1),
      VSTACK(acc,HSTACK(sup_list,IFERROR(SEQUENCE(COUNTA(sup_list),1,country,0),""),IFERROR(SEQUENCE(COUNTA(sup_list),1,weight,0),"")))
    )
  )),
  "SELECT Col1,Col2,SUM(Col3) WHERE Col2 IS NOT NULL GROUP BY Col1,Col2 ORDER BY Col2,SUM(Col3) DESC LABEL SUM(Col3)'总货运量'",
1)

如果需要统计特定主体（比如美国区Walmart）的总货运量，直接在上述QUERY的WHERE子句追加对应筛选条件即可，不会出现漏记。

方案2：辅助列预处理（适配万行以上大数据集） #

如果数据集行数过万，数组递归计算会存在明显延迟，可以用辅助列方案提升性能：

• 逐行用正则匹配整行内容是否包含目标供应商，关联国家字段标记该行货运量是否计入统计，比如针对美国Walmart的统计，辅助列公式写为=REGEXMATCH(TEXTJOIN("|",1,A3:C3),"\bWalmart\b")*(B3="USA")*C3，最后直接对辅助列求和即可
• 全量统计各国家Top4供应商时，可提前维护全量供应商名录做批量匹配，再通过数据透视表做分组排序，计算速度比纯数组公式高3倍以上。

注意：如果存在供应商名称互相包含的情况（比如"Walmart"和"Walmart Supply Chain"为两个独立主体），正则匹配时必须加词边界符\b，避免误匹配导致统计值偏高。

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