Excel FILTER()对空单元格返回0

vngu2lb8 于 2023-03-09 发布在其他

关注(0)|答案(4)|浏览(2063)

我怀疑以前有人问过这个问题，但我找不到。
FILTER()通常会为空行返回0，即使指定了返回字符串。
使用filter()时，空单元格的返回值通常为0。假设A列中有6行数据：

abc
xyz
abc

xyz
abc

如果我使用

FILTER(A10:A15, A10:A15 <> "xyz", "")

我（有时）会得到以下反馈：

abc
abc
  0
abc

这似乎有些不可预测。让0出现在我不希望出现的位置是一个问题，需要额外的逻辑或筛选。这是已知问题吗？除了显式筛选出空白单元格之外，还有其他方法可以解决此问题吗？
这看起来可以摆脱0返回空单元格的情况：

FILTER(A10:A15, (A10:A15 <> "xyz") * (A10:A15 <> ""), "")

这是返回的：

abc
abc
abc

我可以接受这个解决方案，但它应该是不必要的。我还结束了解释为什么我过滤空细胞给人们一遍又一遍。
顺便说一句，过滤掉0不起作用。FILTER()在阅读单元格时似乎将其视为空字符串，但在输出结果时却没有。

FILTER(A10:A15, (A10:A15 <> "xyz") * (A10:A15 <> 0), "")

这将返回带有0的原始结果。
如果没有更好的解决办法，有没有解释？

4条答案

您可以在数组参数前添加&"”。
例如，

FILTER(A10:A15 &"", (A10:A15 <> "xyz") * (A10:A15 <> ""), "")

这对于文本值很有效，但它会将数值数据转换为文本，在这种情况下，您可以使用VALUE函数将其转换回数值。
假设A10：A15是数值数据，您可以用途：

VALUE(FILTER(A10:A15 &"", (A10:A15 > 1000) * (A10:A15 <> ""), ""))

如here所述，第三个参数（If_empty）不适用于存在空白的情况。
FILTER(array, include, [if_empty])
其中：

数组（必需）-要过滤的值的范围或数组。
Include（必需）-以布尔数组形式提供的条件（TRUE和FALSE值）。其高度（当数据位于列中时）或宽度（当数据位于行中时）必须等于数组参数的高度或宽度。
If_empty（可选）-当没有条目符合条件时返回的值。

如果Array中的所有值均为“xyz”，则函数将返回If_empty中指定的字符串。即，对于Array范围内的任何非空值，Include中的条件不会计算为TRUE。
此外，上一个公式似乎不再返回零：

我想出了一个似乎不同的通用解决方案：将第一个参数中的空字符串替换为单引号，一般来说：

=FILTER(SUBSTITUTE($X:$Z,"","'"),*whatever*,*whatever*)

或者在这个问题中：

=FILTER(SUBSTITUTE($A10:A15,"","'"),A10:A15 <> "xyz", "")

我只是简单地利用Excel处理单引号的方式，我还没有发现任何缺点，尽管我没有彻底测试它。

另一个不太优雅的解决方案是用空格字符““替换源数组中的空单元格。
我在重新格式化数据时寻找快速答案时发现了这个帖子。所以添加一个空格比其他解决方案更快。我真的很喜欢这个VALUE（FILTER（&""，答案也一样！