Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Глава 5.doc
Скачиваний:
2
Добавлен:
01.05.2025
Размер:
1 Мб
Скачать
☆

5.5. Функции для работы с базой данных

Основные понятия и определения. БАЗА ДАННЫХ (БД) – это совокупность взаимо­связанных массивов информации, чаще всего имеющих вид таблиц. Для создания баз данных и управления ими используются такие программные продукты, как Access, FoxPro и др.

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

Прежде чем рассматривать эти функции, поясним основные термины, используемые при работе с базами данных:

 Строка таблицы, содержащая информацию об одном объекте, называется ЗАПИСЬЮ.

 Часть записи, имеющая самостоятельное смысловое значение, называется ПОЛЕМ.

 Расположенный в шапке таблицы заголовок столбца называется ИМЕНЕМ ПОЛЯ.

Например, таблица, приведенная на рис.5.20, содержит семь записей. Каждая запись имеет по четыре поля (поля первой записи: Витязь, 03.01.03, 150000, 20000). Имена полей: Судно, Дата, Груз и Прибыль.

Рис. 5.20. Пример использования функций БДСУММ и ДСРЗНАЧ

Все функции баз данных (пока будем называть их обобщенно БД_функции) имеют абсолютно одинаковый синтаксис:

= БД_функция(база_данных; поле; критерий)

База_данных – это диапазон ячеек, где размещены все записи и шапка таблицы. В при­мере, представленном на рис. 5.20, это диапазон A2:D9.

Поле – это заключенное в кавычки имя поля, то есть заголовок столбца, данные которого обрабатываются с помощью БД_функции. В качестве поля можно также использовать ссылку на ячейку с именем поля или порядковый номер столбца в таблице. На рис. 5.20 в формуле БДСУММ использована ссылка на ячейку C2, содержащую имя поля Груз; а в формуле ДСРЗНАЧ использован номер столбца 4, что равноценно имени поля Прибыль.

Критерий – это ссылка на диапазон ячеек (вне базы данных), в верхней строке которого находятся имена полей, а под ними – условия отбора записей, обрабатываемых с помощью БД_функции. На рис. 5.20 в формуле БДСУММ использован Критерий 1 – ссылка на диапазон ячеек F2:H4; а в формуле ДСРЗНАЧ использован Критерий 2 – ссылка на диапазон F7:H9.

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

 Диапазон критерий не может иметь меньше двух расположенных одна над другой ячеек: верхняя – для имени поля, а нижняя – для условия отбора записей в базе данных. Если при этом ячейка для условия пустая (например, задан диапазон F2:F3), БД_функция будет обрабатывать все поля столбца, заданного аргументом поле.

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

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

 Если в диапазоне критерий несколько условий записано в одной строке (см., например, на рис. 5.20 ячейки F8:H8), значит, эти условия связаны между собой логическим И. В таком случае БД_функция будет обрабатывать только те записи, которые удовлетворяют всем заданным условиям.

 Если в диапазоне критерий условия записаны в разных строках (см., например, на рис. 5.20 ячейки F3:G4), значит, эти условия связаны между собой логическим ИЛИ. В таком случае БД_функция обрабатывает данные, удовлетворяющие хотя бы одному условию.

На рис. 5.20 оба критерия имеют по две строки условий. При этом условия каждой строки связаны между собой логическим И, а условия разных строк – логическим ИЛИ.

Функции баз данных. В этом подразделе перечислены наиболее распространенные функции баз данных и пояснено назначение этих функций:

=ДМАКС(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданно­му критерию, и вычисляет для них наибольшее значение в столбце, заданном аргумен­том поле. Например (см. рис. 5.20), функция =ДМАКС(A2:D9; "ГРУЗ"; G2:G3) анали­зи­­рует рейсы до 07.01.03 включительно и возвращает максимальный для них груз 200000.

=ДМИН(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них наименьшее значение в столбце, заданном аргументом поле. Например (см. рис. 5.20), функция =ДМИН(A2:D9; D2; F7:F8) возвращает значение 18000 – минимальную прибыль, полученную судном "Витязь".

=БДСУММ(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них сумму в столбце, заданном аргументом поле. Например (см. рис. 5.20), функция =БДСУММ(A2:D9; C2; F2:H4) возвращает зна­чение 560000 – сумму груза, перевезенного всеми судами в рейсах до 07.01.03 включительно и "Керчью" 25.01.03 (груз, перевезенный "Керчью" 27.01.03, не учитывался, так как для него не выполняется условие Груз > 150000).

=ДСРЗНАЧ(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них среднее значение в столбце, заданном аргументом поле. Например (см. рис. 5.20), функция =ДСРЗНАЧ(A2:D9; 4; F7:H9) возвращает значение 19500 – среднюю прибыль, полученную "Витязем" 15.01.03 и "Керчью" 25.01.03.

=БСЧЁТ(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них количество числовых полей в столбце, заданном аргументом поле. Например (см. рис. 5.20), функция =БСЧЕТ(A2:D9; D2; H7:H8) возвращает значение 3. Именно столько рейсов выполнено до 25.01.03 и для каждого из них в столбце Прибыль есть числовое поле.

=БСЧЁТА(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них количество непустых полей в столбце, заданном аргументом поле.

=БДПРОИЗВЕД(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них произведение в столбце, заданном аргументом поле.

=БИЗВЛЕЧЬ(база_данных; поле; критерий) – выбирает запись, удовлетворяющую задан­ному критерию, и возвращает значение ее поля из столбца, заданного аргументом поле. Если заданному критерию не удовлетворяет ни одна запись, или удовлетворяют несколько записей, функция возвращает сообщение об ошибке.

=БДДИСП(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них дисперсию по значениям, находящимся в столбце, который задан аргументом поле.