Допустим, у нас есть построенная сводная таблица с результатами анализа продаж по месяцам для разных городов (при необходимости прочитайте эту статью, чтобы понять, как вообще их создавать, или освежить память):
Мы хотим немного изменить его внешний вид, чтобы он более четко отображал нужные вам данные, а не просто сваливал на экран кучу цифр. Что для этого можно сделать?
Другие функции расчета вместо банальной суммы
Если щелкнуть правой кнопкой мыши по вычисляемому полю в области данных и выбрать команду из контекстного меню Параметры поля (Настройки поля) или в версии Excel 2007 – Параметры поля значения (Настройки поля значений), то откроется очень полезное окно, с помощью которого можно задать кучу интересных настроек:
В частности, вы можете легко изменить функцию расчета поля на среднее, минимум, максимум и т. д. Например, если мы изменим сумму на количество в нашей сводной таблице, то мы увидим не общий доход, а количество транзакций. для каждого продукта:
По умолчанию Excel всегда автоматически выбирает суммирование для числовых данных. (Сумма), а для нечисловых (даже если из тысячи ячеек с цифрами есть хотя бы одна пустая или с текстом или с числом в текстовом формате) — функция подсчета количества значений (Граф).
Если вы хотите видеть в одной сводной таблице сразу и среднее, и сумму, и количество, т.е. несколько функций расчета для одного и того же поля, то смело несколько раз наведите мышкой на область данных нужного вам поля. подряд, чтобы получить что-то подобное:
…а затем задайте разные функции для каждого из полей, поочередно щелкая по ним мышкой и выбирая команду Параметры поля (Настройки поля)чтобы получить то, что вы хотите:
Если в том же окне Параметры поля нажмите кнопку Дополнительно (Параметры) или перейдите на вкладку Дополнительные расчеты (в Excel 2007-2010), тогда станет доступен выпадающий список Дополнительные расчеты (Показать данные как):
В этом списке, например, можно выбрать параметры Процент от суммы строки (% строки), Процент от общей суммы по столбцу (% столбца) or Доля от общего числа (% от общего количества)для автоматического расчета процентов для каждого продукта или города. Вот как, например, будет выглядеть наша сводная таблица с включенной функцией Процент от общей суммы по столбцу:
Динамика продаж
Если в раскрывающемся списке Дополнительные расчеты (Показать данные как) выберите вариант различие (Разница), и в нижних окнах Поиск (Базовое поле) и Элемент (Базовый предмет) выберите Месяц и Назад (в родном английском варианте вместо этого странного слова было более понятное Предыдущий, те. предыдущий):
…тогда мы получаем сводную таблицу, в которой показаны отличия продаж каждого следующего месяца от предыдущего, т.е. – динамика продаж:
А если заменить различие (Разница) on Учитывая разницу (% разницы) и добавить условное форматирование если выделить красным отрицательные значения, то получим то же самое, но не в рублях, а в процентах:
PS
В Microsoft Excel 2010 все вышеперечисленные настройки расчета можно сделать еще проще – щелкнув правой кнопкой мыши по любому полю и выбрав команды из контекстного меню. Всего за (Суммировать значения по):
… а также Дополнительные расчеты (Показать данные как):
Также в версии Excel 2010 к этому набору добавилось несколько новых функций:
- % от суммы по родительской строке (столбцу) — позволяет рассчитать долю относительно промежуточного итога по строке или столбцу:
В предыдущих версиях вы могли рассчитать только долю относительно общей суммы.
- % от суммы совокупно – работает аналогично функции накопительной суммы, но отображает результат в виде дроби, т.е. в процентах. Удобно посчитать, например, процент выполнения плана или бюджета:
- Сортировка от меньшего к большему и наоборот. — немного странное название функции ранжирования (RANK), вычисляющей порядковый номер (позицию) элемента в общем списке значений. Например, с его помощью удобно ранжировать менеджеров по совокупной выручке, определяя, кто на каком месте находится в общем зачете:
- Что такое сводные таблицы и как их создавать
- Группировка чисел и дат с нужным шагом в сводных таблицах
- Создание отчета сводной таблицы на основе нескольких диапазонов исходных данных