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