Расширенный фильтр в excel диапазон условий - Учим Эксель

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

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

Для что это необходимо

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

Как работает

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

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

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

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

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

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

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

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

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

При помощи макроса

При помощи макроса

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

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

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

Этот код будет автоматом, применятся при любом изменении полей и накладывать фильтр на ваши данные. Он просматривает диапазон (C2:I2) на наличие введенных данных и одномоментно применяет условия, к спектру ваших данных, которые начинаются с ячейки (C5).

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

Вот предоставлены варианты:

Условие

Приобретенный итог

Отбор всех ячеек, где есть 5 знаков. (К примеру, Прага)

Отбор текста на 4 знака, который начинается с «в» и завершается на «а» (К примеру, Вена)

Отбор значений с 3-мя знаками в конце, одна из которых неведома (К примеру, София)

Отбор всех значений, которые начинаются с букв «пр» (К примеру, Прага)

Отбор значений, которые начинаются с «п», содержат либо завершаются на «р» (К примеру, Париж)

Отбор значений, которые завершаются на «н» (К примеру, Берлин)

Отбор четкого совпадения (К примеру, Рим)

Делается отбор всех пустых ячеек

Делается отбор всех не пустых ячеек

Отбор значений, которые равны либо больше 40

Четкий отбор значений, которые равны 20.

Отбор всех значений, которые начинаются с «р»

Отбор данных, которые содержат «монарх»

Отсев дат до 1 ноября 2016 года.

Видите ли эти условия, дозволят для вас сделать лучше способности поиска, а вместе с внедрением макроса для расширенного фильтра, так и совершенно станет все стремительно, тщательно и буквально.

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

Как добавить

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

Фильтр в Excel

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

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

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

Как удалить

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

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

Интересно почитать:  Знак диаметра в excel

Создаем диапазон условий

К примеру, есть таблица:

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

Оставьте пустую строчку отделяющую новейшую таблицу от начальной.

Прописываем условия. Выберем данные с «Товар3», и где «Стоимость за 1 ед» приравнивается 10.

Активируем фильтр

Перейдите:

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

Переместите курсор в «Диапазон условий». Отметьте шапку и строчки с критериями.

Нажмите «ОК».

Мы разглядели, где находится расширенный фильтр в Excel. Представим, что необходимо показать данные, где стоимость продукта больше 1 000 000. Как это создать? В критериях пропишите:

Нажмите «ОК».

Чтоб сбросить фильтр перейдите:

Для активации данной для нас функции в Excel 2010, сделайте подобные деяния.

Как задать несколько характеристик

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

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

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

Жмем «Ок» и получаем лишь продукты стоимостью 100 рублей, произведенные в Рф.

Как поставить расширенный поиск

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

Потом в вольной строке под скопированными заголовками задать нужные условия поиска. К примеру, нужно отыскать продукты, произведенные в Рф, проданные менеджером Ивановым, стоимостью наименее 300 рублей.

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

Перед юзером покажется окно, в котором ему предстоит заполнить две строчки:

  • «Начальный диапазон» — это диапазон таблицы, информация которой подлежит фильтрации, другими словами начальной таблицы. Его эксель введет автоматом;
  • «Диапазон условий» — это ячейки, из которых программка возьмет значения для отсева, — 2-ая таблица, которую мы сделали сверху. Чтоб значения возникли в строке окна, нужно просто захватить две ее строчки: с наименованием раздела и введенными значениями.

Опосля того как оба спектра сформированы, нажмите «Ок» и оцените итог.

Как удостовериться, поставлен ли фильтр

Чтоб выяснить, установлен поиск данных либо нет, нужно посмотреть на строчку с наименованием характеристик — это 1-ая строчка. Если он есть, в каждой ячейке строчки обнаружится маленький значок.

Значком «1» обозначается столбец, в котором поиск установлен, но не применяется. Значком «2» обозначен уже примененный поиск.

Вывод

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

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

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

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

Разглядим его применение для последующей таблицы. Размещена она в спектре ячеек А6:Е31. В ней представлена информация о учениках школы.

К примеру, необходимо избрать всех девченок, рост которых меньше 160 см. Это можно создать, применив обыденный фильтр для столбцов. Но если мы захотим отобрать и мальчишек с ростом больше 180 см, то с его помощью ничего создать у нас не получится. Будут выданы результаты с мальчуганами и девченками, рост которых находится в приделах от 160 до 180 см. Тут нам и понадобиться внедрение расширенного фильтра. Надеюсь, разница Для вас понятна.

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

Как применить

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

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

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

Раскроется диалоговое окно «Расширенный фильтр». В нем изберите маркером, где показать итог, в данной для нас же таблице либо создать ее в другом месте. В качестве «Начального спектра» избираем наши ячейки А6:Е31. «Диапазон условий» – это наши заглавия с критериями А1:Е2. Нажмите «ОК».

Весьма принципиально верно задать диапазон условий. В примере это А1:Е2. Если необходимо будет добавить очередное условие, он станет А1:Е3, и так дальше. В неприятном случае ничего работать не будет.

В спектре условий, данные для столбцов, которые введены в одну строчку, воспринимаются как логическое «И». Данные на различных строчках воспринимаются как логическое «ИЛИ». В примере, мы оставили всех девченок, и из их избрали тех, кто обучается в 9 классе. Если во 2-ой строке записать «девченка» – «>170», то будут отобраны из таблицы к тому же девченки, рост которых больше 170 см. При всем этом они могут обучаться в остальных классах – это логическое «ИЛИ».

Интересно почитать:  Как в excel убрать лишние пробелы

Как удалить

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

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

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

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

Маркером отмечаем «Скопировать итог в другое пространство», избираем ячейки для «Начального спектра» А6:Е31, в поле «Диапазон условий» вписываем адресок А1:Е3. В поле «Поместить итог в диапазон» жмем на кнопку выбора ячеек и выделяем нужные на листе, можно избрать и иной лист открытой книжки. Нажмите «ОК».

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

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

Сейчас, для доборной таблицы с отфильтрованными данными, можно применить, к примеру, сортировку в Excel. Тщательно о том, как отсортировать данные в Эксель, Вы сможете прочитать, перейдя по ссылке.

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

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

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

Что же это все-таки за функция? Описание

Что означает расширенный фильтр в Excel? Это функция, которая дозволяет разграничивать избранные данные (по столбцам в «Экселе») относительно введенных требований.

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

Например, если у нас есть электрическая таблица со сведениями обо всех учениках школы (рост, вес, класс, пол и т. п.), то мы с легкостью сможем выделить посреди их, скажем, всех мальчишек с ростом 160 из 8-го класса. Создать это можно, используя функцию «Расширенный фильтр» в Excel. О ней мы и будем детально говорить дальше.

Что означает автофильтр?

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

Как созодать верно?

ms excel

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

{Инструкция} по расширенной фильтрации электрической таблицы:

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

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

работа с расширенным фильтром

Работа с расширенным фильтром в «Экселе»

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

Для этого нужно:

  1. Расположить условия разграничения (=»-Самара») под предшествующим запросом (=»=Ростов на дону»).
  2. Вызвать меню расширенного фильтра (раздел «Данные», вкладка «Фильтрация и сортировка», избрать в ней «Добавочно»).
  3. Надавить Ок. Опосля этого расширенная фильтрация закроется в Excel. А на дисплее покажется готовая таблица, состоящая из записей, в которых указан город Самара либо Ростов на дону.

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

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

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

Расширенная фильтрация. Главные правила использования при работе «Экселе»

  • Аспектами отбора именуются результаты начальной формулы.
  • Результатом могут быть лишь два значения: «ИСТИНА» либо «ЛОЖЬ».
  • С помощью абсолютных ссылок указывается начальный диапазон фильтруемой таблицы.
  • В результатах формулы будут показаны лишь те строчки, которые получают по итогу значение «ИСТИНА». Значения строк, которые получили по итогу формулы «ЛОЖЬ», не будут высвечиваться.
