Настройка вычислений в сводных таблицах
Допустим, у нас есть построенная сводная таблица с результатами анализа продаж по месяцам для разных городов (если необходимо, то почитайте эту статью, чтобы понять, как их вообще создавать или освежить память):
Нам хочется слегка изменить ее внешний вид, чтобы она отображала нужные вам данные более наглядно, а не просто вываливала кучу чисел на экран. Что для этого можно сделать?
Другие функции расчета вместо банальной суммы
Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (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 к этому набору добавились несколько новых функций:
- % от суммы по родительской строке (столбцу) - позволяет посчитать долю относительно промежуточного итога по строке или столбцу:
В прошлых версиях можно было вычислять долю только относительно общего итога.
- % от суммы нарастающим итогом - работает аналогично функции суммирования нарастающим итогом, но отображает результат в виде доли, т.е. в процентах. Удобно считать, например, процент выполнения плана или исполнения бюджета:
- Сортировка от минимального к максимальному и наоборот - немного странное название для функции ранжирования (РАНГ), вычисляющей порядковый номер (позицию) элемента в общем списке значений. Например, с ее помощью удобно ранжировать менеджеров по их суммарной выручке, определяя кто на каком месте в общем зачете:
Ссылки по теме
- Что такое сводные таблицы, как их строить
- Группировка чисел и дат с нужным шагом в сводных таблицах
- Построение отчета сводной таблицы по нескольким диапазонам исходных данных
: последние 2 столбца. Выделяю группу ячеек и меняется вычисления только в одном поле.
Николай, спасибо за доступное объяснение, вопрос по вычисляемым полям в сводной таблице, создаю поле в котором округляю вверх значения другого поля, как сделать так чтобы в строке итоги суммировались уже округленные значения, а не округлялось вверх итоговое значение исходного столбца?
=(Значение*Общая сумма) / (Сумма по столбцу*Сумма по строке)
Имхо, неочевидная и бесполезная штука - не применял в реальных проектах ни разу.
Помогите разобраться в следующем: имеем сводную таблицу, состоящую из 5-ти столбцов: 1 –номенклатура; 2 –сумма по полю План; 3 – сумма по полю Факт; 4 - % выполнения плана; 5 – прогноз выполнения (два последних – это вычисляемые поля). Поле «прогноз выполнения» вычисляется по формуле: Сумма по полю Факт/9*20/Сумма по полю План, - где цифра 9 – это количество отработанных дней в месяце, 20 – количество рабочих дней в месяце. Так как таблица обновляется каждый день, то и формулу соответственно приходится корректировать в ручную на кол-во этих отработанных дней. К сожалению в формуле вычисляемого поля невозможно использовать функции или ссылки на ячейки. Есть ли другой способ, что б в этой формуле само изменялось кол-во отработанных дней в месяце?
хотя может быть и ошибаюсь и знаю не все возможности сводных...
применительно к моему примеру мне на основе данных сводной надо было вычислить: общую сумму операций, количество операций, среднее значение одной операции, дату первой и дату последне операции, количество дней между первой и последней операцией... все это (кроме последнего параметра) можно вычислить с помощью сводной за исключением одного минуса - сводная по столбцам увеличивается до неимоверного размера, т.к. при перетаскивании в поле значение нужных данных увеличивается количество столбцов... и количество дней между первой и последней операцией в сводной не вычислить...
Есть регионы, в них офисы и в каждом офисе - встречи. Сводную по количеству встреч в каждом офисе и каждом регионе - сделать я могу. Но это не показательно, т.к. в большом регионе (много офисов) и встреч много. Мне нужно посмотреть среднее количество встреч в офисах по регионам (т.е в каждом регионе - количество встреч в этом регионе, деленное на количество офисов в этом регионе).
Спасибо
Если не получится - поэкспериментируйте там же с вычисляемыми полями. Не видя вашей таблицы, точно сказать тяжело.
Благодарю Вас за проделанную вами работу!
Создал в сводной таблице "Вычисляемое поле", можно ли применить именно к этому полю фильтр (допустим по названию получателя)?
В идеале хотелось бы получить что-то в виде
Вычисляемое поле = (Вычисл. поле 1 [фильтр 1] + Вычисл. поле 2 [фильтр 2] + ... + Вычисл. поле n [фильтр n] ) Возможен ли такой вариант?? Может макросом.
И еще по фильтрам, можно ли как-то фильтровать по принципу содержит ("Моск*", "*тербур*" и др.)?
Спасибо!
Доля от общей суммы (% of total) -
значения одинаковые, и от какой именно общей суммы? Спасибо
Если несколько, то Доля от суммы по столбцу берет каждый столбец за 100% и считает долю внутри них, а Доля от общей суммы берет за 100% общий итого по всей сводной (правая нижняя ячейка сводной).
если можно подскажите, почему у меня получаются промежуточные суммы, а я хочу итоговую сумму по каждому городу (на вашем примере) Спасибо, Жанна
Не могу догадаться, как записать формулу в вычисляемом поле в сводной таблице. Буду очень благодарна за подсказку.
В колонке А количество пациентов в больнице. В колонке В доля пациентов с воспалением легких.
Данные приводятся для всех больниц в нескольких городах.
В итогах по каждому городу нужно привести средневзвешенное значение пациентов с воспалением легких.
Средневзвешенное значение - это сумма произведений значений В на количество пациентов А в каждой больнице, отнесенное к сумме количества пациентов А во всех больницах в городе.
Как записать такую формулу?
Работаю в Excel 2013 + интересно, есть ли решение для 2003-го.
Я бы делал отдельный столбец с формулами рядом со сводной или использовал вместо сводной функции выборочного суммирования СУММЕСЛИМН (SUMIFS).
Так, чтобы эти дополнительные итоги - показывали итоги по каждой структуре.
Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).
Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).....
Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице)...... Приходится делать надстройку поверх Сводной....
Сводная таблица и Промежуточные итоги - нужны были для того, чтобы с помощью кнопок (чекбокс) и формул "СуммЕсли" из большой БД показать в маленькой таблице определенные динамические данные.
Так вот Промежуточных итогов не хватало (они показывают не ВСЕ итоги), и в конце концов, пришлось вообще отказаться от Сводной таблицы.
Теперь, с помощью кнопок и формулы Массива "Сумм" напрямую из БД без участия Сводной и Итогов берутся динамические данные.
Формула массива позволила сократить расчеты в 10 раз, отказаться от ненужных Сводных таблиц и неполных Промежуточных итогов.
За формулами Массива будущее!!!! )))
Сейчас работаю с 15.000 строками, и при нажатии кнопки выбора на обработку таблицы уходит 3-4 секунды.
Планирую увеличить таблицу до 50.000 строк - наверное, тогда и начнутся настоящие тормоза )
Возник вопрос.
Чаще всего сводные таблицы используются для подготовки всевозможных отчетов. И в этих отчетах нужно сравнивать разные периоды. Я знаю про дополнительные вычисления отличие и приведенное отличие, где мы сравниваем все итоги с каким либо значением или с предыдущим/следующим значением.
А возможно настроить в сводной таблице сравнение месяц одного года с таким же месяцем другого года (например январь 2013 с январем 2012, и т.д.)???
В обычных сводных таблицах для этого можно попробовать использовать вычисляемые объекты (не поля), но, скорее всего, придется дорабатывать таблицу исходных данных.
Вообще говоря, Мария, спасибо за идею - надо будет отдельную статью про это написать. Интересная задачка - поставлю себе в план
В исходной таблице данных содержится и план, и факт.
С помощью сводной таблицы легко можно сделать отчет с планом и фактом:
А вот как бы сделать еще и расчет отклонений справа от факта?
Через "дополнительные вычисления" это сделать можно, но получается коряво, т.к. помимо столбца собственно с отклонением факта появляется еще и столбец с отклонением плана от плана (пустой).
XL 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: нашёл возможность ручной группировки:
активировать таблицу\Параметры\Группа по выделенному
хотя бы так