Как пользоваться расширенным фильтром: Расширенный фильтр в Excel и примеры его возможностей — call-board

Содержание

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

Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.

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

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

Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.

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

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

Пользоваться автофильтром просто: нужно выделить запись с нужным значением.

Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:

Сразу видим результат:

Особенности работы инструмента:

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

У расширенного фильтра гораздо больше возможностей:

Можно задать столько условий для фильтрации, сколько нужно.

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

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

Готовый пример – как использовать расширенный фильтр в Excel:

Создадим таблицу с условиями отбора. Для этого копируем заголовки исходного списка и вставляем выше. В табличке с критериями для фильтрации оставляем достаточное количество строк плюс пустая строка, отделяющая от исходной таблицы.
Настроим параметры фильтрации для отбора строк со значением «Москва» (в соответствующий столбец таблички с условиями вносим = «=Москва»). Активизируем любую ячейку в исходной таблице. Переходим на вкладку «Данные» — «Сортировка и фильтр» — «Дополнительно».

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

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

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

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

Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

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

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

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

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

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

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

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

Основные правила:

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

Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

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

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

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

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

Скачать пример работы с расширенным фильтром

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

Расширенный фильтр в Excel | Блог Александра Воробьева

Опубликовано 20 мая 2014
Рубрика: Справочник Excel | 4 комментария

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

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

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

Вы читаете четвертый пост в цикле статей о создании баз данных в MS Excel и организации обработки информации.

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

Фильтр №2 – расширенный фильтр!

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

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

1. Открываем в MS Excel файл database.xls.

2. Создавая таблицу критериев отбора расширенного фильтра, рекомендую вначале действовать шаблонно, не задумываясь о задачах, которые предстоит решать. Копируем все заголовки столбцов-полей из ячеек A7…F7 в ячейки A1…F1 – заготовка для таблицы критериев готова! Можно приступать к основной работе.

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

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

Задача №5:

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

1. Записываем параметр фильтрации – слово «балка» в столбце «Изделие» верхней таблицы критериев отбора.

2. Активируем («встаем мышью») любую ячейку внутри таблицы базы данных – это обеспечит автоматическое заполнение окошка «Исходный диапазон» в выпадающем диалоговом окне «Расширенный фильтр».

3. Включаем расширенный фильтр в Excel 2003 через главное меню программы. Выбираем: «Данные» — «Фильтр» — «Расширенный фильтр».

4. В выпавшем окне «Расширенный фильтр» заполняем окна так, как показано на снимке экрана, расположенном ниже этого текста.

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

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

Регистр букв не влияет на результаты фильтрации!

Правила совместной «работы» нескольких условий отбора

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

Но главных правил — всего два! Все остальные случаи — различные сочетания этих двух правил.

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

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

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

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

Задача №6:

Отфильтровать информацию о пластинах массой менее 0,1 тонны по всей базе.

В результате работы расширенного фильтра показаны все пластины базы данных, имеющие массу менее 0,1 тонны (иллюстрация правила №1).

Задача №7:

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

Задача решена – показаны записи обо всех пластинах базы данных и обо всех изделиях тяжелее 1 тонны (иллюстрация правила №2).

Для отмены действия расширенного фильтра необходимо выполнить команду главного меню программы Excel «Данные» — «Фильтр» — «Отобразить все».

Обращаю внимание на необходимость внимательного контроля корректности указания исходного диапазона базы данных и диапазона таблицы условий в выпадающем диалоговом окне «Расширенный фильтр»!

В частности, в последнем примере необходимо указать: «Диапазон условий: $A$1:$F$3»!

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

Итоги.

При вводе условий поиска можно использовать общепринятые знаки подстановки и математические знаки:

* — любое количество любых символов
? – один любой символ
= — равно
< — меньше
> — больше
<= — меньше или равно
>= — больше или равно
<> — не равно

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

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

«Поковыряйтесь» самостоятельно в возможностях этого инструмента — он стоит того, чтобы в нем разобраться! Возросшая эффективность вашей работы многократно перекроет затраты времени потраченного на изучение!

Продолжение темы хранения и управления большими объемами информации — в следующих статьях цикла.

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!

Уважаемые читатели, вопросы и замечания пишите в комментариях внизу страницы.

Другие статьи автора блога

На главную

Статьи с близкой тематикой

Отзывы

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

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

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

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

Шаг 1: Создание таблицы с условиями отбора

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

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

Шаг 2: Запуск расширенного фильтра

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

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

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

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

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

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

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

Чтобы сбросить фильтр при использовании построения списка на месте, на ленте в блоке инструментов «Сортировка и фильтр» щелкните по кнопке «Очистить».

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

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Фильтрация данных в Excel с использованием расширенного фильтра. Расширенный фильтр в MS EXCEL Как сделать фильтр в excel по столбцам

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

Как добавить

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

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

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

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

Как работает

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

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

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

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

Как удалить

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

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

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

Числовой

Применим «Числовой…» к столбцу «Цена» . Кликаем на кнопку в верхней ячейке и выбираем соответствующий пункт из меню. Из выпадающего списка можно выбрать условие, которое нужно применить к данным. Например, отобразим все товары, цена которых ниже «25» . Выбираем «меньше».

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

В примере у меня получилось так. Здесь отображены все данные с «Ценой» ниже 25.

Текстовый

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

Оставим в таблице продукты, которые начинаются с «ка» . В следующем окне, в поле пишем: «ка*» . Нажимаем «ОК» .

«*» в слове, заменяет последовательность знаков. Например, если задать условие «содержит» – «с*л» , останутся слова: стол, стул, сокол и так далее. «?» заменит любой знак. Например, «б?тон» – батон, бутон, бетон. Если нужно оставить слова, состоящие из 5 букв, напишите «?????» .

Вот так я оставила нужные «Названия продуктов» .

По цвету ячейки

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

Сделаем «Фильтр по цвету» ячейки для столбика «Название продукта» . Кликаем по кнопочке со стрелкой и выбираем из меню одноименный пункт. Выберем красный цвет.

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

По цвету текста

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

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

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

О том, как , можно прочесть в предыдущих статьях цикла.

Фильтр №2 – расширенный фильтр!

1. Открываем в MS Excel файл .

Задача №5:

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

1. Записываем параметр фильтрации – слово «балка» в столбце «Изделие» верхней таблицы критериев отбора.

Регистр букв не влияет на результаты фильтрации!

Правила совместной «работы» нескольких условий отбора

Но главных правил — всего два! Все остальные случаи — различные сочетания этих двух правил.

Задача №6:

Отфильтровать информацию о пластинах массой менее 0,1 тонны по всей базе.

Задача №7:

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

В частности, в последнем примере необходимо указать: «Диапазон условий: $A$1:$ F $3 »!

Итоги.

При вводе условий поиска можно использовать общепринятые знаки подстановки и математические знаки:

