Как построить кривую нормального распределения для сотрудников в Excel
Это руководство покажет, как создать кривую нормального распределения (колокол Гаусса) в Excel для визуального анализа оценок сотрудников или клиентов. Вы научитесь рассчитывать среднее значение и стандартное отклонение, а также правильно оформлять диаграмму с подписями.
Вместо абстрактной кривой вы сможете наглядно отобразить, как распределяются результаты вашей команды относительно среднего показателя.
- Подготовьте данные. Создайте таблицу с именами сотрудников в столбце A и их оценками (баллами) в столбце B.
- Рассчитайте ключевые параметры. В отдельные ячейки вычислите среднее арифметическое с помощью функции
=СРЗНАЧ(Б2:Б14) (=AVERAGE(B2:B14))и стандартное отклонение с помощью=СТАНДОТКЛОН.P(Б2:Б14) (=STDEV.P(B2:B14)).
- Добавьте столбец Y. В столбце C рассчитайте значения Y для построения кривой, используя функцию нормального распределения. Формула для первой ячейки (C2) будет выглядеть так:
=НОРМ.РАСП(Б2, Mean, СТАНДОТКЛОН, ЛОЖЬ) (=NORM.DIST(B2, Mean, StDev, FALSE)), где Mean и СТАНДОТКЛОН (StDev) — это ссылки на ячейки с рассчитанными средним и отклонением.
- Отсортируйте данные. Отсортируйте всю таблицу по столбцу с оценками (B) по возрастанию. Это необходимо для корректного построения кривой.
- Постройте диаграмму. Выделите столбцы B (Оценка) и C (Y). На вкладке «Вставка» выберите «Точечная диаграмма с гладкими кривыми».
- Добавьте подписи данных. Это самый важный шаг для идентификации точек.
- Щелкните по диаграмме и нажмите значок «+» справа.
- Выберите «Подписи данных» → «Дополнительные параметры».
- В открывшейся панели нажмите на значок гистограммы и выберите «Значения из ячеек».
- Укажите диапазон с именами сотрудников (столбец A).
Совет: Часто две подписи накладываются друг на друга. Вы можете переместить отдельную подпись, чтобы она отображалась с небольшой выносной линией (как для Гэри и Эда на правой стороне диаграммы). Для этого:
- Щелкните по любой подписи — выделятся все подписи на диаграмме.
- Щелкните второй раз по одной из наложенных подписей. Теперь выбран только этот элемент.
- Перетащите выбранную подпись в нужное место.
Готово! Теперь у вас есть наглядная кривая нормального распределения, показывающая, как оценки ваших сотрудников соотносятся со средним значением по группе. Этот инструмент отлично подходит для анализа эффективности, оценки результатов тестирования или сегментации клиентов.