Сводные таблицы в Excel предназначены для быстрого группирования больших объемов данных для дальнейшего анализа и составления отчетов. Инструмент работает по принципу конструктора, когда одни и те же данные могут быть получены из существующей таблицы под другим углом зрения и в разных «секциях» другой таблицы. Итак, давайте посмотрим, как строятся сводные таблицы в Excel.
Классический способ составления сводной таблицы
Например, рассмотрим последовательность шагов для создания сводных таблиц в Microsoft Excel 2019. Та же логика может применяться в более ранних версиях программы.
За исходную (базовую) таблицу возьмем продажи по данным продаж. В таблице содержится информация о наименовании товара, области, виде спорта, а также основные экономические показатели, такие как количество проданных единиц, цена и количество в рублях. Кроме того, если для одного и того же продукта было осуществлено несколько продаж, то будет несколько строк.
Для правильного формирования сводной таблицы всегда очень важно представить, каким должен быть конечный результат. Другими словами, вам нужно понимать, какую информацию мы хотим обрабатывать.
В этом примере цель сводной таблицы — выяснить, сколько женских теннисных туфель с разбивкой по наименованию продукта мы продали. Кроме того, сводная таблица должна отражать общую сумму для конкретного элемента. Кроме того, если, например, кроссовки того или иного размера были проданы более одного раза, и данные о них распределены по нескольким строкам, то в результате нам нужно увидеть общий объем одной строки.
Шаг 1. Создайте умную таблицу
Как только мы сделаем нашу базовую таблицу «умной» (или динамической, т.е она может автоматически изменяться), при добавлении новых строк с данными они автоматически появятся в сводной таблице.
- Сначала щелкните любую ячейку в таблице. Затем перейдите на вкладку «Главная», найдите раздел «Стили» и нажмите в нем функцию «Форматировать как таблицу». В открывшемся списке определяем желаемый стиль (цвета, наличие или отсутствие нарисованных границ) и кликаем по нему.
- Откроется окно, в котором нужно указать координаты таблицы. Чаще всего область уже выделена, и нам просто нужно нажать кнопку ОК. Но будет полезно убедиться, что все правильно. У нас также есть возможность изменить диапазон таблицы (при необходимости). И не забудьте поставить галочку напротив опции «Таблица с заголовками”.
- У нас есть «умная таблица», которая «растягивается» по мере добавления новой информации. Программа автоматически присвоит ей имя и перейдет на вкладку «Конструктор», где мы сможем внести изменения в созданную таблицу, в том числе изменить ее имя.
Примечание. Преобразование простой таблицы в интеллектуальную не является обязательным и при желании может быть пропущено. В этом случае после каждой корректировки исходной таблицы сводную таблицу необходимо создавать заново или обновлять с помощью метода, описанного в конце статьи.
Шаг 2. Сформируйте сводную таблицу
- Переключитесь на вкладку «Вставка» в левом углу, нажмите кнопку «Таблицы» и выберите «Сводная таблица» из появившегося списка опций”.
- Откроется окно для создания сводной таблицы, в котором уже указан диапазон начальных значений (в нашем случае автоматически выбирается «Таблица2», которую мы создали на шаге 1). При необходимости диапазон можно изменить или указать Имя таблицы, из которой вы хотите собирать сводку.
- В том же окне выберите, где разместить новую таблицу. Есть два варианта: на новом листе или на существующем. При прочих равных, первый вариант имеет больший приоритет, но окончательный выбор остается за пользователем. После того, как вы определились со своим выбором и поставили желаемую отметку (в нашем примере на новом листе), нажмите ОК.
- После выполненных действий на отдельном листе будет создана форма сводной таблицы. Справа находится окно полей сводной таблицы. В верхней половине непосредственно список самих полей (соответствует названиям столбцов исходной таблицы), в нижней половине — области, доступные для настройки:
- Фильтр
- Столбцы
- Струны
- Ценности
- Левой кнопкой мыши перемещаем необходимые поля сверху в область снизу, тем самым настраивая таблицу, подходящую для наших задач. Не существует единого правила, как правильно выполнять все настройки, поскольку у всех пользователей разные исходные табличные данные и конечные цели. В нашем случае мы выполняем следующие движения:
- «Пол» и «Вид спорта» — в «Фильтр”
- «Столбцы» в нашем случае оставляем пустыми
- «Имя» — в «Струнах”
- «Сумма, руб.» — в «Ценности”.
- По мере выполнения перемещений вы можете наблюдать, как формируется структура таблицы и как вставляются данные. В результате мы получаем итоговую таблицу, над которой находятся выбранные нами фильтры для «Пол» и «Вид спорта”.
Шаг 3. Примените фильтры и другие настройки
А теперь вернемся к нашей проблеме. Необходимо отфильтровать данные по полу (женский) и оставить только строки, относящиеся к виду спорта «теннис”.
- Щелкните текущее значение фильтра «Пол», выберите «женский» из списка и нажмите кнопку ОК.
- Таким же образом установите фильтр для «Спорт», оставив только «теннис”.
- Итак, у нас есть итоговая таблица с необходимой нам информацией.
Различные варианты сводной таблицы
В рассматриваемом примере показан только один вариант создания сводной таблицы.
Чтобы создать маску, отличную от предыдущей, снова используйте поле со списком полей (справа). Если вы закрыли это окно намеренно или случайно, чтобы вернуть его, щелкните правой кнопкой мыши любую ячейку в таблице и в открывшемся контекстном меню выберите пункт «Показать список полей”.
Теперь попробуем перетащить поле «Цена, рубли» в пустую область «Столбцы» после удаления ранее установленных фильтров, чтобы посмотреть, как таблица будет преобразована в исходный вид.
Благодаря нашим усилиям стол изменил свой внешний вид. Теперь вы можете увидеть разбивку суммы по цене за единицу. При желании и необходимости вы можете повторно активировать фильтры по жанрам или видам спорта.
Вы также можете попробовать добавить в таблицу еще больше информации. Для этого снова откройте список полей и перетащите поле «Продано, штук» в область «Строки”.
Это действие позволит вам узнать, были ли продажи с одинаковым названием продукта в разных строках базовой таблицы. Как видите, это касается кроссовок 35 размера.
Кроме того, в программе есть возможность представить числовые данные в виде гистограммы. Отметьте нужную ячейку, перейдите на главную вкладку, нажмите на функцию «Условное форматирование», затем в открывшемся списке нажмите кнопку «Гистограммы» и выберите вариант, который вам больше всего понравился.
Гистограмма настраивается для выбранной ячейки. Осталось только нажать на кнопку справа от ячейки и выбрать один из вариантов «Применить правило форматирования ко всем ячейкам…”.
Доработанный стол стал более наглядным и привлекательным.
Использование Мастера сводных таблиц
вы можете создать сводную таблицу с помощью инструмента, называемого мастером сводных таблиц. Но сначала вам нужно вывести значок мастера на панель быстрого доступа. Для этого выполняем следующую цепочку действий:
- Откройте меню «Файл», щелкните строку «Параметры», затем щелкните «Панель быстрого доступа». После выбора «Команды не на ленте» в предложенном списке нам понадобится запись «Мастер сводных таблиц и диаграмм». Отметьте его курсором, нажмите «Добавить >>» и завершите настройку, нажав кнопку ОК.
- В левом верхнем углу окна программы появится значок, щелкнув по нему, мы запускаем Мастер сводных таблиц.
- В открывшемся окне необходимо выбрать источник данных, и на выбор может быть предложено до четырех вариантов. В нашем случае мы сосредоточимся на первом варианте, т.е создадим таблицу из списка или из базы данных Excel. Внизу окна выберите «Сводная таблица» и нажмите «Далее”.
- Появится следующее окно, в котором нужно указать координаты исходной таблицы, из которой будет сгенерирована сводная таблица. Если мы согласны с интервалом, автоматически назначенным программой, нажмите кнопку «Далее» или сначала выберите желаемую область, а затем продолжите.
- Как и в рассмотренном выше примере, выберите место для размещения сводной таблицы и нажмите «Готово». Есть два варианта на выбор.
- на новом листе
- на существующем листе (нужно выбрать конкретный лист).
- Будет создана уже знакомая форма для построения сводной таблицы. Далее приступаем к настройке согласно нашим пожеланиям и задачам.
Важные моменты, которые нужно учитывать при формировании сводных таблиц
- Если данные в исходной таблице не совсем правильные, они будут перенесены в том же виде в сводную таблицу. То есть, если, например, в исходной таблице в одной строке написано «Беговые кроссовки, размер 35», а в другой «Беговые кроссовки, размер 35», то в сводной таблице оба этих параметра будут отображаться как два отдельные строки и нет консолидации, по ним не будет данных.
- Если исходная таблица не была преобразована в интеллектуальную на раннем этапе, а сводная таблица была создана из простой, необходимо убедиться, что все заголовки в заголовке таблицы заполнены. В противном случае программа может выдать ошибку.
- Если сводная таблица построена на основе простой (не «умной»), то после любых изменений исходной таблицы необходимо обновить сводные данные с помощью кнопки «Обновить все» на вкладке «Данные”.
Однако следует отметить, что обновление не удастся, если строки или столбцы были добавлены за пределы, указанные выше.
Заключение
Итак, сводную таблицу в Excel можно создать двумя способами: классическим и с помощью мастера сводных таблиц. Последний охватывает более широкий спектр альтернатив при выборе источников данных, однако в большинстве случаев первого более чем достаточно для построения сводной таблицы.
