Меню

Сводная таблица как посчитать прирост

Сводная таблица как посчитать прирост

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

Файлы для скачивания:

Файл Описание Размер файла: Скачивания
Пример 109 Кб 3450

Допустим, мы имеем такую сводную таблицу:

Как проводить вычисления в сводной таблице?

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

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

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

Как включить видео?

Чтобы увидеть видео включите в браузере GIF-Анимацию.

В открывшемся диалоговом окне «Параметры поля значений», меняем название поля, например, на «Доля»:

kak-provodit-vychisleniya-v-svodnoj-tablitse_2.png

Переходим во вкладку «Дополнительные вычисления», в поле со списком (там, где написано «Без вычислений») выбираем пункт «% от суммы по столбцу» и нажимаем кнопку «ОК»:

kak-provodit-vychisleniya-v-svodnoj-tablitse_3.png

После чего ваша Сводная таблица должна будет приобрести следующий вид:

kak-provodit-vychisleniya-v-svodnoj-tablitse_4.png

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

Еще один пример вычислений, имеем такую Сводную таблицу:

kak-provodit-vychisleniya-v-svodnoj-tablitse_5.png

В одном столбце у нас «Сумма продаж», в другой «Прибыль», нам необходимо получить рентабельность каждой категории товаров. Для этого устанавливаем курсор на сводную таблицу, в главном меню в разделе «Работа со сводными таблицами», во вкладке «Анализ» (в 2007 и 2010 «Параметры»), в разделе «Вычисления», нажимаем кнопку «Поля, элементы и наборы», в выпавшем списке выбираем пункт «Вычисляемое поле. » (в 2007 «Сервис» -> «Формулы»):

kak-provodit-vychisleniya-v-svodnoj-tablitse_6.png

В открывшемся диалоговом окне меняем название поля, например, на «Рентабельность»:

kak-provodit-vychisleniya-v-svodnoj-tablitse_7.png

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

Источник



Дополнительные вычисления в сводных таблицах

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

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

пример сводной таблицы

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

  1. Процентная структура продаж.
  2. Продажи нарастающим итогом.
  3. Продажи с темпами роста.

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

1. Процентная структура продаж

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

Шаг 1. Постройте сводную таблицу, где в области строк Города и Товары, а в области сумм — Доходы (если вы не знаете, как создать сводную таблицу, посмотрите статью «Как построить сводную таблицу в Excel»).

Сводная таблица

Шаг 2. Щелкаем правой кнопкой мыши по любому числу в сводной таблице и выбираем раздел:
Дополнительные вычисления → % от общей суммы. В появившемся меню доступно несколько способов вычисления процентов:

а) % от общей суммы – рассчитывается к итоговой сумме, от «угла».

вычисления в сводных таблицах

Если переместить данные по Городам в область строк, а Товары в столбцы, мы увидим, что общий процент считается как по строкам, так и по колонкам, и сумма процентов равна 100%.

б) % от суммы по столбцу или по строке.
Если требуется рассчитать структуру продаж, например, только по Городам, выбираем % от суммы по столбцу. Если только по товарам, соответственно – по строке.

в) А если нужно видеть структуру продаж и по товарам, и по городам? Не проблема! Нужно выбрать % от суммы по родительской строке.
Тогда процент рассчитается от суммы группы, а не от общего итога. А сумма процентов внутри группы будет равна 100%.

вычисления в сводной таблице

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

несколько одинаковых столбцов в сводной

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

2. Продажи нарастающим итогом

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

Шаг 1. Постройте сводную таблицу. В строки поместите Города, в столбцы — Месяцы.

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

нарастающий итог в сводной таблице

Шаг 3. В открывшемся окне выбираем, что нарастание нужно по Месяцам и все готово!
Можно выбрать, относительно какого поля будет идти нарастание – строк и столбцов, городов или месяцев. В нашем случае выбран вариант нарастающего итога по месяцам. Кстати, столбец Общий итог пустой, потому что нарастающий итог рассчитан в декабре.

3. Темпы роста

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

Шаг 1. В новую сводную таблицу добавляем в строки Города, в столбцы Месяцы. В область значений – два одинаковых столбца Доходы.
Когда в области Значений появляется более двух полей, в столбцах появляется «виртуальное» поле «∑ Значения», которое определяет размещение данных в сводной таблице – по строкам или столбцам. Переместите «∑ Значения» в область строк.

Несколько одинаковых полей в сводной

Шаг 2. Щелкаем правой кнопкой мышки по числам одного из полей сводной таблицы и выбираем Дополнительные вычисления → Приведенное отличие. Указываем Базовое поле «месяцы», элемент – «назад».

Приведенное отличие в сводной таблице

Январь будет пустым, потому что перед ним нет других данных. Это место можно занять спарклайнами. Чтобы их добавить, перейдите в меню Вставка → Спарклайны → График.

Источник

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

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

Подсчет процентов в табличном редакторе

Табличный редактор хорош тем, что большую часть вычислений он производит самостоятельно, а пользователю необходимо ввести только исходные значения и указать принцип расчета. Вычисление производится так: Часть/Целое = Процент. Подробная инструкция выглядит так:

При работе с процентной информацией ячейке необходимо задать соответствующий формат.

formula-prirosta-v-procentah-v-excel

  1. Жмем на необходимую ячейку правой клавишей мышки.
  2. В возникшем маленьком специальном контекстном меню необходимо выбрать кнопку, имеющую наименование «Формат ячеек».

1

  1. Здесь необходимо щелкнуть левой клавишей мышки на элемент «Формат», а затем при помощи элемента «ОК», сохранить внесенные изменения.

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

  1. У нас есть три колонки в табличке. В первой отображено наименование продукта, во второй – запланированные показатели, а в третьей – фактические.

formula-prirosta-v-procentah-v-excel2

  1. В строчку D2 вводим такую формулу: =С2/В2.
  2. Используя вышеприведенную инструкцию, переводим поле D2 в процентный вид.
  3. Используя специальный маркер заполнения, растягиваем введенную формулу на всю колонку.

formula-prirosta-v-procentah-v-excel3

  1. Готово! Табличный редактор сам высчитал процент реализации плана для каждого товара.

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

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

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

formula-prirosta-v-procentah-v-excel4

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

formula-prirosta-v-procentah-v-excel5

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

formula-prirosta-v-procentah-v-excel6

  1. Плюсовые показатели указывают на прирост, а минусовые – на уменьшение.

Расчет темпа прироста в табличном редакторе

Разберемся детально в том, как произвести расчет темпа прироста в табличном редакторе. Темп роста/прироста означает изменение определенного значения. Подразделяется на два вида: базисный и цепной.

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

formula-prirosta-v-procentah-v-excel7

Базисный темп роста обозначает отношение процента к базисному показателю. Формула базисного темпа роста выглядит следующим образом:

formula-prirosta-v-procentah-v-excel8

Предыдущий показатель – это показатель в прошедшем квартале, месяце и так далее. Базисный показатель – это начальный показатель. Цепной тем прироста – это вычисляемая разница между 2 показателями (настоящий и прошлый). Формула цепного темпа прироста выглядит следующим образом:

formula-prirosta-v-procentah-v-excel9

Базисный темп прироста – это вычисляемая разница между 2 показателями (настоящий и базисный). Формула базисного темпа прироста выглядит следующим образом:

formula-prirosta-v-procentah-v-excel10

Рассмотрим все детально на конкретном примере. Подробная инструкция выглядит так:

  1. К примеру, у нас есть такая табличка, отражающая доход по кварталам. Задача: вычислить темпы прироста и роста.

formula-prirosta-v-procentah-v-excel11

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

formula-prirosta-v-procentah-v-excel12

  1. Мы уже выяснили, что такие значения высчитываются в процентах. Нам необходимо задать для таких ячеек процентный формат. Жмем на необходимый диапазон правой клавишей мышки. В возникшем маленьком специальном контекстном меню необходимо выбрать кнопку, имеющую наименование «Формат ячеек». Здесь необходимо щелкнуть левой клавишей мышки на элемент «Формат», а затем при помощи кнопки «ОК», сохранить внесенные изменения.
  2. Вводим такую формулу для подсчета цепного темпа роста и копируем в нижние ячейки.

formula-prirosta-v-procentah-v-excel13

  1. Вводим такую формулу для базисного цепного темпа роста и копируем в нижние ячейки.

formula-prirosta-v-procentah-v-excel14

  1. Вводим такую формулу для подсчета цепного темпа прироста и копируем в нижние ячейки.

formula-prirosta-v-procentah-v-excel15

  1. Вводим такую формулу для базисного цепного темпа прироста и копируем в нижние ячейки.

formula-prirosta-v-procentah-v-excel16

  1. Готово! Мы реализовали подсчет всех необходимых показателей. Вывод по нашему конкретному примеру: в 3 квартале плохая динамика, так как темп роста составляет сто процентов, а прирост положительный.

Заключение и выводы о вычислении прироста в процентах

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

Источник

Прирост в процентах

Как посчитать прирост в процентах между двумя числами? Используйте для расчета бесплатный калькулятор темпа прироста. Укажите начальное значение и фактический показатель, алгоритм выведет ответ по формуле: (B * 100 / A) — 100 = C.

Сохранить ссылку

Отправить коллегам

Справка

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

Зная формулу и применяя онлайн инструменты, рассчитать прирост будет не сложно.

Давайте разберемся на простом случае. Например, в феврале вы получили через интернет-магазин 602 заказа. В марте вы запустили контекстную рекламную кампанию и сделали E-mail рассылку по базе подписчиков. Количество заказов немного подросло и составило 964.

Получается, чтобы рассчитать разницу, вам нужно — (964 * 100% / 602) — 100 = 60,13%.

Инструкция

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

  1. Установите формат в котором будет производиться расчет — в процентах или штуках;
  2. В левое окошко укажите абсолютное значение;
  3. Во втором окне добавляем фактическое значение.

Программа моментально сравнит и выведет ответ.

Обратная связь

Если вы нашли ошибку в работе скрипта, то обязательно пишите на контактную почту info@konstantinbulgakov.com. Будем разбираться!

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

Прирост в процентах: 5 комментариев

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

Александр, спасибо за информацию. Сделаем!

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

Как быть если начальное или конечное 0?

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

Источник

Читайте также:  Один день продолжительность таблица
Adblock
detector