Как сделать кривую нормального распределения в 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 Excel.
Описание
Рассчитывает вероятность, с которой элемент стандартной нормальной совокупности находится в интервале между средним и стандартным отклонением z от среднего.
Синтаксис
Аргументы функции ГАУСС указаны ниже.
Z Обязательный. Возвращает число.
Замечания
Если z не является допустимым числом, гаусс возвращает #NUM! (значение ошибки).
Если z не является допустимым типом данных, гаусс возвращает #VALUE! (значение ошибки).
Поскольку НОРМ.СТ.РАСП(0,Истина) всегда возвращает 0,5, ГАУСС (z) всегда будет на 0,5 меньше, чем НОРМ.СТ.РАСП(z,Истина).
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.
Вероятность, с которой элемент стандартной нормальной совокупности населения находится в интервале между средним и двумя стандартными отклонениями от среднего (результат — 0,47725).
Примеры решений распределения с помощью функции ГАУСС в Excel
Функция ГАУСС, подлежащая применению в версиях Excel начиная от 2013 года или новее. Она позволяет вычислить такую вероятность, с которой элемент стандартной нормальной совокупности будет находиться в интервале между средними и стандартными отклонениями от среднего.
Примеры использования функции ГАУСС в Excel
Синтаксис рассматриваемой функции не представляет из себя ничего сложного, ведь функции ГАУСС присущ всего один обязательный аргумент – Z – возвращающий число.
Важно отметить, что существует определенная связь между функцией ГАУСС и такой статистической функцией, как стандартное нормальное распределение, иначе говоря – НОРМ.СТ.РАСП.
Итак, всегда функция НОРМ.СТ.РАСП (0; Истина) делает возврат 0,5, тогда как ГАУСС (z) имеет в результате значение меньше на 0,5, чем результат функции НОРМ.СТ.РАСП. На рисунке, расположенном ниже, приведен пример использования данных статистических функций для возвращения числа 1,5.
Для наглядности продемонстрируем зависимость между значениями функций графическим способом. Для этого – сформируем таблицу с выборкой чисел, например на интервале от -5 до 5 с шагом 0,5, а затем по имеющимся данным построим график:
На графике четко прослеживается пропорциональная корреляция результатов вычислений функций ГАУСС и НОРМ.СТ.РАСП.
Решение системы вероятности методом ГАУССА в Excel
Задача представляет собой вычисление вероятности возможных значений при бросании двух костей.
Пример с игрой в кости является наиболее наглядным, так как мы имеем ограниченный набор данных, которые соответствуют вероятностям. Так, вероятность имеет значение от нуля до единицы, к которому стремится наблюдаемая частота при бесконечно большой выборке или повторении эксперимента.
Существует 36 возможных комбинаций. При этом, вероятность того, что при бросании двух костей выпадет 2 очка равна 1/36, а 7 очков – 1/6. Отобразим перечень возможных значений бросания двух игральных костей в таблице, приведя при этом все вероятности к общему знаменателю.
Однако, такой ряд данных не дает возможности для выявления полного распределения, поэтому следует отобразить данные об отдельных вероятностях в рассчитанную по функции распределения. Так необходимо, все вероятности просуммировать последовательно (1+2+3+4+5+6+5+4+3+2+1).
Теперь определяем коэффициент вероятности разделив по отдельности последовательную сумму вероятностей на максимально возможное количество комбинаций 36.
В первом случае нами были рассмотрены отдельные вероятности, во втором – сумма вероятностей от первого возможного значения до заданного.
Необходимо преобразовать диапазон ячеек D2:D13 в числовой формат данных, иначе при обращении на них функции ГАУСС будет иметь место ошибка.
В созданный рядом с первоначальной таблицей столбец E введем формулу, которая в качестве аргумента делает обращение к ячейке D2.
Далее, протянем формулу вниз по столбцу, и получим ряд вероятностей с использованием функции ГАУСС.
Для более наглядной визуализации, построим график вероятности:
Решение вероятности методом распределения кривой Гаусса в Excel
Теперь в качестве примера нормального распределения с помощью функции ГАУСС решим задачу о вероятностном соотношении результатов стрельбы по мишени.
Для этого построим базовую таблицу, которая отражает результаты стрельбы по мишени в девяти подходах.
Затем, выберем только уникальные результаты, для этого используем хитрую формулу:
Делаем сортировку формулой для результатов по возрастанию и выводим в отдельную табличку:
После чего определим частоту встречающихся только для уникальных результатов:
Далее применим функцию ГАУСС к значениям ячеек с частотой встречаемости. Отразим результаты вычислений на графике:
На графике красной линией определено нормальное распределение кривой Гаусса.
Функция Гаусса (колоколообразная кривая) в EXCEL
Функция Гаусса широко применяется в статистике для описания нормального распределения, для решения некоторых уравнений физики (уравнения диффузии и теплопроводности) и в ряде других прикладных задач (фильтр Гаусса).
Форма графика функция Гаусса зависит от величины 3-х коэффициентов: a (высота пика), b (положение центра), c (отвечает за ширину кривой).
В файле примера построен график функции (использована диаграмма Точечная с гладкими кривыми ). Коэффициенты находятся в отдельных ячейках, что позволяет быстро построить график нужной функции.
Если коэффициент а =1/КОРЕНЬ(2*ПИ()) , то функция Гаусса является функцией распределения плотности верояности нормально распределенной случайной выличины с математическим ожиданием = b и со стандарным отклонением = с .
В файле примера также построен график для функции y(x)=exp(-x^2) с различными коэффициентами.
СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .