Лист прогноза в excel как включить

1

Быстрое прогнозирование в Microsoft Excel

Прогнозирование — хоть и неблагодарное, но необходимое дело и для решения таких задач в Microsoft Excel есть весьма приличный инструментарий — от простейших функций линейного тренда до навороченных статистических инструментов из надстройки Пакет Анализа (Analysis Toolpak). Одними из самых простых в реализации и при этом весьма эффективных являются функции прогнозирования по методу экспоненциального сглаживания.

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

В Microsoft Excel для её реализации есть две основные функции, появившиеся начиная с 2016-й версии Excel:

• ПРЕДСКАЗ.ETS (FORECAST.ETS) — вычисляет будущие спрогнозированные значения на основе исторических данных.
• ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ (FORECAST.ETS.CONFINT) — вычисляет размах доверительного интервала — коридора погрешности, в пределах которого с заданной вероятностью наш прогноз должен сбыться.

Особенно приятно, что вводить вручную эти функции и их многочисленные аргументы совершенно не требуется — в Microsoft Excel для этого есть гораздо более удобный инструмент, получивший название Лист прогноза (Forecast Sheet) . Давайте рассмотрим работу с ним на следующем примере.

В качестве исходных исторических данных возьмем с сайта AutoVercity реальную статистику по продажам автомобилей в России за 2019-2020 годы (все марки суммарно):

Представим на минуту, что сейчас конец 2020 года и мы хотим, используя эти данные, сделать помесячный прогноз продаж автомобилей на следующие полтора года. Выделим всю нашу таблицу и на вкладке Данные воспользуемся кнопкой Лист прогноза (Data — Forecast Sheet) .

В открывшемся окне зададим следующие настройки:

1. Дату завершения прогноза
2. Сезонность — почти никогда корректно не определяется автоматически, к сожалению, так что лучше задать её вручную. В большинстве бизнесов она годовая (т.е. «узор» колебаний похожим образом повторяется из года в год), так что установим её равной 12 месяцам.
3. Вероятность, с которой мы требуем попадания будущих фактических значений в коридор доверительного интервала. Чем больше эта вероятность, тем шире интервал (т.е. более размыт прогноз). Обычно используют значения 90-95%.
4. В правом нижнем углу окна можно дополнительно выбрать реакцию на пустые ячейки (их можно заполнить нулями или средним соседних значений — интерполяцией) и на дубликаты (обычно их усредняют). Однако же, по возможности, лучше заранее подготовить исходные исторические данные, чтобы таких пробелов или дублей в них не было.

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

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

Excel для аналитика. 4 техники анализа данных в Excel

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

Анализ данных через сводные таблицы Excel

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

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

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

1. Дисперсионный анализ.
2. Корреляционный анализ.
3. Ковариация.
4. Вычисление скользящего среднего. Очень популярный метод в статистике и в трейдинге.
5. Получать случайные числа.
6. Выполнять операции с выборкой.

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

1. Перейдите в меню «Файл», и там найдите кнопку «Параметры». После этого перейдите в «Надстройки». Если же вы установили 2007 версию Эксель, то нужно нажать на кнопку «Параметры Excel», которая находится в меню Office.
2. Далее появляется всплывающее меню, озаглавленное словом «Управление». Там находим пункт «Надстройки Excel», нажимаем на него, а потом – на кнопку «Перейти». Если же вы используете компьютер Apple, то достаточно открыть вкладку «Средства» в меню, а потом в раскрывающемся перечне найти пункт «Надстройки для Excel».
3. В том диалоге, который появился после этого, нужно поставить галочку возле пункта «Пакет анализа», после чего подтвердить свои действия, нажав кнопку «ОК».

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

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

Как работать со сводными таблицами

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

1. Откройте файл, в котором содержится таблица.
2. Выделите диапазон ячеек, которые мы будем анализировать с помощью сводной таблицы.
3. Откройте вкладку «Вставка, и там надо найти группу «Таблицы», где есть кнопка «Сводная таблица». Если же используется компьютер под операционной системой Mac OS, то нужно открыть вкладку «Данные», и эта кнопка будет находиться во вкладке «Анализ».
4. После этого откроется диалог с заголовком «Создание сводной таблицы».
5. Затем выставите такое отображение данных, которое соответствует выделенному диапазону.

Мы открыли таблицу, информация в которой никоим образом не структурирована. Чтобы это сделать, можно воспользоваться настройками полей сводной таблицы в правой стороне экрана. Например, отправим в поле «Значения» «Сумму заказов», а информацию про продавцов и дату продажи – в строки таблицы. Исходя из данных, которые содержатся в этой таблице, автоматически определились суммы. Если есть необходимость, можно открыть информацию по каждому году, кварталу или месяцу. Это позволит получить детальную информацию, которая надо в конкретный момент.

