«Статистическая обработка данных в электронных таблицах». Занятие № 2


ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ
ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ
«КРАСНОЯРСКИЙ ГОСУДАРСТВЕННЫЙ МЕДИЦИНСКИЙ УНИВЕРСИТЕТ
ИМЕНИ ПРОФЕССОРА В.Ф. ВОЙНО-ЯСЕНЕЦКОГО»
МИНИСТЕРСТВА ЗДРАВООХРАНЕНИЯ
РОССИЙСКОЙ ФЕДЕРАЦИИ
ФАРМАЦЕВТИЧЕСКИЙ КОЛЛЕДЖ
Специальность: 31.02.03 –Лабораторная диагностика
Квалификация: Медицинский технолог
МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ ДЛЯ ОБУЧАЮЩИХСЯ
К ПРАКТИЧЕСКОМУ ЗАНЯТИЮ ПО ДИСЦИПЛИНЕ «ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В ПРОФЕССИОНАЛЬНОЙ ДЕЯТЕЛЬНОСТИ»
Тема: «Статистическая обработка данных в электронных таблицах»
Занятие № 2
Клобертанц Е.П.
Красноярск
2016
Занятие № 2
Тема «Статистическая обработка данных в электронных таблицах»
Значение темы:
Пакет Excel оснащен средствами статистической обработки данных. И хотя Excel существенно уступает специализированным статистическим пакетам обработки данных, тем не менее, этот раздел математики представлен в Excel наиболее полно. В него включены основные, наиболее часто используемые статистические процедуры: средства описательной статистики, критерии различия, корреляционные и другие методы, позволяющие проводить необходимый статистический анализ экономических, психологических, педагогических и медико-биологических типов данных.
Умение работать в программе Microsoft Excel являются одним из необходимых умений, которые вам могут понадобиться в профессиональной деятельности.
Цели занятия: на основе теоретических знаний и практических умений обучающийся должен
знать:
осуществление ввода статистических функций: счет, мин, макс, квадроткл, дисп, коррел, медиана, мода.
уметь:
применять возможности Ms Excel для проведения статистического анализа методом описательной статистики.
овладеть ОК и ПК:
OK 5. Использовать информационно-коммуникационные технологии для совершенствования профессиональной деятельности.
ПК 1.3. Регистрировать результаты общеклинических исследований.
ПК 2.4. Регистрировать полученные результаты.
ПК 3.3. Регистрировать результаты лабораторных биохимических исследований.
ПК 4.3. Регистрировать результаты проведенных исследований.
ПК 5.3. Регистрировать результаты гистологических исследований.
ПК 6.4. Регистрировать результаты санитарно-гигиенических исследований.
ПК 7.5. Регистрировать результаты проведенных исследований.
ПК 8.2. Вести учетно-отчетную документацию.

План изучения темы:
Актуализация знаний
Форма: Устный опрос
Вопросы:
Какие виды связей вы знаете?
Какие Вам известны формы представления зависимостей между величинами?
Что такое математическая модель?
Что такое статистика?
Что такое регрессионная модель?
Для чего используется метод наименьших квадратов?
Краткое содержание темы
Корреляционный анализОдна из наиболее распространенных задач статистического исследования состоит в изучении связи между выборками. Обычно связь между выборками носит не функциональный, а вероятностный (или стохастический) характер. В этом случае нет строгой, однозначной зависимости между величинами. При изучении стохастических зависимостей различают корреляцию и регрессию.
Корреляционный анализ состоит в определении степени связи между двумя случайными величинами Xи Y. В качестве меры такой связи используется коэффициент корреляции. Коэффициент корреляции оценивается по выборке объема п связанных пар наблюдений (xi, yi) из совместной генеральной совокупности Xи Y.
Для оценки степени взаимосвязи величин Xи Y, измеренных в количественных шкалах, используется коэффициент линейной корреляции (коэффициент Пирсона), предполагающий, что выборки Xи Y распределены по нормальному закону.
Коэффициент корреляции — параметр, который характеризует степень линейной взаимосвязи между двумя выборками, рассчитывается по формуле:

Коэффициент корреляции изменяется от -1 (строгая обратная линейная зависимость) до 1 (строгая прямая пропорциональная зависимость). При значении 0 линейной зависимости между двумя выборками нет.
В MS Excel для вычисления парных коэффициентов линейной корреляции используется специальная формула:

или функция КОРРЕЛ (массив1; массив2),
где массив 1 – ссылка на диапазон ячеек первой выборки (X);
массив 2 – ссылка на диапазон ячеек второй выборки (Y).
Построение регрессионных моделей с помощью табличного процессора
Одной из чаще всего используемых возможностей Excel является экстраполяция данных – например, для анализа имеющихся фактических данных, оценки тенденции их изменения и получения на этой основе краткосрочного прогноза на будущее. В этом случае используется линейная экстраполяция данных на основе наименьшего квадратичного отклонения – отыскивается линейная зависимость данных, такая, которая бы минимизировала сумму квадратов разностей между имеющимися фактическими данными и соответствующими значениями на прямой линейного тренда (интерполяционной или экстраполяционной зависимости). На основе найденной зависимости можно сделать разумное предположение об ожидаемых будущих значениях изучаемого ряда данных.
Регрессионная модель – это функция, описывающая зависимость между количественными характеристиками сложных систем. Получение регрессионной модели происходит в два этапа:
подбор вида функции;
вычисление параметров функции.
Чаще всего выбор производится среди следующих функций:
y=ax+b – линейная функция;
y=ax2+bx+c – квадратичная функция;
y=aln(x)+b – логарифмическая функция;
y=aebx - экспоненциальная функция;
y=axb - степенная функция.
График регрессионной модели называется трендом (trend – направление, тенденция).
На графиках присутствует ещё одна величина, полученная в результате построения трендов. Она обозначена как R2. В статистике эта величина называется коэффициентом детерминированности. Именно она определяет, насколько удачной получится регрессионная модель. Коэффициент детерминированности всегда заключен в диапазоне от 0 до 1. Если он равен 1, то функция точно проходит через табличные значения, если 0, то выбранный вид регрессионной модели неудачен. Чем R2 ближе к 1, тем удачнее регрессионная модель.
Пакет анализа
В пакете Excel помимо мастера функций имеется набор более мощных инструментов для работы с несколькими выборками и углубленного анализа данных, называемый Пакет анализа, который может быть использован для решения задач статистической обработки выборочных данных.
Описательная статистика - это средство анализа служит для создания одномерного статистического отчета, содержащего информацию о центральной тенденции и изменчивости входных данных.
Корреляция - Корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине, т. е. большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция) или наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (нулевая корреляция). 
Гистограмма – графическое представление результатов обработки статистических данных. Совокупность данных разбивается на частичные интервалы, называемые нормальными. Интервалы разбиения могут быть любой ширины, но обязательно они должны следовать в порядке возрастания. Интервалы разбиения откладываются по оси абсцисс гистограммы. На оси ординат гистограммы откладывается число значений, попавших в интервал разбиения. Это число значений признака совокупности называется частотой.
Для установки пакета Анализа данных в Excel сделайте следующее:
- Вкладка Файл выберите команду Параметры Excel – Надстройки –Пакет анализа;
Для использования статистического пакета анализа данных необходимо:
Выбрать команду Данные – Анализ данных

в окне Анализ данных

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

Аналогично заполняется для корреляции и гистограммы.
Самостоятельная работа
Задание 1.Вычисление коэффициента корреляции
Откройте новую книгу MS Excel, выполните расчеты на Листе 1:
Определите, имеется ли взаимосвязь между рождаемостью и смертностью (количество на 1000 человек) в Красноярске:
Таблица 1
Годы Рождаемость Смертность
2006 9,3 12,5
2007 7,4 13,5
2008 6,6 17,4
2009 7,1 17,2
2010 7,0 15,9
2011 6,6 14,2
2012 7,1 16
2013 8,2 13,4
Постройте в Excel таблицу следующим образом и выполните указанные расчеты:
Годы Рождаемость Х Смертность У ХУ x2 y2 2006 9,3 12,5       2007 7,4 13,5       2008 6,6 17,4       2009 7,1 17,2       2010 7 15,9       2011 6,6 14,2       2012 7,1 16       2013 8,2 13,4       =СЧЕТ() Σx= Σy= Σxy= Σx2= Σy2= Коэффициент корреляции на формулах Коэффициент корреляции с помощью функции КОРРЕЛ     Задание 2. Построение модели линейной регрессии
На Лист 2 3239135403860скопируйте данные Таблицы 1 предыдущего задания.
Выполните расчеты коэффициентов а и b:
Для получения коэффициентов а и b линейного уравнения регрессии y=b*x+ a, описывающего зависимость между рождаемостью и смертностью воспользуемся статистической функцией ЛИНЕЙН.
Для этого выделите две ячейки C13:D13 и выполните вставку функции ЛИНЕЙН с аргументами. Здесь Известные_значения_y – диапазон значений Смертности,
Известные_значения_x – диапазон значений Рождаемости.
Нажмите комбинацию клавиш SHIFT+CTRL+ENTER.
В ячейку C14 введите уравнение регрессии y=b*x+ a, (вместо b и a подставьте полученные коэффициенты линейной регрессии).
3.Сделайте прогноз смертности при рождаемости 17 на 1000 человек.
Задание 3. Построение регрессионных моделей графическим методом
Работа выполняется на Листе 2.
Выделите диапазон ячеек В2:С9.
Построить точечную диаграмму (график);
Через контекстное меню ряда данных, выполните команду: Добавить линию тренда;
В открывшемся окне пункта «Параметры линии тренда» установите следующие значения:

Аналогично построить графики и другие типы трендов, не удаляя предыдущие результаты. Квадратичный тренд получается путем выбора полиномиального типа функции с указанием степени 2. На рабочем листе должно получиться 6 графиков.
Выберите тот график с трендом, который (на Ваш взгляд) наиболее приближается к экспериментальным данным, и выполните для этого графика следующие действия:
Получение экстраполяции. Выполнить команды: контекстное меню линии выбранного тренда команда Формат линии тренда в области Прогноз – строка Вперед на – установить 9 единиц.
Задание 4. Работа с пакетом анализа
Скопируйте исходную Таблицу 1 на Лист 3.
Определите, имеется ли взаимосвязь между рождаемостью и смертностью (количество на 1000 человек) в Красноярске с помощью Пакета анализа.
Сравните с данными, полученными в задании 1.
Воспользуйтесь инструментом Пакета анализа и оцените возможности раздела Описательная статистика
Итоговый контроль
Прием выполненных работ
Подведение итогов
Домашнее задание
[2] изучить стр. 39-40, вопросы для самоконтроля стр. 40
УЧЕБНО-МЕТОДИЧЕСКОЕ И ИНФОРМАЦИОННОЕ ОБЕСПЕЧЕНИЕ УЧЕБНОЙ ДИСЦИПЛИНЫОсновная литература
№ п/п Наименование, вид издания Автор(-ы),составитель(-и),редактор
(-ы) Место издания, издательство, год Кол-во экземпляров
В библиотеке На кафедре
1 2 3 4 5 6
1 Информационные технологии в профессиональной деятельности : учеб. пособие Е. В. Михеева М. : Академия, 2014. 150 Дополнительная литература
№ п/п Наименование, вид издания Автор(-ы),составитель(-и),редактор
(-ы) Место издания, издательство, год Кол-во экземпляров
В библиотеке На кафедре
1 2 3 4 5 6
2 Информационные технологии в профессиональной деятельности [Электронный ресурс] : сб. метод. указаний для обучающихся к внеаудитор. (самостоят.) работе по специальности 31.02.03 - Лабораторная диагностика (очная форма обучения). - Режим доступа: http://krasgmu.vmede.ru/index.php?page[common]=elib&cat=&res_id=44207 сост. Л. Ю. Позднякова, Е. П. Клобертанц, И. П. Клобертанц Красноярск : КрасГМУ, 2014. ЭБС КрасГМУ 3 Информационные технологии в профессиональной деятельности [Электронный ресурс] : сб. тестовых заданий с эталонами ответов для студентов 3 курса, обучающихся по специальностям 31.02.03 - Лабораторная диагностика, 33.02.01 - Фармация, 34.02.01 - Сестринское дело (очная форма обучения). - Режим доступа: http://krasgmu.vmede.ru/index.php?page[common]=elib&cat=&res_id=54657 сост. Е. П. Клобертанц, Л. Ю. Позднякова Красноярск : КрасГМУ, 2015. ЭБС КрасГМУ 4 Практикум по информационным технологиям в профессиональной деятельности : учеб. пособие Е. В. Михеева М. : Академия, 2014. 50 Электронные ресурсы:1. ЭБС КрасГМУ "Colibris";2. ЭБС Консультант студента;3. ЭБС iBooks;4. ЭНБ eLibrary

Приложенные файлы

  • docx file19
    «Статистическая обработка данных в электронных таблицах». Занятие № 2
    Размер файла: 688 kB Загрузок: 3