Я пытаюсь узнать о статистике виндустрия управления инфраструктурными активами.
У меня есть существующее уравнение экспоненциальной регрессии, которое используется для определения состояния заданного актива:
Y = B - е акс
В связанном вопросе кто-то предположил, что я"можно получить переменную a
напрямую изнормальное уравнение":
Базовая экспоненциальная регрессия
Это звучит многообещающе. Но как человек, не имеющий математического образования, я испытываю трудности с преобразованием математической записи в этом уравнении в синтаксис Excel.
Вопрос:
Как я могу рассчитатьнормальное уравнениеиспользуете синтаксис/формулы Excel 2016?
решение1
Вот альтернатива, которая решает проблему, поднятую вами в комментарии к ответу, опубликованному наматематика.stackexchange
Ваша модель:
у = В - ехр(а*х)
и у вас есть,априори, определили, что B = 21.
Модель не является точной зависимостью между значениями y(i) и x(i), поэтому обычно добавляют погрешность e(i) и представляют модель как
у(i) = В - ехр(а*х(i)) + е(i)
Значения y, оцененные моделью для каждого из значений x, обозначаются как y'(i), где
y'(i) = B - exp(a*x(i))
Theметод наименьших квадратовстремится выбрать значение a, которое минимизирует сумму квадратов разностей между фактическими значениями y(i) и соответствующими оценочными значениями или значениями y'(i).
y(i) - y'(i) = [B - exp(a* x(i)) + e(i)] - [B - exp(a*x(i))] = e(i)
Таким образом, сумма квадратов разностей между значениями y(i) и y'(i) равна
Сумма[(y(i) - y'(i))^2] = Сумма[e(i)^2]
Правая часть представляет собой сумму квадратов ошибок, поэтому ее называют суммой квадратов ошибок илиЕСС.
Процесс преобразования значений y(i) в значения z(i) посредством
z(i) = LN(21 - y(i))
создает линейную модель
z = ах
что позволяет использовать LINEST
функцию для оценки значения «наилучшего соответствия»а. С данными, предоставленными в вашем ответе (или в вашем вопросе на math.stackexchange), это наиболее подходящее значениеасоставляет 0,147233 — тот же ответ, который вы получили в своем ответе, реализовав нормальное уравнение.
ESS, связанный с этим значениемасоставляет 8,27991. Однако это значение не является минимально достижимым значением ESS. Это происходит, когдаапринимает значение 0,149140, а соответствующий ESS равен 6,66073.
На скриншоте ниже показаны расчеты.
Оценочные значения y'(i) и соответствующие квадраты ошибок, а также значения ESS показаны для двух версий модели y = 21 - exp(a*x).
В версии 1,авыводится с использованием LINEST
подхода, основанного на преобразованной модели z = ax. В версии 2,аэто значение, которое минимизирует ESS (непреобразованной) модели. Подробнее о том, как это значениеабыла получена, приведена ниже.
С линейными моделями, такими как y = mx + c, нормальные уравнения обеспечивают удобный способ оценки значений m и c, которые минимизируют ESS. Функция LINEST
реализует (помимо прочего) нормальные уравнения.
Для нелинейных моделей (таких как y = 21 - exp(a*x)) таких удобных уравнений обычно не существует, поэтому для нахождения значения необходимо использовать другие методы.ачто минимизирует ЕСС.
Один из подходов заключается в использовании методов поиска: по сути, попробуйте ряд различных возможностей дляаи выберите тот, который даст наименьший ESS.
Это то, что показывает следующий скриншот. Он использует то, что Microsoft называетТаблица данных. Это неудачный выбор названия, поскольку таблица данных не является таблицей данных. Скорее, это инструмент для определения того, как изменяется значение расчета при изменении одного или двух элементов в расчете. Он находится на ленте в группе «Прогноз» меню «Данные», если выбрать «Таблица данных...» из значка «Анализ «Что если»».
Документация Microsoft по созданию и использованию таблиц данных довольно ужасна, поэтому я предоставлю небольшой рецептурный подход.
- Сама таблица данных представлена в диапазоне N2:O23.
- Ячейка
O2
содержит вычисленное значение для исследования. Эта ячейка содержит формулу=J4
, которая является ESS, связанной со значениемав ячейкеJ3
. - Возможности для различных значенийапомещаются в диапазон
N3:N23
и результаты ESS появятся в ячейкахO3:O23
. Это обеспечивает 21 возможное значениеа. Это произвольный выбор, таблицы данных могут включать большее или меньшее количество возможных значений. - Однаждыазначения установлены, выберите диапазон
N2:O23
и откройте диалоговое окно «Таблица данных», выбрав «Таблица данных...» из значка «Анализ «Что если»» в группе «Прогноз» меню «Данные». - В диалоговом окне введите
$J$3
в поле «Ячейка ввода столбца:» и нажмите кнопку «ОК». - Диапазон
O3:O23
теперь будет заполнен значениями ESS, соответствующими значениямавN3:N23
. Изменение любого из значенийN3:N23
обновит значения ESS вO3:O23
.
TheаЗначения N3:N23
задаются с помощью формул, а не вводятся вручную. Значения задаются с использованием стратегии поиска, которая просматривает все более тонкие наборы значений дляа.
21-йаЗначения в N3:N23
основаны наЦентральныйзначение в позиции 11 - ячейка N13
- с ячейками выше и ниже этого последовательно отличающимися наПриращениесумму так, чтобы весь диапазон из 21 значения был в порядке возрастания.
Стратегия поиска проходит через ряд шагов, номер шага определяется значением в ячейке O1
.
На шаге 1,Центральныйзначение установлено на 0,15 (в ячейке R3
) иПриращениеустанавливается как 0,001 (в ячейке S3
), давая значения в N3:N23
диапазоне от 0,14 до 0,16. Этот диапазон выбирается на основе значения версии 1а, с ожиданием, что минимальное значение ESS будет попадать в этот диапазон.
Это доказывает, что это так. Для 21 значенияаначиная с 0,14 и увеличиваясь на 0,001 до 0,16, соответствующие значения ESS начинаются с более чем 39 (когдаасоставляет 0,14), уменьшаются по мереаувеличивается до тех пор, покааимеет значение 0,149 (когда ESS равен 6,66972), а затем увеличивается, достигая значения ESS более 70, когдаасоставляет 0,16. Это показывает, что значениеачто минимизирует ESS, составляет около 0,149.
(Если бы не оказалось, что минимум находится в пределах диапазона значений, все значения ESS либо увеличились бы, либо уменьшились, поместив минимум на один конец диапазона. В этом случаеЦентральныйзначение (в ячейке R3
) потребует корректировки с возможным увеличениемПриращениезначение (в ячейке S3
) до тех пор, пока не будет найдено минимальное значение в середине диапазона.)
Для любого диапазона значений в N3:N23
ячейки O27
и N27
соответственно определяют минимальное значение ESS и значениеакоторый производит минимум.
Значениеапроизводя минимум, обеспечивает новыйЦентральныйзначение для следующего шага поиска. НовыйПриращениеэто предыдущее значение, уменьшенное в 10 раз. Эти новыеЦентральныйиПриращениезначения вводятся вручную в «контрольную таблицу» в столбцах R
и S
номер шага вручную увеличивается на 1 в ячейке O1
.
Поиск осуществляется посредством последовательных шагов и завершается, когда не удается добиться практического снижения значения ESS.
На скриншоте показаны результаты на втором этапе поиска.
решение2
Вот как это будет выглядеть в Excel:
Я попытался описать это псевдокодом:
- Для каждой записи в наборе вычислите x*LN(21-y). Вычислите сумму этих значений (назовем ее «сумма 1»).
- Для каждой записи в наборе вычислите x^2. Вычислите сумму этих значений (назовем ее «сумма 2»).
- Разделите сумму 1 на сумму 2.
От коллеги:
япредставляет собой конкретное наблюдение. Все эти расчеты предполагают, что есть фиксированное число, обычно называемоен, пары наблюдений. Например, у вас было 20 пар наблюдений в ваших данных. Здесь пары означаютИксиузначение вместе, обычно обозначается как
(x, y), (0, 20), (1, 20)....(20, 2)
.япредставляет собойя'th пара наблюдений среди всехнпары.Так что еслия= 1, это означает, что мы имеем в виду первую пару,
(0, 20)
. Еслия= 14, берем 14-ю пару,(14, 12)
. В общем, математически,я-я пара наблюдений - это(xi, yi)
,янаходится в нижнем индексе.Знак сигмы, который говорит:я= 1 кн, по сути означает, что мы берем все пары наблюдений, начиная с первого наблюдения и до последнего.