Консолидация Excel (Содержание)
- Введение в консолидацию в Excel
- Как консолидировать данные в нескольких листах?
Консолидация данных в Excel
Консолидация в целом означает сбор или сбор некоторых общих вещей в одном месте. Это могут быть любые данные или что-то вроде информации или числа. Консолидация в Excel относится к функции, которая объединяет информацию или данные из разных рабочих книг / рабочих листов в одну рабочую таблицу / рабочую книгу. Другими словами, эта функция берет данные из разных рабочих листов, чтобы объединить их в одну рабочую таблицу, которую можно легко обновить.
Например, если у вас есть четыре листа. Каждый лист имеет расходы на четверть. Вам может понадобиться один лист с расходами на все четыре квартала. Это когда функция консолидации вступает в игру. Когда данные в листах находятся в одинаковом положении, что означает под одинаковыми метками и в том же порядке. Мы используем «Консолидация по позициям». И когда данные на листах не находятся под одинаковыми метками и не в том же порядке. Затем мы используем «Консолидация по категориям». Это похоже на создание сводной таблицы.
Как консолидировать данные в нескольких листах?
Давайте разберемся, как объединить данные в нескольких листах на нескольких примерах.
Пример № 1 - Консолидация данных в той же книге
Допустим, у нас есть рабочий лист, в котором у нас есть данные о продажах с четырьмя рабочими листами, помеченными в соответствии с их регионами как Север, Юг, Восток и Запад. Теперь мы хотели бы объединить данные в одном месте, а не на листе в одной книге. Существует пятый лист под названием сводный файл.
Этот пример покажет консолидированные продажи для всех регионов. Вот примеры файлов данных. Здесь вы можете увидеть различные имена рабочих листов и последний объединенный файл, над которым мы должны работать.
Это наш шаблон на листе «консолидированный файл», и теперь мы начнем консолидировать данные из таблиц.
Теперь мы нажмем на ячейку B3.
Где мы хотим, чтобы функция «Консолидация» вставляла данные из других листов. Как мы видим выше, ячейка B3 выбрана, и теперь мы переместимся на вкладку «Данные» на ленте Excel и перейдем к «Консолидация».
Как только мы нажмем «Консолидировать», появится окно ниже:
Первое, на что мы обращаем внимание, - это выпадающий список «Функция», в котором отображаются многие арифметические функции, такие как сумма, количество, максимум, среднее и т. Д. Поскольку мы хотим получить сумму продаж, мы выберем «Сумма» в раскрывающемся списке. Теперь мы перейдем на вкладку «Справка», чтобы добавить ссылку на наши данные из разных таблиц. Затем мы перейдем к нашему первому листу, который содержит данные о продажах на север. Нам нужно только выбрать данные о продажах, а не заголовки и строки. Это показано ниже.
Ссылка отображается в поле «Ссылка» следующим образом.
Нажмите «Добавить», и ссылка будет добавлена в поле «Все ссылки».
Таким же образом мы теперь добавим ссылку со всех других листов, таких как Север, Юг, Восток и Запад. Как только мы закончим добавлять ссылки, нажмите «ОК».
Теперь мы собираем данные по продажам для руководителей по месяцам на национальном уровне.
Пример №2 - Консолидация годового объема продаж
При этом мы имеем квартальные продажи продуктов A, B, C и D, и мы хотим консолидировать ежегодные продажи по продуктам.
Здесь у нас есть данные на четырех листах, первый лист с данными о продажах за первый квартал, затем данные за второй квартал, за которыми следуют данные за третий и четвертый кварталы. Теперь мы перейдем на вкладку ссылок, чтобы добавить ссылки на наши данные из разных рабочих листов. Мы пойдем на наш первый лист, который содержит данные о продажах за первый квартал. Мы выберем данные, как показано ниже.
Теперь перейдем на вкладку «Данные» и нажмите «Консолидировать», появится окно, показанное ниже.
Мы нажмем «Добавить», и ссылка будет добавлена в поле «Все ссылки».
Мы нажмем «Добавить», и ссылка будет добавлена в поле «Все ссылки».
Мы нажмем «Добавить», и ссылка будет добавлена в поле «Все ссылки».
Ниже приведен наш шаблон для сводной таблицы. Теперь мы выберем ячейку B2, чтобы получить данные об общих продажах с других листов.
Мы выберем функцию «Сумма» из выпадающего списка. Также мы добавим ссылки с листов Q2, Q3 и Q4. Это будет выглядеть так. Теперь добавлены все ссылки со всех листов. Нажмите «ОК»
Теперь у нас есть консолидированные годовые данные о продажах с итоговыми суммами для каждого продукта.
В случае, если мы хотим вставить консолидированную таблицу в пустой лист вместо создания таблицы шаблонов. Мы можем сделать это также. Взяв наши предыдущие образцы данных, мы выполним консолидацию ниже.
У нас нет шаблона для таблицы консолидации, и мы хотим получить консолидированные данные на пустом листе с метками строк и столбцов. Мы добавим новый лист, в нашем случае это «Консолидированный файл».
Теперь, как и прежде, мы перейдем на вкладку Данные Теперь мы выберем ссылку из наших таблиц. Мы начнем с листа «Север», а затем перейдем к листам «Юг», «Восток» и «Запад». Мы выберем данные, как показано ниже, которые включают метки строк и столбцов. Затем мы добавим ссылку в поле «Все ссылки»: Теперь добавьте все ссылки таким же образом из всех таблиц. Теперь мы проверим «Top Row» и «Left Column», а затем нажмите OK. Теперь мы видим консолидированные данные о продажах с метками строк и столбцов. Если вы хотите обновить консолидированные данные при обновлении отдельного листа, установите флажок «Создать ссылки для создания данных». Если вы хотите обновить данные вручную, не устанавливайте флажок и нажмите ОК. Ячейки в консолидированной таблице теперь содержат формулу суммы, как показано ниже. Это делает его динамичным по своей природе. Это динамическая таблица консолидации. Если у нас есть данные в разных рабочих книгах, и мы хотим объединить их в одну рабочую книгу. Мы можем сделать это также. Это дает нам большую гибкость и легкость. У нас есть квартальные данные о продажах различных продуктов, например, A, B, C и D, в разных рабочих книгах, как показано ниже. Как только у нас откроются все рабочие книги, мы перейдем к новой рабочей книге и нажмем на ячейку B3. Добавьте ссылки со всех рабочих листов ниже. Мы объединили данные из всех рабочих книг в новую рабочую книгу. Более того, любые изменения данных в любых рабочих книгах также будут обновляться в новой консолидированной рабочей книге. Итак, мы научились использовать функцию консолидации в Excel с помощью примеров. Это очень полезная функция для объединения или сбора данных на одном листе из разных рабочих листов или рабочих книг. Это руководство по консолидации в Excel. Здесь мы обсуждаем, как консолидировать данные в нескольких листах вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие соответствующие статьи -
Пример № 3 - Консолидация данных из разных рабочих книг
Что нужно помнить о консолидации в Excel
Рекомендуемые статьи