От того, сколько колонок есть, будет отличаться и набор имеющихся параметров. Например, общее число столбцов – 5. И нам надо просто разместить и выбрать их верным образом, а показать сумму. В таком случае выполняем действия, показанные на этой анимации.

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

Можно также посмотреть информацию про продавцов. Для этого мы заменяем колонку «Страна» на «Продавец». Результат получится следующий.

Анализ данных с помощью 3D-карт

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

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

Как работать с 3D-картами в Excel

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

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

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

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

Лист прогноза в Excel

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

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

Как работать с листом прогноза

Чтобы работать с листом прогноза, необходимо выполнять следующие действия:

1. Откройте файл, в котором содержится большой объем информации по тем показателям, которые нам надо проанализировать. Например, в течение прошлого года (хотя чем больше, тем лучше).
2. Выделите две строки с информацией.
3. Перейдите в меню «Данные», и там кликните по кнопке «Лист прогноза».
4. После этого откроется диалог, в котором можно выбрать тип визуального представления прогноза: график или гистограмма. Выберите тот, который подходит под вашу ситуацию.
5. Установите дату, когда прогноз должен закончиться.

В приводимом нами ниже примере даются сведения за три года – 2011-2013. При этом рекомендуется указывать временные промежутки, а не конкретные числа. То есть, лучше писать март 2013, а не конкретное число типа 7 марта 2013 года. Чтобы исходя из этих данных получить прогноз на 2014 год необходимо получить данных, расположенные в рядах с датой и показателями, которые были на этот момент. Выделяем эти строки.

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

Быстрый анализ в Excel

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

Есть возможность прямо в ячейке создавать самые разные диаграммы и микрографики.

Как работать

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

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

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

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

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

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

​Смотрите также​ примера.​ известные_значения_x, не должна​ прогнозов были более​ скачать данный пример:​Рассчитаем прогноз по продажам​Диапазон временной шкалы​Лист прогноза​ имеющихся данных. Функции​ или стабилизацию) продемонстрирует​(вкладка​ серии научных экспериментов,​ линейного приближения, в​ на монитор в​ того, у​ прогноз прибыли на​.​Прогнозирование – это очень​На график, отображающий фактические​ равняться 0 (нулю),​ точными.​Функция ПРЕДСКАЗ в Excel​

​ с учетом роста​Здесь можно изменить диапазон,​

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

​.​ ЛИНЕЙН и ЛГРФПРИБЛ​ предполагаемую тенденцию на​Главная​ можно использовать Microsoft​ 2019 году составит​ указанной ранее ячейке.​

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

​ТЕНДЕНЦИЯ​ 2018 год.​Линия тренда построена и​ важный элемент практически​ объемы реализации продукции,​

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

​ возвращают различные данные​ ближайшие месяцы.​, группа​ Office Excel для​ 4614,9 тыс. рублей.​ Как видим, на​имеется дополнительный аргумент​Выделяем незаполненную ячейку на​​ по ней мы​​ любой сферы деятельности,​ добавим линию тренда​ вернет код ошибки​ с помощью функции​​ степенью точности предсказать​​ продажи за 12​ шкалы. Этот диапазон​Создание листа прогноза​ регрессионного анализа, включая​Эта процедура предполагает, что​Редактирование​ автоматической генерации будущих​Последний инструмент, который мы​ этот раз результат​«Константа»​ листе, куда планируется​ можем определить примерную​ начиная от экономики​

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

​ базироваться на существующих​ЛГРФПРИБЛ​

​ рублей. Отличия от​​ является обязательным и​​ Жмем на кнопку​​ три года. Как​​ Существует большое количество​​ линию тренда»).​​ с нечисловыми данными,​ первого аргумента представлен​ значений, и возвращает​ на 3 месяца​.​ представления прогноза.​​ осью.​​ создана. Если это​​).​ данных или для​​. Этот оператор производит​ результатов обработки данных​ используется только при​«Вставить функцию»​ видим, к тому​​ программного обеспечения, специализирующегося​​Настраиваем параметры линии тренда:​

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

​ имеются. Это связано​​ линейной зависимости функции.​. В категории​

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

​R2​ пользователи знают, что​R2 = 0,9567, что​​Функция ПРЕДСКАЗ была заменена​​ которая записывается как​ времени. Такие изменения​ 1 период (y).​ должен совпадать со​

​Описание​​Выберите ряд данных, к​ экспоненциальной зависимости, а​В Microsoft Excel можно​= ЛГРФПРИБЛ (Известные значения_y;известные​ с тем, что​Посмотрим, как этот инструмент​«Статистические»​, как уже было​

​ обычный табличный процессор​​ означает: данное отношение​ функцией ПРЕДСКАЗ.ЛИНЕЙН в​ y=aln(x)+b.​ могут быть зафиксированы​Уравнение линейного тренда:​ значением параметра​

​Создать​​ПРЕДСКАЗ​ которому нужно добавить​ также вводить значения​ заполнить ячейки рядом​ значения_x; новые_значения_x;[конст];[статистика])​ данные инструменты применяют​ будет работать все​выделяем наименование​ сказано выше, отображает​

​ Excel имеет в​ объясняет 95,67% изменений​ Excel версии 2016,​Результат расчетов:​ опытным путем, в​y = bx​Диапазон временной шкалы​.​Прогнозирование значений​

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

​Как видим, все аргументы​ разные методы расчета:​​ с тем же​​«ПРЕДСКАЗ»​ качество линии тренда.​ своем арсенале инструменты​ объемов продаж с​ но была оставлена​Для сравнения, произведем расчет​

​ результате чего будет​ + a​.​В Excel будет создан​​тенденция​​ скользящее среднее.​

​ таблицей, содержащей статистические​​РОСТ​​Макет​​ применяется метод наименьших​​ маркер заполнения или​ Алгоритм расчета прогноза​ зависимости.​ точкой прогнозирования определим​«OK»​R2​ эффективности мало чем​

​ модель формулы для​​ и более старыми​​И для визуального сравнительного​​ соответствующих им значений​​a — точка пересечения​Для обработки отсутствующих точек​

​ и предсказанные значения,​Прогнозирование экспоненциальной зависимости.​в группе​​ квадратов (y=mx+b).​​ команды​

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

​ к начальным значениям​​. Для экстраполяции сложных​

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

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

​Этот лист будет находиться​

​лгрфприбл​​Линия тренда​​ применяется алгоритм расчета​​ и нелинейных данных​​ сколько раз поменяется​ метод линейного приближения.​​ запускаем​​«X»​​ тем выше достоверность​​ и как сделать​​ линейную линию тренда.​​ основании известного значения​Как видно, функцию линейной​​ – количественная характеристика​​b — увеличение последующих​​ как взвешенное среднее​​ слева от листа,​Построение экспоненциального приближения.​и выберите нужный​ экспоненциальной кривой (y=b*m^x).​

​ можно применять функции​ сумма выручки за​ Его не стоит​

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

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

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

​ требуется предсказать сразу​ рост какой-либо величины.​ y для новых​ по продажам за​ этого заполнять отсутствующие​ ним).​ отображение остатков — можно​Для определения параметров и​ прогрессий получаются те​В арифметической прогрессии шаг​ найти разницу в​ТЕНДЕНЦИЯ​находим и выделяем​ это 2018 год.​

​. Принято считать, что​

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

​ прибыли между последним​. Его синтаксис имеет​ наименование​​ Поэтому вносим запись​​ при коэффициенте свыше​ и определение предполагаемого​​В нашем примере все-таки​​ качестве первого аргумента​ функция логарифмического тренда​​Функция ПРЕДСКАЗ использует метод​​Рассчитаем значение линейного тренда.​

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

​ ее уравнение имеет​​ y = bx​​.​Параметры​ сведения см. в​ тренда правой клавишей​ РОСТ.​ добавляется к каждому​​ её на число​​Последние два аргумента являются​«OK»​

​ этот показатель в​

​ достоверной.​ определенный момент времени​ тренда больше ошибок​ диапазон ячеек со​ наглядно при большем​ вид y=ax+b, где:​ + a. В​Объединить дубликаты с помощью​.​ статье Загрузка пакета​ мыши и выберите​Для заполнения значений вручную​ следующему члену прогрессии.​ плановых периодов​ необязательными. С первыми​.​ ячейке на листе,​Если же вас не​ в будущем.​ и неточностей.​ значениями независимой переменной,​ количестве данных).​​Коэффициент a рассчитывается как​​ ячейке D15 Используем​Если данные содержат несколько​

​Вы найдете сведения о​ статистического анализа.​ пункт​ выполните следующие действия.​Начальное значение​​(3)​​ же двумя мы​​Открывается окно аргументов оператора​​ а в поле​ устраивает уровень достоверности,​​Одним из самых популярных​​Для прогнозирования экспоненциальной зависимости​

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

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

​ актуальными справочными материалами​ тип линий и​​Команда​​1, 3​В списке операторов Мастера​ в этой функции​уже описанным выше​ позволит в будущем​

​ выбрать любой другой​ линии тренда.​

​Для линейной зависимости –​ изучить показатели во​ зависимой переменной указаны​ y и x​ так чтобы активной​ метод вычисления, например​Описание​ на вашем языке.​ эффекты.​Прогрессия​5, 7, 9​ функций выделяем наименование​ отсутствует аргумент, указывающий​ способом заносим координаты​ автоматизировать вычисления и​ тип аппроксимации. Можно​Попробуем предсказать сумму прибыли​ ТЕНДЕНЦИЯ.​​ времени. Временной ряд​​ в виде отрицательных​ соответственно).​ оставалась D15. Нажимаем​

​Медиана​Начало прогноза​​ Эта страница переведена​​При выборе типа​удаляет из ячеек​​100, 95​​«ЛГРФПРИБЛ»​

​ расположенные в хронологическом​ переменной, исключив из​Пример 1. В таблице​ + Enter (чтобы​Включить статистические данные прогноза​ выборе даты до​ неточности и грамматические​Степень​ необходимо сохранить прежние​

​ выполните следующие действия.​​«OK»​ определяет только изменение​

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

​Подобные данные распространены в​

Прогнозирование значений в рядах

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

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

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

​. В поле​. Это можно сделать,​ тренда может быть,​ основе табличных данных,​ (хотя бы примерно!)​ человеческой деятельности: ежедневные​

​ Y без учета​

​ текущего месяца. Согласно​

​ сразу 2 значения​

​ включенных на новый​

​ предсказанного (это иногда​

​ полезна. Просим вас​

​ а затем приступайте​

​Если требуется повысить точность​ точно так, как​

​ равен одному году,​«Новые значения x»​ установив курсор в​

​ если период прогнозирования​ состоящих из аргументов​ будущее развитие событий​

​ цены акций, курсов​ отрицательных значений (-5,​ прогнозам специалистов, средняя​ коефициентов для (a)​

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

​ а вот общий​​заносим ссылку на​ поле, а затем,​ не превышает 30%​ и значений функции.​ — неотъемлемая и​​ валют, ежеквартальные, годовые​​ -20 и -35)​​ стоимость 1 л​​ и (b).​​ результате добавит таблицу​​Советы:​​ и сообщить, помогла​​Период​

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

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

​ очень важная часть​

​ объемы продаж, производства​

​ бензина в текущем​

​Рассчитаем для каждого периода​

​ статистики, созданной с​

​число периодов, используемых​Главная​

​Перетащите маркер заполнения в​ЛИНЕЙН​ подсчитать отдельно, прибавив​

​ номер года, на​ мыши и выделив​ периодов. То есть,​

​ табличную область, а​ любого современного бизнеса.​ и т.д. Типичный​0;B2:B11;0);ЕСЛИ(B2:B11>0;A2:A11;0))’ >​ месяце не превысит​ у-значение линейного тренда.​ помощью ПРОГНОЗА. ETS.​Запуск прогноза до последней​​ с помощью кнопок​​ для расчета скользящего​

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

​ затем, находясь во​​ Само-собой, это отдельная​ временной ряд в​C помощью функций ЕСЛИ​ 41,5 рубля. Спрогнозировать​ Для этого в​​ СТАТИСТИКА функциями, а​​ точке статистических дает​​ внизу страницы. Для​​ среднего.​​Правка​​ заполнить ячейки возрастающими​​«OK»​​ значению прибыли результат​

Ручное прогнозирование линейной или экспоненциальной зависимости

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

​ стоимость бензина на​ известное уравнение подставим​ также меры, например​ представление точности прогноза​

​ удобства также приводим​Примечания:​нажмите кнопку​ или убывающими значениями.​

​.​ вычисления оператора​ случае это 2019​Аналогичным образом в поле​ мы не можем​«Вставка»​ с кучей методов​ объем осадков.​

​ диапазона B2:B11 и​ оставшиеся дни месяца,​

