Сводные таблицы – это удивительный встроенный инструмент отчетности в Excel. Хотя обычно они используются для суммирования данных с итогами, вы также можете использовать их для расчета процента изменений между значениями. Еще лучше: это просто сделать.
Вы можете использовать эту технику, чтобы делать разные вещи – практически везде, где вы хотите увидеть, как одно значение сравнивается с другим. В этой статье мы собираемся использовать простой пример расчета и отображения процента, на который общая стоимость продаж меняется с каждым месяцем.
Вот лист, который мы собираемся использовать.
Это довольно типичный пример листа продаж, который показывает дату заказа, имя клиента, торгового представителя, общую стоимость продаж и некоторые другие вещи.
Чтобы сделать все это, мы сначала отформатируем наш диапазон значений в виде таблицы в Excel, а затем мы создадим сводную таблицу, чтобы сделать и отобразить наши расчеты процентного изменения.
Форматирование диапазона в виде таблицы
Если ваш диапазон данных еще не отформатирован в виде таблицы, мы рекомендуем вам сделать это. Данные, хранящиеся в таблицах, имеют несколько преимуществ по сравнению с данными в диапазонах ячеек на рабочем листе, особенно при использовании сводных таблиц (подробнее о преимуществах использования таблиц).
Чтобы отформатировать диапазон в виде таблицы, выберите диапазон ячеек и нажмите «Вставка»> «Таблица».
Убедитесь, что диапазон правильный, что у вас есть заголовки в первой строке этого диапазона, а затем нажмите «ОК».
Диапазон теперь отформатирован как таблица. Присвоение имен таблице будет проще использовать в будущем при создании сводных таблиц, диаграмм и формул.
Перейдите на вкладку «Дизайн» в разделе «Инструменты для таблиц» и введите имя в поле в начале ленты. Эта таблица была названа «Продажи».
Вы также можете изменить стиль таблицы здесь, если хотите.
Создайте сводную таблицу для отображения изменения в процентах
Теперь давайте приступим к созданию сводной таблицы. В новой таблице щелкните Вставить> Сводная таблица.
Откроется окно Создать сводную таблицу. Он автоматически обнаружит вашу таблицу. Но вы можете выбрать таблицу или диапазон, который вы хотите использовать для сводной таблицы на данный момент.
Сгруппируйте даты по месяцам
Затем мы перетащим поле даты, которое мы хотим сгруппировать, в область строк сводной таблицы. В этом примере поле называется Дата заказа.
Начиная с Excel 2016, значения даты автоматически группируются по годам, кварталам и месяцам.
Если ваша версия Excel этого не делает или вы просто хотите изменить группировку, щелкните правой кнопкой мыши ячейку, содержащую значение даты, а затем выберите команду «Группировать».
Выберите группы, которые вы хотите использовать. В этом примере выбраны только годы и месяцы.
Год и месяц теперь являются полями, которые мы можем использовать для анализа. Месяцы по-прежнему называются датой заказа.
Добавьте поля значений в сводную таблицу
Переместите поле Year из Rows в область Filter. Это позволяет пользователю фильтровать сводную таблицу в течение года, а не загромождать сводную таблицу слишком большим количеством информации.
Перетащите поле, содержащее значения (в данном примере «Общая стоимость продаж»), которые вы хотите рассчитать, и представьте изменения в области «Значения» дважды .
Возможно, это пока не очень похоже. Но это очень скоро изменится.
Оба поля значений будут иметь значение по умолчанию для суммы и в настоящее время не имеют форматирования.
Значения в первом столбце мы хотели бы сохранить как итоги. Однако они требуют форматирования.
Щелкните правой кнопкой мыши номер в первом столбце и выберите «Форматирование чисел» в контекстном меню.
Выберите формат «Учет» с 0 десятичными знаками в диалоговом окне «Формат ячеек».
Сводная таблица теперь выглядит следующим образом:
Создайте столбец процентного изменения
Щелкните правой кнопкой мыши значение во втором столбце, укажите «Показать значения», а затем выберите параметр «% отличий от».
Выберите «(Предыдущий)» в качестве базового элемента. Это означает, что значение текущего месяца всегда сравнивается со значением предыдущих месяцев (поле «Дата заказа»).
Сводная таблица теперь показывает как значения, так и процентное изменение.
Щелкните в ячейке, содержащей метки строк, и введите «Месяц» в качестве заголовка для этого столбца. Затем щелкните в ячейке заголовка для второго столбца значений и введите «Дисперсия».
Добавьте несколько стрел дисперсии
Чтобы действительно отшлифовать эту сводную таблицу, мы хотели бы лучше визуализировать процентное изменение, добавив несколько зеленых и красных стрелок.
Это даст нам прекрасный способ увидеть, было ли изменение положительным или отрицательным.
Щелкните любое из значений во втором столбце, а затем нажмите «Главная»> «Условное форматирование»> «Новое правило». В открывшемся окне «Редактировать правило форматирования» выполните следующие действия:
- Выберите параметр «Все ячейки, отображающие значения« Дисперсия »для даты заказа».
- Выберите «Наборы значков» в списке «Формат стиля».
- Выберите красный, янтарный и зеленый треугольники из списка стилей значков.
- В столбце «Тип» измените опцию «Список» вместо «Процент». Это изменит значение столбца на 0. Именно то, что мы хотим.
Нажмите «ОК», и условное форматирование будет применено к сводной таблице.
Сводные таблицы являются невероятным инструментом и одним из самых простых способов отображения процентного изменения значений во времени.
