Линия тренда в Excel – ЭКСЕЛЬ ХАК

Линию тренда часто используют в Excel при прогнозировании. В основном, при построении графиков. Она помогает прогнозировать.

Добавление линии тренда на график

Для примера возьмем средние цены на нефть с 2000 года из открытых источников. Данные для анализа внесем в таблицу:

Нефть.

  1. Построим на основе таблицы график. Выделим диапазон – перейдем на вкладку «Вставка». Из предложенных типов диаграмм выберем простой график. По горизонтали – год, по вертикали – цена.График.
  2. Щелкаем правой кнопкой мыши по самому графику. Нажимаем «Добавить линию тренда».Добавить тренд.
  3. Открывается окно для настройки параметров линии. Выберем линейный тип и поместим на график величину достоверности аппроксимации.Параметры.
  4. На графике появляется косая линия.

Косая.

Линия тренда в Excel – это график аппроксимирующей функции. Для чего он нужен – для составления прогнозов на основе статистических данных. С этой целью необходимо продлить линию и определить ее значения.

Если R2 = 1, то ошибка аппроксимации равняется нулю. В нашем примере выбор линейной аппроксимации дал низкую достоверность и плохой результат. Прогноз будет неточным.

Внимание!!! Линию тренда нельзя добавить следующим типам графиков и диаграмм:

  • лепестковый;
  • круговой;
  • поверхностный;
  • кольцевой;
  • объемный;
  • с накоплением.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Постановка задачи

Исходные данные

Для начала, давайте определимся, какие у нас есть исходные данные и что нам нужно получить на выходе. Фактически, все что у нас есть, это некоторые исторические данные. Если мы говорим о прогнозировании продаж, то историческими данными будут продажи за предыдущие периоды.

Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.

Составляющие прогноза

Следующий шаг: давайте определимся, что нам нужно учесть при построении прогноза. Когда мы исследуем наши данные, нам необходимо учесть следующие факторы:

  • Изменение нашей пронозируемой величины (например, продаж) подчиняется некоторому закону. Другими словами, в временном ряде можно проследить некую тенденцию. В математике такая тенденция называется трендом.
  • Изменение значений в временном ряде может зависить от промежутка времени. Другими словами, при построении модели необходимо будет учесть коэффициент сезонности. Например, продажи арбузов в январе и августе не могут быть одинаковыми, т.к. это сезонный продукт и летом продажи значительно выше.
  • Изменение значений в временном ряде периодически повторяется, т.е. наблюдается некоторая цикличность.

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

Примечание. Не обязательно все три элемента регулярной составляющей должны присутствовать в временном ряде.

Однако, помимо регулярной составляющей, в временном ряде присутствует еще некоторое случайное отклонение. Интуитивно это понятно — продажи могут зависеть от многих факторов, некоторые из которых могут быть случайными.

Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.

Виды моделей

Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”

Обычно выделяют два основных вида:

  • Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
  • Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения

Иногда также выделают смешанную модель в отдельную группу:

  • Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения

С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать?»

Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.

Пример:

график пример адаптивной и мультипликативной модели

Что это такое

График показывает, как одни величины зависят от других. Информация легче воспринимается. Посмотрите визуально, как отображается динамика изменения данных.

Таблица соответствий

Таблица – сохранённые в памяти точки графика, позволяющие найти значение по соответствующему сигналу. С таблицей есть два варианта:

  • Двухмерная таблица. Один столбец хранит сигнал, второй – соответствующее ему значение. Для сложного графика можно оптимизировать размер таблицы, сделав меньше точек на прямых участках, и больше – на участках сложной формы. Заполняется такая таблица последовательно с любым шагом, потому что в дальнейшем мы будем делать поиск по таблице. Чем больше таблица, тем дольше будет происходить поиск дальних значений (речь идёт о единицах микросекунд, не более).
  • Одномерная таблица. Такая таблица заполняется с равным шагом по оси сигнала, а находятся в ней значения. Доступ к таблице осуществляется через преобразование сигнала к номеру ячейки, что позволяет получить одинаковое и минимальное время поиска для таблицы любого размера (фактически это не поиск, а выбор конкретной ячейки). Такой тип таблицы использует в два раза меньше памяти, чем двумерная таблица, а также имеет максимальную скорость доступа. Для критичных ко времени преобразований следует отдать предпочтение этому способу, ведь он будет даже быстрее, чем аппроксимация функцией.

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

  • Таблица, очевидно, будет занимать гораздо больше места в памяти МК. Таблицу можно и нужно разместить в PROGMEM – постоянной памяти, об этом мы говорили в соответствующем уроке и я покажу это ниже здесь.
  • Табличный способ позволяет оцифровать график абсолютно любой формы. В отличие от аппроксимации функцией, его не нужно будет исследовать и разбивать на части, описываемые простейшими функциями.
  • Чем сильнее мы раздробим график, то есть чем больше точек будет в таблице, тем точнее будет определение “значения” и тем меньше будет его минимальный шаг (см. картинку ниже). Занимаемый таблицей объём памяти также увеличится.

Начнём с простой двухмерной таблицы и поиска по ней.

Простая таблица

Таблицей в программе будет являться двумерный массив, давайте создадим его для второго графика с таким же шагом, как на предыдущем скриншоте справа (приблизительно в три раза меньше точек, чем в изначальной таблице, т.е. понизим разрешение оцифровки):

int table[][2] = { {145, 35}, {156, 38}, {170, 41}, {186, 44}, {200, 47}, {212, 50}, {224, 53}, {233, 56}, {240, 59}, {246, 62}, {253, 65}, {259, 68}, {264, 71}, {268, 74}, {269, 77}, {276, 80}, {298, 83}, {327, 86}, {410, 89},};

Для поиска по таблице достаточно просто перебирать все ячейки, начиная с первой. Если сигнал в следующей ячейке будет больше, чем текущий сигнал, считаем текущую ячейку искомой:

int getVal(int signal) { // поиск for (int i = 0; i < tableSize – 1; i++) { // если сигнал в следующей ячейке больше – // вернуть значение в текущей ячейке if (table[i + 1][0] > signal) return table[i][1]; } // вернуть последний, если вылетели за диапазон return table[tableSize – 1][1];}

Результат работы функции для нашего диапазона сигналов: Вот такая получается ступенчатая конструкция, для уменьшения погрешности нужно брать больше точек, или… заменить ступеньки между точками линейным отрезком, вот так: Делается это очень просто, просто добавляем map() между соседними точками:

int getVal(int signal) { if (signal < table[0][0]) return table[0][1]; // поиск for (int i = 0; i < tableSize – 1; i++) { // если сигнал в следующей ячейке больше – // вернуть значение по линейному отрезку между точками if (table[i + 1][0] > signal) return map(signal, table[i][0], table[i + 1][0], table[i][1], table[i + 1][1]); } // вернуть последний, если вылетели за диапазон return table[tableSize – 1][1];}

Результат (примечание: ступеньки по вертикальной оси справа связаны с целочисленным вычислением значения, т.е. шаг ступеньки там – единица): Лучше? Лучше! Таким образом можно добиться максимальной близости к реальному графику при помощи минимального количества точек в таблице.

Прячем в PROGMEM

Для хранения массива в программной памяти достаточно добавить ключевое слово PROGMEM при объявлении, а также сделать функцию или макрос, которая будет доставать данные из pgm:

const int tableSize = 19;const int table[][2] PROGMEM = { {145, 35}, {156, 38}, {170, 41}, {186, 44}, {200, 47}, {212, 50}, {224, 53}, {233, 56}, {240, 59}, {246, 62}, {253, 65}, {259, 68}, {264, 71}, {268, 74}, {269, 77}, {276, 80}, {298, 83}, {327, 86}, {410, 89},};void setup() { Serial.begin(9600); for (int i = 100; i < 430; i += 5) { Serial.print(i); Serial.print(‘t’); Serial.println(getVal(i)); }}#define pgm_table(x, y) pgm_read_word(&table[(x)][(y)])int getVal(int signal) { if (signal < pgm_table(0, 0)) return pgm_table(0, 1); // поиск for (int i = 0; i < tableSize – 1; i++) { // если сигнал в следующей ячейке больше – // вернуть значение в текущей ячейке if (pgm_table(i + 1, 0) > signal) return map(signal, pgm_table(i, 0), pgm_table(i + 1, 0), pgm_table(i, 1), pgm_table(i + 1, 1)); } // вернуть последний, если вылетели за диапазон return pgm_table(tableSize – 1, 1);}void loop() {}

Библиотека Approxy (NEW)

Показанный выше вариант с таблицей я обернул в библиотеку Approxy. Рассмотрим пример из неё с такими же исходными данными. Вариант с хранением в PROGMEM:

#include <Approxy.h>// двумерный массив, у столбцов одинаковый типconst int tab[][2] PROGMEM = { {145, 35}, {156, 38}, {170, 41}, {186, 44}, {200, 47}, {212, 50}, {224, 53}, {233, 56}, {240, 59}, {246, 62}, {253, 65}, {259, 68}, {264, 71}, {268, 74}, {269, 77}, {276, 80}, {298, 83}, {327, 86}, {410, 89},};Approxy2D<int, AP_PGM> table(tab, 19);void setup() { Serial.begin(9600); for (int i = 145; i < 410; i++) { Serial.println(table.get(i)); }}void loop() {}

Линейная таблица

Для создания линейной таблицы нужно производить измерения, строго придерживаясь одинакового шага по сигналу. В саму таблицу идут соответствующие значения. Пример выборки по всё тому же второму графику с шагом по сигналу 10:

140 35150 36160 39170 41180 43190 45200 47210 49220 51230 55240 59250 63260 68270 78280 80290 82300 83310 84320 85330 86340 86350 87360 87370 88380 88390 88400 88410 89

На графике это выглядит так: одинаковый шаг по оси сигнала Что теперь: мы оставляем только столбец значений и пакуем их в одномерный массив. Также запоминаем минимальный и максимальный сигнал в таблице, с его помощью будем мапить:

int table[] = { 35, 36, 39, 41, 43, 45, 47, 49, 51, 55, 59, 63, 68, 78, 80, 82, 83, 84, 85, 86, 86, 87, 87, 88, 88, 88, 88, 89,};const int minSignal = 140;const int maxSignal = 410;const int tableSize = 28;void setup() { Serial.begin(9600); for (int i = 100; i < 430; i += 5) { Serial.print(i); Serial.print(‘t’); Serial.println(getVal(i)); }}int getVal(int signal) { // ограничиваем сигнал signal = constrain(signal, minSignal, maxSignal); // ищем номер ячейки int i = map(signal, minSignal, maxSignal, 0, tableSize – 1); // возвращаем значение return table[i];}void loop() {}

И точно так же массив можно запрятать в прогмем:

int getVal(int signal) { // ограничиваем сигнал signal = constrain(signal, minSignal, maxSignal); // ищем номер ячейки int i = map(signal, minSignal, maxSignal, 0, tableSize – 1); // возвращаем значение return pgm_read_word(&table[i]);}

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

int table[] = { 35, 39, 43, 47, 51, 59, 68, 80, 83, 85, 86, 87, 88, 88,};const int minSignal = 140;const int maxSignal = 400;const int stepSignal = 20;const int tableSize = 14;void setup() { Serial.begin(9600); for (int i = 100; i < 430; i += 5) { Serial.print(i); Serial.print(‘t’); Serial.println(getVal(i)); }}int getVal(int signal) { // ограничиваем сигнал signal = constrain(signal, minSignal, maxSignal); // ищем номер ячейки // ещё -1 за счёт линеаризации! int i = map(signal, minSignal, maxSignal-stepSignal, 0, tableSize – 2); int thisMin = stepSignal * i + minSignal; int thisMax = thisMin + stepSignal; // возвращаем значение return map(signal, thisMin, thisMax, table[i], table[i + 1]);}void loop() {}

И вот так мы восстановили график значения от сигнала всего по 14 точкам в одномерном массиве, зная минимум, максимум и шаг изменения сигнала! 

СинтаксисSyntax

Expression. Линии тренда (указатель)expression.Trendlines (Index)

Expression (выражение ) Переменная, представляющая объект Series .expression A variable that represents a Series object.

Аппроксимация в Excel

ishchem-formulu.jpg(Обратите внимание на дополнительный раздел от 04.06.2017 в конце статьи.)

Учет и контроль! Те, кому за 40 должны хорошо помнить этот лозунг из эпохи построения социализма и коммунизма в нашей стране.

Но без хорошо налаженного учета невозможно эффективное функционирование ни страны, ни области, ни предприятия, ни домашнего хозяйства при любой общественно-экономической формации общества! Для составления прогнозов и планов деятельности и развития необходимы исходные данные. Где их брать? Только один достоверный источник – это ваши статистические учетные данные предыдущих периодов времени.

Учитывать результаты своей деятельности, собирать и записывать информацию, обрабатывать и анализировать данные, применять результаты анализа для принятия правильных решений в будущем должен, в моем понимании, каждый здравомыслящий человек. Это есть ничто иное, как накопление и рациональное использование своего жизненного опыта. Если не вести учет важных данных, то вы через определенный период времени их забудете и, начав заниматься этими вопросами вновь, вы опять наделаете те же ошибки, что делали, когда впервые этим занимались.

«Мы, помню, 5 лет назад изготавливали до 1000 штук таких изделий в месяц, а сейчас и 700 еле-еле собираем!». Открываем статистику и видим, что 5 лет назад и 500 штук не изготавливали…

«Во сколько обходится километр пробега твоего автомобиля с учетом всех затрат?» Открываем статистику – 6 руб./км. Поездка на работу – 107 рублей. Дешевле, чем на такси (180 рублей) более чем в полтора раза. А бывали времена, когда на такси было дешевле…

«Сколько времени требуется для изготовления металлоконструкций уголковой башни связи высотой 50 м?» Открываем статистику – и через 5 минут готов ответ…

«Сколько будет стоить ремонт комнаты в квартире?» Поднимаем старые записи, делаем поправку на инфляцию за прошедшие годы, учитываем, что в прошлый раз купили материалы на 10% дешевле рыночной цены и – ориентировочную стоимость мы уже знаем…

Ведя учет своей профессиональной деятельности, вы всегда будете готовы ответить на вопрос начальника: «Когда. ». Ведя учет домашнего хозяйства, легче спланировать расходы на крупные покупки, отдых и прочие расходы в будущем, приняв соответствующие меры по дополнительному заработку или по сокращению необязательных расходов сегодня.

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

float map()?

Для линеаризации в предыдущих примерах мы использовали функцию map(), которая возвращает целые числа. Что делать, если нужна более высокая точность? Можно работать в более мелкой шкале (например миллиметры вместо сантиметров), а можно сделать свой map, который будет считать во float:

float map_f(long x, long in_min, long in_max, long out_min, long out_max) { return (float)(x – in_min) * (out_max – out_min) / (in_max – in_min) + out_min;}

И использовать его вместо обычного при расчёте значения между точками. Вот так будет выглядеть самый последний пример с дробной линеаризацией, уже без ступенек:

Уравнение линии тренда в Excel

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

Следует выбирать тот тип отображения, который наиболее точно проиллюстрирует тенденцию изменений вводимых пользователем данных. Разберемся с вариантами.

Линейная аппроксимация

Ее геометрическое изображение – прямая. Следовательно, линейная аппроксимация применяется для иллюстрации показателя, который растет или уменьшается с постоянной скоростью.

Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:

Контракты.

На основании данных в таблице Excel построим точечную диаграмму (она поможет проиллюстрировать линейный тип):

График1.

Выделяем диаграмму – «добавить линию тренда». В параметрах выбираем линейный тип. Добавляем величину достоверности аппроксимации и уравнение линии тренда в Excel (достаточно просто поставить галочки внизу окна «Параметры»).

Опции.

Получаем результат:

Пример.

Обратите внимание! При линейном типе аппроксимации точки данных расположены максимально близко к прямой. Данный вид использует следующее уравнение:

y = 4,503x + 6,1333

  • где 4,503 – показатель наклона;
  • 6,1333 – смещения;
  • y – последовательность значений,
  • х – номер периода.

Прямая линия на графике отображает стабильный рост качества работы менеджера. Величина достоверности аппроксимации равняется 0,9929, что указывает на хорошее совпадение расчетной прямой с исходными данными. Прогнозы должны получиться точными.

Чтобы спрогнозировать количество заключенных контрактов, например, в 11 периоде, нужно подставить в уравнение число 11 вместо х. В ходе расчетов узнаем, что в 11 периоде этот менеджер заключит 55-56 контрактов.

Экспоненциальная линия тренда

Данный тип будет полезен, если вводимые значения меняются с непрерывно возрастающей скоростью. Экспоненциальная аппроксимация не применяется при наличии нулевых или отрицательных характеристик.

Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:

Электроэнергия.

Строим график. Добавляем экспоненциальную линию.

Экспоненциальная.

Уравнение имеет следующий вид:

y = 7,6403е^-0,084x

  • где 7,6403 и -0,084 – константы;
  • е – основание натурального логарифма.

Показатель величины достоверности аппроксимации составил 0,938 – кривая соответствует данным, ошибка минимальна, прогнозы будут точными.

Логарифмическая линия тренда в Excel

Используется при следующих изменениях показателя: сначала быстрый рост или убывание, потом – относительная стабильность. Оптимизированная кривая хорошо адаптируется к подобному «поведению» величины. Логарифмический тренд подходит для прогнозирования продаж нового товара, который только вводится на рынок.

На начальном этапе задача производителя – увеличение клиентской базы. Когда у товара будет свой покупатель, его нужно удержать, обслужить.

Построим график и добавим логарифмическую линию тренда для прогноза продаж условного продукта:

Продажи.

R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

Например:

Период 14 15 16 17 18 19 20
Прогноз 1005,4 1024,18 1041,74 1058,24 1073,8 1088,51 1102,47

Для расчета прогнозных цифр использовалась формула вида: =272,14*LN(B18)+287,21. Где В18 – номер периода.

Полиномиальная линия тренда в Excel

Данной кривой свойственны переменные возрастание и убывание. Для полиномов (многочленов) определяется степень (по количеству максимальных и минимальных величин). К примеру, один экстремум (минимум и максимум) – это вторая степень, два экстремума – третья степень, три – четвертая.

Полиномиальный тренд в Excel применяется для анализа большого набора данных о нестабильной величине. Посмотрим на примере первого набора значений (цены на нефть).

Цены на нефть.

Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.

Скачать примеры графиков с линией тренда

6 степень.

Зато такой тренд позволяет составлять более-менее точные прогнозы.

Как построить линию тренда в MS Excel

Зачем нужны диаграммы? Чтобы “сделать красиво”? Вовсе нет – главная задача диаграммы позволить представить малопонятные цифры в удобном для усвоения графическом виде. Чтобы с одного взгляда было понятно состояние дел, и не было необходимости тратить время на изучение сухой статистики.

Ещё один громадный плюс диаграмм состоит в том, что с их помощью гораздо проще показать тенденции, то есть, сделать прогноз на будущее. В самом деле, если дела шли в гору весь год, нет причин думать, что в следующем квартале картина вдруг изменится на противоположную.

Как построить полином в Excel?

Полином — это степенная функция y=ax2+bx+c (полином второй степени) и y=ax3+bx2+cx+d (полином третей степени) и т.

Есть 3 способа расчета значений полинома в Excel:

  1. 1-й способ с помощью графика;
  2. 2-й способ с помощью функции Excel =ЛИНЕЙН;
  3. 3-й способ с помощью Forecast4AC PRO;

ПараметрыParameters

ИмяName Обязательный или необязательныйRequired/Optional Тип данныхData type ОписаниеDescription
ИндексIndex НеобязательныйOptional VariantVariant Имя или номер линии тренда.The name or number of the trendline.

Зачем нужна линия тренда

Линия тренда “по-простому”, это непрерывная линия составленная на основе усредненных на основе специальных алгоритмов значений из которых строится наша диаграмма. Иными словами, если наши данные “прыгают” за три отчетных точки с “-5” на “0”, а следом на “+5”, в итоге мы получим почти ровную линию: “плюсы” ситуации очевидно уравновешивают “минусы”.

Исходя из направления линии тренда гораздо проще увидеть реальное положение дел и видеть те самые тенденции, а следовательно – строить прогнозы на будущее. Ну а теперь, за дело!

Важные страницы

  • Набор GyverKIT – большой стартовый набор Arduino моей разработки, продаётся в России
  • Каталог ссылок на дешёвые Ардуины, датчики, модули и прочие железки с AliExpress у проверенных продавцов
  • Подборка библиотек для Arduino, самых интересных и полезных, официальных и не очень
  • Полная документация по языку Ардуино, все встроенные функции и макросы, все доступные типы данных
  • Сборник полезных алгоритмов для написания скетчей: структура кода, таймеры, фильтры, парсинг данных
  • Видео уроки по программированию Arduino с канала “Заметки Ардуинщика” – одни из самых подробных в рунете
  • Поддержать автора за работу над уроками
  • Обратная связь – сообщить об ошибке в уроке или предложить дополнение по тексту (alex@alexgyver.ru)

Что показывает уравнение линии тренда?

Уравнение Trendline — это формула, которая находит линию, которая наилучшим образом соответствует точкам данных. Значение R-squared измеряет надежность трендовой линии : чем ближе R2 к 1, тем лучше линия тренда соответствует данным.

Линия тренда в excel

Microsoft Office Excel помимо математических вычислений позволяет проводить статистический анализ данных и осуществлять дальнейший прогноз. Это возможно благодаря большим вычислительным мощностям, а также специализированным инструментам, которые встроены в редакторе. Сегодня разберемся, как выглядит линия тренда в excel на графике и как ее построить.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...