Excel多列提取唯一值时如何过滤空行（可变数据长度）

核心问题分析 #

直接使用=UNIQUE(Sheet1!M:N;FALSE;FALSE)会包含整列空单元格，空行被识别为唯一项，日期格式下显示为1900/01/01；固定行号范围又无法适配数据长度变化的场景。

推荐解法（Excel 365/2021 动态数组版本） #

方法1：FILTER + UNIQUE 组合（简洁高效）

先用FILTER筛选出M、N列均不为空的行，再通过UNIQUE提取唯一组合，自动适配数据长度变化：

=UNIQUE(FILTER(Sheet1!M:N; Sheet1!M:M<>"" * Sheet1!N:N<>""); FALSE; FALSE)

• 逻辑说明：Sheet1!M:M<>"" * Sheet1!N:N<>""表示同时满足M列非空、N列非空的行；FILTER输出符合条件的行后，UNIQUE提取其中的唯一值组合。
• 注意：若你的Excel是英文版本，将公式中的分号;替换为逗号,。

方法2：动态范围定义数据源

通过定位M、N列最后一个非空行的行号，动态限定数据源范围，避免包含空行：

=UNIQUE(Sheet1!M1:INDEX(Sheet1!N:N; MAX(AGGREGATE(14;6;ROW(Sheet1!M:M)/(Sheet1!M:M<>"");1); AGGREGATE(14;6;ROW(Sheet1!N:N)/(Sheet1!N:N<>"");1))); FALSE; FALSE)

• 逻辑说明：AGGREGATE(14;6;ROW(Sheet1!M:M)/(Sheet1!M:M<>"");1)用于获取M列最后一个非空行的行号，同理获取N列的最后非空行号，取两者最大值作为范围终点，确保覆盖所有有数据的行。

旧版本Excel（无动态数组）替代方案 #

若使用无动态数组支持的Excel版本，可按以下步骤操作：

1. 定义动态名称：
   • 打开「公式」选项卡 → 「定义名称」，创建名称DynamicMN，引用位置输入：

     =OFFSET(Sheet1!$M$1;0;0;MAX(COUNTA(Sheet1!$M:$M);COUNTA(Sheet1!$N:$N));2)
     

2. 使用数组公式提取唯一值（按Ctrl+Shift+Enter确认）：

   =INDEX(DynamicMN; MATCH(0; COUNTIF($A$1:A1; DynamicMN); 0); COLUMN(A1))
   

   横向、纵向拖动公式填充，直到出现错误值为止。

内容的提问来源于stack exchange，提问作者Németh Péter