Как правильно составить сводную таблицу в excel

Сводные таблицы Excel – тема очень интересная и обширная.

В этой заметке я расскажу все, что вам нужно знать, для того чтобы начать применять сводные таблицы (в английском варианте – pivot table) в своей работе.

Сводные таблицы предоставляют очень широкие возможности для формирования нужных вам отчетов на основе каких-либо данных. При этом отчеты на базе сводных таблиц создаются буквально в несколько щелчков мыши и не требуют от пользователя создания сложнейших формул для группировки или суммирования необходимых данных.

Кроме этого, данные сводных таблиц очень просто визуализировать с помощью диаграмм или графиков, что с успехом применяется при создании так называемых дашбордов, которые сейчас очень широко применяются при анализе данных.

Постановка задачи

Итак, сводные таблицы могут быть применены к абсолютно любым данным, но чаще всего Excel применяют для анализа различных финансовых показателей компаний или проектов, поэтому давайте рассмотрим следующий пример (скачать файл).

Допустим вы трудитесь в компании, которая является поставщиком овощей и фруктов в сетевые супермаркеты, которые находятся в нескольких крупных городах страны.

По каждой поставке в базе есть информация, которую можно выгрузить в приблизительно вот такую таблицу:

Здесь в каждой строке мы видим дату заказа, название сети супермаркетов, город, в который осуществлялась поставка, категорию товара, его наименование, цену на дату поставки, количество заказов и итоговую сумму сделки.

Информации может быть намного больше. Для упрощения задачи я взял лишь минимальный набор данных. Тем не менее данных очень много и таблица состоит из нескольких тысяч строк.

Любую компанию в первую очередь интересует прибыль и поэтому может потребоваться найти ответы на ряд вопросов, например:

1. Определить, в каком из городов за прошедшее время выручка была максимальной.
2. Какая торговая сеть позволила получить компании наибольшую выручку.
3. Определить категорию товаров и конкретный товар, принесшие наибольшую выручку.

В таблице представлены данные за два года, поэтому приблизительно такие же вопросы могут возникнуть у руководства компании по отношению к какому-то конкретному временному интервалу, например, какой товар был наиболее востребованным прошлым летом, или в прошлом году, какова была динамика продаж товаров разных категорий в течении года (по месяцам, кварталам) или какой из заказчиков в прошлом месяце был для компании наиболее значимым. Также часто возникает необходимость определить лидеров продаж, например составить ТОП5 товаров, на которые был наибольший спрос в определенное время.

Решение задачи формулами

На первый взгляд все эти задачи легко решаются несложными формулами и стандартными функциями.

Так, например, для определения города, принесшего максимальную выручку, нужно лишь сложить сумму всех поставок по каждому из городов. Сделать это можно с помощью функции СУММЕСЛИ.

То есть нам нужно будет создать отдельную таблицу, в которую с помощью функции СУММЕСЛИ свести данные по каждому из городов.

Для начала нужно создать список уникальных значений. Для этого скопируем значение столбца с названиями городов (столбец С) и вставим их на новый лист. Далее с помощью удаления дубликатов оставим лишь уникальные значения.

Ну а теперь применим функцию СУММЕСЛИ.

Сначала укажем диапазон значений, в котором будем искать условие (это столбец с городами в исходной таблице), а затем зададим само условие. Нам нужно, чтобы в этой строке суммировались итоги по конкретному городу, поэтому указываем ячейку с его названием в новой таблице. Ну а теперь задаем диапазон, значения которого нужно суммировать в случае выполнения условия – это столбец с итогами.

Получаем выручку, полученную в конкретном городе. Растягиваем формулу на весь диапазон новой таблицы и получаем результат.

В итоге по значениям суммарной выручки мы легко определим победителя и ответим на первый вопрос.

Для решения остальных задач нужно будет также создавать отдельные таблицы и с помощью формул, которые могут быть довольно сложными, рассчитывать значения.

Подобный подход к формированию нужных отчетов весьма трудоемкий и требует не только времени на создание отчета, но и внимательности со стороны пользователя, ведь допустить ошибку в формуле при обработке огромного массива данных довольно просто. Ну а об аппетитах начальства рассказывать и вовсе нет смысла. Как только на стол ляжет ответ на первый вопрос сразу же появятся дополнительные и придется вновь корпеть над формулами и стягивать нужные данные в компактную табличку…

Это не наш метод, тем более что сводные таблицы позволяют сделать ровно тоже самое, но в разы быстрее.

Ответим на первый вопрос с помощью сводной таблицы.

Создание сводной таблицы в Excel

Сводная таблица всегда строится на основе некоторого массива данных, который должен иметь строку заголовков.

В моем примере мы имеем простой диапазон значений, но первая строка диапазона содержит заголовки столбцов, а значит и такой диапазон подойдёт для создания сводной таблицы.

Установим табличный курсор в любую ячейку диапазона и на вкладке Вставка выберем Сводная таблица.

Excel автоматически выберет весь неразрывный диапазон значений и появится окно создания сводной таблицы, где будет указана абсолютная ссылка на этот диапазон.

Абсолютным и относительным ссылкам уже было посвящено отдельное подробное видео, поэтому не буду на этом останавливаться. Упомяну лишь, что в случае использования такого фиксированного диапазона в качестве источника данных сводной таблицы, мы в итоге не сможем добавлять в нее новую информацию. Точнее сказать, если в исходной таблице появятся новые строки, то для того, чтобы информация из них появилась в сводной таблице нам придется вручную корректировать в ее настройках этот диапазон.

Фактически данную проблему полностью решают умные таблицы. Поэтому перед тем, как создать новую сводную таблицу стоит преобразовать исходные данные в умную таблицу.

Делается это очень просто – также устанавливаем табличный курсор в любую ячейку диапазона и либо на вкладке Вставка выбираем Таблица, либо просто нажимаем сочетание клавиш Ctrl+T.

Так как строка с заголовками уже присутствует в диапазоне, то соответствующую галочку не убираем.

Ну и также, как и раньше создадим сводную таблицу, но уже на основе умной таблицы.

Теперь в источнике данных уже указан не фиксированный диапазон на листе, а отдельный объект Таблица1. Это нам гарантирует, что новые данные будут автоматически добавляться в сводную таблицу при ее обновлении.

Вставим сводную таблицу на новый лист.

На новом листе появится подсказка, подсказывающая нам, что это лист сводной таблицы, а также в правой части окна появится панель инструментов, позволяющая сконструировать нужный нам отчет.

Эту панель инструментов условно можно разделить на две части. В верхней находится перечень, так называемых полей. Можно легко убедиться, что названия полей соответствуют названиям заголовков исходной таблицы. То есть по названию поля мы можем легко понять, какой именно столбец с данными за ним стоит.

В нижней части расположены четыре области, которые относятся к четырём конструктивным элементам сводной таблицы. В зависимости от того, в какую область мы перетащим то или иное поле, его данные будут выводиться в той или иной части сводной таблицы.

Например, нам нужно ответить на вопрос – поставки товаров в какой город позволили получить максимальную выручку?

То есть в первую очередь нам нужно получить список городов. Для этого захватываю мышью поле Город и перетягиваем его в область Строки. Мы сразу получаем список названий всех городов из столбца Город исходной таблицы.

То есть область Строки позволят разместить данные в строках.

Если мы перетянем данное поле в область Столбцы, то получим тот же список, но уже в одной строке, то есть каждое название стало заголовком отдельного столбца.

Верну поле в Строки и завершу создание первой сводной таблицы. Нам ведь нужно узнать суммарную выручку по городам, поэтому просто перетягиваем поле Итого в Значения. Получаем точно такую же табличку, как и раньше, но буквально в несколько щелчков мыши.

Пока не будем обращать внимание на внешний вид сводной таблицы, а сосредоточимся на ее функциональности.

Обратите внимание на то, что в области Строки фигурирует имя поля, а в области Значения находится фраза «Сумма по полю Итого». Эта же фраза подставлена в заголовок соответствующего столбца сводной таблицы. Она указывает на то, что при формировании значений столбца сводной таблицы производилось суммирование значений столбца Итого умной таблицы.

