Сводная таблица в Excel. Как сделать?

Logo PDF шпаргалка с ТОП горячих клавиш в Excel Получить

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

Видеоурок: Как создать сводную таблицу в Excel

Что такое сводные таблицы в Excel? Пошаговая инструкция

Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.

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

Сводная таблица в Excel

Она содержит данные:

  • Даты заказов;
  • Регион в котором расположен клиент;
  • Тип клиента;
  • Клиент;
  • Количество продаж;
  • Выручка;
  • Прибыль.

Теперь, представим, что наш руководитель поставил задачу вычислить:

  • Какой объем выручки у региона Север за 2017 год?;
  • ТОП пять клиентов по выручке;
  • Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

Ниже мы разберем, как в решении этих задач нам поможет сводная таблица.

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

Для создания таблицы выполните следующие действия:

  • Выделите любую ячейку в таблице с данными;
  • Нажмите на вкладку «Вставка» => «Сводная таблица»:

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

  • Во всплывающем диалоговом окне система автоматически определит границы данных, на основе которых вы сможете создать сводную таблицу. Рекомендую при каждом создании убеждаться в том, что система правильно определила границы диапазона данных:
    • Таблица или диапазон: Система автоматически определяет границы данных. Они будут корректными при том условии, что в таблице нет пробелов в заголовках и строках. При необходимости вы можете скорректировать диапазон данных.
  • Система по умолчанию создает таблицу в новой вкладке файла Excel. Если вы хотите создать её в конкретном месте на определенном листе, то вы можете указать границы для создания в графе «На существующий лист».

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

  • Нажмите «ОК».

После нажатия кнопки «ОК» таблица будет создана.

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

созданная сводная таблица

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

Области сводной таблицы в Excel

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

Ниже вы узнаете подробней об областях:

  • Кэш
  • Область «Значения»
  • Область «Строки»
  • Область «Столбцы»
  • Область «Фильтры»

Что такое кэш сводной таблицы

При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.

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

Кэш данных увеличивает размер Excel-файла.

Область «Значения»

Область «Значения» включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области «Значения».

область значения в таблице Excel

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

Область «Строки»

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

область строки в сводных таблицах в Excel

Область»Столбцы»

Заголовки вверху значений таблицы называются «Столбцы».

На примере ниже красным выделены поля «Столбцы», в нашем случае это значения месяцев.

Область столбцы в Excel

Область «Фильтры»

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

область фильтры в excel

Сводные таблицы в Excel. Примеры

На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:

  • Какой объем выручки у региона Север за 2017 год?;
  • ТОП пять клиентов по выручке;
  • Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Прежде чем анализировать данные, важно решить каким образом должны выглядеть данные таблицы (какие данные разметить в колонки, строки, значения, фильтры). Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле «Значения». Как только вы представили каким образом вы видите итоговую таблицу — начинайте её создание.

В окне «Поля сводной таблицы» размещены области и поля со значениями для размещения:

поля и области сводной таблицы

Поля создаются на основе значений исходного диапазона данных. Раздел «Области» — это место, где вы размещаете элементы таблицы.

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

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

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

Пример 1. Какой объем выручки у региона Север?

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

  • создать сводную таблицу и поле «Регион» перенести в область «Строки»;
  • поле «Выручка» разместить в области «Значения»
  • задать финансовый числовой формат ячейкам со значениями.

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

сводные таблицы в Excel пример 1

Пример 2. ТОП пять клиентов по продажам

Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:

  • переместить поле «Клиент» в область «Строки»;
  • поле «Выручка» разместить в области «Значения»;
  • задать финансовый числовой формат ячейкам со значениями.

У нас получится следующая таблица:

сводные таблицы в Excel пример 2

По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:

  • кликните правой кнопкой на любой из строчек с данными выручки;
  • перейдите в меню «Сортировка» => «Сортировка по убыванию»:

сортировка данных в сводной таблице excel

Как результат мы получим отсортированный список клиентов по объему выручки.

сводные таблицы в Excel пример 2-1

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

Для этого:

  • поместим поле «Регион» в область «Строки»;
  • поместим поле «Клиент» в область «Строки» под поле «Регион»;
  • зададим финансовый числовой формат ячейкам со значениями.

После перемещения элемента «Регион» и «Клиент» в области «Строки» друг под другом , система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.

  • поле «Выручка» разместим в область «Значения».

В итоге мы получили таблицу, в которой отражены данные выручки клиентов в рамках каждого региона.

сводные таблицы в Excel пример 3

Для сортировки данных выполните следующие шаги:

  • кликните правой кнопкой на любой из строчек с данными выручки;
  • перейдите в меню «Сортировка» => «Сортировка по убыванию»:

сводные таблицы в Excel пример 3-1

В полученной таблице мы можем определить какое место занимает клиент Лудников ИП среди всех клиентов региона Восток.

сводные таблицы в Excel пример 3-2

Существует несколько вариантов для решения этой задачи. Вы можете перенести поле «Регион» в область «Фильтры» и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.

Еще больше полезных приемов в работе со сводными таблицами Excel вы узнаете в практическом курсе « Сводные таблицы в Excel«. Успей зарегистрироваться по ссылке!

VK Logo Больше лайфхаков в нашем ВК Подписаться
Оцени запись
Написать комментарий

  1. blank
    Роман

    Спасибо, всё доступно и понятно.

    Ответить

Подпишись на наш Telegram канал

с лайфхаками в Excel

blank