Как рассчитать среднее значение в Excel

Обновлено: 21.11.2024

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 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции СРЗНАЧ в Microsoft Excel.

Описание

Возвращает среднее (среднее арифметическое) аргументов. Например, если диапазон A1:A20 содержит числа, формула =СРЗНАЧ(A1:A20) возвращает среднее значение этих чисел.

Синтаксис

Синтаксис функции СРЗНАЧ имеет следующие аргументы:

Обязательный номер 1. Первое число, ссылка на ячейку или диапазон, для которого вы хотите получить среднее значение.

Число2, . Необязательный. Дополнительные числа, ссылки на ячейки или диапазоны, для которых требуется среднее значение, не более 255.

Примечания

Аргументами могут быть числа или имена, диапазоны или ссылки на ячейки, содержащие числа.

Логические значения и текстовые представления чисел, которые вы вводите непосредственно в список аргументов, не учитываются.

Если аргумент ссылки на диапазон или ячейку содержит текст, логические значения или пустые ячейки, эти значения игнорируются; однако включаются ячейки с нулевым значением.

Аргументы, которые являются значениями ошибок или текстом, который не может быть преобразован в числа, вызывают ошибки.

Если вы хотите включить логические значения и текстовые представления чисел в ссылку как часть расчета, используйте функцию СРЗНАЧА.

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

Среднее значение, которое представляет собой среднее арифметическое и рассчитывается путем сложения группы чисел и последующего деления на количество этих чисел. Например, среднее значение 2, 3, 3, 5, 7 и 10 равно 30, деленному на 6, что равно 5.

Медиана — среднее число в группе чисел; то есть половина чисел имеет значения больше медианы, а половина чисел имеют значения меньше медианы. Например, медиана 2, 3, 3, 5, 7 и 10 равна 4.

Способ, который является наиболее часто встречающимся числом в группе чисел. Например, режим 2, 3, 3, 5, 7 и 10 равен 3.

Для симметричного распределения группы чисел эти три показателя центральной тенденции одинаковы. При асимметричном распределении группы чисел они могут быть разными.

Совет. При усреднении ячеек учитывайте разницу между пустыми ячейками и ячейками, содержащими нулевое значение, особенно если вы сняли флажок Показывать ноль в ячейках с нулевым значением в диалоговом окне Параметры Excel в настольное приложение Excel. Если выбран этот параметр, пустые ячейки не учитываются, а учитываются нулевые значения.

Чтобы установить флажок Показывать ноль в ячейках, содержащих нулевое значение, выполните следующие действия:

На вкладке "Файл" нажмите "Параметры", а затем в категории "Дополнительно" найдите раздел "Параметры отображения для этого листа".

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.

Функция Excel AVERAGE вычисляет среднее (среднее арифметическое) предоставленных чисел. СРЗНАЧ может обрабатывать до 255 отдельных аргументов, включая числа, ссылки на ячейки, диапазоны, массивы и константы.

число1 – число или ссылка на ячейку, которая относится к числовым значениям.
число2 – [необязательно] число или ссылка на ячейку, которая ссылается на числовые значения.

Функция СРЗНАЧ вычисляет среднее значение чисел, переданных в качестве аргументов. Чтобы вычислить среднее значение, Excel суммирует все числовые значения и делит их на количество числовых значений.

СРЗНАЧ принимает несколько аргументов в виде число1, число2, число3 и т. д., всего до 255 аргументов. Аргументы могут включать числа, ссылки на ячейки, диапазоны, массивы и константы. Пустые ячейки и ячейки, содержащие текст или логические значения, игнорируются. Однако включаются нулевые (0) значения. Вы можете игнорировать нулевые (0) значения с помощью функции СРЗНАЧЕСЛИМН, как описано ниже.

Функция СРЗНАЧ игнорирует логические значения и числа, введенные в виде текста. Если вам нужно включить эти значения в среднее значение, см. функцию СРЗНАЧA.

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

Основное использование

Обычным способом использования функции СРЗНАЧ является указание диапазона, как показано ниже. Формула в F3, скопированная вниз, выглядит так:

В каждой новой строке СРЗНАЧ вычисляет среднее значение результатов теста для каждого человека.

Пустые ячейки

Функция СРЗНАЧ автоматически игнорирует пустые ячейки. Обратите внимание, что на приведенном ниже экране ячейка C4 пуста, СРЗНАЧ просто игнорирует ее и вычисляет среднее значение только с ячейками B4 и D4:

Однако обратите внимание, что нулевое (0) значение в C5 включено в среднее значение, поскольку оно является допустимым числовым значением. Чтобы исключить нулевые значения, используйте вместо них СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН. В приведенном ниже примере СРЗНАЧЕСЛИ используется для исключения нулевых значений. Как и функция СРЗНАЧ, функция СРЗНАЧЕСЛИ автоматически исключает пустые ячейки.

Смешанные аргументы

Числа, предоставляемые функции AVERAGE, могут быть как ссылками, так и константами:

В среднем по критериям

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

Функция СРЗНАЧЕСЛИМН также может применять несколько критериев.

В среднем топ-3

Комбинируя функцию СРЗНАЧ с функцией НАИБОЛЬШИЙ, вы можете вычислить среднее значение первых n значений. В приведенном ниже примере формула в столбце I вычисляет среднее значение трех лучших результатов викторины в каждой строке:

Средневзвешенное значение

Чтобы вычислить взвешенное среднее значение, используйте функцию СУММПРОИЗВ, как показано ниже:

Если количество числовых значений равно нулю, функция ЕСЛИ возвращает пустую строку (""). Когда счетчик больше нуля, функция СРЗНАЧ возвращает среднее значение. Этот пример объясняет эту идею более подробно.

Усреднение вручную

Для расчета среднего значения функция СРЗНАЧ суммирует все числовые значения и делит их на количество числовых значений. Это поведение можно воспроизвести с помощью функций SUM и COUNT вручную следующим образом:

Махеш Маквана

Махеш Маквана
Писатель

Махеш Маквана — технический писатель-фрилансер, специализирующийся на написании практических руководств. Он пишет технические учебники уже более десяти лет. Он написал для некоторых известных технических сайтов, включая MakeUseOf, MakeTechEasier и Online Tech Tips. Подробнее.

Поиск среднего значения (также называемого средним арифметическим) полезен при выполнении различных расчетов. Microsoft Excel позволяет легко вычислить среднее значение, и мы покажем вам несколько способов сделать это.

Что такое среднее значение в Excel?

Среднее числовое значение рассчитывается путем сложения всех чисел и деления суммы этих чисел на количество чисел.

Например, если у вас есть числа 10, 15, 20, 25 и 30, вы сначала сложите все эти числа, а затем разделите эту сумму на 5, потому что у вас всего пять чисел.

Вот математическая формула для этого:

По умолчанию при расчете среднего значения Excel игнорирует все пустые ячейки, но учитывает ячейки, содержащие нули. Вы также можете игнорировать нулевые ячейки, как мы объясним ниже.

Вычислить среднее значение в Excel с помощью ленты

Быстрый способ расчета среднего значения в Excel – использование параметра на ленте.

Чтобы использовать его, сначала откройте электронную таблицу, содержащую ваши числа, в Microsoft Excel. В электронной таблице выберите числа, для которых вы хотите найти среднее значение.

В верхней части ленты Excel нажмите вкладку "Главная".

На вкладке "Главная" в разделе "Редактирование" выберите значок со стрелкой вниз рядом с параметром "Сумма".

В расширенном меню нажмите «Среднее», чтобы найти среднее значение для ваших чисел.

В конце выбранных ячеек вы увидите среднее значение ваших чисел.

Аналогичным образом вы также можете рассчитать средневзвешенное значение в Excel.

Вычислить среднее значение в Excel, включая нули

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

Чтобы использовать эту функцию, сначала откройте электронную таблицу в Microsoft Excel. В электронной таблице щелкните ячейку, в которой вы хотите отобразить полученное среднее значение.

В выбранной ячейке введите следующую функцию и нажмите Enter. Замените C2 и C5 в функции диапазоном, в котором находятся ваши числа.

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

Если вы хотите напрямую использовать числа в функции, введите свои числа в функцию, как показано ниже. Разделяйте числа запятыми.

Нажмите Enter после ввода формулы, и вы увидите среднее значение ваших чисел в выбранной ячейке.

Вычислить среднее значение в Excel, исключая нули

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

Сначала откройте электронную таблицу в Microsoft Excel. Затем выберите ячейку, в которой вы хотите отобразить результат.

В выбранной ячейке введите следующую функцию и нажмите Enter. В этой функции замените C2 и C5 диапазоном ваших чисел.

Excel рассчитает среднее значение, игнорируя все ячейки, содержащие нули.

И это все три, чтобы найти среднее значение в Microsoft Excel. Мы надеемся, что это поможет вам.

› Как рассчитать среднее значение в Google Таблицах
› Как делить числа в Microsoft Excel
› 9 основных функций Google Таблиц, которые вы должны знать
› Почему прозрачные чехлы для телефонов желтеют?
› Худшее, что есть в телефонах Samsung, — это программное обеспечение Samsung.
› Что означает XD и как вы его используете?
›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)
› Как установить Google Play Маркет в Windows 11

Задайте вопрос или присоединитесь к обсуждению всего, что связано с Excel, на нашем канале Slack.

Как рассчитать средние значения в Excel

Хотите верьте, хотите нет, но существует много разных средних значений и разные способы их расчета. В этом ресурсе рассматриваются следующие методы:

СРЕДНЕЕ

Наиболее общепризнанным средним значением является среднее арифметическое, и Excel использует для его нахождения функцию СРЗНАЧ. Функция Excel AVERAGE используется для генерации числа, представляющего типичное значение из диапазона, распределения или списка чисел. Он рассчитывается путем сложения всех чисел в списке, а затем деления общей суммы на количество значений в списке.

Загрузите бесплатный файл среднего значения в формате Excel!

Используйте это бесплатное среднее значение в файле Excel, чтобы практиковаться вместе с учебным пособием.

Синтаксис

Функция СРЗНАЧ в Excel проста. Синтаксис:

Вместо явных значений можно использовать диапазоны или ссылки на ячейки.

Функция СРЗНАЧ может обрабатывать до 255 аргументов, каждый из которых может быть значением, ссылкой на ячейку или диапазоном. Требуется только один аргумент, но, конечно, если вы используете функцию СРЗНАЧ, скорее всего, у вас есть по крайней мере два.

Приведенная ниже формула вычисляет среднее значение чисел 100, 95 и 80.

Чтобы вычислить среднее значение в ячейках B2, B3, B4 и B5, введите:

Это можно ввести непосредственно в ячейку или строку формул или выбрать на листе, выбрав первую ячейку в диапазоне и перетащив мышь на последнюю ячейку в диапазоне.

Чтобы вычислить среднее значение несмежных (несмежных) ячеек, просто удерживайте клавишу Control (или клавишу Command для пользователей Mac) во время выбора.

Если ссылки на ячейки вводятся непосредственно в ячейку или строку формул, несмежные ссылки разделяются запятыми. Например:

Примечания

При использовании функции СРЗНАЧ помните следующее.

Слово «Больной» в ячейке B6 (ниже) приводит к тому, что функция СРЗНАЧ полностью игнорирует эту ячейку. Это означает, что средний балл в ячейке I6 был рассчитан с использованием значений в диапазоне от C8 до H8, а общая сумма была разделена на 6 субъектов вместо 7.

При определении количества значений, на которое нужно разделить итог, нули считаются допустимыми суммами и, следовательно, уменьшают общее среднее значение распределения. Обратите внимание, что средний балл учащегося J сильно отличается от среднего учащегося E, несмотря на то, что их общие оценки были одинаковыми.

СРЕДНЕЕ

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

Вместо явных значений можно использовать диапазон или ссылку на ячейку.

СРЗНАЧА оценивает текстовые значения как ноль, а логическое значение ИСТИНА оценивается как 1. Логическое значение ЛОЖЬ считается нулем.

Сравните разницу в результатах между использованием СРЗНАЧ и СРЗНАЧА в приведенном ниже примере.

Средние значения для учащегося E и учащегося J теперь одинаковы при использовании функции СРЗНАЧA.

СРЕДНЕЕСЛИ

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

Синтаксис функции СРЗНАЧЕСЛИ:

Диапазон — это место, где мы можем ожидать найти ячейки, соответствующие критериям.
Критерии – это значение или выражение, которое Excel должен искать в диапазоне.
Средний_диапазон – необязательный аргумент. Это диапазон ячеек, в которых находятся значения, подлежащие усреднению. Если средний_диапазон не указан, используется диапазон.

СРЕДНЕЕСЛИ, пример 1

Например, из этого списка различных цен на фрукты мы можем попросить Excel извлечь только те ячейки, в которых указано «яблоки» в столбце A, и найти их среднюю цену в столбце B.

СРЕДНЕЕСЛИ, пример 2

Критерии в функции СРЗНАЧЕСЛИ также могут быть в форме логического выражения, как в приведенном ниже примере:

Приведенная выше формула найдет среднее значение в диапазоне от B4 до H4, не равное нулю.Обратите внимание, что третий (необязательный) аргумент опущен, поэтому ячейки в диапазоне используются для расчета среднего значения.

Поскольку ячейки, которые оцениваются как нули, опускаются из-за наших критериев, обратите внимание на разницу в результатах учащихся E и J ниже при использовании функции СРЗНАЧЕСЛИ.

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

МЕДИАНА

Среднее арифметическое может быть наиболее часто используемым методом нахождения среднего, но ни в коем случае не единственным. Одним из результатов использования среднего арифметического является то, что оно позволяет очень высоким или очень низким числам влиять на результат, тем самым значительно изменяя результаты.

Возьмем, к примеру, следующий список чисел:

22, 1, 14, 21, 15, 16, 18, 100, 19, 19, 22, 25, 18

Нахождение среднего арифметического даст результат 23,8.

Однако, если внимательно посмотреть на распределение чисел в списке, вряд ли можно сказать, что 23,8 — это среднее значение этих чисел. Проблема, конечно, в том, что число 100 является выбросом и увеличивает сумму чисел.

Поэтому в некоторых ситуациях предпочтительнее использовать функцию МЕДИАНА. Эта функция определяет числовой порядок оцениваемых значений и использует среднее значение в качестве среднего.

Вместо явных значений можно использовать диапазон или ссылку на ячейку.

В приведенном выше примере числовой порядок будет следующим:

1, 14, 15, 16, 18, 18, 19, 19, 21, 22, 22, 25, 100

В распределении 13 чисел, поэтому седьмое число является средним значением. Таким образом, медиана будет равна 19.

Если число значений четное, медиана будет определяться путем нахождения среднего значения двух чисел в середине распределения. Итак, для значений 7,9,9,11,14,15 медиана будет (9+11)/2=10.

Функция МЕДИАНА игнорирует ячейки, которые содержат текст, логические значения или не содержат значений.

Третий метод определения среднего значения набора чисел – это определение режима или числа, которое повторяется чаще всего.

В настоящее время в Excel есть три функции режима. Классический MODE следует синтаксису:

В этой функции Excel оценивает значения в списке или диапазоне и выбирает наиболее часто встречающееся число в качестве среднего значения группы.

Однако бывают случаи, когда режимом может считаться более одного числа. Например, рассмотрим следующий список:

1, 14, 15, 16, 18, 18, 19, 19, 21, 22, 22, 25, 100

Числа 18, 19 и 22 встречаются по два раза. Какой режим? Microsoft выбирает в качестве режима первое появляющееся значение — в приведенном выше случае 18.

Если бы те же числа были расположены в обратном порядке, то модой считалось бы число 22.

Если бы числа были расположены в случайном порядке, Excel выбрал бы 18, 19 и 22 в зависимости от того, какое число появится в распределении первым.

Например, в списке:

19, 22, 1, 14, 21, 15, 16, 18, 100, 19, 22, 25, 18

Функция MODE считает режимом 19.

РЕЖИМ.МУЛЬТ

Функция MODE.MULT — это решение несоответствий, возникающих в описанном выше сценарии. Это позволяет нам предвидеть и учитывать возможность того, что в группе чисел может быть более одного режима.

Поскольку MODE.MULT является функцией массива (CSE), следующие шаги при использовании этой функции:

Выберите вертикальный диапазон для вывода.
Введите формулу РЕЖИМ.МУЛЬТ
Одновременно нажмите Control + Shift + Enter.

нет повторяющихся значений или
в выходном диапазоне нет дополнительных режимов.

MODE.SNGL

Как и функция МОДА.МУЛЬТ, функция МОДА.ОДИН была реализована в Excel 2010. Синтаксис:

Функция MODE.SNGL ведет себя как классическая функция MODE при определении выходных данных.

Креативное использование функции СРЗНАЧ

Топ-3

Мы можем объединить функцию СРЗНАЧ с функцией НАИБОЛЬШИЙ, чтобы определить среднее значение для n” первых значений.

Функция НАИБОЛЬШИЙ извлекает n t h наибольшее число из диапазона, используя формат

где k — число n t h.

Используя этот формат, мы можем отобразить число в 1-м, 2-м, 5-м или любом другом ранге.

Чтобы получить среднее значение трех самых больших чисел в диапазоне, мы должны вложить функции СРЗНАЧ и НАИБОЛЬШИЙ следующим образом:

Когда мы заключаем в скобки аргумент k, Excel определяет первое, второе и третье по величине числа в массиве, а функция СРЗНАЧ находит их среднее значение.

Ниже 3

Мы можем использовать аналогичную логику, чтобы найти 3 нижних числа в группе чисел, используя функцию МАЛЕНЬКИЙ.

Следующий формат найдет среднее значение трех наименьших чисел в массиве.

Подробнее о формулах и функциях Excel

Три основных метода нахождения среднего значения в Excel: СРЗНАЧ (среднее), МЕДИАНА (среднее значение) и РЕЖИМ (частота). Все они просты в использовании, поэтому выберите тот, который подходит для вашего типа данных и вопросов, на которые вы хотите ответить.

Чтобы узнать больше о полезных формулах, функциях и практических навыках работы с Excel, попробуйте курс GoSkills Basic и Advanced Excel уже сегодня. Или начните с бесплатного курса Excel за час, чтобы получить базовые знания о формулах.

Изучите Excel бесплатно

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

Читайте также: