Результат:
Для прогнозирования не на графике, а в диапазоне ячеек можно воспользоваться функцией ПРЕДСКАЗ (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 при добавлении любой фигуры – и ее границы будут растягиваться строго в соответствии с границами ячеек.
Источники данных:
В книге MrExcel XL – 40 Greatest Excel Tips of All Time есть «периодическая таблица горячих клавиш Excel». Она пригодится вам, если вы используете английскую версию Excel.
Полезную памятку с горячими клавишами русской версии можно скачать на сайте Николая Павлова – planetaexcel.ru.
Надстройка Plex
Автор сайта planetaexcel.ru – пожалуй, лучшего ресурса по Excel в российском интернете – создал надстройку PLEX, которая содержит 50 инструментов, отсутствующих в самом Excel. Например, таких:
• функция FindSame, находящая наиболее похожий с искомым текст;
• сравнение двух списков;
• автоматический ввод суммы прописью;
• номер квартала по месяцу и многие другие.
Надстройка на момент написания книги стоит всего 795 рублей, а протестировать (500 запусков) ее можно бесплатно. Могу сказать, что одна функция FindSame окупила стоимость надстройки, сэкономила мне многие часы работы и очень облегчила работу с отчетностью. Она решает важную задачу: позволяет сопоставлять списки элементов, названия в которых совпадают не полностью (пример из моей практики: в одном списке название книги может быть с подзаголовком, в другом – без. Или в одном списке – с точкой, в другом – с двоеточием. Этого достаточно, чтобы для Excel такие названия были разными. Функция FindSame решает эту типичную проблему).
