Как в Excel использовать консолидацию для объединения данных из разных книг
Вот два способа объединения данных, применяемых в Excel:
- Объединение по позициям — с использованием этого метода Excel объединяет информацию из нескольких книг, используя один и тот же диапазон ячеек в каждой из них. Данный метод необходимо использовать, если книги идентичны по своей структуре.
- Объединение по категориям — в данном случае Excel будет объединять данные в зависимости от заголовка строки или столбца. Например, если в одной из книг слово Диски будет находиться в строке 1, а в другой в строке 5, вы все равно сможете объединить информацию, поскольку в обеих книгах строка начинается с одного заголовка.
В каждом из данных методов необходимо указать один или несколько диапазонов-источников данных и диапазон для вставки данных. Ниже мы рассмотрим практическое применение каждого из методов.
Объединение по позициям
Если книги, с которыми вы работаете, имеют одинаковую структуру построения, объединение по позициям — это наиболее правильный способ объединения данных. Например, обратите внимание на три созданные книги — Баланс1 Баланс2 и Баланс3 на рис. 3.7.
Как вы видите, все три балансовые книги (которые, например, могут представлять собой отчет от трех различных магазинов одной фирмы) имеют одинаковую структуру и расположение данных. Таким образом, они идеальны для объединения по позициям.
- Выберите верхний левый угол диапазона, куда будут занесены данные. В данном примере (см. рис. 3.8) это будет ячейка B3 .
- Перейдите на вкладку Данные ленты инструментов Excel, затем в группе Работа с данными нажмите кнопку Консолидация. В результате на экране появится диалоговое окно Консолидация — см. рис. 3.9.
Вверху окна вы видите раскрывающееся меню с выбором функции для работы. В нашем примере необходимо использовать функцию СУММА, однако обратите внимание, что также вы можете вычислять средние и максимальные значения и многое другое. В поле Ссылка вам необходимо ввести путь к книге с диапазоном. Вот способы это сделать:
- Ввести диапазон вручную. Если данные находятся в другой книге, убедитесь, что вы включили сюда название книги, заключенное в квадратные скобки. Если книга находится в другом каталоге или на другом диске, обязательно также следует ввести полный путь.
- Если книга открыта, переключитесь на нее и затем мышью выделите необходимый диапазон.
- Если книга не открыта, используйте кнопку Обзор, выберите файл и затем допишите имя листа и необходимый диапазон.
Если вы не создадите связь с исходными данными. Excel просто единовременно внесет данные из книг. При создании же связей произойдет следующее:
Если вы раскроете данные нажатием на кнопку рядом с каждой из категорий, например Книги, вы сможете увидеть все связи и данные по каждой из книг-источников.
Объединение по категориям
Если ваши рабочие книги содержат неодинаковую структуру (например, если разные магазины продают разные группы товаров), объединение по диапазонам даст неверные вычисления. В этом случае необходимо использовать объединение по категориям. На рис. 3.11 вы видите пример трех книг с разными категориями.
Рис. 3.11. Исходные книги для объединения по категориям
Как вы можете видеть, в Баланс1 находится информация от какого-то отдела по продаже Дисков и Книг, в Баланс2 — Мороженого, Книг и Кассет, а в Баланс3 — Дисков, Мороженого, Кассет и Приборов. Далее выполните следующие операции:
Объединение двух таблиц в Excel | Помоги себе сам | Яндекс Дзен
- Объединение по позициям — с использованием этого метода Excel объединяет информацию из нескольких книг, используя один и тот же диапазон ячеек в каждой из них. Данный метод необходимо использовать, если книги идентичны по своей структуре.
- Объединение по категориям — в данном случае Excel будет объединять данные в зависимости от заголовка строки или столбца. Например, если в одной из книг слово Диски будет находиться в строке 1, а в другой в строке 5, вы все равно сможете объединить информацию, поскольку в обеих книгах строка начинается с одного заголовка.
Данные на листах Excel могут располагаться не в Таблицах. Напомню, что Power Query «не видит» листы Excel. Поэтому исходные данные можно организовать в именованные диапазоны. Это можно сделать, например, с помощью определения области печати. Трюк работает потому, что имя области печати является именем динамического диапазона.
