- •Консолидация
- •Сортировка данных
- •Самостоятельно!
- •Транспонирование таблицы
- •Закрепление строк и столбцов
- •Сводные таблицы
- •Базы данных
- •Создание базы данных
- •Поиск записей по критериям в форме
- •Фильтрация данных
- •Автофильтр
- •Данные - Фильтр - Отобразить все
- •Расширенный фильтр
- •Функции базы данных
- •Выполнение. N
- •Функции поиска и связи таблиц
- •Контрольные задания
- •Литература
- •Оглавление
Расширенный фильтр
В ряде практических задач достаточно возможностей автофильтра. Но профессиональный пользователь не может игнорировать более богатые возможности расширенного фильтра.
Расширенный фильтрпозволяет:
сразу копироватьотфильтрованные записи в другое место рабочего листа;
сохранятькритерийотбора для дальнейшего использования при изменяемых данных в БД;
выводить отфильтрованные записи только с нужнымистолбцами;
использовать логические операции
ИЛИ для разных столбцов,
И и ИЛИ для одного столбца, объединяя более двух условий;
создавать вычисляемыекритерии;
выводить только уникальныезначения
Для работы расширенного фильтра обязательны две области: область данныхи областькритериев поиска.
Иногда возникает потребность в дополнительной области - области вывода результатовпоиска
Область данных- собственно база данных (список), сформированная по правилам (см. 7.1.).
Область критериев поиска формируется в свободном месте рабочего листа не менее, чем из двух строк:
строки заголовков полей базы данных, которые будут ключевыми при отборе записей,
строки или строка критериев отбора.
При создании критериев отбора следует учитывать правила:
заголовки критериев должны точно совпадатьс заголовками полей БД (столбцов), поэтому их следуеткопироватьиз базы данных в область критериев;
если критерии находятся в одной строке, то они работают по принципу логической операции И.
если в разных строках - по принципу операции ИЛИ.
в критериях можно применять шаблоны * и?.
критерии могут быть вычисляемымии состоять из формул и функций, аргументами которых являются поля БД. Создание вычисляемых критериев предусматривает своиправила:
заголовок критерия может бытьлюбым, но не заголовком столбца исходной базы данных (он может содержать любой текст);
если формула критерия содержит ссылки на ячейки внеБД, то они должны бытьабсолютными;
ссылки на ячейки внутрибазы данных должны бытьотносительнымии выбираться из первой строки данных (“правило первой строки”);
результатомвычисляемого критерия является логическая величина ИСТИНА или ЛОЖЬ.
Область выводарезультатов поиска формируется в свободном месте рабочего листа из имен только тех полей, которые необходимы в выходном документе (для соблюдения точности их следует скопировать из БД).
Количество строк в области вывода Excel определяет сам.
Примеры 7.3.2. Создание критериев расширенного фильтра. (Все примеры приведены на основе базы данных «Магазин» (рис.13))
Критерий по точному значению
Задание1.Вывести в отдельный список цены товаров кондитерского отдела.
Выполнение:
В свободное место на листе скопировать заголовок критериев поиска. В данном примере: Отдел.
Заполнить строку критериев.
В данном примере копированием точного значения из поля «Отдел» - Кондитерский.
Сформировать область вывода результатов поиска -
скопировать в свободное место на листе заголовки интересующих в результате отбора полей.
В данном примере: Отдел, Наименование товара, Цена расхода.
Примечание. Заголовок «Отдел» выводить по заданию не обязательно, но для проверки работы фильтра – полезно.
Выполнить команду:
Данные - Фильтр - Расширенный фильтр.
В диалоговом окне команды задатьпараметры:
в поле Обработкавключить кнопку «Скопировать результат в другое место»,
Примечание. Переключатель «фильтровать список на месте» изменяет вид исходной БД (как в автофильтре).
исходный диапазон- указать курсором область базы данных (включая первую строку заголовков);
диапазон критериев- указать курсором область критериев; В данном примере:
М5:М6
(абсолютную адресацию
определяет Excel)
поместить результат в диапазон- указать область вывода результатов. В данном примере:
OK. Проанализировать результат поиска самостоятельно.
Примечание.В списке имен объектов автоматически появляются служебные имена областей: данных -база, критериев -Критерии, вывода -Извлечь.
Технология работы с расширенным фильтром в последующих примерах 7.3.2. та же, что и в задании 1. Следует особое внимание обратить на создание критериев.
Текстовые критерии
Задание2. Вывести данные о товарах на букву М.
Выполнение:
Заголовок критерия – «Наименование товара»;
в условии отбора (критерии) следует задать только букву М, что равносильно – М*
Задание3.Вывести данные о товарах, названия которых начинаются по алфавиту с буквы М до Я.
Выполнение:Критерий:>=M
Примечание.Критерий:< M, если нужно вывести названия, начинающиеся с А до Л.
Задание4. Текстовая формула. Из базы данных, содержащей списки торговых агентов
вывести сведения об агенте по фамилии Попов.
Выполнение:
Под заголовком критерия «Фамилия» в качестве условия отбора следует ввести текстовую формулу:
= “=Попов”.
Если ввести точное значение (без формулы) - Попов, то выведутся и Попович, и Поповский и т.д.
Логические выражения
Задание5.Вывести список оставшихся товаров кондитерского отдела с указанием суммы остатка.
Выполнение:
Блок критериев этого задания выглядит так:
Примечание.
Данный критерий состоит их двух условий отбора по разным полям, объединённым логической операцией И (условия вводятся в однойстроке, в смежных ячейках).
В область вывода скопировать заголовок поля «Сумма остатка».
Задание6. Вывести список товаров, сумма остатка которых в интервале от 20 до 100 руб.
Блок критериев этого задания:
(операцияИв одном поле )
Задание7. Вывести список товаров кондитерского и молочного отдела.
Выполнение:
Блок критериев этого задания:
Примечание:
Данный критерий состоит их двух
условий отбора: два разных значения
одного поля, объединённых логической операцией ИЛИ
(условия вводятся в одном столбце,в смежных ячейках)
Задание8.Вывести список оставшихся товаров кондитерского и молочного отделов.
Выполнение:
Область критериев
этого задания состоит из
блока в 6 ячеек
и выглядит так:
Примечание: Данный критерий состоит из четырёх условий отбора, объединённых совместно логическими операциямиИиИЛИ.
Вычисляемый критерий
Задание8.Вывести список товаров, цена расхода которых больше средней.
Выполнение:
Вычислить среднее значение цены расхода в любой свободной ячейке рабочего листа, задать имя этой ячейке – Ср_цена (внешняя ссылка в критерии должна быть абсолютной).
Создать вычисляемый критерий в двух ячейках, учитывая правила (стр. 29):
заголовок критерия – текст: «больше среднего»;
формула критерия: =G6>=Ср_цена ;
(результат формулы в данном примере – ЛОЖЬ для первого значения столбца)
Вобласть вывода скопировать поля: «Наименование товара» и «Цена расхода»
Задание9.Вывести список товаров, цена расхода которых больше цены прихода на заданный процент.