Консолидация (объединение) данных из нескольких таблиц в одну
Способ 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) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.
После нажатия на ОК видим результат нашей работы:
Наши файлы просуммировались по совпадениям названий из крайнего левого столбца и верхней строки выделенных областей в каждом файле. Причем, если развернуть группы (значками плюс слева от таблицы), то можно увидеть из какого именно файла какие данные попали в отчет и ссылки на исходные файлы:
Ссылки по теме
- Макрос для автоматической сборки данных с разных листов в одну таблицу
- Макрос для сборки листов из нескольких файлов
Как часто, работая с данными, вам удается уместить всю необходимую для анализа информацию в одну таблицу? Скорее всего, никогда.
Чаще всего, нужные нам данные находятся в разных файлах и мы сталкиваемся с необходимостью сопоставить или объединить их из нескольких таблиц в одну.
В этой статье мы рассмотрим наиболее эффективные способы как это сделать.
Как объединить две таблицы Excel с помощью функции ВПР
Если вы хотите сопоставить две таблицы по данным из одного столбца, то тут лучше всего подойдет функция ВПР.
Представим, у нас есть две таблицы. В основной таблице у нас есть данные с наименованиями товаров, продавцов и количестве продаж, а во второй с ценами на эти товары. Мы хотим подставить данные с ценами на товары в таблицу с данными о продажах и посчитать выручку на каждого из продавцов.
Для этого в ячейку D2 вставим формулу с функцией ВПР:
- B2 – ячейка с названием товара, которое мы ищем в таблице с ценами;
- $G$2:$H$4 – диапазон ячеек таблицы с наименованием товаров и ценами. В столбце G содержатся названия товаров, по которым функция осуществляет поиск. В столбце H отражены цены, которые функция ВПР будет подставлять в нашу таблицу. Диапазон ячеек включает в себя значки $, с их помощью диапазон зафиксирован и не будет изменяться при протягивании формулы по другим ячейкам.
- 2 – номер столбца в диапазоне данных с ценами на товары, которые мы хотим подставить в нашу таблицу.
- 0 – точность совпадения данных. Ставим “0”, так как нам необходимо точное совпадение.
Протянем полученную формулу на все строки столбца и получим таблицу с расчетом выручки по каждому товару, для каждого продавца.
Консолидация данных в программе Microsoft Excel
При работе с однотипными данными, размещенными в разных таблицах, листах или даже книгах, для удобства восприятия лучше собрать информацию воедино. В Microsoft Excel с этой задачей можно справиться с помощью специального инструмента, который называется «Консолидация». Он предоставляет возможность собрать разрозненные данные в одну таблицу. Давайте узнаем, как это делается.
Условия для выполнения процедуры консолидации
Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:
- столбцы во всех таблицах должны иметь одинаковое название (допускается лишь перестановка столбцов местами);
- не должно быть столбцов или строк с пустыми значениями;
- шаблоны у таблиц должны быть одинаковыми.
Создание консолидированной таблицы
Рассмотрим, как создать консолидированную таблицу на примере трех таблиц, имеющих одинаковый шаблон и структуру данных. Каждая из них расположена на отдельном листе, хотя по такому же алгоритму можно создать консолидированную таблицу из данных расположенных в разных книгах (файлах).
- Открываем отдельный лист для консолидированной таблицы.
В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:
- сумма;
- количество;
- среднее;
- максимум;
- минимум;
- произведение;
- количество чисел;
- смещенное отклонение;
- несмещенное отклонение;
- смещенная дисперсия;
- несмещенная дисперсия.
Как видим, после этого диапазон добавляется в список.
Аналогичным образом, добавляем все другие диапазоны, которые будут участвовать в процессе консолидации данных.
Для того, чтобы автоматически добавить название столбцов в шапку, ставим галочку около параметра «Подписи верхней строки». Для того, чтобы производилось суммирование данных устанавливаем галочку около параметра «Значения левого столбца». Если вы хотите, чтобы при обновлении данных в первичных таблицах обновлялась также и вся информация в консолидированной таблице, то обязательно следует установить галочку около параметра «Создавать связи с исходными данными». Но, в этом случае нужно учесть, что, если вы захотите в исходную таблицу добавить новые строки, то придется снять галочку с данного пункта и пересчитать значения вручную.
Как видим, консолидация данных в Эксель является очень удобным инструментом, благодаря которому можно собрать воедино информацию расположенную не только в разных таблицах и на разных листах, но даже размещенную в других файлах (книгах). Делается это относительно просто и быстро.
Мы рады, что смогли помочь Вам в решении проблемы.
Как объединить файлы Excel и таблицы
При работе с несколькими файлами Microsoft Excel иногда необходимо объединить файлы и таблицы Excel в новый или существующий файл Excel или объедините несколько файлов Excel в один файл. Хотя вы всегда можете скопировать и вставить данные с одного листа на другой или из одного файла в другой, это утомительно, когда их слишком много. В этом посте мы расскажем, как их можно объединить с помощью встроенной функции.
Как объединить файлы и таблицы Excel
Прежде чем приступить к объединению файлов и листов, хорошо спланируйте это. Хотя вы всегда можете переставить листы позже, чем лучше вы планируете, тем меньше часов вы потратите на их сортировку после объединения.
- Объединить таблицы в новый или существующий файл
- Объединить несколько файлов Excel
Хотя функции, которые мы здесь будем использовать, позволят вам перемещать листы, я рекомендую копировать. Если вам снова понадобится исходный файл, у вас будет выбор.
Объединить таблицы в новый или существующий файл
Прежде чем мы начнем слияние, убедитесь, что у вас открыты все файлы Excel. Только когда они открыты, функция слияния Excel может выбрать их в качестве места назначения. Если вы планируете переместить листы из нескольких файлов в новый файл Excel, вы также можете это сделать.
- Откройте исходный файл Excel и переключитесь на лист, который вы хотите скопировать.
- Щелкните вкладку «Главная»> раздел «Ячейки»> «Формат»> «Переместить или скопировать лист».
- Откроется всплывающее окно, в котором вы можете выбрать существующий файл Excel или создать новый файл на ходу.
- Когда ты выберите новый вариант файла, он мгновенно создаст новый файл, но не сохранит его.
- Когда ты выберите существующий файл, у вас есть возможность выбрать, куда будет вставлен лист, т. е. до или после существующих листов или в конце всех листов.
Вы также можете перейти в существующий файл, что удобно, если листов слишком много, и вам нужно вставить лист где-то между ними или переместить его в конец.
Если вы хотите переместить несколько листов в другой файл Excel, то перед использованием «Переместите или скопируйте лист » выберите листы с помощью Ctrl или Shift. В то время как Shift позволяет вам выбирать соседние листы или диапазон листов, Ctrl позволяет вам выбирать отдельные листы. Остальные шаги такие же. Вы можете использовать это для ручного слияния файлов Excel.
Объединить несколько файлов Excel
Слияние файлов Excel — сложная вещь, и для этого мы будем использовать код VBA из ExtendOffice. Это позволит вам объединить несколько файлов или книг, доступных в папке.
- Создайте новую электронную таблицу Excel и нажмите ALT + F11, чтобы открыть раздел разработчика.
- Щелкните меню «Вставка», а затем «Модуль»
- Вставьте код, указанный ниже. Назовите модуль MergeExcel
Затем нажмите Alt + F8 открыть Макрос диалог. Это покажет все макросы на листе. Выбирать MergeExcel и нажмите Пробег. Вам будет предложено сохранить файлы, обязательно сделайте это. После этого все листы из разных файлов Excel станут доступны в файле Excel, в котором вы запускали макрос. На веб-сайте ExtendOffice есть много таких макросов, и я настоятельно рекомендую посетить их по адресу extendoffice.com.
Тем не менее, это не будет гладким опытом с первой попытки. Будьте уверены, что он не удалит какие-либо файлы, но я бы посоветовал сохранить резервную копию файлов. Поэкспериментируйте со случайными данными и проверьте, получаете ли вы желаемый результат с помощью макроса.
Мы надеемся, что руководство было легким для понимания, и вы можете объединить файлы Excel и таблицы.
Содержание
- Условия для выполнения процедуры консолидации
- Создание консолидированной таблицы
- Вопросы и ответы
При работе с однотипными данными, размещенными в разных таблицах, листах или даже книгах, для удобства восприятия лучше собрать информацию воедино. В Microsoft Excel с этой задачей можно справиться с помощью специального инструмента, который называется «Консолидация». Он предоставляет возможность собрать разрозненные данные в одну таблицу. Давайте узнаем, как это делается.
Условия для выполнения процедуры консолидации
Естественно, что не все таблицы можно консолидировать в одну, а только те, которые соответствуют определенным условиям:
- столбцы во всех таблицах должны иметь одинаковое название (допускается лишь перестановка столбцов местами);
- не должно быть столбцов или строк с пустыми значениями;
- шаблоны у таблиц должны быть одинаковыми.
Создание консолидированной таблицы
Рассмотрим, как создать консолидированную таблицу на примере трех таблиц, имеющих одинаковый шаблон и структуру данных. Каждая из них расположена на отдельном листе, хотя по такому же алгоритму можно создать консолидированную таблицу из данных расположенных в разных книгах (файлах).
- Открываем отдельный лист для консолидированной таблицы.
- На открывшемся листе отмечаем ячейку, которая будет являться верхней левой ячейкой новой таблицы.
- Находясь во вкладке «Данные» кликаем по кнопке «Консолидация», которая расположена на ленте в блоке инструментов «Работа с данными».
- Открывается окно настройки консолидации данных.
В поле «Функция» требуется установить, какое действие с ячейками будет выполняться при совпадении строк и столбцов. Это могут быть следующие действия:
- сумма;
- количество;
- среднее;
- максимум;
- минимум;
- произведение;
- количество чисел;
- смещенное отклонение;
- несмещенное отклонение;
- смещенная дисперсия;
- несмещенная дисперсия.
В большинстве случаев используется функция «Сумма».
- В поле «Ссылка» указываем диапазон ячеек одной из первичных таблиц, которые подлежат консолидации. Если этот диапазон находится в этом же файле, но на другом листе, то жмем кнопку, которая расположена справа от поля ввода данных.
- Переходим на тот лист, где расположена таблица, выделяем нужный диапазон. После ввода данных жмем опять на кнопку расположенную справа от поля, куда был внесен адрес ячеек.
- Вернувшись в окно настроек консолидации, чтобы добавить уже выбранные нами ячейки в список диапазонов, жмем на кнопку «Добавить».
Как видим, после этого диапазон добавляется в список.
Аналогичным образом, добавляем все другие диапазоны, которые будут участвовать в процессе консолидации данных.
Если же нужный диапазон размещен в другой книге (файле), то сразу жмем на кнопку «Обзор…», выбираем файл на жестком диске или съемном носителе, а уже потом указанным выше способом выделяем диапазон ячеек в этом файле. Естественно, файл должен быть открыт.
- Точно так же можно произвести некоторые другие настройки консолидированной таблицы.
Для того, чтобы автоматически добавить название столбцов в шапку, ставим галочку около параметра «Подписи верхней строки». Для того, чтобы производилось суммирование данных устанавливаем галочку около параметра «Значения левого столбца». Если вы хотите, чтобы при обновлении данных в первичных таблицах обновлялась также и вся информация в консолидированной таблице, то обязательно следует установить галочку около параметра «Создавать связи с исходными данными». Но, в этом случае нужно учесть, что, если вы захотите в исходную таблицу добавить новые строки, то придется снять галочку с данного пункта и пересчитать значения вручную.
Когда все настройки выполнены, жмем на кнопку «OK».
- Консолидированный отчет готов. Как видим, данные его сгруппированы. Чтобы посмотреть информацию внутри каждой группы, кликаем на плюсик слева от таблицы.
Теперь содержимое группы доступно для просмотра. Аналогичным способом можно раскрыть и любую другую группу.
Как видим, консолидация данных в Эксель является очень удобным инструментом, благодаря которому можно собрать воедино информацию расположенную не только в разных таблицах и на разных листах, но даже размещенную в других файлах (книгах). Делается это относительно просто и быстро.
Еще статьи по данной теме:
Помогла ли Вам статья?
При выполнении ряда работ у пользователя Microsoft Excel может быть создано несколько однотипных таблиц в одном файле или в нескольких книгах.
Данные необходимо свести воедино. Собрать в один отчет, чтобы получить общее представление. С такой задачей справляется инструмент «Консолидация».
Как сделать консолидацию данных в Excel
Есть 4 файла, одинаковых по структуре. Допустим, поквартальные итоги продаж мебели.
Нужно сделать общий отчет с помощью «Консолидации данных». Сначала проверим, чтобы
- макеты всех таблиц были одинаковыми;
- названия столбцов – идентичными (допускается перестановка колонок);
- нет пустых строк и столбцов.
Диапазоны с исходными данными нужно открыть.
Для консолидированных данных отводим новый лист или новую книгу. Открываем ее. Ставим курсор в первую ячейку объединенного диапазона.
Внимание!!! Правее и ниже этой ячейки должно быть свободно. Команда «Консолидация» заполнит столько строк и столбцов, сколько нужно.
Переходим на вкладку «Данные». В группе «Работа с данными» нажимаем кнопку «Консолидация».
Открывается диалоговое окно вида:
На картинке открыт выпадающий список «Функций». Это виды вычислений, которые может выполнять команда «Консолидация» при работе с данными. Выберем «Сумму» (значения в исходных диапазонах будут суммироваться).
Переходим к заполнению следующего поля – «Ссылка».
Ставим в поле курсор. Открываем лист «1 квартал». Выделяем таблицу вместе с шапкой. В поле «Ссылка» появится первый диапазон для консолидации. Нажимаем кнопку «Добавить»
Открываем поочередно второй, третий и четвертый квартал – выделяем диапазоны данных. Жмем «Добавить».
Таблицы для консолидации отображаются в поле «Список диапазонов».
Чтобы автоматически сделать заголовки для столбцов консолидированной таблицы, ставим галочку напротив «подписи верхней строки». Чтобы команда суммировала все значения по каждой уникальной записи крайнего левого столбца – напротив «значения левого столбца». Для автоматического обновления объединенного отчета при внесении новых данных в исходные таблицы – напротив «создавать связи с исходными данными».
Внимание!!! Если вносить в исходные таблицы новые значения, сверх выбранного для консолидации диапазона, они не будут отображаться в объединенном отчете. Чтобы можно было вносить данные вручную, снимите флажок «Создавать связи с исходными данными».
Для выхода из меню «Консолидации» и создания сводной таблицы нажимаем ОК.
Консолидированный отчет представляет собой структурированную таблицу. Нажмем «плюсик» в левом поле – появятся значения, на основе которых сформированы итоговые суммы по количеству и выручке.
Консолидация данных в Excel: практическая работа
Программа Microsoft Excel позволяет выполнять разные виды консолидации данных:
- По расположению. Консолидированные данные имеют одинаковое расположение и порядок с исходными.
- По категории. Данные организованы по разным принципам. Но в консолидированной таблице используются одинаковые заглавия строк и столбцов.
- По формуле. Применяются при отсутствии постоянных категорий. Содержат ссылки на ячейки на других листах.
- По отчету сводной таблицы. Используется инструмент «Сводная таблица» вместо «Консолидации данных».
Консолидация данных по расположению (по позициям) подразумевает, что исходные таблицы абсолютно идентичны. Одинаковые не только названия столбцов, но и наименования строк (см. пример выше). Если в диапазоне 1 «тахта» занимает шестую строку, то в диапазоне 2, 3 и 4 это значение должно занимать тоже шестую строку.
Это наиболее правильный способ объединения данных, т.к. исходные диапазоны идеальны для консолидации. Объединим таблицы, которые находятся в разных книгах.
Созданы книги: Магазин 1, Магазин 2 и Магазин 3. Структура одинакова. Расположение данных идентично. Объединим их по позициям.
- Открываем все три книги. Плюс пустую книгу, куда будет помещена консолидированная таблица. В пустой книге выбираем верхний левый угол чистого листа. Открываем меню инструмента «Консолидация».
- Составим консолидированный отчет, используя функцию «Среднее».
- Чтобы показать путь к книгам с исходными диапазонами, ставим курсор в поле «Ссылка». На вкладке «Вид» нажимаем кнопку «Перейти в другое окно».
- Выбираем поочередно имена файлов, выделяем диапазоны в открывающихся книгах – жмем «Добавить».
Примечание. Показать программе путь к исходным диапазонам можно и с помощью кнопки «Обзор». Либо посредством переключения на открытую книгу.
Консолидированная таблица:
Консолидация данных по категориям применяется, когда исходные диапазоны имеют неодинаковую структуру. Например, в магазинах реализуются разные товары. Какие-то наименования повторяются, а какие-то нет.
- Для создания объединенного диапазона открываем меню «Консолидация». Выбираем функцию «Сумма» (для примера).
- Добавляем исходные диапазоны любым из описанных выше способом. Ставим флажки у «значения левого столбца» и «подписи верхней строки».
- Нажимаем ОК.
Excel объединил информацию по трем магазинам по категориям. В отчете имеются данные по всем товарам. Независимо от того, продаются они в одном магазине или во всех трех.
Примеры консолидации данных в Excel
На лист для сводного отчета вводим названия строк и столбцов из консолидируемых диапазонов. Удобнее делать это путем копирования.
В первую ячейку для значений объединенной таблицы вводим формулу со ссылками на исходные ячейки каждого листа. В нашем примере – в ячейку В2. Формула для суммы: =’1 квартал’!B2+’2 квартал’!B2+’3 квартал’!B2.
Копируем формулу на весь столбец:
Консолидация данных с помощью формул удобна, когда объединяемые данные находятся в разных ячейках на разных листах. Например, в ячейке В5 на листе «Магазин», в ячейке Е8 на листе «Склад» и т.п.
Скачать все примеры консолидации данных в Excel
Если в книге включено автоматическое вычисление формул, то при изменении данных в исходных диапазонах объединенная таблица будет обновляться автоматически.
|
Как просуммировать значения с разных книг? |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
