Точность Excel Trendline

5056
Rook

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

Пример:

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

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

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

4
@ DMA57631 - Правка - это может быть, но следует учитывать, что эти показатели теперь настолько малы, что даже на более низких разрешениях их трудно читать / различать. Кроме того, я не думаю, что кто-то здесь неправильно понял первоначальный смысл. Rook 13 лет назад 0

3 ответа на вопрос

7
DMA57361

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

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

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

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

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

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

y = -0.00017256831201215700x³ + 0.03410741673273060000x² - 1.89794238802443000000x + 128.73257845634200000000
+1 за рассказ об очень важном варианте (количество мест). Теперь, W_Whalley рассказал первую часть истории, и я не буду брать с него + A, но просто чтобы вы знали ... Я считаю это равномерным или даже более полезным ответом. Rook 13 лет назад 0
3
W_Whalley

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

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

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

Это ошибка округления в процессе или просто в отображении результатов (коэффициентов)? Если да, есть ли способ увеличить число значащих цифр, чтобы они лучше соответствовали своему назначению (для этого они не только непригодны для использования, но и ненадежны для будущего использования)? Rook 13 лет назад 0
У меня нет Excel для тестирования. Я подозреваю последнее. Возможно, вы сможете найти способ получить более точные коэффициенты из Excel. Я вижу ссылки Google на надстройку для анализа данных или пакет инструментов анализа для Excel. W_Whalley 13 лет назад 0
1
Toc

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

Я решил нужную мне проблему (написал для нее свою собственную программу), но да, я знал и о вашем подходе. Мне это не нравится, в основном потому, что мне нужно полагаться на регрессию, а не проверять ее каждый раз. +1, однако, только для предложения (и это хорошее предложение). Rook 13 лет назад 0

Похожие вопросы