График сравнения объема продаж с прошлым месяцем в Excel

Argument ‘Topic id’ is null or empty

© Николай Павлов, Planetaexcel, 2006-2021
info@planetaexcel.ru

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

За изображения спасибо Depositphotos.com

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071

Виды моделей временного ряда

Обычно, выделяют две модели временного ряда и третью — смешанную.

    Аддитивная модель

Мультипликативная модель

Смешанная модель

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

Амплитуда сезонной составляющей

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Как сделать в Excel диаграмму накопительной суммы?

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

Создать диаграмму накопительных сумм в Excel

1. Выберите пустую ячейку рядом с целевым столбцом, в этом случае выберите ячейку C2 и введите эту формулу = СУММ (Б $ 2: В2), а затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу . Смотрите скриншот:

2. Затем выберите диапазоны столбцов Target и Total, а затем щелкните Вставить > Колонка > Кластерный столбец, см. снимок экрана:

3. На вставленной диаграмме щелкните один столбец итогов и щелкните правой кнопкой мыши, чтобы выбрать Изменить тип диаграммы серии… Из контекстного меню. Смотрите скриншот:

4. В Изменить тип диаграммы диалоговое окно, нажмите Комбо на левой панели, а затем выберите Кластерный столбец тип диаграммы на правой панели, см. снимок экрана:

Внимание: Если вы используете Excel 2010 и более раннюю версию, выберите линия на левой панели, а затем выберите один тип линейной диаграммы на правой панели, см. снимок экрана:

5. Нажмите OK, а затем щелкните правой кнопкой мыши линию на диаграмме и выберите Добавить метки данных > Добавить метки данных из контекстного меню. Смотрите скриншот:

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

Добавляйте накопительную сумму для диаграммы в Excel с помощью удобной функции

Если у вас есть Kutools for Excel, С его Добавить совокупную сумму в диаграмму функция, вы можете вставить кумулятивную итоговую сумму для диаграммы всего за несколько кликов.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Сначала вставьте диаграмму на основе ваших данных, см. Снимок экрана:

2. Затем щелкните, чтобы выбрать диаграмму, а затем щелкните Kutools > Графики > Инструменты для диаграмм > Добавить совокупную сумму в диаграмму, см. снимок экрана:

3. А затем нажмите Да в появившемся окне подсказки и щелкните OK в следующем поле см. снимки экрана:

4.Теперь на график добавлено значение накопленной суммы, см. Снимок экрана:

Как суммировать данные по дням недели / месяцу / кварталу / году в Excel?

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

  • Суммирование данных за месяц / квартал / год с помощью сводной таблицы
  • Суммируйте данные по дням недели / месяцам / годам с помощью Kutools for Excel

Hot

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Суммирование данных за месяц / квартал / год с помощью сводной таблицы

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

1. Выберите исходные данные, которые вы будете суммировать по месяцам / кварталам / годам, и нажмите Вставить > PivotTable (или Вставить > PivotTable > PivotTable). Смотрите скриншот:

2. В открывшемся диалоговом окне «Создание сводной таблицы» укажите место, в которое вы поместите созданную сводную таблицу, и щелкните значок OK кнопка. Смотрите скриншот выше:

3. Теперь справа от листа открывается панель «Поля сводной таблицы». Перетащите поле «Дата» (поле, по которому вы будете суммировать) в Ряды раздел и перетащите поле Сумма (поле, которое вы суммируете) в Ценности раздел. См. Снимок экрана ниже:

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

4. Теперь перейдите к сводной таблице, щелкните правой кнопкой мыши любую дату в заголовки строк столбец и выберите группы из контекстного меню. Смотрите скриншот ниже:

5. В открывшемся диалоговом окне «Группировка» щелкните, чтобы выделить критерий, по которому вы будете суммировать By и нажмите OK кнопка. В нашем случае мы нажимаем, чтобы выделить Месяцев. Смотрите скриншот выше:

На данный момент мы создали сводную таблицу и суммируем суммы по месяцам, как показано ниже:

Суммируйте данные по дням недели / месяцам / годам с помощью Kutools for Excel

Вы могли заметить, что сводная таблица не может суммировать данные по дням недели. Не волнуйтесь! Этот метод представит утилиты Kutools for Excel для простого суммирования данных по дням недели, месяцам или годам следующим образом:

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

1. Выберите ячейки даты, по которым вы будете суммировать данные, и нажмите Kutools > Формат > Применить форматирование даты.

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

В нашем случае мы щелкаем, чтобы выделить формат даты «среда».

3. Продолжайте выбирать ячейки даты и нажмите Kutools > К фактическому для преобразования дат в текст названий дней недели.

4. Выберите диапазон данных, который вы будете суммировать по дням недели, месяцу или году, и нажмите Kutools > Content > Расширенные ряды комбинирования.

5. В открывшемся диалоговом окне «Объединить строки на основе столбца» выполните следующие действия:
(1) Выберите столбец Дата, по которому вы будете суммировать, и нажмите Основной ключ кнопка;
(2) Выберите столбец Сумма, который вы будете суммировать, и нажмите Рассчитать > Сумма.
(3) Задайте правила объединения для других столбцов и нажмите кнопку Ok кнопку.

Теперь вы увидите, что диапазон данных суммирован по дням недели, месяцам или годам в зависимости от ваших настроек. См. Снимок экрана ниже:

Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Метод 5. Анализ по матрице BCG

Цель – определение приоритетных товарных групп, которые в последствие принесут наибольший доход.

Шаблон для расчетов (скачать по ссылке): Анализ BCG

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

После расчетов товары в зависимости от доли рынка и темпов роста рынка распределяются в матрице. Объем продаж отображается с помощью кружков. Результаты анализа оформляются в матрицу BCG, образец ниже.

анализ продаж матрица БКГ

Матрица BCG

Далее для каждого товара принимаем соответствующую стратегию развития. Её определить легко помогает расположение товаров внутри матрицы:

  1. Звезда. Наиболее продаваемые товары, приносящие наибольший доход. Это тренд, как, например, любая суперзвезда, только на полке в магазине. Стратегия: сохраняем лидерство;
  2. Дойная корова. Товары, которые без инвестирования могут приносить неплохой доход. У этих товаров более стабильный жизненный цикл, чем у звезд. Стратегия: получаем прибыль и сохраняем позиции;
  3. Вопрос. Товары, с которыми непонятно что делать: инвестировать в них и доводить до ума, либо же навсегда избавляться. Стратегия: инвестируем дополнительные средства;
  4. Собака. Категория товаров, которые требуют постоянных вложений, но при этом их рентабельность очень низкая. Затраченные на них силы не окупаются. Стратегия: снижаем активность или выводим товар с рынка.

Кстати. Если Вам необходимо отслеживать десятки разных процессов, то рекомендую МойСклад. В нем есть все необходимое: продажи, закупки, склад, финансы, клиенты и поставщики. Кликайте и тестируйте -> МойСклад.

Пример

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

Читайте также:  Формула форматирует число суммы как текст в одной ячейке Excel

Представим, что мы уже провели расчеты и по оси координат определили какой товар куда попадает и получили следующие результаты:

  1. Шоколадные конфеты – это “собаки”. Они дорого обходятся клиентам, однако и себестоимость у них высокая. Такой товар не выгоден для компании;
  2. Пирожные – это “дойные коровы”. Они стабильно приносят высокий доход. Позиции стоит укрепить;
  3. Торты – это “звезды”. Сейчас это модное кондитерское направление, ООО “Тортик” получает высокие доходы от их продажи;
  4. Мороженое – это “вопрос”. Это сезонный товар, объем продаж не стабилен. Можно вложить деньги в расширение ассортимента или сделать акцент на другие группы товаров.

Пример формул таблицы для анализа прогноза продаж в Excel

Ниже на рисунке представлены исходные данные. Допустим по этим данным необходимо составить прогноз продаж на 2020-й год, не смотря на то что собранные статистические данные заканчиваются в декабре 2019-го года. Первым шагом является использование функции ПРЕДСКАЗ и расширение десезонализированных данных на очередные 12 месяцев. Формулы в таблицах:

    Расчет коэффициента сезонности для каждого месяца в году:

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

  1. X – в данном аргументе будет указан месяц, для которого следует получить текущее прогнозируемое значение.
  2. Известные значения y – аргумент содержит десезонализированные данные столбца C.
  3. Известные значения x – здесь указаны месяца соответствующие данным по продажам в столбце A.

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

Как рассчитать темп роста и темп прироста в Excel (Эксель)?

Как в программе Excel посчитать темп роста и темп прироста?

Темп роста (С) определяется как отношение показателя текущего периода (В) к показателю предыдущего периода (А). Рассчитывается в процентах. Если значение выше 100%, то показатель увеличивается, если меньше 100% – снижается.

В Excel формула расчета будет выглядеть так:

Темп прироста (D) рассчитывается как разница между темпом роста (В) и 100. Он показывает на сколько процентов вырос или снизился показатель.

В Excel в строке формул записана формула = С3-100

В Эксель темп роста и темп прироста определяется способом описанным ниже.

Для начала запишем наши показатели в столбик.

Для определения темпа роста необходимо иметь два значения по выручке за два квартала.

Данные показатели рассчитываются в %. Понять темп идёт увеличение или наоборот в сторону снижения можно по значению, если меньше 100%, то показатель идёт к снижению, а больше, наоборот, показатель увеличен.

Темп роста (обозначим буквой С) – отношение показателя за текущий период (В) соответственно к показателю предыдущего периода (А).

Темп прироста (обозначаем буквой D) – разница между темпом роста (В) и 100.

В Excel нужно писать формулу = С3-100

Темп роста и темп прироста характеризуют изменение какого-либо показателя или величины во времени.

Они бывают цепными и базисными.

1) Цепной темп роста – это процентное отношение текущего значения показателя к предшествующему.

2) Базисный темп роста – это процентное отношение текущего значения показателя к базисному.

Предшествующее значение – это значение показателя в прошлом месяце, в прошлом квартале, в прошлом году и т.д.

Базисное значение – это какое-то начальное, стартовое значение показателя.

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

3) Цепной темп прироста – это разница в процентах между текущим значением показателя и предшествующим.

4) Базисный темп прироста – это разница в процентах между текущим значением показателя и базисным.

Другими словами, для получения темпа прироста нужно отнять 100% от соответствующего темпа роста.

Рассмотрим расчёт темпа роста и темпа прироста в Эксель (Excel) на конкретном примере.

В таблице представлены данные о выручке по кварталам:

На основании этих данных нужно посчитать темп роста и темп прироста выручки.

I. Сначала добавим в таблицу Excel 4 столбца, в которые будут записываться формулы:

Цепной темп роста.

Базисный темп роста.

Цепной темп прироста.

Базисный темп прироста.

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

Для этого выделяем ячейки и на панели инструментов "Главная" нажимаем на значок "%" (см. скриншот).

1 квартал в любом случае будет базисом, поэтому строку с ним можно не трогать.

III. Теперь записываем формулы в ячейки.

1) Цепной темп роста.

Для 2 квартала (ячейка D5) формула такая:

То есть ячейку с выручкой за 2 квартал (C5) делим на ячейку с выручкой за 1 квартал (C4).

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

Далее копируем эту формулу в ячейки D6 и D7 – получим цепной темп роста за 3 и 4 квартал соответственно.

2) Базисный темп роста.

Как было сказано выше, при расчёте базисного темпа роста везде участвует определённый базисный (базовый) показатель – в нашем случае это будет выручка за 1 квартал.

Формула для 2 квартала (ячейка E5) будет такой:

Здесь ссылка на ячейку C4 (выручка за 1 квартал) является абсолютной, так как это значение нужно использовать и для последующих вычислений.

Чтобы сделать эту ячейку абсолютной достаточно нажать на клавишу F4.

Теперь при копировании данной формулы в ячейки E6 и E7 ссылка на "базисную" ячейку C4 сохранится.

3) Цепной темп прироста.

Здесь всё очень просто – нужно взять ячейку с цепным темпом роста и отнять от неё единицу.

Так для 2 квартала (ячейка F5) формула получится такой:

Замечу, что отнимаем именно единицу из-за процентного формата ячейки.

Удостовериться в правильности этого действия очень просто: в формуле темпа прироста, которую я приводил выше, можно вынести 100% за скобку.

Тпр = (Тр – 1) * 100%.

4) Базисный темп прироста.

Здесь делаем всё точно также, как и при расчёте цепного темпа прироста, только отнимаем единицу уже из базисного темпа роста.

Формула для 2 квартала (ячейка G5):

В результате можно сделать следующие выводы:

1) В 3 квартале отрицательная динамика, так как темп роста < 100%, а темп прироста отрицательный.

2) Во 2 и 4 кварталах положительная динамика, так как темп роста > 100%, а темп прироста положительный.

Простой план продаж в Excel на основе данных прошлых периодов

gfafik-s-trendom

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

  1. построить график продаж за прошедшие периоды;
  2. построить на графике линию тренда, достаточно хорошо характеризующую эти данные, и получить уравнение линии тренда;
  3. рассчитать прогнозные значения на плановый период, используя уравнение линии тренда.

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

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

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

�?так, скачайте и откройте файл Prognozirovanie-prodazh-prostoi-primer. В таблице представлены фактические данные по выручке компании �?кеа, взятые с официального сайта ikea.com. По этим данным построена обычная диаграмма типа график. Предположим, что у компании есть возможность для дальнейшего роста выручки с той же скоростью, и попробуем построить прогноз на 2015-2020 годы.

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

linia-trenda

В открывшемся окне выберите параметры: Линейная, отметьте Показывать уравнение на диаграмме.

parametri-linii-trenda

На диаграмме появится линия тренда и её уравнение:

gfafik-s-trendom

�?так, уравнение линии тренда выражается формулой «y = 1,6601x + 10,392». Здесь х – номер периода (начинается с единицы), то есть для 2003 год х=1, для 2004 – х=2 и т.д. Рассчитаем прогнозные данные аналогичной формулой. В ячейку В18 добавьте формулу «=(A18-A$3+1)*1,6601+10,392» и протяните до конца таблицы. В этой формуле сначала рассчитывается х для соответствующего года, затем определяется значение выручки по таблице.

Процесс анализа продаж

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

Анализ выполнения плана продаж

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

sales-2

Рис.3 Пример анализа выполнения плана продаж по товарным группам

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

Читайте также:  Примеры формулы ВПР для автозаполнения реквизитов в Excel

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

Анализ динамики продаж по направлениям

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

sales-3

Рис.4 Пример анализа продаж по направлениям

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

Анализ структуры продаж

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

sales-4

Рис.5 Пример анализа структуры продаж ассортимента компании

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

sales-5

Рис.6 Пример анализа себестоимости и рентабельности продаж

АВС анализ

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

sales-6

Рис.7 Пример АВС анализа ассортимента

АВС анализ проводится в разрезе продаж и прибыли 1 раз в квартал.

Контроль остатков

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

sales-7

Рис.8 Пример анализа остатков продукции

  1. Выберите ячейку C3 и введите формулу, показанную ниже.Процентное изменение в Excel
  2. Выберите ячейку C3 и примените к ней процентный формат.Процентное изменение в Excel
  3. Чтобы не повторять 1-й и 2-й шаг еще десять раз, выделите ячейку C3, нажмите на ее правый нижний угол и перетащите его вниз до ячейки С13.Процентное изменение в Excel
  4. Проверьте, всели прошло хорошо.Процентное изменение в Excel
  1. Аналогичным образом, мы можем вычислить общее изменение. На этот раз зафиксируем ссылку на ячейку В2. Выделите ячейку D3 и введите формулу, показанную ниже.Процентное изменение в Excel
  2. Выберите ячейку D3 и применить к ней процентный формат.
  3. Выделите ячейку D3, нажмите на ее правый нижний угол и перетащите его вниз до ячейки D13.
  4. Проверьте, всели прошло хорошо.Процентное изменение в Excel

Объяснение: Когда мы протягиваем (копируем) формулу вниз, абсолютная ссылка ($B$2) остается неизменной, а относительная (B3) изменяется – B4, B5, B6 и т.д. Возможно, этот пример слишком сложен для вас на данном этапе, но он показывает несколько полезных и мощных возможностей, которыми располагает Excel.

Как посчитать проценты в Excel

Подсчет процентных долей в Excel делается элементарно. Надо один раз ввести необходимые числа, и дальше приложение все действия выполнит самостоятельно. Стандартная формула получения процента: доля от числа/число*100. Но если мы осуществляем вычисления через Excel, умножение осуществляется автоматически. Итак, что нам нужно сделать, чтобы определить процент в Excel?

  1. Сперва нам необходимо выставить формат процентов. Для этого делаем правый клик по нужной ячейке, а затем выбираем опцию «Формат ячеек». Когда появится диалоговое окно, нам следует выбрать правильный формат. Прирост в процентах - формула в ExcelПрирост в процентах - формула в Excel
  2. Также выставление формата возможно через главное меню. Нужно найти вкладку «Главная», перейти в нее и найти группу инструментов «Число». Там есть поле ввода формата ячейки. Нужно нажать на стрелочку возле него и выбрать в перечне тот, который нужен. Прирост в процентах - формула в Excel

Прирост в процентах - формула в Excel

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

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

  1. Записываем в ячейку D2 формулу =C2/B2. То есть, нам нужно разделить фактическое выполнение задачи в числителе, а в знаменателе – запланированное.
  2. После этого описанным ранее методом переводим формат в процентный.
  3. Далее распространяем формулу на оставшиеся ячейки с помощью маркера автозаполнения.

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

Процент от числа

Предположим, мы знаем, сколько процентов должна составлять часть от числа. И поставлена задача определить, сколько будет составлять эта часть в числовом виде. Для этого надо применить формулу =процент%*число. Предположим, по условиям задачи требуется определить, сколько составит 7% от семидесяти. Чтобы решить ее, необходимо:

  1. Сделать клик по правильной ячейке и ввести туда следующую формулу: =7%*70. Прирост в процентах - формула в Excel
  2. Нажать на клавишу Enter, и результат будет записан в этой ячейке. Прирост в процентах - формула в Excel

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

Процент от суммы

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

Прирост в процентах - формула в Excel

  1. Если необходимо записать результат вычисления процента от одной конкретной ячейки, нам необходимо записать в знаменателе абсолютную ссылку. Для этого необходимо перед адресом строки и колонки поставить по одному знаку доллара ($).
  2. Поскольку наше итоговое значение записано в ячейке B10, то необходимо закрепить ее адрес, чтобы при распространении формулы в другие ячейки он не изменялся. Для этого записываем следующую формулу: =B2/$B$10.
  3. Затем необходимо изменить формат всех ячеек этого ряда на процентный. После этого с помощью маркера автозаполнения перетягиваем формулу на все остальные строки.

Можем проверить получившийся результат. Поскольку использованная нами ссылка была абсолютной, знаменатель в формуле не изменился в других ячейках. Если бы мы не ставили знак доллара, то адрес «съехал» бы вниз. Так, в следующей строке уже в знаменателе был бы адрес B11, дальше – B12 и так далее.

Но что же делать, если нужная информация распределена по всей таблице? Чтобы решить эту усложненную задачу, необходимо использовать функцию СУММЕСЛИ. Она проверяет значения в диапазоне на предмет соответствия заданным критериям, и если они соответствуют, суммирует их. После этого нужно получить процент от получившегося значения.

Сама формула в общем виде имеет следующий синтаксис: =СУММЕСЛИ (диапазон критерий; диапазон сложения)/общая сумма». В английской версии программы эта функция называется SUMIF. Давайте объясним, как работает описанная выше формула:

Прирост в процентах - формула в Excel

  1. Под диапазоном значений в нашем случае подразумеваются названия продуктов. Они находятся в первой колонке.
  2. Диапазон сложения – это все значения, которые содержатся в колонке B. То есть, в нашем случае – это количество продуктов каждой товарной позиции. Эти значения должны складываться.
  3. Критерий. В нашем случае им выступает название фрукта.
  4. Результат же записан в ячейке B10.

Если адаптировать описанную выше общую формулу к нашему примеру, она будет выглядеть следующим образом: =СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10. И скриншот для наглядности.

Прирост в процентах - формула в Excel

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

Как рассчитать изменения в процентах

А теперь давайте разберемся, что нужно делать для определения прироста или падения определенного значения по сравнению с аналогичным прошлым периодом. Встроенный функционал Excel вполне позволяет осуществлять подобные расчеты. Чтобы это сделать, необходимо воспользоваться формулой, которая в общематематическом виде (не адаптированная под Excel) выглядит следующий образом: (B-A)/A = разница. А как же осуществляется расчет изменения в процентах в Excel?

Прирост в процентах - формула в Excel

  1. Допустим, у нас есть таблица, в которой в первой колонке записан товар, который мы анализируем. Во втором и третьем столбцах указана его стоимость за август и сентябрь соответственно. А в четвертой колонке мы будем вычислять прирост или падение в процентах.
  2. Соответственно, необходимо в первой ячейке после заголовка в столбце D записать формулу расчета процентного изменения в первом ряду. =(С2/В2)/В2.
  3. Далее используем автозаполнения, чтобы растянуть формулу на всю колонку.

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

  1. Во второй колонке указана информация о продажах в каждом конкретном месяце.
  2. В третьей колонке мы осуществляем расчет изменений в процентах. Используемая нами формула: =(B3-B2)/B2 . Прирост в процентах - формула в Excel
  3. Если требуется сравнение значений с четко определенным показателем, который находится в конкретной ячейке, то ссылку делаем абсолютной. Допустим, если нам надо выполнить сравнение с январем, то наша формула будет следующей. Посмотреть на нее можно на скриншоте. Прирост в процентах - формула в Excel
Читайте также:  Самоучитель Excel с примерами для пользователей среднего уровня

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

Расчет значения и общей суммы

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

Прирост в процентах - формула в Excel

  1. Общая формула, которая будет нами использоваться – Общая сумма * % = Значение.
  2. Ставим курсор в ячейку C2. В ней записываем формулу, указанную на скриншоте.
  3. Таким образом, наценка, вызванная налогом, составит 104,5 долларов. Следовательно, общая стоимость ноутбука составит 1054 доллара и 50 центов.

Воспользуемся другим примером, чтобы продемонстрировать второй метод расчета. Предположим, мы покупаем ноутбук стоимостью 400 долларов, и продавец говорит, что в стоимость уже входит 30% скидки. И нас берет любопытство, а какая же первоначальная цена? Чтобы это сделать, нужно следовать такому алгоритму:

  1. Сначала мы определяем долю, которая оплачивается нами. В нашем случае это 70%.
  2. Чтобы найти первоначальную цену, нам необходимо разделить долю на процент. То есть, формула будет следующая: Часть/% = Общая сумма
  3. В нашем примере в первой колонке записана стоимость ноутбука, во втором – итоговый процент от первоначальной цены, который мы заплатили. Соответственно, итоговый результат записан в третьем столбце, в первую клетку после заголовка которого мы записываем формулу =А2/В2 и изменяем формат ячейки на процентный.

Таким образом, стоимость ноутбука без учета скидки составила 571,43 доллара.

Изменение значения на величину процента

Нам нередко приходится изменять число на определенный процент. Как же это сделать? Реализовать поставленную задачу можно с помощью формулы =Затраты*(1+%). Нужно поставить соответствующие значения в правильные места, и цель достигнута.

Операции с процентами в Excel

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

Планирование продаж в Excel с учётом сезонности

obshii-grafik

Планирование продаж — отправная точка создания бюджета в Excel. Практически все коммерческие компании начинают планирование в Excel с этого шага.

При составлении плана продаж на основе данных прошлых периодов многим компаниям приходится учитывать сезонные колебания спроса. Для расчёта плановых данных необходимо будет учесть фактор сезонности с помощью вычисления так называемого индекса сезонности.

�?ндекс сезонности показывает, насколько конкретный период «выбивается» из основной тенденции повышения или понижения продаж.

Планирование будет состоять в следующем:

  1. Расчёт основной линии тренда (подробно о линии тренда – в предыдущем примере простого планирования) и продолжение тренда на прогнозный период.
  2. Расчёт индекса сезонности для каждого периода.
  3. Расчёт прогнозных данных на основе линии тренда и индекса сезонности.
  4. Отображение фактических и прогнозных данных на одном графике.

Скачайте и откройте файл примера Planirovanie-prodazh-s-uchetom-sezonnosti. В диапазоне С6:С17 расположены фактические данные продаж за 3 года (разбитые на 12 кварталов, это упрощённо; в реальности лучше оперировать месячными данными). Необходимо рассчитать значения продаж на следующие два года.

Расчёт и построение линии тренда

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

dobavlenie-linii-trenda

Затем нужно нажать правой клавишей мыши на линии графика, в открывшемся контекстном меню выберите Добавить линию тренда… В открывшемся окне нужно выбрать параметры: Линейная, отметить Показывать уравнение на диаграмме. На графике появится прямая линия тренда и уравнение, её описывающее.

grafik-s-trendom

Planirovanie-prodazh-s-uchetom-sezonnostiС помощью этого уравнения рассчитываются данные тренда по каждому периоду. В ячейку А6 записана соответствующая формула «=A6*4359+117264», аналогично рассчитываются все значения столбца Е, включая плановые значения линии тренда в диапазоне Е18:Е25.

Расчёт фактического и планового индекса сезонности

Фактический индекс сезонности в Excel рассчитывается как отношение выручки за период к соответствующему значению линии тренда. В ячейке F6 записана формула «=C6/E6», аналогично рассчитаны значения ячеек F7:F17.

Плановый индекс сезонности рассчитывается несколько иначе. В ячейке F18 это значение рассчитано формулой «=СРЗНАЧ(F6;F10;F14)/СРЗНАЧ($F$6:$F$17)»: взято усреднённое значение фактических индексов сезонности за несколько одинаковых периодов (1 квартал) и разделено на среднее по всем индексам сезонности за весь период. Аналогичным образом рассчитываются плановые индексы сезонности по остальным периодам.

Расчёт прогнозных данных

Прогноз выручки рассчитывается на основе линии тренда и плановых индексов сезонности, эти величины нужно просто перемножить: в ячейке D18 формула «=E18*F18».

Отображение данных на одном графике

obshii-grafik

Обратите внимание на то, что фактические и прогнозные данные разнесены по разным столбцам таблицы. Это сделано специально для того, чтобы легко отобразить эти данные на графике разными цветами. Ещё одна хитрость: в ячейку С18 занесена формула «=D18», это нужно, чтобы фактические и прогнозные данные на графике отображались одной линией, если здесь будет пусто – на графике будет разрыв. Таблица готова, на основе диапазона B6:D25 строится обычная диаграмма-график.

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

Вычисление изменения в процентах при помощи формулы прироста

При помощи табличного редактора можно реализовать процедуру сравнения 2 долей. Для осуществления этого действия отлично подходит формула прироста. Если пользователю необходимо произвести сравнение числовых значений А и В, то формула будет иметь вид: =(В-А)/А=разница. Разберемся во всем более детально. Подробная инструкция выглядит так:

  1. В столбике А располагаются наименования товаров. В столбике В располагается его стоимость за август. В столбике С располагается его стоимость за сентябрь.
  2. Все необходимые вычисления будем производить в столбике D.
  3. Выбираем ячейку D2 при помощи левой клавиши мышки и вводим туда такую формулу: =(С2/В2)/В2.
  1. Наводим указатель в нижний правый уголок ячейки. Он принял форму небольшого плюсика темного цвета. При помощи зажатой левой клавиши мышки производим растягивание этой формулы на всю колонку.
  2. Если же необходимые значения находятся в одной колонке для определенной продукции за большой временной промежуток, то формула немножко изменится. К примеру, в колонке В располагается информация за все месяцы продаж. В колонке С необходимо вычислить изменения. Формула примет такой вид: =(В3-В2)/В2.
  1. Если числовые значения необходимо сравнить с определенными данными, то ссылку на элемент следует сделать абсолютной. К примеру, необходимо произвести сравнение всех месяцев продаж с январем, тогда формула примет такой вид: =(В3-В2)/$В$2. С помощью абсолютной ссылки при перемещении формулы в другие ячейки, координаты зафиксируются.
  1. Плюсовые показатели указывают на прирост, а минусовые – на уменьшение.

График сравнения объема продаж с прошлым месяцем в Excel

док сравнить месяц год только 1

Если у вас есть два списка дат, теперь вам нужно сравнивать даты только по месяцам и годам и игнорировать значение дня, если они имеют одинаковый месяц и год, результат должен отображаться как True, в противном случае должен быть False, как показано ниже показан снимок экрана. Как сравнивать даты только с месяцем и годом, но без учета дня в Excel?

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

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

Введите эту формулу в пустую ячейку рядом с вашими данными, = И (МЕСЯЦ (A2) = МЕСЯЦ (B2), ГОД (A2) = ГОД (B2)) , C2, например, а затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и вы получите ИСТИНА, если две даты имеют одинаковый месяц и год, в противном случае вы получите ЛОЖЬ, см. Скриншот:

док сравнить месяц год только 2

1. Вот еще одна более короткая формула, которая также может вам помочь: = (A2-ДЕНЬ (A2)) = (B2-ДЕНЬ (B2)) .

2. В формулах A2 и B2 – это две даты, которые вы хотите сравнить, пожалуйста, измените ссылки на ячейки по своему усмотрению.

Прогнозирование объема продаж в MS Excel

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

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

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

Ссылка на основную публикацию
Adblock
detector