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