Прогноз выручки в excel

Процедура прогнозирования

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

Способ 1: линия тренда

Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.

Попробуем предсказать сумму прибыли предприятия через 3 года на основе данных по этому показателю за предыдущие 12 лет.

Строим график зависимости на основе табличных данных, состоящих из аргументов и значений функции. Для этого выделяем табличную область, а затем, находясь во вкладке «Вставка», кликаем по значку нужного вида диаграммы, который находится в блоке «Диаграммы». Затем выбираем подходящий для конкретной ситуации тип. Лучше всего выбрать точечную диаграмму. Можно выбрать и другой вид, но тогда, чтобы данные отображались корректно, придется выполнить редактирование, в частности убрать линию аргумента и выбрать другую шкалу горизонтальной оси.

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

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

Способ 2: оператор ПРЕДСКАЗ

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

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

«Известные значения y» — база известных значений функции. В нашем случае в её роли выступает величина прибыли за предыдущие периоды.

«Известные значения x» — это аргументы, которым соответствуют известные значения функции. В их роли у нас выступает нумерация годов, за которые была собрана информация о прибыли предыдущих лет.

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

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

Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.

Открывается Мастер функций. В категории «Статистические» выделяем наименование «ПРЕДСКАЗ», а затем щелкаем по кнопке «OK».

Запускается окно аргументов. В поле «X» указываем величину аргумента, к которому нужно отыскать значение функции. В нашем случаем это 2018 год. Поэтому вносим запись «2018». Но лучше указать этот показатель в ячейке на листе, а в поле «X» просто дать ссылку на него. Это позволит в будущем автоматизировать вычисления и при надобности легко изменять год.

В поле «Известные значения y» указываем координаты столбца «Прибыль предприятия». Это можно сделать, установив курсор в поле, а затем, зажав левую кнопку мыши и выделив соответствующий столбец на листе.

Аналогичным образом в поле «Известные значения x» вносим адрес столбца «Год» с данными за прошедший период.

После того, как вся информация внесена, жмем на кнопку «OK».

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

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

Способ 3: оператор ТЕНДЕНЦИЯ

Для прогнозирования можно использовать ещё одну функцию – ТЕНДЕНЦИЯ. Она также относится к категории статистических операторов. Её синтаксис во многом напоминает синтаксис инструмента ПРЕДСКАЗ и выглядит следующим образом:

=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы «Известные значения y» и «Известные значения x» полностью соответствуют аналогичным элементам оператора ПРЕДСКАЗ, а аргумент «Новые значения x» соответствует аргументу «X» предыдущего инструмента. Кроме того, у ТЕНДЕНЦИЯ имеется дополнительный аргумент «Константа», но он не является обязательным и используется только при наличии постоянных факторов.

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

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

Способ 4: оператор РОСТ

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

=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

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

Происходит активация окна аргументов указанной выше функции. Вводим в поля этого окна данные полностью аналогично тому, как мы их вводили в окне аргументов оператора ТЕНДЕНЦИЯ. После того, как информация внесена, жмем на кнопку «OK».

Способ 5: оператор ЛИНЕЙН

Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ. Его синтаксис имеет такой вид:

=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

В поле «Известные значения y», открывшегося окна аргументов, вводим координаты столбца «Прибыль предприятия». В поле «Известные значения x» вносим адрес колонки «Год». Остальные поля оставляем пустыми. Затем жмем на кнопку «OK».

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

Как видим, прогнозируемая величина прибыли, рассчитанная методом линейного приближения, в 2019 году составит 4614,9 тыс. рублей.

Способ 6: оператор ЛГРФПРИБЛ

Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ. Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:

= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Запускается окно аргументов. В нем вносим данные точно так, как это делали, применяя функцию ЛИНЕЙН. Щелкаем по кнопке «OK».

Результат экспоненциального тренда подсчитан и выведен в обозначенную ячейку.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Создание прогноза

На листе введите два ряда данных, которые соответствуют друг другу:

Совет: Если выделить ячейку в одном из рядов, Excel автоматически выделит остальные данные.

На вкладке Данные в группе Прогноз нажмите кнопку Лист прогноза.

В окне Создание листа прогноза выберите график или гистограмму для визуального представления прогноза.

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

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

Этот лист будет находиться слева от листа, на котором вы ввели ряды данных (то есть перед ним).

Настройка прогноза

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

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

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

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

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

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

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

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

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

Если данные содержат несколько значений с одной меткой времени, Excel находит их среднее. Чтобы использовать другой метод вычисления (например, медиана или счёт), выберите нужный вариант вычисления из списка.

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

Формулы, используемые при прогнозировании

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

Таблицы могут содержать следующие столбцы, три из которых являются вычисляемыми:

столбец статистических значений времени (ваш ряд данных, содержащий значения времени);

столбец статистических значений (ряд данных, содержащий соответствующие значения);

столбец прогнозируемых значений (вычисленных с помощью функции ПРЕДСКАЗ.ЕTS);

два столбца, представляющие доверительный интервал (вычисленные с помощью функции ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ). Эти столбцы отображаются только в том случае, если в разделе » Параметры » установлен флажок » доверительный интервал «.

Скачайте пример книги.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также:

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

Get expert help now

Прогнозирование продаж в Excel не сложно составить при наличии всех необходимых финансовых показателей.

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

Линейный тренд хорошо подходит для формирования плана по продажам для развивающегося предприятия.

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

Пример прогнозирования продаж в Excel

Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).

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

Общая картина составленного прогноза выглядит следующим образом:



Алгоритм анализа временного ряда и прогнозирования

Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:

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

Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:

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

Коэффициент детерминации в Excel (Эксель) » Компьютерная помощь

Строим график зависимости на основе табличных данных, состоящих из аргументов и значений функции. Для этого выделяем табличную область, а затем, находясь во вкладке «Вставка», кликаем по значку нужного вида диаграммы, который находится в блоке «Диаграммы». Затем выбираем подходящий для конкретной ситуации тип. Лучше всего выбрать точечную диаграмму. Можно выбрать и другой вид, но тогда, чтобы данные отображались корректно, придется выполнить редактирование, в частности убрать линию аргумента и выбрать другую шкалу горизонтальной оси.

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

Найти параметры линии тренда. Линия тренда в Excel на разных графиках

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

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

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

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

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

Наиболее часто тренд представляется линейной зависимостью исследуемой величины вида

где y – исследуемая переменная (например, производительность) или зависимая переменная;
x – число, определяющее позицию (второй, третий и т.д.) года в периоде прогнозирования или независимая переменная.

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

где n – объем исследуемой совокупности (число единиц наблюдений).

Рис. 5.3. Построение тренда методом наименьших квадратов

Значения констант b и a или коэффициента при переменной Х и свободного члена уравнения определяются по формуле:

В табл. 5.1 приведен пример вычисления линейного тренда по данным .

Методы сглаживания колебаний.

Метод «скользящих средних» (МСС).

МСС позволяет сгладить ряд значений с тем, чтобы выделить тренд. При использовании этого метода берется среднее (обычно среднеарифметическое) фиксированного числа значений. Например, трехточечное скользящее среднее. Берется первая тройка значений, составленная из данных за январь, февраль и март (10 + 12 + 13), и определяется среднее, равное 35: 3 = 11,67.

Таблица 5.2 Расчет тренда методом трехточечного скользящего среднего

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

Рис. 5.4. Характер изменения объема продаж по месяцам года:
исходные данные (ряд 3); скользящие средние (ряд 4); экспоненциальное сглаживание (ряд 2); тренд, построенный методом регрессии (ряд 1)

Метод экспоненциального сглаживания.

Альтернативный подход к сокращению разброса значений ряда состоит в использовании метода экспоненциального сглаживания. Метод получил название «экспоненциальное сглаживание» в связи с тем, что каждое значение периодов, уходящих в прошлое, уменьшается на множитель (1 – α).

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

где St – текущее сглаженное значение;
Yt – текущее значение временного ряда; St – 1 – предыдущее сглаженное значение; α – сглаживающая константа, 0 ≤ α ≤ 1.

Чем меньше значение константы α , тем менее оно чувствительно к изменениям тренда в данном временном ряду.

Тренд как модель

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

Итак, что же собой представляет тренд как модель? Это совокупность расчетных коэффициентов уравнения, которые выражают регрессионную зависимость показателя (Y) от изменения времени (t). То есть, это точно такая же регрессия, как и те, что мы рассматривали ранее, только влияющим фактором здесь выступает именно показатель времени.

Важно!

Модель линейного тренда

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

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

Рассчитать коэффициенты модели можно стандартным Методом наименьших квадратов (МНК). Со всеми этими расчетами Microsoft Excel справляется на ура самостоятельно, при чем, чтобы получить модель линейного тренда либо готовый прогноз существует целых пять способов, которые мы по отдельности разберем ниже.

Графический способ получения линейного тренда

В этом и во всех дальнейших примерах будем использовать один и тот же динамический ряд – уровень ВВП, который вычисляется и фиксируется ежегодно, в нашем случае исследование будет проходить на периоде с 2004-го по 2012-й гг.

Добавим к исходным данным еще один столбец, который назовем t и пометим цифрами по возрастающей порядковые номера всех зафиксированных значений ВВП за указанный период с 2004-го по 2012-й гг. – 9 лет или 9 периодов .

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

Построение линейного тренда с помощью формулы ЛИНЕЙН

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

Нам потребуется выделить две рядом стоящие ячейки (на скриншоте это ячейки A38 и B38), далее в строке формул вверху (выделено красным на скриншоте выше) вызываем функцию, написав «=ЛИНЕЙН(», после чего эксель выведет подсказки того, что требуется для этой функции, а именно:

1. выделяем диапазон с известными значениями описываемого показателя Y (в нашем случае ВВП, на скриншоте диапазон выделен синим) и ставим точку с запятой
2. указываем диапазон влияющих факторов X (в нашем случае это показатель t, порядковый номер периодов, на скриншоте выделено зеленым) и ставим точку с запятой
3. следующий по порядку требуемый параметр для функции – это определение того нужно ли рассчитывать константу, так как мы изначально рассматриваем модель с константой (коэффициент a 0), то ставим либо «ИСТИНА» либо «1» и точку с запятой
4. далее нужно указать требуется ли расчет параметров статистики (в случае, если бы мы рассматривали этот вариант, то изначально пришлось бы выделить диапазон «под формулу» на несколько строк ниже). Указывать необходимость расчета параметров статистики, а именно стандартного значение ошибки для коэффициентов, коэффициента детерминированности, стандартной ошибки для Y, критерия Фишера, степеней свободы и пр. , есть смысл только тогда, когда вы понимаете, что они означают, в этом случае ставим либо «ИСТИНА», либо «1». В случае упрощенного моделирования, которому мы пытаемся научиться, на этом этапе прописывания формулы, ставим «ЛОЖЬ» либо «0» и добавляем после закрывающую скобочку «)»
5. чтобы «оживить» формулу, то есть заставить ее работать после прописывания всех необходимых параметров, не достаточно нажать кнопку Enter, необходимо последовательно зажать три клавиши: Ctrl, Shift, Enter

Чтобы получить расчетные значения Y по модели и, соответственно, чтобы получить прогноз, нужно просто подставить формулу в ячейку экселя, а вместо t указать ссылку на ячейку с требуемым номером периода (смотрите на скриншоте ячейку D25 ).

Для сравнения полученной модели с реальными данными, можно построить два графика, где в качестве Х указать порядковый номер периода, а в качестве Y в одном случае – реальный ВВП, а, в другом – расчетный (на скриншоте диаграмма справа).

Построение линейного тренда с помощью инструмента Регрессия в Пакете анализа

В статье , по сути, полностью описан этот метод, единственная же разница в том, что в наших исходных данных только один влияющий фактор Х (номер периода – t ).

Прогнозирование с помощью линейного тренда через функцию ТЕНДЕНЦИЯ

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

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

Прогнозирование с помощью линейного тренда через функцию ПРЕДСКАЗ

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

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

Подводя итог к статье

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

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

Легкая версия построения

Процесс построения линии тренда состоит из трех этапов: ввод в excel исходных данных, построение графика, выбор линии тренда и ее параметров.

2. Выделяем ячейки B3:B17 и перейдя на закладку «Вставка» выбираем «График».

3. После того как график построен, можно добавить подписи и заголовок.

