Как сделать числовой фильтр в excel

Добавил пользователь Владимир З.
Обновлено: 04.09.2024

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

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

Рис. 5.4. Установка фильтра

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

Рис. 5.5. Выбор текстового фильтра

Если вам, например, нужно выбрать в списке только имена, начинающиеся на букву С, щелкните на кнопке в столбце с именами, в появившемся меню раскройте подменю Текстовые фильтры и выберите пункт начинается с. Откроется окно, показанное на рис. 5.6. В поле справа от слов начинается с введите букву С. Можно ввести не одну букву, а несколько. В результате будут выбраны только те имена, которые начинаются с указанного сочетания букв, а остальные записи таблицы скроются с экрана (рис. 5.7).

Рис. 5.6. Параметры текстового фильтра

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

Рис. 5.7. Столбец с установленным фильтром

Для рассматриваемого нами примера (см. рис. 5.4), выберем список людей, родившихся после 1973 года. Для этого щелкнем мышью на кнопке в ячейке Е1 (Год рождения), в появившемся меню раскроем подменю Числовые фильтры, как показано на рис. 5.8, и выберем пункт больше. Откроется окно, показанное на рис. 5.9. В поле справа от слова больше введем значение 1973 и щелкнем мышью на кнопке OK. В результате в списке останутся только люди, родившиеся после 1973 года (рис. 5.10).

Рис. 5.8. Выбор числового фильтра

Рис. 5.9. Параметры числового фильтра

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

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

Для этого откроем эксель и набросаем в нем небольшую таблицу.

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

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

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

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

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

Совет 1

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

Совет 2

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

Например, Вам необходимо найти все заказы менеджера Иванова от 18.01.2014.

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

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

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

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

или

После

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

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

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

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

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

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

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

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

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

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

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

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

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

Основа

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

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

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

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

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

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

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

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

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

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

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

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

Критерий	Результат
гр* или гр	все ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д.
=лук	все ячейки именно и только со словом Лук, т.е. точное совпадение
лив или *лив	ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д.
=п*в	слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д.
а*с	слова начинающиеся с А и содержащие далее С , т.е. Апельсин, Ананас, Асаи и т.д.
=*с	слова оканчивающиеся на С
=.	все ячейки с текстом из 4 символов (букв или цифр, включая пробелы)
=м. н	все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостин и т.д.
=*н??а	все слова оканчивающиеся на А , где 4-я с конца буква Н , т.е. Брусника, Заноза и т.д.
>=э	все слова, начинающиеся с Э , Ю или Я
<>о	все слова, не содержащие букву О
<>*вич	все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству)
=	все пустые ячейки
<>	все непустые ячейки
>=5000	все ячейки со значением больше или равно 5000
5 или =5	все ячейки со значением 5
>=3/18/2013	все ячейки с датой позже 18 марта 2013 (включительно)

Знак * подразумевает под собой любое количество любых символов, а ? - один любой символ.
Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).

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

Условия записанные в разных ячейках, но в одной строке - считаются связанными между собой логическим оператором И (AND) :

Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".

Если нужно связать условия логическим оператором ИЛИ (OR) , то их надо просто вводить в разные строки. Например, если нам нужно найти все заказы менеджера Волиной по московским персикам и все заказы по луку в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:

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

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

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

В Excel предусмотрено три типа фильтров:

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

Автофильтр

Выделить одну ячейку из диапазона данных.
На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
Щелкнуть по кнопке Фильтр [Filter] .

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

Выбрать условие фильтрации.

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

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

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

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

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

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

Срезы

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

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

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

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

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

Форматирование срезов

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

Выбрать кнопку с подходящим стилем форматирования.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: