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