Методичка к КР№2 ТОИ

Воспользуемся статистическими функциями для нахождения следующих величин.

1)Выборочное среднее – функция СРЗНАЧ(массив).

Формула в ячейке I5 – =СРЗНАЧ('Пример 2.1'!3:4)

2)Мода – функция МОДА(массив).

Формула в ячейке I6 – =МОДА('Пример 2.1'!3:4).

Выборка из примера 2.1 имеет две моды. Функция МОДА находит лишь одну из них. Чтобы найти все моды выборки, необходимо создать соответствующую процедуру и командную кнопку для ее вызова. Это будет сделано в разделе «МАКРОСЫ».

3) Медиана – функция МЕДИАНА(массив).

Формула в ячейке I7 – =МЕДИАНА('Пример 2.1'!3:4).

4)Выборочная статистическая дисперсия – функция

ДИСПР(массив).

Формула в ячейке I8 – =ДИСПР('Пример 2.1'!3:4).

5)Исправленная статистическая дисперсия – функция ДИСП(мас-

сив).

Формула в ячейке I13 – =ДИСП('Пример 2.1'!3:4)

6)Исправленное среднее квадратичное отклонение – функция

СТАНДОТКЛОН(массив).

Формула в ячейке I14 – =СТАНДОТКЛОН('Пример 2.1'!3:4).

Подсчитаем моменты выборки.

7)Центральный момент первого порядка равен нулю. Помещаем в ячейку I11 нулевое значение.

8)Центральный момент второго порядка равен выборочной стати-

стической дисперсии. Помещаем в ячейку I12 формулу =I8.

9)Начальный момент первого порядка равен выборочному сред-

нему. Помещаем в ячейку I9 формулу =I5.

10)Для нахождения начального момента второго порядка восполь-

зуемся функцией СУММПРОИЗВ(массив1; массив2; массив3; … ), которая перемножает соответствующие элементы массивов 1, 2, 3, … , а затем складывает полученные произведения. При подсчете начального момента второго порядка в качестве массива1 выбираем статистический ряд (строка 18), а массивы 2 и 3 – это варианты выборки (строка 16). Полученную таким образом сумму делим на объем выборки (ячейка D10).

=СУММПРОИЗВ('Пример 2.1'!18:18;'Пример 2.1'!16:16;'Пример

2.1'!16:16) / 'Пример 2.1'!D10

100

7.5.3. Обновление таблицы

Таблицу из данного примера обновляют одновременно с обновлением таблицы из примера 2.1. Исключение составляют моды 2, 3, и т.д. Для их вычисления нужно нажать соответствующую командную кнопку.

Замечание. Дадим краткое описание программы Описательная статистика, встроенной в Пакет анализа. Она вычисляет ряд числовых характеристик выборки, в том числе предельную ошибку e доверительного интервала

x - e < a < x + e для неизвестного математического ожидания a при заданном уровне надежности g , если дисперсия выборки заранее неизвестна. Последовательность действий при работе с этой программой:

1)указывают входной интервал – массив исходных числовых данных, который введен как строка или столбец;

2)указывают выходной интервал, т.е. указывают адрес левой верхней ячейки местоположения результатов программы Описательная статистика на экране; при этом следят, чтобы эти результаты не перекрывались с уже имеющейся на экране информацией;

3)устанавливают флажки: Параметры вывода+Итоговая статистика и Параметры вывода+Уровень надежности и задают уровень надежности

(тогда в последней строке Уровень надежности результирующей таблицы

появится информация о радиусе e доверительного интервала x - e < a < x + e для неизвестного математического ожидания a при заданном уровне надежности g );

4) запускают программу и затем корректируют местоположение результатов на экране.

7.6.Пример 3.2

7.6.1.Ввод текста задачи и начальных данных

Рис 7.43

Особенности форматирования.

1) Шрифт ячейки A3 – Symbol, полужирный, 12 пт., выравнивание – по правому краю.

101

2) Шрифт ячейки B3 – Arial, полужирный, 10 пт., выравнивание – по левому краю.

7.6.2. Решение задачи

Шаг №1. Вычисляем характеристики выборки, необходимые для нахождения доверительного интервала неизвестного математического ожидания (рис. 7.44).

Рис 7.44

При вычислении искомых величин использовались данные, полученные при решении примера 2.2:

1)интервальный статистический ряд – 'Пример 2.2'!29:29;

2)середины интервалов – 'Пример 2.2'!27:27;

3)объем выборки – 'Пример 2.2'!E20.

Выборочное среднее.

Математическая формула:

x = (åmi x*i ) / n .

i

Формула Excel:

=СУММПРОИЗВ('Пример 2.2'!29:29;'Пример 2.2'!27:27)/ 'Пример 2.2'!E20.

Исправленная статистическая дисперсия.

Математическая формула:

		æ		*	)	2	ö
