 |
В диалоговом окне команды задать параметры:
s в поле Обработка включить кнопку «Скопировать результат в другое место»,
Примечание. Переключатель «фильтровать список на месте» изменяет вид исходной БД (как в автофильтре).
s исходный диапазон - указать курсором область базы данных(включая первую строку заголовков);
s 
диапазон критериев - указать курсором область критериев; В данном примере:
М5:М6
(абсолютную адресацию
определяет Excel)
s 
поместить результат в диапазон - указать область вывода результатов.В данном примере:
s OK. Проанализировать результат поиска самостоятельно.
G Примечание. В списке имен объектов автоматически появляются служебные имена областей: данных - база, критериев - Критерии, вывода - Извлечь.
G Технология работы с расширенным фильтром в последующих примерах 7.3.2. та же, что и в задании 1. Следует особое внимание обратить на создание критериев.
2) Текстовые критерии
Задание 2. Вывести данные о товарах на букву М.
Выполнение:
- Заголовок критерия – «Наименование товара»;
- в условии отбора (критерии) следует задать только букву М, что равносильно – М*
Задание 3. Вывести данные о товарах, названия которых начинаются по алфавиту с буквы М до Я.
Выполнение: Критерий: >=M
Примечание. Критерий: < M, если нужно вывестиназвания, начинающиеся с А до Л.
Задание 4. Текстовая формула. Из базы данных, содержащей списки торговых агентов
вывести сведения об агенте по фамилии Попов.
Выполнение:
Под заголовком критерия «Фамилия» в качестве условия отбора следует ввести текстовую формулу:
= “=Попов”.
Если ввести точное значение (без формулы) - Попов, то выведутся и Попович, и Поповский и т.д.
3) Логические выражения
Задание 5. Вывести список оставшихся товаров кондитерского отдела с указанием суммы остатка.
Выполнение:
· Блок критериев этого задания выглядит так:
G Примечание.
Данный критерий состоит их двух условий отбора по разным полям, объединённым логической операцией И (условия вводятся в одной строке, в смежных ячейках).
· В область вывода скопировать заголовок поля «Сумма остатка».
Задание 6. Вывести список товаров, сумма остатка которых в интервале от 20 до 100 руб.
Блок критериев этого задания:
(G операция И в одном поле)
Задание 7. Вывести список товаров кондитерского и молочного отдела.
Выполнение:
Блок критериев этого задания:
G Примечание:
Данный критерий состоит их двух
условий отбора: два разных значения
одного поля, объединённых логической операцией ИЛИ
(условия вводятся в одном столбце, в смежных ячейках)
Задание 8. Вывести список оставшихся товаров кондитерского и молочного отделов.
Выполнение:
Область критериев
этого задания состоит из
блока в 6 ячеек
и выглядит так:
G Примечание: Данный критерий состоит из четырёх условий отбора, объединённых совместно логическими операциями И и ИЛИ.
4) Вычисляемый критерий
Задание 8. Вывести список товаров, цена расхода которых больше средней.
Выполнение:
· Вычислить среднее значение цены расхода в любой свободной ячейке рабочего листа, задать имя этой ячейке – Ср_цена (внешняя ссылкав критерии должна быть абсолютной).
· Создать вычисляемый критерий в двух ячейках, учитывая правила (стр. 29):
- заголовок критерия – текст: «больше среднего»;
- формула критерия: =G6>=Ср_цена (рис.15, 26с.);
(результат формулы в данном примере – ЛОЖЬ для первого значения столбца)
· 
В область вывода скопировать поля: «Наименование товара» и «Цена расхода»
Задание 9. Вывести список товаров, цена расхода которых больше цены прихода на заданный процент.
Функции базы данных
Расширенный фильтр находит записи, удовлетворяющие критерию, в том числе содержащие числа.
В Excel можно сразу в процессе поиска производить действия над этими числами
(арифметические и статистические).
Для этих действий используются специальные функции базы данных, которые вызываются кнопкой 
(Вставка функции) из группы «Работа с базой данных». Подробное описание каждой из этих функций – в справке
Excel -. Общий формат этих функций при вызове:
БДФункция (база данных; поле; критерий),
где: БДФункция - обобщенное название группы базовых функций;
соответствуют понятиям область
базы данных и область критерия
в Расширенном фильтре.
поле - название поля (столбца) или номер столбца, в котором выполняются расчёты.
G Примечание.
Все параметры задаются в диалоговом окне функции указанием координат (или имени) области или поля.
Примеры 7.3.3. Вычисления с помощью базовых функций. (Все примеры приведены на основе базы данных «Магазин» (рис.15))
Задание 1. Вычислить общую сумму оставшегося товара.
Выполнение: L
· 
Создать критерий:
· Выбрать ячейку для ввода функции,
прокомментировать её текстом:
«общая сумма оставшегося товара»;
· вызвать Мастер функций -
· задать категорию функций – Работа с базой данных;
· выбрать функцию БДСУММ; аргументы функции приведены на рис.20 (стр.41)
· формула в ячейке: = БДСУММ(база;J5;L5: L6).
Рис.18. Окно задания аргументов базовых функций.
Самостоятельно!
1) 
Проверить, изменится ли результат, если для выполнения задания 1 использовать обычную функцию: =СУММ(J5: J18),
2) Чем отличается в использовании базовая функция от обычной?
Задание 3. Сколько наименований товара продаётся в упакованном виде?
Выполнение. N
· 
Блок критериев этого задания:
· Формула расчёта: 5
= БСЧЁТА(база;D5;N5:N6) 6
G Примечания.
1) Функция БСЧЁТА выбирает в поле, содержащем текстовые значения, даты, логические данные.
2) Функция БСЧЁТ выбирает только числовые значения.
Задание 4. Сколько стоит зефир в шоколаде?
Выполнение. M
· 
Блок критериев этого задания:
· Формула расчёта: 5
=БИЗВЛЕЧЬ(база;G5;М5:М6) 6
Задание 5. Определить название товара с максимальной суммой остатка.
Выполнение этого задания разбивается на два этапа.
1) Вычислить максимальную сумму оставшегося товара.
L М
· 
Блок критериев 1) - L5:L6;
· Формула расчёта в ячейке М6: 5
=ДМАКС(база;J5;L5:L6) 6
2) Определить название товара.
· 
Блок критериев 2) - М5:М6;
· Формула расчёта в ячейке L8: 8
=БИЗВЛЕЧЬ(база;C5;M5:M6)
При работе с функциями базы данных следует помнить, G что любое изменение данных в БД автоматически изменяет результат функции.
Задание 6. Сколько товаров продано ценою меньше средней цены расхода?
Задание 7. В каком отделе находится наибольший остаток товара?
