Минимальная точка Полиномиальная кривая

Question

Я пытаюсь найти минимальное значение, которого компоненты x и y достигают на графике (A). Я знаю, что вы можете выполнить это с помощью IgorPro, но я хотел бы знать, как решить эту проблему с помощью Excel.

Участок был построен с использованием следующих точек данных:

110 0.177
115 0.093
120 0.033
125 0.006
130 0.007
135 0.04
140 0.101
145 0.186
150 0.272

Как вы можете видеть, существует минимальная точка данных x и y. Тем не менее, я хочу найти минимальную точку, которую достигает полиномиальное соответствие.

Answer 1

Координаты минимума квадратичной линии тренда при заданном уравнении в виде y(x)=a*x^2+b*x+c вычисляются как: x=-b/2/a , y=c-b^2/4/a .

График для предоставленных точек данных выглядит так

и уравнение функции тренда на графике Excel гласит:

y=0.0005*x^2-0.1383*x+8.8197

Таким образом, получается, что a=0.0005 , b=-0.1383 и c=8.8197 . Давайте найдем минимум тогда. Результаты расчетов в:

x=138.3, y = -0.743745000. 

Чего ждать? Но линия тренда явно выглядит выше и все позитивно! Что происходит?! Давайте нарисуем график этой функции:

Он сдвинут вниз, полностью пропуская точки данных! И координаты минимума верны, но для этой сдвинутой функции, а не той, которая нам нужна.

Корни проблемы здесь скрываются в деталях. Уравнение, показанное на графике, имеет округленные коэффициенты. Округлено до четырех знаков после запятой.

"Four decimal places would be enough for everyone (c)", right?

Видимо, нет, особенно когда x>1 , не говоря уже о x>100 с

С 9 знаками после запятой коэффициенты:

a=0.000542468, b=-0.138278225, c=8.819723377,

участки выглядят хорошо:

и рассчитанные координаты минимума тоже выглядят разумно:

x = 127.4528866, y = .007743909.

Намного лучше!

Но как мы можем получить этот более точный минимум?

Вот простой способ, основанный на матричных функциях MINVERSE() и MMULT() .

Предполагая, что X , Y находятся в B7:B15 , давайте заполним матрицу 3x4 в E7:H9 ,

начиная с трех ячеек E7: =COUNT(B7:B15) , F7: =SUM(B7:B15) , H7: =SUM(C7:C15) .

Каждая формула в следующих пяти ячейках, G7 , G8 , G9 , H8 , H9 , должна быть введена как формула массива (вместо Ctrl+Enter введите Ctrl+Shift+Enter):

G7:

=SUM(B7:B15^2)

G8:

=SUM(B7:B15^3) 

G9:

=SUM(B7:B15^4)

H8:

=SUM(B7:B15*C7:C15)

H9:

=SUM(B7:B15^2*C7:C15)

Блок E8:F9 завершает симметричную матрицу 3x3 в E7:G9:

E8:

=F7

скопируйте E8 чтобы заполнить E8:F9 .

Выберите F11:F13 и введите следующую формулу массива

= МУМНОЖ (МОБР (Е7:G9), Н7: Н9)

который вычисляет желаемые более точные значения

c= 8.81972337662,
b=-0.13827822511,
a= 0.00054246753.

Примечание: также лучше сначала преобразовать исходный диапазон m<=X<=M в единичный интервал 0<=x<=1 , найти координаты минимума и затем преобразовать x обратно в X