6 примеров

МЕТОДИКА КОНСОЛИДАЦИИ ОДДС В ТАБЛИЧНОМ РЕДАКТОРЕ EXCEL

Чтобы создать сводный по группе компаний (ГК) отчет о движении денежных средств в файле Excel, нужно учитывать несколько важных моментов. Как правило, в группе компаний каждая бизнес-единица занимается отдельным видом бизнеса, поэтому справочник статей движения денежных средств у каждой из компаний группы будет сильно отличаться от остальных справочников. Представим распространенный вариант структуры бизнеса группы компаний:

• производственная компания;

• транспортная компания;

• торговый дом;

• компания — балансодержатель основных средств группы;

• управляющая компания группы.

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

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

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

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

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

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

Юридическая консолидация

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

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

Отличительными особенностями этой процедуры являются следующие:

1. Новый акт становится основным в своей сфере. В нём формулируются общие принципы, относящиеся к ней. Они определяют общий характер документа.
2. В этом документе регулируются разнообразные виды юридических отношений, которые приведены в единую систему.
3. Акт является сводным, включающем в себя содержание всех или большинства ранее принятых юридических документов. Положения, которые были в них рассмотрены увязываются между собой.
4. В результате на основе ранее принятых нормативных актов создаются тщательно продуманные нормы, которые носят устойчивый характер и станут основой для принятия решений на протяжении длительного времени.
5. В дальнейшем созданный акт становится основой для дальнейшего юридического развития.
6. В большинстве случаев акт, созданный в результате проведения консолидации, является объёмным и имеет сложную структуру.

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

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

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

Консолидация в бизнесе

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

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

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

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

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы
2. Создайте новую пустую книгу (Ctrl + N)
3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация(Data — Consolidate) . Откроется соответствующее окно:

Установите курсор в строку Ссылка(Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить(Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels)

Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

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

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

Наглядный пример выполнения процедуры консолидации

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

4.1. Указания к выполнению лабораторной работы

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

4.1.1. Связывание таблиц

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

                                             4.1.2.
Консолидация данных

При консолидации данных объединяются
значения из нескольких диапазонов данных.

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

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

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

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

4.1.2. Сводные
таблицы

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

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

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

Динамический именованный диапазон Excel

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

Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:

=$A$2:ИНДЕКС($B:$B;ПОИСКПОЗ(99^99;$B:$B))

Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.

Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.

Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.

Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:

= Excel.CurrentWorkbook()

После ввода формулы (нажатием Enter) Power Query обратится к текущей книге и выведет все объекты, среди которых есть и наш динамический диапазон ДинамОтчет.

Название запроса не подхватывается, поэтому придется изменить самостоятельно.

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

Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.

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

Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.

При торговле акциями

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

Более рискованные могут принести значительную прибыль, но их использование сопряжено со значительными возможными потерями. Существуют активы, которые считают надёжными, но малоприбыльными. Они позволят немного заработать, но не являются очень рискованными.

Доходность и риски инвестиционного портфеля;

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

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

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

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

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

Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

1. Заранее откройте исходные файлы
2. Создайте новую пустую книгу (Ctrl + N)
3. Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация(Data — Consolidate) . Откроется соответствующее окно:

Установите курсор в строку Ссылка(Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить(Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels)

Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

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

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

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

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

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

1. Открываем отдельный лист для консолидированной таблицы.

На открывшемся листе отмечаем ячейку, которая будет являться верхней левой ячейкой новой таблицы.

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

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

Представителям каких профессий пригодится функция консолидации данных

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

1. Бухгалтер.
2. Инвестор.
3. Трейдер.
4. Математик.
5. Ученый абсолютной любой специальности, требующей умения статистически обрабатывать информацию, начиная математиками и заканчивая социологами и психологами.

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

КАК БЫСТРО ПОЛУЧИТЬ СВОДНЫЕ ИТОГИ ПО НУЖНЫМ ПОЗИЦИЯМ

Бывает так, что сформированная таблица (например, ведомость товаров на складе) содержит большое количество позиций, многие наименования в ней повторяются.

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

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

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

Таблица 1 довольно большая — 511 строк. Обработать вручную такой массив показателей не так просто, а для полноценного анализа нужны сводные данные о продажах в разрезе продавцов. Чтобы получить их, ставим курсор на первую ячейку с информацией, которую требуется объединить. Это будет ячейка «Продавец».

Выбираем: Вставка → Сводная таблица. Откроется меню «Создание сводной таблицы» (рис. 1).

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

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

В нашем случае это поле «Продавец» и поле «Сумма продаж». Ставим соответствующие галочки и получаем следующий готовый результат (рис. 3).

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