Консолидация данных – функция в Excel, благодаря которой у пользователей появляется возможность объединить данные из нескольких таблиц в одну, а также совместить листы, которые находятся в одном или разных файлах, в один.
Официальные требования к таблицам, которые необходимы для выполнения консолидации
Опцией под названием «Консолидация» не получится воспользоваться, если таблицы не соответствуют требованиям. Чтобы успешно произвести процедуру объединения данных, необходимо:
- проверить таблицу на наличие пустых строк/столбиков, и удалить их, если они есть;
- использовать одинаковые шаблоны;
- следить за наименованием столбцов, они не должны различаться.
Основные способы консолидации в программе Excel
При сведении данных из разных однотипных отчетов, таблиц, диапазонов в один общий файл можно использовать несколько разных способов. Ниже будет рассмотрено два основных метода сведения данных: по положению и по категории.
- В первом варианте данные в исходных областях находятся в том же порядке, в котором применяются идентичные метки. Выполните сведение по положению, чтобы объединить данные из 3-4 листков, которые базируются на одном шаблоне, к примеру, финансовая отчетность подходит для проверки этого способа.
- Во втором варианте: данные располагаются в произвольном порядке, но имеют идентичные метки. Выполните консолидацию по категории, чтобы объединить данные из нескольких листков с различными макетами, но идентичными метками данных.
Важно! Этот способ имеет много общего с формированием сводной таблицы. Однако в сводной таблице можно реорганизовывать категории.
- Также существует третий способ объединения данных — это консолидация с помощью формул. Правда, его редко используют на практике, в связи с тем, что он отнимает у пользователя довольно много времени.
Пошаговая инструкция по выполнению консолидации в Excel
Далее будет рассмотрен самый простой и быстрый способ консолидации.
Итак, как объединить несколько таблиц:
- Сначала необходимо создать новый лист, после чего программное обеспечение автоматическим образом добавит его с правой стороны. Если необходимо, можно перетащить лист в другое место (к примеру, в конец перечня) посредством левой кнопки мышки.
- Добавленный лист встаньте в ячейку, с которой собираетесь работать. Потом перейдите во вкладку «Данные», найдите раздел «Работа с данными», нажмите на пункт под названием «Консолидация».
- На мониторе появится маленькое окно с настройками.
- Далее нужно будет выбрать подходящую функцию для объединения данных.
- После выбора функции перейдите к полю «Ссылка», щелкнув мышкой внутри него. Здесь вам необходимо будет поочередно выбрать диапазон ячеек. Для этого сначала переключитесь на лист с первой табличкой.
- Потом выделите табличку вместе с шапкой. Убедитесь, что все сделали правильно, потом кликните на значок «Добавить». Кстати, обновить/сменить координаты можно и собственноручно посредством клавиатуры, но это неудобно.
- Для выбора диапазона из нового документа сначала откройте его в Эксель. После этого запустите процесс объединения в первой книжке и переключитесь на вторую, выберите в ней подходящий лист, а после этого выделите определенную часть ячеек.
- В итоге в «Перечне диапазонов» сформируется первая запись.
- Вернитесь в поле «Ссылка», уберите все сведения, которые в нем содержатся, потом добавьте в перечень диапазонов координаты остальных табличек.
- Поставьте галочки напротив следующих функций: «Подписи верхней строки», «Значения левого столбика», «Сформировать связи с исходными данными».
- После нажмите «OK».
- Эксель выполнит процедуру и создаст новый документ согласно установленным параметрам и выбранным функциям.
В примере было выбрано формирование связи, поэтому на выходе была произведена группировка данных, которая помогает отображать/скрывать детализацию.
Дополнительная информация об использовании диапазонов, добавлении и удалении ссылок
- Чтобы использовать новый диапазон для сведения данных, потребуется выбрать опцию «Консолидация», нажать мышкой на поле «Ссылка» и выделить диапазон или вставить ссылку. После нажатия на клавишу «Добавить» ссылка появится в перечне диапазонов.
- Чтобы убрать ссылку, выделите ее и нажмите на надпись «Удалить».
- Чтобы изменить ссылку, выделите ее в перечне диапазонов. Она появится в поле «Ссылка», где ее можно будет обновить. После проделанных манипуляций нажмите на клавишу «Добавить». Потом уберите старый вариант видоизмененной ссылки.
Консолидация данных помогает объединить нужную информацию, которая присутствует не только в различных таблицах и листах, но и в других файлах (книгах). Процедура сведения не занимает много времени и ее несложно запустить, используя пошаговую инструкцию.
