Макарова Н.В. Статистика в Excel-1

Для генерации последовательности случайных чисел, распре­ деленных по закону Пуассона, в области Параметры задается ин­ тенсивность появления событий (поле Лямбда),

Графики пуассоновского распределения строятся на основе интегрального и дифференциального массивов значений, форми­ руемых с помощью функции ПУАССОН {см, подразд. 6.4.3). Так как распределение Пуассона является дискретным, то точечные фафики, построенные с помощью мастера диафамм Microsoft Excel, необходимо дорабатывать вручную с использованием пане­ ли Рисование (нельзя использовать операцию аналитического вы­ равнивания трендом). На рис. 6.5 показан график дифференци­ альной функции распределения Пуассона при X - 0,8.

Подрежим работы «Дискретное распределение» служит для генерации последовательности случайных чисел, распределенных по закону, задаваемому пользователем. В окне данного подрежима в области Параметры задаются значения случайной величины и соответствующие этим значениям вероятности (поле Входной ин­ тервал значений и вероятностей).

0.5

4

0,4

Рис. 6.5

Например, требуется смоделировать 100 подбрасываний двух игральных костей. Для этого, во-первых, на рабочем листе сфор­ мируем входную таблицу значений и вероятностей (табл. 6.1); вовторых, зададим соответствующие параметры в диалоговом окне подрежима (рис. 6.6).

120

;1'Случай«ое рассеивание:

'j ^ 8^евйд«ойинтереалг

Ш^ Новая рабочая книгз

В результате проведенного моделирования получаем 200 зна­ чений случайной величины (100 значений для первой ифальной кости в диапазоне Р7:Р107 и 100 значений для второй игральной кости в диапазоне Q7:Q107).

121

с помощью функции СЧЕТЕСЛИ посчитаем число выпавших значений для каждой Ифальной кости (табл. 6.2):

Подрежим работы «Модельное распределение» служит для ге­ нерации детерминированной последовательности чисел в заданном интервале [а, а^] (рис. 6.7). Числа такой последовательности обра­ зуют арифметическую профессию, каждый член которой опреде­ ляется по формуле

Л, = Л1 + ^ ( / - 1 ) ,

где hx - первый член прогрессии (задается в поле От,.,); d ~ разность прогрессии (задается в поле Шаг);

/— номер взятого члена.

Вполе До... задается число, которое не может превышать по­ следний член генерируемой профессии.

Вподрежиме «Модельное распределение» помимо генерации чисел, образующих арифметическую профессию, существует воз­ можность создания:

•нескольких одинаковых последовательностей, являющихся арифметическими профессиями, которые располагаются в смеж­ ных столбцах (поле Повторяя последовательность);

•последовательности, в которой каждое число, являющееся членом арифметической профессии, повторяется несколько раз (поле Повторяя каждое число).

Ш

