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

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

Піктограма Примітка

Категорію "База даних" можна сплутати з базою даних, інтегрованої у 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 Calc - Обчислити, щоб визначити поведінку LibreOffice Calc при пошуку ідентичних записів.

Див. також сторінку Wiki про Умовний підрахунок і сумування.

DAVERAGE

Функція DAVERAGE повертає середнє значення для всіх клітинок (полів) у всіх рядках (записах бази даних), які відповідають умовам пошуку.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DAVERAGE(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

Щоб обчислити середню вагу всіх дітей одного віку (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:

=DAVERAGE(A1:E10;"Вага";A13:E14)

У комірку рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. Буде відображено середню вагу дітей одного віку.

DCOUNT

Функція DCOUNT повертає кількість рядків (записів) бази даних, що задовольняють певним умовам пошуку і містять числові значення, в колонці "поле" бази даних".

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

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 служить для підрахунку в базі даних кількості рядків (записів), які відповідають обраним критеріям пошуку і містять числові або буквено-числові значення.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DCOUNTA(база даних; [DatabaseField]; критерій пошуку)

Якщо аргумент поле бази даних пропущений, то DCOUNTA повертає число усіх записів, які задовольняють критерій. Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

У наведеному вище прикладі можна визначити кількість дітей, чиї імена починаються з "Ж" або наступних букв. Змініть формулу у клітинці B16 наступним чином =DCOUNTA(A1:E10;"Ім'я";A13:E14). Видаліть попередні критерії пошуку введіть >=F в клітинку А14 стовпці "Ім'я". Результат – 5. Тепер при видаленні всіх числових значень для імені "Женя" у рядку 8 результат зміниться на 4. Рядок 8 більше не враховується, бо в ньому відсутні значення. Ім'я "Женя" являє собою текст, а не значення. Слід зазначити, що параметр "Поле" бази даних" повинен визначати стовпець, який може містити значення.

DGET

Функція DGET повертає вміст комірки з посиланням, що відповідає вказаним умовам пошуку. У разі виникнення помилки функція повертає або #VALUE! для незнайдених рядків, або Помилка:502 для кількох знайдених комірок.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DGET(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

У наведеному вище прикладі потрібно визначити, в який клас ходить дитина, ім'я якого було введено в клітинку А14. Формула, введена в комірку B16, трохи відрізняється від попередніх прикладів, бо для поля бази даних можна ввести тільки один стовпець (поле бази даних). Введіть наступну формулу:

=DGET(A1:E10;"Клас";A13:E14)

Введіть ім'я Єгор в клітинку А14. Отриманий результат буде дорівнює 2. Єгор ходить у другий клас. Введіть формулу "Вік" замість "Клас" і отримаєте вік Єгора.

Введіть значення 11 тільки в комірку C14, а потім видаліть з рядка всі інші значення. Змініть формулу у клітинці B16 наступним чином:

=DGET(A1:E10;"Ім'я";A13:E14)

Замість класу запитується ім'я. Очікуваний результат: Галя (єдина дитина, якій 11 років).

DMAX

Функція DMAX повертає максимальне значення комірки (поля) в базі даних (у всіх записах), що відповідає зазначеним умовам пошуку.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DMAX(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

Щоб дізнатися вагу найважчої дитини в кожному класі (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:

=DMAX(A1:E10; "Вага"; A13:E14)

У стовпець "Клас" введіть 1, 2, 3 і т. д. Після вводу значення для класу буде відображена вага найважчої дитини в цьому класі.

DMIN

Функція DMIN повертає мінімальне значення комірки (поля) в базі даних (у всіх записах), що відповідає зазначеним умовам пошуку.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DMIN(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

Щоб дізнатися найменшу відстань до школи для дітей кожного класу (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:

=DMIN (A1:E10;"Відстань до школи";A13:E14)

У клітинку рядка 14 і стовпця "Клас" введіть 1, 2, 3, і т. д. Буде відображено найменшу відстань до школи для кожного класу.

DPRODUCT

Функція DPRODUCT перемножує всі комірки діапазону даних, вміст яких відповідає умовам пошуку.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DPRODUCT(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

У наведеному вище прикладі про дні народження цю функцію застосувати неможливо.

DSTDEV

Функція DSTDEV служить для розрахунку стандартного відхилення для сукупності комірок на основі зразка. Для цього використовуються цифри в колонці бази даних, який відповідає зазначеним умовам. Записи обробляються як зразок даних. Це означає, що діти в наведеному вище прикладі представляють всіх дітей в цілому. Зверніть увагу, що репрезентативний результат можна отримати тільки для зразка з тисячі записів і більше.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DSTDEV(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

Щоб визначити стандартне відхилення ваги для всіх дітей одного віку (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:

=DSTDEV(A1:E10;"Вага";A13:E14)

У клітинку рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. Показаний результат являє собою стандартне відхилення ваги для всіх дітей цього віку.

DSTDEVP

Функція DSTDEVP визначає стандартне відхилення для вибірки усіх клітинок діапазону даних, вміст яких відповідає умовам пошуку. Записи з прикладу обробляються як вся сукупність.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DSTDEVP(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

Щоб визначити стандартне відхилення ваги для всіх дітей одного віку на дні народження Ігоря (з наведеного вище прикладу), введіть у клітинку B16 наступну формулу:

=DSTDEVP (A1:E10; "Вага"; A13:E14)

У клітинк рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. Результат являє собою стандартне відхилення ваги для всіх дітей одного віку, вага яких була введена.

DSUM

Функція DSUM повертає загальну суму для всіх комірок рядків (записів) в поле бази даних, вміст яких відповідає зазначеним критеріям пошуку.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DSUM(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

Щоб обчислити суму відстаней до школи для всіх дітей-другокласників на дні народження Ігоря (див. приклад вище), введіть у клітинку B16 наступну формулу:

=DSUM(A1:E10;"Відстань до школи";A13:E14)

Введіть значення2 у рядок 14 під заголовком "Клас". Буде відображена сума відстаней до школи (1950) для всіх дітей, які ходять у другий клас.

DVAR

Функція DVAR повертає дисперсію для всіх комірок рядків (записів) в поле бази даних, вміст яких відповідає зазначеним критеріям пошуку. Записи з прикладу обробляються як зразок даних. Репрезентативний результат можна отримати тільки для вибірки з тисячі записів і більше.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DVAR(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

Щоб обчислити дисперсію ваги для всіх дітей одного віку (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:

=DVAR(A1:E10;"Вага";A13:E14)

У комірку рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. В результаті буде відображена дисперсія значень ваги для всіх дітей цього віку.

DVARP

Функція DVARP повертає дисперсію для всіх значень комірок всіх записів у полі бази даних, вміст яких відповідає зазначеним умовам пошуку. Записи з прикладу обробляються як вся сукупність.

Пошук підтримує регулярні вирази. Наприклад, можна ввести "all.*", щоб знайти перше входження "all", після якого слідує довільна кількість символів. Щоб виконати пошук тексту, який також є регулярним виразом, слід ввести символи \ перед кожним символом. Щоб увімкнути або вимкнути автоматичну оцінку регулярних виразів, користуйтесь командою - LibreOffice Calc - Обчислення.

Синтаксис

DVARP(база даних; поле бази даних; умови пошуку)

Для поля бази даних можна ввести посилання на клітинку заголовка або числа, щоб визначити стовпець, в розділі Database, починаючи з 1. Щоб звернутися до стовпця через буквальне значення ім'я його заголовка, ви повинні помістити цю назву в лапки.

Приклад

Щоб визначити дисперсію ваги для всіх дітей одного віку на дні народження Ігоря (з наведеного вище прикладу), введіть у комірку B16 наступну формулу:

=DVARP(A1:E10; "Вага"; A13:E14)

В комірку рядка 14 і стовпця "Вік" введіть 7, 8, 9 і т. д. Буде відображена дисперсія ваги для всіх дітей вказаного віку на дні народження Ігоря.