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

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

pivot_calc1.gif

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

Другие функции расчета вместо банальной суммы

Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений (Value Field Settings), то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:

pivot_calc2.gif

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

pivot_calc3.gif

По умолчанию, для числовых данных Excel всегда автоматически выбирает суммирование (Sum), а для нечисловых (даже если из тысячи ячеек с числами попадется хотя бы одна пустая или с текстом или с числом в текстовом формате) – функцию подсчета количества значений (Count).

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

pivot_calc4.gif

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

pivot_calc5.gif

Долевые проценты

Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то станет доступен выпадающий список Дополнительные вычисления (Show data as):

pivot_calc6.gif

В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row), Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total), чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:

pivot_calc7.gif

Динамика продаж

Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference), а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий):

pivot_calc8.gif

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

pivot_calc10.gif

А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом - то получим то же самое, но не в рублях, а в процентах:

pivot_calc9.gif

P.S.

В Microsoft Excel 2010 все вышеперечисленные настройки вычислений можно проделать еще проще - щелкнув правой кнопкой мыши по любому полю и выбрав в контекстном меню команды Итоги по (Summarize Values By):

pivot_calc11.png

... и Дополнительные вычисления (Show Data as):

pivot_calc12.png

Также в версии Excel 2010 к этому набору добавились несколько новых функций:

  • % от суммы по родительской строке (столбцу) - позволяет посчитать долю относительно промежуточного итога по строке или столбцу:
    2010_pivot_2.gif

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

  • % от суммы нарастающим итогом - работает аналогично функции суммирования нарастающим итогом, но отображает результат в виде доли, т.е. в процентах. Удобно считать, например, процент выполнения плана или исполнения бюджета:
    2010_pivot_3.gif

     
  • Сортировка от минимального к максимальному и наоборот - немного странное название для функции ранжирования (РАНГ), вычисляющей порядковый номер (позицию) элемента в общем списке значений. Например, с ее помощью удобно ранжировать менеджеров по их суммарной выручке, определяя кто на каком месте в общем зачете:
    2010_pivot_4.gif

Ссылки по теме

 


Страницы: 1  2  
Galina
22.11.2012 23:29:49
Огромное спасибо за статью! После 2х лет проб и ошибок, протряски нашего офисного Excel спеца пришла к выводу что показ % различий в сводной таблице не возможен! Оказывается возможен да еще и со столькими вариациями!!!
Марина
22.11.2012 23:31:51
Подскажите, пожалуйста, как в сводной таблице настроить процент от промежуточного итога (не от основного в конце), те чтобы промежуточный итог был 100% и данные по группе в % считались от промежуточного 100%
22.11.2012 23:34:29
Такая функция есть только в Excel 2010. Правой кнопкой мыши по полю - Дополнительные вычисления - % от родительской строки.
14.04.2018 18:38:12
Николай, не могу изменить дополнительные вычисления для нескольких полей.
пример: последние 2 столбца.  Выделяю группу ячеек и меняется вычисления только в одном поле.
Катя
22.11.2012 23:32:56
Все это чудо интересно, но просто. А вот если бы Вам пришлось посчитать в сводной таблице что-то сложное, но достаточно простое в обычном листе Excel? К примеру, Вам надо сослаться на результат по прошлому периоду, умножив его на разницу между продажами за февраль-январь. Какую бы Вы функцию применили? Собсбвенно, анализ данных из сводных таблиц и не дает покоя уже несколько дней.
22.11.2012 23:36:44
Копайте в направлении вставки Вычисляемых полей (Calculated Field) и Вычисляемых объектов (Calculated Items). Либо использовать сводную как промежуточный этап и вытаскивать из нее данные с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и считать дальше самостоятельно.
08.10.2014 09:12:55
Доброго времени суток.

Николай, спасибо за доступное объяснение, вопрос по вычисляемым полям в сводной таблице, создаю поле в котором округляю вверх значения другого поля, как сделать так чтобы в строке итоги суммировались уже округленные значения, а не округлялось вверх итоговое значение исходного столбца?
Леночка
22.11.2012 23:33:37
супер статья!!! очень помогла!!!! пасибки!!!
23.12.2012 02:16:53
Ни как не могу понять, что вычисляет "Индекс"?
17.02.2013 09:12:20
Индекс вычисляет, по словам Microsoft, "относительную важность" элемента в таблице. Вычисляется по формуле:
=(Значение*Общая сумма) / (Сумма по столбцу*Сумма по строке)
Имхо, неочевидная и бесполезная штука - не применял в реальных проектах ни разу.
15.02.2013 12:17:06
Николай, спасибо Вам за такой замечательный сайт!!!
Помогите разобраться в следующем: имеем сводную таблицу, состоящую из 5-ти столбцов: 1 –номенклатура; 2 –сумма по полю План; 3 – сумма по полю Факт; 4 - % выполнения плана; 5 – прогноз выполнения (два последних – это вычисляемые поля). Поле «прогноз выполнения» вычисляется по формуле: Сумма по полю Факт/9*20/Сумма по полю План, - где цифра 9 – это количество отработанных дней в месяце, 20 – количество рабочих дней в месяце. Так как таблица обновляется каждый день, то и формулу соответственно приходится корректировать в ручную на кол-во этих отработанных дней. К сожалению в формуле вычисляемого поля невозможно использовать функции или ссылки на ячейки. Есть ли другой способ, что б в этой формуле само изменялось кол-во отработанных дней в месяце?
17.02.2013 09:15:14
Как вариант, можно добавить в исходную таблицу служебный столбец со значением количества отработанных дней и использовать его в вычисляемом поле.
17.02.2013 14:15:30
Такой вариант уже пробовала, но результат у меня вышел не правильный, тогда я подумала, что идея себя не оправдала, но скорее всего, я ее не довела до конца. Спасибо, буду пробовать по Вашему совету!
18.02.2013 12:30:47
Анна, если своими силами не получится - напишите мне на info@planetaexcel.ru или создайте тему с вопросом на форуме + приложите ваш файл-пример. Чем сможем - поможем!
19.02.2013 13:19:02
19.02.2013 09:04:56
Николай, подскажите, можно ли вычислить в сводной таблице с помощью вычисляемых полей следующее: по строкам - ФИО, по столбцам - дата, в значении - сумма, необходимо вычислить дату первой операции и дату последней операции по каждому ФИО. Я это делал рядом со сводно таблицей с помощью формул =МИН(ЕСЛИ($B6927:$AE6927>0;$B$4:$AE$4;999999)) и =МАКС(ЕСЛИ($B6927:$AE6927>0;$B$4:$AE$4;)) соответственно. Можно ли тоже самое сделать внутри сводной таблицы?
20.02.2013 20:11:30
Закиньте дату не в область столбцов, а в область данных два раза (получится оригинал и копия поля), а потом через правую кнопку мыши по полю - Параметры поля установите для оригинала функцию минимум, а для копии - максимум. Получите самую раннюю и самую позднюю даты по каждой ФИО.
21.02.2013 03:11:44
все гениальное просто!!! сам не догадался... еще не все возможности сводных раскрыл... а как сделать, чтобы минимум и максимум по дате отображался только в Итогах, а в остновном поле нет?
21.02.2013 22:48:14
А вот это никак, к сожалению. Промежуточные итоги еще можно настроить (правой кнопкой по ним - Параметры поля), а вот общий итог всегда имеет ту же методику расчета, что данные в области значений.
22.02.2013 04:29:42
думаю для разработчиков Сводные таблицы хорошее направление деятельности для усовершенствования... ведь если здесь реализовать возможность делать расчеты с помощью сложных формул, то можно творить "такие вещи"...
хотя может быть и ошибаюсь и знаю не все возможности сводных...
применительно к моему примеру мне на основе данных сводной надо было вычислить: общую сумму операций, количество операций, среднее значение одной операции, дату первой и дату последне операции, количество дней между первой и последней операцией... все это (кроме последнего параметра) можно вычислить с помощью сводной за исключением одного минуса - сводная по столбцам увеличивается до неимоверного размера, т.к. при перетаскивании в поле значение нужных данных увеличивается количество столбцов... и количество дней между первой и последней операцией в сводной не вычислить...
28.02.2013 09:57:54
Тут еще большой вопрос - какого вида у вас таблица с исходными данными. Если столбец с датой один, то все проще.
06.04.2013 17:21:03
Мне кажется, что Вам для подобного рода вычислений есть смысл посмотреть на бесплатную надстройку для Excel 2010 от Microsoft которая называется PowerPivot. Подобного рода вычисления делаются там довольно безболезненно (если не считать некоторого времени на освоение нового).
26.02.2013 14:05:16
Подскажите, пожалуйста, как сделать в сводной таблице (2007) такое вычисление.
Есть регионы, в них офисы и в каждом офисе - встречи. Сводную по количеству встреч в каждом офисе и каждом регионе - сделать я могу. Но это не показательно, т.к. в большом регионе (много офисов) и встреч много. Мне нужно посмотреть среднее количество встреч в офисах по регионам (т.е в каждом регионе - количество встреч в этом регионе, деленное на количество офисов в этом регионе).
Спасибо
28.02.2013 09:55:10
Строите сводную, где в области значений будет кол-во офисов и кол-во встреч, а в области строк - регионы. Потом идем на вкладку Параметры - кнопка Поля, Элементы, Наборы (или Формулы) - Вычисляемый объект. Придумываете ему имя и вводите формулу вида =Встречи/Офисы
06.03.2013 02:04:52
В сводной таблице, созданной с помощью PowerPivot в Excel 2010 погашен и не нажимается Вычисляемый объект на вкладке Параметры - кнопка Поля, Элементы, Наборы. А мне необходимо создать вычисляемое поле в данной сводной таблице с процентом выполнения плана (факт/план). Подскажите, пожалуйста, с чем это может быть связано и как мне можно осуществить данный расчет? Заранее благодарна.
09.03.2013 08:12:27
Это лучше на форум - тут вопросы по теме статьи, а не про Power Pivot.
18.03.2013 17:30:52
Подскажите пожалуйста как сделать в сводной таблице вычисление ДЗ. Есть "Контрагенты", есть "наименование продукции" в поле Строка, в поле Столбец "Статус". В поле Данные "Кол-во" и сумма отгрузки и оплаты. Нужно вычислять разницу между суммой отгрузки и суммой поступлений. Никак не могу придумать как это сделать в самой сводной таблице, а не рядом.
11.04.2013 07:50:42
Ставите активную ячейку в область строк, открываете вкладку Параметры - Поля,элементы, наборы - Вычисляемый объект. Вводите имя и формулу вида =Отгрузка-Поступления (с вашими названиями полей, естественно). Жмете ОК.
Если не получится - поэкспериментируйте там же с вычисляемыми полями. Не видя вашей таблицы, точно сказать тяжело.
11.04.2013 09:18:13
Спасибо! Все получилось:)
День добрый. Подскажите, пожалуйста, а как в одной таблице вывести и абсолютные значения и долю от строки.
11.04.2013 07:47:42
Татьяна, кидаете в область значений сводной таблицы ваше поле ДВА РАЗА. Потом правой кнопкой мыши отдельно настраиваете каждое - одно на сумму, второе - на долю (3-я картинка в статье и текст над ней).
25.04.2013 12:00:01
Здравствуйте! Подскажите пожалуйста, как рассчитать среднее значение последних 10 значений в столбце, если информация обновляется каждый день. Заранее спасибо.
09.05.2013 15:18:57
Я бы добавил в исходную таблицу дополнительный вспомогательный столбец, где формулой бы (или руками) напротив 10 последних значений ставил 1, иначе 0. И потом в сводной закинул бы этот столбец в фильтр и вывел среднее по получившимся значениям. Точнее сказать не могу, не видя вашей таблицы.
27.06.2013 11:43:03
Добрый день!
Благодарю Вас за проделанную вами работу!
Создал в сводной таблице "Вычисляемое поле", можно ли применить именно к этому полю фильтр (допустим по названию получателя)?
В идеале хотелось бы получить что-то в виде
Вычисляемое поле = (Вычисл. поле 1 [фильтр 1] +  Вычисл. поле 2 [фильтр 2] + ... +  Вычисл. поле n [фильтр n] ) Возможен ли такой вариант?? Может макросом.
И еще по фильтрам, можно ли как-то фильтровать по принципу содержит ("Моск*", "*тербур*" и др.)?
Спасибо!
01.07.2013 21:08:32
Полноценные фильтры есть только в области строк и столбцов. Причем отдельно по подписям и по значениям.
Подскажите, пожалуйста, чем отличается Доля от суммы по столбцу  (% of column) или
Доля от общей суммы  (% of total) -
значения одинаковые, и от какой именно общей суммы? Спасибо
01.07.2013 21:07:15
Если у вас в сводной таблице один столбец - ничем не отличаются.
Если несколько, то Доля от суммы по столбцу берет каждый столбец за 100% и считает долю внутри них, а Доля от общей суммы берет за 100% общий итого по всей сводной (правая нижняя ячейка сводной).
22.08.2013 23:44:27
Николай, спасибо за добавление к каждому значению объяснение на английском, у меня EXCEL 2010 на английском
если можно подскажите, почему у меня получаются промежуточные суммы, а я хочу итоговую сумму по каждому городу (на вашем примере) Спасибо, Жанна
08.10.2013 11:07:48
Не понял вопрос. Пришлите мне на почту файл с примером или создайте тему на форуме - поможем.
05.10.2013 15:28:37
как сделать ранг от выручки в последней таблице, какие формулы используются в вычисляемом поле и в  сумме значения?
08.10.2013 11:07:07
В последней таблице нет вычисляемых полей - там только сумма и ранг. Сумма будет по умолчанию, а ранг делаем правой кнопкой мыши - Дополнительные вычисления - Сортировка от минимального к максимальному.
16.12.2015 19:55:52
А как сделать это в 2007? Можно как-то исхитриться? Голову уже сломал на подсчете ранга.
27.11.2013 20:57:08
Добрый вечер, Николай!

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

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

Как записать такую формулу?

Работаю в Excel 2013 + интересно, есть ли решение для 2003-го.
04.01.2014 12:58:07
Дария, не думаю, что в сводной такое можно реализовать встроенными средствами.
Я бы делал отдельный столбец с формулами рядом со сводной или использовал вместо сводной функции выборочного суммирования СУММЕСЛИМН (SUMIFS).
16.12.2013 22:12:10
Можно ли добавить дополнительные "Промежуточные итоги" для сводной таблицы, содержащей большую структуру - столбцов.
Так, чтобы эти дополнительные итоги - показывали итоги по каждой структуре.
Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).

Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).....

Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице)...... Приходится делать надстройку поверх Сводной....
04.01.2014 12:59:06
Иван, тут все зависит от конкретной конструкции вашей сводной. В общем случае: правой кнопкой мыши по полю - Промежуточный итог не помогает?
05.01.2014 13:39:15
Спасибо, уже разобрался!!!

Сводная таблица и Промежуточные итоги - нужны были для того, чтобы с помощью кнопок (чекбокс) и формул "СуммЕсли" из большой БД показать в маленькой таблице определенные динамические данные.
Так вот Промежуточных итогов не хватало (они показывают не ВСЕ итоги), и в конце концов, пришлось вообще отказаться от Сводной таблицы.
Теперь, с помощью кнопок и формулы Массива "Сумм" напрямую из БД без участия Сводной и Итогов берутся динамические данные.
Формула массива позволила сократить расчеты в 10 раз, отказаться от ненужных Сводных таблиц и неполных Промежуточных итогов.
За формулами Массива будущее!!!! )))
08.01.2014 10:24:16
Формулы массива - штука классная, но медленная. Вот вырастет у вас исходная таблица до нескольких тысяч строк - начнутся "тормоза", поверьте :)
05.03.2014 00:23:57
Согласен!
Сейчас работаю с 15.000 строками, и при нажатии кнопки выбора на обработку таблицы уходит 3-4 секунды.
Планирую увеличить таблицу до 50.000 строк - наверное, тогда и начнутся настоящие тормоза )
24.01.2014 17:12:25
Спасибо громадное за ваш сайт, за ваши уроки, приемы и хитрости. Постоянно обращаюсь за вашей мудростью и опытом!
Возник вопрос.
Чаще всего сводные таблицы используются для подготовки всевозможных отчетов. И в этих отчетах нужно сравнивать разные периоды. Я знаю про дополнительные вычисления отличие и приведенное отличие, где мы сравниваем все итоги с каким либо значением или с предыдущим/следующим значением.
А возможно настроить в сводной таблице сравнение месяц одного года с таким же месяцем другого года (например январь 2013 с январем 2012, и т.д.)???
05.03.2014 00:22:26
Напрямую нет. Такая возможность есть при построении сводных на основе OLAP-кубов или в Power Pivot.
В обычных сводных таблицах для этого можно попробовать использовать вычисляемые объекты (не поля), но, скорее всего, придется дорабатывать таблицу исходных данных.

Вообще говоря, Мария, спасибо за идею - надо будет отдельную статью про это написать. Интересная задачка - поставлю себе в план :)
07.03.2014 16:18:45
Наверное, вопрос из той же серии.
В исходной таблице данных содержится и план, и факт.
План/факт     ПодразделениеСумма
планА 100
план Б 150
фактА80
фактБ120

С помощью сводной таблицы легко можно сделать отчет с планом и фактом:
Подразделениеплан факт
А10080
Б150120

А вот как бы сделать еще и расчет отклонений справа от факта?

Через "дополнительные вычисления" это сделать можно, но получается коряво, т.к. помимо столбца собственно с отклонением факта появляется еще и столбец с отклонением плана от плана (пустой).
XL 2010
28.02.2014 10:16:46
Добрый день, Николай

А можно в сводной таблице сделать подсчет именно уникальных значений?. Например, есть большая таблица с продажами, у каждого товара есть номер заказа. Поскольку в одном заказе может быть чем один товар, то если мы просто выбираем "кол-во по полю заказ", то в сводной отображается не кол-во заказов, а кол-во товаров в заказах за конкретный период, а хотелось бы именно кол-во заказов
В принципе можно попробовать через дополнительный столбец, поставить ключик напротив уникальных номеров заказов, но может есть более простой способ сделать это в самой сводной?
Z
28.02.2014 18:41:08
А можно в сводной таблице сделать подсчет именно уникальных значений?
Да, если XL - 2013. Или сводную по сводной.
04.03.2014 11:54:33
А если 2010?
05.03.2014 00:18:12
Вам же Z ответил: "Сводную по сводной".
То есть делаете сводную, в поле строк кидаете нужное поле - получите там список уникальных элементов без повторений, т.к. их в строках или столбцах быть не может. И строите затем еще одну сводную, используя предыдущую как исходные данные, где и подсчитываете количество уникальных.
03.04.2014 15:16:40
Подскажите, а можно ли с помощью сводной таблицы вывести приведенное отличие (% изменения) не просто от предыдущего месяца (периода), а от последнего ненулевого? Например, есть динамика по стоимости какого-либо продукта с января по декабрь. В марте и июне продажи=ноль. Нужно, чтобы % изменения в июле и апреле считался не от марта и июня, а от февраля и мая соответственно. Уже голову сломала, не знаю как сделать.
15.04.2014 10:12:01
Стандартными средствами - точно никак. Тут даже вычисляемые поля не помогут :(
Я бы делал сводную и вытаскивал из нее данные с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в другую таблицу, где уже можно использовать ЕСЛИ для проверки и сравнения.
Примерно так.
Точнее, не видя файла, сказать сложно.
15.04.2014 11:12:20
Спасибо за ответ. Я выкладывала пример здесь на форуме, но так никто и не откликнулся :(  Наверное, муторная задача.
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=56428&MID=470774#message470774
15.04.2014 13:01:57
Однозначно муторная. Если есть острая необходимость и хотя бы минимальный бюджет, то можно попробовать продублировать в ветке Работа. С большой вероятностью кто-нибудь возьмется, думаю.
14.04.2014 17:37:03
Николай, если возможно, примите пожелание, в одном из будущих "приемов" расскажите про про подсчет количество уникальных значений в сводной EXCEL 2013, и приемом Сводной по Сводной в предыдущих версиях.
15.04.2014 10:15:57
Принято :) Думаю, добавлю это сюдакак один из способов - будет логично.
Николай, подскажите такую вещь. Отличие и приведенное отличие действует классно, если период времени один - только месяцы или только годы. А если нужно сравнить декабрь 2013 с январем 2014 года? Как вывести эту динамику? Благодарю за помощь.
вот файл примера: https://onedrive.live.com/redir?resid=B37BE04A6CC44BCB!7426&authkey=!AMskIQNihfFKKp4&ithint=file%2c.xlsx
07.05.2014 12:02:56
Я бы, наверное, с ходу просто добавил в исходные данные еще один столбец с порядковым номером месяца (декабрь 2013 будет, допустим 12-м, а январь 2014 уже 13-м и т.д.) И строил потом сводную и считал динамику по этому номеру, а не по фактической дате.
Либо вытаскивать данные из сводной в отдельную таблицу с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и сравнивать уже вручную формулами.
Страницы: 1  2  
Наверх