Краткое руководство: фильтрация данных с помощью автофильтра

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

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

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

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

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

В таблице

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

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

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

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

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

В диапазоне

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

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

Остальные — текстовый.

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

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

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

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

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

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

Как задать несколько параметров

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

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

Теперь необходимо дополнительно отделить товары стоимостью 100 рублей, для этого применяем отсев в разделе «Цена».

Нажимаем «Ок» и получаем только товары стоимостью 100 рублей, произведенные в России.

Как поставить расширенный поиск

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

Затем в свободной строке под скопированными заголовками задать необходимые условия поиска. Например, необходимо найти товары, произведенные в России, проданные менеджером Ивановым, стоимостью менее 300 рублей.

После того как параметры корректно введены, необходимо снова открыть вкладку «Данные» и выбрать функцию «Дополнительно».

Перед пользователем появится окно, в котором ему предстоит заполнить две строки:

• «Исходный диапазон» — это диапазон таблицы, информация которой подлежит фильтрации, то есть исходной таблицы. Его эксель введет автоматически;
• «Диапазон условий» — это ячейки, из которых программа возьмет значения для отсева, — вторая таблица, которую мы создали сверху. Чтобы значения появились в строке окна, необходимо просто захватить две ее строки: с наименованием раздела и введенными значениями.

После того как оба диапазона сформированы, нажмите «Ок» и оцените результат.

Как удостовериться, поставлен ли фильтр

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

Значком «1» обозначается столбец, в котором поиск установлен, но не применяется. Значком «2» обозначен уже примененный поиск.

Как добавить

Если Вы оформляли информацию через вкладку «Вставка» – «Таблица», или вкладка «Главная» – «Форматировать как таблицу», то в ней возможность фильтрации будет включена по умолчанию. Отображается нужная кнопка в виде стрелочки, которая расположена в верхней ячейке с правой стороны.

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

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

Как работает

Теперь давайте рассмотрим, как работает фильтр в Эксель. Для примера воспользуемся следующими данными. У нас есть три столбца: «Название продукта», «Категория» и «Цена», к ним будем применять различные фильтры.

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

Например, оставим в «Категории» только фрукты. Снимаем галочку в поле «овощ» и нажимаем «ОК».

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

Как удалить

Если Вам нужно удалить фильтр данных в Excel, нажмите в ячейке на соответствующий значок и выберите из меню «Удалить фильтр с (название столбца)».

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

Зачем нужны фильтры в таблицах Эксель

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

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

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

Добавляем макрос

“Ну и где же тут удобство?” – спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но “все меняется, когда приходят они ©” – макросы!

Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code). В открывшееся окно скопируйте и вставьте вот такой код:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:I5")) Is Nothing Then
        On Error Resume Next
        ActiveSheet.ShowAllData
        Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1").CurrentRegion
    End If
End Sub

Эта процедура будет автоматически запускаться при изменении любой ячейки на текущем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), то данный макрос снимает все фильтры (если они были) и заново применяет расширенный фильтр к таблице исходных данных, начинающейся с А7, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:

Так все гораздо лучше, правда?

Основа

Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы – это будет диапазон с условиями (выделен для наглядности желтым):

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

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский “Ашан” в III квартале, то условия будут выглядеть так:

Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data – Advanced). В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:

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

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

Реализация сложных запросов

Теперь, когда все фильтруется “на лету”, можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:

Тонкие моменты:

• Знак * подразумевает под собой любое количество любых символов, а ? – один любой символ.
• Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
• Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
• Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).

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

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

Обратите внимание:

Если выделить ВСЮ строку, то Эксель автоматически установит фильтр на ВСЕ столбцы, в которых есть хоть что-то. Если в столбце ничего нет, то фильтр не накладывается.

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

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

Если не выделять несколько ячеек, а просто щёлкнуть на одну, то фильтр добавляется на ВСЮ строку, как если бы вы её выделили.

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

Как пользоваться фильтром в Эксель

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

Смысл фильтра:

заключается в том, что Excel оставит только те строки таблицы, которые в ЭТОМ (с настроенным фильтром) столбце содержат ячейку с выбранным значением. Прочие строки будут скрыты.

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

Наложение нескольких фильтров на таблицу Эксель

Фильтры в таблице Эксель можно комбинировать. Наложение нескольких фильтров происходит по принципу логического “И”. Что это значит, давайте посмотрим на примере.

Если у нас в таблице несколько колонок, то можно сначала установить фильтр, например, на первый столбец. При этом часть строк в соответствии с условием фильтра будет скрыта. Затем настроим фильтр в следующем столбце. В таком случае оставшиеся после первого фильтра строки будут дополнительно отфильтрованы. Проще говоря, к каждой строке будут применены сразу 2 фильтра — по первому столбцу и по второму.

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

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

Теперь посмотрите видеоролик, в котором я показываю реальный пример фильтрации данных в прайс листе более чем из 15 000 строк.

Как пользоваться автофильтром

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

Выбор уникальных значений

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

1. Выделите нужный диапазон клеток.
2. Кликните на кнопку «Сортировка».
3. Выберите инструмент «Фильтр».

4. После этого вы увидите, что около первой ячейки появится символ выпадающего списка.
5. Кликните на него.
6. Благодаря этому появится список уникальных значений. Вам нужно поставить галочки около тех данных, которые должны выводиться на экран.
7. Чтобы сохранить изменения, необходимо нажать на «OK».

В качестве примера оставим все значения, кроме цифры «4».

8. Результат будет следующим.

Как мы видим, все остальные значения остались. Кроме «4». То есть если вы убираете галочку около какого-нибудь пункта, то эта информация будет выпадать из списка видимых.

Числовые фильтры

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

Работает это очень просто.

1. Кликаем на иконку около первой клетки.
2. Выбираем пункт «Числовые фильтры».
3. Указываем нужный критерий отбора информации.

4. В качестве примера выберем «больше или равно».

5. Благодаря этому откроется окно, в котором можно будет указать необходимые условия. Обратите внимание на то, что этот критерий можно расширить. Вы можете использовать сразу две строки для составления логического выражения.
6. Для сохранения пользовательского автофильтра требуется нажать на кнопку «OK».

6. В качестве примера укажем цифру «3».

7. Сразу после этого вы увидите, что все числа, которые были меньше 3, исчезли из поля видимости.

Обратите внимание на то, что у нас осталась видимой только единица. Хотя мы явно указали, что нужно вывести только те данные, которые больше или равны 3.

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

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

1. Кликните на иконку в первой клетке.
2. Выберите пункт «Удалить фильтр из столбца 1». В вашем случае название будет отличаться.
3. Благодаря этому книга примет прежний вид.

Настраиваемый фильтр

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

1. Нажмите на инструмент автофильтра.
2. Выберите пункт «Числовые фильтры».
3. Кликните на указанный вариант.

4. После этого вас попросят указать детали сортировки.
5. Раскройте список и прокрутите вниз. Вы увидите, что там есть такие условия, которые в меню изначально отсутствуют.
6. Затем нужно будет указать сам критерий отбора и сохранить фильтр нажатием на кнопку «OK».

Упорядочивание элементов

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

1. Нажимаем на указанную иконку.
2. Выбираем нужный вам способ для упорядочивания данных.

3. В результате этого вы увидите, что все данные перемешались согласно нашим указаниям. Мы в качестве примера выбрали вариант «по возрастанию».

Упорядочивание по цвету

Для демонстрации этой возможности нужно будет некоторые клетки выделить каким-нибудь цветом.

Затем порядок действий будет таков:

1. Снова нажимаем на иконку автофильтра.
2. Выбираем пункт «Сортировка по цвету».
3. Затем указываем нужный вам вариант.

4. Для более детальной настройки нужно будет выбрать пункт «Пользовательская сортировка».

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

Фильтр по цвету

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

1. Нажимаем на указанную иконку.
2. Выбираем «Фильтр по цвету».
3. Указываем какой-нибудь цвет.

4. В итоге вы увидите, что останутся только те ячейки, которые соответствуют выбранному цвету.

