Google Sheets对比表单历次提交差异 修复重复项计算错误问题

原有公式问题说明 #

你之前使用的差异计算公式核心缺陷是调用UNIQUE做了集合级去重，仅判断值是否存在，完全不统计同一个值的出现次数：

=TEXTJOIN(",",true,unique(ArrayFormula(trim(split(textjoin(", ", true,B2:C2),","))),true,true))

当出现「同一个值删除部分实例、保留部分实例」的场景（比如原有2个lo lo，本次提交只保留1个），公式会直接把该值的所有实例判定为重复值全部移除，完全不符合按实例统计增减的需求。

正确实现逻辑 #

统计增减不能靠值去重判断，必须按值的出现次数做差：

• 先按提交人维度筛选数据，按提交时间排序，给每条提交记录匹配同提交人的上一条历史提交内容
• 分别拆分当前提交、上一次提交的逗号分隔值，逐个统计每个值的出现频次
• 新增项 = 当前记录比上一次记录多出的对应数量的值实例
• 删除项 = 上一次记录比当前记录少掉的对应数量的值实例

可直接套用的公式 #

假设原始表结构如下：

• A列：提交时间
• B列：提交人
• F列：逗号分隔格式的提交内容
• 有效数据从第2行开始

1. 匹配同提交人上一条提交内容
   在H2单元格输入以下公式下拉填充，自动获取当前行提交人上一次提交的F列内容：

=XLOOKUP(1,(B$1:B1=B2)*(A$1:A1<A2),F$1:F1,"",0,-1)

2. 统计新增内容
   在I2（新增项列）输入以下公式下拉填充，自动计算本次提交新增的内容，支持重复值按实例统计：

=LET(
  curr,TRIM(SPLIT(F2,",")),
  prev,TRIM(SPLIT(H2,",")),
  add_res,REDUCE(curr,UNIQUE(prev),LAMBDA(a,v,
    IF(COUNTIF(a,v)>COUNTIF(prev,v),
       {FILTER(a,a<>v),SEQUENCE(COUNTIF(a,v)-COUNTIF(prev,v),1,v)},
       FILTER(a,a<>v)
    )
  )),
  TEXTJOIN(", ",TRUE,add_res)
)

3. 统计删除内容
   在J2（删除项列）输入以下公式下拉填充，自动计算本次提交删除的内容：

=LET(
  curr,TRIM(SPLIT(F2,",")),
  prev,TRIM(SPLIT(H2,",")),
  del_res,REDUCE(prev,UNIQUE(curr),LAMBDA(a,v,
    IF(COUNTIF(a,v)>COUNTIF(curr,v),
       {FILTER(a,a<>v),SEQUENCE(COUNTIF(a,v)-COUNTIF(curr,v),1,v)},
       FILTER(a,a<>v)
    )
  )),
  TEXTJOIN(", ",TRUE,del_res)
)

公式效果说明：以上逻辑完全基于值的出现次数做差，不会误删保留的重复值实例。比如上一次提交包含2个lo lo，本次提交仅保留1个lo lo，删除项只会出现1次lo lo，不会把留存的1个也判定为删除；如果本次新增2个lo lo，新增项会正确展示2次lo lo，不会被去重为1个。

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