Бизнесхак на каждый день. Экономьте время, деньги и силы

В Excel можно построить простой прогноз продаж или другого показателя – с учетом сезонности или без.

Самый простой способ – добавить линию тренда на график с показателем.

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

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

Результат:

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

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

.

• Известные_значения_y – старые значения показателя, значения которого вы будете прогнозировать;

• известные_значения_x – значения фактора, влияющего на зависимый показатель y (в качестве x могут выступать и периоды – как в нашем примере);

• x – новое значение фактора.

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

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

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

Это пример с делением на средние продажи:

А это – с делением на тренд:

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

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

Например, для декабря показатель может составить 1,29, то есть в этот месяц высокого сезона показатель (допустим, выручка) на 129 % выше «обычного». Для января, наоборот, коэффициент может быть равен 0,86, к примеру.

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

Если вы хотите построить многопараметрическую регрессию для прогнозирования показателя, зависящего от нескольких факторов, вам понадобится функция ЛИНЕЙН (LINEST). Ее описание, к сожалению, выходит за рамки этой книги – см. список литературы в конце раздела. Кроме того, вы можете обратиться к сайту planetaexcel.ru, другим порталам или справке Excel.

Горячие клавиши Excel

Чем меньше вы в Excel пользуетесь с мышью, тем быстрее вы работаете.

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

Почти все пользователи знают про стандартные сочетания вроде Ctrl + C и Ctrl + V, но ведь этим возможности программы не ограничиваются.

Очень удобны сочетания клавиш для быстрого выделения и перемещения по таблицам.

• Выделить всю таблицу – Ctrl + A

.

• Переместиться в конец таблицы – Ctrl + стрелки.

• Выделить таблицу до конца – Ctrl + Shift + стрелки.

• Конец рабочего листа – Ctrl + End.

• Начало рабочего листа – Ctrl + Home.

Еще несколько полезных сочетаний:

• Установить или убрать фильтр – Ctrl + Shift + L.

• Создать новый лист – Shift + F11.

• Сохранить файл – Shift + F12.

• Переход на предыдущий/следующий лист – Ctrl + PgDown/PgUp.

• Процентный формат ячеек – Ctrl + Shift + 5.

• Денежный формат ячеек – Ctrl + Shift + 4.

• Формат «дата» – Ctrl + Shift + 3.

• Полужирный шрифт – Ctrl + B

.

• Курсив – Ctrl + I.

• Подчеркнутый – Ctrl + U.

• Вставить текущую дату – Ctrl + Ж.

• Вставить текущее время – Ctrl + Shift + Ж.

• Повторение последнего действия – F4 (работает далеко не для всех действий. Очень удобно для быстрого многократного повторения вставки строки или столбца).

• Вернуться в активную ячейку – Ctrl + Backspace.

• Режим выделения (в котором можно выбирать несвязные диапазоны, не удерживая Ctrl) Shift + F8.

• Выделить всю строку Ctrl + пробел.

• Выделить весь столбец Shift + пробел.

• Создание диаграммы из выделенных данных Alt + F1.

• Создание сводной таблицы Alt + D + P.

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

Источники данных: