Сводные таблицы в Excel – это инструмент, который позволяет представить данные в нужном виде и быстро понять их. Сводные таблицы в ряде случаев заменяют формулы и функции, делая анализ интуитивно понятным и удобным. Инструмент позволяет «крутить» и показывать данные в разном виде: менять уровни группировки, фильтровать, перемещать столбцы и строки, добавлять визуализации.
Как построить сводную таблицу: пример
Рассмотрим базовые возможности сводных таблиц на примере анализа продаж велосипедного магазина. В исходной таблице имеется номер транзации, модель, цвет, дата заказа, стоимость у поставщика и цена продажи в рознице:
Что может интересовать продавца в нашем случае? Какая маржа получается от продажи тех или иных моделей, какие цвета более востребованы, какие модели лучше продаются в начале сезона и др. Конечно, для анализа можно добавить в таблицу новые столбцы и с помощью формул и графиков ответить на разнообразные вопросы. Однако обратимся к сводным таблицам.
- Выделим таблицу (мышкой или кнопками Ctrl + A) и на вкладке Вставка выберем Сводная таблица.
Вы также можете воспользоваться кнопкой Рекомендуемые сводные таблицы, где предлагаются уже готовые варианты отчетов по вашим данным.
- В появившемся окне нам предлагается выбрать таблицу или диапазон данных и указать, куда следует поместить отчет. В нашем случае – на отдельный лист:
- Далее открывается базовый вид сводной таблицы: справа поля с данными и области для анализа, слева – непосредственно сводная таблица, которую нам предстоит заполнить. При этом в меню сверху добавляются две новые вкладки по работе со сводными таблицами: Анализ и Конструктор.
В область Столбцы помещают те поля, которые должны быть в заголовках таблицы. В Строках помещают те поля, которые будут отображаться в строках. Фильтр в сводной таблице позволяет выбирать значения тех или иных полей, по которым проводится отбор нужных данных. В области Значений размещаются показатели, которые мы суммируем или проводим другие операции.
Что мы можем выяснить с помощью сводных таблиц?
а) Например, нам интересно, как продавались те или иные модели велосипедов в зависимости от цвета. Для этого перетаскиваем поле Модель в область строк, поле Цвет – в область Колонок, а область значений задаем Моделью. В результате мы видим, что больше всего было продано красных велосипедов модели Road-150.
б) Далее попробуем узнать, какую маржу мы получаем с тех или иных моделей. Для этого оставляем в строках Модель и удаляем ненужные поля из других областей (перетаскивая мышкой). Затем перетаскиваем в область значений поля Модель (чтобы видеть количество проданных велосипедов), Стоимость и Цена. А значение маржинальной прибыли мы зададим не формулой, а полем. Для этого выберем вкладку Анализ – Поля, элементы и наборы – Вычисляемое поле.
В открывшемся окне вводим имя нового поля и формулу, по которой оно высчитывается: Цена – Стоимость. Нажимаем Добавить и новое поле появляется в общем списке, а также в самой таблице.
Мы можем сортировать данные в полученных столбцах, нажав по столбцу правой кнопкой мыши:
в) Следующий вопрос – какова средняя стоимость наших моделей? Оставим в областях Модель и Стоимость. И кликнем на стрелочку в поле Стоимость и нажмем на Параметры полей значений:
В открывшемся окне можно выбрать различные конфигурации данных по полю. Вместо суммы значений мы выберем Среднее и нажимаем ОК.
г) Как вычислить проценты с помощью сводных таблиц? Оставим в таблице модели велосипедов в области Строки и это же поле перетянем в область Значения. Мы видим, сколько единиц каждой модели было продано. Чтобы вычислить процент, щелкаем правой кнопкой мыши по значениям в столбце, выбираем Дополнительные вычисления – % от общей суммы.
Получаем значения процента от общего:
д) Наконец, мы можем быстро визуализировать полученные результаты. Для этого выбираем вкладку Анализ – Сводная диаграмма и желаемый тип графика.
Не забывайте нажимать кнопку Обновить во вкладке Анализ или внизу экрана, если данные в исходной таблице изменились.
В целом, чтобы быстро освоить работу со сводными таблицами, достаточно просто «поиграть» данными, перетаскивая столбцы в разные поля и оценивая получаемые отчеты и визуализации.
Как можно убедиться, сводные таблицы Excel упрощают и ускоряют процесс анализа данных. Они позволяют собирать важные цифры из большого набора данных или разрозненных документов; менять местами столбцы; быстро подсчитывать итог, проценты; настраивать визуализации и др.
