谷歌表格重复值提取公式转Microsoft Excel适配方案求助

原谷歌表格使用以下公式提取重复值并按出现次数降序排列，导出为Excel后因QUERY函数和数组兼容性问题失效：

=INDEX( QUERY( QUERY( {'DATA'!A2:A}, "SELECT Col1, COUNT(Col1) GROUP BY Col1 ORDER BY Count(Col1) DESC"),  "WHERE Col2 > 1",0) ,,1)

针对Excel提供两种解决方案：

一、动态数组公式（适用于Excel 365/2021及以后版本） #

基础版（提取所有重复值的唯一列表） #

=UNIQUE(FILTER(DATA!A2:A,COUNTIF(DATA!A2:A,DATA!A2:A)>1))

• COUNTIF(DATA!A2:A,DATA!A2:A)：统计A列每个值的出现次数
• FILTER：筛选出出现次数大于1的所有值
• UNIQUE：去除重复项，仅保留每个重复值一次

进阶版（按出现次数降序排列） #

如果需要和原公式一样按出现次数从多到少排序，使用：

=SORTBY(UNIQUE(FILTER(DATA!A2:A,COUNTIF(DATA!A2:A,DATA!A2:A)>1)),COUNTIF(DATA!A2:A,UNIQUE(FILTER(DATA!A2:A,COUNTIF(DATA!A2:A,DATA!A2:A)>1))),-1)

• SORTBY：将提取的唯一重复值按对应出现次数（第二个参数）降序（-1）排列

二、旧版Excel（无动态数组功能）解决方案 #

方法1：高级筛选 #

1. 选中DATA工作表的A列数据（包含表头）
2. 点击「数据」选项卡 → 「高级」筛选
3. 选择「将筛选结果复制到其他位置」
4. 设置参数：
   • 列表区域：DATA!$A$1:$A$X（X替换为A列最后一行行号）
   • 条件区域：在空白单元格输入公式=COUNTIF(DATA!$A$2:$A$X,A2)>1，并选中该单元格作为条件区域
   • 复制到：选择目标工作表的空白单元格
5. 勾选「选择不重复的记录」，点击确定即可

方法2：数组公式（需按Ctrl+Shift+Enter触发） #

在目标工作表的第一个空白单元格输入以下公式，然后下拉填充直到出现#N/A，最后删除错误行：

=INDEX(DATA!$A$2:$A$X,MATCH(0,COUNTIF($B$1:B1,DATA!$A$2:$A$X)+(COUNTIF(DATA!$A$2:$A$X,DATA!$A$2:$A$X)<=1),0))

• 替换公式中的X为DATA表A列最后一行的行号
• $B$1:B1为目标单元格的上方区域，需根据实际位置调整

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