如何用Excel将数据集按非空/非0值生成个性化个人表格？

嘿，我完全懂你要做的事儿——给John和Mike各自生成专属的水果列表，只保留他们名下非零、非空白的项对吧？你之前试INDEX+MATCH没搞定很正常，这个需求用动态数组函数或者条件筛选类的组合公式会更顺手，我给你分两种场景说清楚：

如果你用的是支持动态数组的新版本Excel，直接用FILTER函数就能完美解决，不需要下拉填充，公式会自动溢出结果。

比如要生成John的列表（对应B列数据），在任意空白单元格输入：

=FILTER(A2:A4, (B2:B4<>0)*(B2:B4<>""), "无符合条件的数据")

• 解释下公式逻辑：
  • A2:A4是我们要提取的水果名称区域（跳过第一行的标题）
  • (B2:B4<>0)*(B2:B4<>"")是筛选条件：同时排除0值和空白单元格（用乘号表示“且”的逻辑）
  • 最后一个参数是没有符合条件数据时显示的提示，你可以改成自己需要的内容

输入完按回车，公式会自动生成垂直的列表：Oranges、Pear，正好是John的非空非0项。

同理，生成Mike的列表（对应C列），只需要把公式里的B换成C就行：

=FILTER(A2:A4, (C2:C4<>0)*(C2:C4<>""), "无符合条件的数据")

结果就是Apple、Oranges、Pear，完全符合你的要求。

如果你的Excel版本不支持动态数组（比如2019及更早），可以用两种方式实现：

方式A：生成逗号分隔的文本列表 #

在空白单元格输入以下数组公式，输入完按Ctrl+Shift+Enter（不是单纯回车）：

=TEXTJOIN(", ", TRUE, IF((B2:B4<>0)*(B2:B4<>""), A2:A4, ""))

这个公式会把John的符合条件的水果用逗号加空格连接起来，得到Oranges, Pear。同样，把B换成C就能得到Mike的列表。

方式B：生成垂直的单列列表 #

如果想要和动态数组一样的垂直列表效果，在第一个单元格（比如E2）输入：

=IFERROR(INDEX(A:A, SMALL(IF((B$2:B$4<>0)*(B$2:B$4<>""), ROW(B$2:B$4), ""), ROW(A1))), "")

输入完按Ctrl+Shift+Enter，然后下拉填充这个公式，直到出现空值。公式会依次提取符合条件的水果名称，超出范围后显示空白。

• 记得把公式里的区域A2:A4、B2:B4换成你实际的数据范围，如果数据行数更多，就调整成对应的行号
• 如果你的空白单元格是公式返回的空字符串（比如=""），公式里的B2:B4<>""会自动排除这类情况；如果是真正的空白单元格，B2:B4<>0也能把它排除（因为Excel会把空白视为0）

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