Создание отчетов с помощью Сводных таблиц (Pivot Tables)

Создание отчетов с помощью Сводных таблиц (Pivot Tables)

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

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

• название заемщика
• номер сделки
• дата выдачи и дата погашения по кредиту
• тип кредита
• сумма кредита

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

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

Ответы на все вопросы можно получить легче, чем Вы думаете. Минуты так за три, примерно. С помощью одного из самых потрясающих инструментов Microsoft Excel — Сводных таблиц.

Выделяем всю нашу таблицу с данными и жмем в меню Вставка — Сводная таблица ( Insert — PivotTable ).

Появится вот такое окно, в котором мы можем выбрать куда поместить нашу сводную таблицу и скорректировать диапазон данных.

По умолчанию выбрано поместить сводную таблицу на новый лист (New Worksheet) , однако мы можем, при необходимости, вручную выбрать текущий или любой другой лист книги (Existing Worksheet). Лучше для этого выбирать новый лист — тогда нет риска что сводная таблица «перекроется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку OK и переходим к самому интересному — этапа конструирования нашего отчета.

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

То, что Вы увидите, нажав кнопку OK называется макет (layout) сводной таблицы:

Работать с ним несложно — надо перетаскивать мышью названия столбцов (полей) из окна Список полей сводной таблицы (PivotTable Field List) в области строк (Row Labels), столбцов (Column Lables), фильтров (Report Filter) и данных макета (Values). Единственный нюанс — делайте это точнее, не промахнитесь! Поехали.

В процессе перетаскивания сводная таблица у Вас на глазах начнет менять облик, отражая те данные, которые Вам необходимы. Перебросив все четыре нужных нам поля из списка, Вы получите практически готовый отчет. Останется его только достойно отформатировать.
Выбираем форматирование сводной таблицы в меню PivotTables Tools — Design:

И получаем следующий вид:

Не так уж это все и сложно, не так ли?

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

Макет сводной таблицы в excel

Подробный анализ данных из таблицы базы данных требует возможности получения сводной информации по записям базы данных. Результаты такого анализа в Excel могут быть представлены в виде сводной таблицы или сводной диаграммы.

Построение сводной таблицы производится с помощью Мастера сводной таблицы . Для его запуска первоначально, как обычно, требуется выделить ячейку, относящуюся к базе данных, далее выполнить команду Данные → Сводная таблица.

На первом шаге работы мастера выбирается тип данных на основе которых строится сводная таблица. Для использования базы данных Excel — это пункт В списке или базе данных Microsoft Excel .

После щелчка на кнопке Далее , на втором шаге надо уточнить, правильно ли был выбран диапазон, охватывающий базу данных.

После очередного щелчка на кнопке Далее , на третьем шаге выбирается вначале местоположение сводной таблицы. В большинстве случаев следует размещать ее на новом рабочем листе (переключатель Новый лист ). Затем кнопкой Макет… загружается диалоговое окно макета сводной таблицы, в котором надо сформировать содержание и оформление сводной таблицы.

Содержание сводной таблицы. Сводная таблица состоит из четырех областей: Страница, Строка, Столбец и Данные .

Каждой области сводной таблицы должны соответствовать одно или несколько полей базы данных. Исключение делается только для поля Страница , которое можно оставить пустым. Для заполнения этих полей служат кнопки, названия которых совпадают с названиями полей базы данных. Поместить кнопку в нужное поле можно методом перетаскивания. В сводной таблице можно использовать только те поля, которые необходимы. Незадействованные поля в отборе и вычислениях не участвуют.

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

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

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

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

По их завершении требуется щелкнуть на кнопке ОК , а затем на кнопке Готово , и сводная таблица строится автоматически.

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

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

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

Динамическая связь с исходными данными проявляется и в том, что при изменении данных не требуется заново формировать сводную таблицу. Достаточно щелкнуть в пределах таблицы правой кнопкой мыши и выбрать в контекстном меню пункт Обновить данные .

