Размер шрифта:
Сводная таблица в Excel. Как сделать?
Play

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Выделите любую ячейку в таблице с данными;
  • Нажмите на вкладку “Вставка” => “Сводная таблица”:
  • Во всплывающем диалоговом окне система автоматически определит границы данных, на основе которых вы сможете создать сводную таблицу. Рекомендую при каждом создании убеждаться в том, что система правильно определила границы диапазона данных:
    • Таблица или диапазон: Система автоматически определяет границы данных. Они будут корректными при том условии, что в таблице нет пробелов в заголовках и строках. При необходимости вы можете скорректировать диапазон данных.
    • Нажмите “ОК”.

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

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

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

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

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

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

    • Кэш
    • Область “Значения”
    • Область “Строки”
    • Область “Столбцы”
    • Область “Фильтры”
    Что такое кэш сводной таблицы

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

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

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

    Область “Значения”

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

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

    Область “Строки”

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

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

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

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

    Область “Фильтры”

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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