Adelitusn.ru

ПК и Техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Создание сводной таблицы в 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.

Как создать сводную таблицу в excel 2010

Обрабатывать большие объемы информации и составлять сложные многоуровневые отчеты достаточно непросто без использования средств автоматизации. Excel 2010 как раз и является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных (Pivot table).

Сводная таблица в Excel 2010 используется для:

Читайте так же:
Как отменить или прервать загрузку на Андроиде


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

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

Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»

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

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

2. Откройте вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица».

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

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

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

Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм»

Мастер сводных таблиц в Excel 2010 совсем не многим отличается от аналогичного инструмента в Excel 2007. Для создания сводных таблиц с его помощью выполните следующее. 1. Кликните по иконке мастера в панели быстрого допуска. В диалоговом окне поставьте переключатель на нужный вам пункт списка источников данных:
«в списке или базе данных Microsoft Excel» — источником будет база данных рабочего листа, если таковая имеется; «во внешнем источнике данных» — если существует подключение к внешней базе, которое нужно будет выбрать из доступных; «в нескольких диапазонах консолидации» — если требуется объединение данных из разных источников; «данные в другой сводной таблице или сводной диаграмме» — в качестве источника берется уже существующая сводная таблица или диаграмма.

2. После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)».


  • Если в качестве источника выбран текущий документ, где уже есть простая таблица с элементами будущего отчета, задайте диапазон охвата — выделите курсором нужную область. Далее выберите место размещения таблицы — на новом или на текущем листе, и нажмите «Готово». Сводная таблица будет создана.
  • Если же необходимо консолидировать данные из нескольких источников, поставьте переключатель в соответствующую область и выберите тип отчета. А после нужно будет указать, каким образом создавать поля страницы будущей сводной таблицы: одно поле или несколько полей.

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

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

Создание отчетов с помощью Сводных таблиц (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, так и в другой базе данных, или другом документе.

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

Теперь о требованиях, которых необходимо придерживаться при ее создании в Эксель. Исходные данные должны быть представлены в виде таблицы, у которой должно быть название для столбцов, то есть шапка. Для этих целей отлично подойдет создание умной таблицы в Excel. Также она не должна содержать пустые строки, столбцы и ячейки. Не должно быть скрытых строк/столбцов и объединенных ячеек.

Как создать

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

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

Чтобы сделать сводную таблицу, выделяем любую ячейку из исходной, затем переходим на вкладку «Вставка» и нажимаем на кнопочку «Сводная таблица» .

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

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

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

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

Как расставить поля

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

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

В качестве строк выберем «Товары» . Аналогичным образом перетягиваем нужное поле в область «Названия строк» .

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

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

В область «Названия столбцов» перетянем поле «Дата» . Чтобы отобразить продажи не за каждый день, а, к примеру, по месяцам, кликните по любой дате правой кнопкой мыши и выберите из меню «Группировать» .

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

Таблица примет следующий вид.

Теперь в область «Значения» перетянем поле «Сумма» .

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

Чтобы это исправить, в сводной выделяем нужный диапазон ячеек и нажимаем правую кнопку мыши. дальше выберите из меню «Числовой формат» .

В следующем окне выбираем «Числовой» , можете поставить галочку в поле «Разделитель групп разрядов» и нажмите «ОК» .

Как работать с данными

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

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

В области «Значения» можно настроить параметры для поля. В примере выводится сумма значений: Рома в феврале продал рубашек на сумму 1 800.00. давайте посмотрим, сколько это штук. Кликаем левой кнопкой мышки по строке «Сумма по полю…» и выбираем из меню «Параметры полей значений» .

В следующем окне из списка выбираем «Количество» , выберите то, что подходит конкретно в вашем случае, и нажимаем «ОК» .

Теперь, смотря на значения, можно понять, что Рома в феврале продал две рубашки.

Теперь сделаем, чтобы общий фильтр для таблицы был по месяцам. Меняем области: в «Фильтр отчета» перетаскиваем поле «Дата» , в «Названия столбцов» – «Продавец» .

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

Обратите также внимание на ленту. При выделении любой ячеек из сводной таблицы, на ней появляется вкладка «Работа со сводными таблицами» с двумя подвкладками «Параметры» и «Конструктор» .

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector