Функції баз даних, excel для всіх

  • таблиця повинна обов'язково містити заголовки стовпців. Ці заголовки повинні розташовуватися строго в одному рядку, не повинні містити об'єднаних і порожніх клітинок.
  • таблиця повинна бути неподільна, тобто не повинна містити повністю порожніх рядків і стовпців, а так само пов'язаних осередків
  • в кожному стовпці повинна міститися однотипна інформація: якщо в стовпці повинні міститися дати, значить крім дат там не повинно бути нічого іншого; якщо в стовпці числа (суми, кол-во) - значить повинні бути тільки числа. Не слід при відсутності чисел залишати осередок порожній або ставити пробіл. Замість цього необхідно ставити 0.

Для розбору функції баз даних візьмемо такий приклад таблиці:

Функції баз даних, excel для всіх

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

Саме для цієї таблиці будуть наведені всі приклади описів функцій. І критерії запропоновано такі: відбирати з поля "Дерево" Яблуні з висотою більше 3 і менше 6 і Вишні, зі значенням в полі "Вік" більше 8. Якщо подивитися на таблицю даних (з якої будуть відбиратися дані і проводиться розрахунки функціями), то цим критеріям відповідають лише два рядки: рядки 9 і 10 листа.
Як видно, в якості критеріїв можна вказувати вираз у вигляді:> 6. <>0 (не дорівнює нулю),> = 7. Так само знаки рівності і порівняння можна застосовувати і з текстовими даними, наприклад: <>"Яблуня".

  • ДСРЗНАЧ (DAVERAGE) - Обчислює середнє значення обраних записів бази даних:
    = ДСРЗНАЧ (A6: E12; 5; A1: F3)
    = ДСРЗНАЧ (A6: E12; "Прибуток"; A1: F3)
    = DAVERAGE (A6: E12, 5, A1: F3) поверне значення 90 000р. тому сума прибутку відібраних записів дорівнює 180 000р. а всього відібрано 2 записи. 180 000/2 = 90 000.
  • БСЧЁТ (DCOUNT) - Підраховує кількість числових комірок в базі даних:
    = БСЧЁТ (A6: E12; 5; A1: F3)
    = БСЧЁТ (A6: E12; "Прибуток"; A1: F3)
    = DCOUNT (A6: E12, 5, A1: F3) поверне число 2. тому тільки два рядки в таблиці відповідають критеріям
  • БСЧЁТА (DCOUNTA) - Підраховує кількість непустих осередків в базі даних:
    = БСЧЁТА (A6: E12; 4; A1: F3)
    = БСЧЁТА (A6: E12; "Прибуток"; A1: F3)
    = DCOUNTA (A6: E12, 4, A1: F3) поверне 2, тобто підрахує в відповідають критеріям рядках кількість непустих осередків в стовпці "Прибуток"
  • БІЗВЛЕЧЬ (DGET) - Витягує з бази даних один запис, що задовольняє заданій умові:
    = БІЗВЛЕЧЬ (A6: E12; 5; A1: F3)
    = БІЗВЛЕЧЬ (A6: E12; "Прибуток"; A1: F3)
    = DGET (A6: E12, 5, A1: F3) для заданих умов поверне значення помилки # ЧИСЛО! (#NUM!). тому цим умовам відповідає більше одного запису. Якщо ж вказати діапазон для критерію як:
    = БІЗВЛЕЧЬ (A6: E12; 5; A1: F2) то функція поверне значення 75 000р. тобто єдиний запис про прибуток для Яблонь з висотою більше 3 і менше 6 (в даний проміжок потрапляє лише рядок 10 - яблуні, висота 5)
  • ДМАКС (DMAX) - Знаходить максимальне значення серед виділених записів бази даних:
    = ДМАКС (A6: E12; 5; A1: F3)
    = ДМАКС (A6: E12; "Прибуток"; A1: F3)
    = DMAX (A6: E12, 5, A1: F3) компенсувати 105 000р. тому це максимальний прибуток з усіх відповідають критеріям рядків.
  • ДМІН (DMIN) - Знаходить мінімальне значення серед виділених записів бази даних:
    = ДМІН (A6: E12; 5; A1: F3)
    = ДМІН (A6: E12; "Прибуток"; A1: F3)
    = DMIN (A6: E12, 5, A1: F3) компенсувати 75 000р. тому це мінімальний прибуток з усіх рядків, що відповідають критеріям
  • БДПРОІЗВЕД (DPRODUCT) - Перемножує значення певного поля в записах бази даних, що задовольняють умові:
    = БДПРОІЗВЕД (A6: E12; 3; A1: F3)
    = БДПРОІЗВЕД (A6: E12; "Вік"; A1: F3)
    = DPRODUCT (A6: E12, 3, A1: F3) поверне 210. тому будуть перемножити всі значення стовпця "Вік", що відповідають критеріям (14 * 15 = 210)
  • ДСТАНДОТКЛ (DSTDEV) - Оцінює стандартне відхилення за вибіркою з виділених записів бази даних:
    = ДСТАНДОТКЛ (A6: E12; 4; A1: F3)
    = ДСТАНДОТКЛ (A6: E12; "Врожайність"; A1: F3)
    = DSTDEV (A6: E12, 4, A1: F3) поверне 0,707107. тобто оцінку стандартного відхилення врожайності по зазначеним критеріям.
  • ДСТАНДОТКЛП (DSTDEVP) - Обчислює стандартне відхилення за генеральною сукупністю з виділених записів бази даних:
    = ДСТАНДОТКЛП (A6: E12; 4; A1: F3)
    = ДСТАНДОТКЛП (A6: E12; "Врожайність"; A1: F3)
    = DSTDEVP (A6: E12, 4, A1: F3) поверне 0,5. тобто точне стандартне відхилення врожайності по зазначеним критеріям, якщо вважати, що дані в базі даних описують генеральну сукупність всіх дерев в саду.
  • БДСУММ (DSUM) - Підсумовує числа в поле для записів бази даних, що задовольняють умові:
    = БДСУММ (A6: E12; 5; A1: F3)
    = БДСУММ (A6: E12; "Прибуток"; A1: F3)
    = DSUM (A6: E12, 5, A1: F3) компенсувати прибутку всіх рядків, що відповідають критеріям, тобто 180 000р.
    = БДСУММ (A6: E12; 5; A1: A2)
    = DSUM (A6: E12, 5, A1: A2) компенсувати прибутку від усіх Яблонь, тобто 225 000р.
  • БДДІСП (DVAR) - Оцінює дисперсію за вибіркою з виділених записів бази даних:
    = БДДІСП (A6: E12; 4; A1: A2)
    = БДДІСП (A6: E12; "Врожайність"; A1: A2)
    = DVAR (A6: E12, 4, A1: A2) поверне 0,5. що буде оцінкою дисперсії врожайності по зазначеним критеріям, якщо вважати, що дані в таблиці є вибіркою з генеральної сукупності всіх дерев в саду
  • БДДІСПП (DVARP) - Обчислює дисперсію по генеральної сукупності з виділених записів бази даних:
    = БДДІСПП (A6: E12; 4; A1: A2)
    = БДДІСПП (A6: E12; "Врожайність"; A1: A2)
    = DVARP (A6: E12, 4, A1: A2) поверне 10,66667. тобто точну дисперсію врожайності Яблонь та вишень, якщо вважати, що дані в базі даних описують генеральну сукупність всіх дерев в саду