Как Объединить Данные с Нескольких Листов Excel • Как объединить таблицы

Как в Excel использовать консолидацию для объединения данных из разных книг

Вот два способа объединения данных, применяемых в Excel:

  1. Объединение по позициям — с использованием этого метода Excel объединяет информацию из нескольких книг, используя один и тот же диапазон ячеек в каждой из них. Данный метод необходимо использовать, если книги идентичны по своей структуре.
  2. Объединение по категориям — в данном случае Excel будет объединять данные в зависимости от заголовка строки или столбца. Например, если в одной из книг слово Диски будет находиться в строке 1, а в другой в строке 5, вы все равно сможете объединить информацию, поскольку в обеих книгах строка начинается с одного заголовка.

В каждом из данных методов необходимо указать один или несколько диапазонов-источников данных и диапазон для вставки данных. Ниже мы рассмотрим практическое применение каждого из методов.

Объединение по позициям

Если книги, с которыми вы работаете, имеют одинаковую структуру построения, объединение по позициям — это наиболее правильный способ объединения данных. Например, обратите внимание на три созданные книги — Баланс1 Баланс2 и Баланс3 на рис. 3.7.

Рис. 3.7. Исходные книги для объединения по позициям

Как вы видите, все три балансовые книги (которые, например, могут представлять собой отчет от трех различных магазинов одной фирмы) имеют одинаковую структуру и расположение данных. Таким образом, они идеальны для объединения по позициям.

Рис. 3.8. Книга-шаблон для будущего объединения

  1. Выберите верхний левый угол диапазона, куда будут занесены данные. В данном примере (см. рис. 3.8) это будет ячейка B3 .
  2. Перейдите на вкладку Данные ленты инструментов Excel, затем в группе Работа с данными нажмите кнопку Консолидация. В результате на экране появится диалоговое окно Консолидация — см. рис. 3.9.

Рис. 3.9. Окно «Консолидация»

Вверху окна вы видите раскрывающееся меню с выбором функции для работы. В нашем примере необходимо использовать функцию СУММА, однако обратите внимание, что также вы можете вычислять средние и максимальные значения и многое другое. В поле Ссылка вам необходимо ввести путь к книге с диапазоном. Вот способы это сделать:

  • Ввести диапазон вручную. Если данные находятся в другой книге, убедитесь, что вы включили сюда название книги, заключенное в квадратные скобки. Если книга находится в другом каталоге или на другом диске, обязательно также следует ввести полный путь.
  • Если книга открыта, переключитесь на нее и затем мышью выделите необходимый диапазон.
  • Если книга не открыта, используйте кнопку Обзор, выберите файл и затем допишите имя листа и необходимый диапазон.

Рис. 3.10. Объединенная по позициям книга

Если вы не создадите связь с исходными данными. Excel просто единовременно внесет данные из книг. При создании же связей произойдет следующее:

Если вы раскроете данные нажатием на кнопку рядом с каждой из категорий, например Книги, вы сможете увидеть все связи и данные по каждой из книг-источников.

Объединение по категориям

Если ваши рабочие книги содержат неодинаковую структуру (например, если разные магазины продают разные группы товаров), объединение по диапазонам даст неверные вычисления. В этом случае необходимо использовать объединение по категориям. На рис. 3.11 вы видите пример трех книг с разными категориями.

Рис. 3.11. Исходные книги для объединения по категориям

Рис. 3.11. Исходные книги для объединения по категориям

Как вы можете видеть, в Баланс1 находится информация от какого-то отдела по продаже Дисков и Книг, в Баланс2 — Мороженого, Книг и Кассет, а в Баланс3 — Дисков, Мороженого, Кассет и Приборов. Далее выполните следующие операции:

Как в Excel использовать консолидацию для объединения данных из разных книг — Трюки и приемы в Microsoft Excel
Если ваши рабочие книги содержат неодинаковую структуру (например, если разные магазины продают разные группы товаров), объединение по диапазонам даст неверные вычисления. В этом случае необходимо использовать объединение по категориям. На рис. 3.11 вы видите пример трех книг с разными категориями.
эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Объединение по позициям с использованием этого метода Excel объединяет информацию из нескольких книг, используя один и тот же диапазон ячеек в каждой из них. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Затем надо запустить Мастер запросов (в разделе Создание). Мастер запросов интуитивно понятен, но предлагает не все варианты, которые могут потребоваться в работе. Но это поправимо, если переключиться в режим SQL . В нашем примере получается запрос:
Как Объединить Данные с Нескольких Листов Excel • Как объединить таблицы

Объединение двух таблиц в Excel | Помоги себе сам | Яндекс Дзен

  1. Объединение по позициям — с использованием этого метода Excel объединяет информацию из нескольких книг, используя один и тот же диапазон ячеек в каждой из них. Данный метод необходимо использовать, если книги идентичны по своей структуре.
  2. Объединение по категориям — в данном случае Excel будет объединять данные в зависимости от заголовка строки или столбца. Например, если в одной из книг слово Диски будет находиться в строке 1, а в другой в строке 5, вы все равно сможете объединить информацию, поскольку в обеих книгах строка начинается с одного заголовка.

Данные на листах Excel могут располагаться не в Таблицах. Напомню, что Power Query «не видит» листы Excel. Поэтому исходные данные можно организовать в именованные диапазоны. Это можно сделать, например, с помощью определения области печати. Трюк работает потому, что имя области печати является именем динамического диапазона.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector