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

Рис. 17.3. Функция ЛИНЕЙН вычисляет угловой коэффициент прямой зависимости и точку пересечения линии регрессии с осью Y

Пример регрессионного анализа

Одну из популярных моделей регрессионного анализа многие называют сравнительным анализом рынка (Competitive Market Analysis — СМА). Агенты по продаже недвижимости используют этот метод для получения оценочной стоимости домов на основе данных о проведенных ранее продажах примерно таких же зданий в том же самом регионе. На приведенном ниже рисунке показан пример построения этой модели средствами Excel; документ мы назвали Оценщиком недвижимости.

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

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

В нормальном состоянии этой рабочей книги скрыты заголовки строк и столбцов и строки с 25 по 37, включена защита рабочего листа, а также защищены все ячейки, кроме тех, в которые нужно вводить исходные данные. И наконец, действительно полезный совет: как сказано в примечании к документу, он позволяет получить всего лишь оценочную стоимость, и если вам удастся узнать действительную цену недвижимости, она, несомненно, будет более реалистичной.

Функция ТЕНДЕЦИЯ

Если функция ЛИНЕЙН возвращает параметры прямой линии, аппроксимирующей имеющиеся данные, то функция ТЕНДЕНЦИЯ (TREND) позволяет определить точки, лежащие на этой прямой. Массив чисел, выдаваемых функцией ТЕНДЕЦИЯ, можно использовать для построения линии тренда — прямой, помогающей оценить и понять поведение фактических данных. Кроме того, эта функция позволяет экстраполировать данные, то есть строить прогноз будущих значений на основе тенденции отклонений, выявленной для существующих данных. (Нужно учесть то, что функция ТЕНДЕНЦИЯ лишь выражает исходные данные прямой линией, но она ничего не говорит о том, насколько хорошо эта линия будет предсказывать будущие значения. Сделать выводы на этот счет поможет статистика, возвращаемая функцией ЛИНЕЙН.)

Функция ТЕНДЕНЦИЯ имеет четыре аргумента: известные_значения_у; известные_ значения_x, новые_значепия_x, конст. Первые два являются известными значениями зависимых и независимых переменных соответственно. Как и в случае с функцией ЛИНЕЙН, аргумент известные_зпачепия_у может быть представлен одним столбцом, одной строкой или прямоугольным диапазоном. Описание этих двух аргументов приводилось ранее, при рассмотрении функции ЛИНЕЙН.

Следующие два аргумента могут быть опущены. Аргумент новые_значения_х — это новые значения х, для которых функция ТЕНДЕНЦИЯ возвращает соответствующие значения у. При его отсутствии в списке полагается, что аргументы новые_значения_х и известные_значения_х совпадают. Если аргумент конст имеет значение ИСТИНА или не задан, коэффициент b вычисляется обычным образом, а когда его значение ЛОЖЬ, b полагается равным 0.

Для расчета линии тренда опустите третий и четвертый аргументы этой функции, тогда массив результатов будет иметь точно такой же размер, что и массив известных значений х. На рис. 17.4 для нахождения координат каждой точки на линии регрессии, аппроксимирующей данные, представленные на рис. 17.3, применялась функция ТЕНДЕНЦИЯ. Чтобы получить эти значения, необходимо выделить диапазон С2:С19 и ввести формулу =ТЕНДЕНЦИЯ(В2:В19;А2:А19) как формулу массива, нажав клавиши Ctrl+Shift+ Enter.

Рис. 17.4. Функция ТЕНДЕНЦИЯ возвращает значения для линии регрессии, что позволяет использовать их для построения диаграммы

Чтобы выполнить экстраполяцию на основе имеющихся данных, необходимо задать диапазон для аргумента новые_значения_х, он может состоять из любого количества ячеек. Массив результатов окажется точно такого же размера, что и указанный диапазон. На рис. 17.5 для расчета спроса на 19, 20 и 21-й месяцы работы предприятия применялась функция ТЕНДЕНЦИЯ. Мы ввели числа с 19 по 21 в ячейки А21:А23, затем выделили диапазон С21:С23 и ввели формулу =ТЕНДЕНЦИЯ(В2:В19; А2:А19;А21:А23) как формулу массива.

Функция ПРЕДСКАЗ

Функция ПРЕДСКАЗ (FORECAST) аналогична функции ТЕНДЕНЦИЯ, за исключением того, что она возвращает только одну точку на линии регрессии, а не массив, который определяет эту линию. Данная функция имеет следующие аргументы: х; известные_значения_у; известные_значения_х.

Координата х — это точка на оси X, для которой требуется выполнить экстраполяцию данных. Например, чтобы получить оценочное значение для ячейки

С23 на рис. 17.5, удобно вместо функции ТЕНДЕНЦИЯ воспользоваться функцией ПРЕДСКАЗ: =ПРЕДСКАЗ(21;В2:В19;А2:А19). В данном случае аргумент х это 21-я точка на построенной линии регрессии. С помощью функции ПРЕДСКАЗ можно определить значение для любой точки зависимости.

Рис. 17.5. Функция ТЕНДЕНЦИЯ позволяет провести прогнозную оценку объемов продаж за 19, 20 и 21-й месяцы работы предприятия

Функция НАКЛОН

Функция НАКЛОН (SLOPE) возвращает угловой коэффициент прямой регрессии, который определяется как результат деления расстояния между двумя точками на линии регрессии по вертикали на расстояние между ними по горизонтали. Значение функции НАКЛОН совпадает с первым числом в массиве, возвращаемым функцией ЛИНЕЙН. Функция НАКЛОН имеет следующие аргументы: известные_ значения _у\ известные_значения_х.

Чтобы найти угловой коэффициент линии регрессии для данных, представленных на рис. 17.5, можно записать формулу =НАКЛОН(В2:В19;А2:А19) как формулу массива. Она вернет значение 20,613.

Функция CTOШYX

Функция CTОШYX (STEYX) вычисляет стандартную ошибку регрессии как меру количества ошибок в предсказанных значениях у для каждого значения х в зависимости. Ее аргументы: известные _значения_у; известные_значения_х. Если применить эту функцию к данным, представленным на рис. 17.5, формула =CTОШYX(B2:B19;A2:A19) возвратит значение нормальной ошибки 12,96562.

Экспоненциальная регрессия

В отличие от метода линейной регрессии, который аппроксимирует имеющиеся данные прямой линией, в методе экспоненциальной регрессия для этого используется кривая,

рассчитываемая по массиву исходных значений. Ее уравнение выглядит следующим образом:

x1 x2 хп

у = b?m1

?m2

? …? тn .

Если имеется только одна независимая переменная, то это уравнение примет вид у=b?тх.

Функция ЛГРФПРИБЛ

Функция ЛГРФПРИБЛ (LOGEST) работает так же, как функция ЛИНЕЙН, но используется при анализе нелинейных зависимостей. Она вычисляет параметры экспоненциального приближения, то есть значения коэффициентов для каждой независимой переменной и константу b. Функция ЛГРФПРИБЛ имеет те же самые аргументы (известные зиачеиия_у; известные _значения_х; конст; статистика), что и функция ЛИНЕЙН, и возвращает аналогичный массив результатов.

Если для необязательного аргумента статистика задано значение ИСТИНА, то вместе с параметрами экспоненциальной кривой функция дополнительно генерирует статистику по регрессии. Подробнее об аргументах функции ЛИНЕЙН и возвращаемой статистике рассказывается в разделе «Функция ЛИНЕЙН» данной главы.

Функция РОСТ

В то время как функцию ЛГРФПРИБЛ интересуют параметры экспоненциальной кривой, аппроксимирующей множество исходных данных, функция РОСТ (GROWTH) позволяет находить координаты точек, лежащих на этой кривой. Она работает точно так же, как ее аналог в линейной регрессии — функция ТЕНДЕНЦИЯ, и имеет подобные аргументы (известные _значения_у; известные_значения_х; повые_ значениях; конст). Мы их уже рассматривали подробно в разделе «Функция ТЕНДЕНЦИЯ» данной главы.

Инструменты пакета анализа

Если при установке Excel был выбран полный набор компонентов, то функции и инструменты надстройки Пакет анализа будут доступны при каждом запуске программы. Чтобы обратиться к этим инструментам, выберите в меню Сервис команду Анализ данных (Data Analysis). На экране появится одноименное диалоговое окно, изображенное на рис. 17.6.

Описательная статистика

Инструмент Описательная статистика (Descriptive Statistics) позволяет формировать таблицу основных статистических характеристик и параметров для одного или нескольких наборов исходных значений, как показано на рис. 17.7.

Чтобы применить инструмент Описательная статистика, выберите в меню Сервис команду Анализ данных, а затем в списке Инструменты анализа (Analysis Tools) выберите этот инструмент и нажмите кнопку ОК. На экране появится диалоговое окно Описательная статистика (Descriptive Statistics), представленное на рис. 17.8.

Рис. 17.6. Диалоговое окно Анализ данных содержит список доступных инструментов

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