Формула FILTER с использованием нескольких критериев только если не пусто

Содержание

Вопрос или проблема
Ответ или решение
Задача
Решение
Корректный подход
Объяснение формулы
Примеры использования
Заключение

Вопрос или проблема

У меня есть набор данных в таблице Excel с названием “data”.

На другом листе у меня есть две строки с заголовками “data”. Что-то вроде этого:

      A    B
1   id: 
2 name:

Идея в том, что в ячейках B1 и B2 пользователь вводит критерии поиска, а ниже с помощью формулы FILTER Excel отображает результаты.

Я использую что-то вроде этого для одного критерия:

=FILTER(data, (data[id]=B1), “Нет записей”)

Для нескольких критериев я использую это:

=FILTER(data, (IF(ISBLANK(B1), "", (data[id]=B1)))*(IF(ISBLANK(B2), "", (data[name]=B2))), "Нет записей")

Обе формулы работают, однако если я использую формулу для нескольких критериев, и ячейки B1 или B2 пустые, формула FILTER выдает #VALUE!, вместо того чтобы показать только информацию, которая соответствует B1 или B2.

В основном, идея заключается в использовании формулы FILTER и отображении данных в соответствии с тем, какой столбец (id или name) пользователь выбирает для поиска, или обоими.

Когда обе ячейки B1 и B2 не пустые и совпадают, формула нескольких условий, которую вы используете, отобразит соответствующие данные.
Но учтите, что условия B1 и B2 берутся из разных серий. Я предлагаю использовать две формулы FILTER для достижения этой цели. В ячейках F1 и F2:

=IF(ISBLANK(E1),"",FILTER(data,data[id]=E1,"Нет записей"))
=IF(ISBLANK(E2),"",FILTER(data,data[name]=E2,"Нет записей"))

Ваша трудность здесь заключается в том, что вы строите два массива значений TRUE/FALSE, а затем умножаете эти два массива друг на друга. Идея заключается в том, что если И ID, и Имя совпадают, соответствующие места в массивах будут 1, когда их перемножить, что даст 1 в выводе, и, следовательно, получить соответствующую строку или строки в таблице данных.

Это не работает, когда в B1 или B2 пусто, потому что когда одно из них пустое, его часть формулы не производит массив значений TRUE/FALSE. Вместо этого она производит одно “постоянное” значение на выходе, “”, или значение пустой ячейки.

Поэтому умножение не работает, когда одно из них пустое.

Вы можете решить эту проблему, заменив каждую из двух “” в вашей формуле на FALSE:

=FILTER(Data, (IF(ISBLANK(B1), FALSE, (Data[ID]=B1)))*(IF(ISBLANK(B2), FALSE, (Data[Name]=B2))), "Нет записей")

Таким образом, будет создан фактический массив, все значения которого FALSE, и умножение может происходить так, как вы планируете.

Счастливых дней!

Ответ или решение

Вопрос по формуле FILTER в Excel с использованием нескольких критериев и обработкой пустых значений достаточно распространён и важен для пользователей, работающих с большими наборами данных. В данной статье мы подробно рассмотрим, как правильно использовать формулу FILTER, чтобы она эффективно обрабатывала ситуации, когда некоторые из критериев являются пустыми.

Задача

У вас есть таблица данных, названная "data", и вы хотите фильтровать строки в зависимости от значений в ячейках B1 и B2, которые представляют собой условия фильтрации по колонкам "id" и "name". При этом, если одно из условий не задано (пустое значение), необходимо, чтобы формула корректно обрабатывала такие случаи и не выдавала ошибку #VALUE!.

Решение

Формула, которая у вас уже есть, выглядит следующим образом:

=FILTER(data, (IF(ISBLANK(B1), "", (data[id]=B1)))*(IF(ISBLANK(B2), "", (data[name]=B2))), "No records found")

Как было замечено, использование пустой строки "" в функции IF приводит к тому, что в случае пустоты одной из ячеек результат становится некорректным, и в результате вы получаете ошибку.

Корректный подход

Чтобы исключить получение ошибки #VALUE!, вам необходимо изменить логику формирования условий фильтрации. Вместо возвращения пустой строки при отсутствии условий, вы должны возвращать логическое значение FALSE. Таким образом, при отсутствии условия фильтрации, функция FILTER будет обрабатывать входные данные корректно:

=FILTER(data, (IF(ISBLANK(B1), FALSE, (data[id]=B1)))*(IF(ISBLANK(B2), FALSE, (data[name]=B2))), "No records found")

Объяснение формулы

Проверка на пустоту: Каждое условие фильтрации проверяет, является ли соответствующая ячейка пустой. Если она пустая, возвращается FALSE, что позволяет избежать проблем с массивами.
Логические вычисления: Логическое умножение (оператор *) объединяет массивы TRUE и FALSE, формируя итоговое условие, при котором только строки, удовлетворяющие немодулируемым критериям, отбираются.
Обработка результата: Если ни одна из записей с условиями не найдена, возвращается уведомление "No records found".

Примеры использования

Если вы вводите только id в B1, формула отфильтрует все записи с указанным id, игнорируя name.
Аналогично, если вы вводите только name в B2, вы получите все строки с заданным name.
Если оба поля заполнены, отобразятся записи, удовлетворяющие обоим условиям.

Заключение

Использование формулы FILTER с обработкой условий на пустоту ячеек — это мощный инструмент для работы с большими наборами данных в Excel. Правильная логика фильтрации позволяет избежать ошибок и получить точные результаты. Следуя указанным рекомендациям, вы сможете создать эффективные и удобные решения для анализа данных, что значительно улучшит вашу продуктивность в работе с Excel.