* — любое количество любых символов
? – один любой символ
= — равно
> — больше
>= — больше или равно
— не равно

Продолжение темы хранения и управления большими объемами информации — в следующих статьях цикла.

Уважаемые читатели, вопросы и замечания пишите в комментариях внизу страницы.

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

Первый – автофильтр, предназначен для наиболее простых операций – выделение записей с конкретным значением (например, только выделение только записей, относящихся к Леброну Джеймсу), данных, лежащих в определенном диапазоне (или выше среднего или первую десятку) или ячеек/шрифтов определенного цвета (кстати, очень удобно). Соответственно, пользоваться им очень просто. Вам достаточно выделить те данные, которые вы хотите видеть отфильтрованными. Потом команда «Данные»/ «Фильтр». На каждой верхней ячейке верхней таблицы появится флажок списка, там уже легко разобраться с каждой командой, освоить просто и объяснять, я надеюсь, дальше не нужно, только нюансы использования автофильтра:

1) Работает только с неразрывным диапазоном. Два разных списка на одном листе отфильтровать уже не получится.

2) Самая верхняя строчка таблица автоматически назначается заголовком и в фильтрации не участвует.

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

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

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

1) Задает столько условий, сколько необходимо.

2) Позволяет выделить ячейки с уникальными (неповторяющимися) данными. Это часто бывает нужно в работе с данными и опция отлично справляется с проблемой.

3) Позволяет копировать результат фильтра в отдельное место, не трогая основной массив.

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

Примеры условий:

1) ‘L*’ – ячейки начинающиеся с L

2) ‘>5’ — данные больше 5

Если вы удаляете из отфильтрованной таблицы строки, то они удалятся, не прихватывая с собой соседей. Т.е. если таблица отфильтрована и показывает строчки с 26-29 и 31-25, выделение всех строк и их удаление не повлечет удаление строчки 30. Это удобно, лично я часто пользуюсь этим при написании макросов. Какое преимущество это дает – часто нам достаются таблицы, которые надо привести в рабочий вид, т.е. удалить, например пустые строки. Что мы делаем: применяем фильтр к таблице, показывая только те строки, которые нам не нужны, затем удаляем всю таблицу, включая заголовок. Удаляются ненужные строки и заголовок, при этом таблица не имеет пробелов и составляет единый диапазон. А строку заголовков можно добавить простой операций копирования из загодя заготовленной области. Почему это важно при написании макросов? Неизвестно, с какой строки начинаются нежелательные данные и непонятно, с какой строки их начать удалять, удаление всей таблицы помогает быстро решить эту проблему.

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

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

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

Теперь давайте посмотрим, как собственно добавить фильтры в таблицу.

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

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

[нажмите на картинку для увеличения]

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

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

[нажмите на картинку для увеличения]

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

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

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

[нажмите на картинку для увеличения]

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

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

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

[нажмите на картинку для увеличения]

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

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

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

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

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

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

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

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

Подведём итоги

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

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

Скачать файл, на примере которого я рассматривал работу с фильтрами, вы можете

Microsoft Excel — это повсеместно распространенный и удобный инструмент для работы с электронными таблицами.

Широкие функциональнее возможности делают эту программу второй по популярности после MS Word среди всех офисных программ.

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

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

Где в Excel фильтры — их виды

Найти фильтры в этой программе легко — нужно открыть главное меню или просто зажать клавиши Ctrl+Shift+L.

Основные функции фильтрации в Excel:

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

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

Как использовать расширенный фильтр в Excel — как его настроить

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

ВИДЕО ИНСТРУКЦИЯ

Порядок настройки:

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

Рассмотрим пример настройки.
У нас есть таблица со столбцами Товар, Количество и Цена.

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

Таблица с условиями разместится в ячейках А1:А2. Важно указать название столбца, где будет происходить отбор (ячейка А1) и само слово для отбора – Гвозди (ячейка А2).

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

Затем необходимо:

выделить любую из ячеек,
открыть «Расширенный фильтр» по пути: Данные – Сортировка и фильтр – Дополнительно,
проверить, что задано в поле «Исходный диапазон» — туда должна попасть вся таблица с информацией,
в «Диапазоне условий» необходимо задать значения ячеек с условием отбора, в данном примере это диапазон А1:А2.

После клика по кнопке «ОК» произойдет отбор нужной информации, и в таблице появятся только строки с нужным словом, в нашем случае это «Гвозди». Номера оставшихся строк окрасятся в синий цвет. Для отмены заданного фильтра достаточно нажать клавиши CTRL+SHIFT+L.

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

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

После нажатия «ОК» произойдет отсев данных.

Это самые простые примеры работы с фильтрами в excel . В расширенном варианте удобно задавать и другие условия для отбора, например, отсев с параметром «ИЛИ», отсев с параметром «Гвозди» и значением в столбце «Количество» >40.

Информацию в таблице можно фильтровать по столбцам – одному или нескольким. Рассмотрим на примере таблицы со столбцами «Город», «Месяц» и «Продажи».

Пример 1

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

Для получения информации по обратному принципу нужно воспользоваться параметром «ЯА».

Пример 2

Необходим отсев информации по месяцам, а также город с большим объемом продаж должен стоять в таблице выше города с меньшим объемом продаж. Для решения задачи требуется в «Сортировке и фильтре» выбрать параметр «Сортировка». В появившемся окне с настройками уточнить «Сортировать по» — «Месяц».

Далее нужно добавить второй уровень сортировки. Для этого нужно выбрать в «Сортировке» — «Добавить уровень» и указать столбец «Продажи». В колонке настроек «Порядок» указать «По убыванию». После нажатия «ОК» произойдет отбор данных по заданным параметрам.

ВИДЕО ИНСТРУКЦИЯ

Почему могут не работать фильтры в Excel

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

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

Решение проблемы:

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

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

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

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

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

Фильтрация данных — OpenOffice | Writer — текстовый процессор

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

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

Арифметические функции учитывают и ячейки, которые не отображаются из-за применения фильтра. Возможны три типа фильтрации списков:

• Автофильтр — для автоматической фильтрации и создания однострочных списков;

• Стандартный фильтр — для создания фильтрации согласно параметрам по умолчанию;

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

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

Как применить автофильтр

1. В окне открытого листа выделите столбцы данных или весь диапазон данных.

2. Откройте меню Данные и в списке команд наведите курсор на пункт Фильтр.

3. В раскрывшемся списке щелкните по строке Автофильтр.

4. В первой строке выбранного диапазона отобразятся стрелки полей списка.

5. Щелкните по стрелке списка нужного столбца и выберите условие фильтрации:

• Все — для отображения полного списка;

• 10 первых — для отображения

• 10 верхних значений списка;

• Стандартный фильтр — для переключения в режим работы с окном Стандартный фильтр;

• Пусто — для отображения только пустых ячеек;

• Не пусто — для отображения заполненных ячеек. 6. Отобразятся только строки, соответствующие фильтру. При этом в качестве заголовка также будет отображаться первая строка диапазона ячеек.

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

Как отобразить отфильтрованные строки

В окне открытого листа раскройте список нужного столбца и выберите условие фильтрации Все.

Как применить стандартный фильтр

1. В окне открытого листа выделите столбцы данных или весь диапазон данных.

2. Откройте меню Данные и в списке команд наведите курсор на пункт Фильтр.

3. Щелкните по строке Стандартный фильтр.

4. В окне Стандартный фильтр щелкните по кнопке До полнительно и выберите критерии фильтрации:

• Учитывать регистр — для учета при фильтрации прописных и строчных букв;

• Область содержит заголовки столбцов — для включения в первую строку диапазона ячеек заголовков столбцов;

• Копировать результат в — для размещения в выбранном диапазоне результатов фильтрации. Диапазон задается после активации пункта Сохранить условия;

• Регулярное выражение — для использования в определении фильтра подстановочных знаков;

• Без повторений — для исключения из списка отфильтрованных данных повторяющихся строк.

5. Закройте окно кнопкой ОК.

Как удалить фильтр

1. В окне открытого листа выделите фильтрованный диапазон данных .

2. Откройте меню Данные и в списке команд наведите курсор на пункт Фильтр.

3. В списке щелкните по строке Удалить фильтр.

Как применить расширенный фильтр

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

1. В окне открытого листа скопируйте необходимые заголовки столбцов диапазонов в свободное место.

2. В строке под заголовками введите критерии для фильтрации.

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

3. Откройте меню Данные и в списке команд наведите курсор на пункт Фильтр.

4. В раскрывшемся списке щелкните по строке Расширенный фильтр.

5. В окне Расширенный фильтр щелкните по кнопке Дополнительно и выберите критерии фильтрации (аналогично стандартному фильтру).

6. Щелкните по кнопке Свернуть/развернуть и выделите на листе созданную матрицу фильтра.

7. Закройте окно кнопкой ОК.

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

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

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

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

В программе Excel представлен простейший фильтр. Запустить его можно, используя вкладку «Данные» — «Фильтр» или с помощью ярлыка, расположенного на панели инструментов, который похож на воронку для переливания жидкости. Данный фильтр для большинства случаев является оптимальным вариантом. Однако в случае необходимости осуществления отбора по большому количеству условий с несколькими столбцами, строками и ячейками возникает следующий вопрос: а как сделать в Excel расширенный фильтр? Данный инструмент в расширенной версии называется Advanced Filter.

Расширенный фильтр: первое использование

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

	A	B	C	D	E	Заказчик
1	Продукция	Наименование	Месяц	День недели	Город
2	Зелень				Москва	Пятерочка
3
4	Продукция	Наименование	Месяц	День недели	Город	Заказчик
5	Овощи	Свекла	Январь	Понедельник	Сыктывкар	Магнит
6	Фрукты	Груша	Февраль	Понедельник	Гомель	Магнит
7	Фрукты	Банан	Март	Понедельник	Казань	Лента
8	Фрукты	Яблоко	Апрель	Понедельник	Калининград	Пятерочка
9	Фрукты	Персик	Май	Вторник	Урюпинск	Магнит
10	Овощи	Баклажан	Июнь	Четверг	Москва	Пятерочка
11	Зелень	Петрушка	Июль	Четверг	Москва	Пятерочка
12	Зелень	Сельдерей	Август	пятница	Москва	Пятерочка

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

Первая и вторая строка в представленной таблице предназначены для диапазона условий. Строки с 4 по 12 предназначены для ввода исходных данных. Для начала необходимо ввести во вторую строку соответствующие значения, от которых будет отталкиваться расширенный фильтр. Для запуска фильтра необходимо выделить ячейки исходных данных. Для этого необходимо выбрать вкладку «Данные» и нажать на кнопку «Дополнительно». В открывшемся окне будет отображен диапазон выделенных ячеек. Строка, согласно приведенному примеру, принимает значение $A$4:$F$122. Поле «Диапазон условий» соответственно заполняется значениями $A$1:$F$2. В окошке также содержится два условия: отфильтровать список на месте или скопировать полученный результат в другое место. Используя первое условие можно формировать результат прямо на том месте, которое отведено под ячейки исходного диапазона. При выборе второго условия можно сформировать результат в отдельном диапазоне. Этот диапазон необходимо прописать в поле «Поместить результат в диапазон». Пользователю необходимо выбрать удобный вариант, например, первый. Окно «Расширенный фильтр» после этого закрывается. Фильтр, основываясь на введенных данных сформирует другую таблицу.

	A	B	C	D	E	Заказчик
1	Продукция	Наименование	Месяц	День недели	Город
2	Зелень				Москва	Пятерочка
3
4	Продукция	Наименование	Месяц	День недели	Город	Заказчик
5	Зелень	Петрушка	Июль	Четверг	Москва	Пятерочка
6	Зелень	Сельдерей	Август	пятница	Москва	Пятерочка

В случае использования условия «Скопировать результат в другое место» значения строк 5 и 6 отобразятся в диапазоне, заданном пользователем. Исходный диапазон останется прежним.

Удобство применения

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

Создание сложных запросов

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

№	Пример запроса	Результат
1	=	Результатом будет являться выведение пустых ячеек, которые имеются в рамках заданного диапазона. Иногда использование данной команды может быть очень полезным для редактирования исходных данных. Таблицы с течением времени могут изменяться, а содержимое ячеек может быть удалено за неактуальностью или ненадобностью. Использование данной команды дает возможность выявить пустые ячейки с целью их последующего заполнения.
2	П*	Выведет все слова, которые начинаются на букву П
3	<>	Выведет все заполненные ячейки
4	ию	Выведет все значения, в которых имеется комбинация букв «ию»

Стоит отметить, что знак * может значит любое число символов. При введенном значении «а*» будут выведены все значения все зависимости от числа символов после буквы «а». Знак ? подразумевает один символ.

Использование связок OR и AND

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

Использование сводных таблиц

Еще одним способом фильтрования данных является использование команды «Вставка» — «Таблица» — «Сводная таблица». Данная таблица работает аналогично с выделенным заранее диапазоном данных. Она выбирают уникальные значения, чтобы подвергнуть их в дальнейшем анализу. На деле выглядит это как работа с выпадающим списком полей и диапазоном значений, выдаваемых при выборе уникального поля. Основное неудобство, связанное с использование сводных таблиц, состоит в необходимости ручной корректировки данных при их изменении.

Заключение

Подводя итоги, хотелось бы отметить, что область использования расширенных фильтров в программе Microsoft Excel довольно широка. Нужно только проявить немного фантазии и развить собственные навыки и умения. Фильтр сам по себе довольно прост в использовании и освоении. Как пользоваться расширенным фильтром в Excel, разобраться несложно. Однако стоит отметить, что он предназначен только для тех случаев, когда требуется малое количество раз выполнить фильтрацию сведений для их последующей обработки. Это, как правило, не предусматривает работу с большими массивами информации в виду простого человеческого фактора. В данном случае на помощь приходят более продвинутые технологии обработки информации. Большой популярностью сегодня пользуются макросы, составляемые на языке VBA. Они дают возможность запустить большое количество фильтров, которые способствуют отбору значений и их выводу в соответствующие диапазоны. Макросы дают возможность успешно заменить многочасовую работу по составлению периодической и прочей отчетности, заменяя ее на одно нажатие мышки. Использование макросов всегда оправдано. Любой пользователь, который хоть раз сталкивался с необходимостью использования данного элемента, при желании всегда может найти множество материалов для поиска ответов на интересующие вопросы и развития собственных знаний

Как добавить в фильтр новые строки. Сортировка и фильтрация

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

Фильтр устанавливается на заголовки и подзаголовки таблиц; главное, чтобы ячейки, на которые будет устанавливаться фильтр, не были пустыми. А располагается он в меню книги Excel на вкладке «Данные», раздел «Сортировка и фильтр»:

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

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

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

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

В нем можно задать следующие условия:

Условия «равно» и «не равно» пояснений не требуют, т.к. с ними все предельно ясно;
«больше», «меньше», «больше или равно» и «меньше или равно». Как строки можно сравнить друг с другом? Чтобы это понять, вспомните, каким образом Excel выполняет сортировку. Т.е. чем дальше в списке сортировки будет находиться строка, чем больше ее значение. Следующие утверждения являются истинными (верными): AА; А
«начинается с», «не начинается с», «заканчивается на», «не заканчивается на», «содержит» и «не содержит». В принципе, условия говорят сами за себя и в качестве значений могут принимать символ либо набор символов. Обратите на подсказку в окне, расположенную ниже всех условий (пояснения будут дальше).

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

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

При использовании «ИЛИ» должно соблюдаться хотя бы одно из заданных условий.

В самом конце окна пользовательского автофильтра приведена подсказка. Первая ее часть: «Знак вопроса «»?»» обозначает один любой знак …». Т.е. при проставлении условий, когда нельзя точно определить символ в конкретном месте строки, подставьте на его место «?». Примеры условий:

Начинается с «?ва» (начинается с любого символа за которым следуют символы «ва») вернет результаты: «Иванов», «Иванова», «кварц», «сват» и другие строки, подходящие под условие;
Равно «???????» – вернет в результате строку, которая содержит 7 любых символов.

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

Заканчивается на «о*т» (заканчивается символами «о», после которого идет любая последовательность символов, затем символ «т») вернет результат: «пот», «торт», «оборот» и даже такой – «рвнщуооовиунистврункт».
Равно «*» – вернет строку, которая содержит хотя бы один символ.

Помимо текстовых фильтров бывают «Числовые фильтры», которые в основном принимают те же самые условия, что и текстовые, но имеют и дополнительные, относящиеся только к числам:

«Выше среднего» и «Ниже среднего» – возвращает значения, которые выше и ниже среднего значения соответственно. Среднее значение рассчитывается, исходя из всех числовых значений списка столбца;
«Первые 10…» – клик по данному пункту вызывает окно:

Здесь можно задать какие элементы выводить первые из наибольших или первые из наименьших. Также, какое количество элементов вывести, если в последнем поле выбран пункт «элементов списка». Если же выбран пункт «% от количества элементов», второе значение задает данный процент. Т.е. если в списке 10 значений, то будет выбрано самое высокое (или самое низкое) значение. Если в списке 1000 значений, то либо первые, либо последние 100.

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

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

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

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

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

Для того, чтобы выполнить сортировку в обратном порядке, в том же меню выбираем кнопку Сортировка от Я до А».

Список перестраивается в обратном порядке.

Нужно отметить, что подобный вид сортировки указывается только при текстовом формате данных. Например, при числовом формате указывается сортировка «От минимального к максимальному» (и, наоборот), а при формате даты – «От старых к новым» (и, наоборот).

Настраиваемая сортировка

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

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

После этого, открывается окно настроек сортировки. Если в вашей таблице есть заголовки, то обратите внимание, чтобы в данном окне обязательно стояла галочка около параметра «Мои данные содержат заголовки».

В поле «Столбец» указываем наименование столбца, по которому будет выполняться сортировка. В нашем случае, это столбец «Имя». В поле «Сортировка» указывается, по какому именно типу контента будет производиться сортировка. Существует четыре варианта:

Значения;
Цвет ячейки;
Цвет шрифта;
Значок ячейки.

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

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

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

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

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

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

Но, это ещё не все возможности настраиваемой сортировки. При желании, в этом окне можно настроить сортировку не по столбцам, а по строкам. Для этого, кликаем по кнопке «Параметры».

В открывшемся окне параметров сортировки, переводим переключатель из позиции «Строки диапазона» в позицию «Столбцы диапазона». Жмем на кнопку «OK».

Теперь, по аналогии с предыдущим примером, можно вписывать данные для сортировки. Вводим данные, и жмем на кнопку «OK».

Как видим, после этого, столбцы поменялись местами, согласно введенным параметрам.

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

Фильтр

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

Чтобы воспользоваться данной функцией, становимся на любую ячейку в таблице (а желательно в шапке), опять жмем на кнопку «Сортировка и фильтр» в блоке инструментов «Редактирование». Но, на этот раз в появившемся меню выбираем пункт «Фильтр». Можно также вместо этих действий просто нажать сочетание клавиш Ctrl+Shift+L.

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

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

Когда процедура выполнена, жмем на кнопку «OK».

Как видим, в таблице остались только строки с именем работника Николаева.

Усложним задачу, и оставим в таблице только данные, которые относятся к Николаеву за III квартал 2016 года. Для этого, кликаем по значку в ячейке «Дата». В открывшемся списке, снимаем галочки с месяцев «Май», «Июнь» и «Октябрь», так как они не относятся к третьему кварталу, и жмем на кнопку «OK».

Как видим, остались только нужные нам данные.

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

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

Если нужно полностью удалить фильтр, то, как и при его запуске, в этом же меню следует выбрать пункт «Фильтр», или набрать сочетание клавиш на клавиатуре Ctrl+Shift+L.

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

Умная таблица

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

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

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

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

После этого, как и в прошлый раз, откроется окно, где можно скорректировать координаты размещения таблицы. Жмем на кнопку «OK».

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

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

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

Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.

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

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

Сразу видим результат:

Особенности работы инструмента:

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

У расширенного фильтра гораздо больше возможностей:

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

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

Готовый пример – как использовать расширенный фильтр в Excel:

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

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

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

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

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

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

Основные правила:

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

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

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

Как добавить

Как работает

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

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

Как удалить

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

Числовой

В примере у меня получилось так. Здесь отображены все данные с «Ценой» ниже 25.

Текстовый

Оставим в таблице продукты, которые начинаются с «ка» . В следующем окне, в поле пишем: «ка*» . Нажимаем «ОК» .

Вот так я оставила нужные «Названия продуктов» .

По цвету ячейки

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

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

По цвету текста

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

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

В столбце таблицы остались искомые данные.

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

Фильтр по дате в Excel.
Как правильно настроить таблицу и фильтр по дате, читайте в статье «Сортировка по дате в Excel «.
Фильтр по цвету ячейки в Excel .
Можно отфильтровать данные по цвету ячейки. Ячейки могут быть окрашены вручную или условным форматированием. Как окрасить ячейки условным форматированием по числу, по словам, по дате, т. д., «Условное форматирование в Excel».
Нажимаем на розовый цвет. Нажимаем «ОК». Получилось так.
Как выделить только видимые ячейки в Excel , как вставить только в видимые строки Excel, смотрите статью «Вставить в видимые строки в Excel» .
В Excel можно настроить ячейку, в которой будет указано число отфильтрованных строк, включен фильтр или нет. Это нужно для того, чтобы не запутаться, когда часто пользуемся фильтром, или несколько человек работает в одной таблице, т.д. Подробнее об этом читайте в статье «Как настроить фильтр в Excel «.
После работы с отфильтрованными данными (например, распечатали список этот), можно таблицу вернуть в первоначальный вид, т. е. со всеми, не отфильтрованными данными. Нажимаем кнопку фильтра вверху столбика и выбираем «Выделить все». У нас появилась таблица в первоначальном виде.
Как копировать данные фильтра, смотриет в статье » Копирование в Excel отфильтрованных данных » .
Можно удалить кнопку фильтра из таблицы Excel . Нажимаем на вкладке «Главная» кнопку «Сортировка и фильтр», затем «Фильтр».
Или заходим на вкладку «Данные» и нажимаем кнопку «Фильтр». Кнопка исчезла.
Если таблицу с отфильтрованными данными нужно распечатать, чтобы порядковый номаер строк шел подряд (1, 2, 3…) или посчитать количество отфильтрованных строк, ячеек, то это можно настроить. Смотрите «Порядковый номер строк по порядку после фильтра в Excel».
Как сделать сортировку данных таблицы, читайте в статье »

Видео: подробные сведения о расширенном фильтре

Вы можете использовать Advanced Filter для создания более мощных фильтров, таких как фильтрация для уникальных записей и использование таких операторов, как OR.

Удалить фильтр из столбца

Щелкните значок Фильтр рядом с заголовком столбца, а затем щелкните Очистить фильтр от <« Имя столбца »>.

Удалите все фильтры на листе

Щелкните DATA > Очистить .

Хотите больше?

Сортировать по датам

Сортировка данных с помощью настраиваемого списка

Фильтровать данные в диапазоне или таблице

Мы отфильтровали с помощью автофильтра в подробном видео об автофильтре.Он прост в использовании и имеет множество опций.

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

В диалоговом окне Advanced Filter щелкните диапазон List range .

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

В данном случае столбец «Название продукта». Щелкните Только уникальные записи . Нажмите ОК , и теперь Чай появляется только один раз.

Вы также можете использовать кнопку Remove Duplicates на вкладке DATA , в зависимости от того, чего вы хотите достичь, потому что это удалит повторяющиеся записи, а не просто отфильтрует их.

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

В этом примере я создал критерии в ячейках с G1 по h3.

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

Критерий в столбце «Категория» равен «Напиткам», а столбец «Последний заказ» равен 28 марта 2013 г.

Щелкните Advanced на вкладке DATA . В диалоговом окне Advanced Filter выберите список, который вы хотите отфильтровать. Щелкните в диапазоне Критерии .

Выберите критерии, в данном случае от G1 до h3. Щелкните OK , и диапазон будет отфильтрован с использованием критериев.

Чтобы сделать критерий равным Напитки ИЛИ 28 марта 2013 г., переместите дату последнего заказа на строку вниз.

Щелкните Advanced , сбросьте диапазон Criteria , щелкните OK , и отобразятся строки, в которых столбец «Категория» равен «Напиткам» ИЛИ столбец «Последний заказ» равен 28 марта 2013 г.

Чтобы удалить фильтр, щелкните Очистить на вкладке ДАННЫЕ .

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

Введите знак «больше» перед значением, щелкните Advanced , сбросьте Criteria range , щелкните OK , и диапазон будет отфильтрован с использованием критериев.

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

Стрелка влево, стрелка вправо, звездочка s вернет строки, текст в столбце категории которых не заканчивается на s.

В диалоговом окне Advanced Filter установите диапазон критериев . Я использую только G1 — G2.

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

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

Так что ознакомьтесь с кратким изложением курса в конце и, что лучше всего, изучите Excel 2013 самостоятельно.

Расширенный фильтр Excel — Полное руководство с примерами

Посмотреть видео — Расширенный фильтр Excel

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

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

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

Но сначала … Что такое расширенный фильтр Excel?

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

Вот некоторые различия между обычным фильтром и расширенным фильтром:

Хотя обычный фильтр данных будет фильтровать существующий набор данных, вы также можете использовать расширенный фильтр Excel для извлечения набора данных в другое место.
Расширенный фильтр Excel позволяет использовать сложные критерии. Например, если у вас есть данные о продажах, вы можете отфильтровать данные по критерию, где торговый представитель — Боб, а регион — Север или Юг (мы увидим, как это сделать в примерах).Служба поддержки Office может дать этому хорошее объяснение.
Вы можете использовать расширенный фильтр Excel для извлечения уникальных записей из ваших данных (подробнее об этом через секунду).

РАСШИРЕННЫЙ ФИЛЬТР EXCEL (примеры)

Теперь давайте рассмотрим некоторые примеры использования расширенного фильтра в Excel.

Пример 1. Извлечение уникального списка

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

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

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

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

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

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

Вот шаги, чтобы получить все уникальные записи:

Выберите весь набор данных (включая заголовки).
Перейти на вкладку «Данные» -> Сортировка и фильтр -> Дополнительно. (Вы также можете использовать сочетание клавиш — Alt + A + Q ).Это откроет диалоговое окно Advanced Filter.
В диалоговом окне «Расширенный фильтр» используйте следующие данные:
- Действие: Выберите параметр «Копировать в другое место». Это позволит вам указать место, где вы можете получить список уникальных записей.
- Диапазон списка: Убедитесь, что он относится к набору данных, из которого вы хотите найти уникальные записи. Также убедитесь, что в набор данных включены заголовки.
- Диапазон критериев: Оставьте это поле пустым.
- Копировать в: Укажите адрес ячейки, в которой вы хотите получить список уникальных записей.
- Копировать только уникальные записи: Установите этот флажок.
Щелкните OK.

Это мгновенно даст вам список всех уникальных записей.

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

Пример 2 — Использование критериев в расширенном фильтре Excel

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

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

Вот что я подразумеваю под сложными критериями. Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро получить все записи, где продажи превышают 5000, а регион — США.

Вот как можно использовать расширенный фильтр Excel для фильтрации записей на основе заданных критериев:

Первым шагом при использовании расширенного фильтра Excel со сложными критериями является указание критериев.Для этого скопируйте заголовки и вставьте их где-нибудь на листе.
Укажите критерии, по которым вы хотите фильтровать данные. В этом примере, поскольку мы хотим получить все записи для США с продажами более 5000, введите «США» в ячейку под регионом и> 5000 в ячейку под продажами. Теперь это будет использоваться в качестве входных данных в расширенном фильтре для получения отфильтрованных данных (как показано в следующих шагах).
Выберите весь набор данных (включая заголовки).
Перейти на вкладку «Данные» -> Сортировка и фильтр -> Дополнительно.Это откроет диалоговое окно Advanced Filter.
В диалоговом окне «Расширенный фильтр» используйте следующие данные:
- Действие: Выберите параметр «Копировать в другое место». Это позволит вам указать место, где вы можете получить список уникальных записей.
- Диапазон списка: Убедитесь, что он относится к набору данных, из которого вы хотите найти уникальные записи. Также убедитесь, что в набор данных включены заголовки.
- Диапазон критериев: Укажите критерии, которые мы создали на шагах выше.В этом примере это будет F1: I3.
- Копировать в: Укажите адрес ячейки, в которой вы хотите получить список уникальных записей.
- Копировать только уникальные записи: Установите этот флажок.
Щелкните OK.

Это мгновенно предоставит вам все записи, в которых регионом является США, а продажи превышают 5000.

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

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

Вот несколько примеров создания этих фильтров.

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

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

Например:

Для фильтрации записей, когда регион — США И торговый представитель — Джо.
Для фильтрации записей, когда регион — США И значение продаж превышает 5000.
Если регион — США, И продажи регистрируются после 31 марта 2017 г.

Использование критерия ИЛИ

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

Например:

Для фильтрации записей, когда регион — США ИЛИ регион — Азия.
Для фильтрации записей, когда торговым представителем является Боб ИЛИ Марта.

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

Пример 3 — Использование символов WILDCARD в расширенном фильтре в Excel

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

В Excel есть три подстановочных символа:

* (звездочка) — представляет любое количество символов. Например, ex * может означать excel, excels, example, expert и т. Д.
? (вопросительный знак) — представляет собой один единственный символ.Например, Tr? Mp может означать Трампа или Бродяги.
~ (тильда) — используется для обозначения подстановочного знака (~, *,?) В тексте.

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

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

Обратите внимание, что * представляет любое количество символов. Таким образом, любой представитель с именем, начинающимся с J, будет отфильтрован по этим критериям.

Аналогичным образом можно использовать два других подстановочных знака.

Примечание. Если вы используете Office 365, вам следует проверить функцию ФИЛЬТР. Он может делать много вещей, которые расширенный фильтр может делать с помощью простой формулы.

ПРИМЕЧАНИЕ:

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

Вам также могут понравиться следующие руководства по Excel:

Excel Advanced Filter Introduction

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

Создание расширенного фильтра Excel

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

Создание расширенного фильтра Excel

Вот шаги для настройки ваших данных и создания расширенного Фильтр.

— Настроить базу данных

— Настройка диапазона критериев (необязательно)

— Настройка диапазона извлечения (необязательно)

— применить расширенный фильтр Excel

1. Настроить базу данных

В первой строке (A1: D1) есть заголовки. Каждый столбец должен иметь уникальный заголовок — повторяющиеся заголовки вызовут проблемы при запуске расширенного Фильтр.
Последующие строки содержат данные.
В базе данных нет пустых строк.
В конце базы данных есть пустая строка и пустой столбец. справа.

2. Настройте диапазон критериев (необязательно)

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

ПРЕДУПРЕЖДЕНИЕ : при использовании текстовых критериев Excel интерпретирует их как «начинается с». Если вам нужно точное соответствие, см. Пример точного соответствия ниже.

В этом примере ячейки F1: F2 представляют собой диапазон критериев.
Заголовок в F1 точно соответствует заголовку (D1) в базе данных.
Ячейка F2 содержит критерий. Оператор> (больше) используется с номером 500 (без знака $).

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

Другие операторы:

< менее
<= меньше или равно
> = больше или равно
<> не равно

3.Настройте диапазон извлечения (необязательно)

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

ПРИМЕЧАНИЕ. Если вы хотите извлечь ВСЕ столбцы, вы можете оставить диапазон извлечения пустым для расширенного фильтра Excel.

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

4. Примените расширенный фильтр Excel

Выполните следующие действия, чтобы применить расширенный фильтр

ПРИМЕЧАНИЕ : Если вы хотите отправить данные на другой лист, выполните следующие действия: Отправить данные на другой лист

Выберите ячейку в базе данных.
На вкладке «Данные» ленты Excel щелкните «Дополнительно», чтобы открыть окно «Дополнительно». Диалоговое окно «Фильтр»

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

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

Нажмите ОК

Фильтр уникальных записей

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

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

Фильтр уникальных записей

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

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

Выберите ячейку в базе данных.
На вкладке «Данные» ленты Excel щелкните «Дополнительно».
В диалоговом окне «Расширенный фильтр» выберите «Копировать в другое место».
Для диапазона списка выберите столбцы, из которых вы хотите извлечь уникальные ценности.
Оставьте поле «Диапазон критериев» пустым.
Выберите начальную ячейку для копирования в местоположение.
Установите флажок в поле Только уникальные записи.
Щелкните OK.

Дубликаты в уникальных записях

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

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

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

Видео: удаление дубликатов

В Excel 2007 и более поздних версиях есть инструмент «Удалить дубликаты». В отличие от уникальных записей расширенного фильтра вариант, при этом исходный список не остается неизменным — он полностью удаляет все повторяющиеся элементы из списка. Только первый экземпляр каждый элемент остался.

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

Извлечь данные на другой лист

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

Извлечь данные на другой лист

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

Перейти к Sheet2 (см. Шаги в видео выше)
Выберите ячейку в неиспользуемой части листа (ячейка C4 в этой пример).
На вкладке «Данные» ленты Excel щелкните «Дополнительно»
Выберите Копировать в другое место.
Щелкните в поле диапазона списка
Выберите Sheet1 и выберите базу данных.
(необязательно) Щелкните в поле Диапазон критериев.
Выберите диапазон критериев
Щелкните в поле Копировать в.
Выберите ячейку на Листе 2, в которой вы хотите начать результаты, или выберите заголовки, которые вы ввели на Sheet2.
(необязательно) Установите флажок только для уникальных значений
Нажмите ОК

Настройка диапазона критериев

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

ПРЕДУПРЕЖДЕНИЕ : Когда вы вводите текстовый критерий, Excel интерпретирует его как «начинается с».Если вам нужно точное соответствие, см. Пример точного соответствия ниже.

Диапазон критериев Местоположение

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

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

ПРИМЕЧАНИЕ:

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

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

AND против OR

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

И Правило

В этом примере есть одна строка правил критериев:

имя покупателя должно начинаться с «MegaMart»
И название продукта должно начинаться с «Cookies»
И общая сумма должна быть больше 500

OR Правила

В этом примере есть 3 строки с правилами критериев.

Критерии в разных строках объединяются оператором ИЛИ. Во-вторых пример —

имя покупателя должно начинаться с «MegaMart»
ИЛИ название продукта должно начинаться с «Cookies»
ИЛИ общая сумма должна быть больше 500.

И / ИЛИ Правила

Используя несколько строк, вы можете комбинировать операторы AND и OR, как в этом примере:

имя клиента должно начинаться с «MegaMart» И название продукта должно начинаться с «Cookies».
должно начинаться с «Cookies» И общее количество должно быть больше 500.

Использование подстановочных знаков в критериях

Используйте подстановочные знаки для фильтрации текстовой строки в ячейке.

— Подстановочный знак *

— The? подстановочный знак

— Подстановочный знак ~

Подстановочный знак *

Подстановочный знак звездочка (*) представляет любое количество символов. в этой позиции, включая нулевые символы.

ПРИМЕЧАНИЕ : поскольку Excel интерпретирует текстовые критерии как «начинается с», вам не нужно добавлять подстановочный знак * в конце строки

В этом примере критерий клиента: * o * r

Два имени клиента, Fo o dMa r t и Mart- o — r ama, содержат букву «o», за которой следует буква «r» в любом месте после «o».Записи для этих двух клиентов появятся в отфильтрованном списке.

? подстановочный знак

Подстановочный знак вопросительного знака (?) Представляет один символ в эта позиция.

В этом примере любое имя продукта, которое начинается с c , за которым следует любой одиночный символ, а затем ke , будет проходить через расширенный фильтр Excel. Оба C o ke и C a ke находятся в отфильтрованных результатах.

Подстановочный знак ~

Подстановочный знак тильды (~) позволяет искать символы, которые используются как подстановочные знаки.

В первом примере звездочка находится в ячейке критериев — Good * Eats — поэтому любое имя клиента, которое начинается с Good, , затем любые символы, за которыми следует Eats , будет проходить через расширенный фильтр Excel.

Чтобы найти только имя клиента, начинающееся с Good * Eats , используйте тильду. символ ( ~ ) перед звездочкой в ячейке критерия: Good ~ * Eats

Примеры критериев

Вот еще пара примеров критериев расширенного фильтра:

— Извлечь элементы в диапазоне

— Извлечь элементы с определенным текстом

ПРИМЕЧАНИЕ : На этой странице есть другие примеры критериев: Расширенные фильтры — Комплексные критерии

Извлечь элементы в диапазоне

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

Если вы вводите два критерия в одной строке диапазона критериев, вы создать оператор AND. В этом примере все извлеченные записи должно быть:

больше или равно 500
меньше или равно к 750

Извлечь элементы с определенным текстом

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

Например, если вы введете в качестве критерия «Лед», Excel найдет «Ice», «Ice Cream» и «Ice Milk», потому что все они начинаются с «Ice».

Чтобы извлечь только те записи, которые точно соответствуют тексту критериев, используйте следующий формат в диапазоне критериев (верхний или нижний регистр не повлияет на результаты):
= «= ice»

Где должен быть диапазон критериев?

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

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

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

Примечание

Строки скрытых критериев не повлияют на расширенный фильтр — вы просто не сможете увидеть некоторые из диапазона критериев.

Получить образец книги

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

Инструкции по Excel 2003 см. В Excel. 2003 Расширенный фильтр Введение

Дополнительные уроки

Расширенные фильтры — сложные критерии

Продвинутый Фильтр критериев Слайсеры

Продвинутый Макросы фильтра

Excel Advanced Filter

Home »Basic-Excel» Excel-Advanced-Filter

Страница по теме:

Excel Autofilter

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

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

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

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

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

Приведенная выше электронная таблица справа была подготовлена для выполнения расширенного фильтра Excel в диапазоне ячеек A5-D17 (list_range).

Критерии, которые будут использоваться для фильтра, перечислены в ячейках B1-D3 (диапазон_критериев).

Первая строка диапазона критериев — это строка заголовка, а фактические критерии перечислены ниже.

Критерии, перечисленные в одной строке, связаны оператором «И», а критерии, перечисленные в разных строках, связаны оператором «ИЛИ».Следовательно, критерии в примере электронной таблицы преобразуются в условие:

(Maths%> = 60 AND Science%> = 60 AND English%> = 60) OR Maths%> = 80

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

В текущих версиях Excel (Excel 2007 и более поздних версий:

) Выберите вкладку Data в верхней части электронной таблицы и выберите Advanced в параметрах фильтра.

В Excel 2003:

Выберите меню D ata в верхней части таблицы и выберите в нем параметр F , фильтр , а затем параметр A расширенный фильтр. … .

При этом открывается диалоговое окно расширенного фильтра Excel (см. Справа вверху).

Для приведенного выше примера электронной таблицы данные находятся на листе с именем «Лист1». Следовательно, поле L ist range: в диалоговом окне следует вводить как Sheet1! A5: D17, а диапазон C riteria следует вводить как Sheet1! B1: D3.

Если вы хотите отобразить список на месте, просто нажмите OK. Однако, если вы хотите скопировать результат фильтра в новое место, это также можно указать на этом этапе.

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

(Maths%> = 60 AND Science%> = 60 AND English%> = 60) OR Maths%> = 80

Filtering Text Values

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

= «= текст»	Выберите ячейки, содержимое которых точно равно строка «текст»
<> текст	Выберите ячейки, содержимое которых не равно строка «текст»
текст	Выберите ячейки, содержимое которых начинается с строка «текст»
> текст	Выберите ячейки, содержимое которых упорядоченный (в алфавитном порядке) после строка «текст»
* текст *	Выберите ячейки, содержимое которых содержит строку «текст»
текст * текст	Выберите c элементы, содержимое которых начинается с строка «текст» И содержат второе вхождение строка «текст»
= «= текст * текст»	Выберите ячейки, содержимое которых начинается с строка «текст «И заканчиваются на строка» текст «
? Текст	Выберите ячейки, содержимое которых начинается с любого одиночного символа, за которым следует строка» текст «
=» = текст? Текст «	Выберите ячейки, содержимое которых начинается с строки «текст» И заканчивается строкой «текст» И содержит ровно один символ между этими двумя строками
= «= ???»	Выберите ячейки, содержимое которых содержит ровно 3 символа

