Построение и форматирование линий трендов

Построение и форматирование графиков, линий трендов.

Графики.

Для построения графика необходимо проделать следущие шаги:

1. На вкладке «Вставка» выберите график с линиями(рис. 6). И выберите шрафик, в верхнем левом углу(рис. 7).

Рис. 6

Рис. 7

2. После того, как вы проделаете шаг выше, у вас появится область для построения графика. Теперь вам необходимо выбрать данные, по которым будете строить график. Щёлкните пр. кнопкой мыши по области построения графика и выберите пункт «Выбрать данные»(рис. 8).

Рис. 8

3. Должно появиться диалоговое окно выбора данных. В этом окне нажмите кнопку «Добавить»(рис. 9).

Рис. 9

 

4. Должно появиться диалговое окно для выбора названия и значений данных. В первом поле укажите название индикатора, щёлкнув по ячейке с названием индикатора(рис. 10). Во втором поле оставьте только «=»(рис. 11) и выберите строку с числами по данному показателю(рис. 12). Внимание! Выделяем значения начиная с непустых ячеек.

Рис. 10

Рис. 11

Рис. 12

 

5. После того, как вы проделаете пункты 1 – 4, в окне из шага данных должны появиться в правой колонке – значения горизонтальной оси (от 1 и т.д), а в левой - название индикатора.(пример - рис. 13).

Рис. 13

6. Теперь необходимо сделать так, чтобы на горизонтальной оси отображались года, а не числа, начиная с 1. Для этого нажмите кнопку «Редактировать»(«Изменить») (рис. 14). И выберите ось с годами (рис. 15).Внимание! Выбираем начиная с того года, которому соответствует непустая ячейка показателя.

Рис. 14

Рис. 15

7. Теперь диалоговое окно выбора данных имеет следующий вид (пример – рис. 16)

Рис. 16

8. После всего вышеперечисленного нажимаете кнопку «ОК» и та-да! График построен. (пример – рис. 17).

Рис. 17

Построение и форматирование линий трендов.

Теперь, когда у нас есть сам график, до полного счастья осталось добавить линию тренда. Приступим.

1. Пр. кнопкой мыши щёлкните по линии графика и выберите пункт «Добавить линию тренда»(рис. 18).

Рис. 18

2. Появится диалоговое окно форматирования линии тренда (рис. 19).

Рис. 19

 

При построении линии тренда нас будут интересовать пункты, находящиеся в красных прямоугольниках. В первом выбирается тип линии тренда.

Возвращаясь к нашему заданию, напомню, что по одному индикатору надо построить все виды линий трендов, кроме последнего (Примечание: полиномиальный тренд можно выбирать до 6 степени, т.е строить надо будет начиная со 2-ой по 6-ую степень). Выберем линейный тренд (рис. 19).

Во втором прямоугольнике указываются параметры для прогнозирования. Установите поле «Вперёд» на значение 3 (рис. 20).

В третем прямоугольнике необходисо поставить галочки для вывода уравнения линии тренда и коэффициента R^2 (рис. 20).

Рис. 20

3. Нажимаем кнопку «Закрыть». В результате должна быть построена линия тренда с уравнением и коэфф. R^2 (пример – рис. 21).

Рис. 21

4. Последний штрих – приведём значения коэффициентов линейного уравнения в более читабельный вид. Щёлкаем пр. кнопкой мыши по полю с уравнением линии и выбираем пункт «Форматирование заголовка линии тренда» (рис. 22). И выбираем тип – числовой (рис. 23). Нажимаем кнопку «Закрыть».

Рис. 22

Рис. 23

Теперь коэффициенты отображаются в более читабельном формате. Пожалуйста, всё выражение с уравнением линии в области построения расположите так, чтобы было удобно смотреть коэффициенты уравнения.

Таким образом мы выполнили часть задания с построением линий трендов. Вам осталось по каждому идентификатору построить отдельные графики с другими видами линий трендов (рис. 19 первый прямоугольник). Т.е на один идентификатор должно быть построено 9 графиков, которые различаются видами линий трендов, каждый из которых продён на 3 шага. Графики расположите в том же листе, где лежат ваши данные. Лист назовите «Графическая ч.»

«Численное прогнозирование»

Графики, конечно дело замечательное, но хотелось бы иметь и числовые показатели. Фактически, занимаемся тем же построением линий трендов, но будем работать через формулы. Пару слов о теории. Для линейного тренда испольуется сл. уравнение: . Первый шаг для выполнения задания заключается в нахождении коэффициентов a1 и a0. Конечно, можете вручную их найти, благо инфы в интернете на этот счёт полно, но зачем лишний раз заморачиваться, когда есть более простой способ. Напомню, что Александр Данилович рассказывал про такую функцию в Excel, которая называется ЛИНЕЙН() (LINEST()). Вот её и будем использовать для нахождения коэффициентов a1 и a0.

Шаг 1.

Создайте ещё один лист, назовите его «Численная ч.». Скопируйте в него первые 2 строчки из листа «Графическая ч.» (рис. 24). Рис. 24

Теперь в ячейке D3 напишите большими буквами «ЛИНЕЙНЫЙ». В ячейке E3 напишите «а1», в ячейке F3 напишите «а0». В ячейку E4 записываем функцию ЛИНЕЙН. Для того,чтобы получить значения коэффициентов a1 и a0 делаем следущее:

1. В ячейке E4 пишем =ЛИНЕЙН(

2. В качестве первого аргумента выделяем значения скопированного идентификатора (рис. 25). Внимание! Выделять данные начиная с непустых ячеек.

Рис. 25

3. Ставим запятую, ставим 1, ставим запятую, стави 0, закрываем скобку. Пример: =ЛИНЕЙН(E2:BI2,,1,0). Жмём enter

4. В ячейке E4 должно появиться значение коэффициента a1. Однако нам не хватает коэффициента a0. Для этого выделяем ячейку E4 и соседнюю F4. Нажимаем клавишу F2 и затем нажимаем Ctrl+Shift+Enter. Теперь в ячейку F4 запишется значение коэффициента a0.

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

Шаг 2.

Теперь ищем значения для аппроксимирующей линии. Для этого надо просто подставить в выражение все имеющиеся данные. Для этого делаем следущее:

1. В ячейке D5 пишем большими буквами «ПРОГНОЗ».

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

3. В ячейку, пятой строки, в столбце, начиная с которого идут непустые ячейки пишем следущее выражение y = $E$5 * E2 + $F$5.(Это пример!)

Примечание: $E$5 E5 – ячейка, в которой лежит значение коэффициента a1, $F$5 F5 – ячейка, в которой лежит значение a0. E2 – непустая ячейка с первым числом для показателя. Запись ячеек с долларами используется в случаях, когда значение ячейки при растягивании формулы, не должно переходить на соседнюю ячейку. Т.е коэффициенты a1, a0 при вычислении не изменяются, тогда как значения х изменяются.

4. Нажимаете enter, теперь в ячейке лежит значение. После наводите курсор на нижний правый угол ячейки(появится крестик) и протягиваете до 2011 года. В годах добавьте 2012,2013,2014,2014,2015,2016 (соответвенно строчку ниже протяните тоже).

5. Вернитесь в только что вычесленным дынным, выделите их и протяните до 2016 года.

6. В итоге у вас получится нчето подобное (рис. 26).

Рис. 26

Для линейного тренда всё проделали.

Как быть с нелинейными трендами.

Опять же возвращаясь к лекциям, Александр Данилович объяснял что нелинейные уравнения, сводятся к линейному ().

Рассмотрим пример. Пусть мы взяли экспоненциальную линию тренда. Её уравнение:

,где e – экспонента

Прологорифмируем обе части уравнения:

,

Разложим логарифм произведения на сумму логарифмов по свойству перемножения аргументов логарифма:

,

Воспользуемся ещё одним свойством логарифма, а именно вынесем степень в правой части перед логарифмом:

,

Т.к аргумент логарифма равен основанию, получаем следующее уравнение:

,

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

.

Казалось бы, теперь, как и в предыдущем примере берём функцию линейн() и проделываем все те же шаги. Но, нет. Т.к мы использовали замену переменной, неоьходимо предварительно прологорифмировать значения показетеля и прологорифмировать коэффициент a0. Let’s do it!

1. В столбце D в пустую ячейку, под «ПРОГНОЗ» пишем «ЭКСПОНЕНЦИАЛЬНЫЙ»,в соседние ячейки пишем a1,a0,e^(a0).

2. Через строчку в столбце D пишем LN(Indecator).

3. Ниже пишем «ПРОГНОЗ».

4. В ячейке строки с LN(Indecator) начиная с года, под которым идёт непустая ячейка индикатора,пишем формулу натурального логарифма(поищите её сами) ставим ячейку с которой начинаются непустые значения и после протягиваем до 2011 года.

5. Теперь уже по этим данным применяем функцию ЛИНЕЙН().(Получаем значение a1 и a0). В ячейку под e^(a0) пишем формулу возведения экспоненты в степень,в который передаём значение a0.

6. Теперь по всем этим данным считаем тренд. Начальная формула . Все коэффициенты найдены,осталось сделать подстановку.Думаю тут трудностей возникнуть не должно.

Ах, да. Данные по отдельным трендам выделите рамочкой, причём каждый вид тренда своим цветом, названия, которые относятся к трендам – жирным, коэффициенты - курсивом. В итоге должно получиться нечто подобное:

Sooooooo...вам осталось прикинуть, как по каждому показателю вычислить все виды трендов.(т.е помимо линейного и экспоненциального ещё логарифмический, полиномиальный,степенной. Вообщем, всё тоже самое, что и графиками, просто тут работа с формулами и высислениями J. Даю подсказку – в функции линейн вторым параметром передаются значения x, это понадобится при вычислении остальных уравнений.