Для составления простых прогнозов можно использовать функцию ТЕНДЕНЦИЯ в Excel. С ее помощью рассчитывают будущие значения изучаемого показателя в соответствии с линейным трендом. Используя метод наименьших квадратов, функция аппроксимирует прямой линией диапазоны известных значений y и известных значений x. Прогнозирует значения y, соответствующие данной линии, для новых значений x. Но получить математическое описание и статистическую характеристику модели тренда посредством ТЕНДЕНЦИИ невозможно.

Синтаксис функции ТЕНДЕНЦИЯ

Опишем аргументы функции:

1. Диапазон данных y. Обязательный аргумент. Массив известных значений y для уравнения y = ax + b.
2. Диапазон значений x. Обязательный аргумент, включающий массив уже известных для соотношения y = ax + b значений x.
3. Новые значения x. Обязательный аргумент. Диапазон переменных x, для которых необходимо рассчитать значения y.
4. Константа. Необязательное логическое значение. Если нужно, чтобы значения тенденции рассчитывались без учета коэффициента b (соблюдалось соотношение y = ax), ставим 0.

Особенности работы функции ТЕНДЕНЦИЯ:

1. Если диапазон известных значений y находится в одном столбце (одной строке), то каждый столбец (строка) с известными значениями x воспринимается как отдельная переменная.
2. В массиве с известными значениями x может быть несколько переменных. Но если применяется только одна, диапазоны с известными значениями x и y должны быть соразмерны. Если используется несколько переменных, то диапазон с заданными значениями y должен вмещаться в одной строке или в одном столбце.
3. Если диапазон с известными показателями x не указан, то функция предполагает массив 1; 2; 3; 4;…, соразмерный диапазону с заданными значениями y.
4. Диапазон с новыми значениями x должен вмещаться в такое же количество строк или столбцов, как и диапазон с известными значениями y. То есть быть соразмерным независимым переменным.
5. Если аргумент с новыми значениями x не указан, то функция считает его равным аргументу с известными значениями x. Если и известные показатели не заданы, то предполагается массив 1; 2; 3; 4;…, соразмерный диапазону с заданными параметрами y.
6. Чтобы функция вернула массив, формулу нужно вводить как формулу массива.

Приведем примеры функции ТЕНДЕНЦИЯ.

Прогноз продаж с учетом роста и сезонности

Функцию ТЕНДЕНЦИЯ хорошо использовать для временного ряда, где данные увеличиваются или уменьшаются с постоянной скоростью.

Временной ряд товарооборота по месяцам с двумя переменными:

Сначала рассчитаем значения линейного тренда с помощью графика Excel. По оси Х – номера месяцев, по оси Y – объем товарооборота.

Добавим на график линию тренда и его уравнение.

Для прогнозирования будущих продаж нужно рассчитать показатели линейного тренда для анализируемых данных и для будущих периодов, используя уравнение тренда y = 490,26x + 37747.

Показатели линейного тренда будем считать для каждого месяца.

Получаем данные тренда и для будущих периодов: 16 – 19. Так, в 16 месяце спрогнозированное значение продаж – 45591,16 тыс. руб.

Теперь спрогнозируем товарооборот с помощью встроенной функции ТЕНДЕНЦИЯ. Вызываем «Мастер функций». В категории «Статистические» находим нужную. Заполняем аргументы:

Известные значения y – диапазон с объемами продаж. Данные необходимо зафиксировать (кнопка F4), чтобы при размножении формулы массив сохранился.

Известные значения x – номера месяцев, для которых функция рассчитает данные для линейного тренда. Данные тоже фиксируем.

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

Задаем аргумент «Конст»: 1. Функция при расчете значений тренда учтет коэффициент a.

Обратите внимание: диапазоны известных значений соразмерны.

Функция ТЕНДЕНЦИЯ дала нам те же прогнозные показатели на 16-19 периоды.

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

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

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

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

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

1. Строим таблицу, где в одном столбике будут располагаться временные отрезки (в нашем случае даты), а в другом – величина, динамика которой будет отображаться в графике.
2. Выделяем данную таблицу. Переходим во вкладку «Вставка». Там на ленте в блоке инструментов «Диаграммы» кликаем по кнопке «График». Из представленного списка выбираем самый первый вариант.
3. После этого график будет построен, но его нужно ещё доработать. Делаем заголовок графика. Для этого кликаем по нему. В появившейся группе вкладок «Работа с диаграммами» переходим во вкладку «Макет». В ней кликаем по кнопке «Название диаграммы». В открывшемся списке выбираем пункт «Над диаграммой».
4. В появившееся поле над графиком вписываем то название, которое считаем подходящим.
5. Затем подписываем оси. В той же вкладке «Макет» кликаем по кнопке на ленте «Названия осей». Последовательно переходим по пунктам «Название основной горизонтальной оси» и «Название под осью».
6. В появившемся поле вписываем название горизонтальной оси, согласно контексту расположенных на ней данных.
7. Для того, чтобы присвоить наименование вертикальной оси также используем вкладку «Макет». Кликаем по кнопке «Название осей». Последовательно перемещаемся по пунктам всплывающего меню «Название основной вертикальной оси» и «Повернутое название». Именно такой тип расположения наименования оси будет наиболее удобен для нашего вида диаграмм.
8. В появившемся поле наименования вертикальной оси вписываем нужное название.

Урок: Как сделать график в Excel

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

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

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

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

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

1. Последовательно переходим во вкладке «Макет» по пунктам меню «Анализ», «Линия тренда» и «Дополнительные параметры линии тренда…».
2. Открывается окно параметров, можно произвести различные настройки. Например, можно выполнить изменение типа сглаживания и аппроксимации, выбрав один из шести пунктов:
   • Полиномиальная;
   • Линейная;
   • Степенная;
   • Логарифмическая;
   • Экспоненциальная;
   • Линейная фильтрация.

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

   

   Если данный показатель равен 1, то модель максимально достоверна. Чем дальше уровень от единицы, тем меньше достоверность.

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

Да Нет

2 метода:В WindowsНа Mac

Из этой статьи вы узнаете, как добавить линию тренда к графику в Microsoft Excel. Это можно сделать на компьютере с Windows и с Mac OS X.

Метод 1 В Windows

1. Откройте таблицу Excel.

   Дважды щелкните по файлу Excel с нужными данными.

   • Если вы еще не создали таблицу, откройте Excel и нажмите «Пустая книга», чтобы создать новую книгу. Затем введите данные и постройте график.
2. Выберите график.

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

   • Если вы еще не построили график, сделайте это сейчас.

3. Щелкните по +. Это зеленая кнопка в верхнем правом углу графика. Раскроется меню.

4. Щелкните по стрелке справа от поля «Линия тренда». Возможно, вам придется навести указатель мыши на правую сторону поля «Линия тренда», чтобы отобразить эту стрелку. Откроется второе меню.

5. Выберите тип линии тренда.

   Щелкните по одной из следующих опций:

   • Линейная
   • Экспоненциальная
   • Линейный прогноз
   • Скользящее среднее
   • Также можно нажать «Дополнительные параметры», чтобы открыть панель дополнительных параметров.

6. Выберите данные для анализа. Нажмите на имя серии данных (например, «Серия 1») во всплывающем окне. Если вы уже присвоили имя данным, щелкните по этому имени.

7. Нажмите OK.

   Эта кнопка находится в нижней части всплывающего окна. К графику будет добавлена линия тренда.

   • Если вы нажали «Дополнительные параметры», присвойте имя линии тренда или измените величину прогноза в правой части окна.

8. Сохраните внесенные изменения. Для этого нажмите ^ Ctrl+S. Если это новая таблица, введите имя файла и укажите папку для сохранения.

Метод 2 На Mac

1. Откройте таблицу Excel.

   Дважды щелкните по файлу Excel с нужными данными.

   • Если вы еще не создали таблицу, откройте Excel и создайте новую книгу. Затем введите данные и постройте график.
2. Выберите данные на графике.

   Щелкните по серии данных, которые собираетесь проанализировать.

   • Если вы еще не построили график, сделайте это сейчас.
3. Нажмите на вкладку Конструктор.

   Она находится в верхней части окна Excel.

4. Нажмите Добавить элемент диаграммы. Эта опция находится в левой части панели инструментов «Конструктор». Раскроется меню.

5. Выберите Линия тренда. Эта опция находится внизу меню. Появится всплывающее меню.

6. Выберите тип линии тренда.

   Щелкните по одной из следующих опций в меню:

   • Линейная
   • Экспоненциальная
   • Линейный прогноз
   • Скользящее среднее
   • Также можно нажать «Дополнительные параметры», чтобы открыть окно с дополнительными параметрами (например, с именем линии тренда).

7. Сохраните внесенные изменения. Нажмите ⌘ Command+S или щелкните по «Файл» > «Сохранить». Если это новая таблица, введите имя файла и укажите папку для сохранения.

Советы

• В зависимости от данных графика вы можете увидеть дополнительные типы линии тренда (например, «Полиномиальная»).

Предупреждения

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

Информация о статье

Категории: Microsoft Excel

На других языках:

English: Do Trend Analysis in Excel, Español: hacer un análisis de tendencia en Excel, Italiano: Eseguire un’Analisi di Andamento del Mercato in Excel, Português: Fazer Análise de Tendências Utilizando o Microsoft Excel, Deutsch: In Excel eine Trendanalyse machen, Français: faire une modélisation avec Excel, Bahasa Indonesia: Melakukan Analisis Tren di Excel, Nederlands: Een trendanalyse maken in Excel, Tiếng Việt: Phân tích xu hướng trong Excel, ไทย: วิเคราะห์แนวโน้ม (Trend Analysis) ด้วย Excel, العربية: تحليل اتجاه البيانات في برنامج إكسل, हिन्दी: एक्सेल में ट्रेंड एनालिसिस (Trend Analysis) करें (Do Trend Analysis in Excel)

• Печать
• Править
• Написать благодарственное письмо авторам

Эту страницу просматривали 20 863 раз.

Была ли эта статья полезной?