В приведенной выше таблице использовались два подстановочных знака.Это:

* символизирует любое количество символов
? символизирует любой одиночный символ.

Также обратите внимание, что фильтры Excel не чувствительны к регистру , поэтому, например, фильтр на основе строки «текст» возвращает точно такой же результат, как фильтр на основе строки «ТЕКСТ».

Полезное видео, показывающее использование расширенного фильтра Excel, доступно на веб-сайте Microsoft Office.

Вернуться к Basic Excel Страница
Вернуться к ExcelFunctions.net Домашняя страница

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

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

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

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

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

Пример # 1

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

Нам нужно проверить транзакцию продажи, совершенную с помощью «Таран», и «Суреш». Затем мы можем использовать оператор ИЛИ, отображая записи, удовлетворяющие любым условиям. Чтобы получить результаты, мы можем выполнить шаги, чтобы применить эти фильтры в Excel.

В расширенном фильтре есть два действия.

Фильтровать список на месте : этот параметр фильтрует список в исходном месте, то есть в самом диапазоне списка. После анализа мы можем удалить фильтр с помощью команды «Очистить» в группе «Сортировка и фильтр» в группе «Данные».

Копировать в другое место : эта опция копирует желаемые данные согласно критериям в указанном диапазоне.

Мы можем использовать любой из вариантов в соответствии с нашими потребностями, но мы будем чаще использовать вариант 2 ^и.

Теперь нам нужно

Открыть диалоговое окно «Расширенный фильтр»

Указание диапазона списка как $ A $ 5: $ D $ 26, диапазон критериев как $ A $ 1: $ D $ 3 и « Копировать в» Диапазон как $ F $ 5: $ I $ 26. Нажмите «ОК».

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

Пример # 2

Теперь мы хотим получить все сделки по продаже за 1 квартал и Южную Индию. Диапазон критериев:

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

Теперь мы нажмем на «Дополнительно» команду в группе «Сортировка и фильтр» на вкладке «Данные» .

В диалоговом окне « Advanced Filter » мы выберем «Copy to another location» , а затем определим A5: D26 как List Range , A1: D2 как Criteria Range, и F5: I26 как « Copy To» range.

Теперь результат такой:

Пример # 3

Теперь мы хотим найти продажи в 1 квартале или сделанные в Северной Индии.

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

Шагов:

Необходимо открыть диалоговое окно «Расширенный фильтр» .

Укажите диапазон списка как $ A $ 5: $ D $ 26

Укажите диапазон критериев как $ A $ 1: $ D $ 3

Укажите диапазон как $ F $ 5: $ I $ 26

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

Пример # 4

Теперь мы хотим найти все продажи рупий.2000-4000 и рупий. 10000-13000.

Так как у нас есть четыре условия (Условие 1 И Условие 2) ИЛИ (Условие 3 И Условие 4).

(> = 2000 И <= 4000) ИЛИ (> = 10000 И <= 13000)

Вот почему мы упомянули условия с « И» в одной строке и Условия с «ИЛИ» в разных строках.

Шагов:

Чтобы открыть диалоговое окно «Расширенный фильтр» , мы нажмем на «Расширенный» в группе «Сортировка и фильтр» в группе «Данные.’

В диалоговом окне «Расширенный фильтр» мы укажем
Диапазон списка как $ A $ 5: $ D $ 26

Диапазон критериев как $ A $ 1: $ D $ 3

‘Копировать в’ Диапазон как $ F $ 5: $ I $ 26

После нажатия «ОК». Результат будет:

Пример № 5

Теперь мы хотим найти продажи за 1 квартал у Санни или за 3 квартал у Мукеша.

Поскольку у нас есть И и ИЛИ , оба типа отношений в условиях, поэтому мы будем указывать условия в диапазоне критериев в разных строках (ИЛИ) и в разных столбцах (И).

Шагов:

Чтобы открыть диалоговое окно «Расширенный фильтр» , щелкните «Расширенный» в группе «Сортировка и фильтр» в группе «Данные ».

В расширенном фильтре ‘, мы укажем
Диапазон списка как $ A $ 5: $ D $ 26

Диапазон критериев как $ A $ 1: $ D $ 3

‘ Копировать в ‘ Диапазон как $ F $ 5: $ I $ 26

После нажатия кнопки ОК результатом будет

Пример № 6 — Использование символов WILDCARD

Мы хотим найти все транзакции продаж, имена которых заканчиваются на «esh» или первое слово региона, заканчивающееся на «st», и хотим получить только имя, продажи и регион.

Здесь * обозначает более одного символа, а

«?» Обозначает только один символ.

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

Теперь вызовем команду.

Шагов:

Чтобы открыть диалоговое окно «Расширенный фильтр» , мы нажмем на «Расширенный» в группе «Сортировка и фильтр» в группе «Данные.’

В диалоговом окне «Расширенный фильтр» мы укажем
Диапазон списка как $ A $ 5: $ D $ 26

Диапазон критериев как $ A $ 1: $ D $ 3

‘Копировать в’ Диапазон как $ F $ 5: $ H $ 26

После нажатия « ОК». Результатом будет:

Пример № 7

Теперь мы хотим отфильтровать пять лучших продаж (с большим объемом).

Ячейка формулы должна оценивать ИСТИНА, или ЛОЖЬ, так как мы хотим получить пять наибольших записей. Вот почему мы использовали функцию Excel LARGE и сравнили значение с суммой продаж .

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

Теперь мы укажем диапазоны в диалоговом окне «Расширенный фильтр» .Шаги:

Чтобы открыть диалоговое окно «Расширенный фильтр» , щелкните «Дополнительно» в группе «Сортировка и фильтр» в группе «Данные ». Фильтр ‘, мы укажем
Диапазон списка как $ A $ 5: $ D $ 26

Диапазон критериев как $ A $ 1: $ E $ 2

‘ Копировать to ‘ Диапазон как $ F $ 5: $ I $ 26

После нажатия OK. Результат будет таким:

Что нужно запомнить

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

Операторы общих критериев
=	= «MN»	Находит записи, равные MN.
	«МН»	Находит записи, не равные MN.
		Находит записи меньше 10.
		Находит записи меньше или равные 10.
>	> 10	Находит записи больше 10.
> =	> = 10 И 5	Находит записи больше или равные 10, но не равные 5.
МЕЖДУ	МЕЖДУ 01.01.17 И 31.12.17	Находит записи между 01.01.17 и 31.12.17.
НРАВИТСЯ	КАК «S *»	Находит текст, начинающийся с буквы «S.Вы можете использовать LIKE с подстановочными знаками, такими как *.