如何在Excel中按条件筛选/提取多版本application数据

原始数据 #

需求说明 #

筛选出同一application下存在不同version的所有行，预期结果如下：

方法1：辅助列+普通筛选 #

• 在数据右侧插入新列（如C列），表头设为「是否多版本」
• 在C2单元格输入公式：=SUMPRODUCT(($A$2:$A$8=A2)*($B$2:$B$8<>B2))>0，按回车后下拉填充至所有行
  • 公式逻辑：统计当前应用下，版本与当前行不同的记录数，结果大于0则说明该应用存在多版本
• 选中数据区域，点击「数据」选项卡的「筛选」，在C列筛选值为TRUE的行即可

方法2：高级筛选 #

• 创建条件区域：在空白区域输入两行，第一行写application，第二行输入公式：=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"<>"&B2)>0
• 选中原始数据区域，点击「数据」选项卡的「高级」，选择「将筛选结果复制到其他位置」
• 「条件区域」选择刚才创建的区域，「复制到」指定空白单元格，确定后即可得到目标结果

方法3：Power Query（适合大数据量） #

• 选中数据区域，点击「数据」选项卡的「从表格/区域」，导入Power Query编辑器
• 选中application列，点击「转换」选项卡的「分组依据」：
  • 分组依据列：application，新列名设为「版本数量」，操作选「计数（不同）」，列选version
• 点击「合并查询」，选择原始表和分组后的表，匹配列选application，合并类型选「左外部」
• 展开合并后的列，筛选「版本数量」大于1的行，删除「版本数量」列后，点击「关闭并上载」完成操作

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