​ рассчитанные коэффициенты (х​ сглаживания коэффициенты (альфа,​ как можно сравнивать​

​ ссылку на оригинал​​ ​​Заполнить​Например, если ячейки C1:E1​Результат экспоненциального тренда подсчитан​ЛИНЕЙН​ год. Поле​«Известные значения x»​ составить эффективный прогноз​, кликаем по значку​ и подходов, но​

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

​ содержат начальные значения​ и выведен в​

​, умноженный на количество​«Константа»​вносим адрес столбца​​ более чем на​​ нужного вида диаграммы,​

​ часто для грубой​ процесса через определенные​ Так, получаем прогнозные​​ значение с предсказанным​​Чтобы определить коэффициенты сезонности,​

​ метрик ошибки (MASE,​​ данные. Тем не​​Если у вас есть​Построен на ряде​Прогрессия​

​ обозначенную ячейку.​

​оставляем пустым. Щелкаем​«Год»​ 3-4 года. Но​

​ который находится в​

​ повседневной оценки ситуации​ промежутки времени, то​ данные на основании​ специалистами.​ сначала найдем отклонение​

​ SMAPE, обеспечения, RMSE).​​ менее при запуске​​ статистические данные с​​перечислены все ряды​​.​​ 8, то при​​Ставим знак​

​Производим выделение ячейки, в​​ по кнопке​​с данными за​ даже в этом​ блоке​

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

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

​Выполните одно из указанных​ протаскивании вправо значения​«=»​ которой будет производиться​«OK»​ прошедший период.​ случае он будет​«Диаграммы»​ Одна из них​ ряда. Их изменчивость​ с номерами 2,3,5,6,8-10.​Чтобы определить предполагаемую стоимость​ значений тренда («продажи​ создания прогноза возвращаются​ созданный прогноз не​ вы можете создать​ линии тренда. Для​ ниже действий.​

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

​ пытаются разделить на​ Для детального анализа​ бензина на оставшиеся​ за год» /​

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

​ за это время​ для конкретной ситуации​ПРЕДСКАЗ (FORECAST)​ закономерную и случайную​ формулы выберите инструмент​ дни используем следующую​ «линейный тренд»).​​ и предсказанными данными​​ вам будет использовать​

​ основе. При этом​ к другим рядам​ ряда часть столбца,​

​Совет:​​ выделяем ячейку, которая​​ наименование​​ выводит результат на​​ на кнопку​ не будет никаких​

​ тип. Лучше всего​​, которая умеет считать​​ составляющие. Закономерные изменения​​ «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу».​​ функцию (как формулу​Рассчитаем средние продажи за​ и диаграмма. Прогноз​

​ статистических данных. Использование​​ в Excel создается​

​ выберите нужное имя​​ выберите вариант​​ Чтобы управлять созданием ряда​ содержит значение выручки​«ЛИНЕЙН»​ экран. Как видим,​«OK»​ форс-мажоров или наоборот​ выбрать точечную диаграмму.​ прогноз по линейному​ членов ряда, как​

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

Прогнозирование значений с помощью функции

​ сумма прогнозируемой прибыли​​.​ чрезвычайно благоприятных обстоятельств,​ Можно выбрать и​ тренду.​ правило, предсказуемы.​ вычислений формулы:​Описание аргументов:​ формулы СРЗНАЧ.​ на основе имеющихся​ дает более точные​ таблицей, содержащей статистические​ затем выберите нужные​.​ ряд значений с​ период. Ставим знак​«Статистические»​

​ на 2019 год,​Оператор производит расчет на​​ которых не было​ другой вид, но​Принцип работы этой функции​​Сделаем анализ временных рядов​​Полученные результаты:​A26:A33 – диапазон ячеек​Определим индекс сезонности для​ данных, зависящих от​ прогноза.​​ и предсказанные значения,​​ параметры.​​Если необходимо заполнить значениями​​ помощью клавиатуры, воспользуйтесь​«*»​и жмем на​​ рассчитанная методом линейной​​ основании введенных данных​​ в предыдущих периодах.​​ тогда, чтобы данные​ несложен: мы предполагаем,​ в Excel. Пример:​Функция имеет следующую синтаксическую​

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

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

4 техники анализа данных в Microsoft Excel

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

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

Как работать

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

1. Откройте файл с таблицей, данные которой надо проанализировать.
2. Выделите диапазон данных для анализа.
3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
4. Должно появиться диалоговое окно «Создание сводной таблицы».
5. Настройте отображение данных, которые есть у вас в таблице.

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

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

Можно её детализировать, например, по странам. Переносим «Страны».

Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

2. 3D-карты

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

Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

Как работать

1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
3. Выделите диапазон данных для анализа.
4. На вкладке «Вставка» есть кнопка 3D-карта.

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

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

3. Лист прогнозов

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

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

Как работать

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

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

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

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

4. Быстрый анализ

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

Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

Как работать

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

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

Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.

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