Решение типовой задачи. Для данных, представленных в таблице 1.1 требуется:

Для данных, представленных в таблице 1.1 требуется:

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

2. Построить степенную регрессионную модель зависимости заработной платы от возраста рабочего, оценить её точность по индексу детерминации и средней относительной ошибки аппроксимации и значимость (на уровне ).

3. Сравнить модели парной регрессии (включая линейную) по индексу детерминации и средней относительной ошибки аппроксимации и выбрать наилучшую.

Решение выполним в среде MS Excel.

1. Оценим гиперболическую модель . Она линейна по параметрам .

Введем новую переменную . Линеаризованная модель примет вид: .

Сформируем расчетную таблицу следующей структуры:

№ п/п
Сумма
Среднее

Введем исходные данные , в таблицу по столбцам и рассчитаем колонки , , , . Вычисляем суммы и средние значения столбцов с помощью функций СУММ(…) и СРЗНАЧ(…).

Выполним расчет параметров уравнения регрессии по формулам 1.2.2:

, .

В итоге получена гиперболическая модель: .

Вычислим предсказанные моделью значения з/п по формуле

и тем самым заполним колонку расчетной таблицы. Далее вычисляются остатки и их квадраты . В итоге в строке "Сумма" колонки таблицы определится остаточная сумма квадратов .

Построим график функции на поле корреляции с помощью Мастера диаграмм и убедимся, что МНК дал хорошие результаты аппроксимации.

Рис.1.3. График гиперболической модели

Найдем значения выборочной дисперсии и СКО для по формулам 1.2.3:

, .

Найдем индекс корреляции по формуле 1.2.4:

.

Индекс корреляции близок к единице и это указывает на тесную гиперболическую связь между изучаемыми признаками.

Рассчитаем индекс детерминации по формуле 1.2.5:

.

Значение индекса детерминации близко к единице и по нему следует, что з/п по этой модели на 78% обусловлена таким фактором, как возраст рабочего.

Проверим качество модели по средней относительной ошибке аппроксимации, вычислив по формуле 1.2.6. Для этого в первой строке колонки набираем с использованием функции ABS (…) формулу: =ABS()*100. После протяжки по всему столбцу вычисляем среднее значение данного столбца:

= .

По видно, что в среднем расчетные значения отклоняются от фактических на 8,7%, что говорит о хорошем качестве модели по этому критерию.

Вычислим средний коэффициент эластичности по формуле 1.2.7:

Найдем производную

.

Отсюда

.

Он показывает, что при увеличении возраста рабочего на 1 % от среднего значения з/п в среднем возрастает на 0,74%.

Рассчитаем критерий Фишера по формуле 1.2.8 (в нашем случае ):

.

Табличное значение = определяем с помощью встроенной статистической функции FРАСПОБР по уровню значимости и числам свободы и . Поскольку , то можно сделать вывод о хорошей аппроксимации статистических данных построенной моделью.

2. Построим степенную модель . Эта модель является нелинейной по параметру .

Выполним преобразования по формулам 1.2.9. Линеаризованная модель примет вид: . Здесь , .

Сформируем расчетную таблицу следующей структуры:

№ п/п

(

Сумма

Среднее

Введем исходные данные , в таблицу по столбцам и рассчитаем колонки , , , . Вычисляем суммы и средние значения столбцов с помощью функций СУММ(…) и СРЗНАЧ(…).

Выполним расчет параметров уравнения регрессии по формулам 1.2.10:

, .

Найдем оценку коэффициента с использованием функции EXP (…):

.

В результате построена степенная модель .

Вычислим на основе модели значения з/п по формуле:

с использованием встроенной функции СТЕПЕНЬ (; ). В итоге будет заполнена колонка таблицы.

Далее вычисляются остатки , их квадраты , разности , а также их квадраты . В итоге в строке «Сумма» колонки таблицы определится факторная сумма квадратов .

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

Рис. 1.4. График степенной модели

Найдем индекс корреляции по формуле 1.2.12 (значение определено ранее):

.

Близость индекса корреляции к единице указывает на тесную степенную связь между изучаемыми признаками.

Рассчитаем индекс детерминации по формуле 1.2.13:

.

Из значения индекса детерминации следует, что з/п по этой модели на 84% обусловлена возрастом рабочего.

Оценим качество модели по средней относительной ошибке аппроксимации, вычислив по формуле 2.6:

= .

По видно, что в среднем расчетные значения отклоняются от фактических на 9,9%, что говорит о неплохом качестве модели по этому критерию.

Вычислим средний коэффициент эластичности по формуле 1.2.7:

Найдем производную:

.

Отсюда

.

Из этого следует, что при увеличении возраста рабочего на 1 % от среднего значения з/п в среднем возрастает на 0,86%.

Рассчитаем критерий Фишера по формуле 1.2.8:

.

Табличное значение = уже ранее определено. Так как выполняется неравенство , то можно сделать вывод о надежности и статистической значимости степенной модели.

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

Модель
Гиперболическая	8,73	0,778
Степенная	9,90	0,844
Линейная	10,16	0,72

Из таблицы видно, что по средней ошибке аппроксимации лучшей является гиперболическая модель, а по индексу детерминации – степенная. Но по этим показателям они обе лучше линейной модели. Для практического использования можно рекомендовать степенную модель.