Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Визуализация и дашборды

Как построить кривую нормального распределения для сотрудников в Excel

forest_paw 07.10.2025 1759

Как построить кривую нормального распределения для сотрудников в Excel

Это руководство покажет, как создать кривую нормального распределения (колокол Гаусса) в Excel для визуального анализа оценок сотрудников или клиентов. Вы научитесь рассчитывать среднее значение и стандартное отклонение, а также правильно оформлять диаграмму с подписями.

Здесь собраны практические приемы для тех, кто хочет строить графики в Excel, оформлять диаграммы в экселе и делать отчеты нагляднее для команды и клиентов.

Мы разместим оценки на теоретической нормальной кривой с параметрами группы. Такой график не показывает частоты оценок и не доказывает нормальность их распределения. Для анализа фактического распределения дополнительно постройте гистограмму. Действия с подписями ниже показаны для настольного Excel для Windows.

  1. Подготовьте данные. Создайте таблицу с именами сотрудников в столбце A и их оценками (баллами) в столбце B.
  2. Рассчитайте ключевые параметры. В отдельные ячейки вычислите среднее арифметическое с помощью функции Русская версия: =СРЗНАЧ(B2:B14)English version: =AVERAGE(B2:B14) и стандартное отклонение с помощью Русская версия: =СТАНДОТКЛОН.Г(B2:B14)English version: =STDEV.P(B2:B14). Здесь считаем всех 13 сотрудников всей рассматриваемой совокупностью. Для выборки из большей группы используйте СТАНДОТКЛОН.В / STDEV.S. Разместите среднее в F2, а отклонение в F3; значение F3 должно быть больше нуля.
13 сотрудников в A2:A14. Оценки в B2:B14. Справа рассчитано среднее значение с помощью =AVERAGE(B2:B14) и стандартное отклонение с помощью =STDEV.P(B2:B14)
13 сотрудников в A2:A14. Оценки в B2:B14. Справа рассчитано среднее значение с помощью =СРЗНАЧ(B2:B14) и стандартное отклонение с помощью =СТАНДОТКЛОН.Г(B2:B14) / =STDEV.P(B2:B14)
  1. Добавьте столбец Y. В столбце C рассчитайте значения Y для построения кривой, используя функцию нормального распределения. Формула для первой ячейки (C2) будет выглядеть так: Русская версия: =НОРМ.РАСП(B2;$F$2;$F$3;ЛОЖЬ)English version: =NORM.DIST(B2,$F$2,$F$3,FALSE), где $F$2 и $F$3 — закреплённые ссылки на среднее и стандартное отклонение. Скопируйте формулу до C14.
Оценка в столбце B будет значением X. Чтобы рассчитать значение Y в столбце C, используйте =NORM.DIST(B2,$F$2,$F$3,FALSE).
Оценка в столбце B будет значением X. Чтобы рассчитать значение Y в столбце C, используйте =НОРМ.РАСП(B2;$F$2;$F$3;ЛОЖЬ).
  1. Отсортируйте данные. Отсортируйте диапазон A1:C14 с заголовками по столбцу оценок B по возрастанию, сохраняя имена и оценки в одной строке. Ячейки параметров F2:F3 в сортировку не включайте. Так линия будет соединять точки слева направо.
Теперь у вас есть Имя в A, Оценка (X) в B и Y в C. Отсортируйте сотрудников по Оценке по возрастанию.
Теперь у вас есть Имя в A, Оценка (X) в B и Y в C. Отсортируйте сотрудников по Оценке по возрастанию.
  1. Постройте диаграмму. Выделите B1:C14. На вкладке «Вставка» выберите точечную диаграмму с гладкими кривыми и маркерами. Линия между 13 наблюдениями лишь приближает теоретическую кривую; для её полного контура нужен отдельный равномерный ряд X.
  2. Добавьте подписи данных. Это самый важный шаг для идентификации точек.
    • Щелкните по диаграмме и нажмите значок «+» справа.
    • Выберите «Подписи данных» → «Дополнительные параметры».
    • В открывшейся панели нажмите на значок гистограммы и выберите «Значения из ячеек».
    • Укажите диапазон A2:A14 с именами сотрудников. Если нужны только имена, снимите флажок «Значение Y».
Сложная часть — подписать каждую точку именем человека. На этом скриншоте показана панель 'Формат подписей данных'. Выберите 'Значения из ячеек' и укажите на имена в столбце A.
Сложная часть — подписать каждую точку именем человека. На этом скриншоте показана панель 'Формат подписей данных'. Выберите 'Значения из ячеек' и укажите на имена в столбце A.

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

  1. Щелкните по любой подписи — выделятся все подписи на диаграмме.
  2. Щелкните второй раз по одной из наложенных подписей. Теперь выбран только этот элемент.
  3. Перетащите выбранную подпись в нужное место.
13 сотрудников расположены на кривой нормального распределения, от Хэнка на крайнем левом краю до Майка и Джареда как средних и Гэри и Эда на крайнем правом краю.
13 сотрудников расположены на кривой нормального распределения, от Хэнка на крайнем левом краю до Майка и Джареда как средних и Гэри и Эда на крайнем правом краю.

Готово! Теперь у вас есть наглядная кривая нормального распределения, показывающая, как оценки ваших сотрудников соотносятся со средним значением по группе. Этот инструмент отлично подходит для анализа эффективности, оценки результатов тестирования или сегментации клиентов.

Поделиться:

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