Регрессионные функции

Третьим способом выполнить регрессионный анализ является использование функций. В данном разделе описываются пять функций, применимых для простой регрессии, причем четыре из них имеют одни и те же аргументы. Так функция ОТРЕЗОК имеет следующий синтаксис ОТРЕЗОК(известные_значения_х; известные_значения_у). Такой же синтаксис имеют функции НАКЛОН, КВПИРСОН и СТОШYX (стандартная ошибка оценки). Данные четыре функции введены в ячейки Н2:Н5 рис. 2.15, а значения, возвращаемые ими, находятся в ячейках F2:F5.

Для того чтобы получить значения функций в столбце Н нужно скопировать в Буфер обмена (Правка|Копировать) и вставить в столбец F (Правка|Специальная вставка|Значения). Чтобы формулы отображались в столбце Н, выберите Параметры в меню Сервис, щелкните по вкладке ВИД и отметьте пункт Формулы в разделе Параметры окна.

В ячейках Н9 и Н11 показаны два способа получить предсказанную цену объекта с жилой площадью в 1000 квадратных футов. Если смещение и наклон в уравнении регрессии уже вычислены, то можно ввести формулу «=смещение + наклон*х» в ячейку Н9 с соответствующими ссылками на ячейки. Здесь предсказанная стоимость равна 39,79971699 тысяч долларов или приблизительно $39800.

Другой способ получения предсказанного значения для простой линейной регрессии основывается на функции ПРЕДСКАЗ, имеющей следующий синтаксис ПРЕДСКАЗ(х; известные_значения_у; известные_значения_х). Данный метод, применяемый в ячейке Н11, вычисляет смещение и наклон с помощью метода наименьших квадратов и возвращает предсказанное значение для указанного .

Еще одним способом получения предсказанных значений является использование функции ТЕНДЕНЦИЯ со следующим синтаксисом ТЕНДЕНЦИЯ(известные_значения_у; известные_значения_х; новые_значения_х;конст).

Данная функция, в отличие от функции ПРЕДСКАЗ, может быть использована в случае множественной регрессии. Так как функция ТЕНДЕНЦИЯ использует массивы, ее следует вводить с помощью следующей последовательности шагов.

Рис. 2.15. Регрессия с использованием функций

1. Введите данные для переменных и (А2:В16) и значения переменной (D13:D16), для которых предсказанное уже вычислено.

2. Выделите область, в которую будут помещены предсказанные значения (Н13:Н16).

3. В меню Вставка выполните команду Функция. Или щелкните по кнопке Вставки функции (пиктограмма ). В диалоговом окне Мастера функций выберите категорию Статистические и функцию ТЕНДЕНЦИЯ. Затем нажмите ОК.

4. В диалоговом окне Аргументы функции укажите или введите области листа с известными значениями (В2:В16), известными значениями (А2:А16) и новыми значениями (D13:D16). Не включайте метки в первой строке в данной области. В строке с названием Конст введите цифру 1, означающую ИСТИНА и показывающую, что смещение отлично от нуля. Затем нажмите ОК.

5. При выделенных ячейках Н13:Н16 нажмите клавишу F2 (для редактирования). В строке статуса внизу окна появится «Правка». Удерживая клавиши Shift и Ctrl, нажмите Enter. В панели формул функция ТЕНДЕНЦИЯ будет в фигурных скобках, показывая, что функция массива введена правильно.

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

Для получения результатов, показанных на рис. 2.16, выделите D1:E5, введите ЛИНЕЙН или используйте для этого инструмент Вставка функции, нажмите F2, а затем, удерживая клавиши Shift и Ctrl, нажмите Enter. Значения функций в ячейках D1:Е5 показаны в ячейках D7:E11 с описаниями значений в D13:E17. Те же самые числа с метками были в результатах инструмента анализа Регрессия на рис. 2.11.

Рис. 2.16. Регрессия, использующая функцию ЛИНЕЙН

Задание. В следующей таблице представлены данные о рентах и свободных площадях для десяти городов.

1. Постройте график и добавьте линию тренда.
2. С помощью инструмента анализа Регрессия определите все характеристики.
3. Выполните прогноз доли свободной площади для города с месячной рентой за квадратный фут $3,50.

Задание. В следующей таблице представлены данные о количестве часов, потраченных 20 студентами на подготовку к основному тесту, и их отметки.

1. Постройте график и добавьте линию тренда.
2. С помощью инструмента анализа Регрессия определите все характеристики.
3. Выполните прогноз оценки для студентов, занимающихся десять часов.