Создание отчетов при помощи сводных таблиц
Видео
Лирическое вступление или мотивация
Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам, допустим, овощи-фрукты. Для учета проданного в Excel заполняется вот такая таблица:
В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):
- кто из наших менеджеров заключил сделку
- с каким из заказчиков
- какого именно товара и на какую сумму продано
- с какого из наших складов была отгрузка
- когда (месяц и день месяца)
Естественно, если менеджеры по продажам знают свое дело и пашут всерьез, то каждый день к этой таблице будет дописываться несколько десятков строк и к концу, например, года или хотя бы квартала размеры таблицы станут ужасающими. Однако еще больший ужас вызовет у Вас необходимость создания отчетов по этим данным. Например:
- Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?
- Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается?
- Кто входит в пятерку наших самых крупных заказчиков?
... и т.д.
Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить легче, чем Вы думаете. Нам потребуется один из самых ошеломляющих инструментов Microsof Excel - сводные таблицы.
Поехали...
Если у вас Excel 2003 или старше
Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные - Сводная таблица (Data - PivotTable and PivotChartReport). Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard). Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.
Шаг 1. Откуда данные и что надо на выходе?
На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего - "в списке или базе данных Microsoft Excel". Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel "понимает" практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант "в другой сводной таблице..." нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.
Вид отчета - на Ваш вкус - только таблица или таблица сразу с диаграммой.
Шаг 2. Выделите исходные данные, если нужно
На втором шаге необходимо выделить диапазон с данными, но, скорее всего, даже этой простой операции делать не придется - как правило Excel делает это сам.
Шаг 3. Куда поместить сводную таблицу?
На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист - тогда нет риска что сводная таблица "перехлестнется" с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному - этапу конструирования нашего отчета.
Работа с макетом
То, что Вы увидите далее, называется макетом (layout) сводной таблицы. Работать с ним несложно - надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows), столбцов (Columns), страниц (Pages) и данных (Data Items) макета. Единственный нюанс - делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет.
Останется его только достойно отформатировать:
Если у вас Excel 2007 или новее
В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert). Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:
В нем, также как и ранее, нужно выбрать источник данных и место вывода сводной таблицы, нажать ОК и перейти к редактированию макета. Теперь это делать значительно проще, т.к. можно переносить поля не на лист, а в нижнюю часть окна Список полей сводной таблицы, где представлены области:
- Названия строк (Row labels)
- Названия столбцов (Column labels)
- Значения (Values) - раньше это была область элементов данных - тут происходят вычисления.
- Фильтр отчета (Report Filter) - раньше она называлась Страницы (Pages), смысл тот же.
Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) - минимален.
P.S.
Единственный относительный недостаток сводных таблиц - отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).
Ссылки по теме
- Настройка вычислений в сводных таблицах
- Группировка дат и чисел с нужным шагом в сводных таблицах
- Сводная таблица по нескольким диапазонам с разных листов
Вопрос по гистограмме. Когда я выделяю область (пару-тройку ячеек), excel не предлагает такую полезную кнопку, как "Применить правило форматирования к...." И приходится все делать вручную. Как этого избежать? Excel 2015 for Mac
При добавлении вычислительного поля - невозможно сделать формулу товарооборот - себестоимость, т.к. вычислительное поле видит только основное поле: ПОКАЗАТЕЛЬ. Через объект вычисляет, но коряво - вдоль названия каждой точки идут ВСЕ адреса, а значения везде ноль, кроме верного адреса. т.е. типа такого:
Менять структуру в исходной таблице не хочется...
В версии 2013 возникает проблема с обновлением при переименовании файла.
Приходится называть файл сводной, как при создании- только в этом случае таблица обновляется.
В противном случае выдает ошибку "Не удается открыть файл исходной таблицы "Лист Microsoft Excel (например)".
Возможно ли решить эту проблему?
Заранее благодарна.
Разъясните, что же все-таки сделать? Понимаю, что должно быть просто, но много чего пересмотрела - ничего не получается. Заранее благодарна за ответ:)
Ответ нашел.... Спасибо )
Подскажите, пожалуйста, как с буфера сводной вытянуть данные? кинуть сплошной "простыней" в csv?
Сводная подключалась к серверу посредством sql. Чудным утром айтишники сказали:"сервак пал, безвозвратно"
Благо инфа сохранилась в сводных. Файл в бинарном формате более 300 метров (4 листа-4 сводных-4 квартала). Сейчас интересует возможность раскрыть все сгруппированные строки. Раскрывать двойным кликом итоговые цифры напряжно-зачастую более млн строк или полное зависание компа.
может кто сталкивался... кроме сводной инфы ноль, конец года... ХЕЛП!!!
скрин
Как сделать, чтобы при группировании данных по дате выводилось полное наименование месяца а не сокращенное:
фев
мар
апр
май
июн
И в продолжение данного вопроса, как сделать, чтобы группировка происходила не по названию месяца а именно по дате полностью, например 16.02.2018
23.02.2018
и т.д.
спасибо
Подскажите, пожалуйста, можно ли и как сделать, чтобы в Деталях из сводной таблицы (т.е. когда щелкаешь дважды по итогам и на другом листе раскрывается инфо со всеми столбцами исходной таблицы) отображались ТОЛЬКО Необходимые столбцы исходника. Например исходник имеет столбы А,B,C,D,E,F, а в деталях из сводной нужно отобразить только А и С ?
И записей не 1500 а 350.
Вкралась ошибка или я чот напутал (((
Таблица имеет и другие отличия от ролика в общем таблица не от того ролика.
Все очень понятно, емко, структурировано и содержит много информации, которой больше нет ни у одного автора.
Благополучия и процветания Вам!
К поставленным в этом примере задачам хотелось бы видеть реализацию ещё одну: "Сколько товара осталось на складе на момент просмотра"