Article #34168

В рамках моей обычной работы несколько лет назад одной из вещей, с которыми мне приходилось иметь дело, было объединение данных из разных рабочих тетрадей, которые используют другие люди.

И одной из самых распространенных задач было объединить данные таким образом, чтобы не было повторяющихся записей.

Например, ниже представлен набор данных, содержащий несколько записей для одного и того же региона.

И конечным результатом должен быть консолидированный набор данных, в котором каждая страна представлена ​​только один раз.

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

Объединение и суммирование данных с помощью опции консолидации

Если все, что вам нужно сделать, это объединить данные и добавить все значения для повторяющихся записей, лучший способ — использовать функцию консолидации в Excel.

Другой метод — использовать сводную таблицу и суммировать данные (далее в этом руководстве).

Допустим, у нас есть набор данных, показанный ниже, в котором название страны повторяется несколько раз.

Несмотря на то, что это уникальные записи, поскольку стоимость продажи различается, для целей отчетности вы можете удалить несколько экземпляров одной и той же страны и показать стоимость продажи как консолидированную сумму.

Ниже приведены шаги для этого:

• Скопируйте исходные заголовки данных и вставьте их в то место, где вы хотите консолидировать данные.
• Выберите ячейку под крайним левым заголовком
• Перейдите на вкладку «Данные».
• В группе «Инструменты для работы с данными» щелкните значок «Консолидировать».
• В диалоговом окне «Консолидировать» выберите «Сумма» в раскрывающемся списке «Функция» (если он еще не выбран по умолчанию).
• Щелкните значок переключателя диапазона в поле «Ссылка».
• Выберите диапазон A2: B9 (данные без заголовков)
• Установите флажок в левом столбце.
• Нажмите ОК

Вышеупомянутые шаги объединят данные, удалив повторяющиеся записи и добавив значения для каждой страны.

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

Я решил получить СУММУ значений из каждой записи. Вы также можете выбрать другие параметры, такие как «Счетчик» или «Среднее» или «Макс. / Мин.».

В этом примере я показал вам, как объединить данные в один набор данных на одном листе, вы также можете использовать эту функцию для объединения данных с нескольких листов в одну книгу, а также из разных книг.

Объединение и суммирование данных с помощью сводных таблиц

Сводная таблица — это швейцарский армейский нож для нарезки и нарезки данных в Excel.

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

Недостатком этого метода перед предыдущим является то, что этот метод требует больше кликов и на несколько секунд больше, чем предыдущий.

Допустим, у нас есть набор данных, показанный ниже, где название страны повторяется несколько раз, и мы хотим объединить эти данные.

Ниже приведены шаги по созданию сводной таблицы:

• Выберите ячейку в наборе данных
• Щелкните вкладку Вставка
• В группе Таблицы выберите опцию Сводная таблица».
• В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица или диапазон указаны правильно.
• Щелкните существующий лист
• Выберите место, куда вы хотите вставить итоговую сводную таблицу.
• Щелкните ОК.

Вышеупомянутые шаги вставят сводную таблицу в выбранную целевую ячейку.

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

Ниже приведены шаги для этого:

• Щелкните в любом месте области сводной таблицы, чтобы открыть панель сводной таблицы справа.
• Перетащите поле Country в область Row.
• Перетащите поле «Продажи» в область «Значения».

Предыдущие шаги суммируют данные и предоставляют общий объем продаж для всех стран.

Если это все, что вам нужно, и вам не нужна сводная таблица, вы можете скопировать данные, вставить их как значения в другое место и удалить сводную таблицу.

Это также поможет вам уменьшить размер вашей книги Excel.

Итак, это два быстрых и простых метода, которые вы можете использовать для консолидации данных, где они объединяют повторяющиеся строки и суммируют все значения в этих записях.

Надеюсь, вы нашли этот урок полезным!