Как рассчитать долю в сводной таблице в Excel

Обновлено: 21.11.2024

У вас возникают проблемы с вычислением процентного соотношения в сводной таблице? Сводные таблицы в Excel — отличный инструмент для анализа данных. Они помогают вам агрегировать, обобщать, находить ценную информацию и представлять большой объем данных всего за несколько кликов, включая расчет процента от заданных данных.

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

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

В этом примере у вас есть данные о продажах напитков одиннадцати позиций за 3-й квартал года.

Создайте и отформатируйте сводную таблицу

Чтобы создать сводную таблицу и применить условное форматирование, необходимо выполнить следующие шаги:

Нажмите в любом месте данных.
Выберите Вставка > Рекомендуемые сводные таблицы. Прокрутите вниз и выберите вариант с надписью Сумма продаж по товарам и месяцам.

У вас будет сводная таблица с продажами товаров за каждый месяц.

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

Нажмите в построителе сводных данных запись "Сумма продаж" и выберите "Настройки поля значений".
В окне "Параметры поля значений" на вкладке "Показать значения как" выберите "% от суммы столбца".

Это покажет продажи каждого товара в процентах от общего объема продаж за месяц.

Создать вычисляемое поле в сводной таблице

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

Отсюда у нас есть сводная таблица суммы продаж и прибыли для товаров.

Чтобы добавить размер прибыли для каждого элемента:

Нажмите на любую ячейку в сводной таблице.
Выберите (Инструменты сводной таблицы) Анализ > Поля, элементы и наборы > Вычисляемое поле.

Назначьте имя в поле «Имя».
В поле "Формула" вставьте формулу =Прибыль/Продажи, нажав кнопку "Вставить поле" в поле "Поля".

Ограничение вычисляемых полей в сводной таблице при вычислении процента

Вычисляемые поля в сводной таблице имеют некоторые ограничения. Вычисляемое поле всегда выполняется против СУММЫ данных. Это ограничивает вас от использования большого количества функций. Кроме того, вы не можете использовать в качестве аргумента функции, требующие ссылки на ячейку или определенные имена. Это исключает такие функции, как СЧЕТ, СРЗНАЧ, ЕСЛИ, И, НЕ и ИЛИ.

В следующем примере вы будете использовать записи о мероприятиях клуба легкой атлетики средней школы Клейтона за последние шесть месяцев. Он включает в себя записи Event, Names и Win. Вы рассчитаете количество побед в процентах от количества спортсменов на основе событий.

Отсюда у нас есть сводная таблица Count of Name и Count of Win.

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

Чтобы решить эту проблему, выполните следующие действия:

Убедитесь, что Power Pivot включен в меню «Файл» > «Параметры» > «Надстройки».
Нажмите "Надстройки COM" в поле "Управление" > "Перейти".
Установите флажок для Microsoft Office Power Pivot > нажмите "ОК". Выберите надстройку Power Pivot для Excel, если у вас установлены другие версии Power Pivot.

На листе Events_Record выберите Power Pivot > Управление.

Вы будете использовать выражение анализа данных (DAX) для создания вычисляемых полей в Power Pivot. DAX используется для добавления вычислений. Мера — это формула, созданная специально для использования в сводной таблице, которая использует данные в Power Pivot. Вы будете использовать меру в области значений сводной таблицы.

Это покажет количество побед в процентах от количества спортсменов на основе событий.

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

Есть много мощных функций сводных таблиц, Power Pivot и выражения анализа данных (DAX), которые могут помочь вам получить представление о ваших данных. Если вы хотите сэкономить часы исследований и разочарований и быстро найти решение, попробуйте наш сервис Excel Live Chat! Наши специалисты по Excel доступны 24 часа в сутки, 7 дней в неделю, чтобы ответить на любой ваш вопрос об Excel прямо на месте. Первый вопрос бесплатный.

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

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

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

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

Посмотрите, как рассчитать процент в сводной таблице на YouTube, и поставьте лайк!

Следуйте пошаговому руководству о том, как добавить проценты в сводную таблицу, и загрузите эту книгу Excel, чтобы попрактиковаться:

ШАГ 1. Вставьте новую сводную таблицу, нажав на свои данные и выбрав Вставка > Сводная таблица > Новый рабочий лист или Существующий рабочий лист

ШАГ 2. В разделе СТРОКИ укажите поле Продавец, в СТОЛБЦАХ укажите поле Финансовый год, а в области ЗНАЧЕНИЯ введите поле Продажи дважды, я объясню ниже почему:

Вот как выглядит процент сводной таблицы:

ШАГ 3. Нажмите раскрывающееся меню (Сумма ПРОДАЖ2) второго поля "Продажи" и выберите "Настройки поля значений"

ШАГ 4. Выберите вкладку «Показать значения как» и в раскрывающемся списке выберите % от.

Для Базового поля выберите Финансовый год.

Для выбора базового элемента (предыдущий).

Это означает, что мы хотим получить процент значений на основе предыдущего финансового года.

Кроме того, измените пользовательское имя на Процент от предыдущего года, чтобы сделать его более презентабельным. Нажмите "ОК".

ШАГ 5. Обратите внимание, что данные Процент от предыдущего года представлены в десятичном формате, который трудно прочитать:

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

Цель состоит в том, чтобы преобразовать числа из десятичного формата (например, 0,23) в процентный формат, более читаемый (например, 23%).

ШАГ 6. Нажмите кнопку "Числовой формат".

ШАГ 7. В диалоговом окне Формат ячейки внесите изменения в форматирование и дважды нажмите OK.

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

Следуя этим шагам, вы можете добавить процент в сводную таблицу, показывающую процент от предыдущего года для данных о продажах за 2012, 2013 и 2014 годы.

Все данные о продажах теперь представлены в процентах от предыдущего года (2013 год представлен в процентах от 2012 года, а 2014 год представлен в процентах от 2013 года):

% продаж продавца

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

ШАГ 1. Щелкните любую ячейку в таблице данных.

ШАГ 2. Выберите «Вставка» > «Сводная таблица».

ШАГ 3. В диалоговом окне "Создание сводной таблицы" выберите диапазон таблиц и "Новый рабочий лист", а затем нажмите "ОК".

ШАГ 4. В диалоговом окне полей сводной таблицы перетащите продавца в область строк, год в область столбцов и сумму продаж в область значений.

На вашем листе появится сводная таблица!

ШАГ 5. Щелкните правой кнопкой мыши любое значение в сводной таблице и выберите «Настройка поля значения».

ШАГ 6. В диалоговом окне «Настройка поля значения» в разделе «Показать значения как вкладку» выберите «%» > «ПРОДАВЕЦ» > «Гомер Симпсон».

Это добавит процент в сводную таблицу!

Excel отобразит 100 % для всех значений для Гомера, и оно будет сравниваться с самим собой. Для других продавцов Excel покажет сумму продаж в процентах от продаж Гомера за этот конкретный год.

На этом наше руководство о том, как отображать проценты в сводной таблице, завершено!

ПОЛЕЗНЫЙ РЕСУРС:

Вы можете узнать больше о том, как использовать Excel, просмотрев наш БЕСПЛАТНЫЙ веб-семинар по Excel, посвященный формулам, сводным таблицам, Power Query, макросам и VBA!

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

Чтобы использовать наш пример, загрузите сводную таблицу Excel в процентах от total.xlsx

Сводная таблица Excel — процент от общего числа

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

Чтобы отобразить процент от общего числа в сводной таблице Excel, создайте сводную таблицу с информацией, которую вы хотите обобщить, а затем выполните следующие действия. Эта функция была представлена в Excel 2010, поэтому применима только к 2010 и более поздним версиям. Изображения были сделаны с помощью Excel 2013 в Windows 7.

Щелкните в любом месте сводной таблицы и откройте панель "Поля сводной таблицы". В области «Значения» выберите «Настройки поля значения» в раскрывающемся меню поля.

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

Сводная таблица в процентах от общей суммы

После того как вы выберете % от общего итога в раскрывающемся меню и нажмете OK, значения вашей сводной таблицы отобразятся в процентах.

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

Сводная таблица в процентах от промежуточных итогов

Если мы хотим видеть проценты промежуточных итогов — например, насколько хорошо каждый продавец вносит свой вклад в свой регион, а не общий итог, мы будем использовать параметр % от итоговых значений родительской строки.

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

С помощью этого анализа вы можете быстро увидеть, что Северо-Восточный регион является наиболее эффективным, так как он обеспечивает наибольший процент прибыли. Мы также видим, что Лехоски является лучшим продавцом в целом и в Западном регионе, но занимает только 3-е место в Южно-Центральном регионе.

Пользовательская сводная таблица в процентах

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

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

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

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

