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