В случае, когда записи базы данных добавляются или удаляются, необходимо снова запустить мастер сводных таблиц (проще всего для этого воспользоваться кнопкой Мастер сводных таблиц на панели инструментов Сводные таблицы ), щелкнуть на кнопке Назад и уточнить диапазон ячеек, включающих записи базы данных После щелчка на кнопке Готово данные сводной таблицы будут обновлены с учетом изменения числа записей.

Сводная таблица Excel ч.2. Расширяем навыки

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

Как скопировать сводную таблицу

Совсем недавно один из читателей обратился ко мне с вопросом: «А как скопировать сводную таблицу с одного листа на другой»? Решил осветить этот вопрос здесь, потому что слышу его не впервые. Сводная таблица – это не обычный диапазон данных, это область ячеек с результатами работы инструмента. Поэтому, просто скопировать сводную таблицу в другое место нельзя.

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

данных в сводной таблице одним из известных способов. Например, нажмите Ctrl+C на клавиатуре
1. Установите курсор в ячейку, где должен располагаться верхний левый угол вставляемого диапазона
2. Выполните на ленте Главная – Буфер обмена – Вставить – Значения .

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

Как получить детальные данные из сводной таблицы

Иногда нужно получить исходные данные, на основании которых вычислена та или иная строка сводной таблицы. То есть, сделать выборку данных, удовлетворяющих какому-то критерию из сводной таблицы.

Вернемся к примеру из предыдущего поста. Там у нас была подневная таблица продаж по группам товара, регионам и менеджерам. Мы построили сводную таблицу, в которой посчитали сумму продаж с группировкой по менеджерам и группам товаров.

Представим, что по какой-то причине нас заинтересовали подневные продажи метизов у Романа. Выделим ячейку на пересечении строки «Роман» и столбца «Метизы». Жмем на нем правой кнопкой мыши и выбираем «Показать детали». Программа создаст новый лист и отобразит на нем выборку из исходной таблицы, т.е. все продажи Романа метизов.

А можно просто сделать двойной клик по нужной величине. Это альтернативный, и более простой способ.

Обратите внимание, что результат будет представлен в виде «официальной» таблицы Эксель, а не обычного диапазона. Можете еще раз оценить удобство такой структуры данных и воспользоваться всеми ее преимуществами.

Дополнительные вычисления в сводной таблице

Если стандартных функций вычисления в сводной таблице (сумма, количество, отклонение и т.п.) Вам оказалось мало, инструмент имеет дополнительные вычислительные возможности. Прежде всего, нажмите правой кнопкой мыши в любой ячейке столбца с вычислениями и выберите «Дополнительные вычисления». В списке вычислений будет много интересных вариантов. К примеру. Мы хотим знать какую долю продаж от всего количества выполняет каждый менеджер из примера. Выполним дополнительные вычисления в столбце, где суммируются продажи. Параметром выберем «% от суммы по столбцу». Посмотрите, в 2 клика мы перешли от натуральных величин к процентам, теперь легче сравнивать показатели, или скрыть их, когда не рекомендуется отражать в отчете конкретные цифры в денежных единицах.

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

Вычисляемые поля и объекты

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

Вычисляемые поля

Эти объекты нужны, чтобы вставить в таблицу новые столбцы без вставки их в исходный массив данных. К примеру, у нас есть сумма продаж менеджеров и количество чеков. Рассчитаем в отдельном столбце средний чек.

Выполняем такую последовательность действий:

1. Установим курсор в одну из ячеек, содержащих значения
2. На ленте нажимаем: Работа со сводными таблицами – Анализ – Поля, элементы, наборы – Вычисляемое поле
3. В открывшемся окне в поле «Имя» запишем «Средний чек»
4. Теперь вводим формулу, нам нужно поделить сумму продаж на количество чеков. Всписке полей дважды кликнем на «Сумма продаж», пишем на клавиатуре знак деления «/» и дважды щелкаем на «Количество чеков. Должна получиться такая формула:

1. Жмем Ок и смотрим, что получилось.

Теперь у нас появился еще один столбец, в котором посчитана средняя сумма в чеке для каждого менеджера. Его название появилось в области «Значения» панели настройки сводной таблицы.

Вычисляемые объекты

Похожий функционал предоставляют вычисляемые объекты. Но они вставляют в таблицу не столбцы, а строки. К примеру, у нас есть сумма продаж менеджеров, а нас интересует сколько составит НДС (18%) от этих продаж и общая сумма с НДС. Создаем вычисляемый объект:

1. Ставим курсор в любую строчку первого столбца или любой столбец первой строки
2. Жмем на ленте: Работа со сводными таблицами – Анализ – Поля, элементы, наборы – Вычисляемый объект . Откроется окно вставки:

1. В поле «Имя» запишем «НДС», в списке «Поля» выбираем «Менеджер»
2. Кликая дважды на имя каждого менеджера, запишем формулу: =(Алексей+Анна +Виктор +Виктория +Виталий +Денис +Егор +Роман +Светлана)*0,18
3. Удалим ненужные поля, жмем Ок. Получаем еще одно поле, в котором будет посчитана НДС. Значение в этом поле будет добавлено к общей сумме.

Группировка данных в сводной таблице

Когда Ваша таблица готова, можно сделать дополнительную группировку данных. Это улучшит его читаемость и гибкость. Выделю несколько удобных инструментов группировки.

Группировка с шагом

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

1. Создаем сводную таблицу, где в строках будут дни, а в значениях – продажи. Если Вы не знаете, как создать сводную таблицу – сначала прочтите предыдущий пост;
2. Кликните правой кнопкой мыши по любой из дат в сводной таблице и в контекстном меню выберите «Группировать». Откроется окно настройки группировки;

1. В полях «Начиная с» и «по» автоматически установятся минимальная и максимальная даты в списке. Можете, при необходимости, указать здесь более узкий период для группировки
2. В списке «С шагом» выберите эталонный интервал времени. У нас это «Месяцы». Можно выбрать сразу несколько пунктов в этом списке. Давайте попробуем построить по кварталам и месяцам, отмечаем их;
3. Жмем «Ок» и сразу же получаем результат. Взгляните, что получилось:

Таким же образом можно группировать обычные числовые данные. Например, мы хотим сгруппировать ежедневные продажи с шагом 1000 и узнать, в каком из интервалов было больше всего чеков. Делаем так:

1. Строим сводную таблицу, в строках – суммы продаж, в значениях – количество чеков. Сначала у нас получится длинная и бесполезная таблица.

1. Кликнем правой кнопкой в любо строке первого столбца (суммы продаж) и выберем «Группировать». В открывшемся окне задаем минимальное и максимальное числа для группировки, а так же, шаг. У нас это 1000. Вместо огромной таблицы, получаем компактную, из десяти строк. В каждой строке – интервал сумм и количество чеков в этом интервале.

1. Кликнем правой кнопкой по любой ячейке в столбце «Количество чеков» и выберем Сортировка – по убыванию ;
2. Можно для наглядности выразить результат в процентах. Кликнем по той же ячейке и выберем Дополнительные вычисления — % от суммы по столбцу . Вот и все, задача решена, нам прекрасно видно в каких интервалах сумм было больше всего чеков.

Конечно, эта задача упрощенная, он отличается от тех, которые будете решать Вы. Но главное – понять механизм работы, тогда сможете применить его в своих расчетах.

Так же, можно группировать записи вручную. Для этого выделите нужные данные и нажмите Работа со сводными таблицами – Анализ – Группировать – Группировка по выделенному .

Кстати, чтобы отменить группировку – кликните правой кнопкой мыши по группированному столбцу и выберите «Разгруппировать».

Фильтрация сводных таблиц с помощью срезов

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

А выглядит это вот так:

Вы видите несколько окон на рабочем листе с перечисленными в них данными, а так же, сводную таблицу, содержащую полный набор данных. Но что если кому-то нужно посмотреть продажи метизов у Романа 1 апреля 2016 года? Кликаем в окнах на кнопки:

1. В окне «Дата» ищем и выбираем 01.04.2016;
2. В окне «Менеджер» выбираем «Роман»;
3. В окне «Группа товара» кликаем «Метизы»

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

По-моему, отлично! Так вот, чтобы включить срезы в сводных таблицах – выделите любую ячейку этой таблицы и выполните на ленте Работа со сводными таблицами – Анализ – Фильтр – Вставить срез . На экране появится окошко, где нужно галочками отметить те поля сводной таблицы, по которым можно будет делать срезы. Каждому полю будет соответствовать свое окошко со списком. Выбирайте, жмите Ок и все, заработало!

Аналогично работает временная шкала. Этот инструмент очень похож на срезы, но управляет полями, в которых содержатся даты. Чтобы добавить временную шкалу – нажмите Работа со сводными таблицами – Анализ – Фильтр – Вставить временную шкалу . После простых настроек появится окно фильтрации дат, которое позволяет эффективно и быстро ограничивать периоды дат, выводимые в отчет.

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

Добавить комментарий Отменить ответ

8 комментариев

