Информатика Тема 9 леч
.pdfТема № 9. Электронные таблицы Microsoft Office Excel 2007: методы статистической обработки данных.
Цель занятия: изучить методы статистической обработки данных, определить основные статистические числовые характеристики предложенного распределения, выявить достоверность различий двух выборок, определить коэффициент корреляции.
Оборудование: компьютер под управлением ОС Windows, оснащенный офисным пакетом приложений Microsoft Office 2007, ручка, учебно-методические рекомендации к занятию, лекционная тетрадь.
Краткий теоретический курс по теме занятия:
Для получения опытных данных необходимо провести обследование соответствующих объектов. Совокупность, которая включает в себя все изучаемые объекты, называется генеральной. Генеральную совокупность образуют, например, все больные с данным диагнозом, все новорожденные дети и т.д. Общую сумму членов генеральной совокупности называют её объёмом и обозначают буквой N. Теоретически объем генеральной совокупности ничем не ограничен (N ). Поэтому обычно изучается какая-то часть объектов генеральной совокупности – выборка. Объем выборки будем обозначать буквой n.
Количественные признаки объектов выборки характеризуются числовым распределением. Числовые распределения могут существенно отличаться друг от друга. Они могут иметь различные средние значения случайной величины, или различаться рассеиванием данных наблюдений вокруг указанных значений. Поэтому для дальнейшего изучения изменения значений случайной величины какой-либо характеристики объекта используют числовые характеристики распределений или вариационных рядов. Их обычно называют статистиче-
скими характеристиками или оценками. Оценка может быть точечной или интервальной.
Точечной статистической оценкой называется выборочная характеристика, представляющая собой число, которая используется в качестве приближенного значения неизвестной генеральной характеристики.
Интервальной называют оценку, которая определяется двумя числами – концами интервала, покрывающего оцениваемый параметр.
Для описания числовых распределений (выборочных и генеральных) применяют следующие характеристики распределений: среднее значение, дисперсию, среднее квадратичное отклонение (или стандартное отклонение SD).
Одной из основных областей применения методов статистики в медицине является обоснование выводов, вытекающих из сравнения статистических показателей. Основным вопросом при этом является суждение о том, в какой мере обнаруженная при исследовании разность между двумя выборочными показателями (например: средними значениями) может свидетельствовать о существенном, неслучайном различии генеральных совокупностей. В области биометрии применяют два вида статистических критериев: параметрические, построенные на основании параметров данной совокупности и представляющие функции этих параметров, и непараметрические, представляющие собой функции, зависящие непосредственно от вариант данной совокупности с их частотами. Одним из параметрических методов является метод Стьюдента, который используют для сравнительной оценки средних величин.
Для описания связи между переменными величинами используют математическое понятие функции f, если каждому определенному значению независимой переменной х (аргументу) соответствует единственное значение зависимой переменной у (функции): у=f(х). В биологии и медицине чаще приходится иметь дело с иной связью между признаками, когда одной и той же величине соответствует ряд варьирующих значений другого признака, что обусловлено многообразием взаимодействия различных явлений живой природы. Такая связь называется корреляционной. Степень связи между явлениями оценивается с помощью коэффициента корреляции r и может быть слабой, умеренной, сильной, полной или отсутствовать.
В программе Microsoft Office Excel 2007 реализованы методы статистической обработки медикобиологических данных. При использовании Мастера функции имеется категория Статистические. В этой категории приведены функции проводящие вычисление статистических показателей (СРЗНАЧ – определение среднего значения, СТАНДОТКЛОН – определение стандартного отклонения, КОРРЕЛ - определение коэффициента линейной корреляции), и анализ по различным методам (ТТЕСТ – проведение сравнения выборок по методу Стьюдента).
Практические задания (с рекомендациями по выполнению):
Внимание!!! В процессе выполнения заданий на текущем занятии вам необходимо выполнять разные упражнения (обозначенные цифрами) в отдельных файлах (книгах). Для этого по окончании выполнения каждого упражнения необходимо сохранять файл под уникальным именем.
1. Проверка настройки новой рабочей книги
a)Создайте новую рабочую книгу.
b)Проверьте настройку среды Excel по умолчанию для рабочей книги:
c)кнопка Office → Параметры Excel → Основные (на вкладке обратите внимание на шрифт, размер, число листов) → Формулы (на вкладке: вычисления в книге – выбрать «автоматически», работа с формулами – «стиль ссылок R1C1» – НЕТ) → Правописание (на вкладке: язык словаря – «русский») → Сохранение (на
вкладке: расположение файлов по умолчанию – введите адрес – X:\Студенты\название папки Вашей группы\название Вашей личной папки Закройте окно Параметры Excel нажав кнопку OK.
d)Переименуйте Лист 1: введите новое имя Описательная статистика.
e)Сохраните файл под именем «Статистика+ваша фамилия».
2. Нахождение среднего значения, стандартного отклонения, проверка правила «трех сигм»:
a) Откройте файл под именем «Статистика+ваша фамилия», лист Описательная статистика. Введите дан-
ные: для чего вводим в ячейку А1 слово «Контроль», в ячейки А2:А8 вводим значения: 162, 156, 144, 137, 125, 145, 151; вводим в ячейку В1 слово «Исследование»; в ячейки В2:В8 вводим значения: 135, 126, 115,
140, 121, 112, 130.
b)Найдите среднее значение выборок: для чего установите курсор в ячейку А9; в строке формул нажмите кнопку Вставка функции ( f ), в появившемся окне Мастер функций выбрать категорию Статистические и функцию СРЗНАЧ, после чего нажать кнопку <ОК>; с помощью указателя мыши введите диапазон данных контрольной группы (А2:А8). ( Можно отодвинуть окно за серое поле при нажатой левой кнопке мыши), нажмите кнопку <ОК>, в ячейке А9 появится среднее значение выборки равное = 145,714.
c)Аналогично выполните нахождение среднего значения для исследуемой группы в ячейке В9. ( Среднее зна-
чение =125,571 ).
d)Найдите стандартное отклонение (среднее квадратичное отклонение – сигма) для чего Установите кур-
сор в ячейку А10; в строке формул нажмите кнопку Вставка функции ( f ), в появившемся окне Мастер функций выбрать категорию Статистические и функцию СТАНДОТКЛОН, после чего нажать кнопку <ОК>; с помощью указателя мыши введите диапазон данных контрольной группы ( А2:А8 ). ( Можно ото-
двинуть окно за серое поле при нажатой левой кнопке мыши ), нажмите кнопку <ОК>. В ячейке А10 поя-
вится стандартное отклонение выборки равное = 12,298
e)Аналогично выполните нахождение стандартного отклонения для исследуемой группы в ячейке В10. (СТАНДОТКЛОН =10,277 )
f)Проверьте правило «трёх сигм»: для чего Установите курсор в ячейку С9; Введите формулу : =А9-3*А10 Установите курсор в ячейку С10; Введите формулу : =А9+3*А10 Если численные значения данных выборки находятся в диапазоне, заданном числами в ячейках С9-С10, то правило «трёх сигм» выполняется.
g)Сохраните рабочую книгу.
3. Проверка достоверности различия средних двух выборок с помощью t-критерия Стьюдента:
a)Откройте файл под именем «Статистика+ваша фамилия».
b)Установите курсор в ячейку А11 Лист Описательная статистика; в строке формул нажмите кнопку
Вставка функции ( f ), в появившемся окне Мастер функций выбрать категорию Статистические и
функцию ТТЕСТ, после чего нажать кнопку <ОК>; С помощью указателя мыши в поле Массив1 введите диапазон данных контрольной группы (А2:А8 ) задания 3. ( Можно отодвинуть окно за серое поле при нажатой левой кнопке мыши ) С помощью указателя мыши в поле Массив2 введите диапазон данных иссле-
дуемой группы ( В2:В8 ) задания 3. ( Можно отодвинуть окно за серое поле при нажатой левой кнопке мы-
ши ) В поле Хвосты всегда вводится цифра 2 В поле Тип введите 3 ( группы разные), Нажмите кнопку <ОК>. В ячейке А11 появится значение 0,006295.
c)Сохранить данные в файле под названием «Стьюдент+ваша фамилия». Т.к. 0,006295<0,05 , то выборки достоверно отличаются друг от друга. Поэтому на основании критерия Стьюдента можно сделать вывод о большей эффективности нового препарата.
d)Закройте файл.
4. Выявление взаимосвязей. Коэффициент корреляции
a)Создайте новую книгу.
b)Переименуйте Лист 1: введите новое имя Корреляция.
c)Вводим в ячейку А1 Лист Корреляция слово «ЧСС», В ячейки А2:А8 вводим значения: 120, 84, 105, 92, 113, 90, 80 Вводим в ячейку В1 слово «ЧД», В ячейки В2:В8 вводим значения : 20, 15, 18, 16, 19, 16, 15. Установим курсор в ячейку А9; в строке формул нажмем кнопку Вставка функции ( f ); В появившемся окне Мастер функций выбрать категорию Статистические и функцию КОРРЕЛ, после чего нажать кнопку <ОК>; С помощью указателя мыши в поле Массив1 введите диапазон данных ЧСС ( А2; А8 ). С помощью указателя мыши в поле Массив2 введите диапазон данных ЧД ( В2; В8 ). Нажмите кнопку <ОК>. В ячейке А9 появится значение коэффициента корреляции равное = 0,995493.
d)Сохраните файл под именем «Корреляция+ваша фамилия».
Контрольные вопросы для письменного ответа:
1.Какие основные характеристики числового распределения используют в статистике?
2.Как активировать функцию ТТЕСТ?
3.Как активировать функцию КОРРЕЛ?
4.Как активировать функцию СТАНДОТКЛОН?
5.В чем отличие генеральной и выборочной совокупностей?