Как сделать кривую нормального распределения в Excel: пример + шаблон
«Колокольная кривая» — это прозвище, данное форме нормального распределения , которая имеет отчетливую форму «колокола»:
В этом руководстве объясняется, как построить кривую нормального распределения в Excel для заданного среднего значения и стандартного отклонения, и даже предоставляется бесплатный загружаемый шаблон, который можно использовать для создания собственной кривой нормального распределения в Excel.
Пример: кривая нормального распределения в Excel
Используйте следующие шаги, чтобы сделать кривую колокола в Excel.
Шаг 1: Создайте ячейки для среднего значения и стандартного отклонения.
Шаг 2: Создайте ячейки для процентилей от -4 до 4 с шагом 0,1.
Шаг 3: Создайте столбец значений данных, которые будут использоваться на графике.
Шаг 4: Найдите значения для нормального распределения PDF.
Шаг 5: Создайте метки графика по оси X только для целочисленных процентилей.
Шаг 6: Сделайте кривую колокола.
Сначала выделите все значения в столбце pdf :
Затем в группе « Диаграммы » на вкладке « Вставка » щелкните первый параметр графика в категории « Вставить линию или диаграмму с областями»:
Автоматически появится кривая нормального распределения:
Шаг 7: Измените метки оси X.
Щелкните правой кнопкой мыши в любом месте диаграммы и выберите Выбрать данные.Появится новое окно. Нажмите кнопку « Редактировать » в разделе «Метки горизонтальной оси»:
Выберите диапазон ячеек, в которых расположены метки оси X. В нашем случае это диапазон ячеек D5:D85.Затем нажмите ОК .
Метки оси X будут обновляться автоматически:
Вы заметите, что если вы измените среднее значение и стандартное отклонение, кривая нормального распределения обновится автоматически. Например, вот во что превращается кривая нормального распределения, если мы используем среднее значение = 10 и стандартное отклонение = 2:
Вы можете изменить заголовок диаграммы, добавить метки осей и изменить цвет, если хотите сделать диаграмму более эстетичной.
Бесплатный шаблон
Не стесняйтесь загружать этот бесплатный шаблон , который использовался для создания точной колоколообразной кривой в этом уроке.
Функция НОРМРАСП
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 Еще. Меньше
Возвращает нормальную функцию распределения для указанного среднего и стандартного отклонения. Эта функция очень широко применяется в статистике, в том числе при проверке гипотез.
Важно: Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.
Дополнительные сведения о новом варианте этой функции см. в статье Функция НОРМ.РАСП.
Синтаксис
Аргументы функции НОРМРАСП описаны ниже.
- X Обязательный. Значение, для которого строится распределение.
- Среднее Обязательный. Среднее арифметическое распределения.
- Стандартное_откл Обязательный. Стандартное отклонение распределения.
- Интегральная — обязательный аргумент. Логическое значение, определяющее форму функции. Если аргумент «интегральная» имеет значение ИСТИНА, функция НОРМРАСП возвращает интегральную функцию распределения; если этот аргумент имеет значение ЛОЖЬ, возвращается весовая функция распределения.
Замечания
- Если «standard_dev» не является числом, то возвращается #VALUE! значение ошибки #ЗНАЧ!.
- Если standard_dev ≤ 0, то нормДАТ возвращает #NUM! значение ошибки #ЗНАЧ!.
- Если среднее = 0, стандартное_откл = 1 и интегральная = ИСТИНА, то функция НОРМРАСП возвращает стандартное нормальное распределение, т. е. НОРМСТРАСП.
- Уравнение для плотности нормального распределения (аргумент «интегральная» содержит значение ЛОЖЬ) имеет следующий вид:
- Если аргумент «интегральная» имеет значение ИСТИНА, формула описывает интеграл с пределами от минус бесконечности до x.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Значение, для которого нужно вычислить распределение
Среднее арифметическое распределения
Стандартное отклонение распределения
Интегральная функция распределения для приведенных выше условий
Функция плотности распределения для приведенных выше условий
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Диаграмма нормального распределения (Гаусса) в Excel
Требуется построить диаграмму стандартного нормального распределения Гаусса (стандартное нормальное распределение имеет М = 0 и = 1), используя функцию НОРМСТРАСП.
1. В ячейку A3 введем символ х, а в ячейку ВЗ — символ функции плотности вероятности f(x).
2. Вычислим нижнюю М — За границу диапазона значений х, для чего установим курсор в ячейку С2 и введем формулу =0-3*1, а также верхнюю границу — в ячейку Е2 введем формулу =0+3*1.
3. Скопируем формулу из ячейки С2 в ячейку А4, полученное в ячейке А4 значение нижней границы будет началом последовательности арифметической прогрессии.
4. Создадим последовательность значений х в требуемом диапазоне, для чего установим курсор в ячейку А4 и выполним команду меню Правка/Заполнить/Прогрессия.
5. В открывшемся окне диалога Прогрессия установим переключатели арифметическая, по столбцам, в поле Шаг введем значение 0,5, а в поле Предельное значение — число, равное верхней границе диапазона.
Функция НОРМРАСПР в EXCEL
6. Щелкнем на кнопке ОК. В диапазоне А4:А16 будет сформирована последовательность значений х.
7. Установим курсор в ячейку В4 и выполним команду меню Вставка/Функция. В открывшемся окне Мастер функций выберем категорию Статистические, а в списке функций — НОРМРАСП.
8. Установим значения параметров функции НОРМРАСП: для параметра х установим ссылку на ячейку А4, для параметра Среднее — введем число 0, для параметра Стандартное_откл — число 1, для параметра Интегральное — число 0 (весовая).
Диаграмма нормального интегрального распределения в EXCEL
9. Используя маркер буксировки, скопируем полученную формулу в диапазон ячеек В5:В16.
10. Выделим диапазон полученных табличных значений функции f(х) (ВЗ:В16) и выполним команду меню Вставка/Диаграмма. В окне Мастер диаграмм во вкладке Стандартные выберем График, а в поле Вид — вид графика, щелкнем на кнопке Далее.
11. В окне Мастер диаграмм (шаг 2) выберем закладку Ряд. В поле Подписи оси х укажем ссылку на диапазон, содержащий значения х (А4:А16). Щелкнем на кнопке Далее.
В окне Мастер диаграмм (шаг 3) введем подписи: Название диаграммы, Ось х, Ось у. Щелкнем на кнопке Готово. На рабочий лист будет выведена диаграмма плотности вероятности .
Источник: help-informatika.ru
Знаете ли Вы, в чем фокус эксперимента Майкельсона?
Эксперимент А. Майкельсона, Майкельсона — Морли — действительно является цирковым фокусом, загипнотизировавшим физиков на 120 лет.
Дело в том, что в его постановке и выводах произведена подмена, аналогичная подмене в школьной шуточной задачке на сообразительность, в которой спрашивается:
— Cколько яблок на березе, если на одной ветке их 5, на другой ветке — 10 и так далее
При этом внимание учеников намеренно отвлекается от того основополагающего факта, что на березе яблоки не растут, в принципе.
В эксперименте Майкельсона ставится вопрос о движении эфира относительно покоящегося в лабораторной системе интерферометра. Однако, если мы ищем эфир, как базовую материю, из которой состоит всё вещество интерферометра, лаборатории, да и Земли в целом, то, естественно, эфир тоже будет неподвижен, так как земное вещество есть всего навсего определенным образом структурированный эфир, и никак не может двигаться относительно самого себя.
Удивительно, что этот цирковой трюк овладел на 120 лет умами физиков на полном серьезе, хотя его прототипы есть в сказках-небылицах всех народов всех времен, включая барона Мюнхаузена, вытащившего себя за волосы из болота, и призванных показать детям возможные жульничества и тем защитить их во взрослой жизни. Подробнее читайте в FAQ по эфирной физике.
Bootstrapping 2 — анализ нормального распределения в Excel
Ранее мы рассмотрели, как рассчитать прогноз редких продаж в Excel по методу Bootstrap подробности вы можете прочитать в статье по этой ссылке. П рогноз мы рассчитали с учетом того, что распределение нормально.
В данной статье мы выделим фактическое распределение и за счет этого повысим точность прогноза.
Сегодня вы узнаете, как в Excel быстро проанализировать распределение и уточнить прогноз.
Итак, в предыдущей статье мы:
- рассчитали прогноз, вытащив случайным образом из ряда с редкими продажами по месяцам 12 чисел и
- сделали это 10 000 раз,
- рассчитали среднее по каждому из 10 000 рядов и
- среднее по средним, получили прогноз на месяц,
- Дальше среднее по средним умножили на 3 и получили прогноз на 3 месяца.
Теперь, в продолжение предыдущих шагов, для увеличения точности прогноза проанализируем фактическое распределение и скорректируем прогноз.
Для это:
- Выделим среднее значение по 10 000 рядам выборки;
- Построим сводную таблицу по выделенным средним;
- Сгруппируем ряд средних;
- Выведем в сводную таблицу по группам следующие показатели: количество, среднее, сумму, максимум, минимум.
- Рассчитаем прогноз на 6 месяцев.
- Выделим среднее значение по 10 000 рядам выборки. Для этого устанавливаете курсор в заголовок «Среднее», и нажимаете Ctrl+Shift+стрелочку вниз:
2. Добавляем сводную таблицу
Меню «Вставка» — «Сводная таблица»:
Далее ставим галочку «на существующий лист» и нажимаем «ОК»
3. Сгруппируем ряд средних.
Для этого перетянем средние в область «Строк» сводной таблицы:
Нажимаем правой кнопкой мыши на сводной таблице в столбце со средними и выберем пункт «Группировать»
В появившемся окне мы можем задать параметры группировки – с какого по какое значение группировать с каким шагом. Берем значения по умолчанию и нажимаем «ОК»:
Получаем такую табличку с диапазонами средних:
4. Выводим в сводную таблицу в сгруппированные диапазоны следующие показатели: количество, среднее, сумму, максимум, минимум.
Перетаскиваем среднее 5 раз в область значений сводной таблицы:
Задаем показатели (количество, среднее, сумму, максимум, минимум) в «параметрах полей значений»
Выбираем нужный показатель для расчета по каждому полю — количество, среднее, сумму, максимум, минимум.
Получаем следующую таблицу:
Видим, что 5 375 рядов в диапазоне от 1-2:
Сделаем еще раз группировку, только шаг поставим 2
Видим, что 7 379 рядов из 10 000 в диапазоне от 0-2 – 73% наблюдений. И так мы от нормального распределения перешли к фактическому и опираясь на него сделаем прогноз.
Теперь мы получили 96% наблюдений в диапазоне от 0 до 3.
5. Рассчитаем прогноз на 6 месяцев.
Опираясь на среднее этой группы, уточним прогноз, который рассчитали в статье «Bootstrap метод — прогноз нерегулярных продаж».
Среднее значение ряда по группе с 96% наблюдений у нас получился 1,49, умножим на 6 месяцев, получаем уточненный прогноз на 6 месяцев 8,96, т.е. можем планировать, что нам понадобится 9 шт. данного товара:
Столбцы «Максимум» и «Минимум» — границы прогноза.
Точных вам прогнозов!
Программа Forecast4AC PRO (начиная с версии 4.7) умеет автоматически использовать модель Bootstrap при расчете прогноза на больших массивах данных! Если один из рядов с нерегулярными продажами, программа это понимает и строит прогноз, используя метод Bootstrap!
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite — автоматический расчет прогноза в Excel .
- 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.