Финансовый директор

Консолидация данных в Excel

339482
Салостей Станислав
автор и руководитель проекта Finalytics.PRO, бывший финансовый директор
Консолидация данных в Excel поможет быстро и без проблем собрать данные из разных файлов в один. Разберем, как извлечь данные, если книги Excel с исходной информацией закрыты.

Picture

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

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

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

Что такое консолидация данных в Excel

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

Мы можем консолидировать данные из «смеси» файлов Excel: стандартных, с расширением .XLSX, с поддержкой макросов .XLSM, бинарных .XLSB и старых файлов с расширением XLS.
При выполнении консолидации в Excel необходимо учитывать требование к консолидируемым таблицам, а именно: первый столбец и первая строка должны содержать подписи данных, все остальное – числовые значения.

Требования к консолидируемым таблицамНекорректная консолидируемая таблица

Общие шаги по консолидации данных в Excel

Шаг №1. Открыть все файлы, которые требуется консолидировать (если требуемые данные находятся в разных файлах), если все в одном файле, то, соответственно, других файлов открывать не нужно.

Шаг №2. Перейти в файл, в который «собираются» данные и выполнить следующие действия:
вкладка ДАННЫЕ → Консолидация

Консолидация

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

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

Шаг №4. Нажать «Ok» и получить консолидированную таблицу.

Пример консолидации в Excel

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

Коммерческие расходы

Второй файл, соответственно, расходы на управление.

Административные расходы

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

  • В бюджетах разное количество строк. И это нормально, главное, при выделении таблиц указать их все.
  • Бюджеты содержат три методологически одинаковых статьи: канцтовары, оплата труда, амортизация. Ошибка в том, что в бюджете административных расходов написано «зарплата». Дальше мы увидим, что всё должно называться одинаково.
  • В шапке тоже не все верно: во-первых, вместо «фев» в административных расходах указано полное наименование месяца, во-вторых, «апр» в бюджете коммерческих расходов идет с пробелом на конце.
  • В административных расходах амортизация за июль стоит в текстовом формате.

Проводим консолидацию данных в Excel. Для этого встаем на ячейку в файле, куда мы собираем таблицы.
Идем на вкладку ДАННЫЕ → Консолидация. Функцию консолидации оставляем «Сумма» (если вам не нужно что-нибудь другое). И мышкой указываем первую из таблиц, значения которой нам требуется собрать – Бюджет административных расходов.

Бюджет административных расходов
Затем, нужно обязательно нажать кнопку «Добавить», чтобы выделенный диапазон попал в поле консолидации. После этого активируем следующий файл и выделяем следующую таблицу. И так, до тех пор, пока не выделим все нужные файлы. У нас всего 2 файла, мы их выделили и теперь готовы построить консолидированный отчет.

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

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

Пример отчета в Excel

Кстати, иногда необходимо обновить отчет. Как это сделать? Очень просто: нужно встать на первую ячейку, откуда он начинается (в нашем случае – это A3). Пройти: ДАННЫЕ → Консолидация → Ok (список диапазонов уже не исчезает, снова его создавать не нужно).

Подписи появились. Всё здорово, НО: все текстовые отличия задвоились! Исправим исходные файлы и повторим все действия: Данные → Консолидация → Ставим галочку еще и на «Создавать связи…» → Ok.

Все исправилось. А благодаря последней галочке, слева появилась группировка данных. А в области значений появились ФОРМУЛЫ, ссылающиеся на лист с исходными данными.
 

Группировка данных

Так, легко и быстро мы можем «стягивать» информацию из разных файлов и разных диапазонов в один. Если не ставить галочку «Создавать связи…», то данные нужно обновлять с помощью повторного выполнения консолидирующих шагов, но уже без указания диапазонов: ДАННЫЕ → Консолидация → Ok. Перед этим важно встать на начало диапазона, иначе данные добавятся туда, где у вас стоит активная ячейка.

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

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

Все статьи рубрики Excel для финансиста.



Ваша персональная подборка

    Подписка на статьи

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

    
    ×
    Чтобы скачать документ, зарегистрируйтесь на сайте!

    Это бесплатно и займет всего 1 минуту.

    У меня есть пароль
    напомнить
    Пароль отправлен на почту
    Ввести
    Введите эл. почту или логин
    Неверный логин или пароль
    Неверный пароль
    Введите пароль
    Я тут впервые
    И получить доступ на сайт Займет минуту!
    Внимание! Вы читаете профессиональную статью для финансистов.
    Зарегистрируйтесь на сайте и продолжите чтение!

    Это бесплатно и займет всего 1 минуту.

    У меня есть пароль
    напомнить
    Пароль отправлен на почту
    Ввести
    Введите эл. почту или логин
    Неверный логин или пароль
    Неверный пароль
    Введите пароль
    Я тут впервые
    И получить доступ на сайт Займет минуту!
    Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.