Как сделать вертикальный фильтр в excel

Горизонтальная фильтрация столбцов в Excel

Если вы не совсем начинающий пользователь, то, должно быть, уже заметили, что на 99% всё в Excel устроено для работы именно с вертикальными таблицами, где по столбцам идут параметры или атрибуты (поля), а в строчках располагается информация об объектах или событиях. Сводные таблицы, промежуточные итоги, копирование формул двойным щелчком — всё заточено именно под такой формат данных.

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

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

Способ 1. Новая функция ФИЛЬТР

Если вы работаете на новой версии Excel 2021 или в подписке Excel 365, то можно воспользоваться недавно появившейся функцией ФИЛЬТР (FILTER) , умеющей фильтровать исходные данные не только по строчкам, но и по столбцам. Для работы эта функция требует вспомогательный горизонтальный одномерный массив-строку, где каждое значение (ИСТИНА или ЛОЖЬ) определяет показываем ли мы или, наоборот, скрываем очередной столбец в таблице.

Добавим такую строку над нашей таблицей и пропишем в ней статус каждого столбца:

• Допустим, мы всегда хотим отображать первый и последний столбцы (заголовки и итоги), поэтому для них в первой и последней ячейках массива зададим значение =ИСТИНА.
• Для остальных столбцов содержимое соответствующих ячеек будет формулой, которая проверяет нужное нам условие с помощью функций И (AND) или ИЛИ (OR) . Например, что итог находится в интервале от 300 до 500.

После этого останется лишь использовать функцию ФИЛЬТР для отбора столбцов, над которыми в нашем вспомогательном массиве есть значение ИСТИНА:

Аналогичным образом можно отфильтровать столбцы и по заданному списку. В этом случае поможет функция СЧЁТЕСЛИ (COUNTIF) , проверяющая количество вхождений очередного названия столбца из шапки таблицы в разрешённый список:

Способ 2. Сводная таблица вместо обычной

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

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

Если все эти условия выполняются, то для построения сводной, внешне похожей на нашу исходную таблицу, её (исходную) нужно будет развернуть из кросс-таблицы в плоскую (нормализовать). А это проще всего сделать с помощью надстройки Power Query — мощного инструмента преобразования любых данных, встроенного в Excel начиная с 2016-й версии.

1. Преобразуем таблицу в «умную» динамическую командой Главная — Форматировать как таблицу (Home — Format as Table) .
2. Загружаем в Power Query командой Данные — Из таблицы/диапазона (Data — From Table/Range).
3. Фильтруем строку с итогами (в сводной будут свои итоги).
4. Щёлкаем правой кнопкой мыши по заголовку первого столбца и выбираем Отменить свёртывание других столбцов (Unpivot Other Columns) . Все невыделенные столбцы преобразуются в два — имя сотрудника и значение его показателя.
5. Фильтруем столбец с итогами, который ушёл в колонку Атрибут.
6. Строим сводную таблицу по получившейся плоской (нормализованной) таблице командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close & Load — Close & Load to. ) .

Теперь можно пользоваться имеющейся в сводных таблицах возможностью фильтрации столбцов — привычными галочками напротив имён и пунктами Фильтры по подписи (Label Filters) или Фильтры по значению (Value Filters) :

И само собой при изменении данных нужно будет обновить наш запрос и сводную сочетанием клавиш Ctrl + Alt + F5 или командой Данные — Обновить всё (Data — Refresh All) .

Способ 3. Макрос на VBA

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

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

Как и в первом способе, сначала реализуем вспомогательный диапазон-строку, где в каждой ячейке формулой будет проверяться наш критерий и выводиться логические значения ИСТИНА или ЛОЖЬ для видимых и скрытых столбцов соответственно:

Затем добавим простой макрос. Щёлкните правой кнопкой мыши по ярлычку листа и выберите команду Исходный код (Source code) . В открывшееся окно скопируйте и вставьте следующий VBA-код:

Вертикальный фильтр в excel

​Смотрите также​ списку.​ отображаемые, так и​все ячейки со значением​ер​ вводить туда диапазоны,​:​Результат отбора:​ прост:​ номеров.​ которые не содержат​ именно то, что​Есть таблица, в которой​ значение ЛОЖЬ, то​ таблицы – EXCEL​ такими простыми критериями​ заполнение параметров фильтра);​Главный недостаток стандартного фильтра​

​В качестве критерия можно​ скрытые данные таблиц.​

• ​ больше или равно​,​ жать​
• ​Такой фильтр — штука​Расширенный фильтр позволяет использовать​
• ​Делаем таблицу с исходными​

​Появится меню фильтра. Наведите​​ заданное слово.​​ Вам нужно.​ указаны Год выпуска​ строка после применения​ автоматически вставит ссылку​ особого смысла нет,​вызовите Расширенный фильтр (Данные/​ (Данные/ Сортировка и​ задать несколько условий​ Так же в​ 5000​За​

Задача 1 (начинается. )

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

​ Сортировка и фильтр/​ фильтр/ Фильтр) –​ поиска.​​ программе Excel можно​​5 или =5​лив​. Грустно, согласен! Но​ и для большинства​ формулы. Рассмотрим пример.​ имеющуюся. Например, так:​​ пункт​​Пользовательский автофильтр​

​ которая содержит точную​​ автомобиля.​ будет.​ таблицей (при наличии​ задачами легко справляется​ Дополнительно);​ это отсутствие визуальной​Назначим фильтр для столбца​

​ создавать свои специальные​

​все ячейки со значением​и т.д.​ «все меняется, когда​ случаев вполне сойдет.​Отбор строки с максимальной​Создаем таблицу условий. Особенности:​Числовые фильтры​введите необходимый текст​ фразу, число, дату​Требуется вывести только те​Примеры других формул из​ пустых строк в​ Автофильтр. Рассмотрим более​в поле Исходный диапазон​

​ информации о примененном​
​ «Дата»:​ пользовательские фильтры, которые​ 5​=п*в​ приходят они ©»​ Однако бывают ситуации,​ задолженностью: =МАКС(Таблица1[Задолженность]).​ строка заголовков полностью​

​, затем выберите необходимый​ в поле справа​ и многое другое.​

• ​ строки, в которых Год​ файла примера:​ таблице вставится ссылка​ сложные задачи по​
• ​ убедитесь, что указан​ в данный момент​Нажимаем кнопку автофильтра. Открываем​
• ​ могут быть хорошо​>=3/18/2013​слова начинающиеся с​ — макросы!​ когда нужно проводить​​Таким образом мы получаем​​ совпадает с «шапкой»​
• ​ числовой фильтр в​ от фильтра, затем​ В следующем примере​ выпуска совпадает с​​Вывод строк с ценами​​ не на всю​

​ фильтрации.​ диапазон ячеек таблицы​ фильтре: необходимо каждый​ выпадающий список «Фильтры​ приспособлены к нестандартным​все ячейки с датой​П​Работу с расширенным фильтром​

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

​ раз лезть в​ по дате».​

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

Задача 2 (точно совпадает)

​ по величине цена​ до первой пустой​ указать не =»=Гвозди»,​​ (​​ меню фильтра, чтобы​Чтобы отобразить данные за​ данных выполняется не​ 2013 (включительно)​В​ ускорить и упростить​ сразу по нескольким​ на одном листе​ строку заголовков в​ выберем​

​. В данном примере​ в журнале эксплуатации​ помощью элементарной формулы​​ в таблице. =C8>НАИБОЛЬШИЙ($С$8:$С$83;5) В​​ строки).​ а просто Гвозди,​A7:С83​ вспомнить критерии отбора​ второе полугодие 2014​ только по числовым​​Тонкие моменты:​​т.е.​

​ с помощью простого​ столбцам. Обычный фильтр​ Excel.​

• ​ исходной таблице и​между​ мы введем слово​ оборудования только продукцию​
• ​ =В10=С10.​ этом примере четко​Предыдущие задачи можно было​
• ​ то, будут выведены​);​ записей. Особенно это​ г., назначим критерий​ или текстовым значениям,​​Знак * подразумевает под​​П​
• ​ макроса, который будет​ тут не очень​Создадим фильтр по нескольким​ вставляем на этот​​, чтобы увидеть идентификационные​​ «case», чтобы исключить​
• ​ марки​

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

​ собой любое количество​авло​ автоматически запускать расширенный​ удобен и хочется​ значениям. Для этого​ же лист (сбоку,​​ номера в определенном​​ все позиции, содержащие​Saris​ таблица с перечнем​ НАИБОЛЬШИЙ(). Если отсортировать​​ автофильтром. Эту же​​ наименования​ ячейки содержащие табличку​ несколько критериев. Расширенный​ окне «Пользовательского автофильтра»​ и по цветам​ любых символов, а​в​ фильтр при вводе​ чего-то помощнее. Таким​

Задача 3 (условие ИЛИ для одного столбца)

​ введем в таблицу​ сверху, снизу) или​ диапазоне.​ это слово.​.​​ различных типов гвоздей.​​ столбец​

​ задачу обычным фильтром​начинающиеся​ с критерием, т.е.​ фильтр лишен этого​ для критерия «После»​ фона ячеек или​ ? — один​,​ условий, т.е. изменении​ инструментом может стать​​ условий сразу несколько​​ на другой лист.​

​В появившемся диалоговом окне​Данные будут отфильтрованы по​Откройте вкладку​Требуется отфильтровать только те​

​С​ не решить.​со слова Гвозди​ диапазон​​ недостатка – все​​ напишем условие «01.06.2014».​

Задача 4 (условие И)

​ шрифта. Данная возможность​ любой символ.​​П​​ любой желтой ячейки.​расширенный фильтр (advanced filter)​ критерий отбора данных:​ Вносим в таблицу​Пользовательский автофильтр​ заданному текстовому фильтру.​Данные​ строки, у которых​(цены), то получим:​Произведем отбор только тех​ (Гвозди 80мм, Гвозди2).​А1:А2​ критерии помещаются в​ Выберем функцию «И».​ существенно расширяет возможности​Логика в обработке текстовых​етро​​ Щелкните правой кнопкой​​, особенно с небольшой​

​Применим инструмент «Расширенный фильтр»:​ условий критерии отбора.​введите необходимые числа​ В нашем случае​, затем нажмите команду​ в столбце Товар​

​ 750; 700;​​ строк таблицы, которые​ Чтобы вывести строки​.​ виде отдельной таблички​ Назначим второй критерий​ фильтра в определенных​ и числовых запросов​

​в​​ мыши по ярлычку​ «доработкой напильником» (по​Теперь из таблицы с​Переходим на вкладку «Данные»​ для каждого из​ отражены только позиции​Фильтр​ содержится Гвозди 1​700​точно​ с товаром,​При желании можно отобранные​ над фильтруемыми записями.​ – «До». Условие​ условиях.​ немного разная. Так,​

Задача 5 (условие ИЛИ для разных столбцов)

​и т.д.​ текущего листа и​ традиции).​ отобранными данными извлечем​ — «Сортировка и​

​ условий, затем нажмите​ из категории​​. В каждом заголовке​​ дюйм, Гвозди 2​; 700; 620, 620,​содержат в столбце​содержащие​ строки скопировать в​Алгоритм создания Расширенного фильтра​ – «31.12.2014». Дату​Для включения необходимо щелкнуть​ например, ячейка условия​а*с​ выберите команду​Для начала вставьте над​ новую информацию, отобранную​ фильтр» — «Дополнительно».​OK​Другие​​ столбца появится кнопка​​ дюйма и т.д.​

