Сравнение двух выборок в excel

Работа с матрицами. Балансовые модели

Задание. Межотраслевой баланс производства и распределения продукции для 4 отраслей имеет вид:

Производящие отрасли	Потребляющие отрасли	Валовой продукт (Х)
1	2	3	4
1	80	45	85	95	475
2	25	35	20	30	825
3	15	15	55	75	650
4	95	5	5	95	820

1. Найти конечный продукт каждой отрасли, чистую продукцию каждой отрасли, матрицу коэффициентов прямых затрат.
2. Какой будет конечный продукт каждой отрасли, если валовой продукт первой отрасли увеличится в 2 раза, у второй увеличится на половину, у третьей не изменится, у четвертой – уменьшится на 10 процентов.
3. Найти валовой продукт, если конечный станет равен 700, 500, 850 и 700.
Решение:
1. Составим матрицу коэффициентов прямых затрат А={aij}, где , j=1,2,…,n.
Зная величины валовой продукции (Xi) для каждой отрасли, можно определить объёмы конечной продукции каждой отрасли (Yi) по формуле:
Y = (E – A)X.
Для нахождения Y будем использовать функцию MS Excel, выполняющую умножение матриц «МУМНОЖ».
Чистую продукцию каждой отрасли (Zj) найдем по формуле:

Таблица 1.1
Полная балансовая таблица для четырех отраслей, полученная на основе исходных данных

Производящие отрасли	Потребляющие отрасли	Конечный продукт (Y)	Валовой продукт (Х)
1	2	3	4
1	80	45	85	95	170	475
2	25	35	20	30	715	825
3	15	15	55	75	490	650
4	95	5	5	95	620	820
Чистая продукция (Z)	260	725	485	525
Валовой продукт (Х)	475	825	650	820

Проверка:  
2. Определим, какой будет конечный продукт каждой отрасли, если валовой продукт первой отрасли увеличится в 2 раза, у второй увеличится на половину, у третьей не изменится, у четвертой – уменьшится на 10 процентов, т.е. если :
Рассчитаем дополнительно величину чистой продукции по каждой отрасли и запишем полную балансовую таблицу, соответствующую данной ситуации.

Таблица 1.2

Производящие отрасли	Потребляющие отрасли	Конечный продукт (Y)	Валовой продукт (Х)
1	2	3	4
1	80	45	85	95	645	950
2	25	35	20	30	1127,5	1237,5
3	15	15	55	75	490	650
4	95	5	5	95	538	738
Чистая продукция (Z)	735	1137,5	485	443
Валовой продукт (Х)	950	1237,5	650	738

Проверка: .
3. Найдем валовой продукт, если конечный станет равен 700, 500, 850 и 700, т.е. Для этого используем формулу:,
где  — матрица коэффициентов полных материальных затрат.
На основе матрицы (Е-А) рассчитаем матрицу , используя функцию «МОБР» MS Excel.
После расчета вектора валового продукта , элементы хij (объём продукции отрасли i, расходуемой в отрасли j) рассчитаем по формуле:.
Рассчитаем дополнительно величину чистой продукции по каждой отрасли и запишем полную балансовую таблицу, соответствующую данной ситуации.

Таблица 1.3

Производящие отрасли	Потребляющие отрасли	Конечный продукт (Y)	Валовой продукт (Х)
1	2	3	4
1	203,33	36,26	142,70	124,99	700	1207,28
2	63,54	28,20	33,58	39,47	500	664,79
3	38,12	12,09	92,33	98,68	850	1091,22
4	241,46	4,03	8,39	124,99	700	1078,87
Чистая продукция (Z)	660,83	584,21	814,22	690,74
Валовой продукт (Х)	1207,28	664,79	1091,22	1078,87

Проверка: .

Как провести двухвыборочный t-тест в Excel

Предположим, исследователи хотят знать, имеют ли два разных вида растений в определенной стране одинаковую среднюю высоту. Поскольку обход и измерение каждого растения заняло бы слишком много времени, они решили собрать образец из 20 растений каждого вида.

На следующем изображении показана высота (в дюймах) каждого растения в каждом образце:

Мы можем провести двухвыборочный t-тест, чтобы определить, имеют ли два вида одинаковую среднюю высоту, используя следующие шаги:

Шаг 1: Определите, равны ли дисперсии генеральной совокупности .

Когда мы проводим двухвыборочный t-критерий, мы должны сначала решить, будем ли мы предполагать, что две совокупности имеют равные или неравные дисперсии. Как правило, мы можем предположить, что совокупности имеют равные дисперсии, если отношение большей выборочной дисперсии к меньшей выборочной дисперсии составляет менее 4:1.

Мы можем найти дисперсию для каждого образца, используя функцию Excel =VAR.S(диапазон ячеек) , как показано на следующем рисунке:

Отношение большей дисперсии выборки к меньшей дисперсии выборки составляет 12,9053 / 8,1342 = 1,586 , что меньше 4. Это означает, что мы можем предположить, что дисперсии генеральной совокупности равны.

Шаг 2: Откройте пакет инструментов анализа .

На вкладке «Данные» на верхней ленте нажмите «Анализ данных».

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

Шаг 3: Выберите подходящий тест для использования.

Выберите вариант с надписью t-Test: Two-Sample Assassining Equal Variances и нажмите OK.

Шаг 4: Введите необходимую информацию .

Введите диапазон значений для переменной 1 (наша первая выборка), переменной 2 (наша вторая выборка), гипотетической средней разницы (в этом случае мы поместили «0», потому что мы хотим знать, равна ли истинная средняя разница генеральной совокупности 0), и выходной диапазон, в котором мы хотели бы видеть результаты t-теста. Затем нажмите ОК.

Шаг 5: интерпретируйте результаты .

После того, как вы нажмете OK на предыдущем шаге, отобразятся результаты t-теста.

Вот как интерпретировать результаты:

Среднее значение: это среднее значение для каждого образца. Образец 1 имеет среднюю высоту 15,15 , а образец 2 имеет среднюю высоту 15,8 .

Дисперсия: это дисперсия для каждого образца. Выборка 1 имеет дисперсию 8,13 , а выборка 2 — 12,90 .

Наблюдения: это количество наблюдений в каждой выборке. Обе выборки содержат по 20 наблюдений (например, по 20 отдельных растений в каждой выборке).

Объединенная дисперсия: Число , которое рассчитывается путем «объединения» дисперсий каждой выборки вместе по формуле +n 2 -2), что оказывается равным 10,51974.Это число позже используется при вычислении тестовой статистики t .

Гипотетическая средняя разница: число, которое мы «предполагаем», представляет собой разницу между двумя средними значениями совокупности. В данном случае мы выбрали , потому что хотим проверить, равна ли разница между двумя популяциями в среднем 0, например, разницы нет.

df: Степени свободы для t-критерия, рассчитанные как n 1 + n 2 -2 = 20 + 20 – 2 = 38 .

t Stat: тестовая статистика t , рассчитанная как t = / √ [ s 2 p (1/n 1 + 1/n 2 )]

В этом случае t = / √ [10,51974(1/20+1/20)] = -0,63374 .

P(T<=t) двухсторонний: значение p для двустороннего t-критерия. В этом случае р = 0,530047.Это намного больше, чем альфа = 0,05, поэтому мы не можем отвергнуть нулевую гипотезу. У нас нет достаточных доказательств, чтобы сказать, что два средних значения населения различны.

t Критический двухсторонний: это критическое значение теста, найденное путем определения значения в таблице распределения t , которое соответствует двустороннему тесту с альфа = 0,05 и df = 38. Получается 2,024394.Поскольку наша тестовая статистика t меньше этого значения, мы не можем отвергнуть нулевую гипотезу. У нас нет достаточных доказательств, чтобы сказать, что два средних значения населения различны.

Обратите внимание, что подход с использованием p-значения и критического значения приведет к одному и тому же выводу

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие типы t-тестов в Excel:

Как провести одновыборочный t-тест в ExcelКак провести t-тест для парных выборок в Excel

Нормальное распределение в статистике

