Аппроксимация экспонентой силами Экселя

maldeln

Может, кто-нибудь знает точно ответ на вопрос:
Как просто и научно аппроксимировать ряд данных экспонентой вида y = A Exp[с*x] + B?
Если B = 0, то всё легко строится по МНК.
А если неизвестное В?
Задача усложняется тем, что всё нужно сделать в Экселе и максимально просто, для дебилов.
Если криволинейное МНК, то посоветуйте конкретный источник. Я навскидку простую и удобную формулу не смогла написать.

Sensor4ik

Делается все вручную следующим образом:
Допустим, есть два столбца чисел - x и y.
В двух отдельных ячейках (где-нибудь справа) забиваются стартовые числовые значения параметров A и B.
Далее в отдельном столбце генерируется значение функции (то есть высчитывается для каждого х значение A Exp[x] + B при этом в формуле должны присутствовать жесткие ссылки на ячейки с параметрами A и B (то есть если параметр A хранится в ячейке G1, B - в ячейке G2, то формула должна выглядеть следующим образом: Exp(A1)*$G$1+$G$2). В следующем столбце нужно забить формулу вычисления квадрата отклонения реального y от вычисленного. Далее в какой-нибудь ячейке справа забить сумму по последнему столбцу (=Сумм(D1:D10 например).
Далее идем в меню "Сервис" --> "Надстройки". Напротив "Поиск Решения" должна стоять галка. Если эта надстройка не стоит, доставить из дистрибутива офиса.
Меню "Сервис" --> "Поиск решения".
В открывшемся окне нужно в качестве целевой указать ячейку с суммой отклонений, тыкнуть галочку "Минимальному значению", "Изменяя ячейки" - выделить ячейки, где у нас хранятся значения A и B.
Клацаем "Выполнить". В простейшем случае будет найдено решение. Если нет, то надо играться со стартовыми значениями параметров A и B и параметрами Поиска решения.
P.S. Но проще использовать Origin.

maldeln

Фегасе. А если 200 рядов приближать? :ooo:
Вообще это не сработает.
Потому что к подэкспоненциальному коэффициенту есть большая чувствительность. Так что "подбор параметров" просто зависает через 10 секунд перебора на произвольных параметрах.
Ряды данных, кстати, не гладкие. То есть необходима аппрокимация не как эксель на душу положит, а с наименьшей дисперсией.
Я бы всё с удовольствием решила программным путём, прямо в Оракле. Однако, нужны банальные листы Экселя с формулками.

griz_a

не вижу сложности в оценке по МНК.
Записываем
[math]$RSS=\sum\limits_{i=1..n} (y_i-ae^{cx_i}-b)^2$[/math]
[math]$\frac{dRSS}{da}=-\sum\limits_{i=1..n} (y_i-ae^{cx_i}-b)e^{cx_i}=0$[/math]
[math]$\frac{dRSS}{db}=-\sum\limits_{i=1..n} (y_i-ae^{cx_i}-b)=0$[/math]
[math]$\sum\limits_{i=1..n} y_ie^{cx_i}-a\sum\limits_{i=1..n} e^{2cx_i}-b\sum\limits_{i=1..n}e^{cx_i}=0$[/math]
[math]$\sum\limits_{i=1..n} y_i-a\sum\limits_{i=1..n} e^{cx_i}-b=0$[/math]
[math]$a=\frac{\sum\limits_{i=1..n} y_ie^{cx_i}-\sum\limits_{i=1..n} y_i \sum_{i=1..n} e^{cx_i}}{\sum_{i=1..n} e^{2cx_i} - \sum\limits_{i=1..n} e^{cx_i}\sum\limits_{i=1..n} e^{cx_i}}$[/math]
[math]$b=\sum\limits_{i=1..n} y_i-a\sum\limits_{i=1..n} e^{cx_i}$[/math]

toxin

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

maldeln

:grin: Да, в этом вся загвоздка: в том, что все три коэффициента — переменные. Если продифференцировать ещё и по с, то получится жосткая нетривиальная система уравнений.
Может, задачу можно облегчить тем, что точки для кривой расположены через равные интервалы?
Т.е. можно записать: x = n , где n = [0,1,2..,N]
Кроме того, так как экспонента убывающая, то сам ряд можно привести к виду 100%, 96%, 95% и т.д.
A + B = 1. :crazy:
А ещё есть идеи?

seregaohota

A + B = 1.
Здравствуй ёлка - Новый Год. А это откуда? Выходит у тебя в задаче 2 параметра, а не 3?

y_i = a*f(c*x_i

f(t) = exp(t) - 1

maldeln

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

griz_a

А как ты считала для b=0

maldeln

Получается так:
f = Exp[c*x + a], где a = Ln[A]
Значит, g = Ln[f] = c*x + a.
И тут уже можно развернуться на линейном МНК.
Минимизируется, конечно, не дисперсия первоначального ряда, а его логарифма, но меня это устраивает.

griz_a

Скажем так, f=aexp(c)+eps
eps - нормальная случайная величина. И после логарифмирования как-то странно получается. Но если вас это устраивает и вам нужно решить задачу численно, то нет проблем - загоняете в столбик значения b с шагом h в некоторых пределах, а дальше при фиксированном b выписываете формулу для a, с и RSS. После этого выбираете b с наименьшей RSS

maldeln

А можно это как-то автоматизировать? Или действительно придётся на глазок определять, какое нравится?

griz_a

Конечно можно.
Загоняем столбик b с шагом сколько хочется.
Растягиваем формулу для a,c, RSS при фиксированном b.
Выбираем минимум RSS и берем те a,b,c

maldeln

Мне очень много рыдов придётся подгонять. То есть цель — выяснить закономерности роста или падения этих самых a, b И с, спрогнозировать их дальнейшее поведение. Для этого есть ОЧЕНЬ много данных. Выбирать для каждого из рядов понравившиеся значения — это слишком : (
Спасибо большое за помощь! :)

griz_a

Формулы для a, c и RSS от x,y,b выписываются.
Загоняется в макрос и считается min RSS, берутся соответствующие a,b,c, что еще надо?

maldeln

Так как точек много, то уравнение получается с многими корнями, и подбор параметра не очень-то помогает : (
Фиг его знает какое сочетание из ста соответствует нужной экспоненте...

griz_a

Какое уравнение с многими корнями? По b решение находится явно, осталось минимизировать функцию по одному аргументу. В чем сложность?

maldeln

Похоже, в текстовом режиме объяснения неэффективны :crazy:
Не поняла я про RSS. Я даже не знаю, как это расшифровывается.
Решение в лоб:
Дисперсия от функции: SUM(y_i - A*exp(b*x_i) - c)^2.
Берём частные производные от трёх параметров, они должны быть равны нулю.
По очереди вытесняем из уравений все параметры, кроме b.
Получается крокодил типа:
SUM_m_n( y_m* (x_m * g^x_m - x_n * g^x_n + много-много чего = 0
Где g = exp(b).
То есть получается много-много корней. Случайные числа тут ни при чём.

griz_a

Я предлагаю другой метод. Пусть b известна. Тогда мы для y_i-b и x_i имеем модель без b, которую ты умеешь решать. Выражаем для той модели a, c, и то, что ты называешь дисперсией, это ты умеешь делать в общем виде.
Теперь пробегаем по всем b от -много минимума из y_i с шагом h.
Для каждого из них мы получаем свои a,c и дисперсию. Выбираем то b, при котором дисперсия минимальна. Берем соответствующие a и c, что и требовалось

agszao

если говорить о глупости :), можно попробовать таким наивным способом

потом тупо по графику подогнать знак А :)
Ну а зная А,с оценить В.
тока у-ки надо упорядочить
Оставить комментарий
Имя или ник:
Комментарий: