Выборка данных из списка с помощью автофильтра

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

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

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

Более широкие возможности для создания условий отбора записей из списка предоставляют появляющиеся также после нажатия кнопки со стрелкой команды Текстовые фильтры, Числовые фильтры, Фильтры по дате (вид команды зависит от типа данных в соответствующем поле списка). После выполнения любой из этих команд появляется меню, позволяющее задавать критерии отбора непосредственно (например, Выше среднего, Сегодня, С начала года) или открывать диалоговые окна для их ввода (например, равно…, между…, начинается с…). Универсальной является команда Настраиваемый фильтр…. На рисунке 3 изображено окно, открытое с помощью этой команды, в котором заданы условия выборки из списка сведений о квартирах с жилой площадью в диапазоне от 50 до 75 квадратных метров.

Рисунок 3 – Диалоговое окно автофильтра с заданными условиями отбора для жилой площади квартир

В условиях отбора для полей с текстовыми данными можно использовать подстановочные знаки * (последовательность любых символов) и? (один символ). Например, если ввести для поля Адрес таблицы, изображённой на рисунке 3, условие отбора в виде равно М*, из списка будут выбраны квартиры, расположенные на улицах Мира и Майской. Критерий содержит е?о, созданный в поле Адрес, позволяет найти квартиры, расположенные на улицах Чехова и Седова.

С помощью автофильтра из списка можно выбирать заданное количество максимальных или минимальных значений числовых данных. Для реализации этой возможности выполняются команды Числовые фильтры и Первые 10…, затем в диалоговом окне Наложение условия по списку вводятся необходимые критерии (рисунок 4).

Рисунок 4 – Диалоговое окно для выбора из списка двух квартир с наименьшей общей площадью (условие отбора определено для поля списка Общая площадь)

Команда Первые 10… позволяет получить не только количество искомых значений, но и их долю в процентах от общего числа элементов в анализируемом поле.

После создания и применения критериев для выборки данных размеры списка уменьшаются – в нём остаются только записи, удовлетворяющие условиям отбора. Критерии можно вводить друг за другом в нескольких полях списка, в результате в списке останутся только те записи, которые удовлетворяют всем введённым условиям отбора.

Для удаления критерия для выборки данных в некотором поле следует нажать кнопку со стрелкой в ячейке с заголовком этого поля и выполнить команду Снять фильтр или включить флажок (Выделить всё). Условия отбора, установленные в нескольких полях, надёжнее и проще удалить с помощью кнопки Очистить в группе Сортировка и фильтр на вкладке ленты Данные.

Для выключения автофильтра нужно нажать кнопку Фильтр в группе Сортировка и фильтр на вкладке ленты Данные.

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

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

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

Этап 1. Создаются заголовки диапазона условий – в пустые ячейки рабочего листа копируются заголовки полей списка (напоминаем, что ячейки с любыми данными на рабочем листе должны отделяться от списка пустыми строка-ми или столбцами). Можно скопировать заголовки всех полей списка или только тех полей, для которых планируется создавать условия отбора. Ниже заголовков диапазона условий необходимо оставить несколько пустых строк.

Этап 2. Под заголовками диапазона условий вводятся критерии для выборки нужных данных (правила их формирования будут рассмотрены далее).

Этап 3. Выполняется процедура фильтрации. Для этого следует нажать кнопку Дополнительно в группе Сортировка и фильтр вкладки ленты Данные, затем в диалоговом окне Расширенный фильтр (рисунок 5) установить параметры фильтрации.

Рисунок 5 – Диалоговое окно для ввода параметров фильтрации

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

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

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

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

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

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

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

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