Excel提取数据列非空单元格生成列表问题求助

问题场景 #

我正在为Google Ads构建大型关键词数据库，通过Excel替换关键词已节省大量时间。现有数据集包含5-30组需重建的关键词，当前结构在组间产生大量空白单元格，希望生成忽略空白的紧凑列表以便复制至其他表格，但尝试多个公式后，要么仅显示首个数据，要么仍包含空白且缺失部分条目。尝试的公式如下：

• =IFERROR(INDEX($I$2:$I$20,SMALL(IF(TRIM($I$2:$I$20)<>"",ROW($I$2:$I$20)-ROW($I$2)+1),ROWS($I$2:$I$20))),"")
• =IFERROR(INDEX($I$2:$I$20,SMALL(IF($I$2:$I$20)<>"",ROW($I$2:$I$20)-1),ROW(A1))),"")
• =IFERROR(INDEX($I$2:$I$20,SMALL(IF($I$2:$I$20)<>"",ROW($I$2:$I$20)-ROW($I$2)+1,""),ROW(A2)-ROW($A$2)+1)),"")
• 另一个公式：=IFERROR(INDEX($I$2:$I$20,SMALL(INDEX(($I$2:$I$20)<>"")*ROW($I$2:$I$20),0),COUNTBLANK($I$2:$I$20)+ROW(I2))),"")
  该公式虽显示更多结果，但仍含空白且缺失部分数据。

解决方案 #

1. 支持动态数组的Excel版本（Office 365/2021及以后） #

直接在目标单元格输入以下公式，输入后会自动溢出所有非空白内容，无需手动下拉：

=FILTER($I$2:$I$20,TRIM($I$2:$I$20)<>"")

• 说明：TRIM用于排除仅含空格的单元格，避免这类空白被误判为有效内容；若确认数据里没有仅含空格的单元格，可简化为=FILTER($I$2:$I$20,$I$2:$I$20<>"")。

2. 旧版Excel（无动态数组功能） #

使用数组公式，输入后按Ctrl+Shift+Enter组合键确认（不是单独按回车），然后下拉公式直到出现空白为止：

=IFERROR(INDEX($I$2:$I$20,SMALL(IF(TRIM($I$2:$I$20)<>"",ROW($I$2:$I$20)-ROW($I$2)+1),ROW(A1))),"")

• 修正点：修复了原公式的括号语法错误，同时用ROW(A1)作为SMALL的第二个参数，实现逐个提取第1、2、3...个非空白条目。

原公式问题分析 #

• 第一个公式用ROWS($I$2:$I$20)作为SMALL的参数，会直接提取区域内最后一个非空白条目，无法逐个生成列表；
• 第二、三个公式存在括号匹配错误（如IF($I$2:$I$20)<>的括号位置错误），导致逻辑判断失效；
• 最后一个公式的COUNTBLANK($I$2:$I$20)+ROW(I2)参数逻辑错误，无法准确定位非空白条目的位置，导致内容缺失或残留空白。

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