Настройка вычислений в сводных таблицах
Допустим, у нас есть построенная сводная таблица с результатами анализа продаж по месяцам для разных городов (если необходимо, то почитайте эту статью, чтобы понять, как их вообще создавать или освежить память):
Нам хочется слегка изменить ее внешний вид, чтобы она отображала нужные вам данные более наглядно, а не просто вываливала кучу чисел на экран. Что для этого можно сделать?
Другие функции расчета вместо банальной суммы
Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры поля (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
А можно в сводной таблице сделать подсчет именно уникальных значений?. Например, есть большая таблица с продажами, у каждого товара есть номер заказа. Поскольку в одном заказе может быть чем один товар, то если мы просто выбираем "кол-во по полю заказ", то в сводной отображается не кол-во заказов, а кол-во товаров в заказах за конкретный период, а хотелось бы именно кол-во заказов
В принципе можно попробовать через дополнительный столбец, поставить ключик напротив уникальных номеров заказов, но может есть более простой способ сделать это в самой сводной?
То есть делаете сводную, в поле строк кидаете нужное поле - получите там список уникальных элементов без повторений, т.к. их в строках или столбцах быть не может. И строите затем еще одну сводную, используя предыдущую как исходные данные, где и подсчитываете количество уникальных.
Я бы делал сводную и вытаскивал из нее данные с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в другую таблицу, где уже можно использовать ЕСЛИ для проверки и сравнения.
Примерно так.
Точнее, не видя файла, сказать сложно.
вот файл примера:
Либо вытаскивать данные из сводной в отдельную таблицу с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и сравнивать уже вручную формулами.