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