4.6 Функції баз даних

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

В Excel  є можливість відразу у процесі пошуку проводити дії над цими числами. Для цього використовують функції баз даних, які забезпечують такі основні дії із записами, які задовольняють певні критерії:

арифметичні дії над даними, які знаходяться в комірках відфільтрованих записів;

статистичний аналіз величин, які знаходяться в комірках відфільтрованих записів.

Для того щоб ознайомитися зі списком функцій бази даних, необхідно активізувати Мастера функций на панелі інструментів Стандартная і вибрати команду Робота з базою даних (рис. 9.17).

 

У відповідному полі діалогового вікна з’явиться перелік функцій для роботи з базою даних. Якщо помістити курсор миші на назву потрібної функції, можна дізнатися про її призначення.

До функцій баз даних відносять: БДСУММ, БСЧЕТ, ДСРЗНАЧ, ДМАКС та інші. Всі функції баз даних мають три аргументи:

Функція (база_даних;поле;критерій).

.

 

У полі база_даних зазначають весь діапазон бази даних або списку, включаючи і назви полів. Поле – стовпець, зі значенням якого проводяться розрахунки. Його позначають за допомогою порядкового номера (1, 2,..), назви поля, наприклад Сума кредиту або адреси комірки, де зберігається назва поля, наприклад В2. Критерій – інтервал комірок, що складається у першому рядку з імені (імен) поля, а в другому – з умови для пошуку значень. Критерій необхідно створити перед початком розрахунку функцій.

 

 

 

Функція БДСУММ – додає числа в полі запису бази даних, що задовольняють умову (рис. 9.18).

Функція БСЧЕТ – підраховує кількість числових комірок у виборці із заданої бази даних за заданим критерієм.

Функція ДСРЗНАЧ – повертає середнє всіх значень стовпця списку чи бази даних, які задовольняють задані умови.

Функція ДМАКС – повертає максимальне значення поля записів бази даних, що задовольняють певний критерій.

Інші функції необхідно розглянути самостійно.

 

Приклад 9.7. Потрібно знайти максимальне значення стовпця «Сума кредиту» для запису банку «Аваль», див. табл. 9.3.

Розв’язання

Для цього необхідно послідовно виконати такі дії:

1  Створити критерій, виділивши рядок із назвою стовпців, скопіювати його, скопіювати його під таблицею з даними у вільний рядок, а під назвою стовпця «Банк», створити умову для пошуку відповідних значень банку «Аваль».

2  Встановити курсор у вільну комірку, активізувати кнопку Вставка функции, з переліку категорій вибрати Работа з базой данных, функцію ДМАКС та активізувати кнопкою Ок.

3. У наступному вікні Аргументы функции зазначити такі параметри:

База_данных – вказати відповідний діапазон, наприклад діапазон A2:G9.

Поле – зазначити назву стовпця, для якого виконуються розрахунки, наприклад комірку В2.

Критерий – вказати діапазон комірок, в якому створений критерій, наприклад А3:А5.

Тепер необхідно активізувати функцію кнопкою Ок.

Після цього у відповідній комірці в кінці таблиці А6 з’явиться розраховане значення, наприклад 15000, а на панелі формул – створена функція =ДМАКС(А2:G9;B2;A3:A5)

 

Таблиця 9.3 - Розрахунок щомісячних платежів

 

Приклад 9.8. Необхідно обчислити середню стипендію студентів зі списку, зображеного на рис. 9.2, що навчаються за умовою повної оплати (ПО).

Розв’язання

Тут необхідно виконати такі дії: створити критерій, виділивши рядок із назвами стовпців (рядок 3), скопіювати його під таблицею з даними у вільний рядок (рядок 20), а під назвою стовпця Форма навчання створити умову для пошуку відповідних значень – ПО (комірка Е21). Вставити курсор у вільну комірку F22, активізувати Вставка функції з переліку категорій Работа з базой данных, функція ДСРЗНАЧ, й активізувати кнопкою ОК.

У вікні Аргументы необхідно визначити параметри:

база_данных – діапазон A3:G18;

поле – комірку F3, де вміщено назву стовпця, для якого виконується розрахунок;

критерий – діапазон комірок, у якому створено критерій, тобто E20:E21.

Результатом виконання буде функція =ДСРЗНАЧ(A3:G18;F3;E20:E21), що обчислює середнє значення по полю Стипендія бази даних (рис. 9.19).