Функции за бази от данни

Обзор

Дванадесетте функции в категорията „Бази от данни“ ви помагат да анализирате проста база от данни, разположена в правоъгълна област от колони и редове в работен лист, в която данните са организирани с по един ред за всеки запис. Заглавната клетка на всяка колона показва името на колоната, което обикновено отразява съдържанието на клетките в тази колона.

Функциите в категорията „Бази от данни“ приемат три аргумента, както следва:

БазаОтДанни. Диапазонът от клетки на базата от данни.
ПолеОтБД. Колоната с данните, които да се използват в изчислението на функцията.
КритерииЗаТърсене. Диапазонът от клетки на отделна област от работния лист, съдържаща критерии за търсене.

Следва по-подробно описание на тези аргументи.

Всички функции работят по сходен начин. Първата логическа стъпка е чрез зададените КритерииЗаТърсене да се намери подмножеството от записи в БазаОтДанни, които трябва да се използват при следващите изчисления. Втората стъпка е да се извлекат стойностите на данните и да се извършат изчисленията, свързани с конкретната функция (средна стойност, сбор, произведение и т.н.). Обработват се стойностите в колоната ПолеОтБД на избраните записи.

При изчисляване на тези функции Calc третира датите и логическите стойности (TRUE и FALSE) като числа.

Аргументи на функциите за бази от данни

Следните дефиниции на аргументи важат за всички функции в категорията „Бази от данни“:

Аргумент „БазаОтДанни“

БазаОтДанни указва диапазона от клетки, заеман от таблицата на базата от данни. Първият ред на диапазона съдържа имената на полетата, а следващите редове са записи със съответните стойности на тези полета.

Един начин да се дефинира диапазонът от клетки е да се въведе обръщението към горната лява клетка, последвано от двоеточие (:) и обръщение към долната дясна клетка. Например A1:E10.

Аргументът БазаОтДанни може да се зададе и като се предаде името на наименуван диапазон или диапазон на база от данни. Използването на смислено име за дефиниране на диапазона от клетки може да подобри четливостта на формулата и да улесни поддръжката на документа. Ако името не отговаря на това на дефиниран диапазон, Calc докладва грешка #NAME?.

Други грешки, които могат да възникнат в резултат на невалиден аргумент БазаОтДанни, са #VALUE! и Гр:504 (грешка в списъка с параметри).
Аргумент „ПолеОтБД“

ПолеОтБД указва колоната, която функцията ще използва за изчисленията си, след като се приложат критериите за търсене и се изберат редовете с данни. Тя не е свързана с критериите за търсене.

Задайте аргумента ПолеОтБД по някой от следните начини:
- Като въведете обръщение към заглавна клетка в областта БазаОтДанни. Също така, ако на клетката е дадено смислено име като наименуван диапазон или диапазон на база от данни, можете да въведете това име. Ако името не отговаря на това на наименуван диапазон, Calc докладва грешка #Name?. Ако името е валидно, но не отговаря само на една клетка, Calc докладва грешка Гр:504 (грешка в списъка с параметри).
- Като въведете число, за да укажете колоната в областта БазаОтДанни, започвайки от 1. Например, ако БазаОтДанни заема диапазона от клетки D6:H123, въведете 3, за да посочите заглавната клетка на адрес F6. Calc очаква целочислена стойност в интервала от 1 до броя на колоните, дефинирани в БазаОтДанни, и игнорира дробната част. Ако стойността е по-малка от 1, Calc докладва грешка Гр:504 (грешка в списъка с параметри). Ако стойността е по-голяма от броя на колоните в БазаОтДанни, Calc докладва грешка #VALUE!.
- Като въведете литерал – заглавие на колона от първия ред на диапазона БазаОтДанни, оградено с кавички. Например "Разстояние до училище". Ако низът не съвпада с нито едно едно от заглавията на колони в областта БазаОтДанни, Calc докладва грешка Гр:504 (грешка в списъка с параметри). Можете също така да подадете обръщение към произволна клетка (извън областите БазаОтДанни и КритерииЗаТърсене), която съдържа необходимия низ.
Аргументът ПолеОтБД е незадължителен за функциите DCOUNT и DCOUNTA, но се изисква за останалите десет функции за бази от данни.
Аргумент „КритерииЗаТърсене“

КритерииЗаТърсене указва диапазон от клетки, съдържащ критерии за търсене. Както и БазаОтДанни, първият му ред е от имена на полета, а останалите редове представляват условия за посочените полета. За областите БазаОтДанни и КритерииЗаТърсене не е необходимо да са съседни или дори да са на един и същ лист.

Един начин да се дефинира диапазон от клетки е да се въведе обръщението към горната лява клетка, последвано от двоеточие (:) и обръщението към долната дясна клетка. Например A13:B14. Диапазонът от клетки може да бъде зададен и като се подаде името на наименуван диапазон или диапазон на база от данни. Ако името не отговаря на това на наименуван диапазон, Calc докладва грешка #NAME?.

Може да бъде върната и Гр:504 (грешка в списъка с параметри), ако аргументът КритерииЗаТърсене е невалиден.

Съдържанието на областта КритерииЗаТърсене е описано по-подробно в следващия раздел.

Задаване на критериите за търсене

Броят на колоните, заемани от областта КритерииЗаТърсене, може да не е същият като ширината на областта БазаОтДанни. Всички заглавия, присъстващи в първия ред на КритерииЗаТърсене, трябва да са идентични на заглавия от първия ред на БазаОтДанни. Не е задължително обаче всички заглавия от БазаОтДанни да присъстват в първия ред на КритерииЗаТърсене, освен това заглавие от БазаОтДанни може да е включено няколко пъти в първия ред на КритерииЗаТърсене.

Критериите за търсене се въвеждат в клетките от втория и следващите редове на областта КритерииЗаТърсене, под реда със заглавията. Празните клетки в областта КритерииЗаТърсене се игнорират.

Задайте критериите в клетките на областта КритерииЗаТърсене чрез операциите за сравнение <, <=, =, <>, >= и >. = се подразбира, ако клетката не е празна, но не започва с операция за сравнение.

Ако напишете няколко критерия в един ред, те се свързват с И. Ако напишете няколко критерия в различни редове, те се свързват с ИЛИ.

Критериите могат да се задават със заместващи знаци, стига те да са разрешени чрез настройката Заместващи знаци във формулите в диалога - PRODUCTNAME Calc - Изчисляване. Когато за електронната ви таблица е важна съвместимостта с Excel, тази настройка трябва да е включена.

Още по-мощни критерии могат да бъдат задавани чрез регулярни изрази, ако последните са разрешени с настройката Регулярни изрази във формулите в диалога - LibreOffice Calc - Изчисляване.

Когато се използват функции, в които един или няколко аргумента са низове с критерии за търсене – регулярни изрази, първо се прави опит низовете с критерии да се преобразуват в числа. Например ".0" ще се преобразува в 0.0 и т.н. При успех няма да се използва сравняване по регулярен израз, а сравняване на числа. Ако обаче се превключи към локал, в който десетичният разделител не е точка, ще сработи преобразуването към регулярен израз. За да наложите изчисляване на регулярен израз вместо числов, използвайте израз, който не може да бъде изтълкуван като число, например ".[0]", ".\0" или "(?i).0".

Друга настройка, която влияе върху обработката на критериите за търсене, е Критериите за търсене = и <> се отнасят за цели клетки в диалога - LibreOffice Calc - Изчисляване. Тя указва дали критериите за търсене, които задавате за функциите за бази от данни, трябва да се отнасят точно за цялата клетка. Когато за електронната ви таблица е важна съвместимостта с Excel, тази настройка трябва да е включена.

Примери за употреба на функциите за бази от данни

Ще използваме следващата примерна таблица с данни за демонстрация на употребата на функциите от категорията „Бази от данни“. Диапазонът от клетки A1:E10 съдържа фиктивна информация за гостите, поканени на рождения ден на Иван. За всеки гост е дадена следната информация: име, клас, възраст в години, разстояние до училището в метри и тегло в килограми.

	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

В следващите шест примера е използвана горната таблица, комбинирана с различни области с критерии за търсене.

Пример 1

	A	B	C	D	E
12	Име	Клас	Възраст	Разстояние	Тегло
13				>600
14

Подобно на този прост пример, понякога е подходящо (но не задължително) критериите за търсене да се разположат точно под таблицата с данни, така че колоните в двете области да са подравнени вертикално. Празните записи в областта с критерии за търсене се игнорират. С горната примерна таблица с данни и тази област с критерии за търсене вмъкнете формулата =DCOUNT(A1:E10;;A12:E14) в празна клетка другаде в листа, за да преброите колко от гостите на Иван изминават повече от 600 метра до училище. Резултатът е 5 (Бистра, Георги, Даниела, Здравко и Ирена).

Обърнете внимание и че формулата =DCOUNT(A1:E10;;D12:D13) връща точно същата стойност, което показва, че за областта с критерии за търсене е достатъчно да съдържа само съществените заглавия на колони.

Пример 2

	A	B	C	D	E
12		Възраст	Клас
13		>7	2
14

В този пример областта с критерии за търсене съдържа само две заглавия и те не са вертикално подравнени по съответните заглавия в примерната таблица с данни. Тъй като има две условия в един ред, те се свързват с И. С горната примерна таблица с данни и тази област с критерии за търсене вмъкнете формулата =DCOUNT(A1:E10;;B12:C13) в празна клетка другаде в листа, за да преброите колко от гостите на Иван са във втори клас и са на повече от 7 години. Резултатът е 2 (Даниела и Ирена).

Пример 3

	A	B	C	D	E
12		Възраст
13		9
14		10

В този пример областта с критерии за търсене съдържа само едно заглавие. Тъй като има две условия в последователни редове, те се свързват с ИЛИ. С горната примерна таблица с данни и тази област с критерии за търсене вмъкнете формулата =DCOUNT(A1:E10;;B12:B14) в празна клетка другаде в листа, за да преброите колко от гостите на Иван са на 9 или 10 години. Резултатът е 4 (Андрей, Бистра, Веселин и Здравко).

Пример 4

	A	B	C	D	E
12		Възраст	Възраст
13		>=8	<=10
14

В този пример областта с критерии за търсене съдържа два пъти едно и също заглавие. Тъй като има две условия в един и същ ред, те се свързват с И. С горната примерна таблица с данни и тази област с критерии за търсене вмъкнете формулата =DCOUNT(A1:E10;;B12:C13) в празна клетка другаде в листа, за да преброите колко от гостите на Иван са на възраст между 8 и 10 години (включително). Резултатът е 6 (Андрей, Бистра, Веселин, Даниела, Здравко и Ирена).

Пример 5

	A	B	C	D	E
12	Име
13	F*
14

Този прост пример показва употребата на заместващи знаци. За да работи както е предвидено, разрешете заместващите знаци в - LibreOffice Calc - Изчисляване. С горната примерна таблица с данни и тази област с критерии за търсене вмъкнете формулата =DCOUNT(A1:E10;;A12:A13) в празна клетка другаде в листа, за да преброите колко от гостите на Иван имат имена, започващи с буквата „Е“. Резултатът е 1 (Емил).

Пример 6

	A	B	C	D	E
12	Име
13	[ABC].*
14

Този прост пример показва употребата на регулярни изрази. За да работи както е предвидено, разрешете регулярните изрази в - LibreOffice Calc - Изчисляване. С горната примерна таблица с данни и тази област с критерии за търсене вмъкнете формулата =DCOUNT(A1:E10;;A12:A13) в празна клетка другаде в листа, за да преброите колко от гостите на Иван имат имена, започващи с буквите „А“, „Б“ или „В“. Резултатът е 3 (Андрей, Бистра и Веселин).

DAVERAGE

DAVERAGE изчислява средната стойност на числата в клетките (полетата) на указаната колона за всички редове (записи с данни), които отговарят на зададените критерии за търсене. Нечисловите стойности в тези клетки се игнорират.

Връща грешка #DIV/0!, ако никой запис не отговаря на зададените критерии за търсене, или ако в клетките на съответстващите записи от указаната колона няма числови стойности.

Синтаксис

DAVERAGE(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Примерната таблица с данни за гостите на рождения ден на Иван (описана по-горе) трябва да заема клетките A1:E10. Съдържанието на клетките A12:E12 трябва да е идентично на заглавията от таблицата с данни в клетките A1:E1. Уверете се, че клетките A13:E13 са празни, с изключение на клетката D13, която трябва да съдържа „>0“ (този критерий за търсене е предназначен да съответства на всички записи в базата от данни).

Вмъкнете формулата =DAVERAGE(A1:E10; "Разстояние"; A12:E13) в празна клетка другаде в листа, за да намерите средното разстояние в метри, което гостите на партито на Иван изминават до училище. Резултатът е 666,67.

DCOUNT

DCOUNT брои клетките (полетата) на указаната колона, които съдържат числови стойности, за всички редове (записи в базата от данни), които отговарят на зададените критерии за търсене. Ако не е зададена колона, DCOUNT връща броя на всички записи, които отговарят на зададените критерии за търсене, независимо от съдържанието им.

Синтаксис

DCOUNT(БазаОтДанни; [ПолеОтБД]; КритерииЗаТърсене)

Пример

Примерната таблица с данни за гостите на рождения ден на Иван (описана по-горе) трябва да заема клетките A1:E10. Съдържанието на клетките A12:E12 трябва да е идентично на заглавията от таблицата с данни в клетките A1:E1. Уверете се, че клетките A13:E13 са празни, с изключение на клетката D13, която трябва да съдържа „>600“ (този критерий за търсене ще съответства на записи в базата от данни, които съдържат стойност, по-голяма от 600, в колоната „Разстояние“).

Вмъкнете формулата =DCOUNT(A1:E10;; A12:E13) в празна клетка другаде в листа, за да преброите колко от гостите на Иван изминават повече от 600 метра до училище. Резултатът е 5.

Същият резултат се получава и с формулата =DCOUNT(A1:E10; "Разстояние"; A12:E13), тъй като всички записи в колоната „Разстояние“ са числови. Ако обаче използвате формулата =DCOUNT(A1:E10; "Име"; A12:E13), се получава стойността 0, защото всички записи в колоната „Име“ са нечислови.

DCOUNTA

DCOUNTA брои клетките (полетата) на указаната колона, които не са празни, за всички редове (записи в базата от данни), които отговарят на зададените критерии за търсене. Празните клетки в избраната колона не се броят. Ако не е зададена колона, DCOUNTA връща броя на всички записи, които отговарят на зададените критерии за търсене, независимо от съдържанието им.

Синтаксис

DCOUNTA(БазаОтДанни; [ПолеОтБД]; КритерииЗаТърсене)

Пример

Примерната таблица с данни за гостите на рождения ден на Иван (описана по-горе) трябва да заема клетките A1:E10. Съдържанието на клетките A12:E12 трябва да е идентично на заглавията от таблицата с данни в клетките A1:E1. Уверете се, че клетките A13:E13 са празни, с изключение на клетката D13, която трябва да съдържа „>600“ (този критерий за търсене ще съответства на записи в базата от данни, които съдържат стойност, по-голяма от 600, в колоната „Разстояние“).

Вмъкнете формулата =DCOUNTA(A1:E10;; A12:E13) в празна клетка другаде в листа, за да преброите колко от гостите на Иван изминават повече от 600 метра до училище. Резултатът е 5.

Същият резултат се получава и ако използвате формулата =DCOUNTA(A1:E10; "Разстояние"; A12:E13) или =DCOUNTA(A1:E10; "Име"; A12:E13). Последното отразява факта, че за разлика от DCOUNT, DCOUNTA брои и числовите, и буквено-цифровите стойности в колоната, указана от аргумента ПолеОтБД.

DGET

DGET връща съдържанието на клетката (полето) от указаната колона за единствения ред (запис в базата от данни), който отговаря на зададените критерии за търсене.

Calc докладва Гр:502 (невалиден аргумент), ако се намерят няколко съвпадения, или грешка #VALUE! (погрешен тип данни), ако не бъде намерено съвпадение. Грешка #VALUE! се докладва и ако е намерено точно едно съвпадение, но съответната клетка е празна.

Синтаксис

DGET(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Примерната таблица с данни за гостите на рождения ден на Иван (описана по-горе) трябва да заема клетките A1:E10. Съдържанието на клетките A12:E12 трябва да е идентично на заглавията от таблицата с данни в клетките A1:E1. Уверете се, че клетките A13:E13 са празни, с изключение на клетката C13, която трябва да съдържа „11“ (този критерий за търсене ще съответства на записи в базата от данни, които съдържат стойност 11 в колоната „Възраст“).

Вмъкнете формулата =DGET(A1:E10; "Име"; A12:E13) в празна клетка другаде в листа, за да намерите името на този гост на партито на Иван, чиято възраст е 11 години. Резултатът е името Георги.

Ако промените стойността в клетката C13 на „10“, формулата =DGET(A1:E10; "Име"; A12:E13) връща грешка за невалиден аргумент (Гр:502). Причината е, че повече от един запис отговаря на зададения критерий (и Бистра, и Веселин са на 10 години).

DMAX

DMAX изчислява максималната стойност от клетките (полетата) на указаната колона, които съдържат числови стойности, за всички редове (записи с данни), които отговарят на зададените критерии за търсене. Празните клетки и клетките, съдържащи нечислови знаци, не се включват.

Връща 0, ако не са намерени съвпадения, или ако в клетките от съответстващите записи в указаната колона няма ненулеви числови стойности.

Синтаксис

DMAX(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Вмъкнете формулата =DMAX(A1:E10; "Разстояние"; A12:E13) в празна клетка другаде в листа, за да намерите максималното разстояние в метри, което някой от гостите на партито на Иван изминава до училище. Резултатът е 1200.

DMIN

DMIN изчислява минималната стойност от клетките (полетата) на указаната колона, които съдържат числови стойности, за всички редове (записи с данни), които отговарят на зададените критерии за търсене. Празните клетки и клетките, съдържащи нечислови знаци, не се включват.

Синтаксис

DMIN(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Вмъкнете формулата =DMIN(A1:E10; "Разстояние"; A12:E13) в празна клетка другаде в листа, за да намерите минималното разстояние в метри, което някой от гостите на партито на Иван изминава до училище. Резултатът е 150.

DPRODUCT

DPRODUCT изчислява произведението на всички числови стойности в клетките (полетата) на указаната колона, за всички редове (записи с данни), които отговарят на зададените критерии за търсене. Празните клетки и клетките, съдържащи нечислови знаци, не се включват.

Връща 0, ако не са намерени съответствия, или ако в клетките на указаната колона няма числови стойности за съответстващите записи.

Синтаксис

DPRODUCT(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Примерната таблица с данни за гостите на рождения ден на Иван (описана по-горе) трябва да заема клетките A1:E10. Съдържанието на клетките A12:E12 трябва да е идентично на заглавията от таблицата с данни в клетките A1:E1. Уверете се, че клетките A13:E13 са празни, с изключение на клетката C13, която трябва да съдържа „>0“ (този критерий за търсене е предназначен да съответства на всички записи в базата от данни).

Вмъкнете формулата =DPRODUCT(A1:E10; "Възраст"; A12:E13) в празна клетка другаде в листа, за да изчислите произведението на възрастите в години на гостите на партито на Иван. Резултатът е 279417600.

DSTDEV

DSTDEV изчислява стандартното отклонение на извадка на базата на числовите стойности в клетките (полетата) на указаната колона за всички редове (записи с данни), които отговарят на зададените критерии за търсене. Нечисловите стойности се игнорират.

Връща грешка #NUM!, ако точно един запис отговаря на зададените критерии за търсене, или ако в клетките на съответстващите записи от указаната колона има само една числова стойност.

Синтаксис

DSTDEV(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Примерната таблица с данни за гостите на рождения ден на Иван (описана по-горе) трябва да заема клетките A1:E10. Съдържанието на клетките A12:E12 трябва да е идентично на заглавията от таблицата с данни в клетките A1:E1. Уверете се, че клетките A13:D13 са празни, а клетката E13 съдържа „>0“ (този критерий за търсене е предназначен да съответства на всички записи в базата от данни).

Вмъкнете формулата =DSTDEV(A1:E10; "Тегло"; A12:E13) в празна клетка другаде в листа, за да изчислите стандартното отклонение на теглата в килограми на гостите на партито на Иван. Резултатът е 5,5.

DSTDEVP

DSTDEVP изчислява стандартното отклонение на популация на базата на числовите стойности в клетките (полетата) на указаната колона за всички редове (записи с данни), които отговарят на зададените критерии за търсене. Нечисловите стойности се игнорират.

Връща грешка #NUM!, ако никой запис не отговаря на зададените критерии за търсене, или ако в клетките на съответстващите записи от указаната колона няма числови стойности.

Синтаксис

DSTDEVP(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Примерната таблица с данни за гостите на рождения ден на Иван (описана по-горе) трябва да заема клетките A1:E10. Съдържанието на клетките A12:E12 трябва да е идентично на заглавията от таблицата с данни в клетките A1:E1. Уверете се, че клетките A13:D13 са празни, а клетката E13 съдържа „>0“ (този критерий за търсене е предназначен да съответства на всички записи в базата от данни).

Вмъкнете формулата =DSTDEVP(A1:E10; "Тегло"; A12:E13) в празна клетка другаде в листа, за да изчислите стандартното отклонение на популация на теглата в килограми на гостите на партито на Иван. Резултатът е 5,18545.

DSUM

DSUM изчислява сумата на всички числови стойности в клетките (полетата) на указаната колона за всички редове (записи с данни), които отговарят на зададените критерии за търсене. Празните клетки и клетките, съдържащи нечислови знаци, не се включват.

Синтаксис

DSUM(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Вмъкнете формулата =DSUM(A1:E10; "Разстояние"; A12:E13) в празна клетка другаде в листа, за да намерите общото разстояние в метри, което всички гости на партито на Иван изминават до училище. Резултатът е 6000.

DVAR

DVAR изчислява дисперсията на извадка на базата на числовите стойности в клетките (полетата) на указаната колона за всички редове (записи с данни), които отговарят на зададените критерии за търсене. Нечисловите стойности се игнорират.

Синтаксис

DVAR(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Вмъкнете формулата =DVAR(A1:E10; "Разстояние"; A12:E13) в празна клетка другаде в листа, за да намерите дисперсията на извадка на разстоянията в метри, които гостите на партито на Иван изминават до училище. Резултатът е 193125.

DVARP

DVARP изчислява дисперсията на популация на базата на числовите стойности в клетките (полетата) на указаната колона за всички редове (записи с данни), които отговарят на зададените критерии за търсене. Нечисловите стойности се игнорират.

Синтаксис

DVARP(БазаОтДанни; ПолеОтБД; КритерииЗаТърсене)

Пример

Вмъкнете формулата =DVARP(A1:E10; "Разстояние"; A12:E13) в празна клетка другаде в листа, за да намерите дисперсията на популация на разстоянията в метри, които гостите на партито на Иван изминават до училище. Резултатът е 171666,67.

Функции по категория