Функция СРЗНАЧЕСЛИМН

Возвращает среднее арифметическое всех ячеек в диапазоне, содержимое которых удовлетворяет нескольким заданным условиям. Функция СРЗНАЧЕСЛИМН суммирует все результаты, которые проходят логические проверки, и делит эту сумму на количество выбранных значений.

tip

Эта функция доступна начиная с LibreOffice 4.0.


Синтаксис

СРЗНАЧЕСЛИМН(Func_Range; Range1; Criterion[; Range2; Criterion2][; … ; [Range127; Criterion127]])

Диапазон функции — обязательный аргумент. Диапазон ячеек, имя именованного диапазона, либо заголовок столбца или строки, содержащих значения для вычисления среднего значения.

Диапазон 1 — необходимый аргумент. Представляет собой диапазон ячеек, имя именованного диапазона, либо название столбца или строки, к которому применяется соответствующее условие.

Criterion: A criterion is a single cell Reference, Number or Text. It is used in comparisons with cell contents.

Ссылка на пустую ячейку интерпретируется как числовое значение 0.

Совпадающим выражением может быть:

Диапазон 2 — Необязательный параметр. Диапазон 2 и все последующие поля имеет то же значение, что и Диапазон 1.

Условие 2 — Необязательный параметр. Условие 2 и все последующие поля имеет то же значение, что и Условие.

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 regular expression metacharacter or operator 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".


Диапазон функции и Диапазон 1, Диапазон 2... должны иметь одинаковый размер, в противном случае функция возвращает ошибку:502 - Недопустимый аргумент.

note

Логическое отношение между условиями может определяться в виде логической функции И (объединение). Другими словами, если и только если все заданные условия соблюдаются, значение из соответствующей ячейки заданного Диапазона функции будет включено в вычисление.


Функция может иметь до 255 аргументов, что позволяет указать до 127 диапазонов критериев и критериев для них.

Если ячейка содержит значение ИСТИНА, то она обрабатывается как 1, если же ячейка содержит значение ЛОЖЬ, то обрабатывается как 0 (нуль).

Примеры

Рассмотрите следующую таблицу

A

B

C

1

Имя товара

Sales

Revenue

2

карандаш

20

65

3

ручка

35

85

4

тетрадь

20

190

5

книга

17

180

6

пенал

нет

нет


warning

Во всех примерах внизу диапазоны вычисления содержат строку №6, которая игнорируется, поскольку содержит текст.


Простое использование

=СРЗНАЧЕСЛИМН(B2:B6; B2:B6; ">=20")

Вычисляет среднее для значений диапазона B2:B6, которые больше или равны 20. Возвращает 25, потому что пятая строка не удовлетворяет заданному условию.

=СРЗНАЧЕСЛИМН(C2:C6; B2:B6; ">=20"; C2:C6; ">70")

Вычисляет среднее для значений диапазона C2:C6, которые больше 70 и соответствуют ячейкам B2:B6 со значениями большими или равными 20. Возвращает 137,5, потому что вторая и пятая строки не удовлетворяют по крайней мере одному условию.

Использование регулярных выражений и вложенных функций

=СРЗНАЧЕСЛИМН(C2:C6; B2:B6; ">"&МИН(B2:B6); B2:B6; "<"&МАКС(B2:B6))

Вычисляет среднее значение для значений диапазона C2:C6, которые соответствуют всем значениям диапазона B2:B6, за исключением его минимального и максимального значений. Возвращает 127,5, потому что третья и пятая строки не удовлетворяют по крайней мере одному условию.

=СРЗНАЧЕСЛИМН(C2:C6;A2:A6;"pen.*";B2:B6;"<"&MAX(B2:B6))

Рассчитывает среднее значение для значений диапазона C2:C6, которые соответствуют всем ячейкам диапазона A2:A6, начинаются с «pen» и заканчиваются всеми ячейками диапазона B2:B6, за исключением его максимального значения. Возвращает 65, потому что только вторая строка удовлетворяет всем условиям.

Ссылка на ячейку с условием

Для более удобного изменения условия функции можно указать его в отдельной ячейке и использовать ссылку на него в качестве условия функции СРЗНАЧЕСЛИМН. Например, функцию выше можно переписать следующим образом:

=СРЗНАЧЕСЛИМН(C2:C6; A2:A6; E2&".*"; B2:B6; "<"&МАКС(B2:B6))

Если E2 = pen, то функция возвращает 65, потому что ссылка на ячейку заменяется на её содержимое.

Open file with example:

note

Эта функция является частью формата Open Document для офисных приложений (OpenDocument) v1.2. (ISO/IEC 26300:2-2015)


Пожалуйста, поддержите нас!