Автофильтр в EXCEL

Рассмотрим стандартный фильтр ( Данные/ Сортировка и фильтр/ Фильтр ) – автофильтр. Это удобный инструмент для отбора в таблице строк, соответствующих условиям, задаваемым пользователем.

Для нормальной работы автофильтра требуется "правильно" спроектированная таблица. Правильная с точки зрения MS EXCEL - это таблица без пустых строк/ столбцов, с заголовком, с однотипными данными в столбце (т.е. не нужно смешивать в одном столбце данные разных форматов, например текст и числа или числа и даты). Подробнее см. статью Советы по построению таблиц . Будем использовать именно такую, правильную таблицу (см. файл примера ).

Чтобы включить фильтр выделите любую ячейку таблицы, затем на вкладке Данные в группе Сортировка и фильтр выберите команду Фильтр ( Данные/ Сортировка и фильтр/ Фильтр ) или нажмите сочетание клавиш CTRL+SHIFT+L .

Команда Фильтр также доступна на вкладке Главная ( Главная/ Редактирование/ Сортировка и фильтр/ Фильтр )

В строке заголовка появятся стрелки раскрытия фильтра.

Ниже покажем как отфильтровать строки на основании текстовых, числовых значений и дат.

Отбор по столбцу с текстовыми значениями

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

Сначала отобразим только те строки, которые содержат в столбце Товар слово Гвозди ( Регистр букв не имеет значения). Сделаем это 2-мя способами.

Способ 1. Нажмем стрелку раскрытия фильтра. Затем нажмем на значение (Выделить все) - снимутся все галочки. Затем установите галочку напротив значения гвозди .

Нажмите ОК. В итоге отобразятся только те строки, которые содержат в столбце Товар значение Гвозди (т.е. строки со значениями Лучшие Гвозди или Гвозди 10 мм отобраны не будут). Понять, что применен фильтр очень просто: стрелка раскрытия фильтра изменит свой вид (на ней появится маленькая воронка), а номера отобранных строк станут синими. Если подвести курсор к стрелке раскрытия фильтра столбца, в котором используется фильтрация, отобразится всплывающая подсказка с фильтром, который используется в данном столбце, например, Товар : «Равно Гвозди». В строке состояния (внизу листа) отображается информация о примененном фильтре: «Найдено записей: 13 из 76».

Снять примененный фильтр можно несколькими способами:

• Нажмите стрелку раскрытия фильтра. Выберите пункт Снять фильтр с "Товар" или;
• Нажмите стрелку раскрытия фильтра, затем нажмите на значение (Выделить все) или;
• Выберите команду Очистить ( Данные/ Сортировка и фильтр/ Очистить ) или;
• Выберите команду Очистить , но в другом меню ( Главная/ Редактирование/ Сортировка и фильтр/ Очистить ) или;
• Нажмите сочетание клавиш CTRL+SHIFT+L (должна быть выделена любая ячейка таблицы). При этом фильтр будет выключен.

Способ 2. Нажмем стрелку раскрытия фильтра. Выберем Текстовый фильтр Равно.. . Появится диалоговое окно, введем значение гвозди или выберем его из выпадающего списка.

В результате получим тот же результат.

Примечание: Если в столбце содержится и текстовые значения и числа (пример "неправильной" таблицы), то MS EXCEL будет отображать меню Числовые фильтры только в том случае, если количество чисел в столбце больше чем текстовых значений. В противном случае будут отображено меню Текстовые фильтры .

Теперь используя Способ 2 рассмотрим настройку других текстовых фильтров. Например, установим фильтр для отбора строк, которые содержат в столбце Товар значения начинающиеся со слова Гвозди . В этом случае будут строки со значениями Гвозди и Гвозди 10 мм.

Для этого нажмем стрелку раскрытия фильтра. Выберем Текстовый фильтр Начинается с.. .

Введем значение Гвозди, в результате получим:

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

Более того, в диалоговом окне Настраиваемый фильтр возможно использование Подстановочных знаков , которые могут сделать настройку фильтра очень гибкой. Но, как правило, сложные критерии фильтра не используются, т.к. их невозможно сохранить, в отличие от Расширенного фильтра . Единственное, что можно сделать - это использовать отмену последнего действия ( CTRL+Z ), который запоминает настройки фильтра. Например, применив последовательно 3 условия фильтрации можно используя комбинации CTRL+Z и CTRL+Y отменять и заново применять условия фильтрации (не выполняйте при этом никаких других действий кроме настройки фильтра!).

СОВЕТ : Т.к. условия отбора записей (настройки автофильтра) невозможно сохранить, то чтобы сравнить условия фильтрации одной и той же таблицы в разных случаях, скопируйте лист с исходной таблицей, затем примените нужные фильтры для оригинала таблицы и ее копии. Для сравнения результатов фильтрации используйте 2 окна (для каждой из таблиц). Для этого создайте новое окно ( Вид/ Окно/ Новое окно ), перейдите в новый лист, затем выберите требуемый вид отображения окон ( Вид/ Окно/ Упорядочить все/ Рядом ).

Отбор по столбцу с числовыми значениями

Если столбец содержит числа, то меню фильтра будет иметь другой вид. Рассмотрим на примере столбца Количество .

Настройка фильтра в этом случае интуитивно понятна и аналогична рассмотренному выше примеру с текстом. Стоит отметить пункты меню Выше среднего и Ниже среднего : в этом случае будут отображены те строки, в которых значения в столбце Количество выше и соответственно ниже среднего (в нашем случае среднее значение в столбце Количество равно 59,5).

На картинке ниже показано, что отобраны только строки со значениями в столбце Количество, которые больше среднего (больше 59,5).

Числовой фильтр Первые 10 отберет 10 наибольших значений. Настройка этого фильтра позволяет отобрать и нужное количество наименьших значений.

Настроим фильтр для отбора 9 наибольших значений в столбце Количество . Для наглядности отсортируем таблицу по столбцу Количество .

Что значит 9 наибольших значений? В человеческом понимании это: 450; 300; 200; 100; 90; 87; 50; 40; 30. В понимании MS EXCEL - это 450; 300; 300; 300; 300; 200; 200; 200; 100, т.е. повторы также учитываются. Но это еще не все. Применим фильтр для отбора 9 наибольших значений в столбце Количество .

Вопреки ожиданиям будет отобрано не 9 значений, а 11. Дело в том, что 9-м наибольшим является число 100, которое повторяется еще 2 раза, т.е. в таблице 3 строки со значением 100 в столбце Количество . MS EXCEL естественно не знает какой из трех строк отдать предпочтение, поэтому отбирает все три! В итоге к 9 наибольшим добавляется еще 2 повтора, т.е. всего отбирается 11 строк.

Отбор по столбцу с датами

Если столбец содержит даты, то меню фильтра будет иметь еще более развернутый вид, чем в случае числовых столбцов (см. столбец Дата поставки ).

Как видим, что поле для творчества просто огромно. Настройка же самого фильтра достаточно проста и аналогична вышерассмотренным случаям.

Отбор по цвету

Если ячейки в таблице отличаются цветом шрифта или ячейки, то фильтр также можно настроить и по цвету.

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

Сортировка

Через меню фильтра также доступна сортировка таблицы. Текстовые значения могут быть отсортированы по алфавиту (от A до Z или от А до Я) или в обратном порядке. Числовые значения - по убыванию или возрастанию.

Проясним несколько моментов важных для осуществления правильной сортировки.

1. Перед сортировкой убедитесь, что фильтр охватывает все столбцы - иначе таблица при сортировке будет разрушена! Приведем пример.

Выделим только 2 заголовка 5 столбцовой таблицы и применим автофильтр. Для наглядности строки таблицы выделим разными цветами.

После сортировки столбца Товары от Я до А таблица будет разрушена! Это видно на рисунке ниже.

2. При сортировке столбцов скрытые строки не перемещаются. Перед сортировкой данных отобразите скрытые строки. Строки могут быть скрыты как через меню Главная/ Ячейки/ Формат/ Скрыть или отобразить так и после применения Автофильтра - в любом случае они не примут участия в сортировке!

3. Отменить сортировку таблица нельзя! Для отмены результатов сортировки нужно сразу выполнить команду Отмена последнего действия ( CTRL+Z ). Можно еще закрыть файл без сохранения, но есть риск потери других изменений.

СОВЕТ : Другой способ возвращения к первоначальной сортировке: заранее перед сортировкой создать дополнительный столбец с порядковыми номерами строк (вернуть прежнюю сортировку можно потом, заново отсортировав по нему).

4. Сортировку можно также можно осуществить с помощью меню Данные/ Сортировка и фильтр/ Сортировка . После нажатия кнопки меню будет отображено диалоговое окно.

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

1. Выделите ячейку таблицы.
2. На вкладке Данные в группе Сортировка и фильтр выберите команду Сортировка (та же команда доступна через меню ( Главная/ Редактирование/ Сортировка и фильтр/ Настраиваемая сортировка... ).
3. В диалоговом окне Сортировка выберите столбец по которому нужно произвести сортировку и порядок сортировки от А до Я.
4. В диалоговом окне Сортировка нажмите кнопку Параметры .
5. В диалоговом окне Параметры сортировки установите флажок Учитывать регистр .
6. Дважды нажмите кнопку ОК .

Как видно из рисунка ниже значения гвозди (строчные буквы) располагаются выше, чем значения Гвозди (первая буква Прописная).

Если данные в таблице изменились

Если данные в таблице были добавлены, изменены или удалены; или изменились значения формул в ячейках таблицы, то имеется возможность повторного применения фильтра или сортировки. Для этого выберите пункт Сортировка и фильтр на вкладке Главная в группе Редактирование , а затем нажмите кнопку Применить повторно .

Фильтрация в неправильных таблицах

В заключение рассмотрим неправильные таблицы. Предположим, что в таблице имеется пустая строка. Выделим ячейки заголовков и применим автофильтр (нажмите сочетание клавиш CTRL+SHIFT+L ).

Теперь попытаемся отфильтровать только те строки, которые содержат значения Цемент и гвозди в столбце Товар . Но у нас ничего не получится, т.к. в автофильтре нет значения Цемент !

Значения Цемент нет в меню автофильтра, т.к. в качестве таблицы MS EXCEL рассматривает только строки 6-9, а строки 11 и 12 - это уже другая таблица, т.к. под таблицей MS EXCEL понимает диапазон ячеек окруженный пустыми строками и столбцами (исключение составляют таблицы в формате EXCEL 2007 ).

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

Как и раньше под таблицей MS EXCEL понимает диапазон ячеек окруженный пустыми строками и столбцами и пытается самостоятельно определить строку заголовков. В принципе ничего страшного не произошло, кроме того, что в перечень фильтруемых значений включены названия заголовков (см. файл примера, лист Неправильные ).

Теперь для правильного установления фильтра необходимо сперва выделить ячейки заголовка, а только затем нажать сочетание клавиш CTRL+SHIFT+L ). Это не всегда удобно, поэтому над строкой заголовков всегда располагайте пустую строку, которую можно скрыть.