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

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

 

 

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

 

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

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

 

сводные таблицы в excel

 

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

 

 

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

 

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

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

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

сводные таблицы в excel

 

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

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

 

 

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

 

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

 

сводные таблицы в excel

 

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

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

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

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

 

сводные таблицы в excel

 

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

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

сводные таблицы в excel

 

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

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

сводные таблицы в excel

 

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

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

сводные таблицы в excel

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

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

 

сводные таблицы в excel

 

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

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

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

 

сводные таблицы в excel
сводные таблицы в excel

 

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

 

сводные таблицы в excel

 

 

 

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

 

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

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

 

сводные таблицы в excel

 

 

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

 

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

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

 

сводные таблицы в excel

 

 

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

 

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

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

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

сводные таблицы в excel

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

сводные таблицы в excel

 

 

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

 

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

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

 

сводные таблицы в excel

 

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

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

 

сводные таблицы в excel

 

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

 

сводные таблицы в excel

 

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

 

сводные таблицы в excel
сводные таблицы в excel

 

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

 

сводные таблицы в excel

 

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

 

 

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


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

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

 

сводные таблицы в excel

 

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

 

сводные таблицы в excel
сводные таблицы в excel

 

 

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

 

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

 

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

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

 

сводные таблицы в excel

 

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

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

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

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

 

 

 

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

 

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

 

сводные таблицы в excel

 

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

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

 

сводные таблицы в excel

 

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

Обратите внимание! Если вы убираете значение Пусто, то есть следующая опасность: при добавлении новых значений в исходную таблицу (например, появится новая категория товара) они не появятся автоматически при обновлении сводной таблицы. Их нужно будет добавить вручную, установив “галочку” (там же, где снимали “галочку” с Пусто).

 

 

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

 

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

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

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

 

сводные таблицы в excel

 

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

 

сводные таблицы в excel

 

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

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

 

 

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

 

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

 

 

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

 

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

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

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

 


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

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