Точность Excel Trendline

Question

Это проблема, с которой я сталкиваюсь время от времени, и она очень меня раздражает, потому что я всегда должен перепроверять каждую полученную линию тренда.

Пример:

r       L
(mm)    
30,00   97,0  
60,00   103,2  
90,00   106,0  
110,00  101,0  
125,00  88,0  
140,00  62,0  
148,00  36,7  
152,50  17,0   

После построения линии тренда (с использованием типа полиномиальной регрессии 3-го порядка) с r на оси x и L на оси y Excel выдаст формулу

y = -0,0002x³ + 0,0341x² - 1,8979x + 128,73

с R² = 0,994. Если я интерполирую значения, используя эту формулу для тех же значений r, что и значения, из которых была получена формула, я получаю

r   y  
(mm)      
30,00   97,083  
60,00   94,416  
90,00   88,329  
110,00  66,371  
125,00  33,68  
140,00  -17,416  
148,00  -53,5912  
152,50  -76,97725  

которые совсем разные?

Почему это происходит? В чем причина?

Answer 1

Как обсуждал W_Whalley , это потому, что Excel округляет значения, отображаемые в формуле, исправление состоит в том, чтобы просто изменить формат отображения для метки, и вот как:

1. Создайте график, добавьте линию тренда, сделайте метку уравнения видимой.

2. Щелкните правой кнопкой мыши метку уравнения и выберите Формат меток данных ...

3. На вкладке Number выберите тип Number и введите желаемое количество десятичных знаков.

4. Закройте окно Формат.

Вот результат установки количества знаков после запятой в 20 (например) для данных вашего примера, с добавлением разрыва строки, чтобы избежать прокрутки:

y = -0.00017256831201215700x³ + 0.03410741673273060000x²
                  - 1.89794238802443000000x + 128.73257845634200000000

Answer 2

Похоже, что формула, которую дал Excel, имеет округленные коэффициенты. Используя подпрограмму вычисления OpenOffice для регрессии, я получаю эту формулу, которая намного лучше соответствует данным:

y=-0.00017257x³+0.034107417x²-1.89794239x+128.7325785

Поскольку член x³ настолько велик, небольшая разница в коэффициенте оказывает большое влияние на прогнозируемый результат.

Answer 3

Если вы не можете самостоятельно вывести коэффициенты регрессии, вы можете просто заменить значения r на значения r, деленные на 10. То есть: 30 становится 3, 60 становится 6 и так далее. Вы поймете, что Excel пересчитывает коэффициенты более точно, потому что он будет использовать более значимые цифры.