Excel фильтр по значению в ячейке - Учим Эксель

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

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

advanced-filter1.png

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

База

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

advanced-filter2.png

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

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

advanced-filter3.png

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

advanced-filter5.png

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

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

advanced-filter6.png

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

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

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

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

Так все еще лучше, правда? 🙂

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

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

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

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

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

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

advanced-filter3.png

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

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

advanced-filter7.png

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

advanced-filter8.png

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

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

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

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

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

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

Интересно почитать:  Excel как поставить enter в ячейке

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

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

Срезы

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

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

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

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

    Выделить в таблице одну ячейку и избрать вкладку Конструктор [Design].

Вставка среза в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сходу лицезреем итог:

Пример.

Индивидуальности работы инструмента:

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

У расширенного фильтра еще больше способностей:

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

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

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

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

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

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

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

Интересно почитать:  В excel размер ячейки по тексту

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

Параметры1.

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

Пример2.

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

Заполняем характеристики фильтрации. Жмем ОК.

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

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

Пример4.

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

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

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

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

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

Пример5.

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

Уникальные значения.

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

Как употреблять фильтр в MS Excel. Описание и примеры.

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

Как использовать фильтр в MS Excel. Описание и примеры.

Где отыскать фильтр ?

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

Где найти Фильтр ?

Либо употреблять сочетание кнопок Ctrl + Shift + L. В этом случае, перед тем как надавить сочетание кнопок, необходимо выделить спектр, к которому будет применён фильтр.

Направьте внимание.

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

Создаем фильтр.

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

Описание и примеры.

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

Как использовать фильтр в MS Excel. Описание и примеры.

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

Как использовать фильтр в MS Excel. Описание и примеры.

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

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

Ссылка на основную публикацию
Adblock
detector