Міністерство освіти і науки, молоді та спорту України. Использование электронной таблицы Excel для проведения расчетов и построения графиков
Использование электронной таблицы Excel для проведения расчетов и построения графиков. Графики и функции
Желательно иметь выход Интернет и файлы с данными типа zabol(номер).xls. При отсутствии данные придумываются или заранее готовятся преподавателем.
1. Построение графиков
Откроем сайт http: //www.1mgmu.com/ и перейдем в базу с данными по заболеваемости (находится под таблицей со ссылками) «МЕГАПРОЕКТ с фактическими данными о заболеваемости и средствами ее анализа. Его скромное начало: ВГА и ветрянка». Выберем вирусный гепатит А и нажмем на кнопку вывода и показа численностей:
Выберем данные по Белгородской области и вставим их в файл Excel:
Выполним команду Данные/текст по строкам, выберем вариант «с разделителем» и укажем, что разделителем является также точка с запятой:
В результате нам удалось «расцепить» данные по таблицам:
Вернемся на сайт и нажмем на вторую кнопку, для выбора случаев заболевания:
Выберем данные по заболеваемости в этой области и вставим их правее:
Добавим заголовки и уберем дублирующиеся столбцы:
Построим график динамики численности населения по годам:
Добавим надписи и подписи, сделаем красивые метки, поправим размеры
Добавим ряды для детей и взрослых. Поправим цвета и типы маркеров
Дублируем график, щелкнув в этикетку «лист1» и выполнив команду «переместить/скопировать» с выбором варианта «создать копию»:
В результате имеем:
Выделим щелчком линию «взрослые» и удалим ее нажатием на кнопку «delete»
Щелкнем правой кнопкой в график, выполним команду изменения типа графика и выберем вариант «столбик на столбике»:
Еще раз скопируем диаграмму на новый лист и выберем вариант столбиков, выровненный по высоте. Получим динамику доли детей и взрослых:
В параметрах диаграммы изменим зазор между столбиками, сделав его нулевым:
Поменяем свособы заливки
Поменяем фон и заливку в 2007 году, где произошло резкое изменение:
Обсудим, что это связано с изменением условий регистрации. Если до этого момента в категорию «дети» включали детей до 14, то с этого года – до 18. В исходную таблицу добавим строку «Суммарно» и просуммируем численности и число случаев заболевания:
По суммарному числу заболевших детей и взрослых построим круговую диаграмму:
При помощи изменения параметров диаграммы добавим число случаев заболеваний и название в качестве выноски и уберем из бокового названия:
Добавим название, подправим форматы и размеры
Изменим заливку. При помощи изменения параметров объемно фигуры «покрутим» диаграмму. «Растащим» куски
Рассчитаем интенсивную заболеваемость суммарно, для детей и взрослых на 1000 человек:
Раскопируем. Обратим внимание на то, что интенсивная заболеваемость для суммы не суммируется, а считается по тем же формулам:
Построим график заболеваемости для совокупного населения:
Аналогично делаем для графика заболеваемости детей и взрослых
Рассчитываем статистические погрешности для абсолютной заболеваемости
Если абсолютная заболеваемость более 50, то пользуемся приближенной формулой, что ее статпогрешность и плюс, и минус – 1, 96 на корень из абсолютной заболеваемости:
Копируем на другие ячейки
Удаляем рассчитанные данные для тех ячеек, для которых абсолютное число менее 50:
Рассчитываем точные границы для абсолютной заболеваемости ниже 50. Для этого на сайте 1mgmu.com выбираем расчет доверительных границ к биномиальному распределению. В качестве числа успехов берем число заболеваний, в качестве размеры группы ставим 1000. Нажимаем на «Посчитать»:
Копируем погрешности плюс и минус, УМНОЖЕННЫЕ НА 1000!!!!
Использованные ячейки для наглядности выделены желтым. Делаем то же для других пустых ячеек:
Переводим доверительные границы из абсолютных в интенсивные, аналогично умножив на 1000 и поделив на численность населения
Размножаем расчеты
Добавляем показ полученных величин погрешности на график (ЕСЛИ ЗАБЫЛИ. КАК ЭТО ДЕЛАЕТСЯ – В МОЕМ УЧЕБНИКЕ ПО СТАТИСТИКЕ В САМОМ КОНЦЕ, В ПРИЛОЖЕНИИ ПОДРОБНО, ПО ВСЕМ КАРТИНКАМ, УКАЗАНО, КАК ЭТО ДЕЛАЕТСЯ).
Далее говорим, что для графика с делением по взрослым и детям строится так же, но сейчас для экономии времени делать это не будем. А вот на самостоятельном задании – будем!
Теперь рассчитаем экстенсивную заболеваемость, то есть долю случаев заболевания детей среди всех случаев заболевания:
Переведем формат ячейки в процентный
Размножим
Построим график в виде столбиковой диаграммы:
Изменим формат чисел вертикального столбца. Щелкнем в него правой кнопкой и в типе данный выберем процентный с 0 знаков после запятой.
Рассчитаем доверительные границы для доли при помощи той же программы.
При этом в качестве числа наблюдений будем брать общее число случаев заболевания за данный год, а в качестве успехов – число случаев заболеваний у детей.
Копируем. Умножать на 1000 не надо:
Рассчитываем для других годов:
Используемая программа рассчитана на группы до 2000 случаев. Пи большем количества можно общее число наблюдений и число успехов поделить на 4, а статпогрешности умножить на 2. Добавляем и получаем:
Возвращаемся к базе данных. Выводим численность:
Выделяем и копируем все содержимое. Вставляем в другой лист:
Добавляем абсолютную заболеваемость по всем годам и областям:
Щелкаем в ячейку А1. Даем команду Данные/сортировка и сортируем по столбцу В в порядке возрастания:
Теперь можно анализировать данные за один год по разным областям.
Индивидуальные задания: Для каждого студента задается год, за который он анализирует данные. Выбираются данные по заболеваемости ветряной оспой. Берется 10 областей за данный год. Для них рассчитываются и строятся: Столбиковая диаграмма интенсивной заболеваемости с доверительными границами для детей и взрослых (как 2 ряда столбиков на одном графике), График (столбиковая диаграмма) доли случаев заболевания детей среди общего числа заболевших с доверительными границами. Міністерство освіти і науки, молоді та спорту України
|
