Фильтрация данных: операции, назначение, примеры

Содержащаяся в электронных таблицах и БД информация не всегда необходима пользователю в каждый момент его работы в полном объеме. Поэтому в приложениях MS Office предусмотрены инструменты, позволяющие представить информацию в подходящем для обработки виде. В статье ознакомимся с некоторыми из них.

Что это такое и для чего

Один из таких инструментов – фильтрация данных. Ее используют для того, чтобы из всего массива отобрать только те записи, что соответствуют необходимым условиям. При этом вся информация объекта сохраняется, но человек видит только необходимые ему строки, остальные же, не удовлетворяющие заданным условиям, скрываются.

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

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

Excel

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

Простейший способ селекции – инструмент панели «Редактирование» вкладки «ГЛАВНАЯ». Выделяем ячейку столбца, в выпадающем списке кнопки «Сортировка и фильтр» выбираем пункт меню «Фильтр». При этом в первой ячейке столбца появится кнопка со стрелочкой, при нажатии на которую открывается выпадающее меню инструментов для сортировки и фильтрации данных в Excel.

Здесь определяются параметры выбора строк для отображения.

Выбор по значениям

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

Можно еще более сократить список продукции, отобрав, к примеру, только изделия производителя «MAX FACTOR» и получив в результате лист средств для ногтей этого производителя.

Выбор по условию

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

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

Здесь задается несколько условий. Все они могут быть обязательны к выполнению, либо достаточно удовлетворения только некоторых из них (параметры «И» и «ИЛИ»). В построителе предусмотрена возможность установки условий для различных типов: текстовых, числовых, даты и времени, логических и т.д.

Удобным является такой способ фильтрации данных в MS Excel для числовых полей. Допустим, в нашем прайс-листе мы захотим найти средства для губ со стоимостью до 300-т рублей. В столбце «Тип косметики» выбираем флажками две позиции: «ПОМАДА» и «БЛЕСК ДЛЯ ГУБ».

В столбце «Цена» выбираем позицию «Числовые фильтры», в отличие от текстовых в столбце с типом косметики.

Выбираем из вариантов пункт «меньше или равно» и в открывшемся окне параметров выставляем сумму 300 руб. После нажатия на кнопку «ОК» получаем список из четырех средств типов «Помада» и «Блеск для губ» и стоимостью менее 300 рублей.

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

Сортировка и поиск информации

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

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

Числовые поля сортируются по возрастанию и убыванию. Следует быть внимательными в случае, если числа внесены в столбец как текст. В этом случае они будут упорядочены как текст, то есть, числа 10, 11, 100, 12, 110 будут идти в таком порядке в зависимости от формата полей:

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

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

БД Access

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

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

Способы

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

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

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

Запросы для отбора данных

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

В запросах можно использовать поля нескольких таблиц, добавив условия для получения данных по каждому из них. Эти объекты БД создаются с помощью Конструктора, мастера запросов или редактора SQL.

Запросы SQL на выборку имеют структуру вида

Select [список полей]

From [таблицы]

Where [условия]

Здесь в блоке условий задаются параметры, по которым выбираются записи. Для примера составим запрос для выбора записей из таблицы “Товары по складам”. Пусть нам необходимо выбрать строки с вазами под наименованием «Восторг» и посмотреть, сколько товара с таким названием есть на каждом из складов.

SELECT [Товары по складам].[Тип товара], Вазы.Ваза, [Товары по складам].Склад, [Товары по складам].Количество

FROM Вазы INNER JOIN [Товары по складам] ON Вазы.Код = [Товары по складам].Наименование

WHERE (([Товары по складам].[Тип товара]="Ваза") AND ([Вазы].[Ваза]="Восторг"));

Результат будет выглядеть так:

В режиме конструктора запрос имеет вид:

Сохранение и последующее применение

Иногда возникает необходимость многократного использования однажды установленного отбора или его автоматического применения при запуске таблицы или формы. Сохранение условий происходит автоматически при закрытии объекта. При последующем его открытии отображаются все записи, и для того, чтобы снова применить сохраненное ограничение, нужно нажать кнопку «Фильтр» панели «Сортировка и фильтр».

Для автоматического применения нужного отбора воспользуйтесь свойством объекта «Фильтр при загрузке», установив его в значение «Да»

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

Поиск и выстраивание списка в Access

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

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

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