Обработка экспериментальных данных в ms excel
 Скачать 1.22 Mb.
|
|
3 ОБРАБОТКА ЭКСПЕРИМЕНТАЛЬНЫХ ДАННЫХ В MS EXCEL Хабаровск 2012 4 МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ Федеральное государственное бюджетное учреждение высшего профессионального образования «Тихоокеанский государственный университет» ОБРАБОТКА ЭКСПЕРИМЕНТАЛЬНЫХ ДАННЫХ В MS EXCEL Методические указания к выполнению лабораторных работ для студентов дневной формы обучения Хабаровск Издательство ТОГУ 2012 5 УДК 519.862.6:004.67(076) Обработка экспериментальных данных в MS Excel : методические указания к выполнению лабораторных работ для студентов дневной формы обучения / сост. Е. Г. Агапова, Е. А. Битехтина. – Хабаровск : Изд-во Тихоокеан. гос. ун- та, 2012. – 32 с. Методические указания содержат описание наиболее распространенных методов обработки результатов наблюдений, решений основных задач парного и множественного регрессионного анализа. Все методы снабжены подробными комментариями и рассматриваются на простых и конкретных примерах, что дает возможность использовать работу в качестве справочника по приведенным методам. Методические указания предназначены для студентов, обучающихся по направлению «Прикладная математика», и могут быть использованы студентами других направлений при изучении дисциплин «Обработка экспериментальных данных на ЭВМ», «Математическая статистика», «Эконометрика». Печатается в соответствии с решениями кафедры «Прикладная математика» и методического совета факультета компьютерных и фундаментальных наук. Тихоокеанский государственный университет, 2012 6 ОБРАБОТКА ЭКСПЕРИМЕНТАЛЬНЫХ ДАННЫХ В MS EXCEL Методические указания к выполнению лабораторных работ для студентов дневной формы обучения Агапова Елена Григорьевна Битехтина Екатерина Андреевна Главный редактор Л. А. Суевалова Редактор Л. С. Бакаева Подписано в печать Формат 60х84 1/16. Бумага писчая. Гарнитура «Таймс». Печать цифровая. Усл. печ. л. Тираж 100 экз. Заказ Издательство Тихоокеанского государственного университета. 680035, Хабаровск, ул. Тихоокеанская, 136. Отдел оперативной полиграфии издательства Тихоокеанского государственного университета. 680035, Хабаровск, ул. Тихоокеанская, 136. 7 ОБЩИЕ СВЕДЕНИЯ Методы обработки экспериментальных данных (ОЭД) начали разрабатываться более двух веков тому назад в связи с необходимостью решения практических задач по агробиологии, медицине, экономике, социологии. Полученные при этом результаты составили фундамент такой научной дисциплины, как математическая статистика. Первыми работами, положившими начало статистике как самостоятельной науке, были книги Дж. Граунта «Естественные и политические наблюдения, перечисленные в прилагаемом оглавлении и сделанные над бюллетенями смертности …» (1662 г.) и У. Пети «Два очерка по политической арифметике, относящиеся к людям, зданиям, больницам Лондона и Парижа» (1682 г.). Современный уровень ‒ естественнонаучного эксперимента характеризу- ется большими потоками информации. При этом визуальный просмотр данных, не говоря уже об анализе, невозможен без применения ЭВМ. Обработка результатов экспериментов предполагает знание основных понятий и методов теории вероятностей и математической статистики. Выявление характерных классов задач в ОЭД и стандартных методов их решения позволяет выделить обработку результатов экспериментов из многообразия задач прикладной статистики. Как правило, основным подходом в решении многих задач является метод наименьших квадратов (МНК) в его различных модификациях. Однако МНК эффективно работает только для линейных моделей, а на практике встречаются ситуации, когда связь искомого параметра с измеряемой величиной сугубо нелинейная. В этом случае применяют нелинейный МНК или другие методы обработки. Знакомство со всеми этими методами расширяет арсенал средств, находящихся в распоряжении обработчика, что особенно важно в сложных случаях, например, когда измерения производятся при воздействии большого числа факторов, мешающих их проведению. Появление электронных таблиц (табличных процессоров) привело к тому, что статистические методы, ранее доступные лишь узкому кругу математиков, стали использоваться широким кругом специалистов разных областей. Дальнейшее развитие программного обеспечения привело к созданию большого количества прикладных пакетов по статистике. Удобной универсальной вычислительной средой для решения задач ОЭД является табличный процессор MS Excel. Поэтому основной целью данных методических указаний являетсяизложение (в форме лабораторных работ) численных методик решения основных задач парного и множественного регрессионного анализа в вычислительной среде табличного процессора MS Excel. Жирным шрифтом в работе выделены команды меню, названия панелей инструментов и диалоговых окон и их элементов. 8 Тема 1. ОЦЕНКА ПОКАЗАТЕЛЕЙ КАЧЕСТВА ОБЪЕКТА ПО РЕЗУЛЬТАТАМ ЭКСПЕРИМЕНТОВ В этой теме рассмотрены основные задачи математической статистики о первичной статистической обработке данных. В статистике, как правило, статистические данные являются результатами наблюдений над некоторой случайной величиной X. Пример. В результате эксперимента были получены значения переменной X (табл. 1.1). Таблица 1.1 i 1 2 3 4 5 6 7 8 9 10 X 14,85 14,80 14,84 14,81 14,63 14,81 14,80 14,85 14,84 14,80 Лабораторная работа 1.1 СТАТИСТИЧЕСКИЕ ХАРАКТЕРИСТИКИ СЛУЧАЙНЫХ ВЕЛИЧИН Цель работы. Научиться вычислять оценки основных числовых характеристик по экспериментальным данным. Статистическая функция СЧЕТ используется для определения числа значений (рис. 1.1). Рис. 1.1. Диапазон ячеек указывается адресами первой и последней ячейки данных, записанными через двоеточие, например В6:В15 (рис. 1.2). Функция СРЗНАЧ рассчитывает среднее значение выборки. Функция СТАНДОТКЛОН – стандартное отклонение выборки. Аргументами этих функций служит все тот же диапазон ячеек. Статистическая функция СТЬЮДРАСПОБР используется для нахождения коэффициента Стьюдента. Этот коэффициент зависит от вероятности ошибки (при обычно задаваемой надежности 95 % вероятность ошибки составляет 5 %) и от числа степеней свободы n‒1. Также это значение можно найти по таблице 9 критических значений t-критерия (прил. 1). Рис. 1.2. Для нахождения доверительного интервала используется обычная формула умножения «=СТЬЮДРАСПОБР*Х ср ». Решение. Данные из примера введем в столбец B (табл. 1.2). В столбцах D и Е – подсказки характеристик, которые мы будем рассчитывать. Используя приведенные выше функции, в столбец F поместим результаты. По найденному значению ячейки F11 окончательный результат доверительного интервала можно записать так: с 95 %-ной надежностью Х = 14,81±0,046. В заключение вычислим относительную ошибку определения доверительного интервала: = ДИ/Х ср (формула: «=F11/F7»). Значение относительной ошибки обычно выражают в процентах, в нашем случае 0,3 %. Таблица 1.2 Лабораторная работа 1.2 ОЦЕНКА ПАРАМЕТРОВ И ОПРЕДЕЛЕНИЕ ЗАКОНА РАСПРЕДЕЛЕНИЯ Пример. Исследуется случайная величина ‒ число правонарушений в течение одних суток в некотором городе N. Получены данные за первые 150 10 суток года. 3 5 4 4 5 8 2 3 1 6 6 1 2 5 5 4 4 4 3 4 5 5 2 2 3 4 3 2 4 4 8 10 1 4 3 3 2 5 7 5 3 6 7 5 6 1 4 6 4 5 4 5 7 6 5 3 5 5 8 7 7 5 5 4 5 3 3 6 3 5 2 2 2 6 2 5 6 8 4 4 8 3 6 4 4 5 5 7 5 5 3 5 4 5 5 4 7 6 9 3 3 5 6 6 3 4 5 2 6 7 5 5 4 2 5 4 2 6 2 7 5 5 8 5 3 5 2 5 3 7 4 6 3 6 0 4 4 4 5 2 7 7 3 1 1 3 6 5 7 6 Цель работы. Требуется провести первичную статистическую обработку данных, проверить гипотезу о виде распределения случайной величины с помощью критерия согласия Пирсона. Расчетные соотношения. Данная задача решается с помощью статистических процедур Анализа данных истатистических функций библиотеки встроенных функций MS Excel. Приведем алгоритм решения задачи. 1. Ввод данных. В диапазон ячеек А1:АN ввести выборочные значения 2. Построение вариационного ряда. Скопировать содержимое ячеек А1:АN в ячейки В1:ВN. Упорядочить выборочные значения, используя кнопку сортировки по возрастанию. 3. Построение статистического ряда выборки. В ячейки С1:СК ввести k различных выборочных значений. В меню Данные выделить строку Анализ данных, выделить процедуру Гистограмма. В поле Входной интервал диалогового окна Гистограмма ввести ссылкуна диапазон А1:АN. В поле Интервал карманов ввести ссылку на диапазон С1:СК. Активизировать поле Выходной интервал и ввести в это поле ссылку – левая верхняя ячейка, в которую будет введена таблица результатов решений. Установить флажок Вывод графика. Составить табл. 1.3 статистического ряда по следующему образцу: Таблица 1.3 различные выборочные значения частота выборочного значения i x относительная частота выборочного значения i x накопленная относительная частота Первые столбцы заполнить копированием. Относительные и накопленные частоты вычислить с использованием формул. 4. Построение полигонов относительных и накопленных относительных частот. 11 Скопировать первый и третий столбцы табл. 1.3. Выделить их. Используя меню Вставка, применить к выделенным числам средство диаграммы Точечная. Полученныйграфик есть полигон относительных частот. Если эти же действия проделать с первым и четвертым столбцами табл. 1.3, то получим полигон накопленных частот ‒ сглаженный график эмпирической функции распределения. 5. Определение выборочных характеристик. В меню Данные выделить подменю Анализ данных, выделить процедуру Описательная статистика, в поле ввода Входной интервал ввести ссылку на диапазон ячеек, содержащий статистические данные А1:АN. Установить флажок Итоговая статистика. Активизировать поле Выходной интервал, ввести в это поле ссылку – левая верхняя ячейка, в которую будет введена таблица результатов решений. 6. Проверка гипотезы о виде распределения случайной величины с помощью критерия согласия Пирсона. Заполнить табл. 1.4. Таблица 1.4 различные выборочные значения частота выборочного значения i x теоретическая вероятность выборочного значения теоретическая частота выборочного значения Первые столбцы заполнить копированием, а оставшиеся ‒ вычисленными по формулам значениями. Если проверяется гипотеза о распределении Пуассона, то теоретические вероятности вычислить с помощью функции ПУАССОН . Здесь выборочное среднее, оно определяется в пункте 5, 0 – параметр, показывающий, что вычисляется вероятность того, что случайная величина, распределенная по закону Пуассона, принимает значение Если проверяется гипотеза о биномиальном распределении случайной величины, то теоретические вероятности вычислить с помощью функции БИНОМРАСП , при этом вероятность успеха в одном испытании определить по формуле где ‒ выборочное среднее. В случае других распределений воспользоваться справкой о статистических функциях библиотеки встроенных функций MS Excel. Значение является наблюдаемым значением случайной величины Число степеней свободы этой случайной величины равно при проверке гипотезы о распределении Пуассона и , если проверяется гипотеза о биномиальном распределении. Критическое значение случайной величины определить с помощью функции кр ХИ2ОБР , где ‒ уровень 12 значимости. Полученное наблюдаемое значение сравнить с кр Если кр , то гипотеза о виде распределения принимается при уровне значимости Если кр , то гипотеза отвергается с уровнем значимости Решение. По предложенному алгоритму проведем первичную статистическую обработку данных. Согласно пункту 3 алгоритма находим различных выборочных значений 0 1 2 3 4 5 6 7 8 9 10 С помощью пакета Анализ данных получаем статистический ряд выборки и его графическое представление (рис. 1.3). Рис. 1.3. Построенная гистограмма позволяет сделать предположение о виде распределения случайной величины X. В результате заполнения табл. 1.3 получим табл. 1.5, в третьем столбце которой, представлены относительные, а в четвертом – накопленные относительные частоты выборочных значений. Таблица 1.5 Согласно пункту 4 алгоритма получим полигоны относительных и накопленных частот (рис. 1.4, 1.5). 13 Рис. 1.4. Рис.1.5. Согласно пункту 5 алгоритма получаем выборочныехарактеристики (табл. 1.6). Таблица 1.6 Проверим гипотезу о распределении случайной величины по закону Пуассона. В качестве точечной оценки параметра распределения выбираем выборочное 0 0,05 0,1 0,15 0,2 0,25 0,3 0 1 2 3 4 5 6 7 8 9 10 Ряд1 0 0,2 0,4 0,6 0,8 1 1,2 0 1 2 3 4 5 6 7 8 9 10 Ряд1 14 среднее . В результате заполнения табл. 1.4 получим табл. 1.7. Наблюдаемое значение случайной величины . Оно получено суммированием чисел последнего столбца табл. 1.6. Критическое значение кр ХИ2ОБР , где Так как кр , то гипотеза о распределении по закону Пуассона при уровне значимости не противоречит опытным данным. Таблица 1.7 Тема 2. ЛИНЕЙНАЯ ПАРНАЯ РЕГРЕССИЯ Эта тема включает выполнение лабораторных работ, посвященных построению и исследованию уравнения линейной регрессии вида Построение линейной регрессии сводится к оценке ее параметров a, b. Классический подход к оцениванию параметров линейной регрессии основан на методе наименьших квадратов (МНК). Пример. Для определения зависимости между сменной добычей угля на одного рабочего (переменная Y, измеряемая в тоннах) и мощностью угольного пласта (переменная X, измеряемая в метрах) на 10 шахтах были проведены исследования, результаты которых представлены в табл. 2.1. Таблица 2.1 i 1 2 3 4 5 6 7 8 9 10 X 8 11 12 9 8 8 9 9 8 12 Y 5 10 10 7 5 6 6 5 6 8 15 Лабораторная работа 2.1 ВЫЧИСЛЕНИЕ КОЭФФИЦИЕНТОВ УРАВНЕНИЯ ЛИНЕЙНОЙ РЕГРЕССИИ Цель работы. Вычисление коэффициентов уравнения линейной регрессии по пространственной выборке табл. 2.1. Расчетные соотношения.Коэффициенты, определяемые на основе МНК, являются решением системы уравнений (2.2) Решая эту систему уравнений, получим где ‒ выборочная ковариация; – выборочное значение дисперсии величины x, определяемой по формуле: Решение. Вычислим значения, используя данные табл. 2.2: Таблица 2.2 A B C D E F G H I J K № п/п x y xy x 2 y 2 (у ‒ ) (у ‒ ) 2 y ^ i (y ‒ y^ i ) (y ‒ y^ i ) 2 1 8 5 40 64 25 ‒1,8 3,24 5,38 0,38 0,1429 2 8 5 40 64 25 ‒1,8 3,24 5,38 0,38 0,1429 3 8 6 48 64 36 ‒0,8 0,64 5,38 ‒0,62 0,3869 4 8 6 48 64 36 ‒0,8 0,64 5,38 ‒0,62 0,3869 5 9 7 63 81 49 0,2 0,04 6,39 ‒0,61 0,3672 6 9 6 54 81 36 ‒0,8 0,64 6,39 0,39 0,1552 7 9 5 45 81 25 ‒1,8 3,24 6,39 1,39 1,9432 8 11 10 110 121 100 3,2 10,24 8,43 ‒1,57 2,4775 9 12 10 120 144 100 3,2 10,24 9,44 ‒0,56 0,3114 10 12 8 96 144 64 1,2 1,44 9,44 1,44 2,0794 Сумма 94 68 664 908 496 0 33,6 68 0 8,3934 Среднее 9,4 6,8 66,4 90,8 49,6 Коэффициенты a, b вычислим по формулам (2.3), (2.4) соответственно: а само уравнение регрессии (2.1) примет вид 16 Лабораторная работа 2.2 ВЫЧИСЛЕНИЕ ВЫБОРОЧНОГО КОЭФФИЦИЕНТА КОРРЕЛЯЦИИ Цель работы. Вычисление выборочного коэффициента корреляции по пространственной выборке табл. 2.1. Расчетные соотношения.Выборочный коэффициент корреляции определяется соотношением , XY X Y x y x y r s s (2.7) где 2 2 ( ) X s x x ; 2 2 ( ) Y s y y ; 2 2 1 1 n i i y y n . (2.8) Решение. Используя вычисления лабораторной работы 2.1, данные табл. 2.2 и формулы (2.7), (2.8), получим: Величина говорит о сильной положительной линейной связи. Лабораторная работа 2.3 ПРОВЕРКА ЗНАЧИМОСТИ УРАВНЕНИЯ ЛИНЕЙНОЙ РЕГРЕССИИ ПО КРИТЕРИЮ ФИШЕРА Цель работы. По данным табл. 2.1 оценить при уровне = 0,05 значимость уравнения регрессии (2.6), построенного в лабораторной работе 2.1. Критерий Фишера (F-критерий). Уравнение парной регрессии значимо при уровне значимости , если выполняется следующее неравенство: Величины Q r , Q e являются факторной и остаточной суммами квадратов соответственно: Величина – табличное значение F-распределения с числами степеней свободы k 1 = 1 и k 2 = n – 2 квантиля уровня = 1 – (прил. 2). Эту вероятность можно также определить с помощью функции FРАСПОБР: 17 Значение F для линейной парной регрессии можно вычислить через коэффициент корреляции: Величина 2 xy r = R 2 называется коэффициентом детерминации и характеризует долю дисперсии результативного признака y , объясняемую регрессией, в общей дисперсии результативного признака: Соответственно величина 2 1 xy r характеризует долю дисперсии y , вызванную влиянием остальных, не учтенных в модели факторов. Решение. Вычислим значения Q e , r e Q Q Q и критерий F по данным табл. 2.2. В столбце I значения вычисляются по формуле (2.6). Итак, получены следующие значения: , По формуле (2.11) при k 1 = 1, k 2 = 8 или по таблице (прил. 2) вычисляем квантиль F 0.95; 1; 8 = 5,32. Неравенство (2.9) выполняется, т. е. 24,03 > 5,32, и поэтому уравнение регрессии (2.6) значимо. Лабораторная работа 2.4 ВЫЧИСЛЕНИЕ КОЭФФИЦИЕНТОВ УРАВНЕНИЯ ЛИНЕЙНОЙ РЕГРЕССИИ Цель работы. Вычисление коэффициентов уравнения линейной регрессии. В вычислительной среде табличного процессора MS Excel эта задача решается при помощи статистических функций НАКЛОН (наклон прямой относительно оси Х, коэффициент b) и ОТРЕЗОК (отрезок, отсекаемый прямой на оси Y, коэффициент a). Для знакомства с этими возможностями введем необходимые исходные данные (табл. 2.3). В столбцах В и С вводятся данные табл. 2.1, записи в столбце Е играют роль подсказок, столбец F заполняется по мере обработки. В ячейку F3 вводится функция НАКЛОН, в ячейку F4 ‒ ОТРЕЗОК. Обе эти функции имеют два аргумента: диапазон ячеек со значениями Y (С3:С12) и диапазон ячеек со значениями Х (В3:В12). Статистическая функция КВПИРСОН вычисляет значение коэффициента детерминации. 18 Таблица 2.3 Функция ЛИНЕЙН(изв_знач_у; изв_знач_х; константа; стат) вычисляет коэффициенты линейной регрессии, коэффициент детерминации R 2 , F-статистику. В поле «изв_знач_у» вводится диапазон значений Y (С3:С12); «изв_знач_х» – диапазон значений Х (В3:В12); константа устанавливается на 0, если заранее известно, что свободный член равен 0,и на 1 в противном случае; стат устанавливается на 0, если не нужен вывод дополнительных сведений регрессионного анализа, и на 1 в противном случае. Порядок использования функции ЛИНЕЙН: 1. Выделить область пустых ячеек 5 2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики и 1 2 для вывода только коэффициентов a, b. 2. Ввести функцию ЛИНЕЙН вручную или через Мастер функций. 3. После корректного ввода функции в левой верхней ячейке выделенной таблицы появится первый итоговый элемент таблицы. Чтобы раскрыть всю таблицу, следует сначала нажать клавишу F2, а затем одновременно нажать клавиши [Ctrl], [Shift], [Enter]. Далее появляется следующая регрессионная статистика, представленная в табл. 2.4. Таблица 2.4 Значение коэффициента b Значение коэффициента a Среднеквадратическое отклонение b Среднеквадратическое отклонение a Коэффициент детерминации R 2 Среднеквадратическое отклонение у F-статистика Число степеней свободы Регрессионная сумма квадратов Остаточная сумма квадратов Решение. В результате выполнения вышеуказанных действий получим табл. 2.5. Значения в табл. 2.5 совпадают со значениями, полученными в лабораторных работах 2.1, 2.3. 19 Таблица 2.5 Значение коэффициента b 1,0164 ‒2,754 Значение коэффициента a Среднеквадратическое отклонение b 0,2074 1,9759 Среднеквадратическое отклонение a Коэффициент детерминации R 2 0,7502 1,0243 Среднеквадратическое отклонение у F-статистика 24,025 8 Число степеней свободы Регрессионная сумма квадратов 25,207 8,3934 Остаточная сумма квадратов Лабораторная работа 2.5 АВТОКОРРЕЛЯЦИЯ ОСТАТКОВ. СТАТИСТИКА ДАРБИНА‒УОТСОНА Цель работы. Научиться пользоваться статистикой Дарбина‒Уотсона. Одна из предпосылок МНК – это независимость значений случайных отклонений от значений отклонений во всех других наблюдениях. Автокорреляция (последовательная корреляция) – это корреляция между наблюдаемыми показателями, упорядоченными во времени (временные ряды) или в пространстве (перекрестные ряды). Нам неизвестны истинные значения отклонений e i = y i –f(x i ), i = 1, …, n. Поэтому выводы об их независимости делаются на основе оценок e i , i = 1, …, n. При этом обычно проверяется некоррелированность только соседних величин. При наличии автокорреляции остатков полученное уравнение регрессии считается неудовлетворительным. Критерий Дарбина‒Уотсона – наиболее известный способ обнаружения автокорреляции первого порядка.Пусть n ‒ число наблюдений, k ‒ число факторов модели, уровень значимости α. Для n, k, α по таблицам распределения Дарбина‒Уотсона (прил. 3) находим числа d l и d u . Вычисляем статистику Дарбина‒Уотсона Здесь e i = y i ‒ (ax i + b) ‒ остатки, вычисленные по уравнению линейной регрессии y = a + bx для статистических данных (x i , y i ). Если DW < d l , то это свидетельствует о положительной автокорреляции остатков. Если DW > 4 ‒ d l , то это свидетельствует об отрицательной автокорреляции остатков. При d u < DW < 4 ‒ d u гипотеза об отсутствии автокорреляции остатков принимается. Если d l < DW < d u или 4 – d u < DW < 4 – d l , 20 то гипотеза об отсутствии автокорреляции остатков не может быть ни принята, ни отвергнута. Решение.Вернемсяк примеру. Определим наличие автокорреляции с помощью критерия Дарбина‒Уотсона. В столбце К табл. 2.2 получили Во второй столбец табл. 2.6введем данные столбца J табл. 2.2. Из каждого числа 2-го столбца вычитаем предыдущее число этого же столбца и результат введем в третий столбец Статистика Дарбина‒Уотсона равна Таблица 2.6 № п/п 1 0,38 ‒ ‒ 2 0,38 0 0 3 ‒0,62 ‒1,00 1 4 ‒0,62 0 0 5 ‒0,61 0,01 0 6 0,39 1 1 7 1,39 1 1 8 ‒1,57 ‒2,96 8,76 9 ‒0,56 1,01 1,02 10 1,44 2 4 Сумма 16,78 По таблице распределения Дарбина‒Уотсона (прил. 3) находим числа d l = 0,88 и d u = 1,32 при n = 10, k = 1. Так как d u < DW < 4 ‒ d u (1,32 < 2 < 2,68), то гипотеза об отсутствии автокорреляции остатков не отклоняется на уровне значимости 0,05. Это является одним из подтверждений высокого качества модели. |