Google Sheets 按出现次数统计A列未在B列出现值的方案咨询

核心逻辑 #

你需要的不是简单的存在性匹配，而是对每个取值分别统计A、B两列的出现次数，将两者的差值作为该取值在Pending列的重复展示次数，差值为0时不展示。

可用实现方案 #

方案1：单公式自动溢出（无需辅助列，推荐） #

直接在Pending列第一个单元格（例如C2）输入以下公式，即可自动生成所有符合要求的结果，数据更新时会自动刷新：

=LET(
  // 提取A列所有非空的不重复值
  unique_vals, UNIQUE(FILTER(A2:A, A2:A<>"")),
  // 统计每个值在A列的总出现次数
  count_a, BYROW(unique_vals, LAMBDA(val, COUNTIF(A2:A, val))),
  // 统计每个值在B列的总出现次数
  count_b, BYROW(unique_vals, LAMBDA(val, COUNTIF(B2:B, val))),
  // 计算每个值的剩余待展示次数，小于0时按0处理
  pending_cnt, MAP(count_a, count_b, LAMBDA(a, b, MAX(a - b, 0))),
  // 按剩余次数重复对应值，平铺为单列结果
  FLATTEN(MAP(unique_vals, pending_cnt, LAMBDA(val, cnt, IF(cnt > 0, MAKEARRAY(cnt, 1, LAMBDA(_r, _c, val)), NA())))
)

方案2：辅助列分步实现（易排查问题） #

如果对复杂公式不熟悉，可以拆分步骤用多列辅助实现：

• 第一步：在D列提取A列不重复值，D2输入=UNIQUE(FILTER(A2:A, A2:A<>""))
• 第二步：E列统计对应值在A列的出现次数，E2输入=COUNTIF(A:A, D2)，下拉填充或用BYROW自动溢出
• 第三步：F列统计对应值在B列的出现次数，F2输入=COUNTIF(B:B, D2)，下拉填充或用BYROW自动溢出
• 第四步：G列计算剩余展示次数，G2输入=MAX(E2-F2, 0)，下拉填充
• 第五步：在Pending列C2输入以下公式生成最终结果：

  =FLATTEN(MAP(D2:D, G2:G, LAMBDA(val, cnt, IF(cnt>0, MAKEARRAY(cnt,1,LAMBDA(_,__,val)),NA())))
  

效果验证 #

以你举的例子验证：HQR123在A列出现4次、B列出现1次时，公式计算剩余次数为3，会生成3条HQR123记录；B列新增1条HQR123后，剩余次数自动更新为2，Pending列同步调整为2条记录，完全符合需求。

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