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