​ 160, … В​ Товар продукцию Гвозди, ИЛИ​на слово гвозди,​ другую таблицу, установив​ прост:​ можно вводить вручную,​

Задача 6 (Условия отбора, созданные в результате применения формулы)

​ в любом месте​ с числом 5​слова начинающиеся с​Исходный текст (Source Code)​

​ вашей таблицей с​ по другим критериям.​

• ​ Если отфильтрованная информация​. В этом примере​, которые не содержат​
• ​ со стрелкой. Если​ товары Гвозди нержавеющие,​

​ человеческом понимании «3-ей​ которые в столбце Количество содержат значение​ например, Новые гвозди,​ переключатель в позицию​Создаем таблицу, к которой​

​ а можно выбрать​ таблицы с данными,​ не означает поиск​А​. В открывшееся окно​ данными несколько пустых​​ Например, только отгрузки​​ должна отобразиться на​ мы хотим получить​​ слово «case».​​ Вы уже применяли​ Гвозди хромированные и​ по величине цене»​ >40. Критерии отбора​ необходимо в качестве​ Скопировать результат в​​ будет применяться фильтр​​ в «Календаре».​

​ перейти на вкладку​ всех чисел, начинающихся​и содержащие далее​ скопируйте и вставьте​ строк и скопируйте​ за 2014 год.​ другом листе (НЕ​ номера, которые больше​Расширенные фильтры по дате​ фильтры в таблице,​ т.д. не должны быть​​ соответствует 620, а​​ в этом случае​ критерия указать =»=*Гвозди»​ другое место. Но​ (исходная таблица);​После нажатия кнопки ОК​ «Данные» — «Сортировка​ с пяти, но​С​ вот такой код:​ туда шапку таблицы​Вводим новый критерий в​ там, где находится​ или равны 3000,​ позволяют выделить информацию​ то можете пропустить​ отфильтрованы.​

​ в понимании функции​ должны размещаться под​

• ​ или просто *Гвозди,​ мы это здесь​Создаем табличку с критериями​ пользователю становится доступна​ и фильтр». Нажать​ ячейка условия с​, т.е.​Private Sub Worksheet_Change(ByVal​​ — это будет​​ табличку условий и​ исходная таблица), то​​ но меньше или​​ за определенный промежуток​ этот шаг.​Проще всего это сделать​ НАИБОЛЬШИЙ() –​ соответствующими заголовками (Товар​ где * является​ делать не будем.​​ (с условиями отбора);​​ информация за второе​ кнопку «Фильтр». Или​ буквой Б равносильна​А​
• ​ Target As Range)​ диапазон с условиями​ применяем инструмент фильтрации.​ запускать расширенный фильтр​ равны 4000.​ времени, к примеру,​Нажмите на кнопку со​
• ​ если в качестве​700​ и Количество) и​

​ подстановочным знаком и​
​Нажмите кнопку ОК и​Запускаем Расширенный фильтр.​ полугодие 2014 года.​ нажмите комбинацию горячих​ Б*, т.е. будет​

Задача 7 (Условия отбора содержат формулы и обычные критерии)

​пель​ If Not Intersect(Target,​ (выделен для наглядности​ Исходный диапазон –​

​ нужно с другого​Данные будут отфильтрованы по​ за прошлый год,​ стрелкой в столбце,​ фильтра задать условие,​

​. В итоге, будет​ должны располагаться на​ означает любую последовательность​ фильтр будет применен​Пусть в диапазоне​Отфильтруем текстовый диапазон ячеек:​ клавиш CTRL+SHIFT+L.​ искать любой текст,​с​ Range(«A2:I5»)) Is Nothing​ желтым):​ таблица с отобранными​ листа.​

​ заданному числовому фильтру.​ за этот месяц​ который необходимо отфильтровать.​ что после слова​ выведено не 4​ разных строках. Условия​ символов.​ — в таблице​A7:С83​Нажимаем кнопку у заголовка​Рядом с заголовками появятся​ начинающийся с буквы​ин​

​ Then On Error​Между желтыми ячейками и​ по предыдущему критерию​В открывшемся окне «Расширенного​ В нашем случае​ или между двумя​

​ В данном примере​ Гвозди должно идти​ строки, а только​

​ отбора должны быть​Настроим фильтр для отбора строк,​ останутся только строки​имеется исходная таблица с​ «Наименование». При наведении​ стрелочки. При нажатии​ Б.​,​ Resume Next ActiveSheet.ShowAllData​ исходной таблицей обязательно​

Задача 7.1. (Совпадают ли 2 значения в одной строке?)

​ данными. Так выполняется​ фильтра» выбираем способ​ отображаются только номера​ датами. В следующем​

​ мы выберем столбец​ цифра. Это можно​ одна (750);​ записаны в специальном​ у которых в​ содержащие в столбце​ перечнем товаров, содержащая​

Задача 8 (Является ли символ числом?)

​ курсора на «Текстовые​ открывается выпадающий список​Если текстовый запрос не​

​А​ Range(«A7»).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(«A1»).CurrentRegion​ должна быть хотя​ фильтр по нескольким​ обработки информации (на​ в диапазоне от​ примере мы воспользуемся​ C.​ сделать с помощью​Вывод строк с учетом​

​ формате: =»>40″ и​ столбце Товар содержится​ Товар наименования гвозди​ поля (столбцы) Товар,​ фильтры» откроется список​ с условиями для​ начинается со знака​нана​

​ End If End​ бы одна пустая​ столбцам.​ этом же листе​ 3000 до 4000.​ расширенным фильтром по​Появится меню фильтра. Введите​ формулы =ЕЧИСЛО(—ПСТР(A11;ДЛСТР($A$8)+2;1))​ РЕгиСТра =СОВПАД(«гвозди»;А8). Будут​ =»=Гвозди». Табличку с​ значение начинающееся со​ 20 мм, Гвозди​ Количество и Цена (см. файл​ доступных операторов сравнения,​ фильтрации.​

Задача 9 (Вывести строки, в которых НЕ СОДЕРЖАТСЯ заданные Товары)

​ =, то в​с​ Sub​ строка.​Чтобы использовать несколько фильтров,​

​ или на другом),​Автор: Антон Андронов​

​ дате, чтобы посмотреть​ ключевое слово в​Формула вырезает из наименования​ выведены только те​​ условием отбора разместим​​ слова Гвозди​ 10 мм, Гвозди​ примера). Таблица не​ по которым можно​Если данные отформатированы как​ конце можно мысленно​,​Эта процедура будет автоматически​

Вывод уникальных строк

​Именно в желтые ячейки​ можно сформировать несколько​ задаем исходный диапазон​ИТ попробуйте! ​

Расширенный фильтр в Excel

​ оборудование, которое было​ строке поиска. Результаты​ товара 1 символ​ строки, в которых​ разместим в диапазоне​ИЛИ​ 50 мм и​ должна содержать пустых​ сделать отбор.​ таблица, то автофильтр​ ставить *.​Ас​ запускаться при изменении​ нужно ввести критерии​ таблиц условий на​

​ (табл. 1, пример)​Можно использовать функцию​ отдано на проверку​ поиска появятся под​ после слова Гвозди​ товар гвозди введен​E4:F6​Обои.​ Гвозди. Остальные строки​ строк и столбцов,​Допустим, нам нужно отобразить​ включается автоматически (стрелочки​Даты надо вводить в​аи​ любой ячейки на​ (условия), по которым​

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

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

1. ​ видны сразу).​​ штатовском формате месяц-день-год​​и т.д.​​ текущем листе. Если​​ потом будет произведена​ реализации зависит от​ (табл. 2, условия).​для более мощной​Откройте вкладку​ ввода ключевого слова.​ Если этот символ​
2. ​ букв;​После нажатия кнопки ОК​ случае должны размещаться​Номера отобранных строк будут​ (да и обычный​ в названии которых​
3. ​Чтобы кнопки фильтрации в​ и через дробь​=*с​ адрес измененной ячейки​ фильтрация. Например, если​ поставленной пользователем задачи.​ Строки заголовков должны​ фильтрации, например применять​Данные​ В нашем примере​ число (цифра), то​
4. ​Вывод строк, у которых​​ будут выведены записи​​ под соответствующим заголовком​
5. ​ выделены синим шрифтом.​ Автофильтр) не будет​ есть цифра «2».​ шапке таблицы стали​ (даже если у​слова оканчивающиеся на​ попадает в желтый​​ нужно отобрать бананы​​Стандартными способами – никак.​

Использование расширенных текстовых фильтров в Excel

​ быть включены в​ фильтрацию для отдельных​и нажмите команду​ мы введем слово​ формула возвращает ИСТИНА​ цена выше среднего​ содержащие в столбце​ столбца (Товар) и​Чтобы отменить действие фильтра​​ правильно работать.​​ Выбираем критерий «Содержит».​​ доступны, нужно включить​​ вас русский Excel​С​ диапазон (A2:I5), то​ в московский «Ашан»​​ Программа Microsoft Excel​​ диапазоны.​

1. ​ записей с помощью​​Фильтр​​ «saris», чтобы найти​​ и строка выводится,​​ =С8>СРЗНАЧ($С$8:$С$83);​ Товар продукцию Гвозди ИЛИ​ должны располагаться друг​ выделите любую ячейку​Настроим фильтр для отбора строк,​ Вводим условие «2».​ строку заголовков. Корректная​
2. ​ и региональные настройки).​=. ​ данный макрос снимает​ в III квартале,​ отбирает данные только​Чтобы закрыть окно «Расширенного​
3. ​ операторов, таких как​. В каждом заголовке​ все оборудование этой​​ в противном случае​​ВНИМАНИЕ!​ значение >40 (у​ под другом в​ таблицы и нажмите​ которые содержат в​​После нажатия ОК.​​ работа автофильтра возможна​Условия записанные в разных​все ячейки с текстом​
4. ​ все фильтры (если​​ то условия будут​​ в столбцах. Поэтому​ фильтра», нажимаем ОК.​ или.​ столбца появится кнопка​​ марки.​​ строка не выводится.​Применение Расширенного фильтра​ любого товара).​ одном столбце (см.​CTRL+SHIFT+L​
5. ​ наименовании Товара значения​При назначении условий для​ только в том​ ячейках, но в​ из 4 символов​​ они были) и​​ выглядеть так:​ нужно искать другие​

Использование в Excel расширенных фильтров по дате

​ Видим результат.​Удаление фильтра из столбца​ со стрелкой. Если​Выполнив все шаги, нажмите​ В столбце F​ отменяет примененный к​Настоящая мощь Расширенного фильтра​ рисунок ниже). Табличку​(к заголовку будет​начинающиеся​ пользовательского автофильтра можно​ случае, если все​ одной строке -​ (букв или цифр,​

1. ​ заново применяет расширенный​​Чтобы выполнить фильтрацию выделите​​ решения.​​Верхняя таблица – результат​​Нажмите кнопку значок​ Вы уже применяли​ОК​ показано как работает​ таблице фильтр (Данные/​ проявляется при использовании​ с критериями размести​
2. ​ применен Автофильтр, а​со слова Гвозди. Этому​ пользоваться элементами подстановки:​ значения в определенном​ считаются связанными между​ включая пробелы)​ фильтр к таблице​
3. ​ любую ячейку диапазона​Приводим примеры строковых критериев​ фильтрации. Нижняя табличка​​Фильтр​​ фильтры в таблице,​.​ формула, т.е. ее​ Сортировка и фильтр/​ в качестве условий​​ в диапазоне​​ действие Расширенного фильтра​ условию отбора удовлетворяют​«?» — заменяет один​
4. ​ диапазоне имеют одинаковый​ собой логическим оператором​=м. н​ исходных данных, начинающейся​ с исходными данными,​ расширенного фильтра в​ с условиями дана​

Использование расширенных числовых фильтров в Excel

​рядом с заголовком​ можете пропустить этот​Данные на листе будут​ можно протестировать до​ Фильтр).​ отбора формул.​С1:С3​ будет отменено) или​

1. ​ строки с товарами​​ любой знак. Условие​​ формат (числа, дата,​​И (AND)​​все ячейки с текстом​ с А7, т.е.​ откройте вкладку​ Excel:​ для наглядности рядом.​ столбца и выберите​ шаг.​
2. ​ отфильтрованы в соответствии​ запуска Расширенного фильтра.​Рассмотрим теперь другую таблицу​Существует две возможности задания​.​ нажмите кнопку меню Очистить​ гвозди 20 мм,​ для фильтрации данных​
3. ​ текст и т.д.).​:​ из 8 символов,​​ все будет фильтроваться​​Данные​Преобразовать таблицу. Например, из​​ команду​Нажмите на кнопку со​​ с ключевым словом.​​Требуется отфильтровать только те​ из файла примера​ условий отбора строк:​
4. ​Окно с параметрами Расширенного​​ (Данные/ Сортировка и​​ Гвозди 10 мм,​ в столбце «Наименование»​Сделаем фильтрацию числовых значений:​​Т.е. фильтруй мне бананы​​ начинающиеся на​ мгновенно, сразу после​и нажмите кнопку​ трех строк сделать​Чтобы отменить действие расширенного​Удалить фильтр из​
5. ​ стрелкой в столбце,​ В нашем примере​ строки, у которых​ на листе Задача​непосредственно вводить значения для​ фильтра и таблица​

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

​ — «Содержит «1?»:​​Нажимаем кнопку автофильтра у​​ именно в третьем​М​ ввода очередного условия:​Дополнительно (Data — Advanced)​ список из трех​ фильтра, поставим курсор​

​Имя столбца «>.​

​ который необходимо отфильтровать.​​ после фильтрации таблица​​ в столбце Товар​ 7.​ критерия (см. задачи​​ с отфильтрованными данными​​Настроим фильтр для отбора строк,​

​ и Гвозди.​«*» — заменяет несколько​

​ заголовка столбца с​​ квартале, именно по​​и заканчивающиеся на​​Так все гораздо лучше,​​. В открывшемся окне​

Хотите узнать больше?

​ в любом месте​Удаление всех фильтров на​

​ В этом примере​ содержит только оборудование​

Фильтрация данных в Excel с использованием расширенного фильтра

​ НЕ содержатся: Гвозди,​В столбце Товар приведено​ выше);​ будет выглядеть так.​ у которых в​Табличку с условием отбора​ знаков.​ числовыми значениями. Выбираем​

​ Москве и при​Н​ правда? :)​ должен быть уже​ преобразованному варианту применить​ таблицы и нажмем​ листе​ мы выберем столбец​ марки​ Доска, Клей, Обои.​ название товара, а​сформировать критерий на основе​После нажатия ОК будут​ столбце Товар​ разместим разместим в​В конце любого списка​ «Числовые фильтры» -​

Как сделать расширенный фильтр в Excel?

​ этом из «Ашана».​, т.е.​Теперь, когда все фильтруется​ автоматически введен диапазон​ фильтрацию.​

1. ​ сочетание клавиш Ctrl​Щелкните​
2. ​ D, чтобы увидеть​Saris​
3. ​Для этого придется использовать​ в столбце Тип​
4. ​ результатов выполнения формулы.​

​ выведены все записи,​точно​

1. ​ диапазоне​ фильтров (числовых, текстовых,​ раскрывается список условий.​
2. ​Если нужно связать условия​М​ «на лету», можно​ с данными и​Использовать формулы для отображения​ + Shift +​данные​ нужные нам даты.​.​ простую формулу =ЕНД(ВПР(A15;$A$8:$A$11;1;0))​ товара — его​Рассмотрим критерии задаваемые формулой.​ содержащие в столбце​
3. ​содержится слово Гвозди.​А​ по дате) есть​Выберем, например, «больше или​ логическим оператором​андари​ немного углубиться в​ нам останется только​ именно тех данных​ L или «Данные»​>​
4. ​Появится меню фильтра. Наведите​Расширенные текстовые фильтры используются​Функция ВПР() ищет в​ тип. ​ Формула, указанная в​ Товар продукцию Гвозди​ Этому условию отбора​1:А2. Табличка должна содержать​ «Настраиваемый фильтр». Эта​ равно». Откроется пользовательский​ИЛИ (OR)​н​
5. ​ нюансы и разобрать​ указать диапазон условий,​ в строке, которые​

​ — «Сортировка и​Очистить​ указатель мыши на​ для отображения более​

​ столбце Товар каждой​

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

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

​ механизмы более сложных​ т.е. A1:I2:​ нужны. Например, сделать​ фильтр» — «Очистить».​

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

​ с товарами гвозди и​ столбца, по которому​ окно пользовательского автофильтра​Зададим в качестве критерия​ просто вводить в​

​М​ запросов в расширенном​Обратите внимание, что диапазон​ какой-то показатель выпадающим​Найдем с помощью инструмента​Сортировка по дате​Фильтры по дате​ ячеек, которые не​

​ указанных в диапазоне​

​ типа товара вывести​ ИСТИНА или ЛОЖЬ.​Произведем отбор только тех​ Гвозди (Регистр не​ будет производиться отбор.​ в Excel.​ для фильтрации значение​ разные строки. Например,​ангости​

​ фильтре. Помимо ввода​ условий нельзя выделять​ списком. А в​ «Расширенный фильтр» информацию​Сортировка данных с помощью​

​, затем выберите необходимый​ содержат заданный набор​А8:А11​ товары, у которых​

​Например, отобразим строки, содержащие​

​ строк таблицы, которые​ учитывается). Значения гвозди​ В качестве критерия​

​​ «3000». Введем эти​

​ если нам нужно​н​ точных совпадений, в​ «с запасом», т.е.​ соседнюю ячейку ввести​

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

​ по значениям, которые​ настраиваемого списка​ фильтр в раскрывающемся​ символов. Допустим, наша​. Если эти товары​

​ цена ниже средней.​

​ Товар, который встречается​точно​ 20 мм, Гвозди​ в ячейке​Когда таблица состоит из​ цифры в поле​

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

​ НЕ найдены, ВПР()​ То есть у​ в таблице только​содержат в столбце​ 10 мм, Гвозди​А2​

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

​ нескольких тысяч строк,​ напротив условия.​ менеджера Волиной по​=*н??а​ использовать различные символы​ пустые желтые строки,​

​ ЕСЛИ. Когда из​В таблицу условий внесем​ или таблице​

1. ​ примере мы выберем​ таким образом, что​ возвращает ошибку #Н/Д,​ нас 3 критерия:​ 1 раз. Для​ Товар продукцию Гвозди,​
2. ​ 50 мм учтены​укажем слово Гвозди.​ многие ячейки могут​На листе останутся только​ московским персикам и​все слова оканчивающиеся на​ подстановки (* и​ т.к. пустая ячейка​ выпадающего списка выбирается​ критерии. Например, такие:​Расширенный фильтр в Excel​ пункт​

​ в столбце​ которая обрабатывается функцией​ первый критерий задает​ этого введем в​

​ а в столбце Количество​ не будут.​

​Примечание​ содержать пустые значения.​ те данные, которые​ все заказы по​А​ ?) и знаки​ в диапазоне условий​

​ определенное значение, рядом​Программа в данном случае​ предоставляет более широкие​Сегодня​Тип​ ЕНД() — в​ Товар, 2-й -​

​ значение >40. Критерии​Табличку с условием отбора​: Структура критериев у​ Чистить вручную долго​ соответствуют заданному критерию.​ луку в третьем​

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

​, где 4-я с​ математических неравенств для​ воспринимается Excel как​ появляется его параметр.​ будет искать всю​ возможности по управлению​​, чтобы увидеть оборудование,​отображены только​​ итоге, формула возвращает​

​ его Тип, а​H2​ отбора в этом​ разместим разместим в​ Расширенного фильтра четко​ и непродуктивно.​Чтобы снять фильтр, нажимаем​ квартале по Самаре,​ конца буква​ реализации приблизительного поиска.​ отсутствие критерия, а​Чтобы привести пример как​ информацию по товарам,​ данными электронных таблиц.​​ которое было проверено​​Другие​ ИСТИНА и строка​ 3-й критерий (в​

Основа

​формулу =СЧЁТЕСЛИ(Лист1!$A$8:$A$83;A8)=1, а​ случае должны размещаться​ диапазоне​ определена и она​Отберем в диапазоне с​ на значок в​ то это можно​Н​ Регистр символов роли​

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

​изделия. В дополнение​ выводится.​ виде формулы) задает​ в​ под соответствующими заголовками​B1:В2​ совпадает со структурой​ пустыми ячейками ячейки​ заголовке столбца и​ задать в диапазоне​

​, т.е.​ не играет. Для​ — как просьба​ строкам в Excel,​​ есть слово «Набор».​​ в настройках, но​​Данные будут отфильтрованы по​​ мы исключим все​О выводе уникальных строк​ цену ниже средней.​Н1​ (Товар и Количество)​. Табличка должна содержать​ критериев для функций​

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

​вместо заголовка введем​​ и должны располагаться​ также название заголовка​​ БДСУММ(), БСЧЁТ() и​Выделяем диапазон с данными​Предположим, пользователю необходимо отобразить​Если же нужно наложить​н​ все возможные варианты​ без разбора.​Для списка товаров создадим​ можно использовать знак​​ действии.​​ нашем случае мы​ «case» в столбце​ фильтра можно прочитать​​ 6 и 7.​​ поясняющий текст, например,​​ на одной строке.​​ столбца, по которому​ др.​

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

​ и щелкаем по​ определенное количество наибольших​ два или более​ик​ в таблицу:​Переключатель​ выпадающий список:​ «=». Внесем в​С помощью стандартного фильтра​ увидим только позиции​Описание оборудования​​ в этой статье.​​ Введем нужные Товар​ Неповторяющиеся значения. Применим​ Условия отбора должны​ будет производиться отбор.​

​ ​ кнопке «Фильтр» (чтобы​ или наименьших значений​ условий на один​а​Критерий​Скопировать результат в другое​Над таблицей с исходными​ таблицу условий следующие​ пользователь Microsoft Excel​ оборудования, которые были​.​В прошлом уроке мы​​ и Тип товара.​​ Расширенный фильтр, указав​ быть записаны в​ В качестве критерия​

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

​ может решить далеко​ отданы на проверку​Откройте вкладку​ познакомились и научились​ Для заданного Тип​ в качестве диапазона​ специальном формате: =»=Гвозди»​ в ячейке​ размещают над таблицей,​ «стрелочки»).​Нажимаем кнопку автофильтра у​ просто продублировать заголовок​За​гр* или гр​позволит фильтровать список​ строку. В ячейки​Excel воспринимает знак «=»​

​ не все поставленные​ сегодня.​

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

​Данные​ применять стандартную фильтрацию​ товара вычислим среднее и​ условий ячейки​ и =»>40″. Табличку​B2​ к которой применяют​Нажимаем кнопку у заголовка​ заголовка «Цена». В​ столбца в диапазоне​н​все ячейки начинающиеся с​ не прямо тут​ введем формулу, которая​ как сигнал: сейчас​ задачи. Нет визуального​Расширенные числовые фильтры позволяют​, затем нажмите команду​ в Excel. Но​

​ выведем ее для​	​Н1:Н2​
​ с условием отбора​	​укажем формулу =»=Гвозди».​​ фильтр, но можно​​ первого столбца. В​​ списке «Числовых фильтров»​​ критериев и вписать​​оз​​Гр​​ же, на этом​​ будет показывать, из​​ пользователь задаст формулу.​​ отображения примененных условий​​ оперировать данными самыми​
​Фильтр​	​ очень часто возникают​ наглядности в отдельную​​.​​ разместим разместим в​
​Теперь все подготовлено для​	​ их разместить и сбоку​​ меню фильтрации выбираем​​ выбираем оператор «Первые​​ под него второе,​​а​​, т.е.​​ листе (как обычным​​ каких столбцов берется​​ Чтобы программа работала​​ фильтрации. Невозможно применить​​ различными способами. В​​. В каждом заголовке​​ ситуации, когда базовые​
​ ячейку F7. В​	​Обратите внимание на то,​​ диапазоне​​ работы с Расширенным​​ таблицы. Избегайте размещения​​ «Выделить все». Убираем​​ 10».​​ третье и т.д.​​и т.д.​​Гр​​ фильтром), а выгрузить​​ информация. ””;МАКС($A$1:A1)+1)’ >​​ корректно, в строке​​ более двух критериев​
​ следующем примере, мы​	​ столбца появится кнопка​​ инструменты фильтрации бессильны​​ принципе, формулу можно​​ что диапазон поиска​​E1:F2​​ фильтром:​​ таблички с критериями​​ выделение напротив значения​​Открывается меню «Наложение условия​​ условия. Вот так,​​>=э​​уша​​ отобранные строки в​​Рядом с выпадающим списком​​ формул должна быть​​ отбора. Нельзя фильтровать​​ выберем только то​
​ со стрелкой. Если​	​ и не могут​​ ввести прямо в​
​ значений введен с​	​.​выделите любую ячейку таблицы​ под исходной таблицей,​ «Пустые».​
​ по списку». Определяемся,​	​ например, можно отобрать​все слова, начинающиеся с​,​​ другой диапазон, который​​ ячейку введем следующую​​ запись вида: «;ГПР(СТОЛБЕЦ(A:A);$B$1:$O$33;ПОИСКПОЗ($A$12;$A$4:$A$9;)+3))’ > Ее​​ обл.6 кл.»​​ оставить только уникальные​​ в заданный промежуток​​ фильтры в таблице,​​ выборки. В этом​​ С7.​​ а критерий в​​ будут выведены все​​ но позволит ускорить​​ запрещено, но не​
​ ОК.​	​ видеть: наибольшие или​​ марта по май:​​,​​ейпфрут​​ указать в поле​​ задача – выбирать​
​После использования «Расширенного фильтра»:​	​ записи. Да и​ идентификационных номеров.​​ можете пропустить этот​
​ уроке Вы узнаете,​	​Далее действуем как обычно:​ функции СЧЁТЕСЛИ() –​​ записи содержащие в​​ заполнение параметров фильтра);​ всегда удобно, т.к.​
​Все пустые строки будут​	​ наименьшие. С помощью​
​В общем и целом,​	​Ю​
​,​	​Поместить результат в диапазон​ из таблицы те​Теперь отфильтруем исходную таблицу​
​ сами критерии схематичны​	​Откройте вкладку​ шаг.​
​ как решить эту​	​ выделяем любую ячейку​ с относительной ссылкой.​ столбце Товар продукцию​

​вызовите Расширенный фильтр (Данные/​

• ​ в исходную таблицу​ удалены.​ счетчика задаем, сколько​ после «доработки напильником»​или​
• ​Гр​. В данном случае​ значения, которые соответствуют​ по условию «ИЛИ»​ и просты. Гораздо​Данные​Нажмите на кнопку со​ проблему в Excel​ таблицы, вызываем Расширенный​ Это необходимо, поскольку​ Гвозди с количеством​ Сортировка и фильтр/​ могут добавляться новые​Если таблица имеет промежуточные​
• ​ таких значений должно​ из расширенного фильтра​Я​анат​ мы эту функцию​
• ​ определенному товару​ для разных столбцов.​ богаче функционал расширенного​, затем нажмите команду​ стрелкой в столбце,​ с помощью расширенных​

Логические связки И-ИЛИ

​ фильтр (Advanced Filter)​ при применении Расширенного​ >40.​ Дополнительно);​ строки.​​ итоги, то итоговая​​ отобразиться в таблице.​

​ выходит вполне себе​<>*о*​и т.д.​ не используем, оставляем​Скачать примеры расширенного фильтра​

​ Оператор «ИЛИ» есть​ фильтра. Присмотримся к​​Фильтр​​ который необходимо отфильтровать.​ фильтров.​ и указываем диапазон​ фильтра EXCEL увидит,​СОВЕТ:​в поле Исходный диапазон​ВНИМАНИЕ!​ строка в Excel​Если отбор будет производиться​ приличный инструмент, местами​все слова, не содержащие​=лук​Фильтровать список на месте​

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

​ не хуже классического​ букву​все ячейки именно и​и жмем​ инструмента «Выпадающий список»​ «Автофильтр». Но там​Расширенный фильтр позволяет фильтровать​

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

​ столбца появится кнопка​ мы выберем столбец​ выделить какие-то специфичные​Будут выведены 2 товара​А8​ отбора лучше каждый​ диапазон ячеек таблицы​ со значениями условий​ изменит свои значения.​ назначаем условие «Элементов​ автофильтра.​

​О​ только со словом​ОК​ и встроенных функций​ его можно использовать​ данные по неограниченному​ со стрелкой. Если​ C.​ данные, то, как​ из 4-х (заданного​— это относительная​ раз создавать табличку с​ вместе с заголовками​ отбора и исходной​Сменился диапазон – сумма​ списка». Если необходимо​Автофильтр в Excel –​<>*вич​Лук​. Отобранные строки отобразятся​

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

​ Excel отбирает данные​ в рамках одного​ набору условий. С​ Вы уже применяли​Появится меню фильтра. Наведите​ правило, базовые инструменты​ типа товара).​ ссылка и будет​ критериями и после​

​ (​ таблицей имеется, по​ стала иной.​ отфильтровать значения в​ встроенный режим фильтрации​

​все слова, кроме заканчивающихся​, т.е. точное совпадение​ на листе:​ в строках по​

​ столбца.​ помощью инструмента пользователь​ фильтры в таблице,​ указатель мыши на​ фильтрации с такой​В файле примера для​ перемещаться вниз по​ вызова фильтра лишь​A7:С83​ крайней мере, одна​Если после строки с​

​ процентах, то выбираем​

1. ​ числовых и текстовых​ на​*лив* или *лив​»Ну и где же​ определенному критерию.​
2. ​В табличку условий введем​ может:​ можете пропустить этот​
3. ​ пункт​ задачей уже не​ удобства использовано Условное​ столбцу Товар по​ менять ссылку на​
4. ​);​ пустая строка (это​ промежуточными итогами таблица​

​ «% от количества​ значений по определенным​вич​ячейки содержащие​

​ Расширенным фильтром).​ фильтрации данных нужно​Чтобы закрыть окно и​ или сразу в​ по отчеству)​

​как подстроку, т.е.​ будете правы. Мало​ «фильтрация данных» в​

​ столбец «Название») и​скопировать результат фильтрации на​

1. ​ стрелкой в столбце,​ текстовый фильтр в​ расширенных фильтров, включая​
2. ​ критериям (подробнее см.​ значение либо ИСТИНА,​: Если пришлось очистить​ табличку с критерием,​Теперь все подготовлено для​ выделять весь диапазон.​ выполнить условие поиска,​ нескольких столбцах. Он​=​О​ того, что нужно​ голове всплывает только​ После.» gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/excel-kak-vstavit-v-filtr_48_1.png»>
3. ​ который необходимо отфильтровать.​ раскрывающемся меню. В​ поиск текста, даты​ статью Выделение строк​

1. ​ параметры Расширенного фильтра​ т.е. диапазон​ работы с Расширенным​ Иначе программа может​ жмем ОК.​ позволяет быстро организовать​все пустые ячейки​
2. ​лив​ руками вводить условия​ обычный классический фильтр​Обратите внимание: критерии необходимо​задать условие любой сложности​ В этом примере​
3. ​ данном случае мы​

​ и фильтрацию по​ таблицы в MS​ будет возвращено значение​

• ​ (Данные/ Сортировка и​B1:B2​ фильтром:​ воспринять итоговую строку​Наибольшие и наименьшие значения​
• 
• ​ данные и скрыть​<>​

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

​ числовым значениям, что​

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

​ EXCEL в зависимости​ ИСТИНА, то соответствующая​ фильтр/ Очистить), то​.​выделите любую ячейку таблицы​ как окончание таблицы:​

​ выбираются из исходного​ лишние значения по​все непустые ячейки​

1. ​,​ так еще и​Данные — Фильтр (Data​ заголовками в РАЗНЫХ​извлечь уникальные значения.​
2. ​ A, чтобы увидеть​не содержит​ позволяет сузить результаты​ от условия в​ строка таблицы будет​ перед вызовом фильтра​
3. ​Нажмите ОК​ (это не обязательно,​

​ все, что находится​ диапазона данных, а​

​ условиям пользователя.​>=5000​Лив​ открывать диалоговое окно,​ — Filter)​

​ строках.​Алгоритм применения расширенного фильтра​

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

Фильтрация данных в Excel с использованием расширенного фильтра

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

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

Как сделать расширенный фильтр в Excel?

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

1. задать более двух критериев отбора;
2. скопировать результат фильтрации на другой лист;
3. задать условие любой сложности с помощью формул;
4. извлечь уникальные значения.

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

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

Верхняя таблица – результат фильтрации. Нижняя табличка с условиями дана для наглядности рядом.

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

Чтобы отменить действие расширенного фильтра, поставим курсор в любом месте таблицы и нажмем сочетание клавиш Ctrl + Shift + L или «Данные» — «Сортировка и фильтр» — «Очистить».

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

В таблицу условий внесем критерии. Например, такие:

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

Для поиска точного значения можно использовать знак «=». Внесем в таблицу условий следующие критерии:

Excel воспринимает знак «=» как сигнал: сейчас пользователь задаст формулу. Чтобы программа работала корректно, в строке формул должна быть запись вида: =»=Набор обл.6 кл.»

После использования «Расширенного фильтра»:

Теперь отфильтруем исходную таблицу по условию «ИЛИ» для разных столбцов. Оператор «ИЛИ» есть и в инструменте «Автофильтр». Но там его можно использовать в рамках одного столбца.

В табличку условий введем критерии отбора: =»=Набор обл.6 кл.» (в столбец «Название») и (в столбец «Цена»). То есть программа должна отобрать те значения, содержащие ТОЧНО информацию о товаре «Набор обл.6 кл.» ИЛИ информацию по товарам, цена которых ””;МАКС($A$1:A1)+1)’ >

Рядом с выпадающим списком ячейку введем следующую формулу: МАКС($B$1:$O$1);»»;ГПР(СТОЛБЕЦ(A:A);$B$1:$O$33;ПОИСКПОЗ($A$12;$A$4:$A$9;)+3))’ > Ее задача – выбирать из таблицы те значения, которые соответствуют определенному товару

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

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

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

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

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

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

В таблице

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

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

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

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

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

В диапазоне

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Удаление

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

Из столбца

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

С листа

Чтобы снять все имеющиеся отсевы со всех разделов одновременно, необходимо нажать на кнопку «Фильтр» во вкладке «Данные».