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