Сводная таблица — это один из наиболее полезных инструментов в Excel. С ее помощью появляются широкие возможности для анализа больших массивов данных и быстрых вычислений.
- Видеоурок: Как создать сводную таблицу в Excel
- Что такое сводные таблицы в Excel? Пошаговая инструкция
- Как сделать сводную таблицу в Excel
- Области сводной таблицы в Excel
- Что такое кэш сводной таблицы
- Область «Значения»
- Область «Строки»
- Область»Столбцы»
- Область «Фильтры»
- Сводные таблицы в Excel. Примеры
- Пример 1. Какой объем выручки у региона Север?
- Пример 2. ТОП пять клиентов по продажам
- Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?
Видеоурок: Как создать сводную таблицу в Excel
Что такое сводные таблицы в Excel? Пошаговая инструкция
Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.
Представим, что у нас есть таблица с данными продаж по клиентам за год размером в 1000 строчек:
Она содержит данные:
- Даты заказов;
- Регион в котором расположен клиент;
- Тип клиента;
- Клиент;
- Количество продаж;
- Выручка;
- Прибыль.
Теперь, представим, что наш руководитель поставил задачу вычислить:
- Какой объем выручки у региона Север за 2017 год?;
- ТОП пять клиентов по выручке;
- Какое место по выручке занимает клиент Лудников ИП в регионе Восток?
Для поиска ответа на эти вопросы вы можете использовать различные функции и формулы. Но что, если задач по этим данным будет не три, а тридцать? Каждый раз вам придется менять формулы и функции и подстраивать под каждый тип расчета.
Ниже мы разберем, как в решении этих задач нам поможет сводная таблица.
Как сделать сводную таблицу в Excel
Для создания таблицы выполните следующие действия:
- Выделите любую ячейку в таблице с данными;
- Нажмите на вкладку «Вставка» => «Сводная таблица»:
- Во всплывающем диалоговом окне система автоматически определит границы данных, на основе которых вы сможете создать сводную таблицу. Рекомендую при каждом создании убеждаться в том, что система правильно определила границы диапазона данных:
- Таблица или диапазон: Система автоматически определяет границы данных. Они будут корректными при том условии, что в таблице нет пробелов в заголовках и строках. При необходимости вы можете скорректировать диапазон данных.
- Система по умолчанию создает таблицу в новой вкладке файла Excel. Если вы хотите создать её в конкретном месте на определенном листе, то вы можете указать границы для создания в графе «На существующий лист».
- Нажмите «ОК».
После нажатия кнопки «ОК» таблица будет создана.
После формирования таблицы, вы не увидите на листе никаких данных. Все что будет доступно, это ее имя и меню для выбора данных к отображению.
Теперь, прежде чем мы приступи к анализу данных, предлагаю разобраться что значит каждое поле и область сводной таблицы.
Области сводной таблицы в Excel
Для эффективной работы со сводными таблицами, важно знать принцип их работы.
Ниже вы узнаете подробней об областях:
- Кэш
- Область «Значения»
- Область «Строки»
- Область «Столбцы»
- Область «Фильтры»
Что такое кэш сводной таблицы
При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.
Когда вы осуществляете вычисления, Excel не обращается каждый раз к исходным данным, а использует информацию из кэша. Эта особенность значительно сокращает количество ресурсов системы, затрачиваемых на обработку и вычисления данных.
Кэш данных увеличивает размер Excel-файла.
Область «Значения»
Область «Значения» включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области «Значения».
На примере выше создана таблица, в которой отражены данные продаж по регионам с разбивкой по месяцам.
Область «Строки»
Заголовки таблицы, размещенные слева от значений, называются строками. В нашем примере это названия регионов. На скриншоте ниже, строки выделены красным цветом:
Область»Столбцы»
Заголовки вверху значений таблицы называются «Столбцы».
На примере ниже красным выделены поля «Столбцы», в нашем случае это значения месяцев.
Область «Фильтры»
Область «Фильтры» используется опционально и позволяет задать уровень детализации данных. Например, мы можем в качестве фильтра указать данные «Тип клиента» — «Продуктовый магазин» и Excel отобразит данные в таблице касающиеся только продуктовых магазинов.
Сводные таблицы в Excel. Примеры
На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:
- Какой объем выручки у региона Север за 2017 год?;
- ТОП пять клиентов по выручке;
- Какое место по выручке занимает клиент Лудников ИП в регионе Восток?
Прежде чем анализировать данные, важно решить каким образом должны выглядеть данные таблицы (какие данные разметить в колонки, строки, значения, фильтры). Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле «Значения». Как только вы представили каким образом вы видите итоговую таблицу — начинайте её создание.
В окне «Поля сводной таблицы» размещены области и поля со значениями для размещения:
Поля создаются на основе значений исходного диапазона данных. Раздел «Области» — это место, где вы размещаете элементы таблицы.
Перенос полей из области в область представляет собой удобный интерфейс, в котором, при перемещении, данные автоматически обновляются.
Теперь, попробуем ответить на вопросы руководителя из начала этой статьи на примерах ниже.
Пример 1. Какой объем выручки у региона Север?
Для вычисления объема продаж региона Север, рекомендую разместить в таблице данные продаж по всем регионам. Для этого нам потребуется:
- создать сводную таблицу и поле «Регион» перенести в область «Строки»;
- поле «Выручка» разместить в области «Значения»
- задать финансовый числовой формат ячейкам со значениями.
Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:
Пример 2. ТОП пять клиентов по продажам
Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:
- переместить поле «Клиент» в область «Строки»;
- поле «Выручка» разместить в области «Значения»;
- задать финансовый числовой формат ячейкам со значениями.
У нас получится следующая таблица:
По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:
- кликните правой кнопкой на любой из строчек с данными выручки;
- перейдите в меню «Сортировка» => «Сортировка по убыванию»:
Как результат мы получим отсортированный список клиентов по объему выручки.
Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?
Для расчета места по объему выручки клиента Лудников ИП в регионе Восток рекомендую сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.
Для этого:
- поместим поле «Регион» в область «Строки»;
- поместим поле «Клиент» в область «Строки» под поле «Регион»;
- зададим финансовый числовой формат ячейкам со значениями.
После перемещения элемента «Регион» и «Клиент» в области «Строки» друг под другом , система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.
- поле «Выручка» разместим в область «Значения».
В итоге мы получили таблицу, в которой отражены данные выручки клиентов в рамках каждого региона.
Для сортировки данных выполните следующие шаги:
- кликните правой кнопкой на любой из строчек с данными выручки;
- перейдите в меню «Сортировка» => «Сортировка по убыванию»:
В полученной таблице мы можем определить какое место занимает клиент Лудников ИП среди всех клиентов региона Восток.
Существует несколько вариантов для решения этой задачи. Вы можете перенести поле «Регион» в область «Фильтры» и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.
Еще больше полезных приемов в работе со сводными таблицами Excel вы узнаете в практическом курсе « Сводные таблицы в Excel«. Успей зарегистрироваться по ссылке!
Спасибо, всё доступно и понятно.