Это руководство охватывает:

Что такое вычисляемое поле сводной таблицы?

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

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

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

А теперь, что если вы также хотите узнать, какова была норма прибыли этих розничных продавцов (где норма прибыли — это "Прибыль", деленная на "Продажи").

Есть несколько способов сделать это:

Вернитесь к исходному набору данных и добавьте эту новую точку данных. Таким образом, вы можете вставить новую колонку в исходные данные и рассчитать в ней размер прибыли. Как только вы это сделаете, вам нужно обновить исходные данные сводной таблицы, чтобы получить этот новый столбец как ее часть.
- Хотя этот метод возможен, вам нужно будет вручную вернуться к набору данных и выполнить вычисления. Например, может потребоваться добавить еще один столбец для расчета среднего объема продаж на единицу (Продажи/Количество). Вам снова придется добавить этот столбец в исходные данные, а затем обновить сводную таблицу.
- Этот метод также увеличивает размер сводной таблицы по мере добавления в нее новых данных.
Добавьте расчеты вне сводной таблицы. Это может быть вариантом, если структура вашей сводной таблицы вряд ли изменится. Но если вы измените сводную таблицу, расчет может не обновиться соответствующим образом и может дать вам неправильные результаты или ошибки. Как показано ниже, я рассчитал маржу прибыли, когда в ряду были розничные продавцы. Но когда я изменил его с клиентов на регионы, формула выдала ошибку.
Использование вычисляемого поля сводной таблицы . Это самый эффективный способ использовать существующие данные сводной таблицы и рассчитать желаемую метрику. Рассматривайте вычисляемое поле как виртуальный столбец, который вы добавили, используя существующие столбцы из сводной таблицы. Есть много преимуществ использования вычисляемого поля сводной таблицы (как мы увидим через минуту):
- От вас не требуется обрабатывать формулы или обновлять исходные данные.
- Его можно масштабировать, так как он автоматически учитывает любые новые данные, которые вы можете добавить в свою сводную таблицу. После добавления поля расчета его можно использовать как любое другое поле в сводной таблице.
- Легко обновлять и управлять. Например, если меняются показатели или вам нужно изменить расчет, вы можете легко сделать это из самой сводной таблицы.

Добавление вычисляемого поля в сводную таблицу

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

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

Вот шаги, чтобы добавить вычисляемое поле сводной таблицы:

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

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

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

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

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

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

Вы НЕ МОЖЕТЕ использовать ссылки или именованные диапазоны при создании вычисляемого поля сводной таблицы. Это исключило бы множество формул, таких как ВПР, ИНДЕКС, СМЕЩ и т. д. Однако вы можете использовать формулы, которые могут работать без ссылок (например, СУММ, ЕСЛИ, СЧЕТ и т. д.).
В формуле можно использовать константу. Например, если вы хотите узнать прогнозируемый объем продаж, при котором прогнозируется рост на 10 %, вы можете использовать формулу =Продажи*1,1 (где 1,1 — постоянное значение).
Порядок приоритета соблюдается в формуле, которая создает вычисляемое поле. Рекомендуется использовать круглые скобки, чтобы не помнить порядок приоритета.

Теперь давайте рассмотрим пример использования расширенной формулы для создания вычисляемого поля.

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

Для прогнозируемого значения необходимо использовать увеличение продаж на 5 % для крупных розничных продавцов (объем продаж более 3 миллионов) и увеличение продаж на 10 % для малых и средних розничных продавцов (объем продаж менее 3 млн).

Примечание. Приведенные здесь цифры продаж являются поддельными и использовались для иллюстрации примеров в этом руководстве.

Вот как это сделать:

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

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

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

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

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

Обратите внимание, что промежуточный и общий итоги неверны.

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

Таким образом, для South Total значение должно быть 22 824 000, но South Total ошибочно указывает его как 22 287 000. Это происходит потому, что для получения значения используется формула 21 225 800*1,05.

К сожалению, вы никак не можете это исправить.

Лучший способ решить эту проблему — удалить промежуточные и общие итоги из сводной таблицы.

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

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

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

Как получить список всех формул вычисляемого поля?

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

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

Вот шаги, чтобы быстро получить список формул всех вычисляемых полей:

Как только вы нажмете «Список формул», Excel автоматически вставит новый рабочий лист, содержащий сведения обо всех вычисляемых полях/элементах, которые вы использовали в сводной таблице.

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

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

Читайте также: