Обработка экспериментальных данных в ms excel
 Скачать 1.22 Mb.
|
|
Тема 3. НЕЛИНЕЙНАЯ ПАРНАЯ РЕГРЕССИЯ Эта тема включает выполнение лабораторных работ, посвященных построению уравнения нелинейной парной регрессии. 21 Пример. В табл. 3.1 приведены значения независимой переменной X (товарооборот, тыс. р.) и значения зависимой переменной Y (уровень издержек обращения по отношению к товарообороту, %). Таблица 3.1 X 7 10 15 20 30 45 60 120 Y 10 9 7,2 6 6,3 5,8 5,4 5 Лабораторная работа 3.1 ПОСТРОЕНИЕ НЕЛИНЕЙНОЙ РЕГРЕССИИ Цель работы. Построить уравнение нелинейной регрессии и вычислить индекс детерминации 2 R Расчетные соотношения. В случае нелинейных зависимостей с помощью некоторых преобразований можно перейти к линейным. Приведем некоторые из нелинейных функций и их модификацию к линейной функции – табл. 3.2. Таблица 3.2 № п/п Исходная спецификация Преобразование х х* Преобразо- вание у у* Вычисление b по b* Вычисле ние a по a* 1 y* = y b = b* a = a* 2 x* = x y* = 1 y b = b* a = a* 3 x* = 1 x y* = 1 y b = a* a = b* 4 y = ae b x + x* = x y* = lny b = b* a = e a* 5 y = a e b _ x + x* = 1 x y* = lny b = b* a = e a* 6 x* = e -x y* = 1 y b = b* a = a* 7 y = ax b e x* = lnx y* = lny b = b* a = e a* Решение.На рис. 3.1 изображены данные в виде диаграммы. Из этого рисунка видно, что зависимость между показателями нелинейная. Будем искать регрессионную зависимость в виде степенной: переходя к логарифмам 22 (модификация табл. 3.2), получим линейную зависимость y* = a* + bx*, где y* = lny, x* = lnx. Рис. 3.1. Определяем коэффициенты а*, b для модифицированных данных. Исходные данные и основные вычисления приведены в табл. 3.3. Таблица 3.3 № п/п x x* = lnx y* = lny x 2 y 2 (у ‒ ) 2 y ^ x (y ‒ y ^ x ) 2 1 7 1,95 2,30 3,79 5,30 10,00 9,12 0,77 2 10 2,30 2,20 5,30 4,83 4,68 8,37 0,40 3 15 2,71 1,97 7,33 3,90 0,13 7,59 0,15 4 20 3,00 1,79 8,97 3,21 0,70 7,08 1,17 5 30 3,40 1,84 11,57 3,39 0,29 6,42 0,02 6 45 3,81 1,76 14,49 3,09 1,08 5,82 0,00 7 60 4,09 1,69 16,76 2,84 2,07 5,43 0,00 8 120 4,79 1,61 22,92 2,59 3,38 4,60 0,16 Итого 26,04 15,16 91,14 29,15 22,32 - 2,67 Аналогично лабораторной работе 2.1 определим коэффициенты а* и b из соотношений (2.3), (2.4): а* 2,68; b 0,24. По величине потенциированием находим исходный коэффициент а: Таким образом, уравнение регрессии имеет вид: y = 14,58 x 0,24 . Изобразим эту кривую на диаграмме с исходными данными (рис. 3.2). Аналогично лабораторной работе 2.3 вычислим 0 2 4 6 8 10 12 0 20 40 60 80 100 120 Из д ерж ки , % Товарооборот, тыс. р. 23 Рис. 3.2. Согласно формуле (2.13) индекс детерминации равен Вычисленное значение говорит о том, что уравнением регрессии объясняется 88 % дисперсии результативного признака, а на долю прочих факторов приходится лишь 12 %. Лабораторная работа 3.2 ПОСТРОЕНИЕ НЕЛИНЕЙНОЙ РЕГРЕССИИ С ИСПОЛЬЗОВАНИЕМ КОМАНДЫ «ДОБАВИТЬ ЛИНИЮ ТРЕНДА» Цель работы. Построить уравнение нелинейной регрессии с использованием команды «Добавить линию тренда». Команда «Добавить линию тренда» используется для выделения тренда (медленных изменений) при анализе временных рядов. Однако эту команду можно использовать и для построения уравнения нелинейной регрессии, рассматривая в качестве времени t независимую переменную x. Эта команда позволяет построить следующие уравнения регрессии: линейная полиноминальная логарифмическая степенная экспоненциальная Для построения одной из перечисленных регрессий необходимо выполнить следующие шаги: 0 2 4 6 8 10 12 0 20 40 60 80 100 120 Из д ерж ки , % Товарооборот, тыс. р. 24 Шаг 1. Ввести по столбцам исходные данные (рис. 3.3). Шаг 2. По этим данным построить график в декартовой системе координат (рис. 3.3). Шаг 3. Установить курсор на любую точку построенного графика, сделать щелчок правой кнопкой и в появившемся контекстном меню выполнить команду Добавить линию тренда (рис. 3.3). Рис. 3.3. Шаг 4. В появившемся диалоговом окне выбрать нужное уравнение регрессии. Шаг 5. «Включить» необходимые опции: «Показать уравнение на диаграмме» ‒ на диаграмме будет показано выбранное уравнение регрессии с вычисленными коэффициентами; «Поместить на диаграмму величину достоверности аппроксимации (R^2)» ‒ на диаграмме будет показано значение коэффициента детерминации 2 R (для нелинейной регрессии ‒ индекс детерминации). Если по построенному уравнению регрессии необходимо выполнить прогноз, то нужно указать число периодов прогноза. Шаг 6. После задания всех перечисленных опций на диаграмме появится формула построенного уравнения регрессии и значение индекса детерминации 2 R Решение. По данным табл. 3.1 построить степенное уравнение регрессии После выполнения перечисленных шагов получим уравнение , для которого индекс детерминации равен R² = 0,877 (рис. 3.4). Такая величина говорит о хорошем соответствии построенного уравнения исходным данным. 25 x y 1 7 10 2 10 9 3 15 7,2 4 20 6 5 30 6,3 6 45 5,8 7 60 5,4 8 120 5 Рис. 3.4. Лабораторная работа 3.3 ВЗВЕШЕННЫЙ МЕТОД НАИМЕНЬШИХ КВАДРАТОВ Цель работы. Освоение применения взвешенного метода наименьших квадратов для коррекции гетероскедастичности остатков. Расчетные соотношения. После применения обычного МНК выясняется гетероскедастичность остатков: стандартное отклонение остатков линейно увеличивается при увеличении независимой переменной. Необходимо применить модификацию взвешенного МНК для коррекции такой гетероскедастичности. Решение.Построить точечную диаграмму исходных данных, поместить на нее линию тренда, его уравнение и индекс детерминации. 1. Cформировать массив остатков. 2. Модифицировать массив независимой переменной следующим образом: x i * = 1/х i 3. Модифицировать массив зависимой переменной следующим образом: y i * =y i /х i 4. По полученным данным модифицированной регрессии x i * , y i * построить диаграмму облака рассеяния, поместить на нее линию тренда с уравнением линии регрессии и индексом детерминации. Сравнить это уравнение с уравнением исходной регрессии. y = 14,578x ‒ R² 0 2 4 6 8 10 12 0 50 100 150 Ряд1 Степенная (Ряд1) Линия регрессии 26 Тема 4. ЛИНЕЙНАЯ МНОЖЕСТВЕННАЯ РЕГРЕССИЯ Эта тема включает выполнение лабораторных работ, посвященных построению и исследованию уравнения линейной множественной регрессии вида (4.1) Пример. Данные о сменной добыче угля на одного рабочего (переменная Y, измеряемая в тоннах), мощности пласта (переменная X 1 , измеряемая в метрах) и об уровне механизации работ в шахте (переменная X 2 , измеряемая в процентах), характеризующие процесс добычи угля в 10 шахтах, приведены в табл. 4.1. Таблица 4.1 Номер шахты i X 1 X 2 Y 1 8 5 5 2 11 8 10 3 12 8 10 4 9 5 7 5 8 7 5 6 8 8 6 7 9 6 6 8 9 4 5 9 8 5 6 10 12 7 8 Предполагая, что между переменными Y, X 1 , X 2 существует линейная зависимость, необходимо найти аналитическое выражение для этой зависимости, т. е. построить уравнение линейной регрессии. Лабораторная работа 4.1 ВЫЧИСЛЕНИЕ КОЭФФИЦИЕНТОВ ЛИНЕЙНОЙ МНОЖЕСТВЕННОЙ РЕГРЕССИИ Цель работы. Для пространственной выборки табл. 4.1 необходимо вычислить вектор коэффициентов уравнения регрессии (4.1). Расчетные соотношения. Вектор коэффициентов, найденный методом наименьших квадратов, является решением следующей системы уравнений: 27 где X ‒ матрица размерности 10 3 , первый столбец которой составлен из 1, а другие два столбца составлены из значений т. е. матрица X имеет структуру а Y ‒ вектор, составленный из 10 значений i y , т. е. Матрица имеет обратную матрицу и тогда вектор коэффициентов равен (4.2) Для реализации этой матричной формулы необходимо выполнить следующие операции: транспонирование; умножение матриц (частный случай – умножение матрицы на вектор); вычисление обратной матрицы. Все эти операции можно реализовать с помощью матричных функций MS Excel категории функций Ссылки и массивы. Для работы с этими функциями можно либо обратиться к Мастеру функцийи выбрать нужную категорию функций, затем указать имя функции и задать соответствующие диапазоны ячеек, либо непосредственно ввести с клавиатуры имя функции и задать соответствующие диапазоны ячеек. Функция ТРАНСП(диапазон ячеек)осуществляет транспонирование матрицы, где параметр диапазон ячеекзадает все элементы транспонируемой матрицы (или вектора). Функция МУМНОЖ(диапазон_1; диапазон_2) осуществляет умножение матриц в категории функций Математические.Параметр диапазон_1 задает элементы первой из перемножаемых матриц, а параметр диапазон_2 – элементы второй матрицы. При этом перемножаемые матрицы должны иметь соответствующие размерности (если первая матрица размерности , вторая ‒ , то результатом будет матрица размерности ). Функция МОБР(диапазон ячеек) осуществляет обращение матрицы (вычисление обратной матрицы) в категории функцийМатематические. Параметр диапазон ячеекзадает все элементы обращаемой матрицы, которая должна быть квадратной и невырожденной. При использовании этих функций необходимо соблюдать следующий порядок действий: 1. Выделить фрагмент ячеек, в которые будет занесен результат выполнения матричных функций (при этом надо учитывать размеры исходных матриц); 2. Ввести арифметическое выражение, содержащее обращение к матричным функциям MS Excel; 28 3. Одновременно нажать клавиши [Ctrl], [Shift], [Enter]. Если этого не сделать, то вычислится только один элемент результирующей матрицы или вектора. Решение. Сформируем матрицу X и вектор Y. Затем выполним формирование матрицы , вектора и вычисление вектора по формуле (4.2). В результате получим вектор коэффициентов и тогда уравнение регрессии (4.1) примет вид (4.3) Лабораторная работа 4.2 ВЫЧИСЛЕНИЕ КОЭФФИЦИЕНТОВ ЛИНЕЙНОЙ МНОЖЕСТВЕННОЙ РЕГРЕССИИ И ПРОВЕРКА ЗНАЧИМОСТИ В РЕЖИМЕ РЕГРЕССИЯ Цель работы. Используя режим Регрессия, вычислить вектор коэффициентов уравнения регрессии (4.1). Табличный процессор MS Excel содержит модульАнализ данных. Этот модуль позволяет выполнить статистический анализ выборочных данных (построение гистограмм, вычисление числовых характеристик и т. д.). Режим работы Регрессия этого модуля осуществляет вычисление коэффициентов линейной множественной регрессии с k переменными, построение доверительных интервалов и проверку значимости уравнения регрессии. После вызова режимаРегрессияна экране появляется диалоговое окно (рис. 4.1), в котором задаются следующие параметры: Рис. 4.1. 29 1. Входной интервал Y – вводится диапазон адресов ячеек, содержащих значения i y (ячейки должны составлять один столбец). 2. Входной интервал X – вводится диапазон адресов ячеек, содержащих значения независимых переменных. Значения каждой переменной представляются одним столбцом. Количество переменных ‒ не более 16. 3. Метки – включается, если первая строка во входном диапазоне содержит заголовок. В этом случае автоматически будут созданы стандартные названия. 4. Уровень надежности – при включении этого параметра задается надежность при построении доверительных интервалов. 5. Константа-ноль – при включении этого параметра коэффициент 6. Выходной интервал – при включении активизируется поле, в которое необходимо ввести адрес левой верхней ячейки выходного диапазона, который содержит ячейки с результатами вычислений режима Регрессия. 7. Новый рабочий лист – при включении этого параметра открывается новый лист, в который, начиная с ячейки А1, вставляются результаты работы режима Регрессия. 8. Новая рабочая книга ‒ при включении этого параметра открывается новая книга, на первом листе которой, начиная с ячейки А1, вставляются результаты работы режима Регрессия. 9. Остатки – при включении вычисляется столбец, содержащий невязки 10. Стандартизованные остатки – при включении вычисляется столбец, содержащий стандартизованные остатки. 11. График остатков – при включении выводятся точечные графики невязки в зависимости от значений переменных Количество графиков равно числу k переменных j x 12. График подбора – при включении выводятся точечные графики предсказанных по построенной регрессии значений от значений переменных Количество графиков равно числу k переменных Решение. Первоначально введем в столбец С десять значений первой переменной X 1 , в столбец D ‒ десять значений второй переменной X 2 , а в столбец F – десять значений зависимой переменной Y. После этого вызовем режим Регрессияи в диалоговом окне зададим необходимые параметры (рис. 4.1). Результаты работы приведены на рис. 4.2 – 4.3. Заметим, что из-за большой «ширины» таблиц, в которых выводятся результаты работы режимаРегрессия, часть результатов помещена в другие ячейки. Дадим краткую интерпретацию показателям, значения которых вычисляются в режимеРегрессия. Первоначально рассмотрим показатели, объединенные названием Регрессионная статистика (рис. 4.2). 30 Рис. 4.2. Множественный R ‒ корень квадратный из коэффициента детерминации. R-квадрат – коэффициент детерминации Нормированный R-квадрат – приведенный коэффициент детерминации Стандартная ошибка – оценка s для среднеквадратического отклонения σ. Наблюдения – число наблюдений n. Перейдем к показателям, объединенным названием Дисперсионный анализ (рис. 4.2). Столбец df ‒ число степеней свободы. Для строки Регрессия показатель равен числу независимых переменных для строки Остаток он равен для строки Итого Столбец SS – сумма квадратов отклонений. Для строки Регрессия показатель равен величине r Q (см. формулы (2.10)); для строки Остаток ‒ равен величине e Q (см. формулы (2.10)); для строки Итого r e Q Q Q Столбец MS дисперсии, вычисленные по формуле SS MS df , т. е. дисперсия на одну степень свободы. Столбец F – значение c F , равное F-критерию Фишера, вычисленному по формуле 31 Столбец Значимость F ‒ значение уровня значимости, соответствующее вычисленной величине F-критерия и равное вероятности где ‒ случайная величина, подчиняющаяся распределению Фишера с степенями свободы. Если вероятность меньше уровня значимости α (обычно α = 0,05), то построенная регрессия является значимой. Перейдем к следующей группе показателей, объединенных в табл. 4.2. Таблица 4.2 Коэффициенты Стандартная ошибка t-статистика Y‒пересечение ‒3,539 1,907 ‒1,8564 Переменная X 1 0,854 0,221 3,8726 Переменная X 2 0,367 0,243 1,5108 P-значение Нижние 95 % Верхние 95 % 0,1058 ‒8,0477 0,9690 0,0061 0,3325 1,3753 ‒0,1746 ‒0,2074 0,9415 Столбец Коэффициенты – вычисленные значения коэффициентов расположенных сверху вниз. Столбец Стандартная ошибка – значения вычисленные по формуле Столбец t-статистика – значения статистик Столбец Р-значение – содержит вероятности случайных событий где ‒ случайная величина, подчиняющаяся распределению Стьюдента с n ‒ k степенями свободы. Если эта вероятность меньше уровня значимости , то принимается гипотеза о значимости соответствующего коэффициента регрессии. Из табл. 4.2 видно, что значимым коэффициентом является только коэффициент при x 1 : b 1 = 0,854, так как Р-значение при x 1 равно 0,0061, что меньше α. Столбцы Нижние 95 % и Верхние 95 % ‒ соответственно нижние и верхние интервалы для оцениваемых коэффициентов Перейдем к следующей группе показателей, объединенных в табл. 4.3. Столбец Наблюдение – содержит номера наблюдений. Столбец Предсказанное У – значения , вычисленные по построенному уравнению регрессии. Столбец Остатки – значения невязок 32 Таблица 4.3 В заключение рассмотрения результатов работы режима Регрессия приведем график невязок ‒ остатков при заданных значениях только второй переменной (рис. 4.3). Наличие чередующихся положительных и отрицательных значений невязок является косвенным признаком отсутствия систематической ошибки (неучтенной независимой переменной) в построенном уравнении регрессии. Рис. 4.3. |