Рассчитать коэффициенты уравнения регрессии экспоненциальной структуры с выводом дополнительной статистики по регрессии возможно с помощью функции ЛГРФПРИБЛ.
§ ЛГРФПРИБЛ(известные_значения_Y;известные_значения_X; константа; статистика) – в регрессионном анализе вычисляет экспоненциальную кривую, аппроксимирующую данные, и возвращает массив значений, описывающий эту кривую. Она является универсальной для расчета параметров экспоненциальных моделей, так как, кроме коэффициентов уравнения регрессии, может возвращать и дополнительную статистику по регрессии.
В отличие от функцииЛИНЕЙНаргумент константа — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1. Если константа имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если константа имеет значение ЛОЖЬ, то b полагается равным 1 и значения m подбираются так, чтобы удовлетворить соотношению y = mx.
Способ использования функции ЛГРФПРИБЛ аналогичен функции ЛИНЕЙН, с той лишь разницей, что исходный набор данных аппроксимируется не прямой линией, а экспонентой. В соответствии с этим и количеством независимых переменных уравнение регрессии принимает соответствующий вид.
В случае парной регрессии
Y = b*mx или Y = mx.
В случае множественной регрессии
Y = b∙m1x1 ∙m2x2∙... ∙mnxn или Y = m1x1 ∙m2x2∙... ∙mnxn.
Осуществить прогноз фактора-признака Y на основании экспоненциальной зависимости по наблюдаемым X - и Y -значениям можно с помощью функции РОСТ.
§ РОСТ(известные_значения_Y;известные_значения_X;новые_значения_X; константа) – эта функцияаналогична функции ТЕНДЕНЦИЯ и используется для расчета прогнозируемого экспоненциального роста на основании имеющихся данных. Она возвращает значения Y для последовательности новых значений X, задаваемых с помощью существующих X- и Y-значений.
Оценить адекватность построенной модели на основании коэффициента корреляции, критерия Фишера и критерия Стьюдента можно с помощью функций:
§ КОРРЕЛ(масив1; массив2) – рассчитывает коэффициент корреляции R, который должен находиться в пределах от -1 до +1. Его положительное значение свидетельствует о прямой связи, отрицательное – об обратной, т.е. при увеличении одной переменной другая переменная уменьшается. Чем ближе его абсолютное значение к единице, тем теснее связь. Связь считается достаточно сильной, если коэффициент корреляции по абсолютной величине превышает 0,7, и слабой, если меньше 0,4. Адекватность модели можно оценить с помощью коэффициента детерминированности R2, который равен квадрату коэффициента корреляции и показывает, насколько точно уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными.
масив1; массив2 – анализируемые массивы или интервалы значений.
§ FРАСПОБР(вероятность;степени_свободы1; степени_свободы2 ) – эта функциявычисляет критерий Фишера - F-статистику, используемую для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет.
Вероятность — это вероятность, связанная с F-распределением.
Степени_свободы1 — это числитель степеней свободы.
Степени_свободы2 — это знаменатель степеней свободы.
· СТЬЮДРАСПОБР (вероятность;степени_свободы)- эта функция вычисляет t-статистику Стьюдента, позволяющую оценить значимость коэффициентов уравнения регрессии.
Вероятность — вероятность, соответствующая двустороннему распределению Стьюдента.
Степени_свободы — число степеней свободы, характеризующее распределение.
ПРАКТИЧЕСКАЯ ЧАСТЬ
Задание 1. В таблице 2.2 приведены показатели уровня жизни по территориям регионов республики Беларусь за 200Хг. Провести анализ зависимости среднедневной заработной платы, руб. (Y) от среднедушевого прожиточного минимума в день одного трудоспособного, руб. (
).
Таблица 2.2. Показатели уровня жизни по территориям регионов республики Беларусь за 200Х год
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб. | Среднедневная заработная плата, руб. |
| № | X | Y |
Разместим таблицу с исходными данными в ячейках А3:С24 рабочего листа Excel. В ячейки В22-В24 внесем значения среднедушевого прожиточного минимума, для которых требуется выполнить прогноз уровня среднедневной заработной платы (см рис.2.1).
Чтобы выполнить анализ зависимости среднедневной заработной платы, руб. (Y) от среднедушевого прожиточного минимума в день одного трудоспособного, руб. (X), следует построить однофакторную регрессионную модель вида y = m∙x + b.
Рассчитаем линейную регрессионную однофакторную модель (см. рис.2.1 и рис.2.2), для чего в ячейки Е5:А9 введем функцию ЛИНЕЙН в формате =ЛИНЕЙН(C6:C21;B6:B21;1;1).
Результатом работы функции является массив значений:
ячейки E5, F5 – коэффициенты уравнения регрессии m=1.4111 и b=3816,154;
ячейка Е7 – коэффициент детерминированности R2 =0.885;
ячейка E8 – критерий Фишера F=108.12.
Рис.2.1 – Расчет однофакторной регрессионной модели. Результаты
Таким образом, однофакторная регрессионная модель, оценивающая влияние среднедушевого прожиточного минимума в день одного трудоспособного на величину среднедневной заработной платы имеет вид
Y = 1.411∙x + 3816.154
Вывод: поскольку коэффициент детерминированности R2=0.885 лежит в пределах 0,75 – 1, расчетное значение критерия Фишера F=108,12 больше табличного (FРАСПОБР(0,05;1;F8)= 4,6), модель следует признать адекватной и использовать для прогнозирования.
В ячейке С22 рассчитаем прогнозное значение среднедневной заработной платы по формуле =E5*B22+F5. В ячейках С23, С24 расчет аналогичен (см. рис.2.2).
В ячейках Е12:Е14 рассчитаем прогнозное значение среднедневной заработной платы для среднедушевого прожиточного минимума, равного 4000 руб., 4500 руб., 5000 руб. (ячейки В22–В24) с использованием функции ТЕНДЕНЦИЯ:
=ТЕНДЕНЦИЯ(C6:C21;B6:B21;B22:В24;1).
Рис. 2.2 – Расчет однофакторной регрессионной модели. Формулы
В ячейке Е15 рассчитаем прогнозное значение среднедневной заработной платы с использованием функции ПРЕДСКАЗ:
=ПРЕДСКАЗ(B22;C6:C21;B6:B21).
В ячейке Е18 рассчитаем значение коэффициента корреляции R2:
=КОРРЕЛ(C6:C21;B6:B21)^2.
В ячейке Е12 рассчитаем табличное значение критерия Фишера:
=FРАСПОБР(0,05;1;F8).
Полученные значения совпали c результатами, возвращенными функцией ЛИНЕЙН.
Задание 2. В соответствии с условием задания 1 построить экспоненциальную однофакторную регрессионную модель, оценивающую влияние среднедушевого прожиточного минимума в день одного трудоспособного (X) на величину среднедневной заработной платы (Y).
Воспользуемся данными, размещенными в ячейках А3:С21 рабочего листа Excel. (см. рис. 2.1).
Рассчитаем экспоненциальную регрессионную однофакторную модель вида
y= b∙mx,
для чего в ячейки H5:I9 введем функцию ЛГРФПРИБЛ в формате =ЛГРФПРИБЛ(C6:C21;B6:B21;1;1) (см. рис.2.3).
Результатом работы функции является массив значений (ячейки H5:I9, рис. 2.3):
ячейки H5, I5 – коэффициенты уравнения регрессии m=1.0001 и b=4553.49;
ячейка H7 – коэффициент детерминированности R2 =0.8501;
ячейка H8 – критерий Фишера F=79.42.
Таким образом, экспоненциальная однофакторная регрессионная модель, оценивающая влияние среднедушевого прожиточного минимума в день одного трудоспособного на величину среднедневной заработной платы, имеет вид:
Y = 4553.49∙1.0001 x.
Рис.2.3 – Расчет экспоненциальной однофакторной модели
Вывод: поскольку коэффициент детерминированности R2=0.8501 лежит в пределах 0,75 – 1, расчетное значение критерия Фишера 79,41 больше табличного (4.6), модель следует признать адекватной и, следовательно, ее можно использовать для прогнозирования.
В ячейке H20 рассчитаем прогнозное значение среднедневной заработной платы при уровне среднедушевого прожиточного минимума в день, равном 4000 руб. (ячейка В22) по формуле =I5*H5^B22.
В ячейках H12 – H14 рассчитаем прогнозное значение среднедневной заработной платы с использованием функции РОСТ:
=РОСТ(C6:C21;B6:B21;B22:B24;1).
В ячейке H15 рассчитаем значение коэффициента корреляции R:
=КОРРЕЛ(C6:C21;B6:B21).
Полученные значения совпали c результатами, возвращенными функцией ЛГРФПРИБЛ.
Задания для самостоятельной работы 
С помощью встроенных функций ЭТ Excel установить связь между анализируемыми данными (см. таблицу 2.3), построить и проанализировать экономико-математическую однофакторную регрессионную модель, позволяющую получить прогноз результативного признака на последующие периоды. Вид модели определить самостоятельно. Вывести всю возможную статистическую информацию. Построить график изменения результативного признака во времени. Отразить на графике линию тренда и уравнение регрессии.
Таблица 2.3. Показатели деятельности предприятия
| Номер предприятия | Выработка продукции на одного работника тыс. руб. | Новые ОПФ, % | Удельный вес рабочих высокой квалификации % | Коэффициент использования оборудования |
| № | Y | x1 | x2 | x3 |
| 3,9 | 0,76 | |||
| 3,9 | 0,78 | |||
| 3,7 | 0,75 | |||
| 0,78 | ||||
| 3,8 | 0,74 | |||
| 4,8 | 0,81 | |||
| 5,4 | 0,81 | |||
| 4,4 | 0,82 | |||
| 5,3 | 0,82 | |||
| 6,8 | 0,82 | |||
| 0,84 | ||||
| 6,4 | 0,84 | |||
| 6,8 | 0,8 | |||
| 7,2 | 0,8 | |||
| 0,85 | ||||
| 8,2 | 0,85 | |||
| 8,1 | 0,88 | |||
| 8,5 | 0,87 | |||
| 9,6 | 0,89 | |||
| 0,85 |
Варианты заданий
Варианты 1-4: Результативный признак – Y, факторный признак – X1.
Варианты 5-7: Результативный признак – Y, факторный признак – X2.
Варианты 8-10: Результативный признак – Y, факторный признак – X3.