Для начала кликнем левой кнопкой мыши по границе графика, чтобы выделить его.

Затем перейдем на закладку «Конструктор» и выберем «Макет 1».

4. Переходим к построению линии тренда. Для этого снова выделяем график и переходим на закладку «Макет».

5. Нажимаем на кнопку «Линия тренда» и выбираем «линейное приближение» или «экспоненциальное приближение».

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

И поэтому потребуется настроить параметры нашей линии тренда или выбрать другую функцию.

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

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

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

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

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

Прогноз

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

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

Вывод

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

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

Как поступить в случае, если для определенных объемов/размеров продукции хронометражные замеры отсутствуют? Или число замеров недостаточно, а дополнительные наблюдения в ближайшее время осуществить невозможно? Наилучший способ решения данной проблемы – построение расчетных зависимостей (уравнений регрессии) с помощью линий тренда в MS Excel.

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

Впоследствии возникла необходимость определения затрат времени на отборку 0,6 и 0,9 м3 товара/заказа. В связи с невозможностью проведения дополнительных хронометражных исследований затраты времени на отборку данных объемов заказа были рассчитаны с помощью уравнений регрессии в MS Excel. Для этого таблица 1 была преобразована в таблицу 2.

Следующий шаг: курсор мыши был установлен на одной из точек графика и с помощью правой кнопки мыши было вызвано контекстное меню, в котором был выбран пункт: «добавить линию тренда» (рис.2).

В появившемся окне настройки формата линии тренда (рис. 3) были последовательно выбраны: тип линии линейная/степенная и установлены флажки на следующие пункты: «показать уравнение на диаграмме» и «поместить на диаграмме величину достоверности аппроксимации (R^2)» (коэффициент детерминации).

В результате были получены графики, представленные на рис. 4 и 5.

Примем за основную — линейную расчетную зависимость. Тогда значения затрат времени в зависимости от количества товара будут определяться по формуле: y = 54,511x + 0,1489. Результаты этих расчетов для количества товара, по которому ранее были проведены хронометражные наблюдения, представлены в таблице 3 ниже.

Определим среднее отклонение затрат времени, рассчитанных по уравнению регрессии от затрат времени, рассчитанных по данным хронометражных наблюдений: (-0,05+0,10-0,05+0,01)/4=0,0019. Таким образом, затраты времени, рассчитанные по уравнению регрессии отличаются от затрат времени, рассчитанных по данным хронометражных наблюдений всего на 0,19%. Расхождение данных ничтожно мало.

По формуле: y = 54,511x + 0,1489 установим затраты времени для количества товара, по которому ранее не были проведены хронометражные наблюдения (таблица 4).

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

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

Как показать линию тренда на графике только в Excel?

1. выделяем диапазон с известными значениями описываемого показателя Y (в нашем случае ВВП, на скриншоте диапазон выделен синим) и ставим точку с запятой
2. указываем диапазон влияющих факторов X (в нашем случае это показатель t, порядковый номер периодов, на скриншоте выделено зеленым) и ставим точку с запятой
3. следующий по порядку требуемый параметр для функции – это определение того нужно ли рассчитывать константу, так как мы изначально рассматриваем модель с константой (коэффициент a 0), то ставим либо «ИСТИНА» либо «1» и точку с запятой
4. далее нужно указать требуется ли расчет параметров статистики (в случае, если бы мы рассматривали этот вариант, то изначально пришлось бы выделить диапазон «под формулу» на несколько строк ниже). Указывать необходимость расчета параметров статистики, а именно стандартного значение ошибки для коэффициентов, коэффициента детерминированности, стандартной ошибки для Y, критерия Фишера, степеней свободы и пр. , есть смысл только тогда, когда вы понимаете, что они означают, в этом случае ставим либо «ИСТИНА», либо «1». В случае упрощенного моделирования, которому мы пытаемся научиться, на этом этапе прописывания формулы, ставим «ЛОЖЬ» либо «0» и добавляем после закрывающую скобочку «)»
5. чтобы «оживить» формулу, то есть заставить ее работать после прописывания всех необходимых параметров, не достаточно нажать кнопку Enter, необходимо последовательно зажать три клавиши: Ctrl, Shift, Enter

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