Функции баз данных табличного процессора

Табличный процессор имеет 12 встроенных функций, называемых функциями баз данных, которые специально предназначены для работы со списками. В списке Мастера функций они размещаются в категории Работа с базой данных.

Функции баз данных являются аналогами статистических функций, но в отличие от статистических, функции баз данных возвращают результат, вычисленный при обработке тех данных списка, которые удовлетворяют заданным условиям.

Все функции баз данных имеют одинаковый синтаксис:

ИМЯ_ФУНКЦИИ (База_данных; Поле; Критерий),

гдепараметр База_данных устанавливает адрес диапазона, в котором размещен список (вместе с названиями столбцов), содержащий анализируемые исходные данные;

параметр Поле определяет адрес ячейки, содержащей название столбца списка, в котором группируются данные для расчета;

параметр Критерий указывает адрес диапазона, в котором записано условие на выборку данных для последующей обработки.

Перечень функций баз данных табличного процессора, их назначение и синтаксис приведены в табл. 3.1.

Таблица 3.1 Функции баз данных табличного процессора

№ п/п	Имя функции	Назначение	Синтаксис
	БДДИСП	Оценивает дисперсию генеральной совокупности по выборке, исполь-зуя числа в указанном поле всех записей, удовлетворяющих крите-рию	БДДИСП (База_данных; Поле; Критерий)
	БДДИСПП	Вычисляет дисперсию генеральной совокупности, используя числа в указанном поле всех записей, удовлетворяющих критерию	БДДИСПП (База_данных; Поле; Критерий)
	БДПРОИЗВЕД	Перемножает значения в указанном поле всех записей, удовлетворяющих критерию	БДПРОИЗВЕД (База_данных; Поле; Критерий)
	БДСУМ	Суммирует числа в указанном поле всех записей базы данных, удовлетворяющих критерию	БДСУМ (База_данных; Поле; Критерий)
	БИЗВЛЕЧЬ	Извлекает из базы данных одну запись, которая удовлетворяет заданному критерию	БИЗВЛЕЧЬ (База_данных; Поле; Критерий)
	БСЧЕТ	Вычисляет количество числовых ячеек в выборке из базы данных по заданному критерию	БСЧЕТ (База_данных; Поле; Критерий)
	БСЧЕТА	Вычисляет количество не пустых ячеек в выборке из базы данных по заданному критерию	БСЧЕТА (База_данных; Поле; Критерий)
	ДМАКС	Суммирует числа в указанном поле всех записей базы данных, удовлетворяющих критерию	ДМАКС (База_данных; Поле; Критерий)
	ДМИН	Возвращает наименьшее число в указанном поле всех записей, удовлетворяющих критерию	ДМИН (База_данных; Поле; Критерий)
	ДСРЗНАЧ	Усредняет значения всех записей в указанном поле, удовлетворяющих критерию	ДСРЗНАЧ (База_данных; Поле; Критерий)
	ДСТАНДОТКЛ	Оценивает стандартное отклонение на основе выборки из генеральной совокупности, используя числа всех записей в указанном поле, удовлетворяющих критерию	ДСТАНДОТКЛ (База_данных; Поле; Критерий)
	ДСТАНДОТКЛП	Вычисляет стандартное отклонение генеральной совокупности, исполь-зуя числа в указанном поле всех записей, удовлетворяющих критерию	ДСТАНДОТКЛП (База_данных; Поле; Критерий)

Технология анализа данных

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

– на свободном участке рабочего листа сформировать условие для вычислений (критерий). Условия формируются по тем же правилам, что и для инструмента Расширенный фильтр;

– поместить курсор в ячейку, где должен помещаться результат, вставить формулу функции с записанными параметрами.

Пример 3.7. Требуется вычислить итоговую сумму по всем расходным операциям с номерами счетов 4-15 включительно, проведенным в период с 12 по 24 января.

Решение

Запишем выражение условия в свободном диапазоне рабочей листа, например НЗ:К4 (рис.3.29).

Рис. 3.29

 

В ячейку, например, I1 введем формулу БДСУММ(A3:F125;F3;H3:K4) (рис.3.30).

Рис. 3.30

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

3.6 АНАЛИЗ ДАННЫХ С ПРИМЕНЕНИЕМ ТЕХНОЛОГИИ
КОНСОЛИДАЦИИ

Под консолидацией понимается объединение данных, хранящихся в одном или в нескольких списках, выполнение над объединенными данными необходимых вычислений и формирование результирующей таблицы.

Консолидацию можно выполнять по расположению или по категории. Консолидация по расположению предполагает сбор информации из ячеек исходных списков, расположенных одинаково. При консолидации по категории табличный процессор использует в качестве признака для объединения заголовки столбцов или строк списков. Такой способ консолидации предоставляет большую свободу организации данных.

Консолидация выполняется с помощью инструмента Консолидация, который включается командой меню Данные/Консолидация.

Для выполнения консолидации нужно выполнить операции:

– установить курсор в ячейку рабочего листа, в которой будет размещатьсялевая верхняя ячейка результирующей таблицы;

– включить инструмент Консолидация;

– в диалоговом окне Консолидация в списке Функция выбрать функцию для выполнения необходимых вычислений;

– в поле Ссылка указать адрес диапазона или всего списка, данные которого предполагается консолидировать, если есть еще списки для консолидации щелкнуть на кнопке Добавить, ввести следующий диапазон и т.д.;

– если консолидация выполняется по категории, в группе Использовать в качестве имен установить флажок Подписи верхней строки, при консолидации по расположению - флажок Значения левого столбца;

– для установления связи с исходными списками установить флажок Создавать связи с исходными данными;

– щелкнуть на кнопке ОК.

Пример 3.8 Требуется, используя данные исходной таблицы (рис. 3.31), создать таблицу, отражающую сведения об общих суммах приходных и расходных операций по каждому операционному дню.

Решение

Установим курсор в свободную ячейку рабочего листа, например НЗ, и включим инструмент Консолидация.

В диалоговом окне Консолидация:

– в списке Функция выберем Сумма (устанавливается по умолчанию);

– в поле Ссылка укажем ссылку на диапазон списка так, чтобы самым левым столбцом диапазона был столбец с названием Дата операции, т.е. столбец, который содержит признаковые данные для консолидации (консолидация по расположению), и включал столбцы с именами Приход и Расход, данные которых должны суммироваться при формировании консолидированной таблицы;

– в группе Использовать в качестве имен установим флажок Значения левого столбца и щелкнем на кнопке ОК (рис.3.31).

Рис. 3.31

 

Результирующая консолидированная таблица приведена на рисунке 3.32.

Рис. 3.32

 

В отличие от консолидации по расположению, которая может применяться для данных одной таблицы, консолидация по категориям применяется только в тех случаях, когда нужно консолидировать данные нескольких таблиц, как это показано на рисунке 3.33.

Рис. 3.33