В рамках моей постоянной работы несколько лет назад одной из вещей, с которыми мне приходилось иметь дело, было объединение данных из разных рабочих тетрадей, которыми делятся другие люди.
И одной из распространённых задач было объединить данные таким образом, чтобы не было повторяющихся записей.
Например, у вас может быть набор данных, содержащий несколько записей для одного и того же региона, а конечным результатом должен быть консолидированный набор данных, в котором каждая страна представлена только один раз.
В этом руководстве я покажу вам, как объединить повторяющиеся строки и суммировать значения для создания единого консолидированного набора данных.
Объединение и суммирование данных с помощью опции консолидации
Если всё, что вам нужно сделать, — это объединить данные и добавить все значения для повторяющихся записей, лучше всего использовать функцию консолидации в Excel.
Другой метод — использовать сводную таблицу и суммировать данные (об этом далее в этом руководстве).
Предположим, у вас есть набор данных, в котором название страны повторяется несколько раз.
Хотя это уникальные записи, так как стоимость продажи различается, для целей отчётности вы можете удалить несколько экземпляров одной и той же страны и показать стоимость продаж как одну консолидированную сумму.
Ниже приведены шаги для этого:
Скопируйте заголовки исходных данных и вставьте их туда, где вы хотите консолидировать данные.
Выберите ячейку под крайним левым заголовком.
Перейдите на вкладку «Данные».
В группе «Инструменты для работы с данными» щёлкните значок «Консолидировать».
В диалоговом окне «Консолидировать» выберите «Сумма» в раскрывающемся списке функций (если он ещё не выбран по умолчанию).
Щёлкните значок выбора диапазона в поле «Ссылка».
Выберите диапазон исходных данных без заголовков (например, A2:B9).
Установите флажок «Левый столбец».
Нажмите ОК.
Вышеупомянутые шаги объединят данные, удалив повторяющиеся записи и добавив значения для каждой страны.
В конечном результате вы получите уникальный список стран вместе со стоимостью продаж из исходного набора данных.
Здесь показано, как объединить данные в единый набор данных на листе. Эту же функцию можно использовать для консолидации данных из нескольких листов в одной книге и даже из нескольких разных книг.
Объедините и суммируйте данные с помощью сводных таблиц
Сводная таблица — это швейцарский армейский нож для нарезки и обработки данных в Excel.
Она может легко дать вам сводку, которая представляет собой комбинированный набор данных без дубликатов, со значениями, являющимися суммой всех похожих записей, и многое другое.
Обратная сторона этого метода по сравнению с предыдущим — он требует больше кликов и на несколько секунд больше времени.
Предположим, у вас есть набор данных, в котором название страны повторяется несколько раз, и вы хотите объединить эти данные.
Ниже приведены шаги по созданию сводной таблицы:
Выберите любую ячейку в наборе данных.
Щёлкните вкладку «Вставка».
В группе «Таблицы» выберите параметр «Сводная таблица».
В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно.
Щёлкните «Существующий лист».
Выберите место, куда вы хотите вставить итоговую сводную таблицу.
Нажмите ОК.
Вышеупомянутые шаги вставят сводную таблицу в выбранную целевую ячейку.
Теперь с помощью сводной таблицы мы можем делать всё что угодно, включая консолидацию набора данных и удаление дубликатов.
Ниже приведены шаги для этого:
Щёлкните в любом месте области сводной таблицы, и откроется панель сводной таблицы справа.
Перетащите поле «Страна» в область «Строки».
Перетащите и поместите поле «Продажи» в область «Значения».
Вышеупомянутые шаги суммируют данные и дадут вам сумму продаж по всем странам.
Итак, это два быстрых и простых метода, которые вы можете использовать для консолидации данных, где они объединяют повторяющиеся строки и суммируют все значения в этих записях.
Надеюсь, вы нашли этот урок полезным!
Хотите освоить Excel глубже?
Больше полезных формул, приёмов и готовых решений — на Vip Excel