Подгонка кривой в excel (с примерами)

Часто вам может потребоваться найти уравнение, которое лучше всего соответствует кривой для набора данных в Excel.

К счастью, это довольно просто сделать с помощью функции Trendline Excel.

В этом руководстве представлен пошаговый пример того, как подогнать уравнение к кривой в Excel.

Шаг 1. Создайте данные

Во-первых, давайте создадим поддельный набор данных для работы:

Шаг 2. Создайте диаграмму рассеяния

Далее давайте создадим диаграмму рассеяния для визуализации набора данных.

Сначала выделите ячейки A2:B16 следующим образом:

Затем нажмите вкладку «Вставка» на верхней ленте, затем выберите первый вариант графика в разделе «Разброс» :

В результате получается следующая диаграмма рассеяния:

Шаг 3. Добавьте линию тренда

Затем щелкните в любом месте диаграммы рассеяния. Затем нажмите на знак + в правом верхнем углу. В раскрывающемся меню щелкните стрелку рядом с надписью «Линия тренда» , а затем выберите «Дополнительные параметры» :

В появившемся справа окне нажмите кнопку рядом с Polynomial . Затем установите флажки рядом с «Показать уравнение на диаграмме» и «Показать значение R-квадрата на диаграмме» .

Это создает следующую кривую на облаке точек:

Уравнение кривой выглядит следующим образом:

у = 0,3302х ² – 3,6682х + 21,653

R-квадрат показывает нам процент вариации переменной ответа , который можно объяснить переменными-предикторами. Квадрат R для этой конкретной кривой равен 0,5874 .

Шаг 4: выберите лучшую линию тренда

Мы также можем увеличить порядок используемого нами полинома, чтобы увидеть, обеспечивает ли более гибкая кривая лучшее соответствие набору данных.

Например, мы могли бы установить порядок полинома равный 4:

В результате получается следующая кривая:

Уравнение кривой выглядит следующим образом:

у = -0,0192х ⁴ + 0,7081х ³ – 8,3649х ² + 35,823х – 26,516

R-квадрат для этой конкретной кривой равен 0,9707 .

Этот R-квадрат значительно выше, чем у предыдущей кривой, что указывает на то, что он гораздо точнее соответствует набору данных.

Мы также можем использовать это уравнение кривой для прогнозирования значения переменной отклика на основе переменной-предиктора. Например, если x = 4, мы бы предсказали, что y = 23,34 :

у = -0,0192(4) ⁴ + 0,7081(4) ³ – 8,3649(4) ² + 35,823(4) – 26,516 = 23,34

Дополнительные руководства по Excel вы можете найти на этой странице .