Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Коэффициенты линии тренда. Продолжение
 
#1
11.02.2013 08:03:04
Здравствуйте! В этой теме http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=13249 был решен вопрос о вычислении коэффициентов линии тренда.

Но я столкнулся с более сложной задачей.

Постановка задачи в общем-то таже - вычислить коэффициенты линии тренда полиномы второго порядка. Но особенность в том, что при построении этой линии была отмечена галочка "пересечение кривой с осью Y в точке 0,0". И как получить эти коэффициенты в этом случае не понятно.

К примеру, в прикрепленном файле построены два разных графика для одних и тех же исходных данных. В первом случае - использовалась стандартная линия тренда, во втором случае - линия тренда, проходящая через заданную точку (0;0). В разобранной ранее теме вычисляются коэффициенты для первого случая, но как быть во втором случае?
 
 
 
#2
11.02.2013 09:13:27
Добрый день,

В функции ЛИНЕЙН() есть третий аргумент, который для Вашего случая должен быть равен нулю, а точнее равен значению ЛОЖЬ().

Приложил такой вариант.

В справке по функции ЛИНЕЙН() этот аргумент назван Конст, про него там написано:
Конст  — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.
Если аргумент «конст» имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.
Если аргумент «конст» имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.
 
 
 
#3
11.02.2013 09:40:47
Нужно обновить уравнение тренда во втором случае, оно следующее y=0,858x2+1,056x.
Но вычисленные коэффициенты в предложенном файле не совпадают с графиком, a=0,519 почему-то
 
 
 
#4
11.02.2013 09:46:52
может быть для третьего выражения нужно оставить так:
=ИНДЕКС(ЛИНЕЙН($C$3:$C$9;$B$3:$B$9^{1;2});1;1)

это конечно мое предположение, хотелось бы найти этому объяснение.
и еще небольшой вопрос - данный метод вычисляет коэффициенты без потери точности?
 
 
 
#5
11.02.2013 09:47:46
Цитата
Tim1 пишет:
Нужно обновить уравнение тренда во втором случае, оно следующее y=0,858x2+1,056x.
Обновил, ничего не поменялось  в Excel2007, на диаграмме уравнение: y = 0.5195x2 + 1.056x
Какая у Вас версия Excel?
 
 
 
#6
11.02.2013 09:56:39
в меню пуск написано 2007, позволяет сохранять в формате .xlsx
прикинул коэффициенту, 0,519 действительно должно подходить. тогда что у меня с экселем, даже новую диаграмму построил
 
 
 
#7
11.02.2013 10:11:13
Не знаю, что у Вас с Excel, может быть он не обновленный.
Установите последнее обновления SP3 для Excel 2007, скачать можно отсюда:
Пакет обновления 3 (SP3) для выпуска 2007 набора приложений Microsoft Office
 
 
 
#8
11.02.2013 10:11:42
Владимир! Большое Вам спасибо! Я подсчитал по формуле, коэффициенты соответствуют графику.
И если можно еще пару вопросиков:
1. Данный метод высчитывает коэффициенты без потери точности?
2. Что можете посоветовать по поводу того, что у меня на графике отображается все-таки 0,858 вместо 0,519 (даже файл новый создал), ато хотелось бы приложить график в отчет.
 
 
 
#9
11.02.2013 10:29:48
Цитата
1. Данный метод высчитывает коэффициенты без потери точности?
2. Что можете посоветовать
Ответы такие:
1. Формулы в ячейках C11:C13 считают с максимально возможной для Excel точностью с использованием 15 значащих разрядов. Установите для этих ячеек формат числовой с числом десятичных знаком равным 15, чтобы увидеть это. Коэффициенты же диаграммы - округленные, но и там можно увидеть больше разрядов, для этого на подписи с уравнением нужно кликнуть правой кнопкой, выбрать "Формат подписи линии тренда", установить числовой и увеличить число десятичных знаков.
2. Ответил, что смог, в предыдущем сообщении - попробуйте установить SP3.
А для отчета пока можете в подписи уравнения тренда диаграммы вручную вписать коэффициенты из посчитанных ячеек.

Приложил обновленный файл с проверкой полученного уравнения тренда вблизи нуля
Изменено: ZVI - 11.02.2013 10:42:41
 
 
 
#10
11.02.2013 10:37:35
Обновил excel до SP3, отображается правильно, проблема решена!

Походу дня возник еще один интересный вопрос - линия тренда ограничивается исходными данными. Можно ли увеличить интервал ее отображения?
Изменено: Tim1 - 11.02.2013 18:03:10
 
 
 
#11
12.02.2013 03:05:39
Что SP3 помог - это радует.
Расширить интервал тренда можно, добавив вручную точки X и вписав формулу Y по посчитанным коэффициентам, пример формул для Y  смотрите в ячейках C17:C19  файла Example2.xlsx
 
 
 
#12
12.02.2013 14:07:46
Добавил новые точки, проверил - они не повлияли на уравнение.

Впринципе можно сделать новый график, где отметить исходные точки и здесь же построить график по уравнению тренда в нужном интервале. Можно сделать их разным цветом.

Думаю тему можно закрывать, разобрали ряд интересных вопросов. Спасибо Владимиру Захарову (ZVI)!
 
 
 
#13
11.09.2017 14:26:46
Подниму тему, как вычислить коэффициент детерминации (R2) в случае пересечения линией тренда точки (0;0) (например для линейной регрессии). Если взять исходные данные для Х и У из файла example2 (который скинул уважаемый ZVI чуть выше) и построить по этим точкам линейную регрессию с пересечением в точке (0;0). То на графике будет формула y=3,94x, а коэффициент R2=0,84. И с вычислением коэфициента А (3,94) никаких проблем. =ИНДЕКС(ЛИНЕЙН(y;x;0;1);1;1)=3,9392. А вот с коэффицентом детерминации R2 беда, формула =ИНДЕКС(ЛИНЕЙН(y;x;0;1);3;1) выдает 0,96 а на графике 0,84. Как быть?
 
 
 
#14
11.09.2017 21:52:20
Цитата
sayk2 написал:
Как быть?
Взять и рассчитать:
 
 
 
#15
12.09.2017 10:01:09
С.М., спасибо!
 
 
 
Страницы: 1
Читают тему
Наверх