Как отфильтровать данные по нескольким условиям в Google Таблицах?
В повседневной работе часто приходится иметь дело с большими объёмами данных и необходимостью быстро находить информацию, соответствующую определённым критериям. Например, может понадобиться просмотреть записи о продажах конкретных товаров, отфильтровать строки по заданным условиям или проанализировать данные, удовлетворяющие сразу нескольким требованиям. В Microsoft Excel для таких задач многократной фильтрации обычно используется функция Расширенный фильтр. Однако в Google Таблицах прямого аналога «Расширенного фильтра» нет, поэтому такая фильтрация на первый взгляд кажется сложной.
В этой статье представлены несколько практических методов многокритериальной фильтрации в Google Таблицах, основанных исключительно на встроенных формулах. Освоив их, вы сможете эффективно анализировать и визуализировать данные в соответствии со своими задачами — даже без использования расширенных инструментов. Кроме того, вы найдёте рекомендации по устранению типичных проблем с формулами и альтернативные подходы для достижения аналогичных результатов при выполнении сложной фильтрации.
Фильтрация данных по нескольким условиям в одном столбце с помощью формулы
Фильтрация данных по нескольким условиям в нескольких столбцах с помощью формулы
Альтернативные решения:функция ФИЛЬТР с логикой ИЛИ/И,Представления фильтра в таблицах Google Sheets
Фильтрация данных по нескольким условиям в одном столбце с помощью формулы
Допустим, нужно извлечь все строки из набора данных, где значение в столбце A — «AAA-1» или «EEE-5». Такая задача возникает постоянно: например, при отслеживании конкретных артикулов товаров или подготовке целевых отчётов в Google Таблицах.
Чтобы выполнить эту задачу, используйте функцию ФИЛЬТР, объединив несколько условий — это эквивалентно логическому оператору ИЛИ. Выполните следующие действия:
Введите следующую формулу в пустую ячейку, где должны отображаться результаты фильтрации:
=FILTER(A1:D17, (A1:A17="AAA-1") + (A1:A17="EEE-5"))После ввода формулы просто нажмите клавишу Enter. Таблицы Google Sheets немедленно покажут все строки, в которых товар в столбце A равен либо «AAA-1», либо «EEE-5», как показано на рисунке ниже:
Примечания и советы:
1. В приведённой выше формуле:
- A1:D17 — это весь диапазон данных, который нужно отфильтровать.
- A1:A17 — укажите столбец, к которому применяются условия.
- «AAA-1» и «EEE-5» — это условия фильтрации; замените их на нужные значения в соответствии с вашим набором данных.
2. Чтобы добавить дополнительные условия фильтрации, расширьте формулу, добавив дополнительное условие с помощью знака +, чтобы реализовать логику ИЛИ. Например:
=FILTER(Range, (Condition1) + (Condition2) + (Condition3) + ...)Каждое добавленное условие заключается в круглые скобки и отделяется знаком «плюс». Благодаря такой конструкции Google Таблицы могут фильтровать данные и отображать строки, соответствующие хотя бы одному из указанных условий в целевом столбце.
3. Убедитесь, что диапазоны в вашем фильтре ячеек с формулами совпадают по размеру. Если диапазон условий короче диапазона данных, некоторые подходящие результаты могут быть пропущены.
4. Функция ФИЛЬТР является динамической: как только обновятся исходные данные, результаты фильтрации автоматически обновятся.
Если появляется ошибка #Н/Д, это обычно означает, что ни одна строка не соответствует заданным условиям.
Фильтрация данных по нескольким условиям в нескольких столбцах с помощью формулы
Во многих случаях требуется более точная настройка — например, отображение всех строк, где товар равен «AAA-1»и количество заказа меньше 50. Комбинирование критериев из нескольких столбцов обеспечивает более точную фильтрацию, которая часто необходима в бизнес-сценариях, таких как проверка запасов или составление выборочных отчётов.
Для этого используйте функцию ФИЛЬТР и разделяйте каждое условие запятой — это эквивалентно логическому оператору И. Введите эту формулу в пустую ячейку, где должны отображаться результаты:
=FILTER(A1:D17, A1:A17="AAA-1", B1:B17<,50)После нажатия клавиши Enter таблицы Google Sheets сразу отобразят все строки, удовлетворяющие обоим условиям. Результат показан на рисунке ниже:
Примечания и советы:
1. В формуле:
- A1:D17: диапазон данных, который необходимо отфильтровать.
- A1:A17="AAA-1": первое условие, применяемое к столбцу с товарами.
- B1:B17<50: Второе условие применяется к другому столбцу (например, к количеству заказа).
Вы можете настроить условия и диапазоны под свою таблицу — просто убедитесь, что все диапазоны содержат одинаковое количество строк.
2. Чтобы отфильтровать данные по более чем двум условиям (в разных столбцах), добавьте дополнительные условия в формулу ФИЛЬТР, разделяя их запятыми. Например:
=FILTER(Range, Condition1, Condition2, Condition3, ...)Каждое дополнительное условие уточняет фильтр: строка попадает в результат только если выполняются все условия (логическая связка И).
3. Внимательно проверяйте операторы сравнения для числовых и дат-критериев ()=, >, < и т.д.), а также кавычки для текста (например, «ИмяТовара»), чтобы избежать ошибок в формулах.
4. Формула ФИЛЬТР в таблицах Google Sheets автоматически пересчитывается при изменении Ваших исходных данных, обеспечивая актуальность результатов без необходимости ручного обновления.
Если формула возвращает ошибку, убедитесь, что все диапазоны имеют одинаковую длину и что в ссылочных ячейках отсутствуют недопустимые данные (например, текст в числовом столбце при выполнении сравнения).
Альтернативные решения для фильтрации данных по нескольким условиям в таблицах Google Sheets
Помимо основных подходов с использованием функции ФИЛЬТР, существуют и другие методы и усовершенствования, которые могут оказаться полезными в более сложных сценариях:
Функция фильтрации с пользовательской логикой ИЛИ/И с использованием МАССИВФОРМУЛАи ПОИСКПОЗ
Когда необходимо проверить наличие нескольких текстовых значений (например, нескольких ключевых слов), функция ПОИСКПОЗпозволяет задать шаблон:
1. В пустой ячейке введите следующую формулу, чтобы извлечь строки, где столбец A содержит «AAA-1», «EEE-5» или «CCC-2»:
=FILTER(A1:D17, ARRAYFORMULA(REGEXMATCH(A1:A17, "AAA-1|EEE-5|CCC-2")))Эта формула использует функцию ПОИСКПОЗ для поиска любого из указанных значений в столбце A. При необходимости скорректируйте шаблон, разделяя альтернативные значения с помощью символа | (вертикальная черта).
2. После ввода формулы нажмите клавишу Enter, чтобы отобразить все строки, содержащие хотя бы один из указанных элементов. Этот метод особенно эффективен для гибкой текстовой фильтрации, список значений которой со временем может расширяться.
Использование представлений фильтра для интерактивной многокритериальной фильтрации
Для тех, кто предпочитает удобный способ без использования формул, таблицы Google Sheets предлагают функцию Представления фильтра и стандартный Фильтр, которые позволяют создавать временные или сохранённые фильтры, не изменяя исходные данные. Это особенно полезно, когда нужно делиться отфильтрованными результатами, не скрывая и не удаляя данные для других пользователей.
Совет:
- Выделите свой диапазон данных (включая заголовки), затем нажмите кнопку Фильтр на панели инструментов или перейдите в меню Данные > Создать фильтр.
- Нажмите значок фильтра в заголовке столбца, чтобы выбрать конкретные значения или задать критерии для чисел/текста.
При работе с любым из этих методов фильтрации в таблицах Google Sheets:
- Обязательно тщательно выравнивайте диапазоны в формулах, чтобы избежать ошибок.
- Обновляйте условия и ссылки при изменении структуры ваших данных.
- Если результаты фильтрации отличаются от ожидаемых, внимательно проверьте каждое условие и оператор, а также убедитесь, что в ваших данных отсутствуют скрытые символы (например, лишние пробелы).
Используя эти методы, Вы сможете решить практически любую задачу фильтрации в таблицах Google Sheets эффективно. Если возникают постоянные проблемы или требуется выполнять особенно сложные и повторяющиеся задачи фильтрации, рассмотрите возможность создания пользовательских функций с помощью Apps Script или изучите доступные надстройки для таблиц Google Sheets.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек