Консолидация (объединение) данных из нескольких таблиц в одну
Способ 1. С помощью формул
Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:
Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.
Самый простой способ решения задачи "в лоб" - ввести в ячейку чистого листа формулу вида
=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3
которая просуммирует содержимое ячеек B2 с каждого из указанных листов, и затем скопировать ее на остальные ячейки вниз и вправо.
Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:
=СУММ('2001 год:2003 год'!B3)
Фактически - это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.
Способ 2. Если таблицы неодинаковые или в разных файлах
Если исходные таблицы не абсолютно идентичны, т.е. имеют разное количество строк, столбцов или повторяющиеся данные или находятся в разных файлах, то суммирование при помощи обычных формул придется делать для каждой ячейки персонально, что ужасно трудоемко. Лучше воспользоваться принципиально другим инструментом.
Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:
Хорошо заметно, что таблицы не одинаковы - у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.
Для того, чтобы выполнить такую консолидацию:
- Заранее откройте исходные файлы
- Создайте новую пустую книгу (Ctrl + N)
- Установите в нее активную ячейку и выберите на вкладке (в меню) Данные - Консолидация (Data - Consolidate). Откроется соответствующее окно:
- Установите курсор в строку Ссылка (Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.
- Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:
Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels). Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.
После нажатия на ОК видим результат нашей работы:
Наши файлы просуммировались по совпадениям названий из крайнего левого столбца и верхней строки выделенных областей в каждом файле. Причем, если развернуть группы (значками плюс слева от таблицы), то можно увидеть из какого именно файла какие данные попали в отчет и ссылки на исходные файлы:
Ссылки по теме
- Макрос для автоматической сборки данных с разных листов в одну таблицу
- Макрос для сборки листов из нескольких файлов
Этим методом можно вытащить любой столбец и стоку, также копируем линки и меняем.
Но у меня вот какая проблема: мне по-работе приходится консолидировать три таблицы (2 столбца: артикулы и количества) с разными количествами строк, состав которых меняется (артикулы появляются и выпадают). Консолидировать их нужно тремя способами: 1ая+2ая, 1ая+3я и 1ая+2ая+3я. Создание исходных связей не подходит для автоматизации этого процесса, т.к. нужно "для шефа", без группировок. Думаю с формулировкой "сделай так как я привык/хочу/желаю" все знакомы...
Таблицы (консолидированные) лежат на разных листах, так что запрос конс-ии заполнять каждый раз не нужно. проводить самостоятельно эти три операции, даже с забитыми в файл диапазонами он не готов, ведь нужно поставить галочку и нажать ок...а "я хочу чтоб всё было готово и я просто смотрел".
Может быть есть какой-то способ, который решает такую ситуацию? Кроме смены работы или начальства, естественно. =)
К примеру задача: Собрать отчет с начала года по текущий момент из 20ти однообразных файлов отчетов, к примеру разных отделов. Отчеты ежедневно изменяются. Итоговый отчет должен обновляться по мере изменения данных в исходных отчётах.
Необходимо установить автоматическую связь импортируемых данных сначала в один файл, а после в этом файле создать сводную таблицу.
Пробовал использовать PLEX, но так и не понял как работать в динамике.
у меня 70 книг и из каждой книги с его определенных листов надо извлечь данные,
все 70 книг не могу открыть, программа дает сбой
по советуйте как я смогу выполнить задачу
спасибо
Подскажите, а можно ли консолидировать несколько таблиц с разных листов аналогичным образом (с помощью функции "консолидация"), но чтобы группировка при этом была не по наименованиям строк в таблице (они на листах разные), а по названиям листов? Столбцы таблиц при этом одинаковые на всех листах.
Только нужно исправить код таким образом, чтобы на листах имели право пристутствовать не только таблицы, которые я буду сводить, но и промежуточные расчёты.
Николай, подскажите, можно ли сделать это с помощью форматирования таблиц? Имеется в виду, что на каждом листе у меня будет отформатированная таблица и расчёты, на основании которых она построена. Можно ли тогда выгрузить в кэш только данные этих таблиц (которые имеют одинаковую шапку), например, перечислив их имена?
Здравствуйте!
Часто пользовался подсказками сайта и всегда находил то, что надо!!! А вот сейчас пришлось на форуме спрашивать. Вопрос такой:
есть таблица на несколько сотен строк и состоящая из двух колонок, в первой названия продуктов, которые могут повторяться, во второй колонке их стоимость (цифры), задача состоит в том, чтобы суммировать все продукты, в том числе повторяющиеся, т.е. должна получиться таблица из двух колонок, где в первой названия продуктов (по одному разу!), а во второй стоимость этого продукта (сумма из предыдущей таблицы по значению продукт). Надеюсь ясно объяснил:|См. табицы выше: первая исходная, вторая - итоговая, сводная.
Спасибо!
При консолидации данных из разных файлов, получаем сводную таблицу (пример 2). Однако для формирования сводной таблицы, необходимо заполнить пустые ячейки. В графе «Имя» данная задача решается просто - через автозаполнение вниз. А как быть с графой «Реклама» - где надо заполнить ячейки вверх?
Спасибо!
У меня не получалось выбрать таблички вместе с пунктом Квартал(ы), вот вышло когда я выбрал без Квартала.