Как посчитать долю в сводной таблице
Содержание
Отобразить в процентном соотношении показатели сводной таблицы с применением процентного формата легче всего воспользовавшись макросом. Он позволит быстро и автоматически показать доли в процентах как по строкам, так и по столбцам сводной таблицы.
Как посчитать процент в сводной таблице макросом
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 добавит в сводную таблицу поле значения с уникальным идентификационным номером, который прибавляется к его названию. При необходимости поле можно переименовать.
Щелкните поле значения в сводной таблице правой кнопкой мыши и выберите элемент Дополнительные вычисления.
Выберите нужный параметр.
Используйте этот параметр:
Результат вывода или вычисления
Значение, введенное в поле.
Значения в процентах от общей суммы всех значений или точек данных в отчете.
% от суммы по столбцу
Все значения в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду.
% от суммы по строке
Значения в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Значения в процентах от значения выбранного базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Значения в следующем виде: (значение элемента) / (значение родительского элемента по строкам)
% от суммы по родительскому столбцу
Значения в следующем виде: (значение элемента) / (значение родительского элемента по столбцам)
% от родительской суммы
Значения в следующем виде: (значение элемента) / (значение родительского элемента в выбранном базовом поле)
Значения в виде разности по отношению к значению выбранного базового элемента в соответствующем базовом поле.
Значения в виде разницы в процентах по отношению к значению выбранного базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Значения в виде нарастающего итога для последовательных элементов в выбранном базовом поле.
% от суммы с нарастающим итогом в поле
Значения в виде нарастающего итога для последовательных элементов в выбранном базовом поле в процентах.
Сортировка от минимального к максимальному
Ранг выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно.
Сортировка от максимального к минимальному
Ранг выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг.
Значения в следующем виде: ((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца))
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Вычисления в сводной таблице подчиняются правилам по умолчанию. При формировании сводной таблицы данные в поле значений обрабатывают данные источника следующим образом:
- для числовых значений используется функция СУММ;
- для текстовых – функция СЧЁТ.
Возьмем, например, исходные данные, представленные на рис. 1. Если включить в область значений сводной таблицы поля Заказчик и Доход, то по первому полю посчитается число заказчиков, а по второму – сумма дохода (рис. 2).
Рис. 1. Исходные данные, используемые во всех примерах заметки
Скачать заметку в формате Word или pdf, примеры в формате Excel
Рис. 2. В сводной таблице для числовых полей в области значений по умолчанию находится сумма, для остальных типов данных – количество
В Excel 2013 значительно расширены возможности вычислений в полях, относящихся к области значений. [1] Чтобы увидеть все доступные опции, откройте окно Параметры поля значений (рис. 3). Для этого, например, кликните правой кнопкой мыши на любой ячейки из области значений (на рис. 2 это – область В3:С8), и выберите пункт меню Параметры полей значений… [2] В Excel 2013 на вкладке Операция доступны 11 функций вычислений (на рис. 3а обведены 6 функций, видимых на экране), на вкладке Дополнительные вычисления – еще 15 (рис. 3б). Для сравнения, в Excel 2007 таких функций было только 8.
Рис. 3. Окно Параметры поля значений: (а) вкладка Операция, (б) вкладка Дополнительные вычисления
Основные функции
Проиллюстрируем, как работают 11 основных функций. Для этого создадим сводную таблицу, в которую 11 раз в область значений перетащим поле Доход, и последовательно настроим функции вычисления (рис. 4; я создал две таблицы, поскольку слишком широкое изображение будет плохо читаемым). Для настройки функции кликните на одной из ячеек настраиваемого столбца правой кнопкой мыши, выберите пункт меню Параметры полей значений… и отметьте соответствующую функцию на вкладке Операция. Перейдите в поле Пользовательское имя (см. рис. 3), и введите имя, соответствующее функции, например, Сумма, Среднее и др. На рис 4 видно, что в поле Произведение некоторые значения так велики, что даже превышают возможности Excel отражать такие числа.
Рис. 4. Основные функции вычислений в сводной таблице
Некоторые функции требуют пояснения. Смещенное отклонение – оно же среднеквадратичное отклонение выборки. Несмещенное отклонение – стандартное отклонение в генеральной совокупности. Аналогично и два вида дисперсии. Видно, что смещенные значения немного больше несмещенных. Более подробно о сути различий этих двух оценок см., например, Выборочная дисперсия.
Дополнительные вычисления
Дополнительные вычисления покрывают самые разнообразные требования, которые могут возникнуть при анализе данных. В зависимости от выбранной функции (область 1 на рис. 5) дополнительная настройка может не потребоваться (рис. 5а), или может потребоваться выбрать только базовое поле (рис. 5б) или, и базовое поле, и базовый элемент (рис. 5в).
Рис. 5. В зависимости от выбранной функции (область 1): (а) дополнительная настройка не требуется, (б) требуется выбор базового поля, (в) требуется выбор базового поля и базового элемента
Помните, что дополнительные вычисления накладываются на основные. Например, если в качестве основной функции выбрана Сумма (столбцы Cи Dна рис. 6), то дополнительное вычисление % от общей суммы покажет долю каждого элемента (в столбце D) от итоговой суммы 6 707 812. Если же качестве основной функции выбран Максимум, то дополнительное вычисление % от общей суммы покажет долю каждого элемента (в столбце F) от максимума 25 350.
Рис. 6. Совместное влияние на вычисления основной и дополнительной функций
Работа дополнительных функций «% от общей суммы», «% от суммы по столбцу» и «% от суммы по строке» показана на рис. 7. Эти функции не требуют дополнительных настроек.
Рис. 7. Дополнительные функции: (а) % от общей суммы, (б) % от суммы по столбцу, (в) % от суммы по строке
Несколько более сложной для понимания является дополнительная функция «доля». Чтобы создать сводную таблицу, изображенную на рис. 8:
- Дважды перетащите в область значений поле Доход
- Отсортируйте строки по полю Доход по убыванию
- Установите параметры поля значений для столбца С, как показано на рисунке.
Рис. 8. Дополнительная функция Доля
Видно, что значения в столбце Доля показывают процент от продаж в Нью-Йорке.
Начиная с версии 2010 в Excel появились дополнительные функции Сортировки. На рис. 9а показана сортировка от минимального значения (которому присваивается ранг 1) до максимального. На рис. 9б добавлено поле Регион, а сортировка осуществляется от максимального значения (ранг 1) к минимальному. Видно, что сортировка выполняется в каждом регионе отдельно.
Рис. 9. Дополнительная функция Сортировка: (а) сортировка от минимального к максимальному; (б) сортировка от максимальному к минимальному
Вычисление суммы с нарастающим итогом обычно выполняется для таблиц, в которых месяцы расположены по строкам (рис. 10а), либо, когда нужно показать, что первые Nклиентов дают N% дохода/прибыли (рис. 10б). Видно, что первые 9 клиентов обеспечивают 80% продаж.
Рис. 10. Дополнительная функция: (а) С нарастающим итогом в поле; (б) % от суммы с нарастающим итогом в поле
Дополнительная функция Приведенное отличие похожа на функцию Доля (см. рис. 8). Например, на рис. 9 в ячейке С4 функция Приведенное отличие показывает на сколько процентов доход в Детройте меньше, чем доход в Нью-Йорке.
Рис. 11. Дополнительная функция Приведенное отличие
Дополнительная функция «% от суммы по родительской строке» показывает долю текущего элемента в промежуточных итогах. Например, в ячейке Е13 (рис. 12) значение 2,75% показывает долю дохода в Чикаго ($ 184 425) от общего по стране ($6 707 812). В ячейке D9 значение 78,84% показывает долю Детройта ($1 372 957) от общего по региону Средний Запад ($1 741 424).
Рис. 12. Дополнительная функция % от суммы по родительской строке
Самой загадочной является дополнительная функция Индекс (рис. 13). Обратите внимание, индекс персиков в Джорджии 2,55, а в Калифорнии – 0,5. Если в следующем году урожай персиков пострадает, это нанесет сильный удар по фермерам Джорджии и лишь слегка затронет фермеров Калифорнии.
Рис. 13. Дополнительная функция Индекс
Чтобы понять, что в Excel подразумевается под индексом, проведите следующие вычисления. Сначала разделите продажи персиков в Джорджии (180) на продажи фруктов в Джорджии (210). Получите относительную стоимость персиков в Джорджии = 0,86. Далее разделите общие продажи персиков (285) на общие продажи всех фруктов (847). Получите Относительную долю продаж персиков = 0,34. Индекс равен отношению первого частного (0,86) ко второму (0,34). Индекс = 2,55.
Например, в Огайо индекс яблок = 4,91, поэтому производство яблок имеет первостепенную важность для этого штата.
[2] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 3.
Загрузка...