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

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

Уровень мастерства: средний

image

Содержание

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

Почему метод автофильтрации такой сложный?

Этот пост был вдохновлен вопросом от Криса, участника The VBA Pro Course. Комбинации Критерии и Операторы могут быть запутанными и сложными. Почему это?

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

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

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

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

Содержание

Функционал фильтрации и сортировки в Microsoft Excel

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

image

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

Это не всегда удобно т.к. часто нужно компактное размещение таблицы на листе для вывода на печать. И если вы не будете пользоваться фильтрацией, чтобы эти кнопки не занимали место в названиях таблицы и не отвлекали внимание, их можно убрать. Делаем клик на любой ячейке таблицы, идём в меню «Данные», жмём «Фильтр». И всё – кнопки фильтрации и сортировки убраны из таблицы. Этой же кнопкой «Фильтр» мы можем при необходимости вернуть интеграцию фильтрации и сортировки в таблицу. Либо же можно реализовать её для простой неформатированной таблицы. Если вам необходимо только сортировать данные таблицы, можно сделать это с помощью кнопок для сортировки в меню MS Excel «Данные».

Или в главном меню, всё это один и тот же функционал. Здесь также есть кнопка «Фильтр» для включения/отключения интеграции фильтрации и сортировки в таблицу.

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

Сортировка данных в Microsoft Excel

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

Бóльшие возможности может предложить функция настраиваемой сортировки. Нажмём на данную кнопку.

И здесь мы в качестве критериев для сортировки можем выбрать :

  • «Столбец» — тип данных таблицы, т.е. столбцы с названиями данных нашей таблицы ;
  • «Сортировка» – значения ячеек, выделения цветным маркером или шрифтом, условное форматирование ;
  • «Порядок сортировки» – убывание или возрастание.

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

И вот: отмеченные маркером позиции таблицы находятся в самом верху.

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

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

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

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

И укажем цифру, скажем, 100. Т.е. мы хотим скрыть товарные позиции с сумами меньше 100 руб. Вписываем данную цифру или можем выбрать варианты сумм из выпадающего списка фильтра. Жмём «Ок».

И всё: позиции таблицы с сумами менее 100 руб. теперь скрыты. Чтобы вернуть их назад, удаляем фильтр в этом же столбце.

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

И укажем слово-фильтр. Например, мы хотим видеть в таблице только позиции по кофе. Вписываем слово «кофе», жмём «Ок».

И таблица отфильтрована по этому слову в названиях товаров.

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

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

1Организация работы фильтра в Excel – установка сортировки

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

2Организация работы фильтра в Excel – параметры фильтрации

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

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

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

3Организация работы фильтра в Excel – расширенная фильтрация

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

  • перейти во вкладку данных;
  • выбрать блок сортировки и фильтров;
  • кликнуть меню “Дополнительно”.

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

9-12-2014, 10:03Microsoft Office / Office 2010

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

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

В строке с заголовками нашей таблицы в каждом столбце появятся В«стрелкиВ».

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

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

Основы работы с фильтрами

Применение фильтров к таблице

Щелкните на значкеВ в столбце В«МенеджерВ». Откроется следующее меню:

В данном меню с помощью флажков Вы можете отмечать те элементы, по которым необходимо фильтровать данные.

Совет 1

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

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

Совет 2

Например, Вам нужно отметить только 2 значения из нескольких десятков. Снимать флажок вручную с каждой позиции кроме нужных достаточно затратно по времени. Для ускорения этого процесса снимите флажок с пункта В«(Выделить все)В». При этом снимутся все остальные флажки. Теперь можно отметить только те пункты, которые Вам нужны.

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

Напр., Вам необходимо найти все заказы менеджера Иванова от 18.01.2014. Для начала щелкните наВ в столбце В«МенеджерВ» и выберите Иванова.

Теперь щелкните наВ в столбце В«Дата отгрузкиВ», снимите флажок с В«(Выделить все)В» и выберите 18.01.2014 или введите в строке поиска 18 и нажмите В«ОКВ».

Таблица примет следующий вид:

Аналогичным образом Вы можете продолжить фильтровать данные по столбцам В«ОписаниеВ», В«Кол-воВ» и т.д.

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

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

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

Если необходимо снять фильтр только с одного столбца, оставив фильтры по другим, то щелкните на значкеВ данного столбца, напр., В«Дата отгрузкиВ» и щелкните на пункте В«Удалить фильтр с <Название столбца>В»:

илиВ 

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

До

После

Дополнительные настройки фильтров

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

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

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

  • 1. УсловияВ В«равно» илиВ В«не равно» предполагает , что искомое выражение стопроцентно совпадает с содержанием ячейки. КритерийВ В«равно» оставляет в таблице только те строки, в которых содержится выбранное значение. Соответственно, критерийВ В«не равно» оставляет все значения, кроме выбранного. Для упрощения задачи Вы можете выбрать нужное значение из выпадающего списка:
  • 2. УсловияВ В«больше» иВ В«меньше» предполагают, что в таблице останутся значения, которые по алфавиту начинаются с более ранней или более поздней буквы. Напр., если выбрать значение В«ИвановВ» при опции В«большеВ», то в таблице останутся только те ячейки, которые начинаются на букву В«ЙВ»(Картов, Йогуртов и т.п.), и, соответственно, при опции В«меньшеВ» — значения на букву В«ЗВ» (Захаров, Букин).
  • 3. Единственное отличие условийВ В«больше или равно» иВ В«меньше или равно» от предыдущего пункта в том, что в фильтр включается и выбранное значение.
  • 4. Если необходимо найти все значения, которые начинаются наВ В«ИваВ», то используйте условиеВ«начинается сВ», а если хотите узнать, сколько в таблице значений, оканчивающихся наВ В«ровичВ», то выберите опциюВ В«заканчивается наВ».
  • 5. Соответственно, условияВ В«не начинается с» иВ В«не заканчивается на» предполагают, что Вам не надо отображать в таблице значения, содержащие искомую фразу.
  • 6. При выборе условийВ В«содержит» илиВ В«не содержит» можно указать любую фразу или сочетание букв, которые необходимо включить или исключить из фильтра. Отличие этого пункта от пунктов 1, 4 и 5, в том, что искомая фраза может находится в любом месте ячейки. Например, задав в качестве фильтра В«ИваВ», в результате получим В«Иванов АлексейВ», В«Сергей ИваровскийВ», В«криваяВ» и т.п.

Числовые фильтры

Большинство условий те же самые, что и при текстовых фильтрах. Рассмотрим только новые.

  • 1. УсловиеВ В«междуВ». При выборе данного условия, в появившемся окне сразу же устанавливаются нужные критерии, что облегчает Вашу задачу:
  • 2. УсловиеВ В«Первые 10В». Данный пункт имеет следующие опции:
    • Показать наименьшие или наибольшие значения.
    • Сколько значений отобразить.
    • В данном пункте требуется пояснение по второму значению: % от количества элементов. Например, у Вас в таблице 15 строк с числовыми значениями. При выборе 20% в таблице останется только 15/100*20 = 3 строки.
  • 3. При выборе условийВ В«Выше среднего» илиВ В«Ниже среднего» Excel автоматически высчитывает среднее арифметическое значение в столбце и затем фильтрует данные согласно критерию.

Фильтр по дате

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

И еще немного о фильтрах

Есть еще один способ фильтрации данных. Немного преобразуем нашу таблицу:

Как видите, мы ее раскрасили.

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

Если выбрать условиеВ В«Фильтр по цвету…», то в таблице останутся строки с ячейками того же цвета, что и активная ячейка (желтая заливка).

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

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

1

4020 Версия для печати

Рубрика Excel

Также статьи о работе с данными в Экселе:

  • Как пользоваться расширенным фильтром в Excel?
  • Как объединить данные двух ячеек в Excel?
  • Проверка вводимых данных в Excel
  • Как сделать выпадающий список в Excel?

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

image

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

image

image

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

image

image

image

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

image

image

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

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

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

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

Оцените статью
Рейтинг автора
4,8
Материал подготовил
Максим Коновалов
Наш эксперт
Написано статей
127
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий