что такое фильтрация данных приведите пример фильтрации данных

Фильтрация данных

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

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

Таблица 6.16. Возможности Автофильтра

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

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

• условия в два или более столбца;

• более двух условий в одном столбце;

• условие, которое использует значение, вычисляемое формулой.

Таким образом, чтобы отфильтровать список с помощью расширенного фильтра, следует рядом со списком создать диапазон условий. Для этого в пустую строку создаваемого диапазона нужно ввести или скопировать заголовки фильтруемых столбцов, а в нижележащие строки ввести условия отбора. Диапазон условий и фильтруемый список должны быть разделены, по крайней мере, одним пустым столбцом или строкой. Затем для запуска процесса фильтрации необходимо указать ячейку в фильтруемом списке и выбрать команду Данные/Фильтр/Расши- ренный фильтр. В результате появится диалоговое окно Расширенный фильтр (рис. 6.51). C помощью переключателя Обработка, расположенного в этом окне, пользователь должен указать программе, где следует размещать отфильтрованные записи — на месте или в другом диапазоне. Чтобы поместить отфильтрованные записи за пределами существующего списка, следует установить переключатель Обработка в положение Скопировать результат в другое место, а в поле Поместить результат в диапазон указать верхнюю левую ячейку области вставки. Затем необходимо ввести в поле Диапазон критериев ссылку на диапазон условий отбора, включая заголовки.

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

Пример 7. В условии отбора содержатся ограничения по данным, записанным в два столбца и более.

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

Пример 8. В условии отбора содержится более одного ограничения по данным, записанным в одном столбце.

Источник

Расширенный фильтр в Excel и примеры его возможностей

Основа

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

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

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

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

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

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

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

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

Подготовка диапазона для условий фильтрации

В автофильтре условия для фильтрации данных задаются в меню, выпадающем после клика на кнопке в шапке таблицы. В расширенном же – условия фильтрации должны задаваться в отдельном диапазоне. Этот диапазон может располагаться на том же листе, что и данные, на другом листе или даже в другой книге. Главное, чтобы он был отделен от исходных данных как минимум одним пустым столбцом или строкой (чтобы Excel не принял их за один диапазон).

Также важно помнить, что при указании в 3 шаге (работа в Мастере “Расширенного фильтра”) диапазона условий нужно выделять шапку и строки, где есть какие-то критерии. Пустые строки выделять не нужно, иначе фильтра воспримет их как сигнал “Отобразить все строки”.

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

Формирование условий фильтрации

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

Они могут быть 3 видов:

Если в качестве текстового критерия ввести в поле какое-то слово, например, “Москва”, то будут отобраны ВСЕ строки, в которых в заданном столбце запись начинается со слова “Москва”

– числовые критерии и даты

В качестве критерия можно вводить число (и тогда будут отобраны строки, в которых значения столбца равны этому числу)

Также можно вводить выражения с использованием логических операторов (>, =, ). Например, найти строки с суммой больше 500 000 можно введя критерий >500000

Особо внимательным нужно быть при вводе критериев в виде даты. Даты обязательно необходимо вводить через косую черту. Например, чтобы отобрать все сделки после 4 января 2017, нужно ввести критерий по полю “Дата” – >04/01/2017 (в некоторых версиях Excel требуется осуществлять ввод в формате ММ/ДД/ГГГГ, то есть сначала указывать месяц. Имейте это в виду при работе).

Самое лучшее, что умеет расширенный фильтр – это использовать в качестве критерия формулы. Чтобы все работало, задаваемая Вами формула должна возвращать значение ИСТИНА (и тогда строка выведется) или ЛОЖЬ (строка будет скрыта). Крайне важно – шапка столбца с формулой должна отличаться от любой записи в шапке таблицы (можете вообще оставить ее пустой). При написании формул, не забывайте правильно расставлять абсолютные и относительные ссылки.

Например, если нужно показать топ 5 строк по полю сумма, то необходимо будет ввести следующую формулу:

где F10 – ячейка первой строки в столбце “Сумма” (она не закреплена, так как формула будет перебирать строки по очереди), $F$10:$F$37 – ссылка на диапазон, который занимает столбец “Сумма” (ссылка закреплена, так как столбец не изменяется).

В результате формула пройдет по всем строкам (от 10-ой до 37-ой) и скроет все, кроме тех, где значение больше шестого по величине (то есть оставит ТОП 5).

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

