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

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

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  
J C
31.05.2014 21:46:48
Николай, а как бы вместо среднего арифметического в поле Итогов отобразить среднее геометрическое ??.. (хоть бы это было возможно в сводной)... или как-нибудь через промежуточные итоги или вычисления в сводной? я вижу что Произведение есть, Количество есть - т е надо по строкам из этих произведений элементов извлечь корень степени равной их количеству (ну или возвести в степень 1/n, где n-количество элементов)...это только через доп поле? но внизу Общий от Итогов тоже по такому принципу должен быть (ср геом) - возможно ли как-нибудь простыми методами? Заранее спасибо...
09.06.2014 12:19:40
Стандартными средствами - точно никак. Я бы вытаскивал данные из сводной в отдельную таблицу с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и считал потом уже формулой СРГЕОМ.  
Добрый день, а подскажите пожалуйста можно ли в поле значения отобразить текст? Мне нужно чтобы там отображались значения такого формата "ТО6", "Т48" а получаются только нули да единички))
14.08.2014 13:36:02
Скажите, почему не активна "Сортировка...." в дополнительных вычислениях? Спасибо ))
19.10.2014 12:30:28
Добрый день, Николай
Подскажите пожалуйста, как построить несколько сводных таблиц с отдельным кэшем для каждой в одной книге при использовании одного и того же исходного файла. в 2010 excele
Спасибо
18.11.2014 15:59:12
Здравствуйте, Николай. Как отключить подсчет любых вычислений (кроме итоговой строки) в выборочных столбцах сводной таблицы и есть ли возможность использовать в них формулу? Поясню на примере:в исходной таблице имеется база данных по месяцам и годам (ввела отдельный столбец "месяц" и отдельный "год") следующих значений (числовых): данные на начало периода, обороты и данные на конец периода-это столбцы исходной таблицы, в строках таблицы-название покупателя. В сводной таблице у меня появилась возможность видеть сводную информацию по каждому покупателю по заданному периоду, НО данные на начало и конец периода тоже выводятся как сумма этих значений по периодам, а мне нужно чтобы данные на начало периода в сводной таблице равнялись данным на начало соответствующего периода  исходной таблицы, а данные на конец периода сводной таблицы вычислялись бы формулой по данным сводной таблицы. Можно ли реализовать такую возможность в сводных таблицах или надо использовать другие возможности Excel? Спасибо.
22.03.2015 03:08:17
Помогите срочным советом, как среднее применить к итогу только, и делать таблицу как я прилагаю на картинке ( фото из лабораторной в стиле "как надо чтоб было), пересмотрела все Ваши видео, но у меня даже среднее работает не так, как у Вас - применяется к конкретной строке и все:(

[img]file:///C:/Users/8ADB~1/AppData/Local/Temp/msohtmlclip1/01/clip_image001.jpg[/img]
22.11.2015 20:09:47
Добрый вечер! подскажите пжл как можно посчитать, например, % выполнения плана, исп-я сводную таблицу по 2 столбцам одновременно, т.к. план - это 1 столбец, факт - второй, а в третьем мне нужно посчитать %, но не просто, забив формулу2/1, а с помощью "доп.вычислений". спасибо
06.12.2015 13:02:05
Алексей, имеется ли возможность прописать формулу ВПР в вычисляемом поле для сводной таблицы?
Подскажите как посчитать скритие ячейки , сумировать ?
04.02.2016 16:50:34
Николай, здравствуйте! Ликбез - полезная штука, на все времена. Но вот странное дело, уже давно приходиться считать вручную сумму средних значений в сводной таблице. На слух - задачка тривиальная. Но в Эксель тупо не предусмотрен такой функционал. А может быть это я просто не знаю какого-то секрета? (На вопрос: "А зачем вам это?", отвечаю: Есть такие массивы данных, где для сравнительного анализа численных характеристик разных объектов дублирование повторяющихся характеристик в разных строках данных неизбежно. В итоге, приходиться вычислять сумму всех значений столбца, которые состоят из пар, троек, четверок, и т.д., одинаковых значений.).
Добрый день! Подскажите, пожалуйста, добавила в таблицу % от суммы по родительской строке, считает все правильно, после добавления новых данных в базу и обновления сводника пересчитывает тоже верно Но вот незадача, после закрытия документа, когда открываю снова и обновляю сводник, % от суммы по родительской строке "слетает", нажимаю правой кнопкой на этот столбец, выбираю доп.вычисления , а там галка "без вычислений". Каждый раз приходится заново выставлять % от суммы по родительской строке. Почему так происходит?
20.07.2017 15:49:18
Добрый день!
Как можно создать сводную таблицу, так чтобы новые введенные данные отображались автоматом в этой таблице. Я новичек в экселе. По вашей статье все классно, но новые данные или измененные не отображаются. Надо как бы заново настраивать конструктор. Может как то по другому делается? У меня шеф может в любой момент может спросить итоги продаж, а данные накладных я ввожу каждый день. И чтобы эти введеные данные вводились автоматом. Подскажите заранее спасибо.
k m
04.08.2017 09:53:05
Добрый день! Подскажите, пожалуйста, как в сводной таблице в качестве результатов вывести данные из исходной таблицы, чтобы к ним не применялись вычисления, и они не группировались, не применялась операция для сведения данных, при условии, что в исходной таблице при заданных параметрах эти значения уникальны.
Заранее спасибо!

Вопрос решен!! Спасибо!  
17.01.2018 15:05:20
Всем привет! Кто знает как в сводных таблицах показать долю значения, если переменная бинарная? Есть три марки, которые отмечались опрошенными. Т.е. в таблице есть значения 1, показывающие, что марка была выбрана и 0 (марка не выбрана).

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

Если указывать долю по столбцам - выдает 100%, что логично. Есть еще вкладка Доля, если там выбрать переменную и указать что нужна доля именно кода 1 - выдает Н/Д.

Всем спасибо!

Пример тут
https://drive.google.com/file/d/1kA3MR0FjY_xAt7FyL6UQ-c3JV8c4hn9l/view?usp=sharing
26.01.2018 13:41:40
Николай, добрый день!

Спасибо за статью! Есть вопрос:

Имеется ли возможность для сводной таблицы в итогах по столбцам и строкам настроить разные вычисления? Например для строк это будет "Итоги по: Сумма", а для столбцов "Итоги по: Максимум".

Т.е. в столбцах идёт сумма нарастающим итогом и нужно в общем итоге видеть максимальное значение, а по строкам общий итог нужен именно в сумме значений.  
14.04.2018 18:35:47
03.10.2018 10:45:04
Николай, здравствуйте!
Скажите, пожалуйста, а можно ли создать связь между сводными таблицами? Допустим, есть 2 показателя - продажи в сумме и продажи в количестве. Этот показатель в сводной таблице выносится в поле Значения. Есть 3 сводных таблицы на одном листе: таблица с исходными данными, с абсолютным отклонением  и приведенным отклонением (как описано в данной статье). Можно ли создать какую-либо связь, чтобы автоматически происходила смена значений в этих 3 таблицах при выборе в поле Значения продаж в сумме или в количестве?
15.11.2019 15:37:03
Николай, здравствуйте!
Подскажите пожалуйста, не могу никак показывать разницу минимальных значений в сводной. В исходнике добавил обычную формулу, которая выдает разницу (например, текущего сезона и прошлого). Так как данных много, создал сводную.
Допустим есть несколько групп, в каждой группе разное количество человек и данные по ним за два разных периода. Необходимо найти минимальное значение (не самого человека) по каждой группе за два периода и их разницу. Проблема заключается в том, что при минимальных значениях по каждому периоду разница выходит другая (Пример: в Группе В есть сотрудники Вакуленко Д. (данные за 1 период - 346, второй - 362), Рыбкин М. (1 период - 373, второй - 396) и Соловьев Е. (1 период - 400, второй - 400). При группировке в Группе В минимальные значения за первый период = 346, второй = 362, а разница должна быть -16 (минус 16), а выходит разница -23 (минус 23), то есть минимальная по Рыбкину М., а не Вакуленко Д..
Не знаю. смог ли обьяснить или нет, есть пример - Пример
mo8
26.03.2020 13:48:58
Здравствуйте!
Добавить кварталы в строки к месяцам
можно только через добавление доп.столбца в исходные данные?
Никакие промежуточные итоги не умеют этого делать?
В моей задаче есть месяца разных лет, а мне нужно сравнить по годам
есть такая возможность без доп.вычислений в исходных данных?
Спасибо!
upd: нашёл возможность ручной группировки:
активировать таблицу\Параметры\Группа по выделенному
хотя бы так
Николай, присоединяюсь ко всем благодарностям в Ваш адрес! Сама пользуюсь вашей книгой готовые решения. Но вот нигде не смогла найти ответ на простой как мне кажется вопрос: При протягивании формулы в сводной таблице, копируются значения из первой ячейки, как решить данную задачу. Спасибо!
19.01.2021 09:05:54
Добрый день, не могу разобраться и найти решение в интернете. Ситуация такая, необходимо, чтобы среднее значение в сводной показывалось не как 1 500 000, а как 1,5 млн в сводной. Когда пишу формулу в вычисляемом поле, то почему-то выходит в млн, но не среднее значение строк, а сумма. Даже вроде как пишет, что это "Среднее по полю В МЛН", но по факту все равно это сумма строк деленная на 1 000 000...помогите, пожалуйста
19.05.2021 10:30:05
Добрый день. а возможно ли сделать суммирование в сводной по модулю?
24.09.2021 14:23:59
Здравствуйте. Похожий вопрос: нужно в сводной таблице суммировать только положительные значения столбца исходной таблицы. Но кажется суммесли не работает в режиме "вычисляемое поле"
21.07.2023 01:30:42
Здравствуйте, Николай! Подскажите в какую сторону "копать"? В сводной таблице необходимо отражение значений показателя заболеваемости (на 100 тысяч населения). Умная таблица содержит большой список пациентов, зарегистрированных с болезнью в разные годы, в разных населенных пунктах, где численность населения, естественно, разница по годам. Пытаюсь создать сводную, где по строкам - населенный пункт, по столбцам - годы, значения - этот самый показатель.
16.02.2024 20:22:13
добрый день, Николай!
Простая задача в сводной таблице: столбец А план, столбец B факт.
считаем с помощью вычисляемого поля % выполнения: факт делим на план.
Получаем процент, но если мы убираем вычисляемое поле из области, то при следующем его выборе, он становится числовым.
Подскажите пожалуйста, как настроить вычисляемое поле, чтоб оно всега было в процентах?
Заранее спасибо.
Страницы: 1  2  
Наверх