Примечание. Общепринято записывать полиномы, размещая степени слева на право в порядке возрастания. Однако в Excel уравнения полиномиальной линии тренда записываются в обратном порядке, на что следует обратить внимание при чтении уравнений.
Чтобы построить полиномиальную регрессию второго порядка, отобразите на экране диалоговое окно Формат линии тренда, дважды щелкнув на существующей линии тренда (рис. 12).
"Выберите тип Полиномиальная и установите Степень полинома, равную 2 (рис 5.15).
Похоже, полиномиальная линия тренда описывает данные достаточно хорошо, и соответствующая ей величина R2 близка к единице.
С помощью линий тренда регрессия выполняется довольно легко и быстро. Однако что делать, если в программе Excel необходимо аппроксимировать данные полиномиальной кривой седьмой степени? Такую регрессию позволяет выполнить специальный пакет анализа, доступ к которому осуществляется с помощью команды меню Сервис → Анализ данных...→регрессия. Этот пакет также полезен в тех случаях, когда нужно получить более подробные сведения о результатах регрессии, чем те, которые выводятся при использовании линий тренда.
5. Выполнение линейной регрессии с помощью пакета регрессионного анализа.
Пакет регрессионного анализа в Excel достаточно прост в использовании, однако он несколько сложнее, чем обычное построение линии тренда на диаграмме. Такой более сложный подход может понадобиться по двум причинам.
1. Иногда нужно использовать модель регрессии, линия тренда которой отсутствует. Пакет регрессионного анализа позволяет применить любую модель линейной регрессии.
2. Может понадобиться более подробная информация о процессе регрессии, которую нельзя получить с помощью линий тренда.
5.1. Простая линейная регрессия с нахождением углового коэффициента и точки пересечения линии регрессии с осью ординат.
В качестве первого примера работы с пакетом регрессионного анализа рассмотрим вычисление углового коэффициента и точки пересечения с осью ординат прямой, описывающей зависимость температуры от времени.
Шаг 1. Открываем окно Анализ данных
Чтобы вызвать на экран диалоговое окно Анализ данных, выберите команду Сервис→Анализ данных.... В этом окне отображается список инструментов анализа, один из которых — Регрессия.
Примечание. Этот пакет анализа данных устанавливается по умолчанию, однако он не является активированной частью пакета Excel. Если в меню Сервис не отображается команда Анализ данных..., это означает, что пакет не активирован. Выберите команду Сервис→ Надстройки... и активируйте команду Пакет анализа (это достаточно сделать один раз).
Шаг 2. Выбор в списке окна Анализ данных команды Регрессия
Выберите команду Регрессия и щелкните на кнопке ОК (рис. 5.I6).
При этом на экране должно появиться диалоговое окно Регрессия (рис. 5.17).
Шаг 3(а). Выбор значений зависимой переменной: использование кнопки, позволяющей перейти в электронную таблицу
В диалоговом окне имеется поле Входной интервал Y (рис. 5.17), справа от которого расположена небольшая кнопка, позволяющая перейти в электронную таблицу (чтобы можно было выбрать ячейки, в которых содержатся значения у). Щелкните на этой кнопке.
Примечание. Обычно при выполнении линейной регрессии предполагается, что вся неопределенность спрятана в значениях у, а значения х известны точно. Поэтому важно указать в качестве значений у те числа, которые являются приближенными.
Шаг 3(b). Выбор ячеек, содержащих значения у
После перехода к электронной таблице укажите с помощью мыши те ячейки, в которых содержатся значения у. Выбрав значения у, щелкните на кнопке, позволяющей вернуться к диалоговому окну (на рис. 5.18 она обведена кружком), или нажмите клавишу <Enter>. В результате мы возвратимся к диалоговому окну Регрессия.
Шаг 4(а). Выбор значений независимой переменной: использование кнопки, позволяющей перейти в электронную таблицу
Аналогичным образом следует указать значения х (рис. 5.19).
Шаг 4(b). Выбор ячеек, содержащих значения х
С помощью мыши выберите ячейки, в которых содержатся значения х, а затем возвратитесь к диалоговому окну Регрессия (рис. 5.20).
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.