История закона насчитывает 300 лет. Первым открывателем стал Абрахам де Муавр, который придумал аппроксимацию биномиального распределения еще 1733 году. Через много лет Карл Фридрих Гаусс (1809 г.) и Пьер-Симон Лаплас (1812 г.) вывели математические функции.

Лаплас также обнаружил замечательную закономерность и сформулировал центральную предельную теорему (ЦПТ), согласно которой сумма большого количества малых и независимых величин имеет нормальное распределение.

Нормальный закон не является фиксированным уравнением зависимости одной переменной от другой. Фиксируется только характер этой зависимости. Конкретная форма распределения задается специальными параметрами. Например, у = аx + b – это уравнение прямой. Однако где конкретно она проходит и под каким наклоном, определяется параметрами а и b. Также и с нормальным распределением. Ясно, что это функция, которая описывает тенденцию высокой концентрации значений около центра, но ее точная форма задается специальными параметрами.

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

График нормального распределения напоминает колокол, поэтому можно встретить название колоколообразная кривая. У графика имеется «горб» в середине и резкое снижение плотности по краям. В этом заключается суть нормального распределения. Вероятность того, что случайная величина окажется около центра гораздо выше, чем то, что она сильно отклонится от середины.

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

Формула нормального распределения (плотности) следующая.

Формула состоит из двух математических констант:

π – число пи 3,142;

е – основание натурального логарифма 2,718;

двух изменяемых параметров, которые задают форму конкретной кривой:

m – математическое ожидание (в различных источниках могут использоваться другие обозначения, например, µ или a>);

σ2 – дисперсия;

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

Конкретная форма нормального распределения зависит от 2-х параметров: математического ожидания (m) и дисперсии (σ2). Кратко обозначается N(m, σ2) или N(m, σ). Параметр m (матожидание) определяет центр распределения, которому соответствует максимальная высота графика. Дисперсия σ2 характеризует размах вариации, то есть «размазанность» данных.

Параметр математического ожидания смещает центр распределения вправо или влево, не влияя на саму форму кривой плотности.

А вот дисперсия определяет остроконечность кривой. Когда данные имеют малый разброс, то вся их масса концентрируется у центра. Если же у данных большой разброс, то они «размазываются» по широкому диапазону.

Плотность распределения не имеет прямого практического применения. Для расчета вероятностей нужно проинтегрировать функцию плотности.

Вероятность того, что случайная величина окажется меньше некоторого значения x, определяется функцией нормального распределения:

P(a ≤ X < b) = Ф(b) – Ф(a)

-Критерий стьюдента для уравнения множественной регрессии.

Частный
-критерий
оценивает значимость коэффициентов
чистой регрессии. Зная величину,
можно определить и-критерий
для коэффициента регрессии при-м
факторе,,
а именно:

.
(2.24)

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

,
(2.25)

где
– коэффициент чистой регрессии при
факторе,– средняя квадратическая (стандартная)
ошибка коэффициента регрессии.

Для уравнения множественной регрессии
средняя квадратическая ошибка коэффициента
регрессии может быть определена по
следующей формуле:

,
(2.26)

где
,– среднее квадратическое отклонение
для признака,– коэффициент детерминации для
уравнения множественной регрессии,– коэффициент детерминации для
зависимости факторасо всеми другими факторами уравнения
множественной регрессии;– число степеней свободы для остаточной
суммы квадратов отклонений.

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

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

На данном примере рассмотрим, как оценивается надежность полученного уравнение регрессии. Этот же тест используется для проверки гипотезы о том, что коэффициенты регрессии одновременно равны нулю, a=0 , b=0 . Другими словами, суть расчетов — ответить на вопрос: можно ли его использовать для дальнейшего анализа и прогнозов?

Для установления сходства или различия дисперсий в двух выборках используйте данный t-критерий .

Итак, целью анализа является получение некоторой оценки, с помощью которой можно было бы утверждать, что при некотором уровне α полученное уравнение регрессии — статистически надежно. Для этого используется коэффициент детерминации R 2
.
Проверка значимости модели регрессии проводится с использованием F-критерия Фишера, расчетное значение которого находится как отношение дисперсии исходного ряда наблюдений изучаемого показателя и несмещенной оценки дисперсии остаточной последовательности для данной модели.
Если расчетное значение с k 1 =(m) и k 2 =(n-m-1) степенями свободы больше табличного при заданном уровне значимости, то модель считается значимой.

где m – число факторов в модели.
Оценка статистической значимости парной линейной регрессии производится по следующему алгоритму:
1. Выдвигается нулевая гипотеза о том, что уравнение в целом статистически незначимо: H 0: R 2 =0 на уровне значимости α.
2. Далее определяют фактическое значение F-критерия:
где m=1 для парной регрессии.
3

Табличное значение определяется по таблицам распределения Фишера для заданного уровня значимости, принимая во внимание, что число степеней свободы для общей суммы квадратов (большей дисперсии) равно 1 и число степеней свободы остаточной суммы квадратов (меньшей дисперсии) при линейной регрессии равно n-2 (или через функцию Excel FРАСПОБР(вероятность;1;n-2)).
F табл — это максимально возможное значение критерия под влиянием случайных факторов при данных степенях свободы и уровне значимости α. Уровень значимости α — вероятность отвергнуть правильную гипотезу при условии, что она верна

Обычно α принимается равной 0,05 или 0,01.
4. Если фактическое значение F-критерия меньше табличного, то говорят, что нет основания отклонять нулевую гипотезу.
В противном случае, нулевая гипотеза отклоняется и с вероятностью (1-α) принимается альтернативная гипотеза о статистической значимости уравнения в целом.
Табличное значение критерия со степенями свободы k 1 =1 и k 2 =48, F табл = 4

Выводы
: Поскольку фактическое значение F > F табл, то коэффициент детерминации статистически значим (найденная оценка уравнения регрессии статистически надежна
)
.

Проверка статистических гипотез о виде распределения

Задание 1. Дана выборка числа посетителей Интернет – сайта за 30 дней. Проверить по критерию Пирсона на уровне значимости ? = 0,02 статистическую гипотезу о том, что генеральная совокупность, представленная выборкой, имеет нормальный закон распределения.

15	31	26	34	31	30	28	36	35	33	25	35	33	30	27
19	23	28	25	25	41	29	24	17	18	28	30	31	31	31

Решение:
Составим интервальный вариационный ряд.
Построим интервальный статистический ряд. Оптимальное число интервалов определим по формуле Стерджесса: округляем до 6
Ширину интервала разбиения рассчитываем по формуле:
Записываем интервальный статистический ряд:

Интервал	15-19,3	19,3-23,7	23,7-28	28-32,3	32,3-36,7	36,7-41
Частота	4	1	9	9	6	1

По исходным данным выборки с помощью встроенных функций «СРЗНАЧ» и «СТАНДОТКЛОН» рассчитаем выборочную среднюю и среднее квадратическое отклонение:
Произведем проверку статистической гипотезу о том, что генеральная совокупность, представленная выборкой, имеет нормальный закон распределения с помощью критерия согласия Пирсона (уровень значимости .
Вычислим наблюдаемое значение критерия Пирсона:
n=30
Значения функции Лапласа вычислим, используя встроенную функцию MS Excel «НОРМРАСП» с помощью формулы:
Оформим расчеты в таблице.
Таблица 5.1

хi	xi+1			Ф(zi)	Ф(zi+1)	Pi	n/i	ni
15	19,3	-2,247	-1,520	-0,4877	-0,4358	0,0519	1,56	4	3,835
19,3	23,7	-1,520	-0,777	-0,4358	-0,2814	0,1543	4,63	1	2,846
23,7	28	-0,777	-0,051	-0,2814	-0,0202	0,2612	7,84	9	0,173
28	32,3	-0,051	0,676	-0,0202	0,2504	0,2706	8,12	9	0,096
32,3	36,7	0,676	1,419	0,2504	0,4220	0,1717	5,15	6	0,140
36,7	41	1,419	2,145	0,4220	0,4840	0,0620	1,86	1	0,397
Сумма									7,488

Рассчитываем в MS Excel критическое значение  для уровня значимости  и числа степеней свободы k=s-3=6-3=3 (s — число интервалов) находим .
Так как , то гипотезу о нормальном распределении принимаем.
Ответ: на 2% уровне значимости статистическая гипотеза о том, что генеральная совокупность, представленная выборкой, имеет нормальный закон распределения, подтверждается.

Задание 2. При производстве микросхем процессоров используются кристаллы кварца. Стандартом предусмотрено, чтобы у 50 % образцов не было обнаружено ни одного дефекта кристаллической структуры, у 15% — один дефект, у 13 % — 2 дефекта, у 12 % — 3 дефекта, у 10 % более 3 дефектов.
При анализе выборочной партии оказалось, что из 1000 экземпляров распределение по дефектам имеет распределение, указанное в таблице:

0 дефектов	1 дефект	2 дефекта	3 дефекта	более 3
471	159	135	127	108

Можно ли с вероятностью 0,99 (при ? = 0,01) считать, что партия соответствует стандарту?
Решение:
Вычислим наблюдаемое значение критерия Пирсона:
ni – теоретические частоты (в случаях, предусмотренных стандартом); — эмпирические частоты.
Таблица 5.2

№ п/п	ni	n/i
1	500	471	1,786
2	150	159	0,509
3	130	135	0,185
4	120	127	0,386
5	100	108	0,593
Сумма			3,459

Рассчитываем в MS Excel критическое значение  для уровня значимости  и числа степеней свободы k=s-1=5-1=4 (s — число групп) находим .
Так как , то гипотеза о соответствии СТАНДАРТУ проверенной партии образцов микросхем подтверждается.
Ответ: с вероятностью 0,99 можно считать, что партия соответствует стандарту.

t-тест: как выполнить в MS Excel?

Вне зависимости от того, для чего вам может понадобиться вычислить критерий Стьюдента, в Microsoft Excel есть функция TTEST, которая позволяет это сделать. Она доступна в надстройке Пакет анализа. Рассмотрим, как использовать функцию на примере Microsoft Excel 2013 в Windows 7.

Как выполнить t-тест в Excel 2013

Синтаксис функции следующий: ТТЕСТ(массив1;массив2;хвосты;тип). Скопируйте эту формулу в любое поле вашего документа Excel.

Для выявления различий между двумя выборками с известным законом распределения применяют t-критерий различия Стьюдента и критерий различия Фишера. При этом предполагается, что данные распределены по нормальному закону. Первый критерий сравнивает средние двух выборок и вычисляет вероятность того, что они относятся к одной и той же генеральной совокупности. Второй критерий проверяет принадлежность дисперсий двух выборок одной генеральной совокупности. В обоих случаях по вычисленной вероятности судят о принадлежности выборок к одной или разным совокупностям: если вероятность случайного появления значений в исследуемых выборках меньше уровня значимости α

Воспроизведите полученные результаты. В ячейку В14 введите функцию ТТЕСТ из группы Статистические, заполните параметры и нажмите ОК. Здесь выбран Тип=3, поскольку выборки не связаны, независимы и с разным числом значений.

Далее вызовите инструмент Двухвыборочный t-тест с различными дисперсиями через меню Сервис Анализ данных…. На рис. 2.93 показано заполнение параметров инструмента. Интервал переменной 1 $А$2:$A$10 и интервал переменной 2 $B$2:$B$12 это диапазоны анализируемых данных. Выходной интервал $D$1 – это ячейка, начиная с которой будет выведен результат. Поле Альфа позволяет установить требуемый уровень значимости α =0.05.

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

Дисперсионный анализ

Часто требуется оценить существенность влияния на выборки одного или нескольких факторов. При этом выборки должны стремиться к нормальному распределению и быть независимыми. В Excel включены следующие инструменты: Однофакторный дисперсионный анализ, Двухфакторный дисперсионный анализ с повторениями, Двухфакторный дисперсионный анализ без повторения.

Рассмотрим однофакторный дисперсионный анализ. Степень влияния фактора на выборку определяется сравнением дисперсий двух выборок: выборки с наличием исследуемого фактора и выборки без этого фактора (со случайными причинами). Инструмент Excel Однофакторный дисперсионный анализ вычисляет вероятность случайности различий (Р-значение), которая указывает на значимость различий: если уровень значимости меньше 0.05, то различия не случайны и говорят о статистическом влиянии фактора на выборку (переменную).

В качестве примера проведем анализ влияния фактора цены комплексного обеда на дневную посещаемость кафе. На рисунке приведен результат анализа: Р-значение=0.00068257

А. Сравнение выборочных дисперсий.

В появившимся окне указываем диапазоны ячеек с вариантами обеих выборок («Интервал переменной 1» и «Интервал переменной 2), если диапазон ячеек выделяем вместе с названием столбцов ставим отметку в окошке «Метки», нажимаем «ОК» (как показано на Рисунке и получаем таблицу с результатами.

Б. Сравнение средних.

Выбираем раздел меню «Данные»-«Анализ данных»-«Двухвыборочный t-тест с одинаковыми дисперсиями» (Рис. 9).

появившимся окнеуказываем диапазоны ячеек с вариантами обеих выборок («Интервал переменной 1» и «Интервал переменной 2). Если диапазон ячеек выделяем вместе с названием столбцов, ставим отметку в окошке «Метки», нажимаем «ОК» (как показано на Рисунке 9) и получаем таблицу с результатами.

Распределение Фишера (F-распределение). Распределения математической статистики в EXCEL

history 12 ноября 2016 г.

Распределения вероятностей

Рассмотрим распределение Фишера (F-распределение). С помощью функции MS EXCEL F .РАСП() построим графики функции распределения и плотности вероятности, поясним применение этого распределения для целей математической статистики.

F-распределение (англ. F-distribution) применяется для целей дисперсионного анализа (ANOVA), при проверке гипотезы о равенстве дисперсий двух нормальных распределений (F-тест) и др.

Определение : Если U ₁ и U ₂ независимые случайные величины, имеющие ХИ2-распределение с k ₁ и k ₂степенями свободы соответственно, то распределение случайной величины:

носит название F -распределения с параметрами k ₁ и k ₂ .

Плотность F -распределения выражается формулой:

где Г(…) – гамма-функция:

если альфа – положительное целое, то Г( альфа )=( альфа -1)!

Приведем пример случайной величины, имеющей F -распределение.

Пусть имеется 2 нормальных распределения N(μ ₁ ;σ ₁ ) и N(μ ₂ ; σ ₂ ), из которых сделаны выборки размером n ₁ и n ₂ . Если s ₁ 2 и s ₂ 2 – дисперсии этих выборок , то отношение

имеет F -распределение. Это соотношение нам потребуется при проверке гипотезы о равенстве дисперсий двух нормальных распределений (F-тест) .

Графики функций

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

Примечание : Для построения функции распределения и плотности вероятности можно использовать диаграмму типа График или Точечная (со сглаженными линиями и без точек). Подробнее о построении диаграмм читайте статью Основные типы диаграмм .

F-распределение в MS EXCEL

В MS EXCEL, начиная с версии 2010, для F-распределения имеется специальная функция F.РАСП() , английское название – F.DIST(), которая позволяет вычислить плотность вероятности (см. формулу выше) и интегральную функцию распределения (вероятность, что случайная величина Х, имеющая F — распределение , примет значение меньше или равное х, P(X Примечание Плотность вероятности можно также вычислить впрямую, с помощью формул (см. файл примера ).

До MS EXCEL 2010 в EXCEL была функция FРАСП() , которая позволяет вычислить функцию распределения (точнее — правостороннюю вероятность, т.е. P(X>x)). Функция FРАСП() оставлена в MS EXCEL 2010 для совместимости. Аналогом FРАСП() является функция F.РАСП.ПХ() , появившаяся в MS EXCEL 2010.

Примеры расчетов приведены в файле примера на листе Функции .

В MS EXCEL имеется еще одна функция, использующая для расчетов F-распределение – это F.ТЕСТ(массив1;массив2) . Эта функция возвращает результат F-теста : двухстороннюю вероятность того, что разница между дисперсиями выборок «массив1» и «массив2» несущественна. Предполагается, что выборки делаются из нормального распределения .

Обратная функция F-распределения

Обратная функция используется для вычисления альфа — квантилей , т.е. для вычисления значений x при заданной вероятности альфа , причем х должен удовлетворять выражению P

Функция F.ОБР.ПХ() используется для вычисления верхнего квантиля . Т.е. если в качестве аргумента функции указан уровень значимости, например 0,05, то функция вернет такое значение случайной величины х, для которого P(X>x)=0,05. В качестве сравнения: функция F.ОБР() вернет такое значение случайной величины х, для которого P(X F.ОБР.ПХ() использовалась функция FРАСПОБР() .

Вышеуказанные функции можно взаимозаменять, т.к. следующие формулы возвращают одинаковый результат: =F.ОБР(0,05;k1;k2) =F.ОБР.ПХ(1-0,05;k1;k2) = FРАСПОБР (1-0,05;k1;k2)

СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .

Как выполнить двухвыборочный t-тест в Excel?

Пошаговые инструкции по запуску двухвыборочного t-теста в Excel

1. В Excel щелкните Анализ данных на вкладке Данные.
2. Во всплывающем окне «Анализ данных» выберите t-тест: две выборки, предполагающие равные отклонения.
3. В разделе «Ввод» выберите диапазоны для переменной 1 и переменной 2.

тогда как вы выполняете t-тест с двумя выборками в Google Sheets?

Что такое функция Tinv в Excel? Функция Excel TINV вычисляет обратное двустороннему T-распределению Стьюдента. Если вы хотите вычислить обратное одностороннему t-распределению, вы можете просто удвоить вероятность. Т.е. одностороннее обратное распределение Стьюдента T = TINV(2*вероятность, степени_свободы).

18.2 Двухвыборочный t-тест

Одна из наиболее часто встречающихся задач при анализе данных — это сравнение средних двух выборок. Для этого нам тоже понадобится t-тест, но теперь \(H_0\) нужно сформулировать по-другому: что две генеральные совокупности (из которых взяты соответствующие выборки) имеют одинаковое среднее. \

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

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

Зависимые тесты предполагают, что каждому значению в одной выборке мы можем поставить соответствующее значение из другой выборки. Обычно это повторные измерения какого-либо признака в разные моменты времени. В независимых тестах нет возможности сопоставить одно значение с другим. Мы уже не можем напрямую соотнести значения в двух выборках друг с другом, более того, размер двух выборок может быть разным!

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

Например, мы хотим исследовать влияние кофеина на скорость реакции. Можно поступить по-разному:

1. Набрать выборку, каждому испытуемому дать либо кофеин (например, в виде раствора небольшого количества кофеина в воде), либо обычную воду. Что именно получит испытуемый получит определяется случайным образом. Испытуемый не должен знать, что ему дают (слепое тестирование), а в идеале этого должен не знать даже экспериментатор, который дает напиток и измеряет показатели (двойное слепое тестирование). Посчитать скорость выполнения выбранной задачи, отправить домой. Это межинидивидуальный экспериментальный дизайн, для анализа результатов которого нам понадобится независимый t-тест.

2. Набрать выборку, каждому испытуемому дать и обычную воду, и воду с кофеином, записывать скорость решения задач после употребления простой воды и после употребления воды с кофеином, соответственно. В данном случае будет случайным образом варьироваться порядок предъявления: одни испытуемые сначала получат обычную воду, а потом воду с кофеином, другие испытуемые — наоборот. Для такого эксперимента понадобится меньше участников, но оно будет дольше для каждого участника. Более того, в этом случае мы учтем межиндивидуальные различия участников: одни участники в среднем решают задачи быстрее других. Это внутриинидивидуальный экспериментальный дизайн, для анализа результатов которого нам понадобится зависимый t-тест.

Внутрииндивидуальный план	Межиндивидуальный план
Зависимый t-тест	Независимый t-тест

Итак, с тем, когда использовать зависимый, а когда независимый t-тест, более-менее разобрались, давайте опробуем их!

Мастер функций

Проще всего производить вычисления данного показателя через Мастер функций.

1. Строим таблицу с двумя рядами переменных.

Кликаем по любой пустой ячейке. Жмем на кнопку «Вставить функцию» для вызова Мастера функций.

После того, как Мастер функций открылся. Ищем в списке значение ТТЕСТ или СТЬЮДЕНТ.ТЕСТ. Выделяем его и жмем на кнопку «OK».

Открывается окно аргументов. В полях «Массив1» и «Массив2» вводим координаты соответствующих двух рядов переменных. Это можно сделать, просто выделив курсором нужные ячейки.

В поле «Хвосты» вписываем значение «1», если будет производиться расчет методом одностороннего распределения, и «2» в случае двухстороннего распределения.

В поле «Тип» вводятся следующие значения:

• 1 – выборка состоит из зависимых величин;
• 2 – выборка состоит из независимых величин;
• 3 – выборка состоит из независимых величин с неравным отклонением.

Когда все данные заполнены, жмем на кнопку «OK».

Выполняется расчет, а результат выводится на экран в заранее выделенную ячейку.

Как получить и вставить текущую дату и время в ячейку Excel

Если ячейка, в которой была введена функция ТДАТА, имела формат Общий, он будет автоматически изменен в формат даты, и итоговое значение отобразится в виде записи даты и времени, например, 09.06.2019 19:14. Для получения числового значения как результата выполнения функции, необходимо установить числовой формат данных для требуемой ячейки. Например, в результате будет получено число 43625,80.

Функция ТДАТА удобна для вычислений разницы дат и времени, когда требуется динамически обновляемый результат. Например, некоторые расчеты были проведены в среду, книга Excel была сохранена и закрыта. При открытии книги в пятницу, результаты вычислений с использованием данной функции будут другими (актуальными для пятницы).

Примечание:

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

Пример 1. В таблице указаны даты некоторых событий. Рассчитать число дней, прошедших с момента события до сейчас.

Для расчета используем следующую формулу массива:

Поскольку ТДАТА возвращает дробное число (значение даты и времени), отбрасываем дробную часть с помощью функции ОТБР. Результат вычислений:

Для корректного отображения полученных данных необходимо выделить ячейки B2:B6 и установить Общий формат данных.

Для получения такого же результата можно использовать функцию для вставки только даты СЕГОДНЯ:

Примеры работы функции СЕГОДНЯ в Excel для вставки текущей даты.

Вообще функция =ТДАТА() чаще всего применяется в Excel для получения текущего времени:

Или используйте альтернативную формулу вставки текущего времени, не привязанную к формату ячеек:

Так как рассматриваемая функция возвращает дату и время ее можно встретить в разных формулах для обработки дат и времени.

t-тест: как выполнить в MS Excel?

Вне зависимости от того, для чего вам может понадобиться вычислить критерий Стьюдента, в Microsoft Excel есть функция TTEST, которая позволяет это сделать. Она доступна в надстройке Пакет анализа. Рассмотрим, как использовать функцию на примере Microsoft Excel 2013 в Windows 7.

Как выполнить t-тест в Excel 2013

Синтаксис функции следующий: ТТЕСТ(массив1;массив2;хвосты;тип). Скопируйте эту формулу в любое поле вашего документа Excel.

• Первый набор данных называется «массив1», а «массив2», соответственно, обозначает второй набор данных. В примере (см. изображение) «массив1» имеет значение «A2:A10».
• Переменная «хвосты» определяет тип распределения. Если в ячейке имеется значение «1», используется одностороннее распределение; если имеется значение «2», то используется двустороннее распределение.
• Переменная «тип» указывает, какой тип теста применять. Если значение равно «1», используется тест «Парный». Если выбрать «2», то будет использоваться тип «Двухпарный», если 3 — «Двухпарный с неравным отклонением».

• Как выполнять расчеты времени в Excel
• Как сделать сводную таблицу в Excel
• Excel: 10 формул для работы в офисе