Перейти к основному содержанию

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

Объедините повторяющиеся строки и просуммируйте значения


Объедините повторяющиеся строки и просуммируйте значения с помощью функции консолидации.

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

Шаг 1. Выберите ячейку назначения

Выберите, где вы хотите, чтобы консолидированные данные отображались.

Шаг 2. Получите доступ к функции консолидации и настройте консолидацию.

  1. Нажмите Данные > Консолидировать, см. снимок экрана:
  2. В Консолидировать диалоговое окно:
    • (1.) Выберите Сумма от Функция раскрывающийся список;
    • (2.) Нажмите, чтобы выбрать диапазон, который вы хотите консолидировать в Справка коробка;
    • (3.) Проверить Верхний ряд и Левая колонка от Используйте ярлыки в вариант;
    • (4.) Наконец, нажмите OK .

Результат:

Excel объединит все дубликаты, найденные в первом столбце, и суммирует их соответствующие значения в соседних столбцах, как показано на следующем снимке экрана:

Ноты:
  • Если диапазон не включает строку заголовка, убедитесь, что снимите флажок Верхняя строка из Используйте ярлыки в опцию.
  • Благодаря этой функции расчеты можно консолидировать только на основе первого столбца (крайнего левого) данных.

Объедините повторяющиеся строки и суммируйте значения с помощью мощной функции – Kutools

Если вы установили Kutools for Excel, Его Расширенные ряды комбинирования Функция позволяет вам легко комбинировать повторяющиеся строки, предоставляя возможности суммировать, подсчитывать, усреднять или выполнять другие вычисления с вашими данными. Более того, эта функция не ограничивается одним ключевым столбцом, она может обрабатывать несколько ключевых столбцов, что значительно упрощает сложные задачи консолидации данных.

Внимание: Если вы хотите использовать это Расширенные ряды комбинирования особенность, пожалуйста скачайте и установите Kutools для Excel первый.

После установки Kutools for Excel, выберите диапазон данных и нажмите кнопку Кутулс > Слияние и разделение > Расширенные ряды комбинирования.

В Расширенные ряды комбинирования диалоговое окно, установите следующие операции:

  1. Щелкните имя столбца, на основе которого вы хотите объединить дубликаты. Здесь я нажму «Продукт», а затем выберите Основной ключ из раскрывающегося списка в Эксплуатация столбец;
  2. Затем выберите имя столбца, значения которого вы хотите суммировать, а затем выберите Сумма из раскрывающегося списка в Эксплуатация столбец;
  3. Что касается остальных столбцов, вы можете выбрать нужную вам операцию, например объединение значений с определенным разделителем или выполнение определенного расчета; (этот шаг можно проигнорировать, если у вас только два столбца)
  4. Наконец, вы можете просмотреть объединенный результат, а затем нажать OK .

Результат:

Теперь повторяющиеся значения в ключевом столбце объединяются, а другие соответствующие значения суммируются, как показано на следующем снимке экрана:

Советы:
  • С помощью этой полезной функции вы также можете объединять строки на основе повторяющихся значений ячеек, как показано в следующей демонстрации:
  • Эта особенность поддерживает отмену, если вы хотите восстановить исходные данные, просто нажмите Ctrl + Z.
  • Чтобы применить эту функцию, пожалуйста, скачайте и установите Kutools для Excel первый.

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

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

Шаг 1. Создание сводной таблицы

  1. Выберите диапазон данных. А затем перейдите в Вставить вкладку и щелкните Сводная таблица, см. снимок экрана:
  2. В появившемся диалоговом окне выберите, где вы хотите разместить отчет сводной таблицы. Вы можете поместить его на новый или существующий лист по мере необходимости. Затем нажмите OK. Смотрите скриншот:
  3. Теперь сводная таблица вставляется в выбранную целевую ячейку. Смотрите скриншот:

Шаг 2. Настройка сводной таблицы:

  1. В Поля сводной таблицы перетащите поле, содержащее дубликаты, на панель Строка область. Это сгруппирует ваши дубликаты.
  2. Затем перетащите поля со значениями, которые вы хотите суммировать, в Наши ценности область. По умолчанию Excel суммирует значения. Посмотрите демо ниже:

Результат:

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


Объедините повторяющиеся строки и суммируйте значения с кодом VBA

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

Шаг 1. Откройте редактор модулей листов VBA и скопируйте код.

  1. Удерживая нажатой ALT + F11 ключи в Excel, чтобы открыть Microsoft Visual Basic для приложений окно.
  2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
    Код VBA: объедините повторяющиеся строки и просуммируйте значения
    Sub CombineDuplicateRowsAndSumForMultipleColumns()
    'Update by Extendoffice
        Dim SourceRange As Range, OutputRange As Range
        Dim Dict As Object
        Dim DataArray As Variant
        Dim i As Long, j As Long
        Dim Key As Variant
        Dim ColCount As Long
        Dim SumArray() As Variant
        Dim xArr As Variant
        Set SourceRange = Application.InputBox("Select the original range:", "Kutools for Excel", Type:=8)
        If SourceRange Is Nothing Then Exit Sub
        ColCount = SourceRange.Columns.Count
        Set OutputRange = Application.InputBox("Select a cell for output:", "Kutools for Excel", Type:=8)
        If OutputRange Is Nothing Then Exit Sub
        Set Dict = CreateObject("Scripting.Dictionary")
        DataArray = SourceRange.Value
        For i = 1 To UBound(DataArray, 1)
            Key = DataArray(i, 1)
            If Not Dict.Exists(Key) Then
                ReDim SumArray(1 To ColCount - 1)
                For j = 2 To ColCount
                    SumArray(j - 1) = DataArray(i, j)
                Next j
                Dict.Add Key, SumArray
            Else
                xArr = Dict(Key)
                For j = 2 To ColCount
                    xArr(j - 1) = xArr(j - 1) + DataArray(i, j)
                Next j
                Dict(Key) = xArr
            End If
        Next i
        OutputRange.Resize(Dict.Count, ColCount).ClearContents
        i = 1
        For Each Key In Dict.Keys
            OutputRange.Cells(i, 1).Value = Key
            For j = 1 To ColCount - 1
                OutputRange.Cells(i, j + 1).Value = Dict(Key)(j)
            Next j
            i = i + 1
        Next Key
        Set Dict = Nothing
        Set SourceRange = Nothing
        Set OutputRange = Nothing
    End Sub
    

Шаг 2. Выполните код

  1. После вставки этого кода нажмите F5 ключ для запуска этого кода. В поле подсказки выберите диапазон данных, который вы хотите объединить и суммировать. Затем нажмите OK.
  2. И в следующем окне подсказки выберите ячейку, в которую вы будете выводить результат, и нажмите OK.

Результат:

Теперь повторяющиеся строки объединены, и их соответствующие значения суммированы. Смотрите скриншот:


Объединение и суммирование повторяющихся строк в Excel может быть простым и эффективным. Выбирайте простую функцию консолидации, расширенные инструменты Kutools, аналитические сводные таблицы или гибкое кодирование VBA, чтобы найти решение, соответствующее вашим навыкам и потребностям. Если вы хотите узнать больше советов и рекомендаций по Excel, на нашем веб-сайте представлены тысячи руководств. нажмите здесь, чтобы получить к ним доступ. Спасибо за внимание, и мы с нетерпением ждем возможности предоставить вам еще больше полезной информации в будущем!


Статьи по теме:

  • Объедините несколько строк в одну на основе дубликатов
  • Возможно, у вас есть диапазон данных, в столбце «Название продукта» A есть несколько повторяющихся элементов, и теперь вам нужно удалить повторяющиеся записи в столбце A, но объединить соответствующие значения в столбце B. Как можно выполнить эту задачу в Excel? ?
  • Vlookup и возврат нескольких значений без дубликатов
  • Иногда вам может понадобиться vlookup и сразу вернуть несколько совпадающих значений в одну ячейку. Но если в возвращенные ячейки внесены повторяющиеся значения, как можно игнорировать дубликаты и сохранять только уникальные значения при возврате всех совпадающих значений, как показано на следующем снимке экрана в Excel?