Excel/GoogleSheets 公式列出另一列中没有对应值的所有行

Question

我希望有一种简单的方法来执行以下操作。我有 2 列（A、B），我想要第三列（C），它包含 A 列中所有在 B 列中有空单元格的值。我不希望 C 列每行之间有空格，并且当我从 A 列和 B 列中添加或删除值时它应该自动更新。

例如：

A          B             C
red        x             blue
blue                     yellow
pink       x             white
yellow
green      x
black      x
white
gray       x

这是能轻易实现的事吗？

Answer 1

此函数至少应该适用于 Excel 365。它依赖于函数FILTER，该函数是一个溢出到相邻单元格的动态数组函数。该FILTER函数相对较新。

在单元格 C2 中输入以下公式：

=UNIQUE(FILTER(A2:A11,B2:B11=""))

结果如下。

A	乙	C
红色的	X	蓝色的
蓝色的		黄色的
粉色的	X	白色的
黄色的
绿色的	X
黑色的	X
白色的
灰色的	X
蓝色的
黄色的

为了处理表的动态修改，您可以将 A:B 格式化为表并使用结构化引用在 C2 的公式中。在下面的示例中，我使用默认的“Table1”作为表名。请注意，C 列不是表的一部分；如果是，那么公式将对 C 的每一行重复，并且会出现#SPILL!错误。

=UNIQUE(FILTER(Table1[A],Table1[B]=""))

如果您需要在其他地方引用 C2 公式的结果，可以使用=C2#。当然，您也可以创建一个命名范围对于 C2 并改用该命名范围。即命名 C2FilteredData并通过 在其他位置引用它=FilteredData#。