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

В диалоговом окне команды задать параметры:

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. В каком отделе находится наибольший остаток товара?