Блог

Я подозреваю, что об этом спрашивали ранее, но я не могу его найти.

FILTER() часто возвращает 0 для пустых строк, даже если указана возвращаемая строка.

Используя filter() , я часто получаю возвращаемое значение 0 для пустых ячеек. Предположим, что эти 6 строк данных в столбце A:

 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.

Если лучшего решения нет, есть ли объяснение?

Вы можете добавить amp;»» перед вашим аргументом массива.

Например,

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

Это будет хорошо работать с текстовыми значениями, но преобразует ваши числовые данные в текст, и в этом случае вы можете преобразовать их обратно в числовые, используя функцию VALUE .

Предположим, что если A10:A15 были числовыми данными, вы можете использовать:

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

Я выяснил, что кажется другим общим решением: замените пустые жала в первом аргументе на одинарные кавычки, так что в общем:

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

или в случае этого вопроса:

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

Я просто использую то, как Excel обрабатывает одинарные кавычки, я пока не нашел никаких недостатков, хотя я не проверял это тщательно.

Как объяснено здесь, третий аргумент (If_empty ) не предназначен для случаев, когда есть пробел.

FILTER(array, include, [if_empty])

Где:
Массив (обязательно) — диапазон или массив значений, которые вы хотите отфильтровать.
Включить (обязательно) — критерии, предоставленные в виде логического массива (значения TRUE и FALSE). Его высота (когда данные находятся в столбцах) или ширина (когда данные находятся в строках) должны быть равны высоте аргумента массива.
If_empty (необязательно) — значение, возвращаемое, когда ни одна запись не соответствует критериям.

Ваша функция вернет строку, указанную в If_empty , если все значения в Array были «xyz». Т.Е. Критерии в Include не будут оцениваться как TRUE для любого непустого значения в диапазоне Array .

Кроме того, кажется, что ваша последняя формула больше не возвращает нули:

Другое, менее элегантное решение — заменить пустые ячейки в исходном массиве символом пробела » «.

Я нашел эту тему, когда искал быстрый ответ при переформатировании данных. Таким образом, добавление пробела было быстрее, чем в других решениях. Мне действительно нравится это ЗНАЧЕНИЕ(FILTER( amp;»», ответьте также!