Настройка вычислений в сводных таблицах
Допустим, у нас есть построенная сводная таблица с результатами анализа продаж по месяцам для разных городов (если необходимо, то почитайте эту статью, чтобы понять, как их вообще создавать или освежить память):
Нам хочется слегка изменить ее внешний вид, чтобы она отображала нужные вам данные более наглядно, а не просто вываливала кучу чисел на экран. Что для этого можно сделать?
Другие функции расчета вместо банальной суммы
Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (Field Settings) или в версии Excel 2007 – Параметры полей значений (Value Field Settings), то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:
В частности, можно легко изменить функцию расчета поля на среднее, минимум, максимум и т.д. Например, если поменять в нашей сводной таблице сумму на количество, то мы увидим не суммарную выручку, а количество сделок по каждому товару:
По умолчанию, для числовых данных Excel всегда автоматически выбирает суммирование (Sum), а для нечисловых (даже если из тысячи ячеек с числами попадется хотя бы одна пустая или с текстом или с числом в текстовом формате) – функцию подсчета количества значений (Count).
Если же захочется увидеть в одной сводной таблице сразу и среднее, и сумму, и количество, т.е. несколько функций расчета для одного и того же поля, то смело забрасывайте мышкой в область данных нужное вам поле несколько раз подряд, чтобы получилось что-то похожее:
…а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры поля (Field settings), чтобы в итоге получить желаемое:
Долевые проценты
Если в этом же окне Параметры поля нажать кнопку Дополнительно (Options) или перейти на вкладку Дополнительные вычисления (в Excel 2007-2010), то станет доступен выпадающий список Дополнительные вычисления (Show data as):
В этом списке, например, можно выбрать варианты Доля от суммы по строке (% of row), Доля от суммы по столбцу (% of column) или Доля от общей суммы (% of total), чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:
Динамика продаж
Если в выпадающем списке Дополнительные вычисления (Show data as) выбрать вариант Отличие (Difference), а в нижних окнах Поле (Base field) и Элемент (Base item) выбрать Месяц и Назад (в родной англоязычной версии вместо этого странного слова было более понятное Previous, т.е. предыдущий):
...то получим сводную таблицу, в которой показаны отличия продаж каждого следующего месяца от предыдущего, т.е. – динамика продаж:
А если заменить Отличие (Difference) на Приведенное отличие (% of difference) и добавить условное форматирование для выделения отрицательных значений красным цветом - то получим то же самое, но не в рублях, а в процентах:
P.S.
В Microsoft Excel 2010 все вышеперечисленные настройки вычислений можно проделать еще проще - щелкнув правой кнопкой мыши по любому полю и выбрав в контекстном меню команды Итоги по (Summarize Values By):
... и Дополнительные вычисления (Show Data as):
Также в версии Excel 2010 к этому набору добавились несколько новых функций:
- % от суммы по родительской строке (столбцу) - позволяет посчитать долю относительно промежуточного итога по строке или столбцу:
В прошлых версиях можно было вычислять долю только относительно общего итога.
- % от суммы нарастающим итогом - работает аналогично функции суммирования нарастающим итогом, но отображает результат в виде доли, т.е. в процентах. Удобно считать, например, процент выполнения плана или исполнения бюджета:
- Сортировка от минимального к максимальному и наоборот - немного странное название для функции ранжирования (РАНГ), вычисляющей порядковый номер (позицию) элемента в общем списке значений. Например, с ее помощью удобно ранжировать менеджеров по их суммарной выручке, определяя кто на каком месте в общем зачете:
Ссылки по теме
- Что такое сводные таблицы, как их строить
- Группировка чисел и дат с нужным шагом в сводных таблицах
- Построение отчета сводной таблицы по нескольким диапазонам исходных данных
Подскажите пожалуйста, как построить несколько сводных таблиц с отдельным кэшем для каждой в одной книге при использовании одного и того же исходного файла. в 2010 excele
Спасибо
[img]file:///C:/Users/8ADB~1/AppData/Local/Temp/msohtmlclip1/01/clip_image001.jpg[/img]
Как можно создать сводную таблицу, так чтобы новые введенные данные отображались автоматом в этой таблице. Я новичек в экселе. По вашей статье все классно, но новые данные или измененные не отображаются. Надо как бы заново настраивать конструктор. Может как то по другому делается? У меня шеф может в любой момент может спросить итоги продаж, а данные накладных я ввожу каждый день. И чтобы эти введеные данные вводились автоматом. Подскажите заранее спасибо.
Заранее спасибо!
Вопрос решен!! Спасибо!
На выходе нужна сводная таблица, что будут проранжированы три марки по частоте их выбора.
Если указывать долю по столбцам - выдает 100%, что логично. Есть еще вкладка Доля, если там выбрать переменную и указать что нужна доля именно кода 1 - выдает Н/Д.
Всем спасибо!
Пример тут
Спасибо за статью! Есть вопрос:
Имеется ли возможность для сводной таблицы в итогах по столбцам и строкам настроить разные вычисления? Например для строк это будет "Итоги по: Сумма", а для столбцов "Итоги по: Максимум".
Т.е. в столбцах идёт сумма нарастающим итогом и нужно в общем итоге видеть максимальное значение, а по строкам общий итог нужен именно в сумме значений.
Скажите, пожалуйста, а можно ли создать связь между сводными таблицами? Допустим, есть 2 показателя - продажи в сумме и продажи в количестве. Этот показатель в сводной таблице выносится в поле Значения. Есть 3 сводных таблицы на одном листе: таблица с исходными данными, с абсолютным отклонением и приведенным отклонением (как описано в данной статье). Можно ли создать какую-либо связь, чтобы автоматически происходила смена значений в этих 3 таблицах при выборе в поле Значения продаж в сумме или в количестве?
Подскажите пожалуйста, не могу никак показывать разницу минимальных значений в сводной. В исходнике добавил обычную формулу, которая выдает разницу (например, текущего сезона и прошлого). Так как данных много, создал сводную.
Допустим есть несколько групп, в каждой группе разное количество человек и данные по ним за два разных периода. Необходимо найти минимальное значение (не самого человека) по каждой группе за два периода и их разницу. Проблема заключается в том, что при минимальных значениях по каждому периоду разница выходит другая (Пример: в Группе В есть сотрудники Вакуленко Д. (данные за 1 период - 346, второй - 362), Рыбкин М. (1 период - 373, второй - 396) и Соловьев Е. (1 период - 400, второй - 400). При группировке в Группе В минимальные значения за первый период = 346, второй = 362, а разница должна быть -16 (минус 16), а выходит разница -23 (минус 23), то есть минимальная по Рыбкину М., а не Вакуленко Д..
Не знаю. смог ли обьяснить или нет, есть пример -
Добавить кварталы в строки к месяцам
можно только через добавление доп.столбца в исходные данные?
Никакие промежуточные итоги не умеют этого делать?
В моей задаче есть месяца разных лет, а мне нужно сравнить по годам
есть такая возможность без доп.вычислений в исходных данных?
Спасибо!
upd: нашёл возможность ручной группировки:
активировать таблицу\Параметры\Группа по выделенному
хотя бы так
Простая задача в сводной таблице: столбец А план, столбец B факт.
считаем с помощью вычисляемого поля % выполнения: факт делим на план.
Получаем процент, но если мы убираем вычисляемое поле из области, то при следующем его выборе, он становится числовым.
Подскажите пожалуйста, как настроить вычисляемое поле, чтоб оно всега было в процентах?
Заранее спасибо.