Если щелкнуть мышью по маленькому черному треугольнику и в меню выбрать Параметры полей значений, то появится окно, в котором доступны все возможные операции. Чаще всего приходится применять суммирование или подсчет количества значений.

Так как в столбце Итого в исходной таблицы у нас находятся числовые значения, то Эксель автоматически выбрал суммирование при перетягивании поля в область. Если же в столбце находится текст, то по умолчанию будет выбрано количество.

Так в столбце Заказчик умной таблицы находятся наименования торговых сетей, то если я перетяну их в область Значения мы увидим количество по этому полю. Фактически это значение показывает, сколько было поставок в том или ином городе, то есть сколько сделок было совершено.

Ну а теперь давайте создадим еще одну сводную таблицу, которая ответит на второй вопрос – какая торговая сеть позволила получить компании наибольшую выручку?

Переключимся на лист с исходными данными и точно также создадим еще одну сводную таблицу на новом листе.

В первую очередь нам нужен список торговых сетей, поэтому перетянем поле Заказчик в область Строки. Ну а поле Итого в Значения. Все готово!

Ну и последняя задача – определить категорию товаров, принесшую наибольшую выручку.

Действием по аналогии.

Можно сделать отчет более информативным, если в область Строки перенести еще и Товар. Тогда мы сможем получить информацию не только по отдельным категориям товаров, но и по товарам внутри категории.

При этом важно соблюдать “вложенность” полей. То есть у нас товары принадлежат категориям, а не наоборот. Этот порядок задается последовательностью полей в области. Если сейчас изменить их очередность, то получим следующее – появится список всех товаров, а вложенной информацией станет их принадлежность к какой-либо товарной категории.

В данном случае это крайне не информативно, поэтому верну все как было.

Но не стоит забывать и про область Столбцы. Для примера перетянем в нее поле Заказчик. Мы получим подробный отчет по объемам заказов каждой категории товаров отдельными торговыми сетями. При этом каждую категорию можно раскрыть, чтобы увидеть детализацию по каждому товару и сети.

На первый взгляд формирование сводной таблицы с помощью полей может показаться довольно сложным и непредсказуемым, но небольшая практика быстро расставит все на свои места и позволит вам сходу ориентироваться в нужных областях при создании отчетов.

Итак, у нас есть отчеты, отвечающие на поставленные задачи. Осталось лишь немного отформатировать данные в таблицах, сделав их более приятными для восприятия.

Форматирования сводной таблицы

В первую очередь поговорим о заголовках. Именно их хочется сразу изменить, но тут есть один нюанс, который стоит учитывать.

Заголовок изменяется самым обычным образом – щелкаем по ячейке с ним и затем меняем текст.

Также можно выделить нужную ячейку с заголовком и нажать клавишу F2 для перехода в режим редактирования ее содержимого.

При этом важно учитывать, что в сводной таблице название заголовка не может быть таким же, как и название поля. То есть если я захочу переименовать «Сумма по полю Итого» в «Итого», то ничего не выйдет и появится ошибка.

Правда этот нюанс можно обойти. Если добавить в конце слова пробел, то для Эксель это будет уже другое значение, а пользователь разницы не увидит.

Я же просто переименую поля в «Сумма заказов» и «Количество заказов». Первый заголовок также можно изменить на «Город».

Осталось отформатировать сами значения. В первую очередь изменим числовой формат, сделав его денежным. При этом сразу же приходит на ум воспользоваться соответствующим инструментом со вкладки Главная.

Однако, если выделенным будет только одна ячейка столбца, то и форматирования затронет только ее. В данном случае правильнее будет изменить числовой формат для всего столбца и для этого достаточно из контекстного меню, вызванного щелчком правой кнопки мышки на любой из ячеек столбца, выбрать пункт Числовой формат.

Затем в появившемся окне указываем нужный формат и задаем его параметры.

Форматирование будет применено сразу ко всем столбцу.

Ну а также на контекстной вкладке Конструктор, которая появляется только при выделении сводной таблицы, можно задать стиль оформления таблицы целиком. Для этого нужно либо выбрать одну из готовых цветовых схем, либо можно создать свой вариант стилевого оформления, задав форматирования для каждого элемента сводной таблицы индивидуально.

Общие и промежуточные итоги

И уж если речь зашла о контекстной вкладке Конструктор, то стоит сразу сказать и о настройках сводной таблицы, связанных с ее макетом.

Макет определяет, в какой части сводной таблицы будет выводиться тот или иной ее элемент, то есть определяет ее структуру. Кроме данных, которые автоматически подтягиваются в сводную таблицу из исходной, сама сводная таблица формирует общие и промежуточные итоги по каждому столбцу и строке.

Расположением и видимостью общих и промежуточных итогов мы также можем управлять. Для этого есть соответствующие инструменты на контекстной вкладке Конструктор.

Промежуточные итоги в моем примере формируются суммами по каждой категории товаров и по умолчанию выводятся в строке с наименованием категории, то есть в заголовке группы.

То есть если просуммировать значения по каждому товару, то мы получим значение, указанное в промежуточных итогах.

Далеко не всегда это значение нужно выводить. Так при раскрытом списке оно скорее создает путаницу, если не знать, что именно оно означает. В таком случае можно отключить промежуточные итоги, выбрав соответствующую опцию.

Тогда промежуточные итоги будут выводиться только в случае свернутой категории, когда данные по отдельным товарам не отображаются.

Также можно выводить промежуточные итоги отдельной строкой в нижней части каждой категории товаров (второй пункт меню). Опять же, промежуточные итоги будут отображаться в свернутом виде в основной строке, а при развернутой категории смещаться отдельной строкой ниже.

Общие итоги также формируются автоматически по каждой строке и столбцу и далеко не всегда они необходимы. В соответствующем меню мы можем полностью отключить вывод общих итогов в сводной таблице, либо оставить итоги только по столбцу или только строке.

Макет сводной таблицы

Ну и выбор макета также влияет на внешний вид сводной таблицы. Есть три варианта.

Первый – сжатая форма. Этот вариант по умолчанию и мы его видим сразу после создания сводной таблицы.

При выборе второго варианта – форма структуры, в сводной таблице под каждое поле будет выделен отдельный столбец. То есть в первом столбце теперь выводится только категория товара, а сами товары отображаются во втором столбце.

Табличная форма аналогична форме структуры, но промежуточные итоги из строки с названием категории перемещаются вниз.

В этом же меню есть еще одна настройка, позволяющая повторять или не повторять подписи элементов.

Сейчас категория отображается только в одной строке и это вариант с не повторяющимися подписями. Если выбрать второй вариант, то название категории будет дублироваться в каждой строке.

Ну а теперь со знанием дела приведем отчет к нужному виду – вернем сводной таблице сжатую форму, а затем перенесем промежуточные итоги вниз каждой категории.

С помощью соответствующего инструмента вставим пустые строки после каждой категории, чтобы визуально их отделить друг от друга.

Ну а чтобы быстро свернуть или развернуть все категории можно воспользоваться контекстным меню, вызванным щелчком правой кнопки мыши на соответствующей ячейке. Здесь есть раздел, в котором выбираем нужный вариант.

Ну а если кнопки свертывания не нужны, то можно их скрыть. Для этого на контекстной вкладке Анализ отключим их отображение.

Подкорректируем заголовки, выберем подходящий стиль и наш отчет готов.

Сортировка и фильтрация

Скорее всего вы уже обратили внимание на то, что в сводной таблице есть две ячейки с кнопками.

По щелчку мыши на них появляется меню с возможностью фильтрации и сортировки данных. Эти инструменты относятся к заголовкам строк и, соответственно, столбцов.

Если нужно сформировать отчет только по какой-то одной товарной категории (например, “Зелень”), то с помощью фильтра отключаем все ненужные и получаем результат:

То же самое касается и заказчиков. То есть мы можем сократить отчет только до нужной категории товаров, заказанных определенной торговой сетью.

Если кроме категории нужно отфильтровать данные еще и по конкретным товарам, то в меню в выпадающем списке указываем соответствующее поле, а затем делаем фильтрацию по нему.

При применении сортировки или фильтрации значок на кнопке изменяется. По нему можно однозначно определить, что данные в столбце или строке отфильтрованы или отсортированы.

Чтобы удалить фильтры достаточно выбрать соответствующий пункт в меню, однако в случае с вложенными полями удаление фильтра касается только выбранного в выпадающем списке. То есть если фильтрация была произведена по нескольким полям, то для ее удаления нужно будет сначала переключиться на соответствующее поле.

Кроме стандартных возможностей фильтрации мы можем настроить фильтр по произвольному полю. Для этого есть отдельная область, которая так и называется Фильтры.

Сейчас мы построили отчет, дающий полное представление об объемах заказов со стороны торговых сетей, но вот как дела обстоят по отдельным городам?

Перетаскиваем соответствующее поле в область Фильтр и над сводной таблицей появляется соответствующий выпадающий список.

Мы можем выбрать отдельный город, чтобы получить информацию только по нему.

Что же касается сортировки, то в выпадающем меню есть стандартные инструменты, позволяющие отсортировать заголовки строк или столбцов в алфавитном порядке.

Однако намного удобнее пользоваться контекстным меню. Например одной из первых задач у нас было определить, в каком из городов за прошедшее время выручка была максимальной. Мы получили результат в виде данных по всем городам, но чтобы быстро определить нужное значение необходимо отсортировать значения по возрастанию или убыванию. Вызываем контекстное меню на любой ячейке столбца и выбираем нужный вариант.

Аналогично можно отсортировать данные по любому полю или итогам. Просто вызываем контекстное меню на соответствующей ячейке и выбираем нужное направление сортировки.

Ну и затронув тему фильтрации нельзя обойти стороной так называемые срезы.

Срезы в сводных таблицах

Срез – это тот же фильтр, но интерактивный.

При вставке среза мы также выбираем поле, по которому фильтр будет работать. Например, вставим два среза – по городам и товарам.

Если в ранее вставленном нами фильтре нужно выбирать нужные объекты из списка, то в срезе достаточно щелкнуть мышью по нужному пункту. При этом обратите внимание на то, что срез по городам и ранее вставленный вручную фильтр работают синхронно, то есть полностью дублируют друг друга.

Таким образом выбирая нужные значения в срезах в пару щелчков мыши мы можем изменять отчет, выводя в нем только нужную информацию.

Для выделения нескольких пунктов подряд достаточно выбирать их удерживая нажатой левую кнопку мыши. Если же нужно выбрать несколько несмежных значений, то в окне каждого среза есть соответствующая кнопка. Также как и для очистки фильтров.

Даты в сводных таблицах

Ну и последняя важная тема – это даты. Пока мы вообще не трогали поле Дата, но сводные таблицы позволяют очень гибко выводить информацию, связанную с датами и сейчас я это продемонстрирую.

Создадим еще одну сводную таблицу, в которой выведем выручку за все время.

В исходной таблице указывалась конкретная дата каждой сделки, а сводная таблица автоматически сгруппировала даты при этом не только по годам, но и по кварталам и месяцам. При этом в области Строки поле Дата было автоматически преобразовано в три – Годы, Кварталы и Дата.

Если такая группировка не нужна, то можно ее отменить через контекстное меню.

Также с помощью контекстного меню можно вернуть группировку (пункт Группировать), указав необходимые группы. Здесь можно выбрать сразу несколько, например, месяцы и года.

Сортировка и фильтрация по датам работает также, как и с другими данными. Например, можно отключить какой-то временной период.

Для дат существует свой формат срезов – временная шкала.

Она также в интерактивном режиме позволяет выбирать только интересующие вас временные интервалы.

Таким образом на базе сводной таблицы можно создать интерактивный отчет, в котором с помощью срезов и временной шкалы можно очень тонко фильтровать данные. Ну а преобразовав данные сводной таблицы в диаграммы или графики можно получить отличный дашборд, с помощью которого легко можно анализировать или демонстрировать информацию.

Ну а сводные таблицы – это очень обширная и увлекательная тема, которой я посвятил отдельный очень подробный видеокурс, который так и называется “Сводные таблицы“.

Нажмите на эту ссылку, чтобы перейти на страницу курса >>

________________________________________

Ссылки на мои ресурсы по Excel

★ YouTube-канал Excel Master

★ Серия видеокурсов “Microsoft Excel Шаг за Шагом”

★ Авторские книги и курсы