Экспоненциальная регрессия
Excel позволяет строить экспоненциальную регрессию. Для этого есть функции ЛГРФПРИБЛ и РОСТ.
Функция ЛГРФПРИБЛ
Функция ЛГРФПРИБЛ вычисляет коэффициенты экспоненциальной кривой, которая наилучшим образом аппроксимирует имеющиеся данные, а также выдает дополнительную регрессионную статистику. Функция возвращает массив значений, который описывает полученную кривую. Поскольку данная функция возвращает массив значений, она должна вводиться как формула массива. Уравнение кривой имеет следующий вид:
y = b*m^x или
y = (b*(m1^x1)*(m2^x2)*...) (в случае нескольких переменных x1, х2, ...),
Синтаксис функции:
ЛГРФПРИБЛ(известные_y; [известные_x]; [константа]; [статистика])
Известные_y. Обязательный аргумент. Множество значений y, которые уже известны для соотношения y=b*m^x.
Известные_x. Необязательный аргумент. Множество значений x, которые уже известны для соотношения y=b*m^x. Если аргумент известные_x опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_y.
Константа. Необязательный аргумент. Логическое значение. Если аргумент Константа = 0, то b принудительно полагается равным единице, т.е. y=m^x. Если аргумент Константа имеет значение ИСТИНА или опущен, то b вычисляется обычным образом.
Статистика. Необязательный аргумент. Логическое значение. Если аргумент Статистика = 0 или опущен, то вычисляются только коэффициенты m и b, а если = 1, то выдаётся ещё и дополнительная статистика по регрессии.
Функция РОСТ
Рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью известных x- и y-значений. Функция РОСТ может применяться также для для аппроксимации известных x- и y-значений экспоненциальной кривой.
РОСТ(известные_y; [известные_x]; [новые_x];[константа])
Новые_x. Необязательный аргумент. Новые значения x, для которых РОСТ возвращает соответствующие значения y.
Пример.
В газете "The Chicago Maroon" за 10 ноября 1972 г. сообщалось, что на оптовом рынке ожидаются следующие цены на марочные портвейны в расчете на бутылку в зависимости от года закладки вина.
| Год | 1890 | 1900 | 1920 | 1931 | 1934 | 1935 | 1940 | 1941 | 1944 | 1948 | 1950 | 1952 | 1955 | 1960 |
| Цена | 50,00 | 35,00 | 25,00 | 11,98 | 15,00 | 13,00 | 6,98 | 10,00 | 5,99 | 8,98 | 6,98 | 4,99 | 5,98 | 4,98 |
Построить регрессию для цен на марочное вино в зависимости от возраста вина. Имея формулу регрессии, обоснованно назначить цену на вино, год закладки которого (1926) отсутствует в таблице.
Решение.
Создадим таблицу по образцу.
- Ячейка В1. Дано имя Год.
- Ячейки А4:А17. Дано имя ГодЗакладки.
- Ячейки В4:В17. Дано имя ИзвВозраст. Формула =Год-ГодЗакладки.
- Ячейки С4:С17. Дано имя ИзвЦена.
- Ячейки Н5:I9. Формула массива {=ЛГРФПРИБЛ(ИзвЦена;ИзвВозраст;1;1)}
- Ячейка Н5. Дано имя m.
- Ячейка I5. Дано имя b.
- Ячейки D4:D17. Формула b*m^ИзвВозраст
- Ячейки E4:E17. Формула массива {=РОСТ(ИзвЦена;ИзвВозраст)}
