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

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

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

1

Подробная инструкция выглядит так:

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

2

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

3

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

4

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

5

1. У этого инструмента существует две вариации использования. Первый вариант – «Скопировать результаты в другое место», а второй – «Фильтровать список на месте». Эти функции реализуют различный вывод отфильтрованных данных. 1-я вариация выводит отфильтрованную информацию в другом месте книги, заранее указанном пользователем. 2-я вариация выводит отфильтрованную информацию в основной табличке. Производим выбор необходимого элемента. В нашем конкретном примере ставим отметку около надписи «Фильтровать список на месте». Переходим к следующему шагу.

6

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

7

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

8

1. Вернемся на несколько шагов назад. Если пользователь выберет вариацию «Скопировать результаты в другое место», то итоговый показатель будет отображен в заданном им месте, а основная табличка никак не изменится. В строчку «Поместить результат в диапазон» необходимо будет вбить адрес того места, в котором будет отображен результат. Здесь можно ввести одно поле, которое в итоге и станет началом координат для новой дополнительной таблички. В нашем конкретном примере это ячейка, имеющая адрес А42.

9

1. Нажав на «ОК», новая дополнительная табличка с указанными настройками фильтрации вставится ячейку А42 и растянется на прилегающую область.

10

Фильтрация и поиск в Excel

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

1. Откройте вкладку Данные, затем нажмите команду Фильтр. В каждом заголовке столбца появится кнопка со стрелкой. Если Вы уже применяли фильтры в таблице, то можете пропустить этот шаг.
2. Нажмите на кнопку со стрелкой в столбце, который необходимо отфильтровать. В данном примере мы выберем столбец C.
3. Появится меню фильтра. Введите ключевое слово в строке поиска. Результаты поиска появятся под полем автоматически, после ввода ключевого слова. В нашем примере мы введем слово “saris”, чтобы найти все оборудование этой марки.
4. Выполнив все шаги, нажмите ОК.
5. Данные на листе будут отфильтрованы в соответствии с ключевым словом. В нашем примере после фильтрации таблица содержит только оборудование марки Saris.

Как делать правильно?

Как сделать расширенный фильтр в Excel? Чтобы было понятно, каким образом происходит процедура и как она делается, рассмотрим пример.

Инструкция по расширенной фильтрации электронной таблицы:

1. Необходимо создать место выше основной таблицы. Там и будут располагаться результаты фильтрации. Должно быть достаточное количества места для готовой таблицы. Также требуется еще одна строка. Она будет разделять отфильтрованную таблицу от основной.
2. В самую первую строку освобожденного места скопировать всю шапку (названия колонок) основной таблицы.
3. Ввести необходимые данные для фильтрации в нужный столбец. Отметим, что запись должна выглядеть следующим образом: = «= фильтруемое значение».
4. Теперь необходимо пройти в раздел «Данные». В области фильтрации (значок в виде воронки) выбрать «Дополнительно» (находится в конце правого списка от соответствующего знака).
5. Далее во всплывшем окошке нужно ввести параметры расширенного фильтра в Excel. «Диапазон условий» и «Исходный диапазон» заполняются автоматически, если была выделена ячейка начала рабочей таблицы. Иначе их придется вводить самостоятельно.
6. Нажать на Ок. Произойдет выход из настроек параметров расширенной фильтрации.

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

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

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

Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (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, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:

Фильтр по столбцам в Excel

При работе с большими объёмами данных программе Excel может возникнуть необходимость воспользоваться фильтром.

С его помощью которого можно отобразить только ту информацию, которая необходима.

Для того, чтобы в Экселе (Excel) сделать фильтр по столбцам, нужно:

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

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

2) На панели инструментов Excel выбрать «Данные» -> «Фильтр».

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

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

Например, если нам нужны данные только по сотруднику «Петров А.Д», то в списке убираем все галочки, кроме значения «Петров А.Д».

Удобнее сделать так — снять галочку с пункта «Выделить всё» (это автоматически снимет выделение со всех значений), а затем поставить галочку рядом с интересующим нас значением.

В результате на листе Эксель отобразиться только одна строка из таблицы, а стрелка фильтрации в столбце «ФИО сотрудника» изменит свой вид.

Чтобы убрать фильтр достаточно щёлкнуть ещё раз на кнопке «Фильтр» (она будет выделена).

Рассмотрим другой пример использования фильтра в Excel — отбор по числовым значениям.

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

Сделать такой фильтр можно так:

1) Открываем фильтр для столбца «Зарплата».

2) Для работы с числами используются «Числовые фильтры». В списке условий выбираем «Больше».

3) Появится окно, где указываем значение «30000», а затем нажимаем на «OK».

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

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

Например, нужно отобразить сотрудников с зарплатой больше 30000 рублей, которые относятся к отделу «Руководство».

Для этого нужно применить оба фильтра последовательно.

1) Сначала устанавливаем фильтр «Зарплата > 30000 рублей».

2) Затем выбираем фильтр столбца «Отдел». В списке значений будут отображаться не все отделы, а только те отделы, в которых хотя бы 1 сотрудник получает зарплату > 30000 рублей.

Excel нам покажет следующие строки:

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

Горизонтальная сортировка в Excel

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

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

Но, все-таки, как отсортировать ячейки Excel по горизонтали? Для горизонтальной сортировки в Excel припасено несколько вариантов, которые дополняют друг друга:

1. С помощью стандартного механизма настраиваемой сортировки: Вкладка “Главная”/ группа “Редактирование”/ команда “Сортировка и фильтр”/ “Настраиваемая сортировка”. Далее, в параметрах выбираем сортировку по столбцам диапазона.

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

1. С помощью функций НАИМЕНЬШИЙ (SMALL) или НАИБОЛЬШИЙ (LARGE). Данный способ решает проблему большого количества данных по вертикали, умело оперируя смешанными и абсолютными ссылками, можно запросто сделать горизонтальную сортировку для большого количества данных. Но у этого способа есть один существенный недостаток, который, кстати, отсутствует в первом варианте, – это возможность сортировки только числовой информации. Да, к сожалению, для текста не подойдет.

1. С помощью механизма Power Query. Мы уже неоднократно использовали данный инструмент при решении конкурсных заданий ExcelWorldChamp здесь и здесь. На первый взгляд решение может показаться громоздким, однако, по мере освоения Power Query, можно привыкнуть. А, если вы планируете заниматься обработкой данных в Excel, то без использования данного инструмента сейчас просто нельзя.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Расширенный фильтр и немного магии

У подавляющего большинства пользователей Excel при слове «фильтрация данных» в голове всплывает только обычный классический фильтр с вкладки Данные — Фильтр (Data — Filter) :

Такой фильтр — штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой «доработкой напильником» (по традиции).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как пользоваться расширенным фильтром в Excel

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

Заполняем меню расширенного фильтра:

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» – значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

Применим инструмент «Расширенный фильтр»:

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

1. Результат формулы – это критерий отбора.
2. Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
3. Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
4. Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались только те строки, где значения в столбце «Количество» выше среднего.

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

Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.

Как скопировать данные на другой лист с помощью расширенного фильтра в Excel?

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

Вот простой способ решения этой задачи: сделайте следующее:

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

2. Затем нажмите Данные > Дополнительные, см. снимок экрана:

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

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

Скопируйте данные на другой лист с помощью расширенного фильтра с помощью кода VBA

Если у вас есть опыт работы с кодом VBA, вы можете завершить эту работу с помощью следующего кода VBA.

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

2. Удерживайте ALT + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.

3. Нажмите Вставить > Модульи вставьте следующий код в Модуль Окно.

Код VBA: скопируйте данные на другой лист с помощью расширенного фильтра:

Sub Advancedfiltertoanothersheet()
'Updateby Extendoffice
    Dim xStr As String
    Dim xAddress As String
    Dim xRg As Range
    Dim xCRg As Range
    Dim xSRg As Range
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the filter range:", "Kutools for Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xCRg = Application.InputBox("Please select the criteria range:", "Kutools for Excel", "", , , , , 8)
    If xCRg Is Nothing Then Exit Sub
    Set xSRg = Application.InputBox("Please select the output range:", "Kutools for Excel", "", , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xRg.AdvancedFilter xlFilterCopy, xCRg, xSRg, False
    xSRg.Worksheet.Activate
    xSRg.Worksheet.Columns.AutoFit
End Sub

4, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно, позволяющее выбрать диапазон данных, который вы хотите отфильтровать, см. снимок экрана:

5. Затем нажмите OK, и появляется другое диалоговое окно, напоминающее вам о выборе диапазона критериев, на основе которого вы хотите выполнить фильтрацию, см. снимок экрана:

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

7, Наконец, нажмите OK Кнопка, отфильтрованные данные были выведены на другой указанный лист по мере необходимости.