Лабораторная работа Задание 1. Нахождение выборочных характеристик

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

Задача 1.1.

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

Таблица1. Выборка распределения значения роста у 101 мужчины

номер	Рост	номер	рост	номер	рост	номер		рост
M1	173	M27	174	M53	178,1	M79		185,7
M2	159,8	M28	165	M54	175,3	M80		183,2
M3	170,2	M29	181,3	M55	182,3	M81		175,3
M4	160,2	M30	185,5	M56	174	M82		193,4
M5	176,2	M31	174,9	M57	179,5	M83		182,9
M6	182,2	M32	192,8	M58	184,9	M84		162,3
M7	170,7	M33	163,1	M59	197,8	M85		168,8
M8	175,2	M34	171,1	M60	174,1	M86		175,2
M9	172,8	M35	201	M61	171,1	M87		172,8
M10	166	M36	183,1	M62	188	M88		174,9
M11	184,4	M37	192,7	M63	174,1	M89		177
M12	177,9	M38	163,3	M64	168,7	M90		198,1
M13	169,1	M39	168,5	M65	153,3	M91		176
M14	194	M40	168,3	M66	178,5	M92		184,3
M15	157,4	M41	183	M67	171,3	M93		172,6
M16	185,8	M42	168,7	M68	183	M94		192,1
M17	175	M43	184,1	M69	168,7	M95		174,4
M18	187,4	M44	192,7	M70	185,1	M96		147,4
M19	168,8	M45	161,9	M71	176	M97		168,4
M20	176	M46	185,5	M72	157,9	M98		176
M21	169,1	M47	168,9	M73	175,5	M99		169,8
M22	158	M48	174,9	M74	168,9	M100		174,1
M23	184,9	M49	185,6	M75	174,9	M101		184,2
M24	155	M50	173,7	M76	175,6
M25	163,4	M51	163,6	M77	173,7
M26	183,1	M52	185,7	M78	163,6

В первой колонке таблицы приведен номер обследуемого пациента, во второй – его рост. Требуется построить вариационный ряд, найти и сравнить выборочные характеристики ряда, полученные двумя способами:

• вычислением по формуле, определяющей соответствующую характеристику

• с использованием встроенной функции из библиотеки статистических функций EXCEL

Для выполнения задания данные наблюдений необходимо перенести на рабочий лист электронной таблицы EXCEL, разместив их в двух первых колонках

Таблица 2. Способы вычисления выборочных характеристик

№	Выборочная характеристика	Способ вычисления
		Расчет по формулам	С использованием функций EXCEL
1	Объем выборки	Количество данных (посчитать визуально)	СЧЕТ(диапазон данных)
2	Выборочное среднее		СРЗНАЧ(диапазон данных)
3	Выборочная дисперсия		ДИСПР(диапазон данных)
4	Стандартное отклонение		СТАНДОТКЛОНП(диапазон данных)
5	Минимальное значение выборки	СОРТИРОВКА с последующим указанием минимального значения	МИН(диапазон данных)
6	Максимальное значение выборки	СОРТИРОВКА с последующим указанием максимального значения	МАКС(диапазон данных)
7	Размах выборки	Разность между максимальным и минимальным значением	МАКС(диапазон данных) -МИН(диапазон данных)
8	Медиана ( )	СОРТИРОВКА с последующим указанием медианы	МЕДИАНА(диапазон данных)
9	Квартили ( )	СОРТИРОВКА с последующим указанием квартилей	КВАРТИЛЬ(диапазон данных, номер квартиля)
10	Стандартная ошибка среднего арифметического		Соответствующая функция отсутствует
11	Коэффициент вариации		Соответствующая функция отсутствует
12	Коэффициент асимметрии		СКОС(диапазон данных)
13	Коэффициент эксцесса		ЭКСЦЕСС(диапазон данных)

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

Пояснения к таблице 2:

При расчете по формулам объем выборки определяется количеством использованных строк в электронной таблице под данные, а при вычислении по формулам необходимо использовать функцию =СЧЕТ(диапазон данных). В данной задаче под диапазоном данных понимается набор адресов ячеек, которые содержат значения роста обследуемого контингента.

При расчете по формулам для вычисления среднего арифметического (выборочного среднего) используется формула ( ), где - варианта выборки(значение роста -го человека), - объем наблюдений. Для вычисления суммы необходимо использовать функцию =СУММ(диапазон данных), а значение берется из ячейки, где вычислен объем выборки. Другой способ вычисления среднего использует встроенную функцию СРЗНАЧ(диапазон данных)

При вычислении значения дисперсии по формулам необходимо использовать функции =СУММ(диапазон данных) и функцию =СЧЕТ(диапазон данных)). Следует обратить внимание, что вычисление суммы при нахождении дисперсии проводится по отношению к квадрату разности , который необходимо вычислить для значения каждой варианты , используя для этого формулы EXCEL и записывая данные вычислений в некоторый свободный столбец электронной таблицы. Поскольку значение одно и то же для всех значений , то при задании формулы, вычисляющей разности необходимо задать абсолютный адрес для ячейки, где хранится . Вычисление производится для одного индивида, а затем тиражируется на всех пациентов с использованием технологий, имеющихся в электронных таблицах.

Вычисление значения дисперсии с использованием библиотеки статистических функций производится с использованием функции =ДИСПР(диапазон данных)¹¹

Стандартное (средне-квадратичное) отклонение находится как корень из дисперсии с помощью функции =КОРЕНЬ(диапазон данных). В библиотеке EXCEL для вычисления указанной параметра имеется функция =СТАНДОТКЛОНП(диапазон данных)¹²

Для вычисления некоторых оценок рекомендуется использовать ранжирование. Для этого необходимо скопировать исходные данные роста в некоторый столбец (запрещается изменение исходных данных без копирования, так как это ведет к потере исходной информации), с последующей сортировкой значений в новом столбце. После сортировки минимальные и максимальные значения находятся на противоположных концах построенного ряда. Минимальное и максимальное значение можно также рассчитать используя встроенные функции =МИН(диапазон данных) и = МАКС(диапазон данных)

Первый способ вычисления медианы в нахождении в отсортированной последовательности варианты, выше и ниже которой, в столбце значений находится одинаковое количество вариант. Для вычисления медианы вторым способом необходимо использовать функцию =МЕДИАНА(диапазон данных)

Квартили – значения, отсекающие по 1/4 части вариационного ряда. Необходимо найти квартили для значений 0,25 и 0,75. Первый способ вычисления варианты для значения 0,25 заключается в нахождении в отсортированной последовательности варианты, выше которой в столбце значений находится четвертая часть значений ряда. Соответственно для вычисления 3-ей квартили необходимо найти значение варианты, выше которой находится ¾ значений вариационного ряда. Для вычисления значения квартили с использованием встроенной функции необходимо вызвать функцию =КВАРТИЛЬ(диапазон данных; номер квартиля)

Ниже приведена таблица соответствия номеров квартиля и значений функции

Номер квартиля	Результат
0	Минимальное значение варианты в заданном вариационном ряде
1	Значение варианты, отсекающей четверть вариационного ряда
2	Значение варианты, отсекающей половину вариационного ряда (совпадает с медианой)
3	Значение варианты, отсекающей три четверти вариационного ряда
4	Максимальное значение варианты в заданном вариационном ряде

Стандартную ошибку среднего арифметического и коэффициент вариации необходимо посчитать только с использованием формул.

При вычислении значения коэффициента асимметрии по формулам необходимо использовать функции =СУММ(диапазон данных) и - функцию =СЧЕТ(диапазон данных)). Следует обратить внимание, что вычисление суммы при нахождении дисперсии проводится по отношению к кубу разности , которое необходимо вычислить для значения каждой варианты , используя для этого формулы EXCEL и записывая данные вычислений в некоторый свободный столбец электронной таблицы. В библиотеке EXCEL имеется функция =СКОС(диапазон данных), которая вычисляет коэффициент асимметрии.

При вычислении значения коэффициента эксцесса по формулам необходимо использовать функции =СУММ(диапазон данных) и- функцию =СЧЕТ(диапазон данных)). Следует обратить внимание, что вычисление суммы при нахождении дисперсии проводится по отношению к четвертой степени разности , которое необходимо вычислить для значения каждой варианты , используя для этого формулы EXCEL и записывая данные вычислений в некоторый свободный столбец электронной таблицы. В библиотеке EXCEL имеется функция =ЭКСЦЕСС(диапазон данных), которая вычисляет коэффициент эксцесса.

Результаты вычислений необходимо представить в виде таблицы:

№	Характеристика	формулы	функции
1	Объем выборки
2	Среднее арифметическое
3	Дисперсия
4	Стандартное отклонение
5	Минимальное значение
6	Максимальное значение
7	Размах выборки
8	Медиана (0,5)
9	1-я квартиль (0,25)
10	3-я квартиль (0,75)
11	Стандартная ошибка среднего арифметического
12	Коэффициент вариации
13	Коэффициент асимметрии
14	Коэффициент эксцесса

Алгоритм выполнения задания 1 по шагам¹³:

1. Запустить программу EXCEL. Набрать с клавиатуры данные по значениям роста для выборки из 101 мужчин, или при наличии электронного варианта таблицы, выделить и скопировать исходные данные через буфер обмена в первый лист программы EXCEL, расположив их в двух первых столбцах (A и B) рабочего листа.

2. Скопировать (или набрать с клавиатуры) наименования вычисляемых параметров, расположив их в столбцах G и H, предварительно расширив столбец H. (столбцы C,D,E,F необходимы для проведения промежуточных вычислений)

3. Вычислить значение объема выборки, используя функцию =СЧЕТ(диапазон данных) (в качестве диапазона данных выбирается столбец В (значения роста)). С этой целью, установить курсор в ячейку J2, нажав на кнопку перейти в режим набора формул и выбрать строку «СЧЕТ» из выпадающего списка с наименованием формул. Далее необходимо указать диапазон исходных данных, который для данного параметра определяется данными столбца В.

4. В ячейке I3 вычислить значение среднего арифметического по формуле с использованием функции =СУММ(диапазон данных) и имеющегося объема выборки. Формула расчета параметра выглядит следующим образом: =СУММ(B1:B101)/ J2

5. Вычислить значение среднего арифметического по формуле СРЗНАЧ(диапазон данных). Сравнить результаты двух вычислений

6. Для расчета дисперсии в ячейке C1 вычислить значение формулы с последующим тиражированием построенной формулы на весь вариационный ряд (ячейки С1:С101). При вычислении формулы необходимо обратить внимание на адрес среднего арифметического. Этот адрес должен быть не относительным, а абсолютным (не должен меняться при копировании формул), и должен быть набран с использованием знака $. Окончательная формула для ячейки C1 должна выглядеть следующим образом: =(B1-J$2)*(B1-J$2).

7. Используя значения, полученные в шаге 6, в ячейке I4 вычислить значение дисперсии по формуле . Для этого, после тиражирования формулы =(B1-J$2)*(B1-J$2) на весь столбец С, вычисляется сумма значений по этому столбцу, которая делится на объем выборки.

8. Параллельно провести вычисление значения функции ДИСПР(диапазон данных).

9. В ячейке I5 вычислить значение стандартного отклонения как квадратный корень из дисперсии. Параллельно в ячейке J5 провести вычисления по формулам из статистической библиотеки.

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

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

12. Рассчитать стандартную ошибку среднего арифметического и коэффициент вариации, используя найденные значения среднего арифметического и стандартное отклонение.

13. Рассчитать в ячейке D1 значение формулы , протиражировать построенную формулу с использованием данных всего столбца В, и посчитать по формуле коэффициент асимметрии. Параллельно в ячейке J14 провести вычисления по формулам из статистической библиотеки.

14. Рассчитать в ячейке E1 значение формулы , протиражировать построенную формулу на данные всего столбца E и посчитать коэффициент эксцесса. Параллельно в ячейке J15 провести вычисления по формулам из статистической библиотеки.