Во время работы в Excel у нас есть много данных, доступных на разных листах, и если мы хотим объединить или объединить данные на одном листе, у Excel есть функция для этого после открытия всех листов, на которых есть данные, которые необходимо объединить, Кнопка «Объединить» доступна в разделе инструментов данных на вкладке «Данные».
Если вы не знакомы с инструментом консолидации в Excel, эта статья поможет вам понять динамику инструмента консолидации в Excel.
Обычно, когда мы получаем данные от разных людей, нам приходится иметь дело с множеством рабочих листов. Объединить все — непростая задача, особенно для выравнивателей среднего уровня. В начале своей карьеры я получал данные из самых разных источников.
Например, меня использовали для получения данных о продажах из 4 разных зон от зональных голов. Четыре разные зоны включают четыре разных листа. По окончании сбора данных мне нужно было сопоставить все на одном листе и создать базу данных для анализа. Я делал это вручную традиционным способом копирования и вставки.
Но я потратил некоторое время на изучение новых вещей, и инструменты консолидации — один из них, и это очень помогло мне сопоставить данные с минимальными усилиями.
Как консолидировать данные в Excel? (Пошагово с примером)
Вы можете скачать этот шаблон Excel для консолидации данных здесь — Шаблон для консолидации данных в Excel
У меня есть данные о продажах из четырех разных регионов на четырех разных листах.
Каждый содержит одинаковый формат и одинаковое количество строк и столбцов. Мне нужно объединить все листы в один лист с именем Резюме.
Я создал тот же шаблон, что и на остальных 4 листах.
Шаг 1: После создания указанного выше шаблона в сводке листа выберите ячейку B2, в которой мы хотим объединить все 4 разных региона.
Шаг 2: Теперь перейдите в Data> Consolidate.
Шаг 3: После того, как вы выбрали опцию «Консолидировать», вы увидите следующее окно.
Шаг 4: Это окно, которое нам нужно для волшебства. Во-первых, мы видим раскрывающийся список функций из этого раскрывающегося списка, нам нужно выбрать наш тип консолидации. Здесь нам нужно выбрать функцию СУММ в Excel, потому что мы объединяем все данные о продажах зоны и создаем сводный отчет. Поэтому выберите SUM под этой опцией.
Шаг 5: Следующий шаг — дать ссылку на все 4 таблицы различных регионов. Щелкните поле ссылки и перейдите к первому листу всех листов зоны, т. Е. Юг. После выбора диапазона нажмите кнопку «Добавить».
Шаг 6: После нажатия кнопки «Добавить» ссылки добавляются ко всем ссылкам на ячейки.
Шаг 7: Аналогичным образом создайте ссылку на Excel для всех листов других регионов. В итоге это будет выглядеть так.
Шаг 8: Теперь нажмите кнопку ОК; ваши консолидированные данные готовы без ошибок.
Шаг 9: Теперь мы создали сводный отчет; это не динамический отчет, потому что мы не создали ссылку на другие таблицы. Если есть какие-либо таблицы изменений, оставайтесь прежними, и в этом случае наш отчет будет неверным. Нам нужно связать таблицу с другими листами, чтобы сделать отчет динамическим.
Шаг 10: В диалоговом окне консолидации установите флажок Создайте ссылки на исходные данные. Щелкните ОК.
Затем процесс завершается.
Шаг 11: Теперь он создал функцию СУММ. В правой части таблицы есть значок плюса (+), щелкните значок плюса (+), чтобы увидеть разделение.
Другие варианты в Consolidate
Функция: Это список всех доступных функций в инструменте консолидации. Вы можете использовать функцию SUM, AVERAGE, COUNT, MAX, MIN в excel и т. Д.
Справка: Здесь нам нужно выбрать диапазоны разных листов, чтобы объединить данные на один лист.
Просматривать: В этом вся прелесть консолидированного варианта. Он не только объединяется из той же книги, но также может извлекать данные из других книг.
Добавить и удалить: Здесь мы можем добавлять и удалять все ссылки.
Верхний ряд: Используйте верхнюю строку как заголовок строки
Левая колонка: Используйте левую часть данных в качестве заголовков столбцов.
Создать ссылки на исходные данные: Это сделает отчет динамичным. Параметр консолидации создает формулу для всех остальных листов, чтобы сделать отчет действительно динамичным.
То, что нужно запомнить
При консолидации диапазоны вариантов всех листов должны быть одинаковыми для всех листов.
Мы можем использовать любую функцию в опции консолидации.