Что такое автофильтр в excel

Что такое автофильтр в 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 ). Это не всегда удобно, поэтому над строкой заголовков всегда располагайте пустую строку, которую можно скрыть.

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

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

Как сделать автофильтр в Excel

Для включения необходимо щелкнуть в любом месте таблицы с данными, перейти на вкладку «Данные» — «Сортировка и фильтр». Нажать кнопку «Фильтр». Или нажмите комбинацию горячих клавиш CTRL+SHIFT+L.

Рядом с заголовками появятся стрелочки. При нажатии открывается выпадающий список с условиями для фильтрации.

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

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

Сделаем фильтрацию числовых значений:

  1. Нажимаем кнопку автофильтра у заголовка столбца с числовыми значениями. Выбираем «Числовые фильтры» — раскрывается список условий.
  2. Выберем, например, «больше или равно». Откроется пользовательский автофильтр в Excel.
  3. Зададим в качестве критерия для фильтрации значение «3000». Введем эти цифры в поле напротив условия.
  4. На листе останутся только те данные, которые соответствуют заданному критерию.

Чтобы снять фильтр, нажимаем на значок в заголовке столбца и выбираем «Снять фильтр…».

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

  1. Нажимаем кнопку автофильтра у заголовка «Цена». В списке «Числовых фильтров» выбираем оператор «Первые 10».
  2. Открывается меню «Наложение условия по списку». Определяемся, какие значения хотим видеть: наибольшие или наименьшие. С помощью счетчика задаем, сколько таких значений должно отобразиться в таблице.
  3. Если отбор будет производиться по числам, то назначаем условие «Элементов списка». Если необходимо отфильтровать значения в процентах, то выбираем «% от количества элементов».
  4. Чтобы закрыть окно и выполнить условие поиска, жмем ОК.

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

В качестве критерия можно задать несколько условий поиска.

Назначим фильтр для столбца «Дата»:

  1. Нажимаем кнопку автофильтра. Открываем выпадающий список «Фильтры по дате».
  2. Чтобы отобразить данные за второе полугодие 2014 г., назначим критерий «После». В открывшемся окне «Пользовательского автофильтра» для критерия «После» напишем условие «01.06.2014». Выберем функцию «И». Назначим второй критерий – «До». Условие – «31.12.2014». Дату можно вводить вручную, а можно выбрать в «Календаре».
  3. После нажатия кнопки ОК пользователю становится доступна информация за второе полугодие 2014 года.

Отфильтруем текстовый диапазон ячеек:

  1. Нажимаем кнопку у заголовка «Наименование». При наведении курсора на «Текстовые фильтры» откроется список доступных операторов сравнения, по которым можно сделать отбор.
  2. Допустим, нам нужно отобразить информацию по товарам, в названии которых есть цифра «2». Выбираем критерий «Содержит». Вводим условие «2».
  3. После нажатия ОК.

При назначении условий для пользовательского автофильтра можно пользоваться элементами подстановки:

Читайте также:  Жанры видеороликов какие бывают

  • «?» — заменяет один любой знак. Условие для фильтрации данных в столбце «Наименование» — «Содержит «1?»:
  • «*» — заменяет несколько знаков.

В конце любого списка фильтров (числовых, текстовых, по дате) есть «Настраиваемый фильтр». Эта кнопка тоже открывает окно пользовательского автофильтра в Excel.

Пустые ячейки и промежуточные итоги

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

Отберем в диапазоне с пустыми ячейками ячейки со значениями:

  1. Выделяем диапазон с данными и щелкаем по кнопке «Фильтр» (чтобы у заголовков появились «стрелочки»).
  2. Нажимаем кнопку у заголовка первого столбца. В меню фильтрации выбираем «Выделить все». Убираем выделение напротив значения «Пустые».
  3. Чтобы выполнить команду, жмем ОК.

Все пустые строки будут удалены.

Если таблица имеет промежуточные итоги, то итоговая строка в Excel при применении автофильтра изменит свои значения.

Сменился диапазон – сумма стала иной.

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

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

Включение фильтра

Для работы с настройками автофильтра, прежде всего, нужно включить фильтр. Сделать это можно двумя способами. Кликните по любой ячейке таблицы, к которой вы хотите применить фильтр. Затем, находясь во вкладке «Главная», нажмите на кнопку «Сортировка и фильтр», которая размещена в блоке инструментов «Редактирование» на ленте. В открывшемся меню, выберите пункт «Фильтр».

Для включения фильтра вторым способом, перейдите во вкладку «Данные». Затем, как и в первом случае, нужно кликнуть по одной из ячеек таблицы. На завершающем этапе, нужно нажать на кнопку «Фильтр», размещенную в блоке инструментов «Сортировка и фильтр» на ленте.

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

Использование фильтра

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

После того, как это сделано, жмем на кнопку «OK».

Как видим, в таблице исчезают все строки со значениями, с которых мы сняли галочки.

Настройка автофильтра

Для того, чтобы настроить автофильтр, находясь всё в том же меню, переходим по пункту «Текстовые фильтры» «Числовые фильтры», или «Фильтры по дате» (в зависимости от формата ячеек столбца), а дальше по надписи «Настраиваемый фильтр…».

После этого, открывается пользовательский автофильтр.

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

  • Равно;
  • Не равно;
  • Больше;
  • Меньше
  • Больше или равно;
  • Меньше или равно;
  • Начинается с;
  • Не начинается с;
  • Заканчивается на;
  • Не заканчивается на;
  • Содержит;
  • Не содержит.

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

Например, в колонке о заработной плате зададим пользовательский автофильтр по первому значению «больше 10000», а по второму «больше или равно 12821», включив при этом режим «и».

После того, как нажмем на кнопку «OK», в таблице останутся только те строки, которые в ячейках в столбцах «Сумма заработной платы», имеют значение больше или равно 12821, так как нужно соблюдение обоих критериев.

Поставим переключатель в режим «или», и жмем на кнопку «OK».

Как видим, в этом случае, в видимые результаты попадают строки соответствующие даже одному из установленных критериев. В данную таблицу попадут все строки, значение суммы в которых больше 10000.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Ссылка на основную публикацию
Через какое время отключают сим карту мегафон
Часто можно слышать, что некоторые люди вместо одной сим-карты предпочитают пользоваться двумя или сразу несколькими. Это объясняется лояльной политикой компании...
Утилиты асус для ноутбука
Драйверы и утилиты от производителя для ноутбуков и нетбуков ASUS под операционную систему Windows 10 / 8.1 / 8 /...
Утилиты для виндовс 10 64 бит
Скачать антивирус NOD32 на компьютер Windows 10 бесплатно на русском языке для защиты ноутбука или ПК от вирусов и потенциального...
Через прямую l провести плоскость перпендикулярно данной
Не будет преувеличением утверждать, что построение взаимно перпендикулярных прямых и плоскостей наряду с определением расстояния между двумя точками являются основными...
Adblock detector