Линейная и экспоненциальная регрессии – ЧАСТЬ 1

В  арсенал  средств  Excel  включены  следующие  пять  функций  для  расчета  линейной регрессии:  ЛИНЕЙН  (LINEST), ТЕНДЕНЦИЯ  (TREND),  ПРЕДСКАЗ  (FORECAST), НАКЛОН (SLOPE) и СТОШУХ (STEYX), и две функции для расчета экспоненциальной регрессии: ЛГРФПРИБЛ (LОGEST) и РОСТ (GROWTH). Все эти функции оперируют массивами  данных  и  возвращают  также  массив  результатов.  Любую  из  них  можно использовать с одной или  несколькими независимыми переменными. Ниже приводится описание различных типов регрессии:

? Линейная   регрессия.   Определяет   угол   наклона   прямой,   наилучшим   образом представляющей  набор  данных.  Метод   линейной  регрессии  позволяет,  например, основываясь на данных о продажах за текущий год, рассчитать прогнозируемый объем продаж   за   март  следующего  года.  Кратко  говоря,  строится  график,  на   котором

по  оси  абсцисс  (X)  откладывается  время  (здесь  месяцы),  а  по  оси  ординат  (Y)  — объемы продаж, и зависимость последних от  времени  аппроксимируется некоторой кривой,  в  данном  случае  —  наклонной  прямой,  именно  поэтому  данный  вариант регрессии называется линейным. Исходя из предположения линейного роста объемов продаж, продолжив прямую во времени, можно составлять прогнозы на будущее.

? Экспоненциальная регрессия. Рассчитывает экспоненциальную  кривую, наилучшим образом  описывающую  множество  данных,  для  которых  не  выполняется  условие пропорционального  {линейного)  изменения  по  времени.  Например,  ряд  измерений роста населения всегда лучше описывается экспонентой, чем прямой линией.

? Многомерная    регрессия.    Выполняет    одновременный    анализ    для    нескольких совокупностей данных, что во многих случаях дает  более реалистичный результат. Excel  позволяет  применять  для  анализа  различных  данных  как  линейную,  так  и экспоненциальную  многомерную  регрессию.  Предположим,  что  требуется  оценить стоимость реконструкции дома в определенном районе, основываясь на данных о его жилой площади, количестве комнат, размере участка и возрасте постройки. С помощью многомерной регрессии можно решить эту задачу, имея на руках данные, собранные по существующим домам.

ПРИМЕЧАНИЕ  

Обычно с понятием «регрессия» связывают движение назад. Однако в  мире статистики регрессия часто используется, наоборот, для  предсказания будущих событий. Чтобы это понятие  лучше  уложилось  в  голове,  дадим  ему  такое  определение:  регрессия  —  это статистический метод, позволяющий найти математическое уравнение, которое наилучшим образом описывает набор данных.

Довольно  часто  сами  бизнесмены  пытаются  прогнозировать  будущие  объемы  продаж, опираясь  на  данные  о  продажах  за  прошедший  период.  Метод  процентовки,  например, учитывает активы и пассивы  предприятия, которые по-разному изменяются во времени, поэтому при  прогнозировании будущих продаж каждой из этих частей назначается  своя процентная   зависимость   от   времени.   Этот   простой   метод   способен   давать   вполне реалистичные прогнозы в случаях медленной динамики развития предприятия или динамики с постоянным значением прироста, но он быстро теряет точность при быстром и нелинейном росте продаж.

Регрессионный анализ  основывается на более  сложных уравнениях и  позволяет анализировать большие совокупности данных с построением  соответствующих кривых. Еще в недавние  времена  применение   регрессионного  анализа  ограничивалось  сложностью  и громоздкостью  вычислений.  С  появлением  программ  для  работы  с  электронными  таблицами, таких как Excel, для анализа данных стало возможным  использовать интегрированные в них функции, что значительно  упростило и популяризовало процедуру регрессионного анализа.

Линейная регрессия

Уравнение у = тх + b математически описывает прямую линию для  набора данных с одной  независимой  переменной,  где  х  —   независимая   переменная,  у  —  зависимая переменная,   т   —   угловой   коэффициент,   определяющий   наклон   линии,   а   b   — коэффициент,  определяющий точку  пересечения прямой с осью  Y. При многомерном регрессионном анализе линия регрессии представляет вклад

нескольких   независимых   переменных   в   моделируемый   результат.   В   этом   случае уравнение линейной регрессии перепишется в следующем виде: у = т1х1 + т2×2 + + тпхп

+  b,  где  у  —  по-прежнему  зависимая  переменная,  х1    …,  хп   —  это  п  независимых

переменных, т1,.., тп — коэффициенты при независимых переменных, а b —  константа.

Функция ЛИНЕЙН

Функция ЛИНЕЙН (LINEST) использует последнее, более общее  уравнение и методом наименьших   квадратов   рассчитывает   прямую   линию,   которая   наилучшим   образом согласовывалась бы с имеющимися данным — значениями независимых переменных х и соответствующими им значениями зависимой переменной у. Функция возвращает массив, в  который  входят  все  коэффициенты  т1     …,  тп    и   свободный  член  b,  полностью описывающие      рассчитанную       прямую,      и      имеет      следующие      аргументы: известные_значения_у;        известные_зпачения_х;        конст;         статистика,        где известные_значелшя_у — множество значений у,  которые уже определены ранее иным способом. Этот аргумент может  быть представлен одним столбцом, одной строкой или диапазоном ячеек. Если массив известные_значения_у записан в один столбец или одну строку,  то  соответственно  каждый  столбец  или  строка  массива  известные_зиачения_х интерпретируется    как    отдельная    независимая     переменная.    Если    же    аргумент известные_значеиия_у  представлен   в   виде  прямоугольного  диапазона,  в  уравнении участвует  только  одна  независимая  переменная.  В  таком  случае  диапазон,  задающий аргумент известные_значения_х, должен иметь те же размер и форму, что и диапазон со значениями переменной у.

Необязательный  аргумент  известные_значепия_х,  содержащий   значения   независимой переменной х, можно опускать. Тогда считается, что он представляет собой массив {1; 2; 3;…} такого же размера, как и массив известные_значения_у.

Необязательные аргументы конст и статистика являются логическими  константами и принимают значения ИСТИНА или ЛОЖЬ (допускается подстановка 1 вместо ИСТИНА и  0  вместо  ЛОЖЬ).  Если  аргумент   конст  определен  как  ИСТИНА  или  опущен, свободный член b вычисляется обычным образом, когда же аргумент принимает значение ЛОЖЬ, b полагается равным 0.

Аргумент статистика указывает, требуется ли вернуть дополнительную  статистику по регрессии.  Если  он  принимает  значение  ИСТИНА,   функция  ЛИНЕЙН  возвращает следующую дополнительную информацию:

seb … , sen                       Стандартные значения ошибок для коэффициентов

Seb                                         Стандартная ошибка для константы b r2                                              Коэффициент смешанной корреляции Sey                                         Стандартная ошибка для оценки у

F                               F-статистика

Df                                             Степени свободы

Ssreg                                      Регрессионная сумма квадратов

Ssresid                                   Остаточная сумма квадратов

Прежде чем создавать формулу с включением функции ЛИНЕЙН, необходимо выделить диапазон ячеек, достаточный для размещения  массива возвращаемых ею величин. Если аргумент статистика имеет  значение ЛОЖЬ или опущен, функция ЛИНЕЙН вернет только  коэффициенты т и постоянную b, то есть линейный массив (вектор), в  каждой ячейке которого содержится по одному коэффициенту т, а в последней ячейке массива — константа b. Если для этого аргумента задано значение ИСТИНА, возвращаемый массив будет иметь следующую структуру:

тп

тп 1

m2

т1

b

sen

sen-1

se2

se1

seb

r2                               Sey

F                    Df

Ssreg                       Ssresid

После  выделения  диапазона  для  возвращаемого  массива  данных  введите  функцию  и нажмите клавиши Ctrl+Shift+Enter, чтобы результат был представлен в виде массива. Обратите внимание, что коэффициенты и значения стандартных ошибок для независимых

переменных  возвращаются  в  обратном  порядке.  Например,  если  четыре  независимые

переменные   располагаются   в   четырех   разных   столбцах,   то    функция   ЛИНЕЙН рассматривает  крайний  левый  столбец  как  данные  для  переменной  хи   но  при  этом значение коэффициента т1 появится в крайнем правом столбце выходного массива.

На рис. 17.3 показан пример использования функции ЛИНЕЙН при  одной независимой переменной. Значения в столбце В представляют спрос продукции малого предприятия в месяц. Числа в столбце А — это  порядковые номера месяцев. Предположим, требуется построить     прямую     регрессии,    отображающую    зависимость    спроса     продукции предприятия при его развитии по месяцам, или, другими словами, математически описать набор     данных.     Для     этого     выделите     диапазон     F6:G6,    введите     формулу

=ЛИНЕЙН(В2:В19;А2:А19) и  нажмите  клавиши  Ctrl+Shift+Enter.  Число  в  ячейке  F6 (20,613)  показывает  угловой  коэффициент  прямой  регрессии,  а  число  в  ячейке  G6 (4002,065) — это точка пересечения линии регрессии с осью Y.

ПРИМЕЧАНИЕ

Функции ЛИНЕЙН и ЛГРФПРИБЛ возвращают только координаты по оси Y, которые затем и используются для построения прямых и кривых. Различие между ними заключается в том, что   функция   ЛИНЕЙН   аппроксимирует    набор   данных   прямой,   а   ЛГРФПРИБЛ   — экспоненциальной  кривой.  При  анализе  имеющихся  данных  вы  должны  принимать  эту разницу во внимание. Функция ЛИНЕЙН лучше подходит для расчетов продаж, а функция ЛГРФПРИБЛ  —  для  статистического  анализа  или  анализа  данных,  относящихся  к  численности  народонаселения.  Подробнее  функция  ЛГРФПРИБЛ  рассматривается  далее  в разделе «Функция ЛГРФПРИБЛ».

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий