- •Глава 5. Встроенные функции 141
- •Глава 5. Встроенные функции
- •5.1. Правила использования функций
- •5.2. Математические функции
- •5.3. Логические функции
- •5.4. Статистические функции
- •5.5. Функции для работы с базой данных
- •5.6. Функции дат и времени
- •5.7. Инженерные функции
- •5.8. Функции ссылок и массивов
- •5.9. Финансовые функции
- •5.10. Другие функции
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 и для каждого из них в столбце Прибыль есть числовое поле.
=БСЧЁТА(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них количество непустых полей в столбце, заданном аргументом поле.
=БДПРОИЗВЕД(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них произведение в столбце, заданном аргументом поле.
=БИЗВЛЕЧЬ(база_данных; поле; критерий) – выбирает запись, удовлетворяющую заданному критерию, и возвращает значение ее поля из столбца, заданного аргументом поле. Если заданному критерию не удовлетворяет ни одна запись, или удовлетворяют несколько записей, функция возвращает сообщение об ошибке.
=БДДИСП(база_данных; поле; критерий) – анализирует записи, удовлетворяющие заданному критерию, и вычисляет для них дисперсию по значениям, находящимся в столбце, который задан аргументом поле.
