• Выделите диапазон данных для построения графика;

  • Перейдите во вкладку “Вставка” на панели инструментов. Затем в разделе “Диаграммы” выберите линейный график;

Построить линейный график в Excel

  • Кликните правой кнопкой на созданном графике и выберите пункт “Add Trendline”

как построить линию тренда на графике

  • В окне “Формат линии тренда” выберите параметр “Линейная”;

формат линии тренда

Линия тренда построена. Теперь вы можете предсказать направление динамики данных вашего графика.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Все манипуляции, которые будут описаны ниже, на самом деле можно сделать с помощью одной функции в Excel (она также есть в Google Sheets). Это функция ПРЕДСКАЗ , которая с помощью линейной регрессии поможет найти новое значение на основании известных порядковых номеров периодов и объема продаж, распределенного во времени.

Первый аргумент функции — это номер прогнозируемого периода, далее — диапазон известного объема продаж и диапазон порядковых номеров этих периодов.

Функция ПРЕДСКАЗ

Прогнозное значение готово!

10 Простых советов как сделать диаграммы Excel превосходными

≡ 10 April 2016 · Рубрика: Excel

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

В данной статье не разъясняются базовые действия по созданию диаграмм. Если требуются примеры, ищите их на сайте Майкрософт или в справке Excel.

1. Удаление «шумов» с фона графики

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

От сетки на графике избавиться элементарно. Если необходимо применить формат к чему-либо в Excel (на диаграмме или в таблице), то просто выделите это и нажмите кнопки Ctrl+1 (для Mac: Command+1) – откроется диалоговое окно форматирования выбранных объектов.

В нашем случае щёлкаете одну из линий сетки на диаграмме (любую, но верхняя выделит всю область графика) и открываете диалоговое окно форматирования. Дальше выбираемЦвет линии > Нет линий (для Mac: Линия > Сплошная > Цвет: Без линии ).

Как сделать горизонтальную линию в excel?

Как сделать горизонтальную линию в excel?

2. Переместите легенду

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

Для этого вызываем окошко с настройками форматирования (вы уже должны знать как!) и выбираем нужную позицию в пункте Параметры легенды, у Mac соответственноРазмещение > нужный пункт.

Не снимая выделения с легенды, сразу увеличиваем размер шрифта до 12. Выделять сам текст не надо, достаточно выделенного прямоугольника. Оцените сами, что лучше смотрится…

Как сделать горизонтальную линию в excel?

Как сделать горизонтальную линию в excel?

3. Удалите легенду с единственным рядом

Если на графике отражён только один показатель, нет смысла сохранять легенду, которую Excel вставляет автоматически. Достаточно включить название показателя в заголовок.

Как сделать горизонтальную линию в excel?

4. Добавьте описание в название диаграммы

Распространённая ошибка, встречающаяся на диаграммах маркетологов – отсутствие понятного названия. Пока вы единственный, кто сводит данные вместе, всё, что вы пытаетесь показать, совершенно понятно. Но для остальных это не так очевидно.

Например, для диаграммы приведённой ниже, было бы малоинформативно написать в заголовке только лишь «Показы»:

Как сделать горизонтальную линию в excel?

Чтобы добавить название к выделенному графику выберите Работа с диаграммами > Макет > Название диаграммы > нужный пункт. Для Mac соответственно: Диаграммы > Макет диаграммы > Название диаграммы > нужный пункт. Обычно разумнее выбрать Над диаграммой (Mac: Название над диаграммой).

5. Отсортируйте данные перед созданием диаграмм

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

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

Взгляните на диаграмму ниже. Согласитесь, что для сортировки доходов по категориям придётся бегать глазами взад вперёд.

Как сделать горизонтальную линию в excel?

А вот на следующей диаграмме провести сортировку и интерпретацию гораздо легче, так как это уже фактически сделано за вас.

Как сделать горизонтальную линию в excel?

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

6. Не заставляйте людей наклонять голову

Видели ли вы диаграммы подобные этой?

Как сделать горизонтальную линию в excel?

Как сделать горизонтальную линию в excel?

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

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

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

7. Приведите в порядок оси

Диаграмма ниже это – сплошной кошмар. В ней присутствует все «неправильности», которые наиболее заметны в осях графиков.

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

Потеря разделителя групп разрядов

Если в данных присутствуют числа больше чем 999, необходимо включить разделитель групп разрядов. Лучше всего отформатировать данные прямо в таблице. После этого диаграмма обновится автоматически. В противном случае необходимо снять флажокСвязь с источником в окне настроек Формат оси.

Для включения разделителя групп выделите столбец с данными и нажмите кнопку 000 в группе Число. Появится разделитель, и Excel по умолчанию добавит два знака после запятой.

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

Загромождённость осей

Вертикальная ось на верхней диаграмме загромождена лишними подписями. Для исправления этого выделите ось и вызовите диалоговое окно. В Параметрах осиустановите переключатель цена основных делений на фиксированный (для Mac_: Формат осей > Масштаб > цена основных делений_). На нижней диаграмме этот параметр изменён с 20 000 на 40 000.

  • Если нужно более детальное разбиение, подберите шаг любым удобным способом.
  • Ненужные десятичные знаки

Никогда не включайте десятичные дроби, если все числа – целые (иными словами отсутствуют дробные части). Наиболее часто такое можно увидеть при использовании формата _Денежный, _где встречаются подобные подписи: $20 000.00, $30 000.00, и т.д. Это совершенно бесполезно и только мешает.

Десятичные дроби вместо процентов

Если на оси показаны проценты, форматируйте данные как проценты, не выводите их десятичными дробями. Чем меньше времени придётся тратить на интерпретацию данных, тем более привлекательной будет графика. И даже с процентами не забывайте убрать ненужные дроби. Иначе говоря, не делайте так: 10,00%, 20,00%… Преобразуйте к такому виду: 10%, 20%…

Жуткое форматирование ноля

Последнее неудобство – вывод дефиса вместо 0 в начале вертикальной оси. Это очень распространено. В Интернете можно найти, как работать с пользовательским числовым форматом. Там рассматриваются некоторые любопытные настройки, например, возможность добавлять текст к форматированию с сохранением числовых значений ячейки.

В данном случае нам достаточно изменить формат для 0. Для этого выделяем столбец, из которого берутся данные, потом вызываем диалоговое окно и на вкладке Число, в пункте(все форматы) находим строку «своего», в ней заменяем дефис на 0.

  1. В качестве финального штриха изменяем название диаграммы, смотрим на окончательный результат:

8. Попробуйте разные Темы

Excel предоставляет внушительный выбор по форматированию диаграмм. Но большинство пользователей никогда не уходят от Темы по умолчанию.

В версии 2010 для PC предоставлено 53 темы, и 57 представлено в версии 2011 для Mac. В каждой теме свои настройки стилей диаграмм — по 48 вариантов. Итого 2 544 сочетания в 2010 и 2 736 в 2011. (Это просто праздник какой-то!)

  • Чтобы переключить тему отправляемся Разметка страницы > Темы > Темы (для Mac:Главная > Темы) и выбираем нужное из выпадающего списка.
  • Некоторые из тем немного странноватые, подобно маковской Ареал, придающей текстуру графику.
  • Вы должны попробовать различные темы, чтобы выбрать наиболее подходящую.

9. Создание диаграмм для компаний

Вас не ограничивают исключительно «родными» 2 500+ вариантами Excel. Если желаете чтобы данные на графиках «соответствовали» фирме, вам нужно создать диаграмму в фирменных же цветах и сохранить её как шаблон.

Представим, что мы занимаемся подготовкой маркетинговых материалов для компании «Детский мир», не подумайте, что это реклама. И нам понадобилось для презентации создать круговую диаграмму в цветах бренда.

Excel 2010 (PC) позволяет использовать цветовые пространства RGB или HSL, а вот Excel 2011 (Mac) предоставляет RGB, CMYK, или HSB. (Для получения необходимых значений для них можно применить разные методы.

Автор использовал для подбора цвета на логотипе вышеупомянутой компании инструментом _Пипетка _из дополнения **Web Developer Toolbar, **затем конвертировал шестнадцатеричное значение в RGB).

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

На следующем этапе выбираем отдельные сектора, щёлкнув сначала на всём круге, а потом на очередном секторе. Меняем цвета через Главная > Шрифт >Цвет заливки(кнопочка с ведёрком краски) или через диалоговое окно.

Надеемся, к этому моменту у вас есть значения для RGB. Жмём на вышеупомянутое ведёрко, в выпадающем меню выбираем Другие цвета > Спектр > Цветовая модель: RGB(для Mac: Дополнительные цвета > Цветовые бегунки > Модель RGB). Повторяем для каждого сектора.

  1. На выходе получаем нечто подобное:
  2. PC:
  3. Чтобы сохранить данную диаграмму как шаблон, выделяем её и идём Работа с диаграммами > Конструктор > Тип > Сохранить как шаблон.
  4. Для создания диаграммы на основе шаблона ставим маркер активной ячейки внутри подготовленных данных (или выделяем интересующую часть), выбираем Вставка > Диаграммы > Другие > Все типы диаграмм > Шаблоны (для Mac: Диаграммы > Вставить диаграмму > Другие > Шаблоны), жмём на нужный шаблон.
  5. Mac:

Для Mac: ПКМ в любом месте диаграммы, выбираем Сохранить как шаблон. Диаграмма сохранится как .crtx файл в папке шаблонов диаграмм.

10. Сделайте название диаграммы динамическим

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

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

Продемонстрируем это на ежедневно обновляемом отчёте по доходу от контекстной рекламы. Шаги, которые необходимо проделать:

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

  • Кроме того, таблица автоматически расширяется на новые данные, если их ввести непосредственно справа от неё, или же сразу под ней.
  • Шаг 2:
  • В ячейку, расположенную сразу под 31-ой строкой данных (в расчёте на полный месяц) введём формулу СУММ, в которой укажем весь диапазон суммируемых строк, неважно, что некоторые ячейки – пустые.
  • Шаг 3:
  • Если бы мы хотели использовать оба столбца для рядов данных, достаточно было бы щёлкнуть внутри таблицы и применить Вставка > Диаграммы > Гистограмма (для Mac:Диаграммы > Гистограмма).

Но нам надо отобрать только столбец «Доход» с заголовком. Это из-за того, чтобы не включать столбец «Дни» в качестве ряда данных. Выберите стиль диаграммы среди представленных: Работа с диаграммами > Конструктор > Стили диаграмм (для Mac:Диаграммы > Стили диаграмм).

Шаг 4:

Добавьте название, указывающее на промежуточность суммы. Для примера выбрано такое: «Доход от контекстной рекламы за октябрь 2016: ». За подсказками обращайтесь к пункту №4 выше.

Шаг 5:

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

Выделяем диаграмму и жмём Ctrl+1/Command+1, далее переключаем Заливка: Нет заливки (для Mac: Заливка > Сплошная > Цвет: Нет заливки). Чтобы трюк сработал как надо – отключите отображение сетки листа, впрочем, это надо делать в любом случае. /Автор намекает на свои личные предпочтения, прим. пер./ Вид > Показать (Mac: Макет > Вид).

Шаг 6:

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

Шаг 7:

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

Шаг 8:

Теперь при добавлении новых строк данных диаграмма и её название обновляются динамически. Ловко, да?

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

Если у вас есть вопросы по Excel или теме статьи, не стесняйтесь обращаться по e-mail: ihavesomethingtosay@needfordata.ru

Текст является адаптированным переводом статьи Annie Cushing (Энни Кашинг), оригинал – 10 Simple Tips To Make Your Excel Charts Sexier. Курсивом выделяются названия пунктов меню для Excel 2010 и Mac 2011.

Как оптимизировать рутинную работу в контекстной рекламе?

О чем этот блог

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

Читайте также:  Спарклайны для калькулятора валют рынка Forex в Excel

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

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

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

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

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

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

Построение графика

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

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

Выделяем данную таблицу. Переходим во вкладку «Вставка». Там на ленте в блоке инструментов «Диаграммы» кликаем по кнопке «График». Из представленного списка выбираем самый первый вариант.

После этого график будет построен, но его нужно ещё доработать. Делаем заголовок графика. Для этого кликаем по нему. В появившейся группе вкладок «Работа с диаграммами» переходим во вкладку «Макет». В ней кликаем по кнопке «Название диаграммы». В открывшемся списке выбираем пункт «Над диаграммой».

Затем подписываем оси. В той же вкладке «Макет» кликаем по кнопке на ленте «Названия осей». Последовательно переходим по пунктам «Название основной горизонтальной оси» и «Название под осью».

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

Для того, чтобы присвоить наименование вертикальной оси также используем вкладку «Макет». Кликаем по кнопке «Название осей». Последовательно перемещаемся по пунктам всплывающего меню «Название основной вертикальной оси» и «Повернутое название». Именно такой тип расположения наименования оси будет наиболее удобен для нашего вида диаграмм.

Создание линии тренда

Теперь нужно непосредственно добавить линию тренда.

    Находясь во вкладке «Макет» кликаем по кнопке «Линия тренда», которая расположена в блоке инструментов «Анализ». Из открывшегося списка выбираем пункт «Экспоненциальное приближение» или «Линейное приближение».

Настройка линии тренда

Имеется возможность дополнительной настройки линии.

    Последовательно переходим во вкладке «Макет» по пунктам меню «Анализ», «Линия тренда» и «Дополнительные параметры линии тренда…».

  • Полиномиальная;
  • Линейная;
  • Степенная;
  • Логарифмическая;
  • Экспоненциальная;
  • Линейная фильтрация.

Для того, чтобы определить достоверность нашей модели, устанавливаем галочку около пункта «Поместить на диаграмму величину достоверности аппроксимации». Чтобы посмотреть результат, жмем на кнопку «Закрыть».

Если вас не удовлетворяет уровень достоверности, то можете вернуться опять в параметры и сменить тип сглаживания и аппроксимации. Затем, сформировать коэффициент заново.

Прогнозирование

Главной задачей линии тренда является возможность составить по ней прогноз дальнейшего развития событий.

    Опять переходим в параметры. В блоке настроек «Прогноз» в соответствующих полях указываем насколько периодов вперед или назад нужно продолжить линию тренда для прогнозирования. Жмем на кнопку «Закрыть».

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

Решение задачи с помощью Excel

Итак, необходимые теоретические знания мы с вами получили, пришло время применить их на практике. Мы будем с вами использовать классическую аддитивную модель для построения прогноза. Однако, мы построим с вами два прогноза:

  1. с использованием линейного тренда
  2. с использованием полиномиального тренда

Во всех руководствах, как правило, разбирается только линейный тренд, поэтому полиномиальная модель будет крайне полезна для вас и вашей работы!

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Модель с линейным трендом

Пусть у нас есть исходная информация по продажам за 2 года:

таблица с информацией о продажах для прогнозирования

Учитывая, что мы используем линейный тренд, то нам необходимо найти коэффициенты уравнения

  • y — значения продаж
  • x — номер периода
  • a — коэффициент наклона прямой тренда
  • b — свободный член тренда

Рассчитать коэффициенты данного уравнения можно с помощью формулы массива и функции ЛИНЕЙН. Нам необходимо будет сделать следующую последовательность действий:

  1. Выделяем две ячейки рядом
  2. Ставим курсор в поле формул и вводим формулу =ЛИНЕЙН(C4:C27;B4:B27)
  3. Нажимаем Ctrl+Shift+Enter, чтобы активировать формулу массива

На выходе мы получили 2 числа: первое — коэффициент a, второе — свободный член b.

таблица с информацией о продажах для прогнозирования 2

Теперь нам нужно рассчитать для каждого периода значение линейного тренда. Сделать это крайне просто — достаточно в полученное уравнение подставить известные номера периодов. Например, в нашем случае, мы прописываем формулу =B4*$F$4+$G$4 в ячейке I4 и протягиваем ее вниз по всем периодам.

расчет значения линейного тренда

Нам осталось рассчитать коэффициент сезонности для каждого периода. Учитывая, что у нас есть исторические данные за два года, разумно будет учесть это при расчете. Можем сделать следующим образом: в ячейке J4 прописываем формулу =(C4+C16)/СРЗНАЧ($C$4:$C$27)/2 и протягиваем вниз на 12 месяцев (т.е. до J15).

расчет коэффициента сезонности

Что нам это дало? Мы посчитали, сколько суммарно продавалось каждый январь/каждый февраль и так далее, а потом разделили это на среднее значение продаж за все два периода.

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

Примечание. Рассчитали только 12 коэффициентов, т.к. один коэффициент учитывает продажи сразу за 2 аналогичных периода.

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

Сначала создаем столбец, в котором прописываем номера будущих периодов. В нашем случае нумерация начинается с 25 периода.

Далее, для расчета значения тренда просто прописываем уже известную нам формулу =L4*$F$4+$G$4 и протягиваем вниз на все 12 прогнозируемых периодов.

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

финальная таблица с прогнозом

Модель с полиномиальным трендом

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

Посмотрите сами, какая модель более точно аппроксимирует наши точки — линейный тренд (прямая зеленая линия) или полиномиальный тренд (красная кривая)? Ответ очевиден. Поэтому сейчас мы с вами и разберем, как построить полиномиальную модель в Excel.

Модель прогнозирования с полиномиальным трендом

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

Для начала давайте определимся, чем полиномиальный тренд отличается от обычного линейного. Правильно — формой уравнения. У линейного тренда мы разбирали обычный график прямой:

У полиномиального тренда же уравнение выглядит иначе:

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

где конечная степень определяется степенью полинома.

Т.е. для полинома 4 степени необходимо найти коэффициенты уравнения:

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

  1. Ставим в ячейку F4 курсор и вводим формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^<1;2;3;4>);1;1). Функция ЛИНЕЙН позволяет произвести расчет коэффициентов, а с помощью функции ИНДЕКС мы вытаскиваем нужный нам коэффициент. В данном случае за выбор коэффициента отвечает самый последний аргумент. У нас стоит 1 — это коэффициент при самой высокой степени (т.е. при 4 степени, коэффициент). Кстати, узнать о самых полезных математических формулах Excel можно в нашем бесплатном гайде «Математические функции Excel».
  2. Аналогично прописываем формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^<1;2;3;4>);1;2) в ячейке ниже.
  3. Делаем такие же действия, пока не найдем все коэффициенты.

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

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

Теперь вы наглядно можете видеть, как рассчитанный тренд аппроксимирует исходные данные и как выглядит само уравнение. Можно сравнить уравнение на графике с вашими коэффициентами. Сходится? Значит сделали все верно!

Помимо всего прочего, вы можете сразу оценить точность аппроксимации (не полностью, но хотя бы первично). Это делается с помощью коэффициента R^2. Тут у вас снова есть два пути:

  1. Вы можете вывести коэффициент на график, поставив галочку «Поместить на диаграмму величину достоверности аппроксимации»
  2. Вы можете рассчитать коэффициент R^2 самостоятельно по формуле =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^<1;2;3;4>;;1);3;1)

Построение линии тренда в Excel

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

Линия тренда представляет собой прямую или кривую линию, аппроксимирующую (приближающую) исходные данные на основе уравнения регрессии или скользящего среднего. Аппроксимация определяется по ме­тоду наименьших квадратов. В зависимости от характера поведения исходных данных (убыва­ют, возрастают и т.д.) выбирается метод интерполяции, который сле­дует использовать для построения тренда.

Предусмотрено несколько вариантов формирования линии трен­да.

Линейной функцией: y=mx+b