Работа с датами

Автофильтр может работать не только с числами. Этот инструмент отлично справляется и с временными данными. Для этого сначала добавим случайные даты.

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

1. Выделяем наш диапазон ячеек.
2. Нажимаем на инструмент «Сортировка».
3. Выбираем вариант «Фильтр». В дальнейшем можете использовать горячие клавиши Ctrl+Shift+L.

4. После этого снова кликаем на знакомый нам инструмент.
5. На этот раз вы увидите новый пункт «Фильтр по дате», поскольку наши значения соответствуют этому формату.
6. Кликнув по нему, вы увидите огромный список различных условий фильтрации. Выбирайте что хотите.

Как всё вернуть обратно

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

1. Используем знакомую нам иконку.
2. Убираем галочку около нужной информации.
3. Сохраняем нажатием на «OK».

4. Сразу после этого мы увидим, что ячейки с соответствующей датой оказались скрытыми.

5. Теперь снова выделяем наш диапазон клеток.
6. Кликаем на указанную кнопку.
7. Нажимаем на пункт «Фильтр». Обратите внимание на то, что иконка у этого пункта будет активной (если в выделенной области данных используется фильтрация).
8. В итоге всё вернется на свои места, словно ничего и не было.

Удаление

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

Отбор текста по столбцам

Отбор текста по столбцам

     В случаях, когда столбец наполнен текстовыми значениями, то накладывание фильтра будет производиться следующим образом:       Для примера отберем в столбце текстовых значений «Товар» всю номенклатуру позиций цемента, который был отгружен. Внимание! Регистр букв в фильтре игнорируется. Всё это можно произвести 2 способами:

      Способ 1. Раскрываем меню настройки фильтра, нажимаем на пункт «Выделить всё» для снятия всех галочек и устанавливаем свою галочку, согласно заданного условия, напротив товара «Цемент».        Нажимаем кнопку «ОК» и как итог производится выборка по названию товара «Цемент», но только по нему. Названия «Цемент 50» и «Цемент 60» в фильтр не попадут, несмотря на то, что относятся к одному виду продукции, но к разным маркам.

       Определить то, что был наложен фильтр на таблицу возможно по нескольким визуальным признакам:

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

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

• Установите курсор на любую из ячеек таблицы и комбинацией клавиш Ctrl+Shift+L можете отключить фильтр;
• Раскрываете стрелочку настройки фильтра или через контекстное меню и выбираете пункт «Убрать фильтр с «Товар»» (для иного столбика название будет другим);
• С помощью команды «Очистить» в панели управление на вкладке «Главная» в разделе «Редактирование» выпадающее меню «Сортировка и фильтр» или на вкладке «Данные» в разделе «Сортировка и фильтр»;
• Нажимаете стрелочку настройки фильтра и устанавливаете галочку на пункте «(Выделить всё)».

     Способ 2. Заходим в меню настройки фильтра и выбираем пункт «Текстовые фильтры» и потом пунктик «равно…» и в появившемся диалоговом окне при включенном условии «равно» пишем или выбираем из списка наш товар «Цемент» и получаем аналогичный результат.       Теперь на основе работы второго способа рассмотрим более широкое и точное наложение фильтра для получения всего спектра товаров с названием «Цемент». Для этого нам нужно выбрать Текстовые фильтры» и в предложенном списке нажимаем «начинается с…».        Теперь указываем в поле, что нам нужно отобрать все позиции, которые начинаются с названия «Цемент»:         И получаем нужный нам результат.         Также вы сможете настроить свой фильтр по столбцу «Товар» и по иных критериях, т.е. которые будут отвечать критериям: содержат или не содержат нужные значения, начинаются или заканчиваются по определённым условиям и другим параметрам. Для более гибкой и удобной работы стоит использовать разнообразные подстановочные знаки типа «*», «?». Наибольший эффект можно получить при использовании расширенного фильтра.

Выбор по числовым критериям

Выбор по числовым критериям

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

Наложения фильтра на столбики с датами

Наложения фильтра на столбики с датами

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

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