如何在Google Sheets中按指定规则处理表格数据生成目标表？

原始数据 #

处理规则 #

1. 按NUMBER列分组
2. 每组内选取WORKING DAY数值更大的记录对应的COMPANY、ID、NAME
3. 对每组的WORKING HOUR求和
4. 对每组的WORKING PRICE求和
5. 对每组的WORKING DAY求和
6. 选取每组AUDIT PRICE中的最大值
7. BONUS规则：若分组内存在0值则填0，否则对所有值求和

目标结果 #

公式实现 #

假设原始数据位于A2:I5区域（表头在A1:I1），在空白单元格（如K2）输入以下数组公式即可自动生成结果：

=LET(
  data, A2:I5,
  unique_nums, UNIQUE(INDEX(data,,3)),
  BYROW(unique_nums, LAMBDA(current_num,
    LET(
      group_data, FILTER(data, INDEX(data,,3)=current_num),
      max_day_pos, XMATCH(MAX(INDEX(group_data,,7)), INDEX(group_data,,7)),
      company, INDEX(group_data, max_day_pos, 1),
      id, INDEX(group_data, max_day_pos, 2),
      name, INDEX(group_data, max_day_pos, 4),
      total_hour, SUM(VALUE(SUBSTITUTE(INDEX(group_data,,5), ",", "."))),
      total_price, SUM(INDEX(group_data,,6)),
      total_day, SUM(INDEX(group_data,,7)),
      max_audit, MAX(INDEX(group_data,,8)),
      bonus, IF(COUNTIF(INDEX(group_data,,9), 0) > 0, 0, SUM(INDEX(group_data,,9))),
      HSTACK(company, id, current_num, name, total_hour, total_price, total_day, max_audit, bonus)
    )
  ))
)

公式说明 #

• LET：定义变量简化公式结构，避免重复引用
• unique_nums：提取NUMBER列的唯一值作为分组依据
• BYROW：遍历每个唯一NUMBER，对对应分组执行计算：
  • group_data：筛选当前NUMBER对应的所有行
  • max_day_pos：定位分组内WORKING DAY最大值所在的行
  • 提取对应行的COMPANY、ID、NAME
  • total_hour：将WORKING HOUR的逗号分隔文本转为数值后求和
  • 对WORKING PRICE、WORKING DAY直接求和
  • max_audit：取分组内AUDIT PRICE的最大值
  • bonus：判断分组内是否有0值，有则返回0，否则求和
  • HSTACK：将所有计算结果横向拼接成一行

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