2018-01-08
487
В среде Excel получение параметров регрессионной зависимости и некоторых статистических оценок может быть выполнено с использованием встроенных функций. Основными из них можно считать ЛИНЕЙН, ЛГРФПРИБЛ, НАКЛОН, РОСТ, ПРОГНОЗ и др.
Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов с целью нахождения параметров прямой линии, которая наилучшим образом аппроксимирует имеющиеся данные. Результатом решения является совокупность ячеек (массив), в котором представлены коэффициенты a 0, a 1,…, a к, а также дополнительные параметры, характеризующие значимость полученных результатов, и называемые дополнительной регрессионной статистикой. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива.
Заметим, что для использования встроенных функций перед заданием параметров необходимо выделить блок ячеек, в которые будут переданы результаты расчетов (размерность должна быть равна 5* N) и после указания параметров необходимо нажать Cntr+Shft+Enter (для использования функции как формулы массива).
|
|
|
Уравнение для прямой линии имеет следующий вид:
Y теор = a 0 + a1X1 + a 2X 2 +...+ a k X k,,
где: Y теор - зависимое значение является функцией независимого значения Х.
а0, а1, а2, …,a k - коэффициенты, соответствующие каждой независимой переменной X k.
Для получения результатов применяется следующий синтаксис функции (способ обращения): ЛИНЕЙН(Известные значения Y;Известные значения X;Константа; Статистика).
Известные значения Y - это множество фактических значений Y, которые известны по условию задачи.
Известные значения Х - это необязательное множество значений Х, которые уже известны. Так как функция ЛИНЕЙН может быть использована для решения задач множественной регрессии, то массив Х может содержать одно или несколько множеств переменных.
Константа - это логическое значение, которое указывает, требуется ли, чтобы константа а0 в результате решения была равна 0.
· Если конст имеет значение ИСТИНА или опущено, то а0 вычисляется обычным образом.
· Если конст имеет значение ЛОЖЬ, то а 0. полагается равным 0 и значения a1, …, a к подбираются так, чтобы выполнялось соотношение
Y теор = a1X 1 + a 2X 2 +...+ a k X k..
Статистика - это логическое значение, которое указывает, требуется ли вернуть дополнительную информацию по регрессии.
Если статистика имеет значение ИСТИНА, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. При этом результаты решения (возвращаемый массив ячеек) будут иметь вид:
| аn | ак-1 | … | а1 | а0 |
| SEn; | SEn-1; | ...; | SE1; | SE0 |
| r2; | sey | |||
| F; | df | |||
| ssreg; | ssresid. |
|
|
|
Если статистика имеет значение ЛОЖЬ или опущена, то функция ЛИНЕЙН возвращает только коэффициенты а к и постоянную а 0.
Обозначения переменных, принятые в дополнительной регрессионной статистике представлены в таблице.
| Величина | Описание |
| SEn; SEn-1;...; SE1;SE0 | Стандартные значения ошибок для коэффициентов аn… а1 ; а0. |
| r2 | Коэффициент детерминации (детерминированности). |
| sey | Стандартная ошибка для оценки У. |
| F | F-статистика, или F-наблюдаемое значение. F-статистика используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет. |
| df | Число степеней свободы, необходимых для нахождения F-критических значений в статистической таблице или с использованием функции FРАСПОБР. Для определения уровня надежности модели нужно сравнить критическое значение с F-статистикой, получаемой в результате выполнения функции ЛИНЕЙН. |
| Регрессионная сумма квадратов, равная разности общей суммы квадратов (сумма квадратов разностей между фактическими значениями Yi и средним значением Yсред .) и остаточной суммы квадратов. |
| Остаточная сумма квадратов или сумма квадратов разностей между фактическими значениями Yi и теоретическими Yti, полученными из уравнения регрессии. |
Заметим, что общая сумма квадратов равна сумме регрессионной суммы квадратов и остаточной суммы квадратов, т.е sstotal = ssreg + ssresid, а коэффициент детерминации (детерминированности) есть отношение регрессионной и общей сумм квадратов:
Коэффициент детерминированности r2 (R2) всегда должен находиться в диапазоне от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различия между фактическим и оценочным значениями У.
Покажем на примере использование функции ЛИНЕЙН для решения задачи нахождения множественной линейной регрессии.
Предположим, что застройщик оценивает стоимость группы небольших офисных зданий в традиционном деловом районе.
Застройщик может использовать множественный регрессионный анализ для оценки стоимости офисного здания в заданном районе на основе следующих переменных. Введем обозначения переменных:
| Переменная | Смысл переменной |
| У | Оценочная цена здания под офисы |
| x1 | Общая площадь в квадратных метрах |
| x2 | Количество офисов |
| x3 | Количество входов |
| x4 | Время эксплуатации здания в годах |
В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (x1, x2, x3 и x4) и зависимой переменной (Y), то есть стоимостью здания под офис в данном районе.
Для решения задачи застройщиком были получены данные по одиннадцати зданиям из имеющихся 1500, представленные в таблице.
| №№пп | Общая площадь (кв. м.) Х1 | Кол-во офисов Х2 | Кол- во входов Х3 | Срок эксплуатации (лет) Х4 | Стоимость (тыс. у. е.) Y |
| 1,5[2] | |||||
| 1,5 | |||||
При вводе в качестве массива в среде Excel формулы ={ЛИНЕЙН(E2:E12;A2:D12;ИСТИНА;ИСТИНА)} были получены следующие результаты:
| а4 | -0,206 | а3 | 3,02 | а2 | 13,21 | а1= | 0,011 | а0= | 88,2 |
| SE4; | 0,045 | SE3; | 1,88 | SE2= | 1,44 | SE1= | 0,02 | SE0= | 43,4 |
| r2= | 0,960 | sey | 3,49 | #Н/Д | #Н/Д | #Н/Д | |||
| F= | 36,506 | df= | #Н/Д | #Н/Д | #Н/Д | ||||
| Ssreg= | 1779,4 | Ssresid= | 73,11 | #Н/Д | #Н/Д | #Н/Д | |||
| Sstotal = ssreg + ssresid | 1852,51 |
Уравнение множественной регрессии Y = a0 + a1Xi1 + a2X2 +...+ ak X k теперь может быть получено из строки 1:
Y = 88,2 + 0,011X i1 + 13,21X 2 + 3,02 X 3 - 0,206 X 4
Теперь арендатор может сделать прогнозы относительно стоимости зданий в данном районе. Например, можно определить оценочную стоимость здания под офисы в том же районе, которое имеет площадь 2500 квадратных метров, три офиса, два входа, зданию 25 лет, используя следующее уравнение:
|
|
|
Y = 88,2 + 0,011×2500 + 13,21×3 + 3,02×2 - 0,206×25 = 156,22 тыс. у.е.
Для оценки значимости параметров уравнения множественной регрессии воспользуемся оценками на основе анализа коэффициента детерминации (детерминированности) r2 и F -критерия Фишера.
Коэффициент детерминированности r2 равен 0,960, что указывает на весьма высокую зависимость между независимыми переменными и стоимостью здания. Использование F-критерия позволит определить, является ли полученный результат значимым.
Предположим, что на самом деле нет взаимосвязи между переменными, а просто были выбраны редкие 11 образцов зданий, для которых статистический анализ вывел сильную взаимозависимость.
Для нахождения Fкрит воспользуемся функцией FРАСПОБР(0,05;4;6), равная 4,533677, т.е. Fкрит =4,533677.
Если F-расчетное больше, чем F-критическое, то взаимосвязь между переменными является значимой. В нашем случае расчетное F-значение равно 35,509 (по данным функции ЛИНЕЙН), что заметно больше чем F-критическое значение, равное 4,53. Следовательно, полученное уравнение регрессии может быть использовано для прогнозов оценочной стоимости зданий в данном районе.
Проведем оценку значимости коэффициентов регрессии на примере анализа коэффициентов регрессии. Для этого в соответствии с изложенной выше методикой рассчитаем t -критерий Стьюдента:
.
Полученные коэффициенты и сведем данные в таблицу.
| Переменная | Обозначения | t-наблюдаемое (рассчитанное) значение |
| Общая площадь | ta1 | 5,1 |
| Количество офисов | ta2 | 31,3 |
| Количество входов | ta3 | 4,8 |
| Срок эксплуатации | ta4 | 17,7 |
Для оценки статистической значимости необходимо рассчитать критическое значение tкрит, которое для a=0,05 (при доверительной вероятности 0,95) и числа степеней свободы (11-5)=6 имеет значение 1,94.
Так как для всех факторов t- наблюдаемые (рассчитанные) значения превышают критическое значение tкрит, равное 1,94; следовательно, все переменные, использованные в уравнении регрессии, значимы и могут быть использованы для предсказания оценочной стоимости здания под офис в данном районе.
|
|
|
Для расчета коэффициента корреляции в Excel может быть использована функция КОРРЕЛ. Она позволяет рассчитать коэффициент корреляции в условиях парной корреляции и таким образом сделать вывод о наличии или отсутствии взаимосвязи между двумя признаками.
Для обращения к функции необходимо задать параметры двух массивов, характеризующих два изучаемых признака, взаимосвязь между которыми предполагается определить.
КОРРЕЛ(массив1; массив2).
Контрольный пример
Имеются данные о среднем возрасте оборудования предприятия и средних затратах на его ремонт.
| Номер предприятия | Возраст оборудования (лет) | Затраты на ремонт (тыс. руб.) |
| 1,5 | ||
| 2,0 | ||
| 1,4 | ||
| 2,3 | ||
| 2,7 | ||
| 4,0 | ||
| 2,3 | ||
| 6,6 | ||
| 1,7 |
В целях нормирования расхода средств на ремонт оборудования произвести анализ тесноты связи между возрастом оборудование и затратами на его ремонт. Рассчитать уравнение регрессии между ними.
Расчетная часть
Решение задачи выполним в следующей последовательности.
1. Определение степени тесноты связи и адекватности модели путем построения линий тренда на графике в среде MS Excel.
2. Проведение расчетов с использованием стандартных статистических функций MS Excel (ЛИНЕЙН и другие).
3. Построение линий тренда на графике в среде MS Excel.
1. При построении линий тренда в использованием MS Excel предварительно необходимо выявить результирующий и факторный признаки. Результативным признаком Y модели являются затраты на ремонт, а факторным - возраст оборудования X. Для исключения ошибок следующим шагом является ранжирование исходных данных по факторному признаку (Меню Данные, Сортировка, По возрастанию).
| Номер предприятия | Возраст оборудования (лет) | Затраты на ремонт (тыс. руб.) |
| 1,5 | ||
| 1,4 | ||
| 2,3 | ||
| 1,7 | ||
| 2,7 | ||
| 2,3 | ||
| 6,6 |
2. Построение корреляционного поля.
 |
Анализ корреляционного поля позволяет предположить о наличии прямой связи между возрастом оборудования и затратами на ремонт.
4. Построение линий тренда на графике (диаграмме).
В результате построения трендов получены следующие модели:
А) прямолинейная функция: Y1 = 0,4767x + 0,3389, характеризующаяся коэффициентом детерминации r2 = 0,6243. При этом коэффициент корреляции, рассчитанный по функции КОРРЕЛ, равен r = 0,886636;
В) полиномиальная функция Y2 = 0,1301x2 - 0,8242x + 2,7238 с коэффициентом детерминации r2 = 0,863.
Анализ модели, представленный в виде прямолинейной функции, приводит к следующим выводам.
Параметр, характеризующий угол наклона линейной модели, равен 0,4767. Это означает, что в среднем по совокупности наблюдений отклонение возраста оборудования от его среднего значения на 1 год приводит к возрастанию затрат на ремонт на 0,4767 тыс. руб.
Линейный коэффициент корреляции равен 0,886636, что по шкале Чеддока подтверждает наличие высокой тесноты связи между возрастом оборудования и затратами на ремонт.
Коэффициент детерминации R2, полученный при построении тренда, равен 0,6243 [3]. Это означает, что 62% затрат на ремонт связаны именно с изменением возраста оборудования, а оставшиеся 38% определяются другими, не учтенными в данной постановке, факторами.
Т.к. изучаемая совокупность меньше 30, то целесообразно провести проверку значимости коэффициента корреляции и параметров линейной модели.
Т-критерий Стьюдента, рассчитанный по формуле (7.4), t r= 5,072387.
Критическое (табличное) значение в результате расчетов с использованием функции Excel СТЬЮДРАСПОБР(0,05;7) равно tкрит = 2,364624.
Так как tr > tкрит, то коэффициент корреляции может быть признан значимым и связь между Y и Х является реальной.
Для подтверждения гипотезы воспользуемся оценкой значимости коэффициентов линейной регрессии, что является необходимым применительно к совокупностям, число наблюдений в которых не превышает 30. Расчетные (фактические) значения t -критерия Стьюдента определяются для каждого из параметров модели.
Для параметра а0: 
Для параметра а1: 
где 
- значение затрат, вычисленное по модели (выравненных значений),
- среднее квадратическое отклонение результативного признака от выравненных значений,
- среднее квадратическое отклонение факторного признака х от общей средней.
Расчеты представим в виде таблицы.
| Исходные данные | Расчетные данные | |||
| Номер предприятия | Возраст оборудования (лет) | Затраты на ремонт (тыс. руб.) | YЛинейная = = 0,4767x + 0,3389 | Yполиномиальная = = 0,1301x2 - 0,8242x + 2,7238 |
| 1,5 | 0,8 | 2,0 | ||
| 2,0 | 1,3 | 1,6 | ||
| 1,4 | 1,8 | 1,4 | ||
| 2,3 | 2,2 | 1,5 | ||
| 1,7 | 4,6 | 5,8 | ||
| 2,7 | 2,7 | 1,9 | ||
| 2,3 | 3,7 | 3,3 | ||
| 4,0 | 3,2 | 2,5 | ||
| 6,6 | 4,2 | 4,5 | ||
| Всего затрат | 24,5 | 24,5 | 24,5 |
Значения t -критерия Стьюдента представим в таблице.
| ta0= | 0,630295 |
| ta1= | 2,289138 |
| tкрит= | 2,364624 |
Анализ представленных результатов показывает, что для линейной модели расчетные (фактические). Таким образом, можно предположить, что полученная модель не в полной мере адекватна фактическим (экспериментальным) данным и зависимость между возрастом оборудования и затратами на ремонт определяется неучтенными случайными факторами. Проверка значимости параметров полиномиальной модели проводится аналогично.
При использовании функции ЛИНЕЙН получены следующие результаты.
| а1= | 0,61087 | а0= | -1,55386 |
| SE1= | 0,12043 | SE0= | 0,88589 |
| r2= | 0,78612 | Sey= | 0,8168 |
| F= | 25,7291 | df= | |
| Ssreg= | 17,1654 | Ssresid= | 4,67012 |
Полученные результаты свидетельствуют о соответствии числового значения коэффициента детерминации по функциям ЛИНЕЙН и КОРРЕЛ. Рассчитанные значения t -критерия Стьюдента для параметров модели имеют значения:
| ta0= | 1,75401 |
| ta1= | 5,07241 |
| tкрит= | 2,36462 |
Их сравнение с критическим (табличным) значением t -критерия Стьюдента позволяют утверждать о значимости только коэффициента a1, характеризующего угол наклона прямой линии. Для уточнения выводов воспользуемся распределением Фишера.
Критическое значение рассчитаем с использованием функции FРАСПОБР, которое имеет значение 5,3176. В нашем случае расчетное F-значение по данным функции ЛИНЕЙН равно 35,509, что заметно больше чем F-критическое значение, равное 5,3176. Таким образом, полученное уравнение регрессии в виде Y =0,61086 X -1,55386 может быть использовано для оценки затрат, необходимых для ремонта оборудования в связи с его амортизацией.
