Сводные таблицы — один из самых мощных инструментов MS Excel. С его помощью вы можете преобразовать миллион строк данных в краткий отчет за считанные секунды. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально на лету менять способ анализа, перетаскивая поля из одной области отчета в другую.
Сводная таблица Excel также является одним из самых недооцененных инструментов. Большинство пользователей и не подозревают, какие возможности находятся в их руках. Представьте, что сводные таблицы еще не изобретены. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте всего 4 позиции. Продукцию регулярно приобретают пара десятков клиентов, которые находятся в разных регионах. Каждая транзакция заносится в базу данных и представляет собой отдельную строку.
Его директор поручает составить сводный отчет о реализации всех товаров по регионам (областям). Вы можете решить проблему следующим образом.
Для начала создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки мы транспонируем столбец в строку. То же самое делаем с площадями, только без транспонирования. Получаем заголовок отчета.
Эта пластина должна быть заполнена, т.е. Сложите доход для соответствующих продуктов и регионов. Это легко сделать с помощью функции СУММЕСЛИМН. Добавим и резюме. Вы получите сводный отчет о продажах в разрезе продуктового региона.
Вы выполнили задание и показали отчет директору. Глядя на таблицу, он генерирует сразу несколько отличных идей.
Можно ли сделать отчет не о доходах, а о прибыли?
— Можно ли отображать товары по строкам, а регионы по столбцам?
— Можно ли сделать такие таблицы для каждого менеджера индивидуально?
Даже если вы опытный пользователь Excel, создание новых отчетов займет много времени. Это не говоря о возможных ошибках. Однако, если вы знаете, как сделать сводную таблицу в Excel, то ответ: да, мне нужно 5 минут, может меньше.
Давайте посмотрим, как создать сводную таблицу в Excel.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно построить на обычном диапазоне, но правильнее будет преобразовать ее в таблицу Excel. Это сразу решит проблему с автоматическим захватом новых данных. Выберите любую ячейку и перейдите на вкладку «Вставка». В левой части ленты есть две кнопки: «Сводная таблица» и «Рекомендуемые сводные таблицы.
Если вы не знаете, как организовать свои данные, вы можете использовать команду «Рекомендуемые сводные таблицы». Excel отобразит эскизы возможных макетов на основе ваших данных.
Нажмите на соответствующую опцию, и сводная таблица готова. Остается только вспомнить его, так как стандартная заготовка вряд ли будет полностью соответствовать вашим пожеланиям. Если вам нужно создать сводную таблицу с нуля или если у вас более старая версия программы, нажмите кнопку «Сводная таблица». Появится окно, в котором нужно указать начальный диапазон (если вы активируете любую ячейку в таблице Excel, она сама определится) и расположение будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно здесь ничего менять не нужно. После нажатия кнопки «ОК» будет создан новый лист Excel с пустой компоновкой сводной таблицы.
Макет таблицы настраивается на панели «Поля сводной таблицы», расположенной в правой части листа.
В верхней части панели находится список всех доступных полей, то есть столбцов в исходных данных. Если вам нужно добавить новое поле в макет, вы можете поставить галочку напротив — Excel сам определит, где это поле должно быть размещено. Однако он не всегда угадывается, поэтому лучше перетащить мышь в нужное место на макете. Удалить поля тоже: снимите флажок или перетащите его обратно.
Сводная таблица состоит из 4 областей, расположенных внизу панели: значения, строки, столбцы, фильтры. Давайте подробнее рассмотрим его назначение.
Область значений — это центральная часть сводной таблицы со значениями, которые получены путем добавления исходных данных выбранным способом.
В большинстве случаев агрегация происходит путем дайджеста. Если все данные в выбранном поле представлены в числовом формате, Excel присвоит сумму по умолчанию. Если исходные данные содержат хотя бы один текст или одну пустую ячейку, вместо суммы будет подсчитано количество ячеек. В нашем примере каждая ячейка представляет собой сумму всех релевантных продуктов в соответствующем регионе.
Ячейки сводной таблицы также могут использовать другие методы расчета. Их около 20 видов (среднее, минимальное значение, участие и т.д.). Существует несколько способов изменить метод расчета. Самый простой способ — щелкнуть правой кнопкой мыши любую ячейку нужного поля в сводной таблице и выбрать другой метод агрегации.
область строк — имена строк, которые находятся в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, поэтому таблица многоуровневая. Здесь обычно размещают качественные переменные, такие как названия товаров, месяцы, регионы и т.д.
область столбцов: аналогично строкам, показывает уникальные значения выбранного поля, только по столбцам. Имена столбцов также часто являются качественной характеристикой. Например, годы и месяцы, товарные группы.
область фильтра: используется, как следует из названия, для фильтрации. Например, в самом отчете представлены товары по регионам. Необходимо ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Затем поле фильтра помещается в область фильтра, а желаемое значение уже выбрано в раскрывающемся списке.
Добавляя и удаляя поля в определенных областях, вы можете настроить любую часть ваших данных за считанные секунды.
Давайте посмотрим, как это работает в действии. Создадим пока такую же таблицу, которая уже была создана с помощью функции СУММ.ЕСЛИ.МНОЖ. Для этого перетащите поле «Доход» в область «Значения», перетащите поле «Область» (регион продаж) в область «Строки» и «Товар» в область «Столбцы».
В результате мы получаем настоящую сводную таблицу.
На сборку ушло буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания режиссера легко воплощаются в жизнь.
Заменим выручку прибылью.
Товары и области также обмениваются перетаскиванием мышью.
Существует несколько инструментов для фильтрации сводных таблиц. В этом случае просто поместите поле «Админ» в область фильтра.
На все ушло несколько секунд. Вот как работать со сводными таблицами. Конечно, не все задачи настолько тривиальны. Есть и те, кому нужно использовать более сложную форму агрегации, добавление вычисляемых полей, условное форматирование и т д. Но об этом мы поговорим в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами ваши исходные данные должны соответствовать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь несколько полезных советов.
1. Лучшим форматом для данных является электронная таблица Excel. Это хорошо, потому что у каждого поля есть имя, и когда добавляются новые строки, они автоматически включаются в сводную таблицу.
2. Избегайте повторяющихся групп, таких как столбцы. Например, все даты должны быть в одном поле, а не разбиты по месяцам на отдельные столбцы.
3. Удалите пробелы и пустые ячейки, иначе эта строка может остаться за пределами разбора.
4. Примените к полям правильное форматирование. Числа должны быть в числовом формате, даты должны быть датой. Иначе будут проблемы с группировкой и математической обработкой. Но тут вам поможет Excel, потому что он сам достаточно хорошо определяет формат данных.
В общем, требований немного, но их удобно знать.
Обновление данных в сводной таблице Excel
Если вы внесете изменения в исходник (например, добавите новые строки), сводная таблица не изменится, пока вы не обновите ее правой кнопкой мыши
или
через команду во вкладке Данные — Обновить все.
Это сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы более экономно использовать ресурсы компьютера, работа идет не напрямую к источнику, а к кэшу, где находится снимок исходных данных.
Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, можно значительно увеличить скорость и качество обработки больших объемов данных.