Нормальное уравнение в Excel (статистика)

Question 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.

На скриншоте показаны результаты на втором этапе поиска.

Answer