Сашуль, привет, help. такой вопрос — как в сводных таблицах построить структуру автоматически, не нажимая каждый раз кнопку Группировать?? при выделении таблицы сводной и потом создание Структуры выходит ошибка, хотя перед этим убрала Показывать общие итоги строк и столбцов((( и ничего не получается, руками группировать долго (таблицы большие), оч нужна помощь!!

Даша, здравствуй. Прости за долгое ожидание. Набери меня, разберем структуру твоей таблицы и постараемся найти решение.

Там где речь идет о «детальных данных» можно проще. Достаточно сделать дабл-клик на нужной цифре (двойной клик) и получим «умную»/»официальную» таблицу с разверткой (детализацией) этой цифры, которая будет располагаться на отдельном листе.

Константин, спасибо за уточнение. Добавил в текст.

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

Ирина, здравствуйте. Вносите изменения в исходную таблицу. После этого, правой копкой мыши на сводную таблицу и в контекстном меню выберите «Обновить»

Сводные таблицы Excel — Примеры создания

Сводные таблицы Excel – это особый тип построения, который подразумевает наличие функции моментального формирования отчета по документу.

С их помощью можно легко обобщить некоторые однотипные данные.

Содержание:

В программе Excel 2007 (MS Excel 2010|2013) сводная таблица используется, в первую очередь, для составления математического или экономического анализа данных.

Как сделать сводную таблицу в Excel

Анализ данных документа способствует более быстрому и правильному решению поставленных задач.

Табличный процессор Эксель может справиться даже с самыми объемными и сложными документами. Составить отчет и обобщить данные не составит труда для программы.

Чтобы создать саму простую таблицу-сводку, следуйте нижеприведенным указаниям:

• С помощью вкладки «вставка», которая находится в главном меню панели инструментов программы, выберите клавишу создания рекомендуемых сводных таблиц;

Выбор окна вставки для дальнейшего создания

• В открывшемся диалоговом окошке программы нажмите на понравившийся вам каркас таблички. Таким образом макет станет доступным для предварительного пользовательского просмотра.
  Выберите наиболее подходящий вариант, который в полной мере сможет отобразить всю информацию;

Диалоговое окно создания и просмотра предварительных вариантов

• Нажмите клавишу ОК, и программа сразу добавит выбранную таблицу (или пустой макет) на открытый лист документа. Также программа автоматически определит порядок расположения строк, согласно представляемой информации;
• Чтобы выделить элементы таблицы и упорядочить их вручную, отсортируйте содержимое. Также данные можно фильтровать. По сути, сводная табличка – это прототип небольшой базы данных.
  Фильтрация крайне необходима, когда появляется необходимость быстрого просмотра только определенных колонок и строчек. Ниже приведен пример сводной таблицы по продажам после фильтрования содержимого.
  Таким образом можно быстро просмотреть объемы продаж в отдельных регионах (в нашем случае, запад и Юг);

Пример отфильтрованной таблицы

Желательно пользоваться уже созданными макетами, однако, пользователь может выбрать пустую таблицу и наполнить ее самостоятельно.

В пустой шаблон необходимо добавить поля, формулы для расчета, фильтры.

Созданный пользовательский макет можно сохранить в список доступных шаблонов.

Пустая форма заполняется путем перетаскивания на отдельные области необходимых элементов данных.

Форма создания персональной пользовательской таблицы-сводки

Также можно создавать связанные таблицы-сводки на нескольких листах документа одновременны.

Таким образом можно анализировать данные всего документа или нескольких документов/листов сразу.

Проводить анализ внешних данных тоже можно с помощью сводных таблиц.

Советуем прочитать также эти статьи:

Сводные расчеты в Microsoft Excel — Формулы

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

В таблицу можно добавить, к примеру, элемент для вычисления комиссии для платежа или любой другой вид расчетного элемента.

Все формулы в столбики и строки добавляются с помощью поля «Вставка».

С помощью данной вкладки можно создать график, рассчитать элементы по формуле и построить гистограмму зависимости.

Вкладка сводных расчетов

Еще интересный материал на тему:

Функции для сводных таблиц в Экселе — Виды

Сводные таблицы в табличном процессоре могут выполнять следующие функции:

• Получение данных по запросу пользователя. Редактор документа может сослаться на отдельную ячейку таблички и получить из нее необходимые данные;
• Фильтрация исходной информации. Такая возможность позволяет быстро просмотреть искомую информацию для нескольких сущностей таблицы одновременно;
• Сводка по нескольким диапазонам сразу. Пользователь может выбрать как уже имеющиеся типы сводок, так и создать свою личную с помощь макросов;
• Группировка данных с определенным шагом. То есть редактор документа может объединять в отдельные группы информацию, которая соответствует определенному временному промежутку (данные за месяц, год и прочее);
• Создание полноценных отчетов с возможностью их распечатки. Данная функция позволяет сэкономить время на составление текстового отчета после создания таблицы.

Пример отчета по сводной таблице

Видеоролики:

Создание сводной таблицы в Excel

Как создать аналитический отчет сводной таблицы в Microsoft Excel. Понятно, пошагово и с подробностями.

Анализ данных с помощью сводных таблиц в Excel

Что такое сводные таблицы и как они работают. Быстрый анализ с помощью сводных таблиц. Как подготовить данные для сводных таблиц. Построение сводных таблиц и их настройка

Сводные таблицы в Excel 2010

Создание сводных таблиц в Excel 2010. Использование сводных таблиц для анализа данных.

Макет сводной таблицы в excel

Рассмотрим использование OLE Automation на примере.

• Модель (тип Справочник)
• Разнорядка (тип Документ).

Необходимо построить отчет по состоянию отгрузки моделей автомобилей в разрезе разнорядок.

Код будет выглядеть следующим образом:

//Создадим объект Excel
РабочаяКнига = СоздатьОбъект(«Excel.Application»);
//Ограничим книгу Excel одним листом
РабочаяКнига.SheetsInNewWorkbook = 1;
//Установим заголовок книги
РабочаяКнига.Caption = «Отчет по договорам отгрузки»;
//Создадим новую рабочую книгу
NewBook = РабочаяКнига.Workbooks.Add;

СписокСтолбцов = СоздатьОбъект(«СписокЗначений»);
СписокСтолбцов.ДобавитьЗначение(«Модель»);
СписокСтолбцов.ДобавитьЗначение(«Разнорядка»);
СписокСтолбцов.ДобавитьЗначение(«По договору»);
СписокСтолбцов.ДобавитьЗначение(«Отгрузили»);
СписокСтолбцов.ДобавитьЗначение(«Осталось»);

//Заполним наименование столбцов: Модель, Разнорядка, Количество
Для Столбец = 1 По СписокСтолбцов.РазмерСписка() Цикл
Ячейка = РабочаяКнига.Cells(1,Столбец);
Ячейка.Value = СписокСтолбцов.ПолучитьЗначение(Столбец);
КонецЦикла;

//Формируем запрос по регистру
//Создание объекта типа Запрос
Запрос = СоздатьОбъект(«Запрос»);
ТекстЗапроса =
«// <<ЗАПРОС(Сформировать)
|Период с ДатаНач по ДатаКон;
|Разнорядка = Регистр.УчетДоговоров.Разнорядка;
|Модель = Регистр.УчетДоговоров.Модель.Группа;
|Колво = Регистр.УчетДоговоров.Количество;
|Функция КолвоПриход = Приход(Колво);
|Функция КолвоРасход = Расход(Колво);
|Функция КолвоОст = КонОст(Колво);
|Группировка Разнорядка;
|Группировка Модель;
|»//>>ЗАПРОС
;
// Если ошибка в запросе, то выход из процедуры
Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
Возврат;
КонецЕсли;

НомерСтроки = 1;
Пока Запрос.Группировка(«Разнорядка») = 1 Цикл
Пока Запрос.Группировка(«Модель») = 1 Цикл
Модель = Запрос.Модель;
Разнорядка = Запрос.Разнорядка;
КолПоДоговору = Запрос.КолвоПриход;
КолОтгрузили = Запрос.КолвоРасход;
КолОсталось = Запрос.КолвоОст;
НомерСтроки = НомерСтроки + 1;
//Организум заполнение ячеек Excel
Для Ст = 1 По СписокСтолбцов.РазмерСписка() Цикл
Ячейка = РабочаяКнига.Cells(НомерСтроки,Ст);
Если Ст = 1 Тогда
Ячейка.Value = Модель;
ИначеЕсли Ст = 2 Тогда
Ячейка.Value = Строка(Разнорядка);
ИначеЕсли Ст = 3 Тогда
Ячейка.Value = КолПоДоговору;
ИначеЕсли Ст = 4 Тогда
Ячейка.Value = КолОтгрузили;
ИначеЕсли Ст = 5 Тогда
Ячейка.Value = КолОсталось;
КонецЕсли;
КонецЦикла;
КонецЦикла;
КонецЦикла;

//Теперь создаем сводную таблицу на основе выгруженных данных
СводнаяТаблица = РабочаяКнига.ActiveSheet.PivotTableWizard;

//Разворачиваем макет сводной таблицы
СводнаяТаблица.SmallGrid = 0;

//Теперь разнесем ячейки сводной таблицы
СводнаяТаблица.PivotFields(1).Orientation = 1; //Модель
СводнаяТаблица.PivotFields(2).Orientation = 3; //Разнорядка
СводнаяТаблица.PivotFields(3).Orientation = 4; //По договору
СводнаяТаблица.PivotFields(4).Orientation = 4; //Отгрузили
СводнаяТаблица.PivotFields(5).Orientation = 4; //Осталось
// Где:
// 1 — Строка
// 2 — Столбец
// 3 — Страница
// 4 — Данные (см. Рис1)

//Теперь немного отредактируем полученную таблицу
СводнаяТаблица.Format(1); // (см. Рис.2)

//Закрываем панель инструментов сводной таблицы
РабочаяКнига.CommandBars(«PivotTable»).Visible = 0;

РабочаяКнига.Visible = 1;
РабочаяКнига = 0;

Рис.1 Макет сводной таблицы.

Рис.2 Сформированный отчёт.

В итоге мы получили отчёт, который позволяет посмотреть как общее состояние отгрузки автомобилей, так и отгрузку в разрезе разнорядок.

Построение диаграммы на основе полученных данных показано в руководстве «1С:Предприятие. Описание встроенного языка. Часть 2», стр.903.