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

К первому сообщению темы должен быть прикреплен файл примера в формате xls*.
Приложив пример, Вы избавите себя и других от вопросов типа «А какой критерий?», «А куда выводить результат?», «А сколько строк?» и все тех же просьб выложить файл. Рисовать за Вас Ваши же таблички с заданиями, а затем и решение к ним, никто желанием не горит. Да и, как показывает практика, в большинстве случаев без файла решения не найти.

Главное меню

Профессиональные приемы работы в Microsoft Excel
► Обмен опытом
► Microsoft Excel
► Сводная таблица и расчет разницы между столбцами в %

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

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

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

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

Примечание: В Excel для Mac меню «Показать значения как» не отображаются все те же параметры, что и в Excel для Windows, но они доступны. Выберите пункт «Дополнительные параметры» в меню, если вы не видите нужный вариант в списке.
Выберите параметр вычисления, который вы хотите использовать. Доступны следующие варианты вычисления:

Значение, введенное в данное поле.

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

% от суммы по столбцу

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

% от суммы по строке

Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории.

Значения в процентах от значения базового элемента в соответствующем базовом поле.

% от суммы по родительской строке

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по строкам).

% от суммы по родительскому столбцу

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента по столбцам).

% от родительской суммы

Рассчитывает значения следующим образом:

(значение элемента) / (значение родительского элемента в выбранном базовом поле).

Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле.

Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле.

С нарастающим итогом в поле

Значение в виде нарастающего итога для последовательных элементов в базовом поле.

% от суммы с нарастающим итогом в поле

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

Сортировка от минимального к максимальному

Отображает ранг выбранных значений в определенном поле, перечисляя наименьший элемент в поле как 1 и каждое большее значение с более высоким значением ранга.

Сортировка от максимального к минимальному

Отображает ранг выбранных значений в определенном поле, перечисляя наибольший элемент в поле как 1 и каждое меньшее значение с более высоким значением ранга.

Рассчитывает значения следующим образом:

((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)).

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

Повторяющиеся значения в области значений

В приведенном выше примере три поля значений были добавлены во второй раз в область значений. к их именам добавлено слово «_2». Ниже поле «Сумма 1/1/14» отображается дважды в отчете сводной таблицы, поэтому вы можете увидеть значение для каждого города и его родительского (восточная или западная область). Вы также увидите процент общего итога для каждого региона (в строках 6 и 9).

То же поле со значениями и процентными величинами

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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