Интересно почитать:  Средневзвешенное excel

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

Пример в «Экселе 2010»

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

{Инструкция} для работы с расширенным фильтром в Excel по среднему значению колонки:

  1. Для начала нужно избрать формулу среднего значения данных столбца. Для этого необходимо избрать диапазон результатов от исходной записи до конечной. Просуммировать их и поделить на количество строк (количество записей).
  2. Опосля этого избрать в разделе «Фильтрование и сортировка» пункт «Добавочно». Вписать туда нужные данные (по спектрам).
  3. Опосля этого надавить Ок. В итоге получится таблица, в которой есть записи, не превосходящие среднего значения по данному столбцу.

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

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

Внимание! При работе записи, которые не прошли аспект фильтрации, не удаляются. Они просто не показываются (не показываются). Расширенная фильтрация создана для того, чтоб можно было скопировать итог и вставить его куда-нибудь в другое пространство. Опосля этого нужно надавить на «Очистить» в разделе «Фильтрация и сортировка». Тогда ваша начальная таблица возвратится на экран.

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

Автофильтр. Пример использования

Автофильтр — это обыденный инструмент. Его можно применить, только задав четкие характеристики. К примеру, вывести все значения таблицы, которые превосходят значения 1000 (< 1000), либо показать четкие данные, как было рассмотрено в примере с городками.

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

пример фильтра excel 2010

Плюсы и минусы расширенного фильтра в программке «Эксель»

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

Плюсы расширенной фильтрации:

  • можно употреблять формулы.

Минусы расширенной фильтрации:

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

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

Фильтрация по двум отдельным аспектам. Как верно ее создать?

работа с расширенным фильтром в excel

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

  1. Сделать пространство для ввода параметра фильтрования. Удобнее всего оставлять это пространство над главный таблицей и не забывать копировать шапку (наименования столбцов), чтоб не запутаться, в какую колонку вводить этот аспект.
  2. Ввести подходящий показатель для фильтрации. К примеру, все записи, чьи значения столбца больше 1000 (> 1000).
  3. Пройти во вкладку «Данные». В разделе «Фильтрация и сортировка» избрать пункт «Добавочно».
  4. В открывшемся окошке указать спектры рассматриваемых значений и ячейку со значением рассматриваемого аспекта.
  5. Надавить на Ок. Опосля этого будет выведена отфильтрованная по данному аспекту таблица.
  6. Скопировать итог разграничения. Вставить отфильтрованную таблицу куда-нибудь в сторону на том же листе Excel. Можно пользоваться иной страничкой.
  7. Избрать «Очистить». Данная клавиша находится во вкладке «Данные» в разделе «Фильтрация и сортировка». Опосля ее нажатия отфильтрованная таблица возвратятся в начальный вид. И можно будет работать с ней.
  8. Дальше нужно опять выделить свободное пространство для таблицы, которая будет отфильтрована.
  9. Позже необходимо скопировать шапку (наименования столбцов) основного поля и перенести их в первую строку освобожденного под отфильтрованную структуру места.
  10. Пройти во вкладку «Данные». В разделе «Фильтрация и сортировка» избрать «Добавочно».
  11. В открывшемся окошке избрать диапазон записей (столбцов), по которому будет проводиться фильтрация.
  12. Добавить адресок ячейки, в которой записан аспект разграничения, к примеру, «город Одесса».
  13. Надавить на Ок. Опосля этого произойдет фильтрация по значению «Одесса».
  14. Скопировать отфильтрованную таблицу и вставить ее или на иной лист документа, или на той же страничке, но в стороне от главный.
  15. Опять надавить на «Очистить». Все, готово. Сейчас у вас имеются три таблицы. Основная, отфильтрованная по одному значению (>1000), также та, что отфильтрована по другому значению (Одесса).

пример расширенного фильтра

Маленькое заключение

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

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

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

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