Фильтры в MS Excel

Анатолий Опарин / февраль, 2019

Умелое использование возможностей простого и расширенного фильтра существенно поможет вам быстро и точно анализировать данные больших таблиц.

ПРОСТОЙ ФИЛЬТР

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

1. Выделите столбец путем клика на букву, которая является названием столбца.
2. На панели меню «Данные» нажмите кнопку «Фильтр».
3. В заголовке столбца появится кнопка [▼] для раскрытия окна фильтра.
4. Нажмите на эту кнопку.
5. Снимите галочку с верхнего пункта «Выделить все».
6. Поставьте галочку на значение, по которому необходим фильтр.
7. Нажмите кнопку [OK].

Таким образом можно фильтровать таблицу по нескольким столбцам, если на панели меню «Данные» нажимать кнопку «Фильтр» после выделения нескольких столбцов.

Второй и более быстрый способ отфильтровать таблицу по значению ячейки:

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

При этом заголовки столбцов получат кнопки [▼] для фильтрации по значениям ячеек.

Чтобы снять фильтр с таблицы можно совершить одно из следующих действий:

• На панели меню «Данные» нажать кнопку «Фильтр».
• По кнопке фильтра в заголовке столбца открыть окно фильтра и отметить галочкой пункт «Выделить все».

Ограничения простого фильтра:

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

Этих ограничений нет при использовании расширенного фильтра.

РАСШИРЕННЫЙ ФИЛЬТР

Расширенный фильтр – это кнопка «Дополнительно» рядом с кнопкой «Фильтр» на панели меню «Данные». Но перед тем, как её нажать, надо подготовиться.

Допустим, у нас есть таблица со столбцами «Код», «Товар» и «Категория».

Задание 1. Отобрать записи, в которых присутствуют «яблоки» И «фрукты» И код > 1000

1. Создать в стороне от основной таблицы область фильтров (Диапазон условий).
2. Ячейки с заголовками области фильтров должны в точности повторять ячейки с заголовками основной таблицы;
3. Ниже заголовка в текстовых полях написать желаемые значения;
4. В числовом поле применить формулу, т.е., чтобы код был больше 1000, в поле формулы надо написать =">1000", чтобы ячейка приняла значение >1000;
5. Выделить мышкой диапазон ячеек от A1 до C15;
6. Нажать кнопку «Дополнительно» рядом с кнопкой «Фильтр»;
7. В открывшемся окне в поле «Исходный диапазон» должны уже находиться координаты выделенного диапазона: $A$1:$C$15;
8. Переместиться в поле «Диапазон условий» и мышкой выделить диапазон области фильтров (вместе со строкой заголовков), после чего это поле будет заполнено: $E$1:$G$2;
9. Нажать кнопку [OK].

После этого в таблице останутся только строки, которые удовлетворяют всем трем фильтрам. Строки, не удовлетворяющие фильтру, будут свернуты, но не удалены:

Чтобы отменить фильтр надо откатить историю операций (горячие клавиши Ctrl+Z). Или развернуть скрытые ячейки. Это не всегда удобно, а точнее всегда неудобно, поэтому в Excel предусмотрен вариант, когда результаты фильтрации переносятся в новое место на листе.

Чтобы скопировать результаты фильтрации в другое место надо:

1. В окне настройки расширенного фильтра переключиться на «скопировать результат в другое место».
2. Переместить фокус в поле «Поместить результат в диапазон».
3. Мышкой выделить диапазон ячеек или просто кликнуть на верхнюю левую ячейку желаемого места.
4. Поле «Поместить результат в диапазон» заполнится координатами ячеек (ячейки).
5. Нажать кнопку [OK].

В результате основная таблица останется нетронутой, а в назначенном диапазоне появится таблица согласно примененным фильтрам:

Задание 2. Отобрать записи, в которых присутствуют «фрукты» с кодом > 1000 ИЛИ «яблоки»

По сравнению с решением Задания 1 всего два изменения:

• Расширить «Диапазон условий» до $E$1:$G$3;
• Переместить слово «яблоки» на другую строку.

Результат будет другой:

P.S. Видео-инструкция по работе с расширенным фильтром: https://www.youtube.com/watch?time_continue=170&v=5KFglKRLB1o

(При перепечатке ссылка на оригинал статьи обязательна)