Excel для экономиста: как пользоваться расширенным фильтром?

Функция «Фильтр»

24 сентября 2018 года была представлена новая функция, которая также умеет фильтровать данные. Появилась она совместно с инновационным обновлением, которое добавило динамические массивы в Excel. Эта функция, аналогично шести остальным, доступна тем пользователям, которые пользуются последней версией Microsoft Office 365.

Фактически она призвана полностью заменить функционал расширенного фильтра в Excel, который требовал неких танцев с бубном, чтобы его можно было более-менее удобно использовать.

Синтаксис функции следующий.

=ФИЛЬТР(массив; включить; )

Разберем основные аргументы:

1. Массив. Это диапазон (как представленный в виде ссылки, так и именованный), который будет использоваться для фильтрации данных.
2. Включить. Это массив переменных, имеющих такую же ширину и высоту, как и массив. Простыми словами, это условия, при которых должны отображаться конкретные значения.
3. Если_пусто. Это дополнительный аргумент, который задает значение, которое возвращается при обнаружении, что все значения включенного массива пустые.

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

1. Она задействует динамические массивы. Учитывая это, результат всегда будет касаться и смежных ячеек.
2. Если необходимо задать фильтру несколько условий, необходимо использовать знак умножения во втором аргументе, а сами условия взять в скобки.
3. Для использования оператора ИЛИ нужно использовать знак суммирования вместо знака умножения, а сами критерии также взять в скобки.
4. Если окажется, что в ячейке, в которую будет возвращаться значение, уже есть какие-то значения, то появится ошибка #ПЕРЕНОС! Чтобы избавиться от нее, нужно очистить данные, содержащиеся в соответствующей ячейке.

Таким образом, тот сложный функционал расширенного фильтра можно реализовать с помощью одной простой функции Excel. Главное – немного потренироваться и иметь последнюю версию Microsoft Office.

Работа с мастером «Расширенного фильтра»

Самое сложное позади — Вы научились формировать критерии отбора. Остался один шаг. Выделяем диапазон с исходными данными (или любую его ячейку — Excel сам определит и выделит нужный массив) и находим на ленте команду «Дополнительно» во вкладке «Данные» в группе «Сортировка и Фильтр». Перед Вами появится окно «Расширенный фильтр»

В нем нужно:

1) задать исходный диапазон (он будет выбран автоматически при запуске фильтра),

2) определить диапазон условия (ваша мини-табличка с критериями, автоматически Excel может определить диапазон неточно, лучше каждый раз переназначать его заново), на этом шаге помним, что выделяем только заголовки и строки с критерием, пустые строки включать в диапазон нельзя,

3) выбрать способ обработки (фильтровать данные в вашей исходной таблице или скопировать отфильтрованные строки в другое место),

4) указать, стоит ли показать все записи, или вывести только уникальные.

После настройки нажимаем «ОК» и получаем желаемый результат.

Чтобы очистить фильтр, переместите курсор в шапку таблицы с отфильтрованными данными и воспользуйтесь командой «Очистить» на ленте, или примените Автофильтр (Ctrl+Shift+L).

Как поставить

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

Для начала работы необходимо выбрать одну, любую, ячейку внутри таблицы, открыть вкладку «Данные».

Затем нажать кнопку «Фильтр».

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

В таблице

Пошаговая инструкция: как поставить фильтр в эксель-таблице.

1. Нажать на значок в заголовке столбца.

Перед пользователем появится выпадающее окошко с перечислением всех значений в этом столбце.

2. Убрать лишние галочки, стоящие у параметров, которые пользователя не интересуют. Галочки останутся только у тех параметров, по которым необходимо провести поиск. После чего нажать «Ок».

3. Просмотреть результат — останутся только строки, соответствующие заданному параметру.

В диапазоне

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

Например, в рассматриваемом файле столбцы В и С имеют числовой отсев.

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

Например, в случае с числовым поиском этот порядок действий выглядит так:

1. Выбираем вид отсева.

2. Выбираем необходимый способ формирования диапазона, например, «больше» означает, что в результате появятся все значения, превышающие цифру, которую пользователь введет.

3. Ввести цифру, которая и станет границей для отсева, — выведены будут все значения больше нее.

4. Нажать «Ок» и оценить результат. В выбранном столбце останутся только значения, превышающие установленную границу.

Задача 7 (Условия отбора содержат формулы и обычные критерии)

Рассмотрим теперь другую таблицу из файла примера на листе Задача 7
.

В столбце Товар
приведено название товара, а в столбце Тип товара
— его тип.

Задача состоит в том, чтобы для заданного типа товара вывести товары, у которых цена ниже средней. То есть у нас 3 критерия: первый критерий задает Товар, 2-й — его Тип, а 3-й критерий (в виде формулы) задает цену ниже средней.

Критерии разместим в строках 6 и 7. Введем нужные Товар и Тип товара. Для заданного Тип товара вычислим среднее и выведем ее для наглядности в отдельную ячейку F7. В принципе, формулу можно ввести прямо в формулу-критерий в ячейку С7.

Будут выведены 2 товара из 4-х (заданного типа товара).

Функция «Фильтр»

24 сентября 2020 года была представлена новая функция, которая также умеет фильтровать данные. Появилась она совместно с инновационным обновлением, которое добавило динамические массивы в Excel. Эта функция, аналогично шести остальным, доступна тем пользователям, которые пользуются последней версией Microsoft Office 365.

Фактически она призвана полностью заменить функционал расширенного фильтра в Excel, который требовал неких танцев с бубном, чтобы его можно было более-менее удобно использовать.

Синтаксис функции следующий.

=ФИЛЬТР(массив; включить; )

Разберем основные аргументы:

1. Массив. Это диапазон (как представленный в виде ссылки, так и именованный), который будет использоваться для фильтрации данных.
2. Включить. Это массив переменных, имеющих такую же ширину и высоту, как и массив. Простыми словами, это условия, при которых должны отображаться конкретные значения.
3. Если_пусто. Это дополнительный аргумент, который задает значение, которое возвращается при обнаружении, что все значения включенного массива пустые.

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

1. Она задействует динамические массивы. Учитывая это, результат всегда будет касаться и смежных ячеек.
2. Если необходимо задать фильтру несколько условий, необходимо использовать знак умножения во втором аргументе, а сами условия взять в скобки.
3. Для использования оператора ИЛИ нужно использовать знак суммирования вместо знака умножения, а сами критерии также взять в скобки.
4. Если окажется, что в ячейке, в которую будет возвращаться значение, уже есть какие-то значения, то появится ошибка #ПЕРЕНОС! Чтобы избавиться от нее, нужно очистить данные, содержащиеся в соответствующей ячейке.

Таким образом, тот сложный функционал расширенного фильтра можно реализовать с помощью одной простой функции Excel. Главное – немного потренироваться и иметь последнюю версию Microsoft Office.

Устанавливаем фильтр в Excel

Фильтры в Excel работают по тому же принципу. Пользователь выбирает параметры данных, которые ему нужно отобразить, — и Excel убирает из таблицы всё лишнее.

Разберёмся, как это сделать.

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

Таблица, в которой хранятся данные о продажах автосалонаСкриншот: Excel / Skillbox Media

Допустим, нужно показать продажи только одного менеджера — Соколова П. Воспользуемся фильтрацией.

Шаг 1. Выделяем ячейку внутри таблицы — не обязательно ячейку столбца «Менеджер», любую.

Чтобы настроить фильтрацию, выбираем любую ячейку таблицыСкриншот: Excel / Skillbox Media

Шаг 2. На вкладке «Главная» нажимаем кнопку «Сортировка и фильтр».

Нажимаем кнопку «Сортировка и фильтр»Скриншот: Excel / Skillbox Media

Шаг 3. В появившемся меню выбираем пункт «Фильтр».

Жмём сюда, чтобы применить фильтрацию ко всем столбцам таблицыСкриншот: Excel / Skillbox Media

То же самое можно сделать через кнопку «Фильтр» на вкладке «Данные».

Второй способ установить фильтрациюСкриншот: Excel / Skillbox Media

Шаг 4. В каждой ячейке шапки таблицы появились кнопки со стрелками — нажимаем на кнопку столбца, который нужно отфильтровать. В нашем случае это столбец «Менеджер».

Нажимаем кнопку столбца, который нужно отфильтровать, — появляется меню фильтраСкриншот: Excel / Skillbox Media

Шаг 5. В появившемся меню флажком выбираем данные, которые нужно оставить в таблице, — в нашем случае данные менеджера Соколова П., — и нажимаем кнопку «Применить фильтр».

Выбираем менеджера, данные о продажах которого нужно показать в таблицеСкриншот: Excel / Skillbox Media

Готово — таблица показывает данные о продажах только одного менеджера. На кнопке со стрелкой появился дополнительный значок. Он означает, что в этом столбце настроена фильтрация.

Как сделать (наложить) фильтр на таблицу Excel

Это очень просто! Допустим, у нас есть таблица с заголовками. Поскольку фильтр на таблицу Эксель накладывается на определённую часть таблицы, то эту самую часть нужно сначала выделить. Сам Excel ни за что не догадается, что вы хотите, поэтому выделяем в строке с заголовками таблицы нужные ячейки, как показано на рисунке ниже.

После того как выделили нужные столбцы, нужно собственно установить фильтр. Самый простой способ — это зайти на вкладке ленты «Главное» / «Фильтр и сортировка» и нажать фильтр. Привожу скриншот из Office 2010, в прочих версиях Excel фильтр на таблицу накладывается аналогично.

После нажатия этой кнопки в выделенные ячейки добавится фильтр.

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

Показанный пример включения фильтра у таблицы является самым простым. Давайте посмотрим как он работает.