I. Построение модели в среде Excel.

В таблице 2.1 приведены значения независимой переменной X(доход американской семьи (тысяч долларов) и значения зависимой переменной Y(доля расходов на товары длительного пользования в процентах от общей суммы расходов).

Таблица 2.1.

Используя экспериментальные данные из таблицы 2.1 необходимо построить несколько аналитических моделей зависимости расходов на товары длительного пользования от доходов, и вычислить коэффициент детерминации  R².

В пакете Excel присутствует команда «Добавить линию тренда».  Стандартно она используется для выделения тренда (медленных изменений) при анализе временных рядов. Однако эту команду можно использовать и для построения уравнения регрессии, рассматривая в качестве времени t независимую переменную X.

Эта команда позволяет построить следующие уравнения регрессии:

• линейную y=a₀+a₁x;

• полиноминальную y=a₀+a₁x+a₂x²+a₃x³+…+a_kx^k (k≤6).

• логарифмическую y=a₀+a₁*ln(x)

• степенную ;

• экспоненциальную 

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

Шаг 1. В выбранном листе Excel ввести по столбцам исходные данные {x_i;y_i} (см. рис. 2.1).

Шаг 2. По этим данным построить график в декартовый системе координат (см. рис 2.1).

Шаг 3. Установить курсор на построенном графике, сделать щелчок правой кнопкой и в появившемся контекстном меню выполнить команду Добавить линию тренда (см. рис. 2.1).

Рис. 2.1. Построение графика по исходным данным и выбор добавления линии тренда

Шаг 4. В появившемся диалоговом окне (см. рис. 2.2) активизировать поле «Параметры линии тренда» и выбрать нужное уравнение регрессии.

Рис. 2.2. Выбор вида уравнения регрессии

 Шаг 5. Включить необходимые опции:

• «Показывать уравнение на диаграмме» - на диаграмме будет показано выбранное уравнение регрессии с вычисленным коэффициентами;

•   «Поместить на диаграмму величину достоверности аппроксимации (R^2)» - на диаграмме будет показана значение коэффициента детерминации R² .

Рис. 2.3. Задание опций вывода информации

Шаг 6. После задания всех перечисленных опций щелкнуть на кнопке «OK», и на диаграмме появиться формула построенного уравнения регрессии и значение индекса детерминации R² .

Рис. 2.4. График и уравнение построенной регрессии

Меняя параметры и вид функции регрессии в окне «Формат линии тренда» можно моментально наблюдать на графике изменение линии регрессии и ее характеристик. Таким образом можно «перебирать» различные функции регрессии для выбора оптимальной, то есть той, которая обеспечит максимальный коэффициент детерминации.