где m — тангенс угла наклона прямой, b — смещение.

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

Логарифмической функцией: y=c*ln⁡x+b

где с и b — константы.

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

Полиномиальной функцией (до 6­й степени включительно): y= b + c1*x + c2*x 2 + c3*x 3 + . + c6*x 6

Полиномиальная линия тренда используется для описания попеременно возрастающих и убывающих данных. Степень полинома подбирают таким образом, чтобы она была на единицу больше количества экстремумов (максимумов и минимумов) кривой.

Степенной функцией: y = cxb

где c и b — константы.

Степенная линия тренда дает хорошие результаты для положительных данных с постоянным ускорением. Для рядов с нулевыми или отрицательными значениями построение указанной линии трен­да невозможно.

Экспоненциальной функцией: y = cebx

где c и b — константы, е — основание натурального логарифма.

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

С использованием линейной фильтрации по формуле: Ft= (At+A(t-1)+⋯+A(t-n+1))/n

где n — общее число членов ряда, t — заданное число точек (2 ≤ t < n).

Тренд с линейной фильтрацией позволяет сгладить колебания данных, наглядно демонстрируя характер зависимостей. Для построения указанной линии тренда пользователь должен задать число — параметр фильтра. Если задано число 2, то первая точка линии трен­да определяется как среднее значение из первых двух элементов данных, вторая точка — как среднее второго и третьего элементов данных и т.д.

Для некоторых типов диаграмм линия тренда в принципе не мо­жет быть построена — диаграмм с накоплением, объемных, лепест­ковых, круговых, поверхностных, кольцевых. При возможности к диаграмме можно добавить несколько линий с разными па­раметрами. Соответствие линии тренда фактическим значениям ряда данных устанавливается с помощью коэффициента достоверности аппрок­симации:

Линия тренда, а также ее параметры добавляются к данным диа­граммы следующими командами:

При необходимости параметры линии можно изменить, вызвав щелчком мыши по ряду данных диаграммы или линии трен­да окно Формат линии тренда. Можно добавить (или удалить) урав­нение регрессии, коэффициент достоверности аппроксимации, оп­ределить направление и прогноз изменения ряда данных, а также выполнить коррекцию оформительских элементов линии тренда. Выделенная линия тренда может быть также удалена.

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

Базовые понятия

Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:

Y — это объем продаж, та переменная, которую мы будем объяснять временем и от которого она зависит, то есть Y(t);

t — номер периода (порядковый номер месяца), который объясняет план продаж Y;

a0 — это нулевой коэффициент регрессии, который показывает значение Y(t), при отсутствии влияния объясняющего фактора (t=0);

a1 — коэффициент регрессии, который показывает, на сколько исследуемый показатель продаж Y зависит от влияющего фактора t;

E — случайные возмущения, которые отражают влияния других неучтенных в модели факторов, кроме времени t.

5 способов расчета значений линейного тренда в MS Excel

Это первая статья из серии “Как самостоятельно рассчитать прогноз продаж с учетом роста и сезонности”, из которой вы узнаете о 5 способах расчета значений линейного тренда в Excel.

Для того, чтобы легче было научиться прогнозировать продажи с учетом роста и сезонности, я разбил 1 большую статью о расчете прогноза на 3 части:

    1. Расчет значений тренда (рассмотрим на примере Линейного тренда в этой статье);
    2. Расчет сезонности;
    3. Расчет прогноза;

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

    Линейный тренд хорошо применять для временного ряда, данные которого увеличиваются или убывают с постоянной скоростью.

    Рассмотрим линейный тренд на примере расчета прогноза продаж в Excel по месяцам.

    Временной ряд продажи по месяцам (см. вложенный файл).

    В этом временном ряду у нас есть 2 переменных:

    1. Время — месяцы;
    2. Объём продаж;

    Уравнение линейного тренда y(x)=a+bx, где

    y — это объёмы продаж

    x — номер периода (порядковый номер месяца)

    a – точка пересечения с осью y на графике (минимальный уровень);

    b – это значение, на которое увеличивается следующее значение временного ряда;

    1-й способ расчета значений линейного тренда в Excel с помощью графика

    Расчет прогноза - линейный тренд

    Выделяем анализируемый объём продаж и строим график, где по оси Х — наш временной ряд (1, 2, 3… — январь, февраль, март …), по оси У – объёмы продаж. Добавляем линию тренда и уравнение тренда на график. Получаем уравнение тренда y=135134x+4594044

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

    При расчете значений линейного тренде нам будут известны:

    1. Время – значение по оси Х;
    2. Значение “a” и “b” уравнения линейного тренда y(x)=a+bx;

    Рассчитываем значения тренда для каждого периода времени от 1 до 25, а также для будущих периодов с 26 месяца до 36.

    Например, для 26 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=26 и получаем y=135134*26+4594044=8107551

    27-го y=135134*27+4594044=8242686

    2-й способ расчета значений линейного тренда в Excel — функция ЛИНЕЙН

    1. Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel:

    =ЛИНЕЙН(известные значения y, известные значения x, константа, статистика)

    Для расчета коэффициентов в формулу вводим

    известные значения y (объёмы продаж за периоды),

    известные значения x (номера периодов),

    вместо константы ставим 1,

    вместо статистики 0,

    Получаем 135135 – значение (b) линейного тренда y=a+bx;

    Для того чтобы Excel рассчитал сразу 2 коэффициента (a) и (b) линейного тренда y=a+bx, необходимо

      1. установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке;линейный тренд
      2. нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

      Получаем 135135, 4594044 – значение (b) и (a) линейного тренда y=a+bx;

      2. Рассчитаем значения линейного тренда с помощью полученных коэффициентов . Подставляем в уравнение y=135134*x+4594044 номера периодов – x, для которых хотим рассчитать значения линейного тренда.

      2-й способ точнее, чем первый, т.к. коэффициенты тренда мы получаем без округления, а также быстрее.

      3-й способ расчета значений линейного тренда в Excel — функция ТЕНДЕНЦИЯ

      Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

      =ТЕНДЕНЦИЯ(известные значения y; известные значения x; новые значения x; конста)

      Подставляем в формулу

      1. известные значения y – это объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);
      2. известные значения x – это номера периодов x для известных значений объёмов продаж y;
      3. новые значения x – это номера периодов, для которых мы хотим рассчитать значения линейного тренда;
      4. константа – ставим 1, необходимо для того, чтобы значения тренда рассчитывались с учетом коэффицента (a) для линейного тренда y=a+bx;

      Для того чтобы рассчитать значения тренда для всего временного диапазона, в “новые значения x” вводим диапазон значений X, выделяем диапазон ячеек равный диапазону со значениями X с формулой в первой ячейке и нажимаем клавишу F2, а затем — клавиши CTRL + SHIFT + ВВОД.

      4-й способ расчета значений линейного тренда в Excel — функция ПРЕДСКАЗ

      Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

      =ПРЕДСКАЗ(x; известные значения y; известные значения x)

      Вместо X поставляем номер периода, для которого рассчитываем значение тренда.

      Вместо “известные значения y” – объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);

      “известные значения x” – это номера периодов для каждого выделенного объёма продаж.

      3-й и 4-й способ расчета значений линейного тренда быстрее, чем 1 и 2-й, однако с его помощью невозможно управлять коэффициентами тренда, как описано в статье “О линейном тренде”.

      5-й способ расчета значений линейного тренда в Excel — Forecast4AC PRO

      2. Заходим в меню программы и нажимаем “Start_Forecast”. Значения линейного тренда рассчитаны.

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

      В следующих статье “Как самостоятельно сделать прогноз продаж с учетом роста и сезонности” мы:

      1. рассчитаем коэффициенты сезонности,очищенные от роста и выровненные;
      2. сделаем прогноз;

      О том, что еще важно знать о линейном тренде, вы можете узнать в статье “Что важно знать о линейном тренде”.

      Точных вам прогнозов!

      Присоединяйтесь к нам!

      Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

      Novo Forecast - прогноз в Excel - точно, легко и быстро!

      • Novo Forecast Lite – автоматический расчет прогноза в Excel .
      • 4analytics – ABC-XYZ-анализ и анализ выбросов в Excel.
      • Qlik Sense Desktop и QlikView Personal Edition – BI-системы для анализа и визуализации данных.

      Тестируйте возможности платных решений:

      • Novo Forecast PRO – прогнозирование в Excel для больших массивов данных.

      Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

      5 способов расчета логарифмического тренда в Excel.

      Как в Excel мы можем рассчитать коэффициенты логарифмического тренда?

      1-й способ – с помощью графика.

      Строим график в Excel и видим по оси x — наш временной рад (1, 2, 3. — ноябрь, декабрь, январь . ), по оси y объёмы продаж + добавляем на график линию тренда и уравнение тренда.

      логарифмический тренд

      Получаем уравнение тренда y=2 673 493 ln(x) + 2 913 282

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

      При расчете значений логарифмического тренда нам будут известны:

      1. Время – значение по оси Х;
      2. Значение “a” и “b” уравнения логарифмического тренда y(x)=a*ln(x)+b;

      Рассчитываем значения тренда для каждого анализируемого периода времени от 1 до 13, а также для будущих периодов с 14 месяца до 20.

      Например, для 14 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=14 и получаем y=2 673 493 ln(14) + 2 913 282=9 968 782

      20-го y=2 673 493 ln(20) + 2 913 282=10 922 350

      2-й способ – с помощью функции Excel =Линейн().

      Для расчета коэффициентов логарифмического тренда воспользуемся функцией Excel =ЛИНЕЙН() .

      Для этого в функцию =ЛИНЕЙН() введем:

      1. известные значения y – объем продаж;

      2. известные значения x – номера периодов, причём введенные, как LN(номера периодов);

      3. константа – вводим 1 для расчёта коэффициента b уравнения y(x)=a*ln(x)+b;

      4. Статистика — 1 или 0;

      Формула будит выглядеть вот так =ЛИНЕЙН(C2:O2;LN(C1:O1);ИСТИНА;ИСТИНА)

      линейн

      Теперь формулу вводим как формулу массива, выделяем 2 ячейки (подробнее о формулах массива) и нажимаем F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

      линейн формула массива

      Коэффициенты «а» и «b» логарифмического тренда y(x)=a*ln(x)+b рассчитаны;

      Получаем уравнение тренда y=2673492*ln(x)+2913281

      Для прогнозирования нам необходимо продлить линию тренда и определить её значения. При её продлении нам будет известен только один параметр — это время, т.е. значения по оси X.

      Рассчитываем значения тренда с 1-го месяца (ноябрь) до 20 (июнь)— y=2673492*ln(14)+2913281=9968782

      3-й способ – с помощью функции Excel =ТЕНДЕНЦИЯ().

      Расчет значений логарифмического тренда с помощью функции Excel =ТЕНДЕНЦИЯ().

      Для этого в функцию =ТЕНДЕНЦИЯ() вводим:

      тенденция

      1. Известные значения y — объёмы продаж за анализируемый период;

      2. Известные значений x — порядковые номера периодов (месяцев), причем введенные как LN(Известные значений x);

      3. Новые значения x— порядковые номера периодов, для которых хотим рассчитать значения трендов, причем введенные как LN(Новые значения x);

      4. Константа — ставим «1», если хотим рассчитать значения тренда y(x)=a*ln(x)+b с коэффициентом b.

      Формула будет выглядеть вот так =ТЕНДЕНЦИЯ(C4:O4;LN(C2:O2);LN(Q2:W2);1)

      Затем, вводим формулу =ТЕНДЕНЦИЯ(), как формулу массива. Для этого

      1. Выделяем диапазон ячеек с 1-го по 20-й период, в первой ячейке введена формула =ТЕНДЕНЦИЯ();

      2. Нажимаем F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

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

      Значения логарифмического тренда с помощью формулы Excel =тенденция() рассчитаны.

      4-й способ – функция Excel =предсказ().

      Расчёт значений логарифмического тренда — с помощью функции Excel =предсказ()

      предсказ

      Для этого вводим в функцию =предсказ(

      1. X — номер периода, для которого рассчитываем прогноз, причем вводим как LN(x);

      2. Известные значения y — объёмы продаж по месяцам, фиксируем диапазон, выделяем его и нажимаем F4. Получаем ссылку, как на картинке:

      зафиксировать диапазон

      3. Известные значения x — порядковые номера периодов, для которых хотим рассчитать значения логарифмического тренда, причем вводим как LN(Известные значения x) + фиксируем выделенный диапазон, выделяем его и нажимаем F4;

      Получаем формулу =ПРЕДСКАЗ(LN(Q2);$C$4:$O$4;LN($C$2:$O$2))

      Протягиваем формулу, значения логарифмического тренда рассчитаны.

      5-й способ – Forecast4AC PRO

      Расчет значений логарифмического тренда — с помощью программы Forecast4AC PRO.

      1. Устанавливаем курсор в начало временного ряда, выбираем в настройках программы:

      – Что рассчитываем – значения тренда;

      – Тренд – Логарифмический тренд;

      – Временной ряд – месячный;

      2. Заходим в меню программы и нажимаем «Start_Forecast» — готово, значения логарифмического тренда рассчитаны!

      Для того чтобы рассчитать прогноз с учетом роста и сезонности, мы умножаем рассчитанные значения тренда на коэффициенты сезонности.

      Коэффициенты сезонности рассчитаем с помощью программы Forecast4AC PRO (лист ” Лист2FYMLn “) или по аналоги, как описано в данной статье, только для рассчета коэффициентов сезонности вместо линейного тренда используем логарифмический.

      Теперь значения тренда умножаем на коэффициенты сезонности и прогноз готов.

      Отношение прогноза к предыдущему периоду получилось 116%, т.е. прогнозируется рост на 16%.

      Как мы можем скорректировать прогнозные значения логарифмического тренда?

      Если нас рост не устраивает, и мы планируем, что он будет больше, мы можем увеличить рост, скорректировав коэффициенты логарифмического тренда.

      Скорректируем значение “a” и “b” рассчитанного нами выше тренда y=2673492*ln(x)+2913281

      При изменении значений «a» и «b» логарифмического тренда y(x)=a*ln(x)+b, получаем увеличение значений тренда, причем увеличение коэффициента “а” на 10% даёт больший рост, чем увеличение коэффициента «b» на 20%.

      график логарифм

      Теперь рассчитаем коэффициенты сезонности для логарифмического тренда с помощью Forecast4AC PRO (лист ” Лист2FYMLn “). Умножим скорректированные значения тренда на сезонность. Также при прогнозировании стоит учесть дополнительные факторы, которые значительно влияют на объём продаж. Прогноз продаж готов!

      С помощью программы Forecast4AC PRO вы сможете в Excel одним нажатием клавиши рассчитать значения логарифмического тренда, коэффициенты сезонности и прогноз для более чем 5000 строк одновременно.

      Точных вам прогнозов!

      Присоединяйтесь к нам!

      Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

      Novo Forecast - прогноз в Excel - точно, легко и быстро!

      • Novo Forecast Lite – автоматический расчет прогноза в Excel .
      • 4analytics – ABC-XYZ-анализ и анализ выбросов в Excel.
      • Qlik Sense Desktop и QlikView Personal Edition – BI-системы для анализа и визуализации данных.

      Тестируйте возможности платных решений:

      • Novo Forecast PRO – прогнозирование в Excel для больших массивов данных.

      Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

      Прогноз продаж в Excel

      Хитрости » 23 Март 2017 Дмитрий 72054 просмотров

      • Скачать файл, используемый в видеоуроке:
      • Прогноз_продаж.xls (59,5 KiB, 16 541 скачиваний)
      • А в довершение мы построим красивый график с прогнозом.

      Прогнозирование продаж является неотъемлемой частью при планировании работы коммерческих и финансовых служб, поэтому задача довольно актуальная. Вариантов построения прогнозов достаточное множество, но я хочу показать как сделать простой, но в то же время достаточно жизнеспособный прогноз «на скорую руку», без лишних телодвижений и поправок «на ветер»(читайте как: без кучи доп.расчетов, которые применяются для создания более точных прогнозов). Почему я это уточняю? Потому что на мой взгляд, каким бы точным ни был прогноз продаж – это всего лишь предположение и быть уверенным в том, что именно так и будет развиваться ход событий, никак нельзя.
      И тем не менее при помощи встроенных в Excel функций мы можем построить довольно неплохой прогноз даже с учетом сезонности. Плюс я хочу показать как сделать не просто прогноз, а прогноз с отклонениями – пессимистичный и оптимистичный. С помощью подобной модели можно будет выстроить тактику продаж таким образом, чтобы постараться максимально «вписаться» в границы между пессимистичным и оптимистичным прогнозом. Исходные данные
      Для расчета прогноза потребуются данные о продажах за ранние периоды. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года. На мой взгляд это тот минимум, на основании которого можно построить весьма точный прогноз с учетом прошлого опыта. Именно из таких данных и будем исходить. Предположим, что у нас есть данные с января 2013 года по август 2015, в табличном виде:Как сделать тенденцию в excel?
      Нам необходимо рассчитать прогноз продаж на будущий год: с сентября 2015 по август 2016 и отразить это на графике. Я специально беру рваный период посреди года, чтобы показать, что начало прогноза может быть с любой даты.

      Как сделать тенденцию в excel?

      Чтобы дальше в статье не запутать вас столбцами и где они должны быть добавлены, сразу приведу конечную структуру:

      Т.е. у нас должно быть именно в указанном порядке 7 столбцов: Период; Продажи компании, руб.; Прогноз; Оптимистичный; Пессимистичный; Коэффициент сезонности; Отклонение. И чтобы все получилось они должны идти точно в таком же порядке, как на картинке выше.

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

      Прогноз_продаж.xls (59,5 KiB, 16 541 скачиваний)

      В файле два листа:

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

      В самый низ таблицы, после последней фактической даты, я добавил даты, на которые необходимо построить прогноз(от сен.2015 до авг.2016).

      Расчет прогноза
      Для расчета непосредственно прогноза в Excel есть специальная функция, которая основываясь на данных предыдущих периодов предсказывает вероятные значения для указанной даты. Она так и называется – ПРЕДСКАЗ(FORECAST). Функция основана на линейной регрессии и специально предназначена именно для прогнозирования продаж, потребления товара и пр. В столбец Прогноз (столбец C – сразу после столбца с суммами продаж) в ячейку C34 записываем функцию (и распространяем на все прогнозируемые даты – C34:C45):
      =ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)
      =FORECAST(A34,$B$2:$B$33,$A$2:$A$33)
      Сама функция требует указания следующих входных данных:

      • х — Дата, значение для которой необходимо спрогонозировать (A34)
      • Известные значения y — ссылка на ячейки таблицы с суммами продаж за известные периоды ($B$2:$B$33)
      • Известные значения x — ссылка на ячейки таблицы с дата продаж за известные периоды ($A$2:$A$33)

      С одной стороны, мы уже имеем готовый прогноз, а с другой…Данная функция пока не учитывает фактор сезонности. А это в продажах в большинстве случаев немаловажный фактор. Поэтому желательно потратить еще чуточку времени и сделать так, чтобы прогноз получился еще больше приближен к реальности.

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

      Для этого добавим в столбец Коэффициент сезонности следующую формулу:
      =(($B$2:$B$13+$B$14:$B$25)/СУММ($B$2:$B$25))*12
      =(($B$2:$B$13+$B$14:$B$25)/SUM($B$2:$B$25))*12
      Формула вводится в ячейку как формула массива и сразу в 12 ячеек(чтобы получить коэффициенты для каждого месяца года).

      Для этого сначала выделяем ячейки F2:F13 -переходим в строку формул и вводим формулу выше. После указания верных ссылок на нужные ячейки завершаем ввод формулы одновременным нажатием трех клавиш: Ctrl+Shift+Enter. Если этого не сделать, то функция вернет значение ошибки #ЗНАЧ!(#VALUE!)

      Подробнее про принцип работы формулы: она берет отдельно сумму каждого месяца за 2013 и 2014 год, складывает их. Делит полученное значение на общую сумму продаж за весь период целых месяцев(т.е.

      24 месяца) и умножает на 12, чтобы получить коэффициент именно за один месяц. И так для каждого месяца. Т.е. для ячейки F2 расчет будет выглядеть следующим образом:
      =((56 769+68 521)/ 1 542 293)*12
      =((сумма за янв.2013 + сумма за янв.

      2014)/ общая сумма за два года(янв.2013 – дек.2014))*12

      В результате для января получим коэффициент 0,974834224106574, для февраля — 0,989928632237843 и т.д.

      Я для наглядности назначил ячейкам процентный формат(правая кнопка мыши —Формат ячеек -вкладка ЧислоПроцентный(Format cellsNumberPercent), два знака после запятой):

      Теперь добавим учет этих коэффициентов для расчета прогноза в имеющуюся функцию ПРЕДСКАЗ(ячейки C34:C45):
      =ПРЕДСКАЗ(A34;$B$2:$B$33;$A$2:$A$33)*ИНДЕКС($F$2:$F$13;МЕСЯЦ(A34))
      =FORECAST(A34,$B$2:$B$33,$A$2:$A$33)*INDEX($F$2:$F$13,MONTH(A34))
      Здесь применяется функция ИНДЕКС(INDEX), в которой первым аргументом указываем ссылку на 12 ячеек с коэффициентами сезонности($F$2:$F$13), а вторым – номер месяца, чтобы вернуть коэффициент именно для нужного месяца(для этого используем функцию МЕСЯЦ(MONTH), которая возвращает только номер месяца из указанной даты). Для сентября 2015 это будет выглядеть так:
      =ПРЕДСКАЗ(A34; $B$2:$B$33; $A$2:$A$33)*ИНДЕКС(<97,48%:98,99%:90,38%:94,66%:100,86%:99,02%:100,66%:110,39%:100,47%:104,82%:105,13%:97,14%>; 9)

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

      Для того, чтобы построить такие прогнозы необходимо рассчитать допустимое отклонение от прогнозируемых значений. Здесь так же будем использовать имеющиеся в Excel функции. В ячейку G2 запишем формулу:
      =ДОВЕРИТ(0,05; СТАНДОТКЛОН(C34:C45); СЧЁТ(C34:C45))
      =CONFIDENCE(0.

      05,STDEV(C34:C45),COUNT(C34:C45))
      ДОВЕРИТ(CONFIDENCE) – возвращает доверительный интервал, используя нормальное распределение.

      • алфа – уровень значимости для вычисления доверительного уровня. Используемое в формуле 0,05 означает доверительный уровень в 95%. В большинстве случаев это оптимальное значение
      • станд_откл – стандартное отклонение генеральной совокупности. Должно быть известно. Но т.к. мы этими данными не располагаем – то это значение вычисляем при помощи функции СТАНДОТКЛОН(STDEV), передавая ей для расчетов спрогнозированные данные
      • размер – указывается целое число, обозначающее количество данных для выборки. Как правило равно количеству спрогнозированных данных. У нас количество определяется функцией СЧЁТ, которая подсчитывает количество чисел в указанных ячейках.

      Как сделать тенденцию в excel?

      Теперь в ячейки столбцов Оптимистичный и Пессимистичный(D и E), начиная со строки 34, запишем такие формулы:
      Оптимистичный: =$C34+$G$2
      Пессимистичный: =$C34-$G$2

      Т.е. мы для оптимистичного прогноза берем сумму прогноза и прибавляем к ней сумму рассчитанного отклонения. А для пессимистичного, мы сумму отклонения вычитаем. Вот мы и получили все необходимые данные.

      Как сделать тенденцию в excel?

      График
      Но было бы кощунством с нашей стороны проделать такую работу и не использовать возможности Excel для построения красивого графика. Придется добавить немного шаманства(на деле, мы уже начали шаманить, когда стали записывать прогноз в отдельный столбец, а не продолжать его в том же столбце, что и фактические продажи). В ячейки C33, D33 и E33 скопируем значение из ячейки B33, чтобы они все имели одинаковые значения:

      Как сделать тенденцию в excel?

      Теперь выделяем все данные (A1:E45), переходим на вкладку Вставка(Insert) – группа Диаграммы(Charts)График(Line). И получим такую картину:

      Наглядно и сразу понятно что к чему и чего можно ожидать.

      • Синим – фактические продажи
      • Оранжевый – прогноз
      • Серый – Оптимистичный прогноз
      • Желтый – Пессимистичный

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

      Быстрый прогноз в Excel 2016 и выше
      Начиная с версии 2016 в Excel появилась замечательная возможность создать прогноз двумя кликами мыши. При этом сразу с оптимистичным и пессимистичным развитием событий и графиком. За основу возьмем все те же исходные данные из двух столбцов:

      Как сделать тенденцию в excel?
      Выделяем необходимые данные из двух столбцов -переходим на вкладку Данные(Data) -группа Прогноз(Forecast)Лист прогноза(Forecast Sheet):
      Как сделать тенденцию в excel?
      В появившемся окне раскрываем пункт Параметры(Options) и настраиваем:
      Как сделать тенденцию в excel?

      • Завершение прогноза(Forecase End) – указывается дата, которой должен заканчиваться прогноз. Я советую всегда проверять эту дату, т.к. по умолчанию Excel почти всегда выставляет некую среднюю дату, которая отличается от необходимой.
      • Начало прогноза(Forecase Start) – указывается дата, с которой необходимо начать строить прогноз. Как правило это последняя дата фактических данных. Если указать дату, которая будет раньше последней даты фактических данных, то для построения прогноза будут использоваться данные только ДО этой даты (так же это называется «ретроспективным прогнозированием»).
      • Доверительный интервал(Confidence interval) – этот пункт поможет понять, насколько точно построен прогноз. Чем больше будет доверительный интервал, тем меньше точность прогноза и чем меньше доверительный интервал – тем выше точность прогноза. Что вполне логично. По умолчанию определяется для 95% точек, хотя его можно изменить в соответствующем поле. Если интервал создавать не нужно – снять галочку.
      • Сезонность(Seasonality) – как понятно из названия, отвечает за определение фактора сезонности. Лучше оставлять автоматическим, при котором сезонность определяется на основании всех точек месяцев(т.е. 12). Но если этот фактор необходимо рассчитывать из иного количества точек, то необходимо выбрать Установка вручную и указать нужное количество точек. Но следует учитывать, что если точек будет недостаточно – то прогноз может быть очень неточным и график в итоге будет иметь вид, далекий от ожидаемого.
      • Диапазон временной шкалы(Timeline Range) – указывается диапазон значений с датами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон значений.
      • Диапазон значений(Values Range) – указывается диапазон значений с суммами фактических продаж, на основании которых необходимо построить прогноз. По размерам должен совпадать с параметром Диапазон временной шкалы.
      • Заполнить отсутствующие точки с помощью(Fill Missing Poins Using) – если каких-то данных не хватает(например, имеются пропуски в ячейках с суммами), то можно выбрать чем эти данные заполнить. По умолчанию используется интерполяция. Это означает, что отсутствующие данные вычисляется как взвешенное среднее соседних ячеек, если отсутствует менее 30 % точек. Если необходимо заполнять отсутствующие точки нулями, то необходимо выбрать из выпадающего списка пункт Нули.
      • Объединить дубликаты с помощью(Aggregate Duplicates Using) – если в фактических данных есть повторяющиеся даты, то Excel объединит их в одну точку с этой датой, а в качестве суммы подставит среднее арифметическое для этой даты. Это оптимальный вариант, но так же допускается выбрать из списка и другую функцию: Количество, СЧЁТЗ, Максимум, Медиана, Минимум, Сумма.
      • Включить статистические данные прогноза(Include Forecast Statistics) – при включении данного пункта на листе с таблицей графика правее основных данных будет создана таблица с дополнительной статистической информации о прогнозе. В таблице при помощи функции ПРЕДСКАЗ.ЕTS.СТАТ будут рассчитаны коэффициенты сглаживания (Альфа, Бета, Гамма), и метрики ошибок (MASE, SMAPE, MAE, RMSE).

      Статья помогла? Поделись ссылкой с друзьями!

      Построение линии тренда в Excel

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

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

      Аппроксимация определяется по ме­тоду наименьших квадратов. В зависимости от характера поведения исходных данных (убыва­ют, возрастают и т.д.

      ) выбирается метод интерполяции, который сле­дует использовать для построения тренда.

      • Предусмотрено несколько вариантов формирования линии трен­да.
      • Линейной функцией: y=mx+b
      • где m — тангенс угла наклона прямой, b — смещение.

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

      Логарифмической функцией: y=c*ln⁡x+b

      где с и b — константы.

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

      Полиномиальной функцией (до 6­й степени включительно): y= b + c1*x + c2*x2 + c3*x3 + …+ c6*x6

      где b, c1, c2 , … c6 — константы.

      Полиномиальная линия тренда используется для описания попеременно возрастающих и убывающих данных. Степень полинома подбирают таким образом, чтобы она была на единицу больше количества экстремумов (максимумов и минимумов) кривой.

      Степенной функцией: y = cxb

      где c и b — константы.

      Степенная линия тренда дает хорошие результаты для положительных данных с постоянным ускорением. Для рядов с нулевыми или отрицательными значениями построение указанной линии трен­да невозможно.

      Экспоненциальной функцией: y = cebx

      где c и b — константы, е — основание натурального логарифма.

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

      С использованием линейной фильтрации по формуле: Ft= (At+A(t-1)+⋯+A(t-n+1))/n

      где n — общее число членов ряда, t — заданное число точек (2 ≤ t http://ya-znau.ru/znaniya/zn/202

      Возможности инструмента

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

      Окно содержит четыре настройки, в которые входят цвет, объем и тип линии, а также параметры самого инструмента.

      Параметры линии тренда можно условно поделить на четыре блока:

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

      Выведем на исходный график уравнение линии и коэффициент достоверности.

      Как видите, значение близко к 0,5, это говорит о низкой достоверности полученной линии тренда, и дальнейший прогноз будет ошибочным.

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

      Итак, давайте разберем простой пример: У Вас есть небольшой магазин и есть статистика по продажам за последние 9 месяцев. Вы хотите сделать прогноз на 10 и 11 месяц, но не знаете как – линия тренда придет на помощь. Конечно Ваши продажи могут отклоняться от месяца к месяцу, но тренд будет рассчитывать общую тенденцию и покажет куда двигается Ваш бизнес.

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

      Пошаговая инструкция построения линии тренда в Excel.

      1. Введите набор данных. Как правило этот набор должен состоять из двух столбцов, данные по одному из них будут соответствовать оси X, а второй оси Y.

      2. Постройте точечный график с линиями. Для этого выделите мышкой диапазон данных, перейдите в меню “Вставка” и Диаграммах выберите “Точечная с прямыми отрезками”

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

      3. Далее выделите график кликом левой кнопкой мышки, а затем нажмите по графику правой кнопкой мышки и выберите “Добавить линию тренда”

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

      4. Поздравляю, Вы построили линию тренда. Теперь давайте добавим прогноз на ближайшие два периода. Для этого выделите линию тренда и в блоке Прогноз укажите значение 2 в окне “Вперед на период”. Готово.

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

      Теперь у Вас есть новый способ спрогнозировать свою деятельность в будущем и представить это в наглядном, визуальном виде.

      Профессиональная версия: выбор линии тренда и настройка параметров

      6. Нажимаем на кнопку «Линия тренда» и выбираем «Дополнительные параметры и линии тренда».

      7. В окне «Формат линии тренда», мы ставим флажок напротив «поместить на диаграмму величину достоверности аппроксимации R^2 и нажимаем кнопку «закрыть».

      Видим на диаграмме коэффициент R^2= 0,6442

      8. Отменяем изменения. Выделяем график, нажимаем на закладку “Макет”, кнопку “линия тренда” и выбираем “Нет”.

      9. Переходим в окно «Формат линии тренда», но уже для того, чтобы выбрать «Полиноминальную» линию тренда, меняем степень, добиваясь показателей коэффициента R^2= 0,8321

      Прогноз

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

      На основе прогноза мы можем предположить, что 25 января количество набранных баллов было бы от 60 до 70.

      Вывод

      И в заключение если Вам интересна формула по которой построен тренд, в коне «Формат линии тренда» поставьте флажок напротив «показать уравнение на диаграмме».

      Теперь Вы знаете, как выполнить задание и построить линию тренда, даже в такой программе как excel 2010.

Ссылка на основную публикацию
Adblock
detector