2.2.6. Диаграммы регрессии

В случае парной линейной регрессии инструмент анализа строит две диаграммы: график остатков и график подбора. Данные диаграммы располагаются вверху листа, справа от итоговых результатов. График подбора прямой приведен на рис. 2.13.

Рис. 2.13. Первоначальный график подбора прямой

График остатков представлен на рис.2.14. Данный тип диаграмм применяется для определения, является ли приемлемой форма функционала аппроксимирующей кривой. Если график остатков имеет случайный рисунок, то линейное приближение является удовлетворительным. Если же график остатков имеет определенную структуру, то может потребоваться дополнительное моделирование. Если имеется только переменная , то график остатков выглядит так, как если бы аппроксимирующая прямая на рис. 2.13 стала горизонтальной. Если же имеется несколько переменных (множественная регрессия), то график остатков является существенным инструментом проверки точности модели, так как обычно нет возможности нарисовать аппроксимирующую кривую в случае трех и более измерений.

Рис. 2.14. График остатков регрессии

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

Третьим способом выполнить регрессионный анализ является использование функций. В данном разделе описываются пять функций, применимых для простой регрессии, причем четыре из них имеют одни и те же аргументы. Так функция ОТРЕЗОК имеет следующий синтаксис ОТРЕЗОК(известные_значения_х; известные_значения_у). Такой же синтаксис имеют функции НАКЛОН, КВПИРСОН и СТОШ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	4	5	6	7	8	9	10
Доля площадей	10	2	7	8	4	6	7	8	9	10
Месячная рента за квадратный фут (долл.)	5,0	2,5	4,75	4,5	3,0	4,5	4,0	3,0	3,25	2,75

1. Постройте график и добавьте линию тренда.

2. С помощью инструмента анализа Регрессия определите все характеристики.

3. Выполните прогноз доли свободной площади для города с месячной рентой за квадратный фут $3,50.

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

Студент	Время	Отметка
1	5	54
2	10	56
3	4	63
4	8	64
5	12	62
6	9	61
7	10	63
8	12	73
9	15	78
10	12	72
11	12	74
12	20	78
13	16	83
14	14	86
15	22	83
16	18	81
17	30	88
18	21	87
19	28	89
20	24	93

1. Постройте график и добавьте линию тренда.

2. С помощью инструмента анализа Регрессия определите все характеристики.

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