Генерация сл*{Чвйных мисеп

\

; ^ Новая р>абочая кнкга

Рис. 6.7

6.3.

Статистические функции непрерывных распределений

6.3.1.

Функции нормального распределений

Функция НОРМРАСП

См. также НОРМОБР, НОРМСТРАСП, НОРМСТОБР, НОР­ МАЛИЗАЦИЯ.

Синтаксис:

НОРМРАСП (х; среднее; стандартное откл; интегральная)

Результат:

Рассчитывает нормальное распределение.

Аргументы:

• х: значение, для которого вычисляется нормальное распре­ деление;

123

• среднее: средняя арифметическая распределения;

• интегральная: логическое значение, определяющее форму функции. Если аргумент интегральная = 1, то функция НОРМ­ РАСП рассчитывает интегральную функцию распределения; если аргумент интегральная = О - дифференциальную функцию рас­ пределения.

Замечания:

• если аргумент среднее или аргумент стандартное откл не является числом, то функция НОРМРАСП помещает в ячейку значение ошибки #3FL\4!;

• если аргумент стандартное откл < О, то функция НОР­ МРАСП помешает в ячейку значение ошибки #ЧИСЛО!;

• если аргумент среднее = О и аргумент стандартное откл = =1, то функция НОРМРАСП рассчитывает стандартное нормаль­ ное распределение (см, описание функции НОРМСТРАСП).

МатематикО'Статистинеская интерпретация:

Нормальный закон распределения (часто называемый зако­ ном Гаусса*) имеет в статистике широкий круг приложений и за­ нимает среди других законов распределения особое положение. Ставная особенность, выделяющая нормальный закон среди дру­ гих, состоит в том, что он является предельным законом, к кото­ рому приближаются другие законы распределения при весьма ча­ сто встречающихся условиях.

Доказано, что сумма достаточно большого числа независимых (или слабо зависимых) случайных величин, подчиненных какимлибо законам распределения, приближенно подчиняется нор­ мальному закону, и это выполняется тем точнее, чем большее ко­ личество случайных величин суммируется. Основное ограниче­ ние, налагаемое на суммируемые величины, состоит в том, что

* (Gauss Carl Friedrich) Гаусс Карл Фридрих (1777-1855) - немецкий ма­ тематик, внесший фундаментальный вклад также в астрономию и геодезию, иностранный чл.-корр. (1802) и иностранный почетный член (1824) Петер­ бургской АН. Отличительными чертами творчества Гаусса являются глубокая органическая связь в его исследованиях между теоретической и прикладной математикой, необычайная широта проблематики. Работы Гаусса оказали большое влияние на развитие алгебры, теории чисел, дифференциальной ге­ ометрии, теории тяготения, классической теории электричества и магнетиз­ ма, геодезии, целых отраслей теоретической астрономии.

124

они все должны играть в общей сумме относительно малую роль. Если ни одна из случайно действующих величин по своему дейст­ вию не окажется преобладающей над другими, то закон распреде­ ления очень близко подходит к нормальному

Такая закономерность проявляется во многих практических случаях. Например, еще Кетле* обнаружил, что вариация в одно­ родной группе характеризуется нормальной кривой. Если пост­ роить эмпирическую кривую распределения людей одной нации, пола и возраста по росту, весу, то она напоминает кривую Гаусса - Лапласа. Поэтому нормальное распределение часто применяется в тех случаях, когда истинный закон распределения известен, но вычисления по этому закону затруднительны, а аппроксимация его нормальным распределением допустима.

Примечание, Несмотря на широкое распространение, нормальное рас­ пределение не универсально. Если нет уверенности в его применимости, следует проверить возможность использования нормального распределения

для описания случайной величины с помощью критериев согласия.

Уравнение для плотности нормального распределения име­ ет вид

1 /(х;х,ст) = — ^ е 2а ^

а уравнение нормальной функции распределения -

Функция НОРМРАСП использует первое уравнение, если ар­ гумент интегральная = О, и второе уравнение, если аргумент инте-

* (Quetelet) Кетле Ламбер Адольф Жак (1796-1874) - бельгийский уче­ ный, социолог-позитивист Один из создателей научной статистики, иност­ ранный чл.-корр. Петербургской АН (1847). Установил, что некоторые мас­ совые общественные явления (рождаемость, смертность, преступность и др,) подчиняются определенным закономерностям, применил математические методы к их изучению.

125

гральная = 1. Так, формула =НОРМРАСП(42;40;1,5;0) рассчитает значение 0,109, а формула =НОРМРАСП(42;40;1,5;1) - значение 0,909.

Кривая плотности нормального распределения имеет симмет­ ричный холмообразный вид (рис. 6.8).

Рис. 6.8

Максимальная ордината кривой соответствует точке х -х = Мо = Me, По мере удаления от этой точки плотность распределе­ ния падает, и при х -> ± оо кривая асимптотически приближается к оси абсцисс. Изменение х при постоянстве а приводит к сме­ щению кривой вдоль оси абсцисс, не меняя ее формы. С увеличе­ нием а кривая становится более пологой, с уменьшением а - бо­ лее острой. Площадь, заключенная под кривой, асимптотически приближающейся к оси абсцисс, равна единице.

Для нормального распределения выполняются следующие ра­

венства: 1^1 = Цз ^ 0; Ц2 ^ ^^' ^4 ~ За"^; А^ = Q\ Ei^ = 0. Весьма важной практической задачей является определение

вероятности того, что случайная величина попадет на заданный

126

Пример 6.1. Для закупки и последующей продажи мужских зимних курток фирмой было проведено выборочное обследова­ ние мужского населения города в возрасте от 18 до 65 лет в целях определения его среднего роста. В результате было установлено, что средний рост Зс = 176 см, стандартное отклонение а = 6 см. Необходимо определить, какой процент общего числа закупае­ мых курток должны составлять куртки 5-го роста (182—186 см). Предполагается, что рост мужского населения города распределен по нормальному закону

Формула для решения задачи имеет следующий вид:

=НОРМРАСП(186;176;6;ИСТИНА)-"НОРМРАСП (182;176;6;ИСТИНА) = 0,95221 - 0,84134 = 0,11086 « 11%.

Таким образом, куртки 5-го роста должны составлять прибли­ зительно 11% общего числа закупаемых курток.

Функция НОРМОБР

См. также НОРМРАСП, НОРМСТРАСП, НОРМСТОБР, НОРМАЛИЗАЦИЯ, ДОВЕРИТ

Синтаксис:

НОРМОБР (вероятность; среднее; стандартное откл)

Резулыпат:

Рассчитывает обратное нормальное распределение.

Аргументы:

•вероятность: вероятность, соответствующая нормальному распределению;

•среднее: средняя арифметическая распределения;

• стандартное откл: стандартное отклонение распределения.

Замечания:

•если какой-либо аргумент не является числом, то функция НОРМОБР помещает в ячейку значение ошибки #ЗНАЧ!;

•если аргумент вероятность < О или аргумент вероятность > 1, то функция НОРМОБР помещает в ячейку значение ошибки #ЧИСЛО!;

• если аргумент стандартное откл < О, то функция НОР­ МОБР помещает в ячейку значение ошибки #ЧИСЛО!;

• если аргумент среднее = О и аргумент стандартное откл = =1, то функция НОРМОБР использует обратное стандартное нор­ мальное распределение (см, описание функции НОРМСТОБР);

127

• функция НОРМОБР использует для вычисления метод ите­ раций и производит вычисления, пока не получит результат с точ­ ностью ±3 • 10"^^. Если результат не сходится после 100 итераций, то функция помещает в ячейку значение ошибки #Н/Д.

Математико-статистическая интерпретация:

См, описание функции НОРМРАСП.

Функция обратного нормального распределения используется в ситуациях, когда известна вероятность определенного значения случайной величины и необходимо рассчитать это значение.

Например, формула =НОРМОБР(0,90879;40;1,5) рассчитыва­ ет значение 42,00001 (сравните с формулой =НОРМРАСП(42;40; 1,5; 1), рассчитывающей значение 0,90879).

На практике часто встречается задача, обратная задаче вычис­ ления вероятности попадания нормально распределенной слу­ чайной величины на участок, симметричный относительно мате­ матического ожидания X. Формула для вероятности попадания случайной величины на участок, симметричный относительно математического ожидания, имеет следующий вид:

Р{\х-х\<1)^2Ф*Ш-\

где / — половина длины участка, симметричного относительно ма­ тематического ожидания.

Пример 6,2. Для задачи, рассмотренной в примере 6Л, рассчи­ тать границы интервала роста мужского населения города, веро­ ятность попадания в который случайной величины роста состав­ ляет 0,95.

Для этого предварительно необходимо преобразовать аргу­ менты НОРМОБР к стандартному виду, в результате чего имеем

/ - НОРМОБР {{Р + 1)/2;0;а).

После подстановки данных получим формулу = НОРМОБР ((0,95 + 1)/2;0;6), которая рассчитает значение 11,7598» Таким об­ разом, границы искомого интервала составят 164,24 и 187,76 см.

В качестве границ интервалов часто берутся точки, отстоящие от математического ожидания на целое число стандартных откло-

128

нений (обычно а, 2а, За). Приведем значения вероятности попа­ дания нормально распределенной величины в интервалы с таки­ ми фаницами.

Фун!ащя НОРМСТРАСП

См. также НОРМРАСП, НОРМОБР, НОРМСТОБР, НОРМА­ ЛИЗАЦИЯ.

Синтаксис:

НОРМСТРАСП (Z)

Результат:

Рассчитывает стандартное нормальное распределение.

Аргументы:

z: значение, для которого вычисляется стандартное нормаль­ ное распределение.

Замечания:

если аргумент z не является числом, то функция НОРМ­ СТРАСП помещает в ячейку значение ошибки #ЗНАЧ!.

МатематикО'Статистическая интерпретация:

См. описание функции НОРМРАСП.

Стандартное нормальное распределение представляет собой не что иное, как «обычное» нормальное распределение, у которо­ го среднее равно нулю, а стандартное отклонение — единице.

Особое вьщеление функции стандартного нормального рас­ пределения связано с тем, что она используется при вычислении нормальных функций с другими значениями х и а (отличными от О и 1 соответственно). Практически во всех учебниках по тео­ рии вероятностей и теории статистики приведены таблицы для функции стандартного нормального распределения.

Например, формула =НОРМСТРАСП((42-40)/1,5) рассчита­ ет значение 0,90879, такое же как и формула =НОРМРАСП(42;40; 1,5;1) {см. описание функции НОРМРАСП).

129