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

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

Что такое сводные таблицы в Excel? Cводные таблицы в Excel для чайников

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

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

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

В таблице указаны:

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

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

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

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

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

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

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

Для того чтобы создать сводную таблицу в 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

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

comments powered by HyperComments
Понравилась статья? Поделиться с друзьями: