Что такое нормобр в эксель
Как видно из названия формулы, мы можем думать, что эта функция вычисляет обратное нормальному распределению, и для этой функции среднее значение набора данных всегда остается равным нулю, а стандартное отклонение всегда равно единице, это также встроенная функция в Excel. который используется в статистике, такой как регрессионный анализ.
Функция НОРМ.ОБР () в Excel
Это встроенная встроенная функция в Excel, относящаяся к категории Статистические функции в Excel. Функция Excel NORM.S.INV — это последняя обновленная версия с повышенной точностью по сравнению со старой версией этой функции Excel.
- Нормальное распределение — это наиболее широко используемое распределение в статистике. Ее также называют «кривой Белла» или «кривой Гаусса».
- Нормальное распределение можно полностью описать на основе его средних значений и значений стандартного отклонения (SD).
- Нормальное распределение называется стандартным нормальным распределением, когда его среднее значение равно «0» или ноль, а значение стандартного отклонения равно 1.
Нормальное распределение может быть стандартизировано с помощью приведенной ниже формулы
z = (x — среднее) / sd
Значение Z на оси x — стандартное нормальное отклонение. Здесь линия с красной стрелкой на кривой указывает, где стандартное отклонение среднего значения находится в пределах 1, тогда как линия с зеленой стрелкой на кривой указывает, где стандартное отклонение среднего значения 0 находится в пределах 2.
Определение функции Excel NORM.S.INV
НОРМ.С.ОБР Функция Excel используется для определения или вычисления обратного нормального кумулятивного распределения для заданного значения вероятности.
Он возвращает значение, обратное стандартному нормальному кумулятивному распределению. Среднее значение распределения равно нулю, а стандартное отклонение равно единице.
Учитывая вероятность того, что переменная находится на определенном расстоянии от среднего, вычисляется значение z (стандартное нормальное отклонение), где оно соответствует площади под кривой.
Обычно площадь должна быть от 0 до 1.
Здесь значение z (стандартное нормальное отклонение) соответствует односторонней вероятности P.
Где значение P должно быть от 0 до 1 (0
Формула NORM.S.INV в Excel
Синтаксис или формула функции НОРМ.С.ОБР в Microsoft Excel:
Его синтаксис или формула имеет следующий аргумент:
Вероятность: (Обязательный или обязательный параметр) Это вероятность, соответствующая нормальному распределению
Это инверсия функции НОРМ.С.РАСП.
Как использовать функцию NORM.S.INV в Excel?
Давайте посмотрим, как функция Excel NORM.S.INV работает в Excel.
Пример №1 — Значение вероятности (P) меньше 0,5
В приведенном ниже примере у меня есть набор данных в ячейке «C9», то есть 0,28, что является значением вероятности.
Здесь мне нужно узнать приблизительное значение, обратное стандартному нормальному кумулятивному распределению, используя NORM.S.INV excel FUNCTION
Применим эту функцию в ячейке «C13». Выберите ячейку «C13».
появится диалоговое окно,
Введите ключевое слово «NORM» в поле поиска функции; появляются различные стандартные уравнения нормального кумулятивного распределения. В этом выберите функцию Excel NORM.S.INV
Вероятность: Это вероятность, соответствующая нормальному распределению; здесь 0,28
Нажмите ОК после ввода аргумента вероятности = НОРМ.ОБР (0,28)
Это возвращает приблизительное значение, обратное стандартному нормальному кумулятивному распределению или стандартизированному нормальному отклонению, то есть -0,582841507
Пример № 2 — Значение вероятности (P) больше 0,5
В приведенном ниже примере у меня есть набор данных в ячейке «B22», т.е. 0,88, что является значением вероятности.
Здесь мне нужно узнать приблизительное значение, обратное стандартному нормальному кумулятивному распределению, используя ФУНКЦИЮ НОРМСТОБР ()
Применим эту функцию в ячейке «B26». Выберите ячейку «B26».
Появится диалоговое окно.
Введите ключевое слово «NORM» в поле поиска функции; появляются различные стандартные уравнения нормального кумулятивного распределения. В этом случае выберите функцию NORM.S.INV ().
Дважды щелкните функцию, появится диалоговое окно, в котором необходимо заполнить или ввести аргументы, т. Е. = NORM.S.INV (вероятность)
Вероятность: Это вероятность, соответствующая нормальному распределению; здесь 0,88
Нажмите ОК после ввода аргумента вероятности, т. Е. = NORM.S.INV (0,88).
Он возвращает приблизительное значение, обратное стандартному нормальному кумулятивному распределению или стандартизованному нормальному отклонению, т. Е. 1,174986792.
Пример № 3 — Значение вероятности (P) 0,51
В приведенном ниже примере у меня есть набор данных в ячейке «B32», то есть 0,51, что является значением вероятности.
Здесь мне нужно узнать приблизительное значение, обратное стандартному нормальному кумулятивному распределению, используя ФУНКЦИЮ NORM.S.INV ()
Применим эту функцию в ячейке «B37».
появится диалоговое окно.
Введите ключевое слово «NORM» в поле поиска функции; появляются различные стандартные уравнения нормального кумулятивного распределения.
Вероятность: Это вероятность, соответствующая нормальному распределению; здесь 0,51
Нажмите ОК после ввода аргумента вероятности = НОРМ.ОБР (0,51).
Он возвращает приблизительное значение, обратное стандартному нормальному кумулятивному распределению или стандартизованному нормальному отклонению, то есть 0,025068908.
Что нужно помнить о функции НОРМСТОБР () в Excel
Точность или точность значений этой функции зависит от точности НОРМ.С.ОБР и НОРМ.С.РАСП. значения. Он использует метод итеративного поиска.
Целью данной статьи является описание функция НОРМОБР в Microsoft Office Excel 2003 и последующих версиях Excel, чтобы показать, как используется функция и сравнить результаты функции в Excel 2003 и более поздних версиях Excel с результатами Функция НОРМОБР, если он используется в более ранних версиях Excel.
Дополнительные сведения
Функция НОРМОБР (p, мю, сигма) возвращает значение x, с вероятностью p, обычный случайная величина с виду среднее и стандартное отклонение сигма принимает значение меньше или равно x.
Синтаксис
где p, "среднее" и "стандартное_откл" являются числовыми значениями. Так как p соответствует вероятности (см. Примечание 1), он должен быть больше 0 и меньше 1. Поскольку "стандартное_откл" представляет собой стандартное отклонение, то оно должно быть больше 0.
Примечание 1 Точнее говоря «поскольку p соответствует интегральную для непрерывного случайная величина, которая может принимать любое значение в диапазоне от минус бесконечности и плюс бесконечность. » Нет никаких конкретных отрицательный z для NORMSDIST(z) = 0 и z не определенные положительные для NORMSDIST(z) = 1 (хотя существуют значения z сколь угодно близко к 0 и 1 соответственно), поэтому мы должны настаивать, для NORMINV(p), 0 < p < 1 вместо 0 < = p < = 1.
Пример использования
Функция НОРМРАСП и НОРМОБР, связанных функций. Если функция НОРМРАСП (x, мю, сигма) возвращает p, НОРМОБР (p, мю, сигма) возвращает x. Аналогичным образом функция НОРМСТОБР и НОРМСТРАСП, связанных функций; Если NORMSDIST(z) возвращает p, то NORMSINV(p) возвращает по существу z. Excel преобразует НОРМРАСП (x, мю, сигма) mu + sigma*NORMSDIST((x-mu)/sigma). Аналогичным образом функция НОРМОБР (p, мю, сигма) преобразуется в мю + sigma*NORMSINV(p).
Для демонстрации функции НОРМОБР, создайте пустой лист Excel и скопируйте приведенную ниже таблицу. Выделите ячейку A1 листа Excel и вставьте записи таким образом, таблица заполняет A1:D14 ячеек на листе.
Функция НОРМОБР (p, мю, сигма)
=NORMINV(A4, $B$1, $B$2)
=NORMINV(A5, $B$1, $B$2)
=NORMINV(A6, $B$1, $B$2)
=NORMINV(A7, $B$1, $B$2)
=NORMINV(A8, $B$1, $B$2)
=NORMINV(A9, $B$1, $B$2)
=NORMINV(A10, $B$1, $B$2)
=NORMINV(A11, $B$1, $B$2)
=NORMINV(A12, $B$1, $B$2)
=NORMINV(A13, $B$1, $B$2)
=NORMINV(A14, $B$1, $B$2)
После вставки таблицы в новый лист Excel, нажмите кнопку Параметры вставки и нажмите кнопку Использовать форматирование конечного фрагмента. Со вставленным диапазоном, который по-прежнему выбран, используйте одну из следующих процедур в зависимости от версии Excel, который выполняется:
В Microsoft Office Excel 2007 перейдите на вкладку Главная , в группе ячейки нажмите кнопку Формат и выберите команду Автоподбор ширины.
В Excel 2003 выберите пункт
В меню Формат , а затем щелкните столбец
Автоподбор.
Можно отформатировать столбцы B, C и D для согласованного читаемости (например 5 десятичных знаков).
A4:B14 ячейки отображает значения НОРМОБР (p, мю, сигма) для конкретных значений p. "среднее" и "стандартное_откл" инициализируются 100 и 10 в ячейки B1 и B2. Сравнение значений в столбцах B и D показывает, что функция НОРМОБР (p, мю, сигма) равно мю + sigma*NORMSINV(p). Как упоминалось ранее, если вызывается функция НОРМОБР (p, мю, сигма), Microsoft Excel вычисляет среднее + sigma*NORMSINV(p). Любые неточности в НОРМОБР возникает из-за неточности в НОРМСТОБР.
В следующих трех разделах рассматриваются неточности в НОРМСТОБР и берутся непосредственно из статьи на НОРМСТОБР. Эта статья также имеет другой пример Excel и учебника обсуждение связи между функцией и ее обратную.
Вы можете поэкспериментировать с изменением значения "среднее" и "стандартное_откл" в ячейках B1 и B2. Измените mu до 200 и обратите внимание, что это просто добавляет 100 все НОРМОБР результаты. Измените mu 100 и измените сигма на 20. Обратите внимание, что поскольку сигма удвоилась, значения, возвращаемые НОРМОБР два раза далеко не mu (например, 100), как и раньше. Конечно можно изучить влияние дополнительные изменения в "среднее" и "стандартное_откл" на свои собственные. Увеличение mu увеличивает все значения НОРМСТОБР на ту же сумму; увеличивает сигма, несколько больше 1, оставив без изменений, mu увеличивает расстояние от всех значений НОРМСТОБР из mu же несколько.
Результаты в более ранних версиях Excel
Точности НОРМСТОБР зависит от двух факторов. Так как функция НОРМСТОБР включает систематическую поиска над значениями НОРМСТРАСП, важно точности НОРМСТРАСП.
Кроме того поиск должен быть достаточно уточнение, что он «домов» на соответствующий ответ. Можно использовать таблицу нормального распределения вероятности учебника по аналогии, записи в таблице должно быть точным и поэтому многие, можно найти соответствующую строку таблицы, которая дает вероятность правильного определенное число десятичных знаков. Конечно с программным обеспечением компьютера один не построить и хранения такой monstrous таблицы; Вместо этого отдельные операции вычисляются по запросу как поиск через выручка «таблица». Но таблица должна быть точным в первую очередь, а поиск продолжить достаточно далеко, что он не останавливается преждевременно ответ, которого соответствующая вероятность (строка таблицы, если вам нравится) находится слишком далеко от пользователя p в вызове NORMSINV(p). Таким образом усовершенствования в НОРМСТОБР состоят из следующие усовершенствования:
Улучшения в точности НОРМСТРАСП
Усовершенствования в процессе поиска для повышения чистоты звука
Функция НОРМСТРАСП была улучшена, но только для Excel 2003 и последующих версиях Excel, но не для более ранних версиях Excel. Улучшенный усовершенствования в процессе поиска впервые появились в Microsoft Excel 2002, но не до этого. Статьи по Knusel (см. Примечание 2) Указывает числовое недостаток в НОРМСТОБР в Microsoft Excel 97. Указанные недостатки сохраняются, как описано, Knusel, ожидая улучшения в процессе поиска в программе Microsoft Excel 2002 результаты лучше. Однако результаты еще не были полностью согласен с его Knusel.
Примечание 2 Knusel, L. на точность статистического распределения в Microsoft Excel 97, вычислительные статистики и анализа данных, 26, 375-377, 1998.
Результаты в Excel 2003 и последующих версиях Excel
Процедура в Excel 2003 и последующих версиях Excel НОРМСТОБР использует преимущества улучшений в НОРМСТРАСП в Excel 2003 и последующих версиях Excel.
Для получения дополнительных сведений щелкните следующий номер статьи базы знаний Майкрософт:
Статистические функции excel 827369 : функция НОРМСТРАСП
Результаты следует всегда совпадает с Knusel в число десятичных разрядов, отображаемых.
Выводы
Для очень больших или очень малые значения p в NORMSINV(p) обычно возникают неточности в более ранних версиях Excel. Более точные значения в Excel 2003 и последующих версиях Excel.
Статья о функции НОРМСТРАСП указывает, что большинство пользователей не должны повлиять на неточности в НОРМСТРАСП в более ранних версиях Excel. Из этого следует, что пользователи Excel 2002 не осуществляемых за НОРМСТОБР поскольку детали процесса поиска были добавлены в программе Microsoft Excel 2002.
Для пользователей Excel 2002 и более ранних версиях Excel нет больше беспокойство неточность НОРМСТОБР, так как функция НОРМСТРАСП и процесс поиска необходимые улучшения в этих версиях.
Функция НОРМ.ОБР в Excel предназначена для расчета значения нормальной распределенной переменной при известных значениях вероятности, средней величины и стандартного отклонения, и возвращает соответствующее число. Данная функция является обратной для НОРМ.РАСП.
Как рассчитать обратное стандартное нормальное распределение в Excel
Алгоритм работы функции НОРМ.ОБР основан на Центральной предельной теоремы теории вероятности, согласно которой распределение случайной величины x может быть описано нормальным законом, если ее плотность распределения может быть вычислена с использованием следующей функции:
Нормальное распределение имеет два характеризующих его параметра: математическое ожидание (), определяющее положение центра плотности вероятности, и стандартное отклонение (), характеризующее разброс относительно центральной части.
Пример 1. Длина грифа гитары, производимого на заводе, составляет в среднем 650 мм, стандартное отклонение в партии составляет 12 мм. Определить максимальную длину грифа по техническим условиям, чтобы 95% всех грифов соответствовали ей.
Вид таблицы данных:
Для решения введем в ячейку B5 функцию:
В результате вычислений функции получаем наиболее оптимальные параметры.
Генератор случайных чисел с нормальным распределением в Excel
Пример 2. В таблицу Excel введены некоторые данные из эксперимента. Необходимо сгенерировать на основе имеющихся данных массив случайных числовых значений с нормальным распределением.
Вид таблицы данных:
Для решения выделим 11 ячеек соседнего столбца B и запишем следующую формулу массива CTRL+SHIFT+Enter:
В качестве первого аргумента используется функция СЛЧИС, возвращающая случайные числа из диапазона от 0 до 1 (оба включительно), что соответствует диапазону допустимых значений вероятности. Для определения второго аргумента используется функция СРЗНАЧ, возвращающая среднее арифметическое для исследуемого распределения. Третий аргумент – результат выполнения функции СТАНДОТКЛОН.Г.
Функция НОРМСТРАСП в Excel используется для нахождения значения статистической функции стандартного нормального распределения. Рассмотрим примеры использования данной функции и самостоятельно составим таблицу нормального закона.
Алгоритм функции нормального стандартного распределения чисел в Excel
В новых версиях Microsoft Office была введена более универсальная функция =НОРМ.СТ.РАСП(), содержащая дополнительный аргумент, который принимает два возможных значения:
- ИСТИНА – для получения интегральной функции распределения;
- ЛОЖЬ – для получения весовой функции распределения.
Стандартное нормальное распределение (СНР) – специальная форма распределения, используемая в качестве эталона для оценки данных любого вида. Данный тип распределения по причине неудобства использования формулы общего нормального распределения на практике.
Главные особенности функции:
- Площадь участка, ограниченного кривой и осью абсцисс принята за 1.
- Стандартное отклонение считается равным 1.
- Среднее арифметическое значение принято равным 0.
- В функцию f(x) общего теоретического нормального распределения введена переменная z (стандартная нормальная).
Переменная z рассчитывается по формуле:
- X – значение некоторой случайной величины;
- µ - среднее значение;
- ó - значение стандартного отклонения.
Смысл переменной z – число стандартных отклонений, на которые отличается значение случайной величины от среднего значения.
Функция НОРМСТРАСП возвращает результат, рассчитанный на основе следующей формулы:
Именно так и выглядит алгоритм вычисления функции НОРМСТРАСП в Excel
Таблица стандартного нормального распределения в Excel
Пример 1. Найти стандартные нормальные распределения для числовых данных, указанных в таблице.
Вид таблицы данных:
Для расчетов используем следующую формулу:
- A2:A11 – диапазон ячеек, содержащих значения переменной z.
С принципом действия функции мы ознакомились. Теперь ничто нам не мешает составить свою таблицу стандартного распределения в Excel. Для этого построим шаблон таблицы нормального закона и заполним ее ячейки формулой со смешанными ссылками:
Таким образом мы самостоятельно составили таблицу стандартного нормального распределения в Excel.
Расчет вероятности стандартным нормальным распределением в Excel
Пример 2. На заводе изготавливают лампочки. Средний период бесперебойной работы каждой лампы составляет 1000 ч. Стандартное отклонение от срока службы составляет 50 ч. Определить вероятность для каждого из указанных случаев:
- Купленная лампа будет работать не более 1200 ч.
- Срок службы составит менее 800 ч.
- Количество ламп в партии из 500 шт., которые проработают от 900 до 1100 часов.
Вид таблицы данных:
Для расчета вероятности срока службы менее 1200 ч используем следующую формулу:
(1200-B2)/B3 – выражение для расчета переменной z.
В результате вычислений получим следующее значение вероятности:
Аналогично рассчитаем вероятность того, что срок службы составит менее 800 часов:
Результат вычислений (получена слишком маленькая вероятность, поэтому для наглядности был установлен формат Проценты):
Нормальное распределение является симметричным относительно оси ординат, поэтому функция НОРМСТРАСП может вычислить значение даже для отрицательного z.
Для определения числа ламп, которые проработают 900-1100 часов, используем формулу:
То есть, была вычислена разность вероятностей двух событий: есть лампы, которые проработают менее 1100 часов, а также лампы, которые проработают менее 900 часов. Результат произведения полученной вероятности и общего числа ламп в партии является искомым значением.
Статистические вычисления значительно ускоряются с использованием программного обеспечения. Один из способов сделать эти вычисления — использовать Microsoft Excel. Из всего разнообразия статистических данных и вероятностей, которые могут быть получены с помощью этой программы для работы с электронными таблицами, мы рассмотрим функцию НОРМ.ОБР.
Причина использования
Предположим, что у нас есть нормально распределенная случайная величина, обозначенная x . Можно задать один вопрос: «При каком значении x у нас есть нижние 10% распределения?» Шаги, которые мы должны выполнить для этого типа проблемы:
- Используя стандартную таблицу нормального распределения, найдите z , который соответствует 10% наименьшего распределения.
- Воспользуйтесь формулой z -score и решите ее для x . Это дает нам x = μ + z σ, где μ — среднее значение распределения, а σ — стандартное отклонение.
- Вставьте все наши значения в приведенную выше формулу. Это дает нам ответ.
В Excel все это делает за нас функция НОРМ.ОБР.
Аргументы для NORM.INV
Чтобы использовать функцию, просто введите в пустую ячейку следующее:
Аргументы этой функции по порядку:
- Вероятность — это совокупная доля распределения, соответствующая площади в левой части распределения.
- Среднее — это было обозначено выше как μ и является центром нашего распределения.
- Стандартное отклонение — оно было обозначено выше как σ и учитывает разброс нашего распределения.
Просто введите каждый из этих аргументов через запятую, разделяя их. После ввода стандартного отклонения закройте круглые скобки с помощью) и нажмите клавишу ввода. ключ. Результатом в ячейке является значение x , которое соответствует нашей пропорции.
Примеры расчетов
Мы увидим, как использовать эту функцию, на нескольких примерах вычислений. Для всего этого мы будем предполагать, что IQ обычно распределяется со средним значением 100 и стандартным отклонением 15. Мы ответим на следующие вопросы:
- Каков диапазон значений самых низких 10% всех оценок IQ?
- Каков диапазон значений самых высоких 1% всех оценок IQ?
- Каков диапазон значений средних 50% всех оценок IQ?
Для вопроса 1 мы вводим = NORM .INV (.1,100,15). Выходной сигнал Excel составляет примерно 80,78. Это означает, что оценки ниже или равные 80,78 составляют самые низкие 10% всех оценок IQ.
Для вопроса 2 нам нужно немного подумать, прежде чем использовать функция. Функция НОРМ.ОБР предназначена для работы с левой частью нашего распределения. Когда мы спрашиваем о верхней пропорции, мы смотрим на правую часть.
Верхний 1% эквивалентен вопросу о нижних 99%. Вводим = НОРМ.ОБР (0,99,100,15). Выходной сигнал Excel составляет приблизительно 134,90. Это означает, что баллы, превышающие или равные 134,9, составляют 1% лучших оценок IQ.
Для вопроса 3 мы должны быть еще более умными. Мы понимаем, что средние 50% находятся, когда мы исключаем нижние 25% и верхние 25%.
- Для нижних 25% вводим = НОРМ.ОБР (0,25,100,15) и получаем 89,88.
- Для верхних 25% вводим = НОРМ.ОБР (0,75, 100, 15) и получаем 110,12
NORM.S.INV
Если мы работаем только со стандартными нормальными распределениями, то NORM.S.INV функция работает немного быстрее. Для этой функции среднее значение всегда равно 0, а стандартное отклонение всегда равно 1. Единственный аргумент — это вероятность.
Связь между двумя функциями:
Для любых других нормальных распределений мы должны использовать функцию НОРМ.ОБР.
Читайте также: