Макрос позволяет рассчитать процент в сводной таблице Excel
Отобразить в процентном соотношении показатели сводной таблицы с применением процентного формата легче всего воспользовавшись макросом. Он позволит быстро и автоматически показать доли в процентах как по строкам, так и по столбцам сводной таблицы.
Как посчитать процент в сводной таблице макросом
Excel по умолчанию создает сводные таблицы с полями, в которых отображаются суммы итоговых значений:
Тем не менее можно заставить Excel отображать данные не как итоговые значения сумм, а как процентная часть в общем обороте. Или же, например, процентная часть оборота по месяцам (строки таблицы) или по годам (столбцы). Таким образом можно быстро получить общую картину о текущей ситуации фирмы при анализе ее показателей.
Доли в процентах по строкам
Чтобы показать в процентном соотношении значения оборотов по отдельным месяцам, как процентная часть в текущем году, воспользуемся следующим кодом макроса:
Sub ProcentZnach()
With ActiveSheet.PivotTables( «ТаблицаМ» ).PivotFields( «Сумма по полю Оборот» )
.Calculation = xlPercentOfRow
.NumberFormat = «0.00%»
End With
End Su
Напоминаем, чтобы воспользоваться данным кодом макроса сначала откройте редактор VisualBasic (ALT+F11), а потом создайте новый модуль в редакторе: «Insert»-«Module» и введете в него выше указанный VBA-код:
Аргумент «ТаблицаМ» – это ссылка на внутренние имя сводной таблицы, которое было присвоено ей еще при создании. Подробнее об этом читайте пример: Макрос для создания сводной таблицы в Excel.
С помощью присвоения для свойства Calculation параметра xlPercentOfRow — мы определяем способ перерасчета и отображения значений в сводной таблице. И получаем желаемый результат:
Обратите внимание! Отображение значений в таблице в процентном соотношении предоставляет нам много новой и интересной информации. Не смотря на то что, мы используем одни и те же показатели деятельности фирмы. Не смотря на то что, отображая таким способом данные о оборотах фирмы по годам, или любую другую информацию с продолжительного периода, можно быстро и легко проследить динамику изменений. Либо выполнить другие аналитические операции, например, определить тренды (восходящий, нисходящий и боковой). Это позволяет существенно повысить качество принятых решений по устранению ошибок и выбора дальнейших стратегий развития в лучшую сторону.
Примечание. Excel по умолчанию подает значения в крайнем столбце как сумма итоговых значений, хотя в некоторых случаях там могут отображаться и средние значения, а не итоговые суммы.
Доли в процентах по столбцам
Естественно очень простым способом можно настроить макрос так, чтобы значения в сводной таблице отображали объем оборотов как процентное соотношение по отдельным месяцам в очередных годах. Код модифицированного макроса выглядит следующим образом:
Sub ProcentZnach()
With ActiveSheet.PivotTables( «ТаблицаМ» ).PivotFields( «Сумма по полю Оборот» )
.Calculation = xlPercentOfColumn
.NumberFormat = «0.00%»
End With
End Su
Пример VBA-кода в действии:
Применение таких макросов очень полезные и удобное для пользователей, которые теперь могут быстро преобразовывать сводные таблицы в желаемый читабельный вид. Нет необходимости создавать несколько сводных таблиц с одними и теми же данными только в другой структуре отображения значений.
Нет необходимости рыться в тонких настройках сводных таблиц чтобы получить желаемый результат. Достаточно переключаться между макросами, которые автоматически преобразуют показатели разные презентабельные виды.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Отслеживание процента от родительского элемента в сводной таблице Excel 2010
В устаревших версиях Excel использовалась функция % от обшей суммы (% of Total). В результате применения этой функции выясняется, что величина продаж за февраль 2010 года, равная 3651807 долл., составляет 3,42% (ячейка G5, показанная на рис. 3.36) от общего дохода компании, отображенного в строке 30.
Рис. 3.36. Новая функция подсчета промежуточных итогов в Excel 2010 позволяет вычислить процент от родительской строки
Пользователи ресурса MrExcel.com часто задавали вопросы о том, каким образом можно вычислить долю продаж за февраль 2010 года по отношению к общему объему продаж за этот год. В устаревших версиях Excel эта задача была невыполнимой. В Excel 2010 появились три новые функции, вычисляющие проценты от родительских элементов, которые могут применяться в подобных ситуациях. Как показано на рис. 3.36, в столбце F отображается величина продаж (в процентах) для каждого месяца по отношению к величине продаж за 2010 год. В рассматриваемом примере величина продаж за февраль 2010 года составляет примерно 8,05% по отношению к величине продаж за весь 2010 год.
Посмотрите видеоролик на английском языке, демонстрирующий использование функции вычисления процента от родительского элемента.
Вычисляемое поле в Сводных таблицах в MS Excel
В качестве исходной таблицы возьмем таблицу продаж товара по месяцам. В этой таблице также содержится план продаж. Подробности можно посмотреть в файле примера .
Нашей задачей будет:
- вычислить % выполнения плана
- представить полученные данные по годам для каждого месяца (каждый год — отдельный столбец)
В итоге у нас должна получиться вот такая сводная таблица.
Исходная таблица
Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007 ).
На основе даты продажи в столбце А, в таблице рассчитываются 2 столбца: Номер месяца =МЕСЯЦ() и Год =ГОД() . Для форматирования ячеек столбца А в виде окт11 использован пользовательский формат Даты [$-419]МММГГ;@.
Столбец План представляет собой линейный тренд (это не важно для целей данной статьи), столбец Продано — фактический объем продаж.
Сводная таблица
Для создания сводной таблицы выделите любую ее ячейку и в меню Вставка/ Таблицы нажмите кнопку Сводная таблица. В результате появится диалоговое окно.
Нажав ОК, сводная таблица автоматически создастся на новом листе.
В окне Список полей будут отражены названия всех столбцов исходной таблицы. Таким образом, поле — это просто столбец. Вычисляемое поле — это, по сути, вычисляемый столбец.
Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.
Создаем вычисляемое поле
Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’
Это можно сделать непосредственно в Сводной таблице , создав Вычисляемое поле ПроцентВыполнения.
Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :
Появится диалоговое окно:
Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:
- Вместо Поле1 введите название Вычисляемого поля, например, ПроцентВыполнения
- В списке полей выделите поле Продано, руб. и нажмите кнопку Добавить поле или дважды кликните на него. Название поля будет введено в поле Формула
- Введите символ деления / в поле Формула
- В списке полей выделите поле План, руб. и нажмите кнопку Добавить поле
- Нажмите ОК
После проведенных манипуляций в списке поле Сводной таблицы появится еще одно поле. Завершите формирование Сводной таблицы как показано на рисунке ниже, разместив Вычисляемое поле в область Значения.
После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный ).
Обратите внимание, что Сводная таблица содержит Общий итог как по столбцам, так и по строкам.
Теперь разберемся, что Вычисляемое поле нам насчитало.
Вычисляемое поле. Алгоритм расчета
Для каждого месяца у нас есть только одно значение фактических продаж (столбец Продажи) и плана. Вычисляемое поле ПроцентВыполнения возвращает значение равное их отношению. Например, для января 2012 года — это 50,19% (продано было 36992,22, а план был 73697,76). 36992,22/73697,76=0,5019 (см. строку 10 на листе Исходная таблица).
Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?
Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).
В этом состоит одно из ограничений Вычисляемого поля — итоговые значения вычисляются только на основании суммирования.
Аналогично расчет ведется и для итогов по столбцам: находится сумма продаж и плана по годам, затем вычисляется их отношение.
Если бы для каждого месяца в исходной таблице было бы несколько сумм продаж и плановых значений, то расчет был бы аналогичен подсчету итоговых значений.
Чтобы обойти данное ограничение и вычислить, например, средний % выполнения плана для всех январских месяцев, придется отказаться от Вычисляемого поля. Создайте в исходной таблице новый столбец — отношение продажи к плану для каждого месяца (см. лист Исходная таблица2). Затем, создайте на ее основе другую сводную таблицу. В окне параметров полей значений установите Среднее.
В итоговом столбце теперь будет отображаться средний процент выполнения плана.
Изменяем и удаляем Вычисляемое поле
Вызовите тоже диалоговое окно, которое мы использовали для создания Вычисляемого поля. В выпадающем списке выберите нужное поле. Появится его формула, которую можно отредактировать, также как и название этого Вычисляемого поля.
Там же можно удалить это поле.
Еще одно ограничение
Еще одно ограничение Вычисляемого поля проявляется при попытке использовать его в качестве названия Строк или Столбцов Сводной таблицы. Этого сделать нельзя. Покажем это на нашем примере.
Изначально в исходной таблице номер месяца и года вычислялись в отдельных столбцах. Попробуем сделать эти вычисления в Вычисляемом поле.
Создать само Вычисляемое поле для номера месяца — не проблема:
Однако, перенести его в качестве строк сводной таблицы не получается.
Как посчитать долю в сводной таблице в экселе
В файле сводная таблица, в ней посчитаны доли за 2014 и за 2015 год. Необходимо определить динамику этих долей. Как это можно сделать в сводных таблицах?
Функция дополнительные вычисления — «отличие» отлично считает по разнице в количестве, а вот приведенное отличие и непонятно как считает и значения разницы больше нуля не считает. Ссылка на файл:
[moder]Тема закрыта. Дублирование[/moder]
В файле сводная таблица, в ней посчитаны доли за 2014 и за 2015 год. Необходимо определить динамику этих долей. Как это можно сделать в сводных таблицах?
Функция дополнительные вычисления — «отличие» отлично считает по разнице в количестве, а вот приведенное отличие и непонятно как считает и значения разницы больше нуля не считает. Ссылка на файл:
[moder]Тема закрыта. Дублирование[/moder] MILASHKA
К сообщению приложен файл: —.xlsx (15.5 Kb)
Сообщение отредактировал Pelena — Пятница, 12.01.2018, 14:15
Сообщение В файле сводная таблица, в ней посчитаны доли за 2014 и за 2015 год. Необходимо определить динамику этих долей. Как это можно сделать в сводных таблицах?
Функция дополнительные вычисления — «отличие» отлично считает по разнице в количестве, а вот приведенное отличие и непонятно как считает и значения разницы больше нуля не считает. Ссылка на файл:
[moder]Тема закрыта. Дублирование[/moder] Автор — MILASHKA
Дата добавления — 12.01.2018 в 13:20