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

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

 

 

Что такое сводная таблица

 

Сводная таблица в Excel (или pivit table, или “пивотная таблица”) — это особым образом организованная и структурированная таблица, которая выводит данные в нужном пользователю разрезе и производит необходимые вычисления без использования формул.

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

 

 

Предположим, нужна информация о сумме выручки в разрезе категории товара. Или по месяцам. Конечно, можно взять все категории товара и функцией СУММЕСЛИМН вытянуть нужные данные, но это займет намного больше времени. К тому же, если у вас завтра появится новая категория, ее нужно будет вручную добавить в таблицу (не забыть об этом, как минимум). Если же построить сводную таблицу в excel, они это сделает это за вас.

 

 

Подготавливаем данные для построения сводной таблицы

 

Давайте разберемся, из какой исходной таблицы можно построить сводную таблицу в excel (спойлер: не каждая таблица годится).

Основные требования к таблице, из которой будет создаваться сводная таблица:

• Все столбцы в исходной таблице должны иметь уникальные (т.е. не повторяющиеся) заголовки.
• Все строки в пределах одного столбца должны иметь однородные данные. Например, если в столбце указан месяц в формате “янв”, то не должно быть данных в формате “Январь” — эксель сочтет их как два разных месяца. Также использование русских и похожих латинских букв (например С и C на глаз не отличишь, но одна из них в латинской раскладке, и эксель также посчитает их разными).
• Числовые данные, которым вы будете оперировать, должны быть именно числовыми. Если есть зеленый треугольник в верхнем левом углу ячейки — такое число является текстом, и должно быть преобразовано в число, иначе с ним нельзя делать вычисления.

 

• Исходная таблица не должна содержать промежуточных итогов.
• Нежелательно иметь пустые строки 
• Под исходной таблицей нежелательно иметь какие-то другие данные, оставьте это пространство пустым, это облегчит дальнейшее обновление сводной таблицы.

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

 

 

Как построить сводную таблицу в Excel из обычной таблицы

 

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

 

 

Рассматривать будем на примере обычной таблицы.

Если мы не предполагаем в дальнейшем добавлять в исходную таблицу данные, то можно выделить только диапазон этой таблицы, например, А1:Н202. Однако, если в исходную таблицу будут добавляться данные, лучше выделить полностью столбцы, входящие в таблицу. Иначе есть риск, что при обновлении сводной таблицы новые данные в нее не попадут.

Выделяем полностью столбцы, зажав левую кнопку мыши.

Далее вкладка Вставка — Сводная таблица.

 

 

Появится окно создания сводной таблицы. Разберем, что в нем:

• Выберите данные для анализа. Здесь уже указан диапазон, который вы выделили, не меняйте его.
• Укажите, куда следует поместить отчет сводной таблицы. Новичкам (и не только) советую всегда выбирать Новый лист. Если выбираете “на существующий лист”, то нужно указать ячейку на уже созданном листе, с которой будет начинаться сводная таблица. Только учтите, что пространство нужно выбрать с запасом, чтобы потом сводная таблица не “съела” данные на листе.

 

Выберем На новый лист и нажмем ОК.

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

 

Как можно добавлять поля из исходной таблицы в сводную.

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

• перетаскивая мышкой поля в нужный раздел. Такой способ рекомендуется.

Перетащим поле Категория в блок СТРОКИ, поле Месяц — в блок СТОЛБЦЫ, а поле Стоимость — в блок ЗНАЧЕНИЯ. 

 

 

Обратите внимание!

Возле слова Количество не случайно указан знак вопроса. В большинстве версий excel по умолчанию считает в значениях именно количество элементов. По невнимательности можно оставить так и получить кривой результат.

Нам же нужна именно сумма по полю Стоимость. Нажмем на “треугольник” и выберем Параметры полей значений, и далее — Сумма.

 

 

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

 

 

 

 

Где найти панель редактирования сводной таблицы

 

Вы создали сводную таблицу и закрыли списки полей (так обычно и делают, чтобы не мешали просмотру), но возникла необходимость внести корректировки в макет сводной таблицы.

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

 

 

 

Как изменить структуру сводной таблицы

 

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

Получаем результат. 

 

 

 

Как удалить поле сводной таблицы

 

Теперь нам больше не нужно видеть развертку по наименованию товара в строках. 

Для того, чтобы удалить поле из сводной таблицы, есть 3 основных способа:

• снять галочку с названия поля, оно автоматически исчезнет
• в блоке, где располагается поле, щелкнуть на “треугольник” и выбрать Удалить поле.

• в самой сводной таблице щелкнуть правой кнопкой мыши на любом значении поля и выбрать Удалить “Название поля”

 

 

Для чего нужно поле Фильтр в сводной таблице

 

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

Перестроим таблицу таким образом: в строки добавим Наименование товаров, в столбцы — месяцы.

 

 

Предположим, нужно видеть не все названия товаров, а только по категории Инструмент.

Для этого нужно перетащить поле Категория товара в блок ФИЛЬТРЫ.

 

 

После этого над сводной таблицей появляется дополнительная строка с фильтрами.

 

 

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

 

 

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

 

 

К томе же, полей, по которым фильтруем данные, может быть несколько. Чтобы отфильтровать данные в сводной таблице не только по категории товара, но и по другим полям, их также нужно перетянуть в блок ФИЛЬТРЫ.

 

 

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

Если исходная таблица с данными периодически пополняется новыми строками, то потребуется обновлять сводную таблицу.

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

 

 

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

 

 

 

Как изменить внешний вид (формат) сводной таблицы

 

Про настройку форматирования сводной таблицы есть видеоролик:

 

В статье кратко остановимся на возможностях.

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

 

 

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

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

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

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

 

 

 

Значение (пусто) в сводной таблице — как убрать

 

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

 

 

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

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

 

 

В этом случае Пусто исчезнет и для строк, и для столбцов одновременно.

 

 

Где изменить источник сводной таблицы

 

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

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

Для этого щелкнем в любом месте сводной таблицы и далее идем на вкладку Анализ — Источник данных.

 

 

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

 

 

Далее необходимо в открывшемся окне в поле Таблица или диапазон указать новый диапазон для сводной таблицы: просто выделить необходимый диапазон левой кнопкой мыши (полностью диапазон, включая новый столбец, а не только новый столбец). Нажимаете Ок.

Вернувшись на лист сводной таблицы, вы увидите, что новое поле Продавец появилось в списке полей. Теперь его можно перетаскивать в блоки СТРОКИ, СТОЛБЦЫ или ФИЛЬТРЫ.

 

 

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

 

Чтобы скопировать сводную таблицу, необходимо выделить ее полностью, вместе со строками фильтров. Далее копировать как обычно (Ctrl+C или кнопка Копировать) и вставить в нужное место (Ctrl + V или кнопка Вставить).

 

 

Удаляем сводную таблицу

 

Если сводная таблица расположена на отдельном листе, то чтобы удалить сводную таблицу, можно удалить целиком весь лист (правой кнопкой мыши на ярлычке листа и выбрать Удалить).

Если сводная таблица расположена на листе, где есть другие данные, самый простой способ ее удалить — выделить полностью сводную таблицу вместе с фильтрами (если они есть) и нажать кнопку Delete.

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

 

---

   Сообщество Excel Analytics | обучение Excel

    Канал на Яндекс.Дзен 

---