Скидка на подписку 30% | 8(800)550-07-98
Журнал

Консолидация в Excel: пошаговая инструкция

  • 18 июня 2019
  • 198
Консолидация в Excel: пошаговая инструкция

MS Excel значительно упрощает расчеты. С ее помощью можно, например, найти нужные данные среди большого объема информации, вычислить сумму показателей. А еще программа позволяет объединить, данные. Узнайте, как это сделать.

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

Есть два способа, как сделать консолидацию данных в Excel. Во-первых, можно объединить данные по расположению. Этот способ подойдет, если необходимо соединить информацию из нескольких листов в программе Excel. Важно, чтобы таблицы на разных листах были идентичными: одинаковые заголовки в таблице и пр.

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

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

Подготовлено по материалам
"Системы Финансовый директор"

Консолидация данных с нескольких листов

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

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

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

Как упростить консолидацию многостраничных отчетов в Excel

Скачать модели в Excel

 Чтобы объединить данные из всех таблиц, используем одну из самых простых функций – «СУММ». Сложим показатель по зарплате для одного отдельного сотрудника. Формулу условно можно представить так:

= Лист 1 + Лист 2 +…

Или для нашего примера: =январь!B2+февраль!B2+март!B2+апрель!B2+май!B2

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

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

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

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

Для пример возьмем следующую ситуацию. Три сотрудника составили план по рекламе: наименование компании, у которой купили ее, вид рекламы и стоимость. Каждый прислал отдельный файл. Необходимо объединить эти сведения в одной книге. Разберем пошаговую инструкцию, как сделать консолидацию в Excel. 

В этом примере будем использовать именно специальную функцию, которую предлагает Excel – «Консолидация». В прошлом же примеры для объединения мы использовали формулу.

Шаг 1.

Необходимо открыть вкладку «Данные» на панели задач в программе Эксель. С правой стороны будет кнопка «Консолидация».

Шаг 2.

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

Программа предложить выбрать нужный файл. И уже в нем необходимо будет выделить нужный диапазон. Операцию дублируем для остальных двух файлов.

Консолидация в Excel: пошаговая инструкция

Шаг 3.

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

Консолидация данных с помощью формул в Excel

Еще один способ объединить данные – применить специальную функцию «СУММ». Логику того, как собрать информацию в одном месте, мы уже показали в первом примере, когда говорили о консолидации данных с нескольких листов. Напомним, что в общем виде формула выглядит так:

=СУММ(число 1; число 2;…)

Кроме того, разработчики программы в качестве альтернативы предлагают создавать сводные таблицы. Это еще один вариант, как объединить данные. Путь к этой опции следующий: «Вставка»/ «Сводная таблица».

Этот вариант объединения информации называется консолидация по категориям.

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

"Финансовый директор" - единственный профессиональный ресурс по управлению финансами компании. Материалы подготовлены финансовыми директорами и экспертами. Пройдите короткую регистрацию и получите доступ

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

Мы ещё не знакомы?
Давайте познакомимся!

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

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

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

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

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

Мы подобрали для вас книги:

Как решать нерешаемые задачи, посмотрев проблему с другой стороны Управление финансовой службой по KPI Как не потерять на налогах: проверенные способы
Скачать бесплатно Скачать бесплатно Скачать бесплатно
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.