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

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

Консолидация данных в Excel бюджетных заявок

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

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

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

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

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

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

Консолидация данных в Excel бюджетных заявокКонсолидация данных в Excel бюджетных заявок

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

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

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

Консолидация данных в Excel бюджетных заявок

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

Консолидация данных в Excel бюджетных заявок

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

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

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

Консолидация данных в Excel бюджетных заявок

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

Консолидация данных в Excel бюджетных заявок

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

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

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

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

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

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

Консолидация данных в Excel бюджетных заявок

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

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

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

Консолидация данных в Excel бюджетных заявок

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

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

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

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



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

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

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Живое общение с редакцией

А еще...


Рассылка




© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
×
Зарегистрируйтесь на сайте,
чтобы скачать образец документа

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

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

Еще Вы сможете бесплатно:
Скачать надстройку для Excel. Узнайте риск налоговой проверки в вашей компании Прочитать книгу «Запасной финансовый выход» (раздел «Книги»)

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