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

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

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

consolid1.gif

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

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

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

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

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

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

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

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

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

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

consolidation2.png

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

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

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

    consolidation3.png

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

    consolidation4.png

 

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

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

consolidation5.png

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

consolidation6.png

Ссылки по теме

 


29.05.2013 10:31:37
хорошая вещь, спс!
01.06.2013 17:08:06
Насколько я понял, текстовое содержимое в исходниках столбец "Компания" не попадает в консолидацию?
01.06.2013 20:41:43
Совершенно верно, поскольку математическая операция "сумма".
10.07.2013 15:19:37
Николай, подскажите как свести три таблицы "командировки" из примера для скачки? То есть иметь возможность просмотреть затраты Иванова, Петрова, Сидорова.
23.07.2013 09:20:34
А видео смотрели? Там же подробно этот процесс показан.
24.10.2014 11:12:37
Не могли бы Вы помочь мне с моим вопросом изложенным в последнем комментарии?
24.08.2013 21:48:22
У меня есть один файл, но этот файл должны заполнять 3 разных отдела. Т е в листе есть три столбца, каждый столбец на каждый отдел. Можно сделать так чтобы было три одинакового файла, каждый отдел заполнял свою таблицу, а после все данные собирались в четвертый единый файл? Спасибо
18.11.2013 16:18:51
Чтобы подтянуть в таблицу "компания", необходимо в файле, где Вы выполнили консолидацию, скопировать линки из колонки D в колонку С (у меня с 2-й по 45 строку), а потом с помощью Ctrl+H заменить $C$ на $B$, затем удалить ошибки от формулы SUM (СУММА) с помощью автофильтра.
Этим методом можно вытащить любой столбец и стоку, также копируем линки и меняем.
21.05.2014 18:00:19
Очень нужная штука, но ОЧЕНЬ не хватает функции "Вычесть" (((
22.10.2014 16:31:15
Николай, огромное спасибо, очень толково объясняете!

Но у меня вот какая проблема: мне по-работе приходится консолидировать три таблицы (2 столбца: артикулы и количества) с разными количествами строк, состав которых меняется (артикулы появляются и выпадают). Консолидировать их нужно тремя способами: 1ая+2ая, 1ая+3я и 1ая+2ая+3я. Создание исходных связей не подходит для автоматизации этого процесса, т.к. нужно "для шефа", без группировок. Думаю с формулировкой "сделай так как я привык/хочу/желаю" все знакомы...

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


Может быть есть какой-то способ, который решает такую ситуацию? Кроме смены работы или начальства, естественно. =)
26.01.2015 19:42:49
Вадим, попробуйте сервис All-Link, вероятно это как раз то, что вам нужно. Сервис только развивается и для пользователей сейчас полностью бесплатен.
11.11.2014 19:00:52
Как осуществить консолидацию динамических диапазонов?
К примеру задача: Собрать отчет с начала года по текущий момент из 20ти однообразных файлов отчетов, к примеру разных отделов. Отчеты ежедневно изменяются. Итоговый отчет должен обновляться по мере изменения данных в исходных отчётах.

Необходимо установить автоматическую связь импортируемых данных сначала в один файл, а после в этом файле создать сводную таблицу.
Пробовал использовать PLEX, но так и не понял как работать в динамике.
21.08.2015 12:46:32
Очень полезная функция,
у меня 70 книг и из каждой книги с его определенных листов надо извлечь данные,
все 70 книг не могу открыть, программа дает сбой
по советуйте как я смогу выполнить задачу

спасибо    
23.05.2016 21:35:44
При таком количестве книг вам стоит уже про макросы думать - формулами или консолидацией такое количество не собрать, ресурсов не хватит.
23.05.2016 10:07:53
Супер!
Подскажите, а можно ли консолидировать несколько таблиц с разных листов аналогичным образом (с помощью функции "консолидация"), но чтобы группировка при этом была не по наименованиям строк в таблице (они на листах разные), а по названиям листов? Столбцы таблиц при этом одинаковые на всех листах.
23.05.2016 21:34:17
А если добавить к каждой таблице столбец с именем листа слева и потом по нему консолидировать?
26.05.2016 15:35:24
Мне скорее подходит что-то типа этого: http://www.planetaexcel.ru/techniques/8/133/
Только нужно исправить код таким образом, чтобы на листах имели право пристутствовать не только таблицы, которые я буду сводить, но и промежуточные расчёты.
Николай, подскажите, можно ли сделать это с помощью форматирования таблиц? Имеется в виду, что на каждом листе у меня будет отформатированная таблица и расчёты, на основании которых она построена. Можно ли тогда выгрузить в кэш только данные этих таблиц (которые имеют одинаковую шапку), например, перечислив их имена?
10.06.2016 22:26:57
Здравствуйте,а подскажите пожалуйста как свести 5 таблиц состоящих из 40000 строк в 1 (таблицы одинаковые, люди вносят данные по своим позициям)
13.03.2017 19:55:26
Мария, меня этот вопрос тоже интересует. Подскажите, Вы нашли решение этому вопросу, который сами задали???
25.07.2016 20:22:29
Здравствуйте! Подскажите, как можно консолидировать сгруппированные данные, чтобы не потерялись группировки?
18.02.2018 21:48:43
А как быть, если нужно консолидировать не ограниченный диапазон, а, например, число столбцов одинаковое, а вот список строк разный?
30.03.2018 16:51:06
 
фуфлин10
витамин зю5
фуфлин9
витамин зю11
витамин зю7
озверин155
офигей-трава1
офигей-трава5
от жадности8
офигей-трава14

фуфлин 19
витамин зю 23
озверин155
офигей-трава20
от жадности8

Здравствуйте!
Часто пользовался подсказками сайта и всегда находил то, что надо!!! А вот сейчас пришлось на форуме спрашивать. Вопрос такой:
есть таблица на несколько сотен строк и состоящая из двух колонок, в первой названия продуктов, которые могут повторяться, во второй колонке их стоимость (цифры), задача состоит в том, чтобы суммировать все продукты, в том числе повторяющиеся, т.е. должна получиться таблица из двух колонок, где в первой названия продуктов (по одному разу!), а во второй стоимость этого продукта (сумма из предыдущей таблицы по значению продукт). Надеюсь ясно объяснил:|См. табицы выше: первая исходная, вторая - итоговая, сводная.
Спасибо!
 тут сказано про хитрый символ * звездочка, мол она указывает все листы кроме текущего, а каким символом указать все листы слева. Т.е. что бы она на листе3 она суммировала значения ячейки В8 с листов1,2,3, даже если на листах4,5,6 тоже есть ячейка В8 и там тоже есть данные. И эта же формула на листе8 считала сумму всех ячеек В8 с листов №1 по №8
12.07.2018 21:15:48
Здравствуйте!
При консолидации данных из разных файлов, получаем сводную таблицу (пример 2). Однако для формирования сводной таблицы, необходимо заполнить пустые ячейки.  В графе «Имя» данная задача решается просто - через автозаполнение вниз. А как быть с графой «Реклама» - где надо заполнить ячейки вверх?
Спасибо!