Функции работы с списками (базами данных)

 

В Excel существует группа функций предназначенных для работы со списками. Эти функции размещены в категории «Работа с базой данных» мастера функций.

 

 

 

Наиболее часто используемой функцией работы с базами данных является функция БДСУММ.

 

Синтаксис:

БДСУММ(База_Данных; Поле; Критерий)

 

где База_Данных – прямоугольный диапазон ячеек или наименование списка; Поле­ – имя поля, значения которого необходимо просуммировать; Критерий – диапазон или имя диапазона ячеек, содержащего критерии отбора записей списка для суммирования. Любой диапазон ячеек, содержащий в качестве первой строки названия полей, а второй – значения ограничений может быть использован в качестве параметра критерий.

 

Рассмотрим использование функции БДСУММ на примере рассмотренного ранее списка «Реализация товаров». Предположим, что требуется получить отчет об объемах продаж за произвольный период времени.

Для этого будет необходимо создать на отдельном листе Excel соответствующую форму вида:

№ п/п	Наименование товара	Ед.изм.	Кол-во	Сумма
…	…	…	…	…

 

Форма на листе «Отчет №1» Excel будет выглядеть следующим образом:

 

 

Ячейки C4 и С5 предназначены для ввода начальной и конечной даты периода. Им необходимо присвоить имена (Вставка ® Имя ® Присвоить…) НачДата и КонДата соответственно.

Далее на отдельном листе сформировать критерии отбора. Для этого создадим новый лист и назовем его «Критерии отбора». На этом листе создадим форму для установки критериев отбора следующего вида.

 

 

Ячейка B6 должна содержать условие отбора элементов базы данных «Реализация товаров» по дате. Заголовок столбца (ячейка B5) содержит левую часть логического выражения, следовательно ячейка под заголовком столбца (ячейка B6) должна содержать правую часть логического выражения.

Введем в ячейку B6 правую часть условия

 

=">="&НачДата

 

Оператор & выполняет роль соединителя строк.

 

Таким образом, получается логическое выражение вида:

Дата >= НачДата

 

где НачДата – имя ячейки, содержащей начальную дату периода расчета.

Теперь необходимо заполнить ячейку C6. Туда необходимо ввести ограничение временного интервала сверху.

 

="<="&КонДата

 

Получили второе условие:

 

Дата <= КонДата

 

Осталось ввести ограничение по наименованию товара. В ячейку А6 введем наименование товара «Товар1», чем получаем третий критерий отбора

 

Наименование товара = «Товар1»

 

Выделим область критериев отбора для товара «Товар1» и присвоим ей имя (Вставка ® Имя … ® Присвоить…) Товар1Критерии.

Формирование критериев окончено. Возвращаемся к форме «Отчет №1. Реализация товаров».

 

 

 

Применим функцию БДСУММ. В ячейку сумма введем формулу

 

=БДСУММ(РеализацияТоваров;"Сумма";Товар1Критерии)

 

Что означает: просуммируй поле «Сумма» в базе данных РеализацияТоваров с учетом условий, размещенных в диапазоне Товар1Критерии.

 

 

Теперь в ячейки C4 и С5 введем соответственно 01.05.2001 и 02.05.2001. Получим:

 

 

Возвратившись к исходным данным нетрудно заметить, что ячейка Е8 содержит выручку от реализации за период с 01.05.2001 по 02.05.2001 (т.е. за два дня) по товару «Товар1» (12 000 + 9 000 = 21 000).

Подсчитаем объем продаж в натуральном выражении. Формула для ячейки D8 будет выглядеть следующим образом:

 

=БДСУММ(РеализацияТоваров;"Кол-во";Товар1Критерии)

 

Аналогичные операции можно проделать для других товаров, в результате чего получим отчет необходимой формы.