Отображение различных вычислений в полях значений сводной таблицы
Вместо написания собственных формул в вычисляемых полях можно использовать команду Показать значения как , чтобы быстро представить значения различными способами. Он также предоставляет несколько новых параметров вычисления, таких как % от родительского итога или процент запущенного общего числа.
Совет: Эту функцию можно использовать для выполнения различных вычислений в поле значения. Тем не менее, так как одни и те же поля значений можно добавить в сводную таблицу несколько раз, эту функцию можно также использовать для параллельного отображения фактического значения и других вычислений, таких как выполняемый общий расчет.
- Чтобы добавить два или более одинаковых полей значений в сводную таблицу, чтобы вы могли отображать различные вычисления в дополнение к фактическому значению определенного поля, выполните следующие действия.
- В списке полей перетащите поле значения, которое нужно добавить, в область Значения , которая уже содержит это поле, и поместите его прямо под этим полем.
Примечание: Поле значения добавляется в сводную таблицу, а номер версии добавляется к ее имени поля. При необходимости имя поля можно изменить.
Примечание: В Excel для Mac меню Показать значения как не перечислены все те же параметры, что и Excel для Windows, но они доступны. Выберите Дополнительные параметры в меню, если вы не видите нужный вариант.
Значение, введенное в данное поле.
Отображает значения в процентах от общего числа всех значений или точек данных в отчете.
% от суммы по столбцу
Отображает все значения в каждом столбце или ряде в процентах от общего числа для столбца или ряда.
% от суммы по строке
Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.
Значения в процентах от значения базового элемента в соответствующем базовом поле.
% от суммы по родительской строке
Рассчитывает значения следующим образом:
(значение элемента) / (значение родительского элемента по строкам).
% от суммы по родительскому столбцу
Рассчитывает значения следующим образом:
(значение элемента) / (значение родительского элемента по столбцам).
% от родительской суммы
Рассчитывает значения следующим образом:
(значение элемента) / (значение родительского элемента в выбранном базовом поле).
Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.
Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.
С нарастающим итогом в поле
Значение в виде нарастающего итога для последовательных элементов в базовом поле.
% от суммы с нарастающим итогом в поле
Вычисляет значение в процентах для последовательных элементов в базовом поле , которые отображаются в виде выполняющегося итога.
Сортировка от минимального к максимальному
Отображает ранг выбранных значений в определенном поле, перечисляя наименьший элемент в поле как 1 и каждое большее значение с более высоким значением ранжирования.
Сортировка от максимального к минимальному
Отображает ранг выбранных значений в определенном поле с наибольшим элементом поля как 1 и каждое меньшее значение с более высоким значением ранжирования.
Рассчитывает значения следующим образом:
((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).
Чтобы отобразить вычисления рядом со значениями, на которые они основаны (например, чтобы отобразить процент от общего итога рядом с промежуточным итогом), сначала необходимо дублировать поле значения, добавив его несколько раз. В списке полей перетащите поле, которое нужно дублировать, в область Значения и поместите его прямо под тем же полем.
В приведенном выше примере три поля значения были добавлены во второй раз в область Значений; к их именам добавляется «_2». Ниже поле «Сумма 1/1/14» отображается дважды в отчете сводной таблицы, поэтому вы можете увидеть значение для каждого города и их родительского региона (регион «Восток» или «Запад»). Вы также увидите процентную долю общего итога для каждого региона (в строках 6 и 9).
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как в сводной таблице посчитать процент между столбцами
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ruИспользование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071Как отследить динамику роста в Microsoft Excel
Сравнение показателей в таблице за определенный период – одна из популярных задач, которая стоит перед пользователями Microsoft Excel. Чаще всего это осуществляется путем вывода вспомогательного столбца, где значения указаны в процентах, и сразу становится понятно, насколько сильной была позитивная или негативная динамика.
В этой статье я на простом примере предлагаю разобраться с соответствующим оформлением таблицы.
Предварительные действия
Сначала понадобится подготовить таблицу, чтобы кроме ввода функций далее от вас ничего не требовалось. Выберите два общих значения, которые будете сравнивать. Если это должна быть, например, месячная выручка, сначала сделайте сводную таблицу по дням и ссылайтесь на нее при создании общей суммы. Выведите два значения, динамику между которыми нужно сравнивать, чтобы перейти далее. На изображении ниже показан самый простой пример подходящей таблицы.
Создайте вспомогательный столбец для вывода процентов. Введите для него совершенно любое удобное для вас название и задайте визуальное оформление в соответствии со стилем основной таблицы.
Сейчас стоит учитывать, что тип ячейки, в которой будут отображаться проценты, является общим. При расчетах в таком случае появится очень большое значение в виде обычного числа. Поэтому понадобится изменить формат ячеек, чтобы проценты выводились корректно.
- Для этого выделите необходимые ячейки и щелкните по любой из них правой кнопкой мыши. Из контекстного меню выберите пункт «Формат ячеек».
- Отобразится отдельное окно, в котором будет выбрана вкладка «Число». На ней выберите «Процентный» и определите количество знаков после запятой или оставьте его по умолчанию. После этого сохраните новый формат ячейки.
На этом подготовительные действия завершены, поэтому можно приступить к заполнению формул и вычислении положительной или негативной динамики.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостейСоздание формулы отслеживания динамики роста
В следующей инструкции я постараюсь наглядно описать каждое действие при написании функции, чтобы вы понимали, какие значения берутся, как они сравниваются и что получается в итоге. Если вы сделали таблицу такого же формата, как и в примере, достаточно будет выполнить точно такие же действия, чтобы отследить динамику роста в процентах.
- Выделите ячейку, где должны выводиться проценты и напишите первую часть формулы =(C2 — B2). Это поможет определить разницу между суммами за два разных месяца. Соответственно, если номера ячеек в вашей таблице другие, замените их, отняв сумму второго значения от первого.
- После скобок добавьте /B2, чтобы разделить это все на сумму первого значения, то есть на 100%, если говорить о стандартных математических операциях с процентами.
- Нажмите Enter и посмотрите на результат. В моем случае сумма за первый месяц была 102 000, за второй — 124 000, а разница – 22 000. Получается, динамика роста является положительной и равняется 21,62%. У вас значение будет другим, в соответствии с суммами в таблице.
- Если далее в таблице должно происходить такое же сравнение значений и вывод процентов, просто зажмите левой кнопкой мыши правый угол текущей ячейки и растяните ее вниз.
- В итоге таблица сама заполнится, и на экране появится список всех процентов, характеризующих положительную или отрицательную динамику роста.
Далее ничего вас не ограничивает, вы можете сравнивать показатели бесконечное количество раз. Отнимать и добавлять проценты, чтобы понять общую сумму изменений и заниматься дальнейшим взаимодействием с электронной таблицей для своих целей.
Формула отслеживания динамики роста по норме
Иногда расчеты подразумевают наличие нормы, то есть средневзвешенной суммы, которая является оптимальной. Вы можете использовать только это значение, чтобы сравнить его с другими показателями и вычислить проценты. Давайте наглядно разберемся с изменениями, которые вносятся в этом типе расчетов.
- Найдите ту ячейку, где будет указана норма. Если ее еще нет, создайте и введите необходимое число. Формула будет немного отличаться от предыдущей. Вам нужно в качестве первого значения как раз и задать эту ячейку, полностью закрепив ее при помощи знаков $.
- Больше никаких изменений вводить не нужно, достаточно нажать Enter, чтобы ознакомиться с результатом.
- При растягивании формула тоже будет считаться корректно, поскольку ранее вы полностью закрепили одну ячейку.
В этом случае формула обретает вид =(C2-$B$9)/$B$9, где $B$9 – номер той самой закрепленной ячейки, а C2 – сумма второго месяца, от которой и отнимается норма для вычисления процентов.
На основе созданной таблицы вы можете добавлять наглядный график, визуально демонстрирующий динамику роста. Детальнее о его создании рассказано в другой моей статье по ссылке ниже.
Макрос позволяет рассчитать процент в сводной таблице 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
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры