Функция ФИЛЬТР в Microsoft Excel — одна из наиболее важных функций, которые необходимо освоить. Без него вам будет сложно найти нужные данные. Вот ускоренный курс использования ФИЛЬТРА в Excel.
Также полезно отметить, что данная функция не является единственным способом фильтрации данных в MS Excel. У вас есть такие инструменты, как «Автофильтр» и «Расширенный фильтр», позволяющие добиться того же, но с некоторыми важными оговорками, которые мы обсудим в этом руководстве.
Что такое функция ФИЛЬТР?
Функции или формулы Excel — это основа Excel, позволяющая вам выполнять такие действия, как нахождение среднего значения для большого набора данных или создание графика кривой Белла. Каждая функция имеет свой синтаксис, который обычно можно проверить, просто введя имя функции в Excel.
Функция Excel ФИЛЬТР, как следует из названия, используется для «фильтрации» значений указанного диапазона в соответствии с определенными условиями. И диапазон, и условия, которые будут использоваться, вводятся вместе с функцией, что делает ее чрезвычайно настраиваемой.
При правильных параметрах вы можете извлечь точную информацию, которая вам нужна, из электронной таблицы, не просматривая ее целиком, чтобы найти соответствующие записи вручную. А поскольку выходные данные содержатся в ячейке, вы можете объединить больше функций для выполнения вычислений или визуализации результатов в виде графика.
Почему функция ФИЛЬТР предпочтительнее Расширенный фильтр?
Большинство Excel для начинающих используют встроенные в Excel инструменты фильтрации данных, а не пытаются изучить синтаксис функции. Автоматический фильтр — самый простой, он позволяет исключать столбцы и устанавливать критерии фильтрации через интерфейс на основе меню. Кроме того, существует расширенный фильтр с возможностью применения нескольких критериев для реализации сложных схем фильтрации.
Тогда зачем вообще использовать функцию ФИЛЬТР?
Основное преимущество использования функций Excel перед выполнением любых операций вручную (с помощью другого инструмента Excel или даже любой другой программы) заключается в том, что функции являются динамическими. Автоматический фильтр или расширенный фильтр дают одноразовые результаты, которые не меняются при изменении исходных данных. С другой стороны, функция ФИЛЬТР соответствующим образом обновляет свои результаты при изменении данных..
Синтаксис функции FILTER
Синтаксис формулы ФИЛЬТР достаточно прост:
=FILTER(массив, включить, [if_empty])
Массив — это прямоугольное подмножество электронной таблицы, обозначаемое указанием диапазона между верхней левой ячейкой и нижней правой ячейкой. Например, A3:E10 — это массив, состоящий из столбцов от A до E и строк с 3 по 10.
Следующий параметр — это просто используемые критерии или, более технически, логический массив. Оно вводится в виде выражения, оценивающего значение диапазона ячеек (обычно столбца), которое возвращает ИСТИНА или ЛОЖЬ. Например, A3:A10="Pass" вернет TRUE, если значение ячейки соответствует заданной строке.
Наконец, вы можете ввести значение, которое будет возвращено функцией ФИЛЬТР, если ни одна строка не соответствует условиям. Это может быть простая строка типа «Записи не найдены».
Связано: Хотите узнать, насколько точно выборочные данные соответствуют более крупному набору данных? Ознакомьтесь с нашим руководством по расчет стандартной ошибки в Excel.
Использование функции ФИЛЬТР
Теперь, когда мы знаем синтаксис функции ФИЛЬТР, давайте посмотрим, как на самом деле использовать ФИЛЬТР в электронной таблице.
Образец данных, который мы используем для этой демонстрации, представляет собой массив от A2 до F11, в котором представлены оценки десяти учащихся по биологии вместе с нормальным распределением.
Давайте напишем функцию, которая будет фильтровать записи на основе их оценок на экзамене (перечисленных в столбце D) и возвращать только те, которые набрали меньше 30. Синтаксис должен быть таким:
=FILTER(A2:F11,D2:D11<30, «Совпадения не найдены»)
Поскольку отфильтрованные результаты являются подмножеством массива, используйте функцию в ячейке, оставив после нее достаточно места. Сделаем это под исходной таблицей:
И мы получаем ожидаемые результаты. Все записи с оценкой ниже 30 выбираются и отображаются в одном формате таблицы.
Вы также не ограничены одним условием. Используйте оператор AND (*), чтобы объединить несколько выражений в один параметр, создавая более сложный фильтр.
Давайте создадим функцию, которая возвращает записи, лежащие между 30 и 70 баллами. Вот синтаксис и результаты:.
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70), «Совпадения не найдены»)
Для неисключающих критериев вы также можете использовать оператор ИЛИ (+). Это соответствует фильтру, даже если только одно из включенных условий имеет значение TRUE.
В следующей формуле мы используем ее для поиска выбросов путем фильтрации результатов менее 15 или более 70.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70)», «Записи не найдены»)
Наконец, вместо использования одного значения или строки для возврата, когда функция ФИЛЬТР ничего не находит, вы можете указать значения для каждого столбца, чтобы гарантировать, что выходные данные всегда будут иметь согласованный формат.
Давайте попробуем условие, которое, как мы знаем, является ложным, чтобы увидеть, как оно выглядит по умолчанию:
=FILTER(A2:F11,D2:D11>90, «Совпадения не найдены»)
Как видите, результат содержит только одну строку, не соответствующую ожидаемому формату. Обычно это не проблема, если только вы не хотите передать результаты (или некоторые их значения) в другую формулу.
Итак, давайте попробуем указать значения по умолчанию в том же формате, что и элемент массива. Мы можем сделать это, указав значения, разделенные запятыми, заключенные в фигурные скобки. Вот так:
=FILTER(A2:F11,D2:D11>90,{"Нет записи", "Нет записи", "Нет записи", 0})
Это дает нам более приемлемые результаты, соответствующие остальному формату электронной таблицы.
Стоит ли использовать функцию ФИЛЬТР?
Даже если вы используете MS Excel только для ведения записей и не собираетесь выполнять какие-либо сложные вычисления, функция ФИЛЬТР — одна из немногих, на которую вам все равно стоит обратить внимание.
Потому что, как только ваша книга достигнет определенного размера, поиск данных вручную может быть затруднительным. И хотя инструменты «Автофильтр» и «Расширенный фильтр» удобны, использование функции на самом деле более удобно в долгосрочной перспективе, поскольку результаты обновляются сами по себе и могут быть объединены с другими функциями.
.