Расширенный фильтр в excel 2010 как сделать

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

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

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

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

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

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

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

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

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

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

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

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

Как удалить

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать ранжированный ряд в excel?

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Сводная таблица из нескольких листов в excel как сделать

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

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

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

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

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

Применение расширенного фильтра в Excel

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

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

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

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

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

  1. Первым делом потребуется создать вторую вспомогательную таблицу с условиями фильтра. Для ее создания необходимо скопировать шапку исходной таблицы и вставить во вспомогательную.
    Для наглядности поместим дополнительную таблицу сверху, рядом с основной. А также пометим ее, залив другим цветом (это делается для большей наглядности и не обязательно). Вспомогательную таблицу можно поместить абсолютно в любом месте документа, причем, вовсе не обязательно, чтобы она была на том же самом листе, где располагается основная таблица.
  2. Затем приступим к заполнению вспомогательной таблицы данными, которые потребуются для работы. А нужны нам значения из основной таблицы, по которым необходимо отфильтровать данные. В нашем случае, мы хотим отобрать информацию по женскому полу и виду спорта – теннис.
  3. Когда вспомогательная таблица готова, можно приступать к следующему шагу. Поместив курсор на любую ячейку начальной или вспомогательной таблицы, в основном меню программы кликните по вкладке «Данные», выберите из открывшегося блока инструментов “Фильтр” пункт “Дополнительно”.
  4. В результате должно появиться окно с настройками расширенного фильтра.
  5. У данной функции есть два варианта применения: «Скопировать результаты в другое место» и «Фильтровать список на месте».Как можно понять из названий, эти опции отвечают за то, каким образом будет выводится отфильтрованная информация. В первом варианте данные будут выведены отдельно в указанном вами месте документа. Во втором – непосредственно в исходной таблице. Выбираем подходящий вариант (в нашем случае оставляем фильтрацию на месте) и двигаемся дальше.
  6. В поле «Диапазон списка» необходимо указать координаты таблицы (вместе с шапкой). Выполнить это можно, прописав их вручную, либо простым выделением таблицы мышью, щелкнув по небольшой пиктограмме в конце поля для ввода координат. В строке «Диапазон условий» таким же образом указываем координаты вспомогательной таблицы (заголовок и строку с условиями).Хотим обратить внимание на одну немаловажную деталь. Следите за тем, чтобы в выделяемую область не попали пустые ячейки. В противном случае, ничего не получится. По завершении подтвердите указанные координаты нажатием «OK».
  7. В результате проделанных действий в исходной таблице останутся только требуемые нам данные.
  8. Если мы выберем вариант «Скопировать результаты в другое место», то результат будет выведен в указанное нами место, а исходная таблица останется без изменений. При этом в строке «Поместить результат в диапазон» от нас требуется ввести координаты для вывода результата. Можно указать лишь одну ячейку, которая будет самой верхней левой координатой для новой таблицы. В данном случае, выбранная ячейка – A42.
  9. После нажатия кнопки “OK” новая таблица с заданными параметрами фильтрации будет вставлена, начиная с ячейки A42.

Как отменить расширенную фильтрацию

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

Заключение

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

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

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

Читать еще:  Как сделать шрифт по умолчанию в excel 2016?

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

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

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

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

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

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

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

2. Создавая таблицу критериев отбора расширенного фильтра, рекомендую вначале действовать шаблонно, не задумываясь о задачах, которые предстоит решать. Копируем все заголовки столбцов-полей из ячеек A7…F7 в ячейки A1…F1 – заготовка для таблицы критериев готова! Можно приступать к основной работе.

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

Продолжим изучать расширенный фильтр в Excel, решая практические задачи на примере работы с базой данных БД2 «Выпуск металлоконструкций участком №2».

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

1. Записываем параметр фильтрации – слово «балка» в столбце «Изделие» верхней таблицы критериев отбора.

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

3. Включаем расширенный фильтр в Excel 2003 через главное меню программы. Выбираем: «Данные» — «Фильтр» — «Расширенный фильтр».

4. В выпавшем окне «Расширенный фильтр» заполняем окна так, как показано на снимке экрана, расположенном ниже этого текста.

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

5. Результат работы расширенного фильтра – на следующем снимке экрана. Расширенный фильтр показал все записи базы данных, которые содержат слово «балка» в столбце «Изделие» — задача выполнена.

Регистр букв не влияет на результаты фильтрации!

Правила совместной «работы» нескольких условий отбора

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

Но главных правил — всего два! Все остальные случаи — различные сочетания этих двух правил.

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

2. Условия отбора, размещенные на разных строках в одном или нескольких столбцах таблицы критериев, предписывают расширенному фильтру показывать все строки, для которых выполняется хотя бы одно из этих условий. (Параметры отбора соединены логическими операторами «ИЛИ».)

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

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

Отфильтровать информацию о пластинах массой менее 0,1 тонны по всей базе.

В результате работы расширенного фильтра показаны все пластины базы данных, имеющие массу менее 0,1 тонны (иллюстрация правила №1).

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

Задача решена – показаны записи обо всех пластинах базы данных и обо всех изделиях тяжелее 1 тонны (иллюстрация правила №2).

Для отмены действия расширенного фильтра необходимо выполнить команду главного меню программы Excel «Данные» — «Фильтр» — «Отобразить все».

Обращаю внимание на необходимость внимательного контроля корректности указания исходного диапазона базы данных и диапазона таблицы условий в выпадающем диалоговом окне «Расширенный фильтр»!

В частности, в последнем примере необходимо указать: «Диапазон условий: $A$1:$F$3»!

Если в диапазоне условий или в вашей базе окажутся полностью пустые строки, то расширенный фильтр работать не будет!

При вводе условий поиска можно использовать общепринятые знаки подстановки и математические знаки:

  • * — любое количество любых символов
  • ? – один любой символ
  • = — равно
  • — больше
  • = — больше или равно
  • <> — не равно

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

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

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

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

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

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

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