Создание отчетов при помощи сводных таблиц

Видео

Лирическое вступление или мотивация

Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам, допустим, овощи-фрукты. Для учета проданного в Excel заполняется вот такая таблица:

pivot0.png

В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):

  • кто из наших менеджеров заключил сделку
  • с каким из заказчиков
  • какого именно товара и на какую сумму продано
  • с какого из наших складов была отгрузка
  • когда (месяц и день месяца)

Естественно, если менеджеры по продажам знают свое дело и пашут всерьез, то каждый день к этой таблице будет дописываться несколько десятков строк и к концу, например, года или хотя бы квартала размеры таблицы станут ужасающими. Однако еще больший ужас вызовет у Вас необходимость создания отчетов по этим данным. Например:

  • Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?  
  • Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается? 
  • Кто входит в пятерку наших самых крупных заказчиков? 

... и т.д.

Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить легче, чем Вы думаете. Нам потребуется один из самых ошеломляющих инструментов Microsof Excel - сводные таблицы.

Поехали...

Если у вас Excel 2003 или старше

Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные - Сводная таблица (Data - PivotTable and PivotChartReport). Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard). Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.

Шаг 1. Откуда данные и что надо на выходе?

pivot1.gif

На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего - "в списке или базе данных Microsoft Excel". Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel "понимает" практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант "в другой сводной таблице..." нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.

Вид отчета - на Ваш вкус - только таблица или таблица сразу с диаграммой.

Шаг 2. Выделите исходные данные, если нужно

pivot2.gif

На втором шаге необходимо выделить диапазон с данными, но, скорее всего, даже этой простой операции делать не придется - как правило Excel делает это сам.

Шаг 3. Куда поместить сводную таблицу?

pivot3.gif

На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист - тогда нет риска что сводная таблица "перехлестнется" с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному - этапу конструирования нашего отчета.

Работа с макетом

То, что Вы увидите далее, называется макетом (layout) сводной таблицы. Работать с ним несложно - надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows), столбцов (Columns), страниц (Pages) и данных (Data Items) макета. Единственный нюанс - делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет. 

pivot6.gif

Останется его только достойно отформатировать:

pivot_finish.gif

Если у вас Excel 2007 или новее

В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert). Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

pivot6.png

В нем, также как и ранее, нужно выбрать источник данных и место вывода сводной таблицы, нажать ОК и перейти к редактированию макета. Теперь это делать значительно проще, т.к. можно переносить поля не на лист, а в нижнюю часть окна Список полей сводной таблицы, где представлены области:

  • Названия строк (Row labels)
  • Названия столбцов (Column labels)
  • Значения (Values) - раньше это была область элементов данных - тут происходят вычисления.
  • Фильтр отчета (Report Filter) - раньше она называлась Страницы (Pages), смысл тот же.

pivot7.png

Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) - минимален. 

P.S.

Единственный относительный недостаток сводных таблиц - отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).

Ссылки по теме

 


Страницы: 1  2  
27.02.2013 13:42:38
Добрый день,
подскажите, пожалуйста, если необходимо сделать ссылку на итог сводной таблицы, каким образом это сделать? т.к. если изменить структуру, например, свернув под категорию, ссылка остается на пустое поле
28.02.2013 09:47:16
Артем, чтобы ссылка на нужные данные в сводной таблице не слетала, лучше использовать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Просто выделите любую пустую ячейку на листе, введите в нее знак = и щелкните по ячейке в сводной с нужным вам итогом. Должна вставиться функция вида:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Продажа";$A$3;"Регион";"Центр")

которая (для примера) ссылается на итог по полю Продажа для региона Центр.
Такая ссылка не слетает при реструктуризации сводной и всегда будет выдавать правильное значение.
28.02.2013 09:51:33
Большое спасибо :) все получилось
15.03.2013 21:39:45
Добрый день, такая проблема, в сформированной сводной таблице, в списке полей сводной таблицы при перетаскивании значения цены в поле *сумма значения*-находится в нижнем левом углу и  при изменеии в параметрах полей значений на *сумму* вместо *колличества* автоматически выходит *0*, хотя если это же значение перетащить в *название строк* то будет выходить нормальное числовое значение соответствующее значению в исходной таблице
16.03.2013 12:01:53
Евгения, скорее всего у вас в исходных данных числа в текстовом формате, т.е. только выглядят как числа, а на самом деле воспринимаются Excel как текст. Поэтому и не считаются в поле значений, но выводятся в поле строк.
16.03.2013 13:21:58
спасибо, в итоге форматом ячеек в число не переводились, потому что стояло поле числа 2 пробела -)
30.04.2013 12:00:15
Здравствуйте Николай! подскажите пожалуйста уже замучился вся надежда на вас ) Вот я создал турнирную таблицу по футболу в Excel как сделать чтоб команда выигравшая  заработавшая 3 очка перемещалась на верх таблицы  сама  что бы не переписывать таблицу каждый раз??? вообщем чтоб команда с наибольшим числом очков перемещалась в верх? Или может есть уже такой пример на сайте ??? Помогите очень нужно?

Заранее благодарен !
01.05.2013 00:33:39
Иван, если ваша турнирная таблица - сводная, то она (начиная с версии Excel 2007) поддерживает автосортировку, т.е. достаточно один раз ее отсортировать по убыванию, и она дальше сама будет поддерживать себя в отсортированном виде после изменения и пересчета. Если же ваша турнирная таблица - просто таблица, то вам поможет сортировка формулой.
28.05.2013 10:07:03
Добрый!
Сделал отчет с кучей сводных таблиц, во всех приходится менять фильтр по дате, число одно и тоже.
Можно  вставить туда ссылку или имя переменной?
29.05.2013 11:59:52
Андрей, если у вас Excel 2010, то можно вставить для фильтрации срезы (Вставка - Срез) и привязать их потом сразу к нескольким сводным таблицам. В более старых версиях одновременная фильтрация сводных делается только макросом (см. http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/ для примера).
Николай, добрый день! У меня на Вас последняя надежда. Создала файл со сводной таблицей, все работает прекрасно. НО! Мне необходимо расшарить этот файл, чтобы одновремено могли работать в этом файле несколько человек. И в этот момент сводная таблица не дает с ней работать, а именно - нельзя даже расхлопнуть подитоги и проваливаться в результаты.
Можно как-то обойти эту проблему?
Нам очень нужно работать в нем одновременно со сводной таблицей...
01.07.2013 21:04:48
Валентина, порадовать нечем. В общих файлах не работают очень многие функции, в том числе - сводные таблицы.
26.07.2013 20:38:25
Здравствуйте, Николай!
Подскажите, пожалуйста, можно ли в сводной таблице вместо суммы вычислить разницу между двумя столбцами?
Буду благодарна за помощь.
01.09.2013 20:26:27
Вам нужны вычисляемые поля или вычисляемые объекты - в зависимости от задачи. Посмотрите вкладку Параметры - Поля, элементы, наборы (Формулы) - Вычисляемое поле/объект.
27.09.2013 16:40:27
Добрый день,Николай. Есть файл с несколькими листами-с отчетом прибыли-убытки за каждый месяц .Создала сводную таблицу и  не могу переименовать и отсортировать объекты Страниц. Они просто подписаны объект1,объект 2 и т.д. и расположены в хаотическом порядке ,а мне нужно, что бы это были названия месяцев и отсортированы. Подскажите пожалуйста ,как это можно сделать. Спасибо.
04.01.2014 12:51:25
Наталья, вы создали сводную по нескольким диапазонам консолидации (видимо).
Такая сводная не является полноценной и нормально с ней работать не получится :(
28.11.2013 11:03:36
Николай, добрый день!

Подскажите, пожалуйста, мне в поле значения необходимо указать ID номер, т.е. это номер, который присвоен определенному товару, можно сделать так, чтобы в категории, которая выше не отображалась сумма этих ID?
04.01.2014 12:50:27
Юлия, попробуйте переключиться из режима суммирования в подсчет максимального или минимального (правой кнопкой мыши по ID в области значений - Итоги по - Максимум).
v.plagov
28.11.2013 14:32:03
Спасибо за видео.
Часто видел такую таблицу в прайс-листах. Теперь и сам буду делать :)
28.11.2013 18:24:06
Здравствуйте Николай!

Можно ли сделать так, чтобы при размещении нескольких полей в одной области они отражались не иерархично, а параллельно? Допустим, чтобы в области строк параллельно шла следующая информация: клиент, регион, товар и т.п.
04.01.2014 12:49:19
Искандер, забрасываете все нужные вам поля в область строк, затем убираете итоги (вкладка Конструктор - Промежуточные итоги) и переключаетесь в табличную форму (вкладка Конструктор - Макет).
08.01.2014 11:12:26
Николай, спасибо за ответ, но получился совсем не тот результат который мне нужен - иерархичность как была, так и осталась, просто исчезли итоги по полям клиент, регион, товар - хотя именно они мне и нужны.
18.11.2015 03:01:31
Попробуйте встать на колонку Клиент и правой мышкой расширить (Expand) Регион. При Табличной форме добавиться паралельно.
15.01.2014 15:55:52
Добрый день!
Подскажите, пож-та.
У меня есть нумерованный список данных. Как сформировать сводную таблицу, чтобы этот список был в нужном порядке? Имеется ввиду список 2.1., 2.1.1. и т.д.
23.01.2014 09:34:22
Здравствуйте!
У меня Excel 2003. Я создала сводную таблицу с 3 полями Фирма, Модель (2 модели) и Количество продаж в разрезе моделей.
Подскажите, как сделать так, чтобы Фирмы с одинаковыми наименованиями проссумировались? Спасибо.
05.02.2014 14:37:32
Здравствуйте!
Единственный относительный недостаток сводных таблиц - отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).
Подскажите проблема с автоматическим обновлением как  то решаема?
03.03.2014 17:42:06
Николай день добрый.
Допустим я руководитель отдела продаж.
Собрал данные по продажам - основные данные "Клиент" "Номенклатура" " Месяц" Сумма происходит по полю количество.
И когда сморю полную табличку то все хорошо - продажи идут каждый месяц(столбики) по всей номенклатуре(строки).
А вот когда смотрю отдельного клиента то получаю данные только по тому что он купил и только в те месяца в которые он покупал.

А хотелось бы видеть картинку по всем месяцам и по всей номенклатуре. Чтобы увидеть дырки.
19.04.2014 10:48:22
Данила, можно правой кнопкой мыши по полю в таблице (в области строк или столбцов) - Параметры поля - вкладка Разметка и печать - включить флажок Отображать пустые элементы.
21.04.2014 10:46:50
Огромное спасибо.
21.04.2014 14:58:34
Эх - как оказалось не панацея
Если делать столбик в две группы то все позиции второго столбика попадают в каждую группу первого.  
06.05.2014 12:48:13
Еще - 13 офис при включении Data Object Model и выборе в поле значения Число различных элементов (Distinct Count)
возможность установки галочки на Отображать пустые элементы отсутствует.
06.04.2014 13:50:36
Николай, помогите :)  Как мне из исходной таблицы разнести данные по разным листам с соответствующим признаком для каждого листа? Например, товары по секциям (бакалея, напитки и т.д.), нужно сделать листы (лист "бакалея", лист "напитки"..) и так далее, при этом чтобы переносились все остальные данные (ед.изм., поставщик) и так далее.
19.04.2014 09:49:08
Татьяна, если речь про сводную таблицу, то поле секций забрасываете в фильтр отчета, потом идем на вкладку Параметры, выпадающий список Параметры, команда Отобразить страницы фильтра отчета.
Если речь про обычную таблицу, то лучше использовать функцию разнесения по листам из PLEX.
27.05.2014 11:25:11
Николай, у меня возникли проблемы на этапе "форматировать как таблицу". Excel видит только часть данных как таблицу. У меня много данных, которые готовили разные люди. Возможно в разных форматах. Подскажите, пожалуйста, как можно подготовить исходную таблицу для того, чтобы потом сделать сводную.
01.07.2014 14:16:06
Николай, добрый день
пользуюсь таблицами. возникла проблема с оформлением.
На вашем примере столбик итогов по месяцам выделен цветом.
на моем компьютере, встав на заголовок итогового столбца (или строки), мышь меняет форму и я могу выделить одновременно все подобные столбцы (или строки). например, чтобы тоже их покрасить.
на компьютере шефа (у нас обоих EXCEL 2010) такой возможности нет. где-то сбились настройки? что проверить?
так происходит и при построении новых таблиц, и при использовании одного и того же файла (по очереди) на этих двух компьютерах.
надежда только на Вас. очень долго искала возможность перенастроить...
01.07.2014 17:26:36
Спасибо, похоже я все же нашла
30.07.2014 10:40:44
Николай, здраствуйте! Подскажите пожалуйста. Можно ли в сводный отчет добавить столбци, которых нет в исходной таблице? В этих столбцах мне нужны новые формулы, которые связаны с итогами в строках. Т.е. если рассматривать Ваш пример, то мне нужен новый столбик, который считал бы формулу по итогам групп (фрукты, овощи, зелень) и еще один столбик, в котором производятся расчеты с учетом расчета по итогам групп. Конечно хотелось бы, что бы эти столбики обновлялись вместе со сводной таблицей. И можно ли в сводный отчет добавить формулу, например ЕСЛИ?
11.08.2014 17:55:30
Добрый день!

Есть сводная таблица (строки=список артикулов, столбцы=даты, на пересечение сумма продаж). и обычная таблица которая содержит (строки=расширенный список артикулов, столбцы=даты), а на пересечение нужно подтянуть суммы продаж из сводной.
Можно ли это сделать функцией "получить.данные.сводной.таблицы", если да то как? Я думал что функция работает по принципу ВПР, но она подтягивает данные по порядку, а если я указываю в "поле данных" артикул из простой таблицы, то формула выдает #ССЫЛКА!
24.12.2014 10:34:11
Здравствуйте,
Помогите пожалуйста решить задачу.
Есть три столбца
/
ГородТовар Менеджер
МоскваТовар1 Иванов
Москва Товар2 Петров
ТулаТовар1Сидоров
ТулаТовар2Иванов

Как сделать сводную таблицу такого формата
МоскваТула
Товар1ИвановСидоров
Товар2ПетровИванов


Не как не могу найти с помощью какого инструмента это можно решить.
27.12.2014 23:47:37
С помощью сводных такое не сделать точно - у них в области значений текста не бывает :(
Здесь скорее что-то похожее на двумерный ВПР нужно пробовать.
27.12.2014 17:46:47
Добрый вечер!

Ситуация: У меня две разные исходные таблицы. Они совпадают лишь по дате события и ФИО сотрудника. Поэтому я создал 2 pivot таблицы и с помощью Getpivotdata объединил их на новом листе в одну, куда подгружаются нужные мне из этих таблиц данные. Но мне нужно двойным щелчком в итоговой таблице просматривать результаты ссылающиеся на источник.

Подскажите, есть ли возможность получать значение ячейки, взятого из исходного Pivot с помощью GETPIVOTDATA,  с сохранением возмости нажатия на этом значении двойного щелчка для открытия на отдельном листе, чтобы посмотреть что исходное значения?

Спасибо.
27.12.2014 23:49:01
Встроенными средствами - нет.
С помощью макроса - возможно.
Как вариант: в Excel 2013 можно сделать сводную сразу по обеим вашим таблицам (при построении ставим флажок про Data Model). Тогда и двойной щелчок работать будет и GETPIVOTDATA не нужна.
26.02.2015 14:23:03
Добрый день, Нкиколай.
Ситуация следующая: есть сводная таблица по планам и фактам продаж. Отдельно идут факты по отделам, отдельно планы. Необходимо посчитать процент выполнения плана отдельно по сотрудникам и по отделам в целом. То есть разделить факт на план. Можно ли это сделать при помощи сводной таблицы, или все-таки просто написать формулу?
26.02.2015 14:47:58
Уже нашел ответ. Если кому пригодится: нужно создать вычисляемое поле, в котором и указать что на что будет делиться. В любом случае, спасибо)
06.03.2015 19:16:32
Добрый день Николай
Я только начал разбираться с таблицами и сразу возникли проблемы.
У меня есть большая исходная таблица с данными с которой нужно вывести на печать данные. Пр. данные по учету животных: столбцы - № учета, дата, район, область, вид, численность, биотоп. Как сделать, что бы при фильтрации в сводной таблице высвечивались др. показатели.
Заранее огромное СПАСИБО  
18.03.2015 15:26:51
Спасибо, Вам Николай, я со всем разобралась!
05.04.2015 22:25:57
Урок очень полезен. Не знал раньше о такой крутой фишке. Но вот возник вопрос. Как сформировать сводную таблицу, если мне нужно в поле значение поставить текстовое значение. Что бы в столбце первом производилась сортировка по нужным столбцам исходной таблицы, в столбце втором писалось значение текстовое (в моем случаи это единицы измерения), а в следующем столбце писалось уже расчетное значение (числовое)?
Спасибо!
24.04.2015 09:19:23
Добрый день,

Как и все Ваши уроки очень полезны, за что огромная благодарность. Пользуюсь надстройкой Plex начиная с версии 2.0. Очень полезная и точная надстройка. E-book тоже приобрел сразу же как появилась на сайте :)   Просто супер!!!

Есть одна задача которую не могу решит. Задача: Ест две базы из которых создал 3 сводные таблицы (с одной базы одну, а с другой две). Каждые из них по дате фильтруются. Две сводные таблицы из разных баз всегда должны быт в одинаковой дате. А другая на один рабочий день раньше. Хочу что если поменял дату на одной сводной (в том которая не связанна с другой базой) то следующие сводные таблицы (которые связаны с одинаковой базой) автоматически настраивались сами (один на ту же дату, а другой на рабочий день назад).

Попробовал Timeline, по отдельности получается. Если сказанная задача будет управляться с Timeline, то вообще было бы супер. В Timeline есть месяц, квартал и т. д.    

Заранее за все СПАСИБО.
20.05.2015 09:41:01
Спасибо за теплые слова :)
Если выделить Timeline, то на вкладке Параметры будет кнопка Подключения к отчетам - она позволит выбрать какие сводные таблицы должны фильтроваться и, таким образом, можно будет выбрать и фильтровать обе сразу.
19.05.2015 18:05:53
Николай добрый день!

Вопрос по Вашему примеру.
Если на листе "заказы" добавить нового Заказчика или новый месяц, то в сводной таблице эта информация не обновляется и на экране не появляется, почему?
20.05.2015 09:36:38
Во-первых, надо изменить область данных, откуда сводная таблица берет информацию (вкладка Параметры - Исходные данные). Если сводная построена на основе умной таблицы, то это произойдет автоматически.
Во-вторых, надо обновить сводную (правой кнопкой мыши по сводной - Обновить).
20.05.2015 10:19:10
Обновить- правая кнопка мышки. Вот где эта кнопка пряталась. Николай, огромное Вам спасибо!
06.07.2015 09:38:27
Здравствуйте! столкнулся с такой проблемой..при изменении значений в сводной таблице (фильтрация срезами или добавление новых строк в сводной) слетает , например, параметр 1-ого столбца св.табл. по ширине и перенос по строкам..таблица разъезжается слетают параметры печати  и т.д.((( подскажите, как это победить??
26.06.2016 09:31:00
Правой кнопкой мыши по таблице - Параметры сводной таблицы - снять флажок Автоподбор ширины столбцов при обновлении.
Страницы: 1  2  
Наверх