Функции базы данных

Используются для анализа данных (и вычислений) из списков или баз данных.

Список – это совокупность данных, связанных однотипностью информации.

При выполнении таких операций, таких как поиск, сортировка или подведение итогов, список автоматически обрабатывается как база данных (БД).

Каждая строка в БД является записью, а столбцы – полями. Заголовки столбцов БД (верхняя строка) являются именами полей.

При создании БД (списка):

1. На листе не следует помещать более одного списка.

2. Между списком и другими данными листа нужно оставлять хотя бы одну пустую строку и один пустой столбец.

3. В самом списке не должно быть пустых строк и столбцов.

4. Данные в полях одного столбца должны быть одного типа.

Функции БД в общем виде записываются так:

ИМЯ­_ФУНКЦИИ (база_данных; поле; критерий)

База_данных – это интервал ячеек, содержащий исходные данные. Этот интервал должен включать строку с именами полей.

Поле – это имя столбца, по которому производятся вычисления.

Аргумент «поле» может быть задан как текст, например «№ группы», или как номер поля: 1 – для первого поля, 2 – для второго поля, или как ссылка на ячейку, в которой находится имя поля.

Критерий – это адрес диапазона, содержащего условия.

Правила создания критерия:

1. Критерий состоит из 2-х и более ячеек.

2. В первой ячейке находится имя поля, а в последующие заносятся условие.

3. Условие может быть текстом, числом, датой или логическим выражением.

4. Критерии могут быть простыми (содержат одно условие) и сложными (два и более условий).

5. Критерий может содержать возвращенное формулой значение. В этом случае первая ячейка не заполняется, но при указании диапазона ссылка на нее обязательно дается.

Примеры критериев:

Простой критерий

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

Сложный критерий

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

Критерий соответствует логическому условию ИЛИ. Т.е. если выполняется хотя бы одно из условий, запись будет обработана.

Критерий, содержащий в условии формулу, аналогичен критерию ИЛИ, рассмотренному ранее.

Использование критериев будет далее рассмотрено на примере.

Наиболее часто используемые функции в данной категории:

БДСУММ – суммирует числа в полях столбца записей базы данных, удовлетворяющих условию.

БИЗВЛЕЧЬ – извлекает из базы данных одну запись, удовлетворяющую заданному критерию.

БСЧЁТ – подсчитывает количество числовых ячеек в выборке из заданной базы данных по заданному критерию.

БСЧЁТА – подсчитывает количество непустых ячеек в выборке из заданной базы данных по заданному критерию.

ДМАКС – возвращает максимальное значение среди выделенных фрагментов базы данных.

ДМИН – возвращает минимальное значение среди выделенных фрагментов базы данных.

ДСРЗНАЧ – возвращает среднее значение выбранных фрагментов базы данных.

Пример.

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

Таблица 3 – Таблица данных

Но в нашем примере нужно суммировать только значения выплат для сотрудников второго подразделения, поэтому используем функцию БДСУММ. Сначала в ячейках А15: А16 создадим критерий (рис 2.).

Рисунок 2 – Задание критерия

Затем, установив курсор в любую свободную ячейку списка, выполним задание (с помощью мастера составим формулу), рис. 3:

Рисунок 3 – Диалоговое окно мастера функции БДСУММ

Рисунок 4 – Результат, полученный при использовании функции БДСУММ