S 2 =	1	ç	åmi (x*i )2 -	(åmi xi			÷
		ç		i			÷.
				n
	n -1ç		i				÷
		ç					÷
		è					ø

Формула Excel:

=(СУММПРОИЗВ('Пример 2.2'!29:29;'Пример 2.2'!27:27; 'Пример 2.2'!27:27)–СУММПРОИЗВ('Пример 2.2'!29:29; 'Пример 2.2'!27:27)^2/'Пример 2.2'!E20)/('Пример 2.2'!E20–1).

102

Стандартное отклонение равно квадратному корню из исправленной статистической дисперсии – =E7^0,5.

Величина ta,n-1 . Для вычисления ta,n-1 воспользуемся статистической

функцией Excel СТЬЮДРАСПОБР, которая является электронным аналогом таблицы распределения Стьюдента, составленной для двусторонней критической области:

СТЬЮДРАСПОБР(аргумент1;аргумент2)

·аргумент1 – уровень значимости;

·аргумент2 – число степеней свободы.

Заносим в E9 формулу =СТЬЮДРАСПОБР(B3;'Пример 2.2'!E20–1).

Шаг №2. Находим доверительный интервал для неизвестного математического ожидания выборки (рис. 7.45).

Рис 7.45

Доверительный интервал. Середина интервала – выборочное сред-

нее, радиус интервала – S ×ta,n-1 . Следовательно, n

·левый конец интервала – =E6–E8*E9/'Пример 2.2'!E20^0,5;

·правый конец интервала – =E6+E8*E9/'Пример 2.2'!E20^0,5.

Шаг №3. Находим доверительный интервал для неизвестного среднего квадратичного отклонения выборки (рис. 7.46).

Рис 7.46

Доверительная вероятность. Равна 1–a. Заносим в E13 формулу

=1–B3.

Доверительный интервал. Чтобы найти концы доверительного интервала, воспользуемся статистической функцией ХИ2ОБР, которая яв-

ляется электронным аналогом таблицы c2 -распределения.

ХИ2ОБР(аргумент1;аргумент2):

·аргумент1 – значение вероятности;

·аргумент2 – число степеней свободы c2 -распределения.

103

Математические формулы для вычисления соответственно левого и правого концов доверительного интервала:

S ×

n -1

,

S ×

n -1

.

c12-g

,n-1

c12+g

,n-1

2

2

Соответствующие формулы Excel:

·=(E7*('Пример 2.2'!E20–1)/ХИ2ОБР((1–E13)/2;'Пример 2.2'!E20–1))^0,5;

·=(E7*('Пример 2.2'!E20–1)/ХИ2ОБР((1+E13)/2;'Пример 2.2'!E20–1))^0,5.

7.6.3. Обновление таблицы

Все значения таблицы обновляются автоматически после ввода новых начальных данных и обновления таблицы из примера 2.2.

Замечание. Если массив исходных числовых данных введен как строка или столбец, то для определения радиуса e доверительного интервала

x - e < a < x + e для неизвестного математического ожидания a при неизвестной дисперсии и заданном уровне надежности g можно использовать встро-

енную в Пакет анализа программу Описательная статистика (см. пример

3.1). Если же дисперсия выборки заранее известна, то для нахождения радиуса указанного выше доверительного интервала пользуются статистической функ-

цией ДОВЕРИТ.

7.7.Пример 3.3

7.7.1.Ввод текста задачи и начальных данных

Рис 7.47

7.7.2. Решение задачи

Шаг №1. Находим искомый доверительный интервал (рис. 7.48).

104

Рис 7.48

Доверительный интервал. Середина интервала – выборочное среднее (ячейка E7). Поскольку среднее квадратичное отклонение в данной задаче известно, то для вычисления радиуса доверительного интервала будем использовать статистическую функцию

ДОВЕРИТ(аргумент1;аргумент2;аргумент3):

·аргумент1 – уровень значимости;

·аргумент2 – среднее квадратичное отклонение;

·аргумент3 – объем выборки.

Формулы в ячейках D10, D11.

·=E7–ДОВЕРИТ(1–E4;E3;A6)

·=E7+ДОВЕРИТ(1–E4;E3;A6)

7.7.3. Обновление таблицы

Все значения таблицы обновляются автоматически после ввода новых начальных данных.

7.8.Пример 4.1

7.8.1.Ввод текста задачи и начальных данных

Рис 7.49

7.8.2. Решение задачи

Шаг №1. Строим интервалы выборки и находим количество степеней свободы (рис. 7.50).

105

Рис 7.50

Концы интервалов. Интервалы уже построены и находятся в строке 25 листа пример 2.2. Поэтому помещаем в A8 формулу ='Пример 2.2'!A25 и копируем ячейку вправо.

Первый и последний интервалы бесконечны, поэтому заносим в крайние ячейки символ «¥».

Особенности форматирования.

Вызвать символ «¥» можно в редакторе Word с помощью команды Вставка+Символ, а затем скопировать его в ячейки Excel.

Количество степеней свободы.

Математическая формула: n = k -1- l , где

k – количество интервалов;

l – количество неизвестных параметров предполагаемого закона распределения случайной величины X.

Величина k–1 равна количеству числовых значений в строке 8 (рис. 7.50). Поэтому формула для вычисления n в Excel будет выглядеть следующим образом:

=СЧЁТ(8:8)–I4.

Шаг №2. Проверяем выполнение эмпирического правила критерия согласия Пирсона (рис. 7.51).

Рис 7.51

106

Значения нормального распределения. Необходимо занести в ячей-

æ x - x ö

ки строки 12 значения интегральной функции Лапласа Fç i ÷ , где

çè s ÷ø

·x – выборочное среднее (ячейка E6 листа Пример 3.2);

·s – стандартное отклонение (ячейка E8 листа Пример 3.2);

·xi – соответствующий конец интервала (строка 8).

		1		x	-	t 2
Чтобы найти значения F(x) =				òe		2	dt , воспользуемся статисти-
		2p
			0

ческой функцией НОРМСТРАСП(аргумент):

1)F(-¥) = -1/ 2 , Þ заносим в A12 значение –0,5;

2)F(+¥) =1/ 2 , Þ заносим в I12 значение 0,5;

æ			ö
ç	xi - x			÷	, Þ заносим в B12 формулу
3) Fç	s	÷
è		ø

=НОРМСТРАСП((B8–'Пример 3.2'!$E$6)/'Пример 3.2'!$E$8)–0,5

и копируем ее вправо.

Замечание. В прил. 3 приведены значения интегральной функции Лапласа

	F(x)=		1		x	-t 2 / 2dt .
					òe
			2p
				0

Аналогом этой таблицы в Excel служит встроенная статистическая функция НОРМСТРАСП. Эта функция для заданного значения аргумента x возвращает значение интегральной функции Лапласа вида

F(x)=

1

x

-t 2 / 2dt =

1

1

x

-t 2 / 2dt ,

òe

+

òe

2p

2

2p

-¥

0

т. е. полученный при помощи функции НОРМСТРАСП результат больше соответствующего результата из прил. 3 на 0,5. Это учитывают при программировании формул для подсчета вероятностей pi .

Вероятность попадания возможных значений случайной величи-

ны в соответствующий интервал. Равна разности значений функции Лапласа, находящихся в строке 12, вычисленных на правом и левом концах соответствующего интервала. Заносим в ячейку A14 формулу

=B12–A12 и копируем ее вправо.

Произведение вероятности на объем выборки. Объем выборки на-

ходится в ячейке E20 листа Пример 2.2. Заносим в A16 формулу =A14*'Пример 2.2'!$E$20 и копируем ее вправо.

107

Вывод. Чтобы сформулировать вывод, воспользуемся встроенной функцией ЕСЛИ.

Функция ЕСЛИ(условие;значение1;значение2) возвращает значение1,

если условие выполняется, и возвращает значение2, если условие не выполняется.

Эмпирическое правило критерия согласия Пирсона выполняется, если минимум значений, находящихся в строке 16 (рис. 7.51), больше либо равно 5. Заносим в ячейку A18 формулу вида

=ЕСЛИ(МИН(16:16)>=5; "Эмпирическое правило критерия согласия Пирсона выполняется"; "Эмпирическое правило критерия согласия Пирсона не выполняется").

Шаг №3. Меняем интервалы выборки (рис. 7.52).

Рис 7.52

Новые концы интервалов. Чтобы эмпирическое правило критерия согласия Пирсона выполнялось, необходимо построить новые интервалы выборки. Для этого в разделе Макросы будет создана соответствующая процедура. Для ее вызова необходимо нажать кнопку Строим новые интервалы.

Новое количество степеней свободы. Формула в F22 аналогична формуле, описанной выше (ячейка F9, рис 7.50):

=СЧЁТ(21:21)–I4.

Новый интервальный статистический ряд. Будет сформирован макросом, строящим новые интервалы.

Шаг №4. Повторяем проверку эмпирического правила (рис. 7.53).

108

Рис 7.53

Построение данного участка таблицы аналогично описанному в шаге №2. Поэтому проще всего скопировать строки 11:18, а затем откорректировать формулы строки 27 и длину строк 27, 29, 31 в соответствии с новыми интервалами.

Шаг №5. Применяем критерий согласия Пирсона (рис. 7.54).

Рис 7.54

Величины (mi - npi )2 . Заносим в A36 формулу =(A24–A31)^2/A31 npi

и копируем ее вправо.

Величина cнабл2 . Равна сумме элементов строки 36: =СУММ(36:36). Величина ca2 ,n . Уровень значимости находится в ячейке B3, новое

количество степеней свободы – в F22. Следовательно, формула в E37

=ХИ2ОБР(B3;F22). Особенности форматирования.

Математические формулы вставляют в ячейки с помощью команды Вставка+Объект. При этом следует отказываться от линии рамки и заливки. Сделать это можно на странице Цвета и линии команды Формат объекта (рис. 7.55).

109