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

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

note

Категорію "База даних" можна сплутати з базою даних, інтегрованої у 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)

Параметри для функції бази даних:

Нижче наведені визначення параметрів для всіх функцій бази даних:

База даних - це список комірок, що визначають базу даних . Перший рядок списку містить назви полів, а наступні рядки - це записи з відповідними значеннями полів.

DatabaseField specifies the column where the function operates on after the search criteria of the first parameter is applied and the data rows are selected. It is not related to the search criteria itself. For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

SearchCriteria is the cell range containing search criteria. Like Database, its first row is also field names, and following rows are conditions for related fields. If you write several criteria in one row they are connected by AND. If you write the criteria in different rows they are connected by OR. Empty cells in the search criteria range will be ignored.

Choose - LibreOffice Calc - Calculate to define how LibreOffice Calc acts when searching for identical entries.

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

DAVERAGE

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

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

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

DCOUNT

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

Якщо аргумент поле бази даних пропущений, то DCOUNT повертає число усіх записів, які задовольняють критерій. For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

If you choose to omit the DatabaseField argument, your formula should be of the form =DCOUNT(Database; ; SearchCriteria).

Example

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

Якщо аргумент поле бази даних пропущений, то DCOUNTA повертає число усіх записів, які задовольняють критерій. For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

If you choose to omit the DatabaseField argument, your formula should be of the form =DCOUNTA(Database; ; SearchCriteria).

Example

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

DGET

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

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

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

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

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

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

DMAX

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

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

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

DMIN

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

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

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

DPRODUCT

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

DSTDEV

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

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

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

DSTDEVP

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

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

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

DSUM

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

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

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

DVAR

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

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

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

DVARP

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

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Syntax

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

For the DatabaseField parameter you can enter a reference to a header cell or a number to specify the column within the Database area, starting with 1. To reference a column by means of the literal column header name (from the first row of Database range), place quotation marks around the header name.

Example

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

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

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

Будь ласка, підтримайте нас!