Функция СРЗНАЧЕСЛИ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование С AVERAGEIF в Microsoft Excel.
Описание
Возвращает среднее значение (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют данному условию.
Синтаксис
СРЗНАЧЕСЛИ(диапазон, условия, [диапазон_усреднения])
Аргументы функции СРЗНАЧЕСЛИ указаны ниже.
- Диапазон. Обязательный. Одна или несколько ячеек для вычисления среднего, включающих числа или имена, массивы или ссылки, содержащие числа.
- Условие. Обязательный. Условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.
- Диапазон_усреднения. Необязательный. Фактическое множество ячеек для вычисления среднего. Если этот параметр не указан, используется диапазон.
Замечания
- Ячейки в диапазоне, которые содержат значения ИСТИНА или ЛОЖЬ, игнорируются.
- Если ячейка в «диапазоне_усреднения» пустая, функция СРЗНАЧЕСЛИ игнорирует ее.
- Если диапазон является пустым или текстовым значением, то #DIV0! значение ошибки #ЗНАЧ!.
- Если ячейка в условии пустая, «СРЗНАЧЕСЛИ» обрабатывает ее как ячейки со значением 0.
- Если ни одна из ячеек в диапазоне не соответствует условиям, то #DIV/0! значение ошибки #ДЕЛ/0!.
- В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).
- Значение «диапазон_усреднения» не обязательно должно совпадать по размеру и форме с диапазоном. При определении фактических ячеек, для которых вычисляется среднее, в качестве начальной используется верхняя левая ячейка в «диапазоне_усреднения», а затем добавляются ячейки с совпадающим размером и формой. Например:
Если диапазон равен
И «диапазон_усреднения»
Обрабатываемые ячейки
Примечание: Функция СРЗНАЧЕСЛИ измеряет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения: :
- Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
- Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
- Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При смещенном распределении множества чисел значения могут быть разными.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Стоимость имущества
Расчет среднего значения
В Excel существует несколько способов найти среднее для набора чисел. Например, можно воспользоваться функцией для расчета простого среднего, взвешенного среднего или среднего, исключающего определенные значения.
Чтобы научиться вычислять средние значения, используйте предоставленные образцы данных и описанные ниже процедуры.
Копирование примера данных
Чтобы лучше понять описываемые действия, скопируйте пример данных в ячейку A1 пустого листа.
- Создайте пустую книгу или лист.
- Выделите приведенный ниже образец данных.
Примечание: Не выделяйте заголовки строк или столбцов (1, 2, 3. A, B, C. ) при копировании данных примера на пустой лист.
Выбор примеров данных в справке
| Качество изделия | Цена за единицу | Количество заказанных изделий |
|---|---|---|
| 10 | 35 | 500 |
| 7 | 25 | 750 |
| 9 | 30 | 200 |
| 10 | 40 | 300 |
| 8 | 27 | 400 |
| 5 | 20 | 200 |
| Среднее качество изделий | ||
| Средняя цена изделия | ||
| Среднее качество всех изделий с оценкой качества выше 5 |
Расчет простого среднего значения
- Выделите ячейки с A2 по A7 (значения в столбце «Качество изделия»).
- На вкладке Формулы щелкните стрелку рядом с кнопкой Автоумма и выберитесреднее значение . Результат — 8,166666667.
Расчет среднего для несмежных ячеек
- Выберите ячейку, в которой должно отображаться среднее значение, например ячейку A8, которая находится слева ячейки с текстом «Среднее качество изделия» в примере данных.
- На вкладке Формулы щелкните стрелку рядом с кнопкой Автоумма кнопку Среднее инажмите клавишу RETURN.
- Щелкните ячейку, которая содержит только что найденное среднее значение (ячейка A8 в этом примере). Если используется образец данных, формула отображается в строка формул, =СС00(A2:A7).
- В строке формул выделите содержимое между скобками (при использовании примера данных — A2:A7).
- Удерживая нажатой клавишу , щелкните ячейки, для чего нужно вычесть среднее значение, и нажмите клавишу RETURN. Например, выберите A2, A4 и A7 и нажмите клавишу RETURN. Выделенная ссылка на диапазон в функции СРЗНАЧ заменится ссылками на выделенные ячейки. В приведенном примере результат будет равен 8.
Расчет среднего взвешенного значения
В приведенном ниже примере рассчитывается средняя цена за изделие по всем заказам, каждый из которых содержит различное количество изделий по разной цене.
- Выделите ячейку A9, расположенную слева от ячейки с текстом «Средняя цена изделия».
- На вкладке Формулы нажмите кнопку Вставить функцию, чтобы открыть панель Построитель формул.
- В списке построителя формул дважды щелкните функцию СУММПРОИЗВ.
Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СУММПРОИЗВ.
Расчет среднего, исключающего определенные значения
Вы можете создать формулу, которая исключает определенные значения. В приведенном ниже примере создается формула для расчета среднего качества всех изделий, у которых оценка качества выше 5.
- Выделите ячейку A10, расположенную слева от ячейки с текстом «Среднее качество всех изделий с оценкой качества выше 5».
- На вкладке Формулы нажмите кнопку Вставить функцию, чтобы открыть панель Построитель формул.
- В списке построителя формул дважды щелкните функцию СРЗНАЧЕСЛИ.
Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СРЗНАЧЕСЛИ.
Совет: Чтобы использовать функцию СРЗНАЧЕСЛИ для расчета среднего без нулевых значений, введите выражение «<>0″ в поле условие.
Чтобы научиться вычислять средние значения, используйте предоставленные образцы данных и описанные ниже процедуры.
Копирование примера данных
Чтобы лучше понять описываемые действия, скопируйте пример данных в ячейку A1 пустого листа.
- Создайте пустую книгу или лист.
- Выделите приведенный ниже образец данных.
Примечание: Не выделяйте заголовки строк или столбцов (1, 2, 3. A, B, C. ) при копировании данных примера на пустой лист.
Выбор примеров данных в справке
| Качество изделия | Цена за единицу | Количество заказанных изделий |
|---|---|---|
| 10 | 35 | 500 |
| 7 | 25 | 750 |
| 9 | 30 | 200 |
| 10 | 40 | 300 |
| 8 | 27 | 400 |
| 5 | 20 | 200 |
| Среднее качество изделий | ||
| Средняя цена изделия | ||
| Среднее качество всех изделий с оценкой качества выше 5 |
Расчет простого среднего значения
Рассчитаем среднее качество изделий двумя разными способами. Первый способ позволяет быстро узнать среднее значение, не вводя формулу. Второй способ предполагает использование функции «Автосумма» для расчета среднего значения и позволяет вывести его на листе.
Быстрый расчет среднего
- Выделите ячейки с A2 по A7 (значения в столбце «Качество изделия»).
- На строка состояния щелкните стрелку всплывающее меню (если вы используете образец данных, вероятно, область содержит текст Sum=49),а затем выберите среднее . Результат — 8,166666667.
Примечание: Если строка состояния не отображается, в меню Вид выберите пункт Строка состояния.
Расчет среднего с отображением на листе
- Выберите ячейку, в которой должно отображаться среднее значение, например ячейку A8, которая находится слева ячейки с текстом «Среднее качество изделия» в примере данных.
- На панели инструментов Стандартная под названием книги щелкните стрелку рядом с кнопкой кнопку Среднее и нажмите клавишу RETURN. Результат составляет 8,166666667 — это средняя оценка качества всех изделий.
Совет: Если вы работаете с данными, в которые перечислены числа в строке, выберите первую пустую ячейку в конце строки, а затем щелкните стрелку рядом с кнопкой .
Расчет среднего для несмежных ячеек
Существует два способа расчета среднего для ячеек, которые не следуют одна за другой. Первый способ позволяет быстро узнать среднее значение, не вводя формулу. Второй способ предполагает использование функции СРЗНАЧ для расчета среднего значения и позволяет вывести его на листе.
Быстрый расчет среднего
-
Выделите ячейки, для которых вы хотите найти среднее значение. Например, выделите ячейки A2, A4 и A7.
Совет: Чтобы выбрать несмещные ячейки, щелкните их, удерживая клавишу.
Примечание: Если строка состояния не отображается, в меню Вид выберите пункт Строка состояния.
Расчет среднего с отображением на листе
- Выберите ячейку, в которой должно отображаться среднее значение, например ячейку A8, которая находится слева ячейки с текстом «Среднее качество изделия» в примере данных.
- На панели инструментов Стандартная под названием книги щелкните стрелку рядом с кнопкой кнопку Среднее и нажмите клавишу RETURN.
- Щелкните ячейку, которая содержит только что найденное среднее значение (ячейка A8 в этом примере). Если используется образец данных, формула отображается в строка формул, =СС00(A2:A7).
- В строке формул выделите содержимое между скобками (при использовании примера данных — A2:A7).
- Удерживая нажатой клавишу , щелкните ячейки, для чего нужно вычесть среднее значение, и нажмите клавишу RETURN. Например, выберите A2, A4 и A7 и нажмите клавишу RETURN. Выделенная ссылка на диапазон в функции СРЗНАЧ заменится ссылками на выделенные ячейки. В приведенном примере результат будет равен 8.
Расчет среднего взвешенного значения
В приведенном ниже примере рассчитывается средняя цена за изделие по всем заказам, каждый из которых содержит различное количество изделий по разной цене.
- Выделите ячейку A9, расположенную слева от ячейки с текстом «Средняя цена изделия».
- На вкладке Формулы в разделе Функция выберите пункт Построитель формул.
- В списке построителя формул дважды щелкните функцию СУММПРОИЗВ.
Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СУММПРОИЗВ.
Расчет среднего, исключающего определенные значения
Вы можете создать формулу, которая исключает определенные значения. В приведенном ниже примере создается формула для расчета среднего качества всех изделий, у которых оценка качества выше 5.
- Выделите ячейку A10, расположенную слева от ячейки с текстом «Среднее качество всех изделий с оценкой качества выше 5».
- На вкладке Формулы в разделе Функция выберите пункт Построитель формул.
- В списке построителя формул дважды щелкните функцию СРЗНАЧЕСЛИ.
Совет: Чтобы быстро найти функцию, начните вводить ее имя в поле Поиск функции. Например, начните вводить СРЗНАЧЕСЛИ.
Совет: Чтобы использовать функцию СРЗНАЧЕСЛИ для расчета среднего без нулевых значений, введите выражение «<>0″ в поле условие.
Как посчитать среднее значение в Excel и найти правильный ответ
Как посчитать среднее значение в Excel? Выделите необходимую группу чисел, жмите на кнопку суммы на рабочей панели (∑), а в выпадающем списке жмите на «Среднее». При желании можно вручную вести параметр «=СРЗНАЧ» с указанием диапазона ячеек. Ниже рассмотрим, как правильно рассчитать параметр для разных случаев — с текстом, с условием, средневзвешенное и т. д.
Простой расчет
Наиболее простой метод, как вычислить среднее значение в Excel — сделать это с помощью специального значка на панели инструментов Майкрософт Эксель. Для этого сделайте следующее:
- Выделите группу числе в столбце, для которых нужно рассчитать среднее значение в Экселе.
- Перейдите в раздел «Главная».
- Найдите значок «∑» на рабочей панели в блоке редактирования.
- В выпадающем списке жмите на «Среднее».
- Получите результат, который выводится в качестве усредненного арифметического набора чисел.
Этот метод подходит легкостью и удобством применения, но у него имеются и недостатки. В частности, с его помощью можно подсчитать усредненный параметр для чисел, которые находятся в одном столбце / строке. Если же нужно работать с массивом данных или ячейками, которые находятся в разных участках, расчет среднего значения в Excel будет затруднен.
Альтернативный вариант — ручной ввод необходимой опции с помощью функции «СРЗНАЧ». Чтобы посчитать нужные данные, введите команду по следующему алгоритму: «=СРЗНАЧ(адрес_диапазона_ячеек(цифра); адрес_диапазона_ячеек(цифра)). Он не такой быстрый, как рассмотрен выше, но позволяет найти среднее число в Excel даже в сложных ситуациях.
Как посчитать для нескольких ячеек
Если нужно посчитать среднее значение в Excel для большой группы ячеек, можно использовать «Мастер функций». Он позволяет считать среднее значение в Excel с помощью озвученной выше формулы, но сам процесс проходит немного проще. Алгоритм действий следующий:
- Кликните по ячейке, где нужно посчитать требуемое значение в Эксель.
- Жмите на «Вставить функцию», находящуюся с левой стороны от строки формул или же кликните Shift+F3.
- Запустите «Мастер функций», в перечне доступных опций найдите «СРЗНАЧ».
- Выделите его и кликните на «ОК».
- В графе «Число» укажите аргументы функции, а именно цифры или адреса секций, где они находятся. Для большего удобства кликните на клавишу, которая находится с правой стороны от поля ввода данных.
- Выделите те ячейки, для которых нужно посчитать данные, а после кликните на клавишу с левой стороны от поля ввода данных для возврата к окну аргументов функции.
Если нужно высчитать среднее значение в Excel от цифр, которые расположены в разных ячейках, сделайте рассмотренные выше действия, но в графе «Число 2». Действовать нужно до тех пор, пока все группы не окажутся выделенными. Результат указывается в той ячейке, которая была выделена перед входом в «Мастер функций».
Через формулы
Следующий способ, как найти среднее значение в Excel — через формулы. Для запуска параметра «СРЗНАЧ» сделайте следующее:
- Кликните на раздел «Формулы».
- Выделите ячейку, где нужно получить результат.
- В секции «Библиотека функций» ленты кликните «Другие …».
- Кликните на «Статические» и «СРЗНАЧ».
- Выполните остальную работу по принципу, который рассмотрен выше.
Как посчитать с условием
Нередко возникает вопрос, как найти среднее арифметическое в Excel с помощью опции условия. В такой ситуации для расчета берутся лишь те цифры из диапазона, соответствующего определенному условию, к примеру, если параметр больше или меньше определенного числа.
Для такой опции применяется функция «СРЗНАЧЕСЛИ». Для ее запуска можно использовать три пути:
- «Панель формул».
- «Мастер функций».
- «Ручной ввод в ячейку».
После открытия окна аргументов остается ввести параметры, а в поле «Диапазон» ввести пределы ячеек, которые будут участвовать для определения среднего арифметического. Здесь принцип действия такой же, как в случае с обычным «СРЗНАЧ».
Чтобы в Экселе сделать среднее значение с условием, необходимо в графе «Условие» указать определенный параметр, в отношении которого будет осуществляться сравнение. К примеру, это может быть «
Графу «Диапазон усреднения» можно не заполнять. Вводить в нее данные обязательно только с применением ячеек с текстом. Как только все сведения указаны, кликните на «ОК». Среднее значение в Excel будет показано для определенного диапазона, кроме данных, которые не соответствуют заявленному условию.
С текстом
Бывают ситуации, когда необходимо посчитать среднее значение в Эксель вместе с текстом. В этом случае используется функция, имеющая следующий вид: =СРЗНАЧЕСЛИ($A$2:$A$13;A7;$B$2:$B$13). Здесь в графа «А» содержится текст, а «В» — цифры. В качестве критерия поиска может использоваться определенный адрес ячейки с необходимым словом. Как вариант, его можно написать в необходимом месте. При этом расчет среднего значения производится для второй колонки.
Как посчитать средневзвешенное значение
Следующий вопрос — как вывести среднее значение в Excel для средневзвешенного параметра. Чтобы посчитать эти данные, потребуются функции СУММПРОИЗВ и СУММ. Для расчета средневзвешенного параметра используется следующая формула (пример):
Расчет в этом случае выполняется в два шага:
- СУММПРОИЗВ позволяет определить сумму.
- Применение СУММ позволяет определить общее количество каких-либо единиц, к примеру, реализованной продукции.
Расчет без игнорирования нулевых значений
Итого в Экселе приходится посчитать среднее значение столбцов с учетом нулевых параметров. В таком случае нужно использовать опции С AVERAGE и ЕСЛИ. При этом сам запрос может иметь следующий вид =С AVERAGEIF(A2:A7;»<>0″).
Как видно из сказанного, посчитать среднее значение в Excel можно разными способами — через формулы, путем ввода ручные или с помощью мастера. При этом в некоторых случаях может потребоваться применение дополнительных функций.
В комментариях расскажите, пригодились ли вам указанные выше рекомендации, и какие еще методы можно использовать для решения поставленной задачи.
СРЗНАЧЕСЛИМН (функция СРЗНАЧЕСЛИМН)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции СРЗНАЧЕСЛИМН в Microsoft Excel.
Описание
Возвращает среднее значение (среднее арифметическое) всех ячеек, которые соответствуют нескольким условиям.
Синтаксис
Аргументы функции СРЗНАЧЕСЛИМН указаны ниже.
- Диапазон_усреднения: обязательный. Одна или несколько ячеек для вычисления среднего с числами или именами, массивами или ссылками, содержащими числа.
- Диапазон_условий1, диапазон_условий2, … параметр «диапазон_условий1» — обязательный, остальные диапазоны условий — нет. От 1 до 127 интервалов, в которых проверяется соответствующее условие.
- Условие1, условие2, … Параметр «условие1» является обязательным, остальные условия — нет. От 1 до 127 условий в форме числа, выражения, ссылки на ячейку или текста, определяющих ячейки, для которых будет вычисляться среднее. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.
Замечания
- Если «диапазон_усреднения» является пустым или текстовым значением, то функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
- Если ячейка в диапазоне условий пустая, функция СРЗНАЧЕСЛИМН обрабатывает ее как ячейку со значением 0.
- Ячейки в диапазоне, которые содержат значение ИСТИНА, оцениваются как 1; ячейки в диапазоне, которые содержат значение ЛОЖЬ, оцениваются как 0 (ноль).
- Каждая ячейка в аргументе «диапазон_усреднения» используется в вычислении среднего значения, только если все указанные для этой ячейки условия истинны.
- В отличие от аргументов диапазона и условия в функции СРЗНАЧЕСЛИ, в функции СРЗНАЧЕСЛИМН каждый диапазон_условий должен быть одного размера и формы с диапазоном_суммирования.
- Если ячейки в параметре «диапазон_усреднения» не могут быть преобразованы в численные значения, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
- Если нет ячеек, которые соответствуют условиям, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
- В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).
Примечание: Функция СРЗНАЧЕСЛИМН измеряет среднее значение распределения, то есть расположение центра набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения:
- Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
- Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
- Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При смещенном распределении множества чисел значения могут быть разными.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.