Нет функции фильтра в Excel
Обновлено: 05.07.2024
Фильтр работает неправильно или не так, как вам хотелось бы?
Вот несколько причин, по которым ваш фильтр Excel может не работать.
Excel ожидает, что вы подготовили свои данные в соответствии с некоторыми основными стандартами макета, прежде чем использовать фильтр. Сделайте это правильно, и вы сведете к минимуму проблемы с фильтрацией.
Проверьте, соблюдаете ли вы эти правила фильтрации.
1. Убедитесь, что вы выбрали все данные
Если в ваших данных есть пустые строки и/или столбцы или если вы хотите отфильтровать только определенный диапазон, выберите область, которую вы хотите отфильтровать, прежде чем включать фильтр.
Если область не выбрана, Excel задает область фильтрации. Это может привести к тому, что Excel выберет только первую пустую строку или столбец, исключая дополнительные данные после этих точек.
Намного лучше выбрать вручную, чтобы убедиться, что включены все данные.
Дополнительное примечание: если вы хотите удалить пустые строки из области фильтра, просто включите фильтр, щелкните стрелку раскрывающегося списка в любом столбце, чтобы отобразить список фильтров. Снимите флажок «Выбрать все», а затем прокрутите список фильтров вправо до конца. Выберите «Пробелы» и нажмите «ОК».
Теперь будут отображаться только пустые строки.
Вы можете легко идентифицировать строки, так как номер строки теперь будет окрашен в синий цвет.
Чтобы удалить пустые строки, просто выберите их, а затем щелкните правой кнопкой мыши над одним из синих чисел в строке. Выберите Удалить, чтобы удалить строки.
Отключите фильтрацию, и вы увидите, что строки удалены.
2. Проверьте заголовки столбцов
Убедитесь, что ваши данные содержат только одну строку заголовков столбцов.
Если вам нужно несколько строк для заголовка, просто введите первую строку в ячейку, а затем нажмите Alt + Enter, чтобы ввести новую строку внутри ячейки.
Форматирование ячейки с помощью Wrap Text также работает.
3. Проверить объединенные ячейки
Еще одна причина, по которой ваш фильтр Excel может не работать, может быть связана с объединением ячеек.
Разъедините все объединенные ячейки или сделайте так, чтобы каждая строка и столбец имели свое собственное содержимое.
Если заголовки ваших столбцов объединены, при фильтрации вы не сможете выбрать элементы из одного из объединенных столбцов.
Если у вас есть объединенные строки, фильтрация не будет охватывать все объединенные строки.
4. Проверить на ошибки
Убедитесь, что ваши данные не содержат ошибок. Например, если вы пытались отфильтровать значения «10 лучших», «Выше среднего» или «Ниже среднего» в своем списке (используйте для этого числовые фильтры), ошибка может помешать Excel применить фильтр.
Чтобы удалить ошибки, сначала используйте фильтр, чтобы найти их. Они всегда перечислены внизу списка, поэтому прокрутите вправо до конца.
Если вы видите один (или несколько), сначала снимите флажок «Выбрать все» в верхней части списка, а затем прокрутите его до конца.
Выберите ошибку и нажмите OK. Найдя ошибку, исправьте ее или удалите, а затем очистите фильтр. Не продолжайте и используйте Top 10, Выше или Ниже среднего, и вы вернетесь в бизнес!
5. Проверить наличие скрытых строк
Скрытые строки даже не отображаются как параметр фильтра в списке фильтров.
Чтобы отобразить строки, сначала выберите область, содержащую скрытые строки. Это может означать, что вам нужно выбрать строку выше и ниже скрытых данных.
Затем либо щелкните правой кнопкой мыши область заголовка строки (над номерами строк) и выберите "Показать", либо на вкладке "Главная" выберите "Формат", "Скрыть и показать", "Показать строки".
Дополнительные проверки и информация.
Проверить наличие других фильтров
Убедитесь, что фильтр не остался в другом столбце.
Лучший способ очистить все фильтры – нажать кнопку "Очистить" на ленте (справа от кнопки "Фильтр").
При этом фильтр остается включенным, но удаляются все настройки фильтра, что позволяет начать заново с полным набором данных.
Кнопка "Фильтр" неактивна
Если кнопка "Фильтр" неактивна, убедитесь, что рабочие листы не сгруппированы.
Вы можете определить, есть ли они, просто взглянув на строку заголовка, где имя файла отображается в верхней части экрана.
Если вы видите "Имя вашего файла" — "Группа", у вас есть сгруппированные рабочие листы.
Просто перейдите к одному из своих рабочих листов, щелкните правой кнопкой мыши вкладку листа и выберите "Разгруппировать листы". Кнопка «Фильтр» теперь будет доступна.
Фильтр "Равно" не работает
Если вы используете числовой фильтр или фильтр даты, фильтр равенства, а Excel не возвращает правильные данные, проверьте, совпадают ли форматы ваших данных.
Например, если у вас есть 2 ячейки, в каждую из которых введено значение 1000, и одна ячейка отформатирована в формате валюты, а другая — в числовом формате, при использовании числового фильтра, параметра «Равно» Excel найдет совпадения только там, где вы вводите формат числа.
Просто введите 1000, чтобы найти совпадение для ячейки, отформатированной только в числовом формате. Если ввести $1000,00, ячейка будет отформатирована как валюта.
То же самое относится и к датам. 16-янв-19 не будет соответствовать 16/01/2019. Поэтому убедитесь, что весь столбец данных отформатирован в одном формате, чтобы избежать этой проблемы.
Мой файл очень медленно обновляется
Если вы обнаружите, что ваш файл начинает отвечать очень медленно, т. е. "вихревое колесо" отображается более чем на несколько секунд каждый раз, когда вы обновляете файл, убедитесь, что перед этим вы выбрали только область таблицы. вы применили фильтр, а не целые строки, целые столбцы или весь лист.
Кроме того, если вы применили фильтр к нескольким таблицам в файле, вы можете удалить фильтр из всех таблиц, с которыми вы не работаете. Это также может помочь устранить «вихревые» моменты.
Надеюсь, один из этих советов снова поможет вам вернуться на правильный путь. Существует так много причин, по которым ваш фильтр Excel может не работать. Однако именно с ними я чаще всего сталкиваюсь.
Если вы хотите узнать больше о том, как использовать фильтр в Excel, ознакомьтесь с нашими сообщениями о фильтрации данных в Excel и суммировании только видимых строк при фильтрации данных в Excel.
Был ли этот блог полезен? Дайте нам знать в комментариях ниже.
Если вам понравился этот пост, ознакомьтесь с похожими постами ниже.
Фильтрация — обычное повседневное действие для большинства пользователей Excel. Независимо от того, используете ли вы автофильтр или таблицу, это удобный способ быстро просмотреть подмножество данных. Пока функция ФИЛЬТР не появилась в Excel, не было простого способа добиться этого с помощью формул. Когда Microsoft объявила об изменениях в механизме вычислений Excel, они также представили множество новых функций. Одной из таких новых функций является ФИЛЬТР, который возвращает все ячейки диапазона, соответствующие определенным критериям.
На момент написания функция ФИЛЬТР доступна только тем, у кого есть подписка на Microsoft 365. Он не будет доступен в Excel 2019 и более ранних версиях.
Скачать файл примера
Я рекомендую вам загрузить файл примера для этого поста. Затем вы сможете работать с примерами и увидеть решение в действии, а файл будет полезен для дальнейшего использования.
Скачать файл: 0035 Функция ФИЛЬТР в Excel.zip
Посмотреть видео:
Аргументы функции ФИЛЬТР
Прежде чем мы рассмотрим аргументы, необходимые для функции ФИЛЬТР, давайте рассмотрим базовый пример, чтобы понять, что она делает.
Здесь функция ФИЛЬТР возвращает все значения в ячейках B3–B10, где количество символов превышает 15. Не тот сценарий, который понадобится многим из нас, но он демонстрирует мощь новой функции ФИЛЬТР.
- массив: диапазон ячеек или массив значений для фильтрации.
- include: массив результатов TRUE/FALSE, где значения TRUE будут сохранены в фильтре.
- [if_empty]: отображаемое значение, если строки не возвращаются.
Примеры использования функции ФИЛЬТР
В следующих примерах показано, как использовать функцию ФИЛЬТР.
Пример 1. ФИЛЬТР возвращает массив строк и столбцов
В этом примере ячейка F3 содержит одну формулу, но эта формула возвращает массив значений в соседних строках и столбцах.
Формула в ячейке F3:
Эта единственная формула возвращает 2 строки и 3 столбца данных, где значения в C3–C10 превышают 100.
Формула в ячейке F3:
К счастью, Microsoft предоставила нам аргумент if_empty, который отображает сообщение, если нет возвращенных строк.
Формула в ячейке F3:
Если бы мы хотели отобразить результат в каждом столбце, мы могли бы включить постоянный массив в аргумент if_empty. В следующем примере в столбцах «Сумма счета» и «Сроки оплаты» будет отображаться н/д.
Эта формула приведет к следующему:
Пример 3. ФИЛЬТР автоматически расширяется при связывании с таблицей
В этом примере показано, как функция ФИЛЬТР реагирует на связь с таблицей Excel.
Новые записи, добавленные в таблицу, которые соответствуют критериям, автоматически добавляются в диапазон разлива функции. Удивительные вещи!
Пример 4. Использование FILTER с несколькими критериями.
В примере 4 показано, как применить ФИЛЬТР с несколькими критериями.
Формула в ячейке F3:
Всем, кто использовал функцию СУММПРОИЗВ, этот метод применения нескольких условий будет знаком. Умножение создает логику И (т. е. все критерии должны быть ИСТИНА). В приведенном выше примере показано, где значение счета-фактуры больше 50, а срок оплаты больше 30.
Сложение создает логику ИЛИ (т. е. любое отдельное условие может быть ИСТИНА).
Формула в ячейке G3:
В приведенном выше примере показано, где значение счета-фактуры больше 50 или срок оплаты больше 30.
Пример 5. Использование FILTER для зависимых динамических раскрывающихся списков
Раскрывающиеся списки — это метод проверки данных. Зависимые раскрывающиеся списки — это расширенный метод, при котором списки меняются в зависимости от результата другой ячейки. Например, если в первом раскрывающемся списке отображаются названия стран, во втором раскрывающемся списке должны отображаться только города, существующие в этой стране. В настоящее время существуют очень утомительные методы для достижения этого эффекта, но новый ФИЛЬТР делает это очень простым.
Формула в ячейке H3:
Функция UNIQUE создает уникальный список для заполнения раскрывающегося списка в ячейке F4.
Формула в ячейке I3:
В зависимости от значения в ячейке F4 значения, возвращаемые функцией ФИЛЬТР, изменяются. Второй раскрывающийся список в ячейке F6 динамически меняется в зависимости от значения в ячейке F4.
Пример 6. Использование FILTER с другими функциями
В этом последнем примере ФИЛЬТР вложен в функцию СОРТИРОВКИ.
Формула в ячейке F3:
Во-первых, функция ФИЛЬТР возвращает ячейки, основанные на том, что срок оплаты меньше или равен 30. Затем функция СОРТИРОВКА расставляет клиентов в алфавитном порядке по возрастанию.
Хотите узнать больше?
-
– узнайте, как изменился механизм расчета Excel. – для вывода списка уникальных значений в диапазоне – для сортировки значений в диапазоне – для сортировки значений на основе порядка других значений – для возврата только тех значений, которые соответствуют определенным критериям – для возврата последовательности чисел – для возврата массива случайных чисел — научитесь использовать динамические массивы с диаграммами, сводными таблицами, изображениями и т. д. — изучите передовые методы управления динамическими массивами
Автоматизируйте Excel, чтобы сэкономить время и перестать выполнять работу, которую могла бы выполнять обученная обезьяна.
Не забывайте:
Если вы нашли этот пост полезным или у вас есть лучший подход, оставьте комментарий ниже.
Вам нужна помощь в адаптации этого к вашим потребностям?
Я предполагаю, что примеры в этом посте не совсем соответствуют вашей ситуации. Мы все используем Excel по-разному, поэтому невозможно написать пост, который удовлетворит потребности всех. Потратив время на изучение методов и принципов, изложенных в этом посте (и в других местах на этом сайте), вы сможете адаптировать его к своим потребностям.
- Читайте другие блоги или смотрите видео на YouTube по той же теме.Вы получите гораздо больше пользы, найдя собственные решения.
- Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
- Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
- Используйте Excel Rescue, моего партнера-консультанта. Они помогают решить небольшие проблемы с Excel.
Что дальше?
Пока не уходите, в Excel Off The Grid есть чему поучиться. Ознакомьтесь с последними сообщениями:
Функция ФИЛЬТР Excel фильтрует диапазон данных на основе предоставленных критериев и извлекает соответствующие записи.
- массив — диапазон или массив для фильтрации.
- include — логический массив, указанный в качестве критерия.
- if_empty – [необязательно] возвращаемое значение, если результаты не возвращаются.
Функция ФИЛЬТР "фильтрует" диапазон данных на основе предоставленных критериев. Результатом является массив совпадающих значений из исходного диапазона. Говоря простым языком, функция ФИЛЬТР будет извлекать совпадающие записи из набора данных, применяя один или несколько логических тестов. Логические тесты предоставляются в качестве аргумента include и могут включать в себя множество типов критериев формулы. Например, ФИЛЬТР может сопоставлять данные за определенный год или месяц, данные, содержащие определенный текст, или значения, превышающие определенный порог.
Функция FILTER принимает три аргумента: массив, include и if_empty. Массив – это диапазон или массив для фильтрации. Аргумент include должен состоять из одного или нескольких логических тестов. Эти тесты должны возвращать TRUE или FALSE в зависимости от оценки значений из массива. Последний аргумент, if_empty, — это результат, возвращаемый, когда FILTER не находит совпадающих значений. Обычно это сообщение типа «Нет записей», но могут быть возвращены и другие значения. Укажите пустую строку (""), чтобы ничего не отображалось.
Результаты FILTER являются динамическими. При изменении значений в исходных данных или изменении размера массива исходных данных результаты FILTER будут обновляться автоматически. Результаты FILTER будут "разлиты" на листе по нескольким ячейкам.
Базовый пример
Чтобы извлечь значения в A1:A5, превышающие 100:
Чтобы извлечь строки в A1:C5, где значение в A1:A5 больше 100:
Обратите внимание, что единственная разница в приведенных выше формулах заключается в том, что вторая формула предоставляет диапазон из нескольких столбцов для массива. Логическая проверка, используемая для аргумента include, такая же.
Фильтр для красной группы
В показанном выше примере формула в F5 выглядит следующим образом:
Поскольку значение в H2 "красное", функция ФИЛЬТР извлекает данные из массива, где столбец "Группа" содержит "красный". Все совпадающие записи возвращаются на лист, начиная с ячейки F5, где существует формула.
Значения также можно жестко закодировать. Приведенная ниже формула дает тот же результат, что и выше, с жестко заданным критерием «красный»:
Нет соответствующих данных
Часто is_empty настроен на предоставление пользователю текстового сообщения:
Чтобы ничего не отображалось, если соответствующие данные не найдены, укажите пустую строку ("") для if_empty:
Значения, содержащие текст
Чтобы извлечь данные на основе логической проверки значений, содержащих определенный текст, можно использовать следующую формулу:
В этой формуле функция ПОИСК используется для поиска "txt" в rng2, который обычно является столбцом в rng1. Функция ISNUMBER используется для преобразования результата SEARCH в TRUE или FALSE. Прочитайте полное объяснение здесь.
Фильтровать по дате
ФИЛЬТР можно использовать с датами, создавая логические тесты, подходящие для дат Excel. Например, чтобы извлечь записи из rng1, где дата в rng2 приходится на июль, вы можете использовать такую общую формулу:
Эта формула использует функцию МЕСЯЦ для сравнения дат месяца в rng2 с 7. Полное объяснение см. здесь.
Более одного критерия
Аргумент include можно расширить с помощью логической логики. Например, чтобы извлечь только данные, в которых группа отмечена как "красная" и оценка выше 80, можно использовать следующую формулу:
где цвет и оценка — это столбцы в диапазоне.
Сложные критерии
Чтобы отфильтровать и извлечь данные на основе нескольких сложных критериев, вы можете использовать функцию ФИЛЬТР с цепочкой выражений, использующих логическую логику. Например, приведенная ниже общая формула фильтрует на основе трех отдельных условий: учетная запись начинается с «x» И регион — «восток», а месяц — НЕ апрель.
Полное объяснение см. на этой странице. Построение критериев с помощью логических выражений — это элегантный и гибкий подход, который можно расширить для обработки многих сложных сценариев. Ниже приведены дополнительные примеры.
Функция ФИЛЬТР — это функция Excel, позволяющая извлекать или "фильтровать" набор данных на основе критериев, переданных через аргумент. Функция ФИЛЬТР появилась в Office 365 и не будет доступна в Office 2019 и более ранних версиях.
ФИЛЬТР — это встроенная функция рабочего листа, принадлежащая к новой категории функций динамических массивов Excel. Функция ФИЛЬТР возвращает массив значений, которые передаются на ваш рабочий лист, если функция не вложена для передачи вывода другой функции.
ФИЛЬТР — это динамическая функция. Это означает, что когда вы изменяете значения в исходных данных или изменяете размер массива исходных данных, функция ФИЛЬТР Excel автоматически обновляет возвращаемые значения.
Оглавление
Синтаксис
Синтаксис функции фильтра следующий:
Аргументы:
массив — это обязательный аргумент, в котором вы указываете диапазон или массив, который хотите отфильтровать.
include — это обязательный аргумент, когда вы предоставляете критерии фильтрации в виде логического массива.
if_empty — это необязательный аргумент, в котором вы указываете значение, которое должна вернуть функция, если ни одна из записей не соответствует предоставленным критериям.
Важные характеристики функции ФИЛЬТР
Примеры функции ФИЛЬТР
Давайте посмотрим на примеры функции ФИЛЬТР:
Пример 1. Обычная версия функции фильтра
Предположим, нам нужен список учащихся с оценкой "А" в наборе данных.
Для этого мы введем весь набор данных, т. е. A2:C16, в аргумент массива.
В аргументе include мы введем C2:C16="A", что создаст логический массив, который присваивает TRUE всем буквам "A".
Последний аргумент ( if_empty ) — это необязательная строка, в которой вы сообщаете формуле, что она должна вернуть, если не находит никаких значений. В нашем случае мы используем текстовую строку "Нет совпадений", но формула также может ничего не возвращать, передав пустую строку ("").
Поэтому окончательная формула будет такой:
Если вы предпочитаете, вы также можете указать ссылку на ячейку вместо добавления символа или текстовой строки в аргумент включения. Например, допустим, вы пишете "A" в ячейке G6, а затем вводите аргумент включения как C3:C17=G6.
Таким образом, формула будет –
Функция ФИЛЬТР также без проблем работает для горизонтально организованных наборов данных. Просто убедитесь, что ширина диапазонов, определенных в массиве и включенных аргументах, одинакова.
Кроме того, в случае отсутствия совпадающих записей формула возвращает строку, не совпадающую, как показано.
Поскольку ни один из учащихся в приведенном выше наборе данных не имеет оценки "D", результатом будет строка "Нет совпадений".
Пример 2. Функция FILTER используется в сочетании с функцией EXACT
Вложение функции EXACT в функцию FILTER позволяет получить точное совпадение с учетом регистра.
Функция EXACT играет здесь простую роль и работает как включаемый аргумент в функции FILTER. EXACT возвращает TRUE для точно совпадающей строки и FALSE в противном случае. Таким образом, функция EXACT вернет 15 логических значений (для каждой ячейки в диапазоне B2:B16), которые в нашем случае будут выглядеть примерно так:
На позиции 5 – единственное совпадение – "Bing", поэтому возвращается значение TRUE.
Этот массив передается функции FILTER в качестве аргумента включения.Затем FILTER использует массив, возвращенный функцией EXACT, чтобы вернуть только те строки, которые содержат строку «Bing» (т. е. строки, которым функция EXACT присвоила значение TRUE).
Примечание: обратите внимание, что мы также можем получить тот же результат, просто добавив условие (A2:A16)="Bing" в аргумент включения, но в этом случае поиск будет нечувствительным к регистру.
Пример 3. Фильтрация с использованием подстановочных знаков с помощью функции FILTER
А теперь давайте обсудим некоторые формулы для больших мальчиков. Функция FILTER сама по себе не поддерживает подстановочные знаки, но мы можем ввести логическую проверку в аргумент include, чтобы получить желаемый результат.
Мы вложим функции ISNUMBER и SEARCH следующим образом:
Давайте рассмотрим каждый компонент формулы и посмотрим, какую роль они играют. Сначала мы обработаем вывод функции ПОИСК. Функция поиска ищет в столбце A любую строку, содержащую букву «G». У нас есть 3 фамилии, которые содержат G — Бинг, Геллер и Грин. Помните, что функция поиска возвращает только позицию искомого символа.
= ФИЛЬТР (A2:A16, IНОМЕР (
Итак, в итоге у нас должно быть 3 имени: Бинг, Геллер и Грин.
Пример 4. Функция FILTER с несколькими критериями (оператор И/ИЛИ)
До сих пор мы не использовали столбец Оценки из нашего набора данных. Давайте исправим это, хорошо?
Итак, здесь мы пытаемся найти учащихся с оценкой "А" и баллом выше 100.
Сначала мы будем использовать критерии И со звездочкой (*). Для этого мы расширим аргумент include, используя логическую логику, например:
Использование '*' умножает логические значения, где True равно 1, а False равно 0. Таким образом, когда в формуле умножаются выражения в круглых скобках, возможны две возможности:
- Вы получаете FALSE (или 0): если выражения внутри обеих скобок возвращают FALSE (0*0) или одно из них возвращает FALSE (0*1).
- Вы получаете ИСТИНА (или 1): если выражения внутри обеих скобок возвращают ИСТИНА (1*1).
Поэтому, когда первая скобка находит A и возвращает ИСТИНА и вторая скобка находит, что оценка больше 100 и также возвращает ИСТИНА, мы получим наш возврат (или получим строку "Нет совпадений"). Использование подобных логических выражений является эффективным подходом к критериям фильтрации.
Похожим образом мы можем использовать критерий ИЛИ, используя плюс (+) между двумя круглыми скобками в аргументе включения, например:
Это даст нам всех учащихся, у которых либо оценка "А", либо баллы выше 100.
Подобно тому, что мы видели для критериев AND, мы можем получить два возможных результата при добавлении логических значений в аргументе include:
- Вы получаете FALSE (или 0): если выражения внутри обеих скобок возвращают FALSE (0+0).
- Вы получаете ИСТИНА (или 1): если выражение внутри одной из скобок возвращает ИСТИНА (0+1) или обе скобки возвращают ИСТИНА (1+1).
Подождите, если обе скобки вернут 1, в сумме получится 2, верно?
К счастью, это не имеет значения. Excel будет рассматривать все, что равно 0, как ЛОЖЬ, а все, что не равно 0, как ИСТИНА.
Вот как вы можете включать критерии И или ИЛИ в свои формулы.
Пример 5. Фильтрация дубликатов с помощью функций FILTER и COUNTIFS
Если у вас большой набор данных, вполне вероятно, что вам придется иметь дело с дубликатами. Когда точка данных имеет более одного экземпляра, мы можем отфильтровать повторяющиеся экземпляры с помощью вложенной формулы, например так:
По сути, мы используем функцию COUNTIFS для подсчета экземпляров точек данных и извлечения тех точек данных, которые встречаются более одного раза. Вкладывая функцию СЧЁТЕСЛИМН в аргумент включения, вы указываете формуле ФИЛЬТР включать только те результаты, которые встречаются более одного раза (>1).
Вы можете добавить или удалить количество столбцов, переданных функции СЧЁТЕСЛИМН для фильтрации повторяющихся данных в соответствии с вашими требованиями.
Функция ФИЛЬТР перенесет результат с повторяющимися экземплярами в диапазон ячеек, как показано на рисунке.
Пример 6. Фильтрация пустых записей с помощью функции FILTER
Мы еще раз вернемся к тому, что узнали в предыдущем примере, где мы обсуждали использование критериев И с помощью '*'. Мы будем использовать оператор <> (т. е. не равно) внутри круглых скобок, чтобы проверить, есть ли в ячейке пустая строка (""), например:
Здесь мы проверяем точки данных, содержащиеся в столбцах A, B и C, и извлекаем только те точки данных, которые имеют значения во всех трех столбцах. Для этого мы указываем Excel проверять наличие пустых ячеек, определяя столбец и проверяя наличие пустых ячеек, добавляя оператор «<>» (не равно) и пустую строку («»). Чтобы добавить критерий И, мы будем использовать '*' между круглыми скобками.
Если ячейка в любом из этих столбцов пуста, возвращаемые данные полностью исключат эту конкретную строку.
Пример 7. Функция FILTER используется в сочетании с функциями SUM, MIN, MAX и AVERAGE
Фильтр - это очень удобно. Помимо выполнения целого списка задач, описанных выше, он также способен обобщать данные, возвращаемые после фильтрации.
Для этого мы будем использовать семейство функций агрегирования, таких как SUM, MIN, MAX и AVERAGE.
На самом деле это довольно просто. Все, что нам нужно сделать, это передать результат функции FILTER любой из упомянутых функций агрегации, например так:
= SUM ( FILTER (B2:B16,C2:C16 = " A ",0)) //Сумма баллов класса A
= MIN ( FILTER (B2:B16,C2:C16 = " A " ,0)) //Минимум баллов для класса A
= MAX ( FILTER (B2:B16,C2:C16 = " A ",0)) // Максимум баллов для класса A
= AVERAGE ( FILTER (B2:B16,C2:C16 = " A ",0)) //Среднее значение оценок A
Здесь функция ФИЛЬТР будет включать все строки, содержащие букву "А", и возвращать 0 для строк, которые не совпадают. Затем он выберет значения, соответствующие "A" из столбца B. Эти значения будут переданы функциям агрегирования, что даст нам окончательный результат.
Пример 8. Функция FILTER для возврата несмежных столбцов
Допустим, нам нужны только столбцы Имя и Оценка учащихся с оценкой "А". Как мы должны объяснить функции ФИЛЬТР, что нам нужны только эти два столбца, если они незаразны?
Хотя для этого нет отдельной формулы, мы можем добиться этого, внеся некоторую логику в функцию ФИЛЬТР. Мы вложим одну формулу FILTER в другую формулу FILTER и используем логические значения в аргументе include внешней формулы FILTER, чтобы исключить столбец Names, например:
Сформулируем эту формулу словами.
Вложенная (внутренняя) функция FILTER – это простая формула, с которой мы уже знакомы.
Итак, давайте перейдем к внешней функции FILTER.
Вложенная функция FILTER работает как аргумент массива внешней функции FILTER, информируя внешнюю функцию FILTER о том, откуда должны поступать возвращаемые значения. Аргумент include — это место, где происходит волшебство. Мы используем «1, 0, 1» (в качестве альтернативы мы могли бы использовать TRUE, FALSE, TRUE) и сообщаем внешней функции FILTER, что мы хотим включить только первый и третий столбцы.
Надеюсь, я разгадал все тайны, связанные с новой функцией ФИЛЬТР, а также рассказал кое-что еще. Включите эти формулы в свои рабочие листы, и вы быстро станете ниндзя функции ФИЛЬТР. Скоро увидимся с другой интригующей функцией Excel, а пока… sayonara.
Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!
Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.
Читайте также: