Содержание
- Стандартный способ вычисления
- Вычисление с помощью Мастера функций
- Панель формул
- Ручной ввод функции
- Расчет среднего значения по условию
- Вопросы и ответы
В процессе различных расчетов и работы с данными довольно часто требуется подсчитать их среднее значение. Оно рассчитывается путем сложения чисел и деления общей суммы на их количество. Давайте выясним, как вычислить среднее значение набора чисел при помощи программы Microsoft Excel различными способами.
Стандартный способ вычисления
Самый простой и известный способ найти среднее арифметическое набора чисел — это воспользоваться специальной кнопкой на ленте Microsoft Excel. Выделяем диапазон чисел, расположенных в столбце или в строке документа. Находясь во вкладке «Главная», жмем на кнопку «Автосумма», которая расположена на ленте в блоке инструментов «Редактирование». Из выпадающее списка выбираем пункт «Среднее».
После этого, с помощью функции «СРЗНАЧ», производится расчет. В ячейку под выделенным столбцом, или справа от выделенной строки, выводится средняя арифметическая данного набора чисел.
Этот способ хорош простотой и удобством. Но, у него имеются и существенные недостатки. С помощью этого способа можно произвести подсчет среднего значения только тех чисел, которые располагаются в ряд в одном столбце, или в одной строке. А вот, с массивом ячеек, или с разрозненными ячейками на листе, с помощью этого способа работать нельзя.
Например, если выделить два столбца, и вышеописанным способом вычислить среднее арифметическое, то ответ будет дан для каждого столбца в отдельности, а не для всего массива ячеек.
Вычисление с помощью Мастера функций
Для случаев, когда нужно подсчитать среднюю арифметическую массива ячеек, или разрозненных ячеек, можно использовать Мастер функций. Он применяет все ту же функцию «СРЗНАЧ», известную нам по первому методу вычисления, но делает это несколько другим способом.
Кликаем по ячейке, где хотим, чтобы выводился результат подсчета среднего значения. Жмем на кнопку «Вставить функцию», которая размещена слева от строки формул. Либо же, набираем на клавиатуре комбинацию Shift+F3.
Запускается Мастер функций. В списке представленных функций ищем «СРЗНАЧ». Выделяем его, и жмем на кнопку «OK».
Открывается окно аргументов данной функции. В поля «Число» вводятся аргументы функции. Это могут быть как обычные числа, так и адреса ячеек, где эти числа расположены. Если вам неудобно вводить адреса ячеек вручную, то следует нажать на кнопку расположенную справа от поля ввода данных.
После этого, окно аргументов функции свернется, а вы сможете выделить ту группу ячеек на листе, которую берете для расчета. Затем, опять нажимаете на кнопку слева от поля ввода данных, чтобы вернуться в окно аргументов функции.
Если вы хотите подсчитать среднее арифметическое между числами, находящимися в разрозненных группах ячеек, то те же самые действия, о которых говорилось выше, проделывайте в поле «Число 2». И так до тех пор, пока все нужные группы ячеек не будут выделены.
После этого, жмите на кнопку «OK».
Результат расчета среднего арифметического будет выделен в ту ячейку, которую вы выделили перед запуском Мастера функций.
Панель формул
Существует ещё третий способ запустить функцию «СРЗНАЧ». Для этого, переходим во вкладку «Формулы». Выделяем ячейку, в которой будет выводиться результат. После этого, в группе инструментов «Библиотека функций» на ленте жмем на кнопку «Другие функции». Появляется список, в котором нужно последовательно перейти по пунктам «Статистические» и «СРЗНАЧ».
Затем, запускается точно такое же окно аргументов функции, как и при использовании Мастера функций, работу в котором мы подробно описали выше.
Дальнейшие действия точно такие же.
Ручной ввод функции
Но, не забывайте, что всегда при желании можно ввести функцию «СРЗНАЧ» вручную. Она будет иметь следующий шаблон: «=СРЗНАЧ(адрес_диапазона_ячеек(число); адрес_диапазона_ячеек(число)).
Конечно, этот способ не такой удобный, как предыдущие, и требует держать в голове пользователя определенные формулы, но он более гибкий.
Расчет среднего значения по условию
Кроме обычного расчета среднего значения, имеется возможность подсчета среднего значения по условию. В этом случае, в расчет будут браться только те числа из выбранного диапазона, которые соответствуют определенному условию. Например, если эти числа больше или меньше конкретно установленного значения.
Для этих целей, используется функция «СРЗНАЧЕСЛИ». Как и функцию «СРЗНАЧ», запустить её можно через Мастер функций, из панели формул, или при помощи ручного ввода в ячейку. После того, как открылось окно аргументов функции, нужно ввести её параметры. В поле «Диапазон» вводим диапазон ячеек, значения которых будут участвовать в определении среднего арифметического числа. Делаем это тем же способом, как и с функцией «СРЗНАЧ».
А вот, в поле «Условие» мы должны указать конкретное значение, числа больше или меньше которого будут участвовать в расчете. Это можно сделать при помощи знаков сравнения. Например, мы взяли выражение «>=15000». То есть, для расчета будут браться только ячейки диапазона, в которых находятся числа большие или равные 15000. При необходимости, вместо конкретного числа, тут можно указать адрес ячейки, в которой расположено соответствующее число.
Поле «Диапазон усреднения» не обязательно для заполнения. Ввод в него данных является обязательным только при использовании ячеек с текстовым содержимым.
Когда все данные введены, жмем на кнопку «OK».
После этого, в предварительно выбранную ячейку выводится результат расчета среднего арифметического числа для выбранного диапазона, за исключением ячеек, данные которых не отвечают условиям.
Как видим, в программе Microsoft Excel существует целый ряд инструментов, с помощью которых можно рассчитать среднее значение выбранного ряда чисел. Более того, существует функция, которая автоматически отбирает числа из диапазона, не соответствующие заранее установленному пользователем критерию. Это делает вычисления в приложении Microsoft Excel ещё более удобными для пользователей.
В этом руководстве показано, как использовать функцию Excel СРЗНАЧЕСЛИМН для расчета среднего значения с несколькими условиями.
Когда дело доходит до вычисления среднего арифметического группы чисел в Excel, СРЗНАЧ — это то, что нужно. Для усреднения ячеек, отвечающих определенному условию, пригодится функция СРЗНАЧЕСЛИ. Чтобы найти среднее значение по нескольким критериям, используйте функцию СРЗНАЧЕСЛИМН. Чтобы узнать, как это работает, пожалуйста, продолжайте читать!
Функция Excel СРЗНАЧЕСЛИМН вычисляет среднее арифметическое всех ячеек в диапазоне, которые соответствуют заданным критериям.
Синтаксис следующий:
СРЗНАЧЕСЛИМН(диапазон_средних,диапазон_критериев1, критерий1, [criteria_range2, criteria2]…)
Где:
- Average_range — диапазон ячеек для усреднения.
- Критерий_диапазон1, критерий_диапазон2, … — диапазоны для проверки по соответствующим критериям.
- Критерии1, критерии2, … — критерии, определяющие, какие ячейки усреднять. Критерии могут быть представлены в виде числа, логического выражения, текстового значения или ссылки на ячейку.
Критерии_диапазон1 / критерии1 обязательны, последующие необязательны. В одной формуле можно использовать от 1 до 127 пар диапазон/критерий.
Функция СРЗНАЧЕСЛИМН доступна в Excel 2007 — Excel 365.
Примечание. Функция СРЗНАЧЕСЛИМН работает с логикой И, т.е. усредняются только те ячейки, для которых все условия ИСТИННЫ. Чтобы вычислить ячейки, для которых хотя бы одно условие является ИСТИННЫМ, используйте формулу СРЗНАЧ, ЕСЛИ ИЛИ.
Функция СРЗНАЧЕСЛИМН — примечания по использованию
Чтобы получить четкое представление о том, как работает функция, и избежать ошибок, обратите внимание на следующие факты:
- В аргументе range_range игнорируются пустые ячейки, логические значения TRUE/FALSE и текстовые значения. Нулевые значения включены.
- Если критерием является пустая ячейка, она рассматривается как нулевое значение.
- Если средний_диапазон не содержит ни одного числового значения, #DIV/0! возникает ошибка.
- Если ни одна ячейка не соответствует всем указанным критериям, #DIV/0! возвращается ошибка.
- Критерии AVERAGEIFS могут применяться как к одному диапазону, так и к разным диапазонам.
- Каждый критерий_диапазон должен иметь тот же размер и форму, что и средний_диапазон, иначе #VALUE! возникает ошибка.
Теперь, когда вы знаете теорию, давайте посмотрим, как использовать функцию СРЗНАЧЕСЛИМН на практике.
формула Excel СРЗНАЧЕСЛИМН
Во-первых, давайте наметим общий подход. Чтобы правильно построить формулу СРЗНАЧЕСЛИМН, следуйте следующим рекомендациям:
- В первом аргументе укажите диапазон, который вы хотите усреднить.
- В последующих аргументах укажите пары диапазон/критерий. Пары могут быть расположены в любом порядке, но критерии всегда соответствуют диапазону, к которому они относятся.
- Формула СРЗНАЧЕСЛИМН всегда должна содержать нечетное число аргументов: среднее_диапазон + одна или несколько пар критерии_диапазон/критерий.
СРЗНАЧСЛИМН с текстовыми критериями
Чтобы получить среднее значение чисел в одном столбце, если другой столбец (столбцы) содержит определенный текст, используйте этот текст для критериев.
В качестве примера найдем среднее значение продаж «Apple» в регионе «Север». Для этого составим формулу СРЗНАЧЕСЛИМН с двумя критериями:
- Average_range — это C3:C15 (ячейки усредняются).
- Критерий_диапазон1 — это A3:A15 (элементы для проверки), а критерий 1 — «яблоко».
- Критерий_диапазон2 — B3:B15 (регионы для проверки), а критерий2 — «север».
Сложив аргументы вместе, получим следующую формулу:
=СРЗНАЧЕСЛИ(C3:C15, A3:A15, «яблоко», B3:B15, «север»)
С критериями в предопределенных ячейках (F3 и F4) формула принимает следующий вид:
=СРЗНАЧЕСЛИ(C3:C15, A3:A15, F3, B3:B15, F4)
СРЗНАЧЕСЛИ с логическими операторами
Когда критерий по умолчанию равен «равно», знак равенства можно опустить, и вы просто помещаете целевой текст (заключенный в кавычки) или число (без кавычек) в соответствующий аргумент, как показано в предыдущем примере.
При использовании других логических операторов, таких как «больше» (>), «меньше» (<), не равно (<>) и других с числом или датой, вы заключаете всю конструкцию в двойные кавычки.
Например, для средних продаж, превышающих ноль, до 1 октября 2022 года, используется следующая формула:
=СРЗНАЧЕСЛИ(C3:C15, B3:B15, «<1.10.2022″, C3:C15, «>0″)
Когда критерии находятся в отдельных ячейках, вы заключаете логический оператор в кавычки и объединяете его со ссылкой на ячейку с помощью амперсанда (&). Например:
=СРЗНАЧЕСЛИ(C3:C15, B3:B15, «<«&F3, C3:C15, «>«&F4)
СРЗНАЧЕСЛИ с подстановочными знаками
Чтобы усреднить ячейки на основе частичного совпадения текста, используйте подстановочные знаки в критериях — вопросительный знак (?) для соответствия любому отдельному символу или звездочке.
для соответствия любому количеству символов.
В приведенной ниже таблице предположим, что вы хотите усреднить «оранжевые» продажи во всех «южных» регионах, включая «юго-запад» и «юго-восток». Для этого включим звездочку во второй критерий:
=СРЗНАЧЕСЛИ(C3:C15, A3:A15, F3, B3:B15, «юг*»)
Если в ячейку вводится критерий частичного совпадения текста, соедините подстановочный знак со ссылкой на ячейку. В нашем случае формула принимает такой вид:
Формула с подстановочными знаками AVERAGEIFS на основе частичного совпадения текста
Среднее, если между двумя значениями
Чтобы получить среднее значение значений, попадающих между двумя конкретными значениями, используйте одну из следующих общих формул:
Среднее между двумя значениями включительно:
СРЗНАЧЕСЛИМН(диапазон_средних,диапазон_критериев,»>=значение1″,диапазон_критериев,»<=значение2″)
Среднее, если между двумя значениями, исключая:
СРЗНАЧЕСЛИМН(диапазон_средних,диапазон_критериев,»>значение1″,диапазон_критериев,»<значение2″)
В первой формуле используются логические операторы больше или равно (>=) и меньше или равно (<=), поэтому граничные значения включаются в среднее значение.
Во второй формуле логические критерии больше (>) и меньше (<) исключают граничные значения из среднего.
Эти формулы хорошо работают или в обоих сценариях — когда ячейки для усреднения и ячейки для проверки находятся в одном и том же столбце или в двух разных столбцах.
Например, чтобы рассчитать среднее значение продаж от 100 до 130 включительно, вы можете использовать эту формулу:
=СРЗНАЧЕСЛИ(C3:C15, C3:C15, «>=100», C3:C15, «<=130″)
С граничными значениями в ячейках E3 и F3 формула принимает следующий вид:
=СРЗНАЧЕСЛИ(C3:C15, C3:C15, «>=»&E3, C3:C15, «<=»&F3)
Среднее, если между двумя значениями.
Чтобы усреднить ячейки в заданном столбце, если значения в другом столбце находятся между двумя значениями, укажите другой диапазон для аргументов Average_range и критерии_диапазона.
Например, чтобы усреднить продажи в столбце C, если дата в столбце B находится между 1 сентября и 30 октября, используйте следующую формулу:
=СРЗНАЧЕСЛИ(C3:C15, B3:B15, «>=01.09.2022», B3:B15, «<=30.10.2022″)
Со ссылками на ячейки:
=»&E3, B3:B15, «<=»&F3)» title=»Усреднение ячеек в заданном столбце, если значения в другом столбце находятся между двумя значениями.»/>
Усреднение ячеек в заданном столбце, если значения в другом столбце находятся между двумя значениями.
Вот как вы используете функцию СРЗНАЧЕСЛИМН в Excel, чтобы найти среднее арифметическое с несколькими критериями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь Функция СРЗНАЧЕСЛИ в Excel — примеры
(файл .xlsx)
Вас также могут заинтересовать
Average a number expressing the central or typical value in a set of data, in particular the mode, median, or (most commonly) the mean, which is calculated by dividing the sum of the values in the set by their number. The basic formula for the average of n numbers x1,x2,……xn is
A = (x1 + x2 ........xn)/ n
In Excel, there is an average function for this purpose, but it can be calculated manually also using SUM and COUNT functions like this
= SUM(D1:D5)/COUNT(D1:D5)
Here
- SUM: This function is used to find the sum of values in the required range of cells.
- COUNT: This function is used to get the count of the required range of cells containing numeric values.
AVERAGE Function
Excel provides a direct function named AVERAGE to calculate the average (or mean) of the numbers in the range specified in the function’s argument. In this function, maximum of 255 arguments can be given (numbers / cell references/ ranges/ arrays or constants).
Syntax:
= AVERAGE(Num1, [Num2], ...)
Here
- Num1 [Required]: Provide range, cell references, or the first number for calculating the average.
- Num2, … [Optional]: Provide additional numbers or range of cell references for calculating the average.
Example:
AVERAGE(C1:C5)
This will give the average of numeric values in the range C1 to C5.
Note: In the above-mentioned methods, any criteria for calculating the average cannot be mentioned. For this excel provide another function called AVERAGEIF.
Average Cells Based On One Criterion
Excel provides a direct function named AVERAGEIF to calculate the average (or mean) of the numbers in the range specified that meets particular criteria specified in the function’s argument.
Syntax:
= AVERAGEIF(Range_Cells_For_Criteria, Criteria, [Range_Cells_For_Average])
Here
- Range_Cells_For_Criteria [Required]: Give the range of cells on which criteria are to be tested.
- Criteria [Required]: This is the condition on which to decide the cells to average. The criteria can be given in the form of an expression(logical), text-value or number, or reference to a cell, e.g. 100 or “>100” or “Apple” or A7.
- Range_Cells_For_Average [Optional]: Specify the cells on which the average needs to be calculated. Range_Cells_For_Criteria is used to find the average if it is not included.
Note:
- An empty cell in average_range is ignored here.
- Cells in the range that contain TRUE or FALSE are ignored here.
- If the range is blank, return the #DIV0! Error value.
- Empty cell in the criteria is treated as 0.
Using Double Quotes (“”) In Criteria
In Excel, the text values are surrounded by double quotations (“”), but integers are not, in general. But when numbers are used in criteria(s) with a logical operator, the number and operator must also be wrapped in quotes.
Examples:
1. To calculate the average of only positive numbers excluding 0 in the range A2 to A6
= AVERAGEIF(A2:A6,”>0″) is correct.
= AVERAGEIF(A2:A6,>0) is incorrect as the logical operator should be included in double-quotes.
2. To get the average age (AGE is in column C) of the employees living in the city having id = 1 (CityId is in column D)
= AVERAGEIF(D2:D10, “=1”, C2:C10) is correct
= AVERAGEIF(D2:D10, 1, C2:C10) is also correct as here also D2 to D10 cells will be checked for value 1.
3. To get the average age (AGE is in column C) of the employees living in a city named Mumbai (City is in column B)
= AVERAGEIF(B2:B10, “Mumbai”, C2:C10) is correct
= AVERAGEIF(B2:B10, Mumbai, C2:C10) is incorrect as text should be included in double-quotes.
Example 1: To calculate the average of only positive numbers excluding 0 in the range A2 to A6.
Solution: In this example, a single criterion is given- “>0″. So all the numbers in the range that are greater than 0 come in the criteria. The average will be calculated as
(12.7 + 87.2 + 100) / 3 = 66.66666667
So, the average formula for the above example is
AVERAGEIF(A2:A6,">0")
Example 2: To get the average age (AGE is in column C) of the employees living in a city named Mumbai (City is in column B).
Solution: Let’s break the example into different parts so that the individual parts can then be substituted in the AVERAGEIF function
- Criteria: “Mumbai”.
- Cells for criteria: B2 to B6.
- Cells for average: C2 to C6 that meet the criteria.
The average age of the employees living in Mumbai
= (65 + 45) /2 = 55
So, the average formula for this example is
AVERAGEIF(B2:B6,"Mumbai",C2:C6)
Average Cells Based On Multiple Criterion
Excel provides a direct function named AVERAGEIFS to calculate the average (or mean) of the numbers in the range specified that meets multiple criteria specified in the function’s argument.
Syntax:
AVERAGEIFS(RangeForAverage,RangeForCriteria1,Criteria1,
[RangeForCriteria2, Criteria2],..)
Here
- RangeForAverage [Required]: Provide the cell range on which average needs to be computed.
- RangeForCriteria1: Range on which criteria1 is applied.
- Criteria1: The first criteria to apply on the RangeForCriteria1.
Note:
- All ranges are required along with their criteria.
- Criteria1 is required, the rest are optional.
Example 1: To calculate the average age of the employees living in Mumbai whose ages are > 50.
Solution: The following criteria are used to solve the problem
- RangeForAverage: C2 to C8.
- Criteria 1: “Mumbai”.
- RangeForCriteria1: B2 to B8.
- Criteria 2: “>50”.
- RangeForCriteria1: C2 to C8.
Here, the average is calculated on basis of two criteria- Employees living in Mumbai and employees whose Age > 50
AVERAGEIFS(C2:C8,B2:B8,"Mumbai",C2:C8,">50")
Example 2: To get the average price of the product with Category ID 1 and having a Price greater than Rs 500/-.
Solution: The following criteria will be used for the AVERAGEIF function
- RangeForAverage: C2 to C11.
- Criteria 1: “=1”.
- RangeForCriteria1: B2 to B11.
- Criteria 2: “>500”.
- RangeForCriteria2: C2 to C11.
Here, the average is calculated on the basis of two criteria- Product having Category ID 1 and Price greater than 500
AVERAGEIFSC2:C11,B2:B11,"=1",C2:C11,">500")
Using Values From Another Cell
The concatenation of text can be used to use the value from another cell. Concatenation is the process of connecting two or more values to form a text string. There are two ways for concatenation of the strings in Excel
- Logical & operator can be used.
- Concatenate function in Excel for concatenation of two texts.
Example: To get average marks (marks are in column B) of passed students having minimum passing marks value contained in cell D2.
Solution: In this example, the logical & operator will be used to concatenate the two conditions for finding the result
- Marks greater than 0.
- Minimum passing marks i.e. 10.
Here, D2 has a value of 10, so the minimum pass marks are 10, the following criteria will be used- “>=”&D2. This will concat “>=” & value contained in cell D2, i.e. 10. After concatenation, the criteria will become “>=10”.
So, in this example
- RangeForAverage- B2 to B12.
- Criteria1- Marks greater than 0.
- RangeForCriteria1- B2 to B12.
The following formula will be used to calculate the average
=AVERAGEIFS(B2:B12,B2:B12,">=0"&D2)
Wildcards
Excel provides wildcard characters that can be used in the criteria. Some wild cards are
- * (asterisk): This symbol can be used to represent any number of characters.
- ? (question mark): This symbol can be used to represent a single character.
- ~ (tilde): This symbol is used before the above two wild cards and also tilde itself so that when there is a need to match a text containing * or? Or ~, Excel does not treat them as a wildcard. To treat them as normal characters in Excel criteria, the tilde is used before them.
Examples: There are the product names in column A (From A1 to A10) and their respective price in column C.
- AVERAGEIFS(C2:C11, A:A11, “*a*”): To find the average price of all the products who have character ‘a’ anywhere in their name.
- AVERAGEIFS(C2:C11, A:A11, “?a*”): To find the average price of all the products that have the second character ‘a’ in their name.
- AVERAGEIFS(C2:C11, A:A11, “?aree”): To find the average price of all the products that have a single character before ‘aree’ in their name.
- AVERAGEIFS(C2:C11, A:A11, ” ~*Saree “): To find the average price of all the products that have a name is *Saree.
Example: To find the average price of all the products that have character ‘a’ anywhere in their name.
Solution: In this example
- Product Saree, Nail Paint, Jeans, and Lehenga contains the character ‘a’ in them.
- The average prices for these products will be
= (1200+ 100+ 1000+ 5000)/4 = 7300/4 = 1825
So, the complete formula for calculating the average is as follows
AVERAGEIFS(C2:C11,A2:A11,"*a")
Conclusion
Using AVERAGE, it is possible to find average without any criteria. Using AVERAGEIF, the average can be calculated with particular criteria and using AVERAGEIFS, the average can be computed with multiple criteria(s).
Для того чтобы найти среднее значение в Excel (при том неважно числовое, текстовое, процентное или другое значение) существует много функций. И каждая из них обладает своими особенностями и преимуществами. Ведь в данной задаче могут быть поставлены определенные условия.
Например, средние значения ряда чисел в Excel считают с помощью статистических функций. Можно также вручную ввести собственную формулу. Рассмотрим различные варианты.
Как найти среднее арифметическое чисел?
Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.
Как это быстро сделать с помощью функций Excel? Возьмем для примера ряд случайных чисел в строке:
- Ставим курсор в ячейку А2 (под набором чисел). В главном меню – инструмент «Редактирование» — кнопка «Сумма». Выбираем опцию «Среднее». После нажатия в активной ячейке появляется формула. Выделяем диапазон: A1:H1 и нажимаем ВВОД.
- В основе второго метода тот же принцип нахождения среднего арифметического. Но функцию СРЗНАЧ мы вызовем по-другому. С помощью мастера функций (кнопка fx или комбинация клавиш SHIFT+F3).
- Третий способ вызова функции СРЗНАЧ из панели: «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».
Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).
Теперь посмотрим, что еще умеет функция СРЗНАЧ.
Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:
Среднее значение по условию
Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().
Найти среднее арифметическое чисел, которые больше или равны 10.
Функция: =СРЗНАЧЕСЛИ(A1:A8;»>=10″)
Результат использования функции СРЗНАЧЕСЛИ по условию «>=10»:
Третий аргумент – «Диапазон усреднения» — опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.
Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку.
Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».
Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово «столы»). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.
В результате вычисления функции получаем следующее значение:
Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно.
Как посчитать средневзвешенную цену в Excel?
Как посчитать средний процент в Excel? Для этой цели подойдут функции СУММПРОИЗВ и СУММ. Таблица для примера:
Как мы узнали средневзвешенную цену?
Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).
С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ — сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.
Среднее квадратическое отклонение: формула в Excel
Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.
Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.
Среднеквадратическое отклонение имеет привязку к масштабу исходных данных. Для образного представления о вариации анализируемого диапазона этого недостаточно. Чтобы получить относительный уровень разброса данных, рассчитывается коэффициент вариации:
среднеквадратическое отклонение / среднее арифметическое значение
Формула в Excel выглядит следующим образом:
СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).
Коэффициент вариации считается в процентах. Поэтому в ячейке устанавливаем процентный формат.
Skip to content
В статье показано, как определить среднее значение в Excel для разных типов данных с помощью формул СРЗНАЧ или СРЗНАЧА. Вы также узнаете, как использовать функции СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН для усреднения ячеек, соответствующих определенным критериям.
Средняя зарплата… Средняя продолжительность жизни… Ежедневно мы с вами слышим эти словосочетания, служащие для описания множества одним единственным числом. Но, как ни странно, «среднее значение» — достаточно коварное понятие, часто вводящее в заблуждение обычного, неискушенного в математической статистике, человека. Например, в какой-то абстрактной компании работает десять сотрудников. Девять из них получают зарплату около 50 000 рублей, а один 1 500 000 рублей (по странному совпадению он же является генеральным директором этой компании).
В математике среднее называется средним арифметическим, и оно рассчитывается путем сложения группы чисел, а затем деления на количество этих чисел.
В приведенном выше примере средняя зарплата составляет: 195 000 рублей =(50000*9+1500000)/10. Вряд ли эта цифра соответствует реальной ситуации, но таковы расчёты среднего арифметического значения.
И всё же в большинстве случаев знать среднее весьма полезно.
- Функция СРЗНАЧ.
- Пример 1. Расчет среднего из нескольких чисел.
- Пример 2. Расчет среднего процента.
- Пример 3. Расчет среднего времени.
- Чем отличается функция СРЗНАЧА?
- Среднее по условию — функция СРЗНАЧЕСЛИ
- Среднее, если пусто.
- Среднее, если не пустое.
- Среднее по нескольким условиям — функция СРЗНАЧЕСЛИМН
- Пример 1. Среднеарифметическое количество ячеек по нескольким параметрам (текст и число)
- Пример 2. Среднеарифметическое на основе критерия даты
- Пример 1. Среднее с логикой ИЛИ на основе нескольких текстовых критериев
- Пример 2. Среднее с логикой ИЛИ на основе числовых критериев с операторами сравнения
- Пример 3. Среднее с логикой ИЛИ на основе пустых/непустых ячеек
Как посчитать среднее арифметическое в Экселе? Вам не нужно писать сложные математические выражения. Есть несколько функций «на все случаи жизни».
- СРЗНАЧ (AVERAGE- а английском варианте) — рассчитать среднее арифметическое ячеек с числами.
- СРЗНАЧА (или AVERAGEA) — найти среднее арифметическое ячеек с любыми данными (числа, логические и текстовые значения).
- СРЗНАЧЕСЛИ (или AVERAGEIF) – среднее по заданному критерию.
- СРЗНАЧЕСЛИМН (или AVERAGEIFS) – среднее ячеек, удовлетворяющих нескольким условиям.
Функция СРЗНАЧ.
СРЗНАЧ применяется для возврата среднего арифметического указанных ячеек.
СРЗНАЧ число 1; [число 2];…)
Число 1, 2 и так далее — числа, для которых вы хотите найти среднее. Первый аргумент обязателен, последующие — нет. В одну формулу может быть включено до 255 аргументов. Они могут быть представлены в виде чисел, ссылок на ячейки или диапазонов.
Использование функции СРЗНАЧ – на примерах.
СРЗНАЧ — одна из самых простых и простых в использовании функций Excel, и следующие примеры подтверждают это.
Пример 1. Расчет среднего из нескольких чисел.
Вы можете указать числа непосредственно. Например,
=СРЗНАЧ(1;2;3;4) возвращает результат 2,5.
Чтобы вычислить среднее по столбцу, укажите ссылку на него целиком:
=СРЗНАЧ(A:A)
Чтобы получить по строке, введите ссылку на нее:
=СРЗНАЧ(1:1)
Чтобы вычислить в каком-то диапазоне, укажите его:
=СРЗНАЧ(A1:C20)
Чтобы вернуть среднее из несмежных ячеек, запишите каждую ячейку отдельно, например
=СРЗНАЧ(A1; C1; D1)
И, естественно, ничто не мешает вам включать числа, ссылки и диапазоны в одну формулу. Например, следующее выражение вычисляет среднее из 2 диапазонов и 1 отдельного значения:
=СРЗНАЧ(B3:B5; C7:D9; B11)
Примечание. Если вы хотите округлить полученное число до ближайшего целого, примените одну из функций округления, например:
=ОКРУГЛ(СРЗНАЧ(B3:B5; B7:B9; B11);0)
Помимо чисел, вы можете использовать функцию СРЗНАЧ для вычисления среднего на основе других видов чисел, таких как проценты и время. Это показано в следующих примерах.
Пример 2. Расчет среднего процента.
Если у вас есть столбец с процентами на вашем листе, как вы получаете средний процент выполнения? Возьмём обычную формулу Excel для среднего 
Примечание. Обратите внимание, что СРЗНАЧ учитывает и нулевые значения при расчете. А это существенно повлияет на итоговую цифру. Если вы предпочитаете исключать нули из расчета, возьмите вместо этого СРЗНАЧЕСЛИ, как будет показано чуть ниже. ССЫЛКА
Пример 3. Расчет среднего времени.
Этим совсем не так просто, как с обычными числами. Ведь время включает часы, минуты и секунды? Вычислять разные единицы времени вручную было бы очень сложно… но СРЗНАЧ отлично с этим справляется.
Важные особенности СРЗНАЧ!
Как вы только что видели, применять СРЗНАЧ очень просто. Тем не менее, у нее есть несколько особенностей, о которых вам нужно знать.
- Нулевые значения (0) включены в расчет.
- Текстовые строки, логические значения ИСТИНА и ЛОЖЬ, а также пустые ячейки — игнорируются. Если вы хотите включить в расчет логические и текстовые представления чисел, примените СРЗНАЧА.
- Учитываются логические значения, которые вы вводите непосредственно в выражение. Например, =СРЗНАЧ(ИСТИНА; ЛОЖЬ) возвращает 0,5, что является средним из 1 и 0.
Примечание. При использовании СРЗНАЧ в листах Excel, учитывайте различие между ячейками, содержащими нулевые значения, и реально пустыми. Нули считаются, а пустые — нет. Это может быть особенно важно, если опция «Показывать ноль в ячейках, которые содержат нулевые значения» не включена на данном листе. То есть, клетки с нулями внешне будут пустыми. Вы можете найти эту опцию в Эксель: Параметры > Дополнительно > Параметры отображения листа .
Чем отличается функция СРЗНАЧА?
СРЗНАЧА аналогична СРЗНАЧ в том, что она вычисляет среднее арифметическое чисел. Разница в том, что СРЗНАЧА включает в расчет все непустые ячейки, независимо от того, содержат ли они числа, текст, логические ИСТИНА и ЛОЖЬ, или пустые строки, возвращаемые другими формулами.
СРЗНАЧА(значение1; [значение2];…)
значение1, 2,… — это цифры, массивы, ссылки на ячейки или диапазоны, которые вы хотите усреднить. Первый аргумент обязателен, другие (до 255) необязательны.
Она обрабатывает различные типы данных, такие как числа, текстовые строки и логические. Что нужно запомнить:
- Пустые клетки таблиц игнорируются.
- Текст, включая пустые строки («»), возвращаемые другими формулами, оценивается как 0.
- Логическое ИСТИНА оценивается как 1, а ЛОЖЬ оценивается как 0.
Например, =СРЗНАЧА(2;ЛОЖЬ) дает нам 1, что является средним между 2 и 0.
=СРЗНАЧА(2;ИСТИНА) возвращает 1,5, что является средним от 2 и 1.
На следующем скриншоте показаны два варианта расчёта для среднего значения в Excel, и разные результаты, которые они возвращают:
Поэтому, если вы не хотите включать в свои вычисления логические выражения и текстовые строки, используйте СРЗНАЧ, а не СРЗНАЧА.
Среднее по условию — функция СРЗНАЧЕСЛИ
СРЗНАЧЕСЛИ вычисляет среднее арифметическое всех ячеек, которые соответствуют заданным критериям.
СРЗНАЧЕСЛИ (диапазон; критерии; [диапазон_усреднения])
Она имеет следующие аргументы (первые 2 обязательны, последний – нет):
- Диапазон — диапазон ячеек, которые будут проверены по заданным параметрам.
- Критерии — условие, применяемое для определения, какие ячейки усреднять. Они могут быть представлены в виде числа, логического выражения, текста или ссылки, например, 5,> 5, «кот» или A2.
- Диапазон усреднения — что вы хотите усреднить (он необязательный). Если опущено, функция будет считать по диапазону условия.
А теперь давайте посмотрим, как вы можете применить СРЗНАЧЕСЛИ на реальных рабочих листах, чтобы найти среднее ячеек, соответствующих вашим требованиям.
Точное соответствие критериям
Классическое применение СРЗНАЧЕСЛИ — это нахождение среднего арифметического ячеек, которые точно соответствуют заданному условию.
В этом примере давайте усредним только продажи (B2: B8) для заказов на бананы (A2: A8):
=СРЗНАЧЕСЛИ($A$2:$A$8; $B$10; $B$2:$B$8)
Вместо того, чтобы вводить условие непосредственно в формулу, вы можете ввести его в отдельную ячейку ($B$10) и затем обратиться к ней. Это очень известный и всеми применяемый приём.
Примечание. Чтобы округлить возвращаемое число до определенного количества десятичных знаков, примените одну из функций округления Excel, или диалоговое окно «Формат ячеек», чтобы изменить только отображение числа на экране.
Например, чтобы округлить полученный выше результат до 2 десятичных знаков, вы можете заключить его в функцию ОКРУГЛ следующим образом:
=ОКРУГЛ(СРЗНАЧЕСЛИ(A2:A8;»бананы»; B2:B8);2)
Кроме того, вы можете выбрать ячейку с формулой (E1 в этом примере), нажмите комбинацию клавиш Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Затем перейдите на вкладку «Числовой» или «Денежный » и выберите количество десятичных разрядов, которые вы хотите показать. Помните, что в этом случае фактическое сохраненное значение не изменится, и точное неокругленное число будет использоваться во всех вычислениях, когда вы ссылаетесь на него в других формулах.
Частичное соответствие критериям (с символами подстановки).
Вы можете применить символы подстановки в аргументе критерия для выбора ячеек на основе частичного совпадения:
- Вставьте знак вопроса (?), чтобы заменить любой отдельный символ.
- Вставьте звездочку (*), чтобы заменить ею любую последовательность символов.
- Чтобы найти реальный знак вопроса или звездочку, введите тильду (~) перед этим символом (то есть, ~? или ~*).
Предположим, что у вас есть 3 разных вида бананов, и вы хотите найти среднюю величину их продаж:
=СРЗНАЧЕСЛИ($A$2:$A$8; «*бананы»; $B$2:$B$8)
Если вашему ключевому слову может предшествовать и/или последовать какой-либо другой символ, добавьте звездочку * как перед словом, так и после него.
=СРЗНАЧЕСЛИ($A$2:$A$8; «*бананы*»; $B$2:$B$8)
Чтобы найти среднее по всем товарам, за исключением каких-либо бананов, запишите следующую формулу:
=СРЗНАЧЕСЛИ($A$2:$A$8; «<>*бананы*»; $B$2:$B$8)
Как применить числовые условия и логические операторы.
Довольно часто вам может потребоваться усреднить ячейки, в которых количество больше или меньше определенного числа. Например, есть перечень чисел в столбце A, и мы хотим найти среднее из тех из них, которые больше 10.
Правильный подход — заключить логический оператор и число в двойные кавычки.
Итак, мы получили вот что:
=СРЗНАЧЕСЛИ($A$2:$A$7;»>10″)
Другая весьма распространенная проблема – как найти среднее чисел, которые не равны нулю. Для этого вам понадобится оператор «не равно» в аргументе критерия:
=СРЗНАЧЕСЛИ($A$2:$A$7;»<>0″)
Как вы, возможно, заметили, мы не задействуем третий аргумент [диапазон усреднения] ни в одном из приведенных выше выражений, поскольку мы хотим считать по начальному диапазону.
Для пустых или непустых ячеек
При выполнении анализа данных в Excel часто может потребоваться найти среднее чисел, которые соответствуют либо пустым, либо непустым областям.
Среднее, если пусто.
Чтобы включить пустые ячейки, которые не содержат абсолютно ничего (без формулы, без строки нулевой длины), введите «=» в аргументе.
Например, вычислим среднее ячеек C2: C8, если клетка в столбце B в той же строке абсолютно пуста:
=СРЗНАЧЕСЛИ($B$2:$B$8; «=»; $C$2:$C$8)
Чтобы усреднить значения, соответствующие визуально пустым ячейкам, включая те, которые содержат пустые строки, возвращаемые другими функциями (например, с формулой вроде = «»), запишите «». Например:
=СРЗНАЧЕСЛИ($B$2:$B$8; «»; $C$2:$C$8)
Среднее, если не пустое.
Чтобы найти среднее, соответствующее непустым ячейкам, введите «<>».
Например, так вычисляем среднеарифметическое C2:C8, если соответствующая позиция в столбце B не пуста:
=СРЗНАЧЕСЛИ($B$2:$B$8; «<>»; $C$2:$C$8)
Использование ссылок и других функций в описании условий.
Вместо того, чтобы записывать параметры подсчета непосредственно в формуле, вы можете обратиться к определенной ячейке, в которую можно вводить различные значения, не изменяя саму формулу. Так значительно уменьшается риск случайной ошибки при корректировке.
Если ссылка является критерием точного соответствия, то просто укажите ее в аргументе:
=СРЗНАЧЕСЛИ($B$2:$B$8;E1; $C$2:$C$8)
Если в качестве условия вы берёте логическое выражение со ссылкой или c другой функцией, нужно заключить логический оператор в «двойные кавычки» и добавить амперсанд (&) для их объединения.
Например, чтобы рассчитать средние продажи (C2: C8), превышающие указанное в E4:
=СРЗНАЧЕСЛИ($C$2:$C$8; «>»&$B$13)
С датами в B2: B8, приведенное ниже возвращает среднюю величину продаж (C2: C8), которые произошли до текущей даты:
=СРЗНАЧЕСЛИ($B$2:$B$8; «<=»&»22-07-2020»; $C$2:$C$8)
Вместо прямого указания даты можно взять соответствующую функцию. К примеру,
=СРЗНАЧЕСЛИ($B$2:$B$8; «<=»&СЕГОДНЯ(); $C$2:$C$8)
Среднее по нескольким условиям — функция СРЗНАЧЕСЛИМН
СРЗНАЧЕСЛИМН учитывает сразу несколько условий и возвращает среднеарифметическое ячеек, которые соответствуют всем указанным критериям.
СРЗНАЧЕСЛИМН(диапазон подсчёта, диапазон_критерия_1, критерий_1, [диапазон_критерия_2, критерий_2],…)
Она имеет следующие аргументы:
- Диапазон_подсчёта — область ячеек, которые вы хотите усреднить.
- диапазон_критерия_1, 2 ,… — от 1 до 127 диапазонов для проверки на соответствие указанным параметрам. Первый из них обязателен, последующие – по желанию.
- Критерий_1, 2,… — от 1 до 127 критериев, определяющих, какие именно клетки следует усреднить. Они могут быть в виде числа, логического выражения, текста или ссылки. Первый обязателен, дополнительные – по необходимости.
Функция СРЗНАЧЕСЛИМН доступна в Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010 и 2007.
Как уже упоминалось, СРЗНАЧЕСЛИМН находит среднее ячеек, которые соответствуют всем указанным вами критериям (логика «И»). По сути, вы применяете его совершенно аналогично СРЗНАЧЕСЛИ, за исключением того, что вы можете указать в ней несколько областей проверки и соответствующих условий.
Пример 1. Среднеарифметическое количество ячеек по нескольким параметрам (текст и число)
Предположим, что у вас есть список товаров в столбце A и суммы продаж в столбце B. Давайте выясним среднее продаж бананов, превышающее 100 единиц.
Здесь:
- С2:С8 — ячейки, которые вы хотите усреднить, если выполняются оба условия;
- Первое условие: A2:A8 (столбец «Товар»), а критерий_1 — «бананы»;
- Второе условие — это С2:С8 (столбец «Продажи»), а критерий_2 — «> 100».
Собираем вышеперечисленные компоненты вместе. И если вы замените условия ссылками, вы получите нечто похожее на это:
=СРЗНАЧЕСЛИМН($C$2:$C$8; $A$2:$A$8;$C$10; $C$2:$C$8; «>»&$C$11)
Как видите, только две ячейки (С3 и С5) удовлетворяют обоим условиям, и поэтому только они и берутся для расчета.
Пример 2. Среднеарифметическое на основе критерия даты
В этом примере давайте посчитаем среднее количество товаров, доставленных до 22 июля 2020 года, статус которых определен (позиция в соответствующем столбце не пустая). С количеством, указанным в столбце D, датами в колонке В (критерий_1) и статусом в С (критерий_2), формула выглядит следующим образом:
=СРЗНАЧЕСЛИМН($D$2:$D$8; $B$2:$B$8; «<=22-07-2020»; $C$2:$C$8; «<>»)
В критерии1 вы вводите дату, которой предшествует оператор сравнения. В критерии2 вы вводите «<>», который указывает включать только непустые ячейки в пределах столбца «Статус».
Примечание. Когда вы используете число или дату в сочетании с логическим оператором в указании условия, то заключайте эту комбинацию в двойные кавычки — “<=22.07.2020”.
Что нужно запомнить!
СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН имеют много общего, в частности:
- При подсчёте пустые области, логические ИСТИНА/ЛОЖЬ и текст — игнорируются.
- При оценке заданных условий пустые ячейки рассматриваются как нулевые (0).
- Если область, по которой считаем среднее, содержит только пустые клетки или текст, то обе функции возвращают ошибку #ДЕЛ/0!.
- Если ничего не соответствует требованиям, то также получаем ошибку #ДЕЛ/0!.
ОСОБЕННОСТИ
- Диапазон для подсчёта СРЗНАЧЕСЛИ не обязательно должен быть того же размера, что и область условий. Однако фактические данные, с которыми работает функция, определяются размером аргумента усреднения. Другими словами, если записать выражение
=СРЗНАЧЕСЛИ($B$2:$B$10; «<>»; $C$2:$C$8)
то в любом случае находить среднее мы будем по $C$2:$C$8.
- В отличие от СРЗНАЧЕСЛИ, СРЗНАЧЕСЛИМН требует, чтобы каждая область критериев была того же размера, что и область для подсчета. Иначе получаем ошибку #ЗНАЧ!.
Как усреднить числа по нескольким параметрам с помощью логики ИЛИ?
Поскольку СРЗНАЧЕСЛИМН работает с логикой «И», а СРЗНАЧЕСЛИ допускает только 1 критерий, нам придется составить нашу собственную формулу для усреднения с помощью логики «ИЛИ».
Другими словами, мы составим выражение для вычисления среднего арифметического в Excel, если выполняется любое из указанных требований.
Пример 1. Среднее с логикой ИЛИ на основе нескольких текстовых критериев
Предположим, вы хотите получить средние продажи (C2: C8) как для бананов, так и для яблок (A2: A8). Чтобы рассчитать это, вам понадобится формула массива, включающая несколько функций Excel:
{=СРЗНАЧ(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A2:A8;{«бананы»;»яблоки»};0));B2:B8))}
Пожалуйста, помните, что формулы массива должны быть введены через Ctrl + Shift + Enter, а не просто ENTER.
Если товары, которые мы ищем, записать отдельно (скажем, в E1 и E2), то наш расчет будет выглядеть так:
{=СРЗНАЧ(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A2:A8;E1:E2;0));B2:B8))}
Из всех формул усреднения Excel, которые обсуждались до сих пор, это самая хитрая (хотя осталось еще два примера 
Как это работает?
Для наших любопытных и вдумчивых читателей, которые хотят не только механически применять рекомендации, но и понимать, что же они делают, даем подробное объяснение логики расчетов.
В начале функция ЕСЛИ определяет, какие значения в исходном диапазоне соответствуют любому из указанных параметров, и передает их в СРЗНАЧ. Вот как:
ПОИСКПОЗ берёт исходные данные в A2:A8 в качестве условий поиска и сравнивает каждое из них с массивом поиска, представленным критериями в E1: E2.
3- й аргумент, установленный в 0, указывает искать точные совпадения:
ПОИСКПОЗ(A2:A8,E1:E2,0)
Когда совпадение найдено, программа возвращает относительную позицию в массиве поиска, в противном случае ошибку #Н/Д!.
{#Н/Д!; 1; 2; 1; 2; #Н/Д!; 1}
ЕЧИСЛО преобразует любые числа в ИСТИНА, а ошибки в ЛОЖЬ:
{ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА}
Этот массив отправляется на логическую проверку ЕСЛИ.
Она заменяет позиции, соответствующие ИСТИНА в приведённом выше массиве, фактическими значениями из B2: B8:
{ЛОЖЬ; 80; 130; 120; 70; ЛОЖЬ; 90}
Этот окончательный массив передается в СРЗНАЧ, которая вычисляет, игнорируя логические значения.
Пример 2. Среднее с логикой ИЛИ на основе числовых критериев с операторами сравнения
Если вы хотите найти среднее арифметическое на основе нескольких числовых критериев и условий больше/меньше, чем в сочетании с логикой ИЛИ, то формула, рассмотренная в предыдущем примере, не будет работать. Ведь вы не можете поместить эти логические выражения в массив. Решение заключается в применении функции СУММ в формуле массива.
Предположим, у вас есть количество в столбце B и сумма в столбце C, и вы хотите усреднить суммы, которые больше 50 в столбце B или C. При этом вы хотите избежать дубликатов, то есть не считать строку дважды, если число больше 50 в обоих столбцах.
Вот рабочий пример:
{=СУММ(ЕСЛИ((C2:C8+D2:D8>50);D2:D8;0))/СУММ( —((C2:C8+D2:D8)>50))}
Помните, что это формула массива, и поэтому вы должны нажать Ctrl + Shift + Enter и ввести ее правильно.
Как видите, выражение состоит из 2 частей. В первой части вы применяете функцию ЕСЛИ с оператором ИЛИ в аргументе (C2:C8> 50) + (D2:D8> 50). Как вы, вероятно, знаете, в формулах массива плюс (+) действует как оператор ИЛИ.
Итак, первая часть складывает числа в столбце C, если выполняется любое из условий.
Вторая часть возвращает количество таких ячеек, а затем вы делите сумму на количество, чтобы найти среднеарифметическое.
И, естественно, вы можете указать разные условия для каждого диапазона. Например, чтобы усреднить продажи, если столбец C больше 50 или столбец D больше 100, используйте следующие выражения: (C2: C8> 50) + (D2: D8> 100). Всё целиком будет выглядеть следующим образом:
=СУММ(ЕСЛИ((C2:C8>50)+(D2:D8>100);D2:D8;0))/СУММ(—((C2:C8>50)+(D2:D8>100)))
Пример 3. Среднее с логикой ИЛИ на основе пустых/непустых ячеек
Средняя формула с несколькими условиями ИЛИ, соответствующими пустым и непустым ячейкам, очень похожа на ту, которую мы только что обсуждали.
Для непустых ячеек.
Следующая формула массива находит среднее количество (столбец B), если указана либо дата (столбец B), либо статус (столбец C), т. е. если столбцы B или C не пустые.
Чтобы сделать её более компактной, вы можете объединить диапазоны с помощью амперсанда (&):
{=СУММ(ЕСЛИ((B2:B8&C2:C8)<>»»;D2:D8;0))/СУММ( —((B2:B8&C2:C8)<>»»))}
Для пустых ячеек.
Чтобы усреднить числа в столбце D, соответствующие пустым ячейкам в B или C, замените непустой оператор (<> «») пустым оператором (= «»).
Объединение диапазонов при помощи амперсанда не будет работать в этом случае.
Это также формула массива, поэтому не забудьте нажать Ctrl + Shift + Enter.
{=СУММ(ЕСЛИ(—((B2:B8=»») + (C2:C8=»»))>0;D2:D8;0)) / СУММ(—(((B2:B8=»») + (C2:C8=»»))>0))}
Вот как рассчитывается среднее в Excel.
Я благодарю вас за чтение и надеюсь еще не раз увидеть вас в нашем блоге!