Правила создания диапазона критериев следующие. В первой строке диапазона помещают имена полей списка, для которых задаются условия отбора. Во второй и последующих строках под именами полей вводятся непосредственно сами условия отбора. Если условия отбора размещены в одной строке, то тем самым задается их взаимодействие по схеме «И» (требуется одновременное их выполнение). Для соединения условий по схеме «ИЛИ» необходимо располагать каждое условие в отдельной строке.
Фильтрация
После того, как диапазон критериев создан, нужно выполнить команду Данные-Фильтр-Расширенный фильтр. В окне диалога указать диапазоны:
- исходной базы данных;
- критериев;
- размещения отфильтрованных данных (на месте или отдельно).
Пример. С помощью расширенного фильтра вывести записи о продаже автомобилей марки «Мерседес» продавцами Ивановым и Сидоровым.
Решение задачи. Вначале создадим диапазон критериев, например под таблицей. Для этого, отступив одну строку, ниже под таблицей в ячейку A10 скопируем имя поля «Продавец». Под ним впишем условие отбора для этого поля: Иванов и Сидоров по схеме «ИЛИ». Далее скопируем имя поля «Марка» и для каждого продавца впишем Мерседес, чтобы получилась схема «И» для продавца и марки автомобиля.
Диапазон критериев будет иметь вид, указанный в таблице 1.8.
Таблица 1.8 – Диапазон критериев
| A | B | |
| Продавец | Марка | |
| Иванов | Мерседес | |
| Сидоров | Мерседес |
Далее выполняем команду (курсор остается в базе данных) Данные-Фильтр-Расширенный фильтр. В появившемся диалоговом окне указываем буксировкой мыши диапазоны базы данных, критериев и диапазон для вывода отфильтрованных записей (рис. 1.3).
Рисунок 1.3 – Окно диалога Расширенный фильтр
Щелкнув по кнопке Ok, получим отфильтрованные записи (таблица 1.9):
Таблица 1.9 – Записи, отфильтрованные расширенным фильтром
| A | B | C | D | |
| Продавец | Марка | Год выпуска | Оборот | |
| Иванов | Мерседес | |||
| Сидоров | Мерседес | |||
| Сидоров | Мерседес |
Пример. Расширенным фильтром вывести все записи с оборотом от 200 до 400 включительно.
Для решения задачи вначале необходимо на любом свободном месте рабочего листа создать диапазон критериев (таблица 1.10).
Таблица 1.10 – Диапазон критериев
| Оборот | Оборот |
| >200 | <400 |
Поле «Оборот» скопировали дважды, чтобы получилась схема «И». Далее нужно выполнить фильтрацию, командой Данные-Фильтр-Расширенный фильтр.
Функции базы данных
Функции базы данных позволяют легко обрабатывать данные списка.
Эти функции имеют вид
ИМЯ_ФУНКЦИИ(база_данных;поле;диапазон_критериев)
где
- база_данных – это любой непрерывный интервал ячеек с заголовками в первой строке;
- поле – это либо заголовок столбца в двойных кавычках, либо адрес ячейки с заголовком столбца, либо порядковый номер столбца;
- Диапазон критериев – это интервал ячеек, содержащий условия отбора записей.
Функции данной категории отбирают из списка записи, удовлетворяющие заданному критерию. Затем выполняют определенные операции с данными из указанного поля. Перед вводом любой функции базы данных необходимо создать диапазон критериев. Создание критерия для функции аналогично созданию критерия для расширенного фильтра.
К функциям базы данных относятся следующие функции: БДСУММ, БСЧЁТ, ДМАКС, БИЗВЛЕЧЬ, ДСРЗНАЧ. Для иллюстрации работы этих функций рассмотрим два примера.
Пример. Посчитать выручку от продажи автомобилей 1998 г. выпуска.
Данное задание будем выполнять функцией БДСУММ, которая суммирует числа из указанного поля для записей, удовлетворяющих критерию.
Сначала создадим диапазон критериев. Для этого в любом месте текущего рабочего листа (например, в ячейке A22) впишем (лучше скопируем) имя поля, по которому задано условие отбора. Это «Год выпуска». Под ним запишем само условие (таблица 1.11).
Таблица 1.11 – Диапазон критериев
| Год выпуска | |
Рядом в ячейке C22 расположим пояснительный текст «Выручка от продажи автомобилей 1998 г», а в ячейку C23 введем функцию
=БДСУММ(A1:D8;D1;A22:A23) Получим: 270.
Пример. Найти продавца с наименьшим оборотом.
В этом примере нужно воспользоваться функцией БИЗВЛЕЧЬ, которая извлекает содержимое ячейки из указанного поля записи, удовлетворяющей критерию. Здесь отобрать надо запись с наименьшим оборотом. Значит, условие надо наложить на поле «Оборот». В качестве условия надо использовать функцию =МИН(D2:D8). Итак, диапазон критериев должен иметь вид, указанный в таблице 1.12).
Таблица 1.12 – Диапазон критериев
| A | |
| Оборот | |
Поскольку получить надо фамилию продавца, то извлекать надо из поля «Продавец». Следовательно, в ячейку C32 введем функцию =БИЗВЛЕЧЬ(A1:D8;A1;A31:A32). Получим Сидоров.
