Как посчитать средний балл оценок в excel
Перейти к содержимому

Как посчитать средний балл оценок в excel

  • автор:

СРЗНАЧ (функция СРЗНАЧ)

В этой статье описывается синтаксис формулы и использование функции AVERAGE в Microsoft Excel.

Описание

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

Синтаксис

Аргументы функции СРЗНАЧ описаны ниже.

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

Замечания

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

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

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

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

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

Местонахождение флажка Показывать нули в ячейках, которые содержат нулевые значения

  • Откройте вкладку Файл , а затем нажмите кнопку Параметры и в категории Дополнительно найдите группу Показать параметры для следующего листа.

Пример

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

Вычисление среднего значения ряда чисел

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

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

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

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

Вычисление среднего числа в непрерывной строке или столбце

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

  1. Щелкните ячейку ниже или справа от чисел, для которых требуется найти среднее значение.
  2. На вкладке Главная в группе Редактирование щелкните стрелку рядом с элементом

Вычисление среднего числа, не в непрерывной строке или столбце

Для выполнения этой задачи используйте функцию AVERAGE . Скопируйте приведенную ниже таблицу на пустой лист.

Описание (результат)

Среднее значение для всех чисел в списке выше (9,5)

Средние три и последнее число в списке (7,5).

Усреднение чисел в списке, кроме тех, которые содержат ноль, например ячейка A6 (11.4).

Расчет среднего взвешенного значения

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

Скопируйте приведенную ниже таблицу на пустой лист.

Excel: как рассчитать среднее, если между двумя значениями

Excel: как рассчитать среднее, если между двумя значениями

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

=AVERAGEIFS( B:B , B:B ,">=90", B:B ,"<=95") 

Эта конкретная формула будет вычислять среднее значение только для значений, которые находятся между 90 и 95 в столбце B.

Следующие примеры показывают, как использовать эту формулу на практике.

Пример 1. Расчет среднего значения между двумя значениями в Excel (с использованием одного диапазона)

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

Мы можем использовать следующую формулу для расчета среднего экзаменационного балла только для студентов, набравших от 90 до 95 баллов:

=AVERAGEIFS( A:A , A:A ,">=90", A:A ,"<=95") 

На следующем снимке экрана показано, как использовать эту формулу на практике:

Средний балл на экзамене только для студентов, набравших от 90 до 95 баллов, составляет 92,4.

Мы можем вручную проверить, что это правильно:

Средний балл за экзамен = (90 + 92 + 92 + 93 + 95) / 5 = 92,4 .

Пример 2. Вычисление среднего значения между двумя значениями в Excel (с использованием нескольких диапазонов)

Предположим, у нас есть следующий набор данных, который показывает рост (в дюймах) и очки, набранные 15 баскетболистами:

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

=AVERAGEIFS( B:B , A:A ,">=70", A:A ,"<=75") 

На следующем снимке экрана показано, как использовать эту формулу на практике:

Среднее количество очков, набранных игроками ростом от 70 до 75 дюймов, составляет 17,833 .

Мы можем вручную проверить, что это правильно:

Среднее количество набранных баллов = (14 + 14 + 16 + 19 + 20 + 24 / 6 = 17,833 .

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Как посчитать средний балл оценок в excel

DEYNEKINA HR&BA

contact@deynekina.ru
+7-916-571-91-94
DEYNEKINA HR&BA
Как анализировать результаты тестов
с помощью стандартного отклонения?

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

Давайте с вами продолжим разбираться со статистическими показателями.

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

Давайте разберем этот показатель на примере результатов теста на знание Excel, который есть на нашем сайте. Если вы еще не проходили его, просим заглянуть.

Итак, тест состоит из 13 вопросов. Сейчас тест прошли 130 человек. В итоге я делаю такую гистограмму, чтобы посмотреть распределение количества правильных ответов по количеству ответивших.

То есть 8 человек дали 1 верный ответ из 13, 10 человек – 2 верных ответа из 13 и тд.

При этом среднее значение в этом наборе данных равно 6,9, медиана – 7, мода – 7. То есть наши три показателя почти совпадают. Надеюсь, вы помните, чем эти три показателя отличаются друг от друга?
Если нет, ссылки на статьи я дала в первом абзаце этой статьи.

Так что же такое стандартное отклонение? Это показатель, который описывает, насколько значения данных отличаются от среднего значения. То есть среднее расстояние всех данных от среднего значения.

В нашем примере стандартное отклонение показывает, насколько баллы теста по всем ответившим
отличаются от среднего значения, равного 6,9 баллам.

Стандартное отклонение рассчитывается с помощью функции Excel =СТАНДОТКЛОН.В(диапазон) для выборки или =СТАНДОТКЛОН.Г(диапазон) для генеральной совокупности.

В примере мы работаем с выборкой, так как тест на знание Excel прошли лишь 130 человек, а не все HR-специалисты России и СНГ.

Давайте разберем правила расчета стандартного отклонения на примере:

1. Возьмем выборку из 10 участников нашего теста
2. Посчитаем средний балл для выборки участников – 7,1 балл
3. Вычтем из каждого ответа среднее значение
4. Возведем в квадрат получившиеся значения, чтобы избавиться от отрицательных значений
5. Сложим получившиеся значения – 106,9
6. Разделим получившуюся сумму на количество участников выборки, то есть на 10 участников – 10,69
7. Вычтем квадратный корень из получившегося значения – 3,27.

Значение 3,27 и есть наше стандартное отклонение по результату теста от среднего балла по выборке из 10 человек.

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

Если не возводить в квадрат, то сумма значений после этапа вычитания среднего значения из баллов участников всегда будет равна 0. И стандартное отклонение посчитать не получится. Чтобы это обойти, мы делаем взаимоисключающие действия: сначала возводим в квадрат, потом вычитаем квадратный корень. Таким образом мы избавляемся от отрицательных значений.

Конечно, я показала этапы расчета для того, чтобы вы поняли, как рассчитывается этот показатель.
На практике мы считаем стандартное отклонение с помощью одной из функций Excel
=СТАНДОТКЛОН.В(диапазон) или =СТАНДОТКЛОН.Г(диапазон). В нашем случае мы используем =СТАНДОТКЛОН.В(диапазон), так как работаем с выборкой.

Итак, вернемся к общим результатам нашего теста по 130 участникам.

Мы можем описать результаты с помощью следующих данных: среднее значение – 6,9 баллов, стандартное отклонение – 3,3 балла.

В среднем на 3,3 балла отклоняются значения от среднего результата теста. То есть от 3,6 баллов до 10,2 баллов. Это довольно широкий диапазон распределения баллов участников теста.

Стандартное отклонение помогает сравнивать результаты нескольких групп.
Например, в компании провели аттестацию рабочих.

Давайте сравним результаты аттестации цеха 1 и 2.
Если бы мы ориентировались только на среднее значение, мы бы не увидели разницу, так как среднее значение в обоих цехах равно 80 баллам. Но мы видим, что стандартное отклонение отличается: в первом цехе равно 10, во втором – 5. То есть разброс баллов по аттестации рабочих в первом цехе сильнее, чем во втором цехе.

Предположим, у нас есть проходной балл аттестации – 75. Давайте посмотрим, в каком цехе больше рабочих, набравших проходной балл и прошедших аттестацию?

В первом цехе средний балл – 80, но стандартное отклонение – 10, значит, в среднем результаты аттестации находятся в диапазоне от 70 до 90 баллов.
Во втором цехе стандартное отклонение – 5, в среднем результаты аттестации находятся в диапазоне
от 75 до 85 баллов.

Значит, в первом цехе больше сотрудников, которые не набрали проходной балл в 75 баллов, чем во втором.
Давайте глянем на картинку.

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

Итак, что позволяет сделать стандартное отклонение и почему я рекомендую его считать:

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

Все эти показатели и применение их на практике мы разбираем на наших онлайн-курсах. Если вы хотите повысить квалификацию в аналитике, приглашаем в нашу онлайн-школу HR-аналитики .

Your Company

© 2020 Все права защищены

ИП Дейнекина Галина Игоревна
ИНН 231408484160
ОГРНИП 318505300003952

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *