Функтсияҳои махзани далелҳо

Ин боб бо функтсияҳои бо маълумотҳои дар як сатр ҷойдошта кор мекунанд.

note

Категорияи Махзани далелҳо мумкин аст бо махзани далелҳои LibreOffice мансуб карда шавад, ки ин ғалаи аст. Байни махзани далелҳои LibreOffice ва Махзани далелҳои категория дар LibreOffice Calc ягон робитае нест.


Далели намунавӣ:

Далелҳои зерин дар мисолҳо истифода карда мешаванд:

Диапазони A1:E10 бачаҳои ба зодрӯзи Joe даъватшударо дорад. Маъзумоти зер барои ҳар як навишт оварда шудааст: Сутуни 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

Сину сол

5


Формула дар чашмаки B16 баробари =DCOUNT(A1:E10;0;A13:E14) аст

Хосиятҳои функтсияҳои Махзани далелҳо:

Дар зер шарҳи параметрҳои функтсияҳои Махзани Далелҳо (Аз ин пас МД) оварда мешаванд:

Database is the cell range defining the database. The first row of the range contains the field names, and following rows are records with corresponding field values.

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.

See also the Wiki page about Conditional Counting and Summation.

АРЗМИЁНА

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...14 дохил кунед. Вазни миёнаи ҳамаи бачаҳо пайдо мешавад.

ШУМОРА

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(МД; Майдони МД; Критерияи ҷустуҷӯ)

If the DatabaseField argument is omitted, DCOUNT returns the count of all records that satisfy Criteria. 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 аст.

DGET

DGET таркиби чашмаки ҳаволашударо бармегардонад, ки ба критерия дуруст меояд Дар натиҷаи хато рух додан #VALUE! барои ягон сатр пайдо нашуд ё Err502 барои зиёда аз як сатр пайдо шуд бармегардонад.

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

Дар мисоли боло мо мехоҳем фаҳмем, ки бача дар кадом синф аст, ки номаш дар чашмаки A14 дохил карда шудааст. Формулаи чашмаки B16 ва каме аз мисоли пешин фарқ мекунад, зеро кӣ танҳо як сутун барои Майдони МД дохил карда мешавад. Ин формуларо дохил кунед:

=DGET(A1:E10;"Синф";A13:E14)

Номи Frank-ро дар A14 дохил карда шумо мебинед ки натиҷа 2 аст. Frank дар синфи 2 аст. "Синро" ба ҷои "Синф" дохил кунед, шумо синни Frank-ро мебинед.

Ё арзиши 11-ро дохил кунед дар C14, ва дигар чашмакҳои ин сатрро кӯр кунед. Формулаи B16 ба тарзи зайл ислоҳ кунед:

=DGET(A1:E10;"Ном";A13:E14)

Ба ҷои ном синф дархост шуд. Ҷавоб тайёр аст: Daniel ягона бачаи 11 сола мебошад.

МАКС

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 ... 10 дохил кунед. Пас аз дохил кардани синф вазни баччаи вазнинтарин пайдо мешавад.

МИН

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...9 дохил кунед. кӯтоҳтарин роҳ ба мактаб барои ҳар синф пайдо мешавад.

ҲОСИЛ

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 фарқияти стандартии аҳолиро ҳисоб мекунад, ки ба критерия ҷавобгӯ ҳастанд.

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...15 дохил кунед. Натиҷа фарҳияти вазни баччаҳоро нишон медиҳад.

ФАРҚРСТАНД

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...15 дохил кунед. Натиҷа фарҳияти вазни баччаҳоро нишон медиҳад.

СУММА

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 дар зери "Синф" дохил кунед. Суммаи масофа то мактаби ҳама мактаббачаҳои синфи 2 муайян шуд.

ФАР

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....14 ро дохил кунед.

ФАРП

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....14 ро дохил кунед.

ШУМОРАИA

шумораи сатрҳои МД-ро мешуморад, ки ба критерияи ҷустуҷӯ мувофиқанд.

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(МД; Майдони МД; Критерияи ҷустуҷӯ)

If the DatabaseField argument is omitted, DCOUNTA returns the count of all records that satisfy Criteria. 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

Дар мисоли боло метавонед бачаҳоеро кӣ номашон бо E ё калон аз он (алфавитӣ) сар мешавад ёбед. Формуларо дар B16 ислоҳ карда A пас аз номи функтсия дохил кунед DCOUNT. Критерияи кӯҳнаи ҷустуҷӯро кӯр карда >=E дохил кунед Ном дар A14. Натиҷа - 5.

Please support us!