|
Объединение данных из нескольких таблиц в одну |
  
|
| скрыть/показать скрытый текст |
|
Объединение данных из нескольких таблиц в одну |
|
| скрыть/показать скрытый текст |
Если таблицы одинаковые...
Имеем несколько однотипных таблиц на разных листах. Например, вот такие:
Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.
Способ 1. Простые формулы
Самый простой способ. Ввести в ячейку чистого листа формулу
=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3
и скопировать ее на четыре ячейки вправо и на четыре вниз
Способ 2. Трехмерные формулы
Чуть более изящный способ, чем предыдущий. Формула может выглядеть немного иначе:
=СУММ('2001 год:2003 год'!B3)
Фактически - это суммирование всех ячеек B3 на листах с 2001 по 2003. То есть, в будущем, возможно поместить между этими листами дополнительные листы с данными, которые также станут учитываться при суммировании.
Если таблицы неодинаковые или в разных файлах
Если исходные таблицы не абсолютно идентичны, т.е. имеют разное количество строк, столбцов или повторяющиеся данные или находятся в разных файлах, то суммирование "в лоб" при помощи обычных формул не поможет.
Рассмотрим следующий пример. Имеем три разных файла (John.xls, Rita.xls и Stiven.xls) с тремя таблицами:
Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.
Для того, чтобы запустить консолидиацию, сначала заранее откройте файлы John.xls, Rita.xls и Stiven.xls. Затем создайте новую пустую книгу, установите в нее активную ячейку и выберите в меню Данные - Консолидация (Data - Consolidate). Откроется окно:
Установите курсор в строку Ссылка (Reference) и, переключившись в файл John.xls через меню Окно (Window), выделите таблицу Джона, затем нажмите кнопку Добавить (Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов. Теперь повторите эти же действия для файлов Риты и Стивена. В итоге в списке должны оказаться три диапазона наших трех сотрудников:
Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек).
Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен... (Use labels) Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах Джона, Риты и Стивена) производить пересчет консолидированного отчета автоматически.
После нажатия на ОК видим такую картину:
Наши файлы проконсолидировались по крайнему левому столбцу и верхней строке выделенных областей в каждом файле. Причем, если развернуть группы (значками плюс слева от таблицы), то можно увидеть из какого именно файла какие данные попали в отчет и ссылки на исходные файлы:
|
|
 |
