Сводная таблица в нескольких диапазонах консолидации

Запустите мастера сводных таблиц (PivotTable) и сводных диаграмм (PivotChart), нажав Alt + D + P на клавиатуре. Выберите Multiple Consolidation Ranges (несколько диапазонов консолидации), затем Next.

ДОПОЛНИТЕЛЬНЫЙ МАТЕРИАЛ Инструмент консолидации бюджетов на базе Access
с пояснениями по ее использованию.
Покажем на примере, как можно быстро составить в Excel сводный бюджет продаж и график поступления денежных средств от реализации. Допустим, есть торговая компания, продающая комплектующие для инженерных систем. Реализацией занимаются две коммерческие дирекции. Первая отвечает за оптовое направление и ведет сбытовую деятельность в двух регионах. Вторая – за розничное направление, которое представлено только в одном регионе.
Каждая дирекция составляет полугодовой бюджет продаж в отдельном файле Excel. Данные о будущей реализации товаров представлены в разрезе наименований товаров и с разбивкой по месяцам, также указывается натуральный объем продаж и сумма.
Итак, необходимо создать консолидированный бюджет продаж со следующими аналитическими признаками:
календарный период (месяц);
дирекция (оптовая или розничная);
регион;
натуральные или стоимостные показатели.
А теперь пошагово соберем сводный бюджет с помощью Excel. Шаг 1. Доработка исходных данных
В Excel можно корректно объединять таблицы, представляющие собой непрерывный диапазон данных. У каждого из цифровых блоков, подлежащих консолидации, должны быть заголовки (подписи) как по вертикали, так и по горизонтали. Исходные бюджеты обеих коммерческих дирекций, как по региону № 1, так и по региону № 2, содержат по два диапазона данных – план продаж в натуральном выражении и в денежном. У каждого из них есть лишь заголовки по горизонтали (месяцы), а по вертикали – нет (названия товаров размещены намного левее).
Чтобы решить эту проблему, достаточно скопировать столбец с наименованиями товаров и вставить его непосредственно рядом с данными о планируемых объемах продаж в натуральном и стоимостном выражении. На рисунке 1 приведен пример доработанного таким образом бюджета продаж оптовой коммерческой дирекции по региону № 1 (изменения выделены зеленым фоном).
Рисунок 1. Доработанный бюджет продаж оптовой коммерческой дирекции
По аналогии придется доработать и все остальные бюджеты продаж, а именно план реализации оптовой коммерческой дирекции по региону № 2 и план розничной коммерческой дирекции по региону № 1. После этого можно приступать к консолидации. Шаг 2. Консолидация бюджетов
Сводный бюджет сделаем в отдельном файле Excel*. Создаваемая в нем таблица будет ссылаться на бюджеты коммерческих дирекций (они тоже представлены двумя отдельными файлами), доработанные на предыдущем шаге. Такая схема при необходимости поз¬волит коммерческим дирекциям независимо друг от друга корректировать исходные данные, и они будут автоматически учтены в консолидированном плане (достаточно кликнуть на сводной таблице правой кнопкой мыши и выбрать опцию «Обновить»).

23.01.2014, 20:45 сводная таблица по данным из нескольких листов. #2. Сводная "в нескольких диапазонах консолидации" попробуйте. Или сначала консолидацию, затем сводную. @anvor.

Итак, в Excel открыт новый пустой файл, а также еще два файла: один с планом продаж оптовой коммерческой дирекции по регионам № 1 и № 2, другой –розничной дирекции по региону № 1. Переходим в пока еще пустой файл, открываем меню «Данные» и кликаем по команде «Сводная таблица». Откроется окно мастера построения сводной таблицы. Здесь важно не просто нажать кнопку «Далее», а указать, что сводная таблица будет создаваться на основе данных, расположенных в нескольких диапазонах консолидации.
Теперь можно перейти к следующему окну мастера и выбрать команду «Создать поля страницы», где последовательно указать источники исходной информации для будущего сводного бюджета. Указать источники – значит установить курсор в поле «Диапазон», затем перейти в соответствующий файл с планом и выделить необходимый интервал ячеек (включая подписи строк и столбцов), нажать кнопку «Добавить». Исходной информацией будут, например, ячейки, содержащие план продаж в натуральном выражении оптовой коммерческой дирекции по региону № 1. Другой источник данных – план продаж той же дирекции, по тому же региону, но в стоимостном выражении. По аналогии еще два источника данных указывается для оптовых продаж (регион № 2) и два для розничных (регион № 2).
Для каждого источника данных нужно задать количество и имена полей сводной таблицы. Все это делается в том же окне, где указывались источники данных (см. рис. 2).
Рисунок 2. Выбор полей для сводной таблицы
По сути поля сводной таблицы – это аналитические признаки, в разрезе которых потом будет анализироваться консолидированный бюджет. В нашем примере всего три аналитических признака:
дирекция продаж («Розница» или «Опт»);
регион. Обозначим первый регион продаж как «Регион 1», а второй – «Регион 2»;
измерение. В примере данные приводятся в натуральном и денежном выражениях. Присвоим соответственно им такие метки – «Количество» и «Сумма».
К примеру, в бюджете оптовой коммерческой дирекции по региону № 1 в качестве источников исходных данных будут указаны планы продаж: один в натуральном, а другой в денежном выражении. Соответственно, аналитика для первого плана – «Регион 1», «Опт», «Количество», для второго – «Регион 1», «Опт» и «Сумма». Аналогичным образом задаются аналитические признаки для всех остальных исходных данных.

Сводная таблица из нескольких диапазонов. Posted on 21.07.2011 by admin.  Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция "построить сводную по нескольким диапазонам консолидации".

После этого можно кликнуть кнопку «Далее», и последнее, что останется сделать, это определить место расположения сводной таблицы – новый или текущий лист. Поскольку все действия производились в чистом файле, то можно выбрать «Текущий лист». Консолидированный бюджет в первом приближении готов (см. рисунок 3).
Рисунок 3. Первый вариант консолидированного бюджета
Шаг 3. Настройка сводного бюджета
Работать с получившимся консолидированным бюджетом будет удобнее, если дать привычные названия полям сводной таблицы. Например, переименовать поле «Столбец» в «Месяц» (щелкнуть по имени поля правой клавишей мыши, выбрать в контекстном меню команду «Параметры поля» и в открывшемся окне в строке сверху набрать «Месяц»). По аналогии название поля «Строка» будет оправданно поменять на «Товар», «Страница 1» – «Регион», «Страница 2» – «Дирекция», а полю «Страница 3» присвоить название «Измерение» (см. рисунок 4).
Рисунок 4. Финальный вариант консолидированного бюджета
Обратите внимание, что по умолчанию сводная таблица суммирует все исходные данные, невзирая на то, указаны они в натуральных единицах измерения или в рублях. Чтобы избежать этого, необходимо выбрать в поле «Измерение» (сверху над сводным бюджетом) нужный режим представления продаж (в примере на рис. 5 выбрано измерение «Сумма» – в стоимостном выражении) и работать только с натуральным или денежным планом.
На этом преобразования формы консолидированного бюджета не заканчиваются. В созданной сводной таблице нашел отражение пока только один аналитический признак исходных бюджетов – «Товар», но на практике их бывает гораздо больше. В примере, в исходных планах коммерческих дирекций, присутствовала аналитика по товарным группам. Ее можно включить в сводный бюджет при помощи команды «Группировать». Для этого нужно выделить мышкой диапазон ячеек, которые надо объединить в товарную группу (например, товары, входящие в группу «Трубы»), потом вызвать правой кнопкой мыши контекстное меню и выбрать команду «Группировать».
Заголовок появившейся группы автоматически получает имя «Группа 1». Чтобы изменить его на «Трубы», нужно просто ввести в ячейку, содержащую это имя, новое название. На этом сводный бюджет можно считать законченным (см. рис. 5). Или почти законченным. Шаг 4. Добавление расчетных полей
При построении консолидированного бюджета зачастую возникает необходимость добавления расчетных показателей (в строках или столбцах). Сводные таблицы Excel поз¬воляют решить эту задачу. Покажем, как дополнить построенный консолидированный бюджет продаж планом поступления денежных средств за отгруженную продукцию.
Предположим, что в торговой компании, о которой шла речь ранее, 60 процентов продукции отпускается клиентам на условиях предоплаты, 30 процентов – с отсрочкой платежа до месяца и оставшиеся 10 процентов – с отсрочкой до двух месяцев. К примеру, выручка от реализации в январе будет поступать на счета компании следующим образом: 60 процентов средств в том же месяце (в январе), 30 процентов в феврале, 10 процентов в марте.
На это распределение можно посмотреть и с другой стороны – относительно месяца поступления денег. К примеру, в марте на счета компании должны поступить: 60 процентов от выручки марта, 30 процентов от выручки февраля, 10 процентов от выручки января. Соответственно формула следующая:
Поступление денежных средств в марте = (0,6 Выручка в марте + 0,3 Выручка в феврале + + 0,1 Выручка в январе) (1 + НДС).
И все, что потребуется сделать для формирования ориентировочного плана поступления средств на основе сводного бюджета продаж, это внести в полученную на предыдущем шаге таблицу такие формулы.
Для добавления расчетных значений в сводную таблицу понадобится панель инструментов «Сводная таблица» (ее можно вывести на экран с помощью команды меню «Вид» – «Панели инструментов» – «Сводные таблицы»). Сначала потребуется выделить диапазон данных для расчетов. В нашем примере – это месяц, для которого определяется поступление денежных средств. Пусть это будет март. Затем на упомянутой выше панели инструментов нажать кнопку «Сводная таблица» и выбрать команду «Формулы» – «Вычисляемый объект» (см. рис. 5).
Рисунок 5. Добавление формул в сводную таблицу
В появившемся диалоговом окне «Вставка вычисляемого объекта» в поле «Месяц» присвоить имя новому столбцу, в котором будут выводиться суммы поступлений денежных средств (в примере имя – «Март_ДС») и ввести формулу, приведенную выше. Обратите внимание: в качестве аргументов формулы будут использоваться названия столбцов сводной таблицы, содержащих помесячные данные о продажах (в примере они обозначены как «Май», «Июнь» и пр.). В диалоговом окне они перечислены под заголовком «Элементы». Аналогично задается расчет входящих денежных потоков и для других месяцев.
Стоит отметить, что в Excel вычисляемые объекты сводных таблиц (те самые дополнительные расчетные значения) по умолчанию участвуют в подведении общих итогов по строкам или столбцам. Иначе говоря, в примере выручка и поступления денежных сре

В Excel 2003 это можно было сделать во время создания простой сводной (Меню- Данные- Отчет сводной таблицы и диаграммы), выбрав в меню "Из нескольких диапазонов консолидации".


2007: Вставка - сводная таблица - в нескольких диапазонах консолидации24 июня 2010

Использование сводной таблицы для консолидации диапазонов. May 23rd, 2012 admin. Мастер сводных таблиц и диаграмм позволяет консолидировать данные из нескольких диапазонов Excel.27 мая 2012


Письмо о защите персональных данных

В целях совершенствования защиты персональных данных работников МОУ СОШ №6 с  от 27.07.2006 № 152-ФЗ «О персональных данных», и рекомендаций Рособрнадзора (письмо от 29.07.2009 № 17-110 «Об обеспечении защиты

<Письмо> Рособразования от 29.07.2009 N 17-110 «Об обеспечении защиты персональных данных»  Положение о защите персональных данных сотрудника предприятия 29 Октября 2014, 20:47, вопрос №600504 1 ответ.

Подробнее...

Обязательная оценка в конкурсном производстве

Конкурсное производство представляет завершающую процедуру банкротства коммерческой организации-должника в российском правовом поле. Конкурсное производство — процедура, применяемая в деле о банкротстве к должнику

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

Подробнее...

Документооборот в бухгалтерском учете 2014

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

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

Подробнее...

Колледж при института управления и права

Зарегистрироваться представителем организации. Организация ЭКОНОМИКО-ПРАВОВОЙ КОЛЛЕДЖ ПРИ ИНСТИТУТЕ ЭКОНОМИКИ, УПРАВЛЕНИЯ И ПРАВА расположена в разделе Колледжи.

Экономико-правовой колледж при институте экономики, управления и права (Г. Казань.

Подробнее...