Excel – довольно мощный инструмент, позволяющий работать с большими массивами данных, производя с ними целый ряд операций. Можно утверждать, что таблицы Excel представляют собой реляционные базы данных, с которыми можно работать, не обладая навыками программиста. Но если программа используется профессионально, базовых функций недостаточно, чтобы генерировать отчёты разной структуры. Для этих целей в табличном процессоре имеется более функциональный и мощный инструмент – сводные таблицы.
Для чего в Excel нужны сводные таблицы
В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен: «таблица, которую можно менять, крутить, демонстрировать в разных проекциях». Что это означает? Интерпретация табличных данных – задача непростая. Обычно это делается с помощью формул, получая итоговые результаты в конце таблицы.
Если же требуется создавать сложные отчёты, используя фильтры и меняя исходные данные в нужной последовательности, сделать это стандартными средствами Excel если и можно, то очень сложно. А вот благодаря сводным таблицам такие отчёты создавать гораздо проще, как и различные диаграммы, позволяя только с помощью мышки группировать строки и столбцы, менять их местами, применять различные элементы управления.
Большинство регулярных пользователей Excel даже не догадываются о наличии такого мощного инструмента. Для наглядности приведём небольшой и показательный пример использования сводных таблиц Excel «для чайников».
Допустим, вы менеджер компании и отвечаете за ведение документации по продажам. Все сделки заносятся в таблицу с указанием всех деталей (продавец, покупатель, количество, сумма, дата продажи, адрес покупателя и т. д.). Руководитель просит отчёт о продажах по регионам.
Как мы поступаем? Создаём макет новой таблицы, в шапке которой указываем весь ассортимент реализуемых товаров, а в строках – наименования регионов. Для этого копируем из исходной таблицы столбец с товарами, удалив дубликаты. Используя режим специальной вставки, транспонируем этот столбец в шапку сводной страницы. Аналогичным образом поступаем с регионами, но, поскольку они располагаются в сводной таблице по вертикали, просто копируем без транспонирования.
В каждую ячейку суммируем выручку, используя функцию СУММЕСЛИМН. Наконец, внизу добавляем итоговые результаты и отправляем отчёт руководителю. Руководитель просит регионы расположить вверху, а товары – по строкам. Вы делаете новый отчёт за 5 минут, и получаете новое задание: предоставить данные по прибыли, а не выручке, то есть с учётом затрат. Что ж, немного изменяем формулу, и опять результат готов за считанные минуты. Наконец, поступает просьба подготовить такие отчёты по каждому продавцу, чтобы оценить их эффективность. И это для сводной таблицы не проблема, если знать, где располагаются нужные исходные данные!
Между тем любой пользователь Excel знает, насколько сложно выполнять подобные манипуляции в рамках исходной таблицы.
Создание и работа со сводными таблицами в Excel
Допустим, у нас есть исходная таблица продаж:
Рассмотрим, как на основании этих табличных данных создать сводную таблицу. Для этого выделяем любую ячейку, после чего в главной панели выбираем вкладку «Вставить». Появится две дополнительные вкладки: «Сводная таблица» и левее – «Рекомендуемые сводные таблицы». Если вы новичок и пока не совсем понимаете, каким образом можно организовать исходные данные, рекомендуем воспользоваться второй кнопкой. Это мастер, который предложит вам готовые варианты отчётов. Вам останется только выбрать подходящий вариант, и получить в своё распоряжение готовую сводную таблицу. Останется только подкорректировать её, если по каким-то критериям она вас не удовлетворяет.
Второй, основной способ создания сводных таблиц – «с нуля», для чего жмём первую кнопку.
Откроется новое окно, в котором необходимо задать два параметра: исходный диапазон ячейки и место расположения СТ. Поскольку мы перед созданием сводной таблицы выделили ячейку, диапазон определится автоматически, значением по умолчания для второго параметра является новый лист. Так что ничего не меняем и жмём Ок. В результате откроется новый лист с незаполненным макетом.
Для его настройки следует воспользоваться правой панелью, озаглавленной «Поля сводной таблицы». Здесь в верхнем блоке перечислен список доступных полей, соответствующих столбцам исходной таблицы. Добавлять поля можно двумя способами: проставив галочку напротив нужного значения, и тогда оно автоматически попадёт в макет. Но далеко не всегда в то место, которое вам нужно. В таких случаях нужно просто перетащить поле на необходимую позицию макета. Удаление позиции в макете осуществляется аналогичным образом, снятием галочки, или перетаскиванием на правую панель.
В ней внизу указаны четыре области, на основании которых будет сформирована сводная таблица. Рассмотрим их более подробно:
- область значений является главной частью СТ, поскольку именно здесь указывается, каким образом обрабатывать столбцы и строки. Обычно это суммирование – этот способ агрегирования будет установлен по умолчанию при условии, что все ячейки диапазона принимают числовые значения. При наличии хотя бы одной пустой ячейки или с данными в формате, отличном от числового, вместо суммы значений ячеек будет подсчитано их количество, так что этот факт нужно обязательно учитывать. Для нашего примера суммирование будет производиться по каждому товару в соответствующем регионе. Кроме суммирования, можно задать любой другой способ вычислений, которых имеется около двух десятков (вычисление среднего значения, доли и пр.). Проще всего это сделать непосредственно в сводной таблице, кликнув ПКМ по любой ячейке и выбрав необходимый способ вычислений;
- область строк – состоит из уникальных значений ячеек, расположенных в левом столбце. Если вставить сюда несколько полей, сводная таблица будет уже не плоской, а многоуровневой;
- область столбцов также содержит значения полей, но уже относительно верхней строки исходной таблицы;
- область фильтра позволяет накладывать ограничения на условия вычислений значений ячеек. Обычно сюда помещают поле, по которому должна фильтроваться выборка, заданная в трёх других областях.
Благодаря наличию этих областей можно настроить выборку из исходной таблицы практически любой сложности, потратив на это считанные минуты.
Чтобы было яснее, поясним вышесказанное на примере, описанном в предыдущем разделе.
Итак, пошаговая инструкция, как сделать сводную таблицу в Excel:
- из перечня полей в сводной таблице перетаскиваем поле «Выручка» в область значений;
- поле «Область» (регион нахождения покупателя) перетаскиваем в область строк;
- поле «Товар» помещаем в область столбцов.
Всего три действия – и сводная таблица, пригодная для отправки начальству, готова! На её составление у нас ушло порядка 10 секунд.
А как выполнить остальные пожелания директора?
Чтобы вместо выручки просуммировать прибыль, достаточно в область значений вместо выручки поместить соответствующее поле, соответствующие вычисления в сводной таблице будут произведены автоматически.
Вторая задача тоже выполняется за секунды – чтобы поменять местами регионы и товары, просто перетащите их из одной области в другую (поменяйте местами).
Чтобы получить результаты по каждому менеджеру, достаточно в область фильтра поместить поле «Менеджер», тогда вверху появится селектор, в котором нужно указать нужную фамилию для получения отчёта. Имеется возможность объединять отчёт по нескольким продавцам.
Разумеется, мы привели не самый сложный, но весьма показательный пример, позволяющий понять, как в Excel создавать сводные таблицы. Способ вычислений значений в ячейках может быть более замысловатым, возможно, придётся использовать вычисляемые поля, а также применять условное форматирование – возможности в этом плане у Excel внушительные.
Об источнике данных
Мы уже упоминали, что к исходным данным, используемым для правильного создания сводных таблиц в программе Excel, предъявляются определённые требования. Перечислим их:
- главное ограничение связано с обязательным наличием названий над столбцами, участвующими в вычислениях. Такие идентификаторы необходимы для формирования результирующих отчётов – при добавлении в исходную таблицу новых записей (строк) формат СТ менять не нужно, а результаты будут пересчитаны автоматически;
- убедитесь, что в ячейках строк и столбцов, участвующих в выборке, введены числовые параметры. Если они будут пустыми или содержать текстовые значения, эти строки выпадут из расчётов, что исказит результаты вычислений;
- следите за соответствием форматов строк и содержимым ячеек. Если она определена как дата, то все значения в столбце должны иметь такой же формат, иначе фильтрация и просчеты будут неправильными.
Если придерживаться этих правил, то с построением сводной таблицы проблем возникнуть не должно.
Обновление данных в сводной таблице Excel
Хотя мы говорили об автоматическом пересчёте сводных таблиц при внесении корректировок в исходные данные или в структуру самой СТ, на самом деле из соображений экономии ресурсов компьютера для пересчёта нужно выполнить определённые действия.
Обновить данные в нашей сводной таблице Excel можно двумя способами:
- кликнув правой кнопкой мыши на любой ячейке и выбрав в контекстном меню пункт «Обновить»;
- используя вкладку «Данные» и кнопку «Обновить всё».
Кэширование таблиц особенно оправдано, если они большие по размерам, в этом случае скорость работы программы существенно увеличится, особенно если вы вводите новые строки регулярно и с высокой частотой.
Добавление в СТ Excel столбца или строки
В целом операция тривиальная, если нужный параметр имеется в исходной таблице. Вам останется только перетащить нужное поле в нужную область. Если же строка или столбец в исходной таблице отсутствует, добавьте её, введите, если нужно, значения. Далее кликаем на вкладке «Анализ» и открываем нашу изменённую таблицу. Останется только обновить данные (см. предыдущий раздел), в результате в правой панели СТ перечень полей изменится – в него будет добавлен новый параметр.
Часто сводная таблица имеет не совсем удобный для визуализации вид. Например, когда в области строк имеется несколько полей. Тогда одно из них выводится в полном объёме, а значения другого нужно указывать в верхней части таблицы. Если таких значений немного (скажем, в нашем примере регионов продаж всего 6), то имеет смысл добавить столбец «Регионы» сводной таблицы в соответствующую область, перенеся его из области строк.
Изменение порядка следования столбцов также добиться несложно, просто перенеся мышкой заголовок столбца"