Функції баз даних
Цей розділ включає в себе функції обробки даних, організованих таким чином, що кожен рядок містить один запис.
Категорію "База даних" можна сплутати з базою даних, інтегрованої у LibreOffice. Однак між базою даних у LibreOffice і категорією "База даних" у LibreOffice Calc немає якого-небудь зв'язку.
Взірець даних:
Такі дані використовуються в деяких прикладах опису функцій:
У діапазоні A1:E10 міститься список дітей, запрошених на день народження Степана. Для кожного запису зазначені такі відомості: у стовпці A вказано ім'я, у стовпці B - клас, потім вік в роках, відстань до школи в метрах і вага в кілограмах.
A |
B |
C |
D |
E |
|
1 |
Ім'я |
Клас |
Вік |
Відстань до школи |
Вага |
2 |
Анничка |
3 |
9 |
150 |
40 |
3 |
Богдан |
4 |
10 |
1000 |
42 |
4 |
Віктор |
3 |
10 |
300 |
51 |
5 |
Галинка |
5 |
11 |
1200 |
48 |
6 |
Данило |
2 |
8 |
650 |
33 |
7 |
Федір |
2 |
7 |
300 |
42 |
8 |
Марта |
1 |
7 |
200 |
36 |
9 |
Григорій |
3 |
9 |
1200 |
44 |
10 |
Ірина |
2 |
8 |
1000 |
42 |
11 |
|||||
12 |
|||||
13 |
Ім'я |
Клас |
Вік |
Відстань до школи |
Вага |
14 |
>600 |
||||
15 |
|||||
16 |
DCOUNT |
5 |
Формула у комірці B16 =DCOUNT(A1:E10;D1;A13:E14)
Параметри для функції бази даних:
Нижче наведені визначення параметрів для всіх функцій бази даних:
База даних - це діапазон клітинок, який визначає її межі.
Поле бази даних визначає стовпець, на якому функція працює після встановлення критеріїв пошуку першого параметра і виділення рядків даних. Це не пов'язано з вашими критеріями пошуку. Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.
Умови пошуку - це діапазон клітинок, який містить умови, за якими виконується пошук. Якщо в один рядок внесено кілька умов пошуку, вони з'єднуються за допомогою І. Якщо умови пошуку внесені у декілька рядків, вони з'єднуються за допомогою АБО. Порожні комірки в діапазоні умов пошуку ігноруються.
Виберіть команду LibreOffice - НалаштуванняЗасоби - Параметри - LibreOffice Calc - Обчислити, щоб визначити поведінку LibreOffice Calc при пошуку ідентичних записів.
Див. також сторінку Wiki про Умовний підрахунок і сумування.
DAVERAGE
Функція DAVERAGE повертає середнє значення для всіх клітинок (полів) у всіх рядках (записах бази даних), які відповідають умовам пошуку.
Синтаксис
DAVERAGE(база даних; поле бази даних; умови пошуку)
Приклад
Щоб обчислити середню вагу всіх дітей одного віку (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:
=DAVERAGE(A1:E10;"Вага";A13:E14)
У комірку рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. Буде відображено середню вагу дітей одного віку.
DCOUNT
Функція DCOUNT повертає кількість рядків (записів) бази даних, що задовольняють певним умовам пошуку і містять числові значення, в колонці "поле" бази даних".
Синтаксис
DCOUNT(база даних; [DatabaseField]; критерій пошуку)
Якщо аргумент поле бази даних пропущений, то DCOUNT повертає число усіх записів, які задовольняють критерій. Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.
Приклад
У наведеному вище прикладі необхідно дізнатися, скільки дітей проходять до школи понад 600 метрів. Результат буде збережений в комірці B16. Встановіть курсор у комірку B16. Введіть формулу =DCOUNT(A1:E10;0;A13:E14) у B16. Майстер функцій допоможе ввести діапазони.
База даних - це діапазон даних, що включає також заголовки, включені в розрахунки: у даному випадку A1:E10. Поле бази даних визначає стовпець критеріїв пошуку: в даному випадку це стовпець з числовими значеннями відстані. Критерій пошуку являє собою діапазон, в якому можна ввести критерії пошуку: в цьому випадку A13:E14.
Щоб дізнатися, скільки у другому класі дітей старших за 7 років, видаліть запис >600 в комірці D14 і введіть 2 в клітинку B14 стовпця "Клас", а потім введіть >7 у комірку C14 праворуч. Отриманий результат дорівнює 2. У другому класі двоє дітей старші за 7 років. Оскільки обидві умови введені в один рядок, вони з'єднані за допомогою І.
DCOUNTA
Функція DCOUNTA служить для підрахунку в базі даних кількості рядків (записів), які відповідають обраним критеріям пошуку і містять числові або буквено-числові значення.
Синтаксис
DCOUNTA(база даних; [DatabaseField]; критерій пошуку)
Якщо аргумент поле бази даних пропущений, то DCOUNTA повертає число усіх записів, які задовольняють критерій. Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.
Приклад
У наведеному вище прикладі можна визначити кількість дітей, чиї імена починаються з "Ж" або наступних букв. Змініть формулу у клітинці B16 наступним чином =DCOUNTA(A1:E10;"Ім'я";A13:E14). Видаліть попередні критерії пошуку введіть >=F в клітинку А14 стовпці "Ім'я". Результат – 5. Тепер при видаленні всіх числових значень для імені "Женя" у рядку 8 результат зміниться на 4. Рядок 8 більше не враховується, бо в ньому відсутні значення. Ім'я "Женя" являє собою текст, а не значення. Слід зазначити, що параметр "Поле" бази даних" повинен визначати стовпець, який може містити значення.
DGET
Функція DGET повертає вміст комірки з посиланням, що відповідає вказаним умовам пошуку. У разі виникнення помилки функція повертає або #VALUE! для незнайдених рядків, або Помилка:502 для кількох знайдених комірок.
Синтаксис
DGET(база даних; поле бази даних; умови пошуку)
Приклад
У наведеному вище прикладі потрібно визначити, в який клас ходить дитина, ім'я якого було введено в клітинку А14. Формула, введена в комірку B16, трохи відрізняється від попередніх прикладів, бо для поля бази даних можна ввести тільки один стовпець (поле бази даних). Введіть наступну формулу:
=DGET(A1:E10;"Клас";A13:E14)
Введіть ім'я Єгор в клітинку А14. Отриманий результат буде дорівнює 2. Єгор ходить у другий клас. Введіть формулу "Вік" замість "Клас" і отримаєте вік Єгора.
Введіть значення 11 тільки в комірку C14, а потім видаліть з рядка всі інші значення. Змініть формулу у клітинці B16 наступним чином:
=DGET(A1:E10;"Ім'я";A13:E14)
Замість класу запитується ім'я. Очікуваний результат: Галя (єдина дитина, якій 11 років).
DMAX
Функція DMAX повертає максимальне значення комірки (поля) в базі даних (у всіх записах), що відповідає зазначеним умовам пошуку.
Синтаксис
DMAX(база даних; поле бази даних; умови пошуку)
Приклад
Щоб дізнатися вагу найважчої дитини в кожному класі (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:
=DMAX(A1:E10; "Вага"; A13:E14)
У стовпець "Клас" введіть 1, 2, 3 і т. д. Після вводу значення для класу буде відображена вага найважчої дитини в цьому класі.
DMIN
Функція DMIN повертає мінімальне значення комірки (поля) в базі даних (у всіх записах), що відповідає зазначеним умовам пошуку.
Синтаксис
DMIN(база даних; поле бази даних; умови пошуку)
Приклад
Щоб дізнатися найменшу відстань до школи для дітей кожного класу (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:
=DMIN (A1:E10;"Відстань до школи";A13:E14)
У клітинку рядка 14 і стовпця "Клас" введіть 1, 2, 3, і т. д. Буде відображено найменшу відстань до школи для кожного класу.
DPRODUCT
Функція DPRODUCT перемножує всі комірки діапазону даних, вміст яких відповідає умовам пошуку.
Синтаксис
DPRODUCT(база даних; поле бази даних; умови пошуку)
Приклад
У наведеному вище прикладі про дні народження цю функцію застосувати неможливо.
DSTDEV
Функція DSTDEV служить для розрахунку стандартного відхилення для сукупності комірок на основі зразка. Для цього використовуються цифри в колонці бази даних, який відповідає зазначеним умовам. Записи обробляються як зразок даних. Це означає, що діти в наведеному вище прикладі представляють всіх дітей в цілому. Зверніть увагу, що репрезентативний результат можна отримати тільки для зразка з тисячі записів і більше.
Синтаксис
DSTDEV(база даних; поле бази даних; умови пошуку)
Приклад
Щоб визначити стандартне відхилення ваги для всіх дітей одного віку (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:
=DSTDEV(A1:E10;"Вага";A13:E14)
У клітинку рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. Показаний результат являє собою стандартне відхилення ваги для всіх дітей цього віку.
DSTDEVP
Функція DSTDEVP визначає стандартне відхилення для вибірки усіх клітинок діапазону даних, вміст яких відповідає умовам пошуку. Записи з прикладу обробляються як вся сукупність.
Синтаксис
DSTDEVP(база даних; поле бази даних; умови пошуку)
Приклад
Щоб визначити стандартне відхилення ваги для всіх дітей одного віку на дні народження Ігоря (з наведеного вище прикладу), введіть у клітинку B16 наступну формулу:
=DSTDEVP (A1:E10; "Вага"; A13:E14)
У клітинк рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. Результат являє собою стандартне відхилення ваги для всіх дітей одного віку, вага яких була введена.
DSUM
Функція DSUM повертає загальну суму для всіх комірок рядків (записів) в поле бази даних, вміст яких відповідає зазначеним критеріям пошуку.
Синтаксис
DSUM(база даних; поле бази даних; умови пошуку)
Приклад
Щоб обчислити суму відстаней до школи для всіх дітей-другокласників на дні народження Ігоря (див. приклад вище), введіть у клітинку B16 наступну формулу:
=DSUM(A1:E10;"Відстань до школи";A13:E14)
Введіть значення2 у рядок 14 під заголовком "Клас". Буде відображена сума відстаней до школи (1950) для всіх дітей, які ходять у другий клас.
DVAR
Функція DVAR повертає дисперсію для всіх комірок рядків (записів) в поле бази даних, вміст яких відповідає зазначеним критеріям пошуку. Записи з прикладу обробляються як зразок даних. Репрезентативний результат можна отримати тільки для вибірки з тисячі записів і більше.
Синтаксис
DVAR(база даних; поле бази даних; умови пошуку)
Приклад
Щоб обчислити дисперсію ваги для всіх дітей одного віку (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:
=DVAR(A1:E10;"Вага";A13:E14)
У комірку рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. В результаті буде відображена дисперсія значень ваги для всіх дітей цього віку.
DVARP
Функція DVARP повертає дисперсію для всіх значень комірок всіх записів у полі бази даних, вміст яких відповідає зазначеним умовам пошуку. Записи з прикладу обробляються як вся сукупність.
Синтаксис
DVARP(база даних; поле бази даних; умови пошуку)
Приклад
Щоб визначити дисперсію ваги для всіх дітей одного віку на дні народження Ігоря (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:
=DVARP(A1:E10; "Вага"; A13:E14)
В комірку рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. Буде відображена дисперсія ваги для всіх дітей вказаного віку на дні народження Ігоря.