Сводная таблица в 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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Подпишись на новые статьи

от ExcelHack.Ru

Подписка оформлена!