Итак, основные концепции, которые Вам нужно усвоить для успешного применения Расширенного фильтра:

– заголовок столбца, в котором пишем критерий отбора, должен быть точно таким же, как у того столбца, к которому применяем этот критерий. То есть, если отбираем строки, в которых в столбце “Сумма” значение больше 500, то и условие >500 пишем под шапку “Сумма”;

– если нужно задать условие И, но при этом использовать один и тот же столбец (например, И сумма больше 500 000, И сумма меньше 600 000 ), то заголовок такого столбца нужно продублировать дважды. Пример:

Теперь Вы знаете, какие критерии можно задавать, и как их правильно комбинировать. Этого достаточно, чтобы создавать сложные запросы, которые не под силу обычному автофильтру. Например, если нужно показать все сделки в Москве за 2017 год с суммой больше 500 000, а также одновременно отобразить все сделки Иванова за 2016 год, которые входят в ТОП5, то критерии будут выглядеть вот так:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Использование макроса расширенного фильтра

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

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

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range(“A2:I5”)) Is Nothing Then

On Error Resume Next

Range(“A7”).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(“A1”).CurrentRegion

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

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

Подробнее о сложных запросах

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

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

Здесь нужно учитывать такие моменты:

Логические условия

Огромное преимущество расширенного фильтра заключается в том, что можно использовать логические условия. Что это такое? Логическим условием называется такое условие, которое использует логический оператор И или ИЛИ. Конечно, есть и другие, но они описаны выше.

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

То есть, логическое условие «И» фактически расшифровывается, как «если критерий 1 и критерий 2 соответствуют критерию 3». Например, если яблоки и морковь стоят одинаково, то. Или – аналогично, просто вместо «И» вставляется «Или». На практике это может выглядеть так. Если пользователи купили товары компании 1 или компании 2, то отобразить эти покупки. В таком случае при приобретении чего-то третьего, оно не отображается.

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

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

13

В описываемом примере фильтр покажет только те бананы, которые были куплены в Московском Ашане в третьем квартале.

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

Например, в приведенном ниже примере фильтр будет учитывать только заказы менеджера Волиной как по персикам, купленным в Москве и луке, который продавался в третьем квартале в Самаре.

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

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

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

Для настройки фильтрации используйте:

кнопку « Фильтрация » на вкладке « Конструктор » ленты инструментов (также на вкладке « Главная » или « Данные » для экспресс-отчета);

вкладку « Фильтрация » боковой панели.

Доступны следующие варианты фильтрации:

большие или равные А;

меньшие или равные А;

между А и В включительно;

меньшие А или большие В;

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

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

Для отключения фильтрации отожмите верхнюю часть кнопки « Фильтрация » на вкладке ленты « Данные » или « Конструктор » (в экспресс-отчете) или « Конструктор » (для выделенного блока аналитической панели).

Срезы

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

Форматирование срезов

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Текстовый фильтр в Эксель

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

Однако, можно и более гибко настраивать отбор. Если в окне фильтра нажать « Текстовые фильтры », то в контекстном меню появится выбор способа сопоставления: равно, не равно, начинается с, заканчивается на, содержит, не содержит.

Например, нужно выбрать людей, которых не зовут Богдан. Выберем вариант «не содержит» и запишем ему критерий « Богдан ». Поставим пробелы перед именем и после. Иначе, например, Богданов Егор Егорович тоже попадет под фильтр, хотя его не зовут Богданом:

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

Пусть нам понадобилось отобрать людей с именем Богдан или Никита. Запишем логику, как на картинке

Как определить, какой выбрать оператор сравнения, «И» или «ИЛИ»? Логика такая:

Кроме того, в условии можно использовать операторы:

Например, чтобы выбрать ФИО, в котором присутствует строка «ктор», запишем условие так: *ктор*.

Как поставить фильтр в Экселе на столбец с числами

Числовые фильтры тоже можно гибко настраивать, есть такие способы отбора:

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

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

Стандартными способами – никак. Программа Microsoft Excel отбирает данные только в столбцах. Поэтому нужно искать другие решения.

Приводим примеры строковых критериев расширенного фильтра в Excel:

Чтобы привести пример как работает фильтр по строкам в Excel, создадим табличку:

Для списка товаров создадим выпадающий список:

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

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

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

Источник