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

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

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

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

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

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

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

Дополнительные сведения о фильтрации

Два типа фильтров

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

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

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

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

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

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

Не используйте смешанные типы данных

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

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

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

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

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

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

    На вкладке " данные " нажмите кнопку " Фильтр ".

Параметры фильтрации для таблиц и диапазонов

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

Выбрав параметр Числовые фильтры вы можете применить один из перечисленных ниже настраиваемых фильтров.


Включение автофильтра:

  1. Выделить одну ячейку из диапазона данных.
  2. На вкладке Данные найдите группу Сортировка и фильтр .
  3. Щелкнуть по кнопке Фильтр .

Фильтрация записей:

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


  1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно , больше , меньше , Первые 10… и др.
  2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит... , начинается с… и др.
  3. При выборе опции Фильтры по дате варианты фильтрации - завтра , на следующей неделе , в прошлом месяце и др.
  4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… , используя который можно задать одновременно два условия отбора, связанные отношением И - одновременное выполнение 2 условий, ИЛИ - выполнение хотя бы одного условия.

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

Отмена фильтрации

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

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы

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

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

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

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

  1. Выделить в таблице одну ячейку и выбрать вкладку Конструктор .
  2. В группе Сервис (или на вкладке Вставка в группе Фильтры ) выбрать кнопку Вставить срез .

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

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

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

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

Задание условий фильтрации

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

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

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

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

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

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

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

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

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

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

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

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 гибок и информативен при использовании. Критерии отбора всегда перед глазами пользователя в виде таблицы критериев отбора, что, несомненно, очень удобно.

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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


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

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

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



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

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

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

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



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

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

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

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



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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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