Ход работы. 1. Работа с формулами. На новом листе редактора MS Excel выполнить расчёт объёма выборки для проведения выборочных исследований

1. Работа с формулами. На новом листе редактора MS Excel выполнить расчёт объёма выборки для проведения выборочных исследований.

Задача (А) звучит следующим образом, определить, какую долю составляет некоторый препарат в розничной продаже (n). Формула для расчёта имеет вид:

Исходные данные для расчёта взять из табл. 2:

Табл.2. Исходные данные задачи (А).

Обозначение	Значение	Величина
P	0,4	Предположительный размер оцениваемой доли
Q	1-P
N		Общее количество аптек
d	0,05	Абсолютная предельно допустимая ошибка в определении значения доли
	≈2	Критическое значение распределения Стьюдента

Данные из табл. 2 ввести в незаполненные ячейки рабочего листа, построить формулу с учётом особенностей синтаксиса Excel (см. табл. 1.) для вычисления объёма выборки. В формуле использовать относительные ссылки на ячейки с исходными данными.

Задача (Б) звучит следующим образом, определить средний размер продаж определённого препарата в аптеке, при этом предельное абсолютное отклонение не должно превышать 2000 грн. Формула для расчёта имеет вид:

Исходные данные для расчёта взять из табл. 3:

Табл. 3. Исходные данные задачи (Б).

Обозначение	Значение	Величина
N		Общее количество аптек
S2		Оценка выборочной дисперсии
d	0,05	Абсолютная предельно допустимая ошибка в определении значения
	≈2	Критическое значение распределения Стьюдента

Данные из табл. 3 ввести в незаполненные ячейки рабочего листа, построить формулу с учётом особенностей синтаксиса Excel (см. табл. 1.) для вычисления объёма выборки. В формуле использовать относительные ссылки на ячейки с исходными данными.

2. Работа со статистическими функциями Excel для определения параметров выборки. На новом листе редактора MS Excel выполнить расчёт характеристик выборки: среднее значение, медиану, моду, среднее линейное отклонение, дисперсию, среднеквадратическое отклонение, минимальное и максимальное значение. Исходные данные для задачи даны в табл. 4.

Табл. 4. Показатели физического развития школьников.

№	Масса тела, кг	Рост, см	ОКГ, см

Для вычисления параметров выборки используйте встроенные статистические функции Excel. Для вставки встроенных функций можно использовать Мастер функций.

Рис. 1. Мастер функций Шаг 1 (выбор категории функций и конкретной функции).

Рис. 2. Вставка функции СРЗНАЧ.

3. Параметрические критерии проверки статистических гипотез о средних и дисперсиях. Задача. Исследовали динамику продаж лекарственного препарата для лечения хронического гепатита, как с использованием рекламной акции, так и без неё. Для определения эффективности рекламы при продаже лекарственного препарата ГЕПТРАЛ® в аптеках области взяты данные:

· 15-ти аптек области, не проводивших рекламной акции (контрольная группа);

· 10-ти аптек сети «ЕВРОАПКЕКА» на 1, З и 9-й неделе от проведения рекламной акции (опытная группа).

Данные о динамике продаж приведены в табл. 5.

Табл. 5. Исходные данные.

№ п/п	Контрольная, X0	Опытная группа
1-я неделя, X1	3-я неделя, X2	9-я неделя, X3

Необходимо определить, с точки зрения статистики, эффективна ли реклама?

Для этого необходимо проверить средние значения для представленных выборок и оценить значимость различий. Прежде чем проверять гипотезу о равенстве средних этих выборок, необходимо проверить гипотезу равенстве дисперсий, чтобы знать какой из критериев выбрать для её проверки (существует несколько вариантов, с равными и неравными дисперсиями, а также без предположений о дисперсиях). Перед проверкой равенства дисперсий нужно убедиться, в том, что данные распределены по нормальному закону. Определим можно ли считать закон распределения выборок (X₀,X₁,X₂,X₃) нормальным. Если нет, хотябы для одной из пары выборок, то необходимо использовать непараметрический критерий. Следует помнить, что непараметрические критерии имеют для случая нормального распределения меньшую мощность, чем соответствующие параметрические критерии. В связи с этим не следует использовать непараметрические критерии при нормальном распределении случайных величин в исследуемых выборках.

Для проверки гипотезы о нормальном распределении можно воспользоваться правилом 3-х сигм, которое говорит о том, что практически все значения нормально распределённой случайной величины лежат в интервале , где σ-среднеквадратическое отклонение. Более строго — не менее чем с 99,7 % достоверностью, значение нормально распределенной случайной величины лежит в указанном интервале.

Следствие из этого правила является критерий проверки на нормальность: если почти все отклонения от среднего (99,7%) меньше 3-х сигм (ε_i <3σ), то данные можно считать распределёнными по нормальному закону. Для более строгой проверки, необходимо чтобы 2/3 отклонений от среднего были меньше, чем σ, и чтобы половина отклонений от среднего было меньше, чем 0,625σ.

На новом листе Excel ввести исходные данные для задачи (табл. 5.) и проверить нормально ли распределена каждая из выборок. Для этого для каждой из выборок рассчитать среднее (СРЗНАЧ) и СКО (СТАНДОКЛОН). Далее для каждого значения каждой выборки рассчитать абсолютное значение (модуль) отклонения от среднего (ABS). Почти все (15 значений для контрольний группы и по 10 значений для опытной группы) отклонения от среднего должны быть меньше утроенного значения СКО. Заполнить таблицу:

Условие	Необходимо	X0	X1	X2	X3
Почти все (99,7%) отклоенения от среднего меньше 3 сигм.	≈15(X0) и ≈10(X1,X2,X3)

Для автоматизации расчётов постройте столбец с разностями . Далее воспользуйтесь функцией, ЕСЛИ для идентификации тех значений разности, которые менее трёх сигм. Дальше воспользуетесь функцией СЧЁТЕСЛИ для подсчёта количества значений, не противоречащих условию. Впишите результат в таблицу.

После подтверждения гипотезы о нормальности распределения исследуемых выборок нужно проверить равенство дисперсий для связанных выборок (выборки X₀ и X₁,X₀ и X₂, X₀ и X₃). Воспользуемся критерием Фишера для каждой пары выборок. Сформулируем гипотезы H₀ и H₁ .

1. Нулевая гипотеза (H₀): S₁²= S₂² (дисперсии выборок равны);

2. Альтернативная гипотеза (H₁): S₁²≠S₂² (дисперсии выборок неравны, двусторонняя гипотеза).

Для проверки гипотез воспользуемся функцией ФТЕСТ, которая возвращает двустороннюю вероятность того, что разница между дисперсиями аргументов «Выборка1» и «Выборка2» несущественна. Эта функция позволяет определить, имеют ли две выборки различные дисперсии. Заполните таблицу:

	X0 и X1	X0 и X2	X0 и X3
Результат F-теста (%)
Вывод: (Да, равны или нет, не равны)

При этом интерпретировать полученный результат нужно следующим образом: если результат F-теста не менее 95%, тогда принимается нулевая гипотеза (H₀) (разница между дисперсиями несущественная, т.е. они равны), в противном случае принимается альтернативная гипотеза (H₁) (дисперсии неравны, и вероятность этого утверждения статистически значимая, т.е. более 95%, что достаточно для медико-биологических исследований).

Далее следует проверить равенство средних значений связанных выборок, чтобы определить эффективность рекламы после рекламной акции. Воспользуемся критерием Стьюдента (при равных или неравных дисперсиях см. выше). Сформулируем гипотезы H₀ и H₁ .

1. Нулевая гипотеза (H₀): (средние выборок равны);

2. Альтернативная гипотеза (H₁): (средние выборок неравны, двусторонняя гипотеза).

Для проверки гипотез воспользуемся функцией ТТЕСТ, которая возвращает вероятность, соответствующую критерию Стьюдента. Функция ТТЕСТ позволяет определить, вероятность того, что две выборки взяты из генеральных совокупностей, которые имеют одно и то же среднее. Заполните таблицу:

	X0 и X1	X0 и X2	X0 и X3
Результат Т-теста (%)
Вывод: (Да, равны или нет, не равны)

Для расчётов с помощью функции ТТЕСТ, нужно задать два дополнительных параметра: хвосты и тип теста. Задать параметр «хвосты», равным 2 (двустороннее распределение), а параметр «тип»: 2 – тест двухвыборочный с равными дисперсиями (гомоскедастический), 3 – тест двухвыборочный с неравными дисперсиями (гетероскедастический).

При этом интерпретировать полученный результат нужно следующим образом: если результат Т-теста не менее 95%, тогда принимается нулевая гипотеза (H₀) (средние равны), в противном случае принимается альтернативная гипотеза (H₁) (средние неравны, и вероятность этого утверждения статистически значимая, т.е. более 95%, что достаточно для медико-биологических исследований).

Общий результат должен выглядеть следующим образом: выборки (их средние и дисперсии) взятые после проведения рекламной акции должны принадлежать другой ГС (т.е. совокупности сети аптек в которых проведена рекламная акция, в отличие от исходной ГС, где рекламной акции пока ещё не было) и чем больше времени прошло после рекламной акции, тем менее должны отличаться выборки: исходная (взятая из ГС, без проведения рекламной акции) и экспериментальная (взятая из ГС аптек с проведённой рекламной акцией). Т.обр. эффективность рекламной акции должна подтвердиться в ближайшие дни после её проведения, и далее идти на спад, что должны подтверждать цифры, полученные в результате Ф- и Т- тестов. Т.е. вероятность равенства средних значений (т.е. принадлежности выборок одной ГС) исходной (X₀) и последней (X₃) выборки должна быть уже достаточно высокой.