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