Объединение дублирующихся строк и суммирование их значений в Excel (Простые хитрости)

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

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

• С помощью функции Консолидация
• С мощной функцией – Kutools
• С помощью сводной таблицы
• С помощью кода VBA

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

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

Шаг 1: Выберите целевую ячейку

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

Шаг 2: Доступ к функции Консолидации и настройка консолидации

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

Результат:

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

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

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

После установки «Kutools для Excel» выберите диапазон данных, затем нажмите «Kutools» > «Объединить и разделить» > «Расширенное объединение строк».

В диалоговом окне «Расширенное объединение строк» настройте следующие операции:

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

Результат:

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

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

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

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

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

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

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

Результат:

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

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

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

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

1. Удерживайте клавиши «ALT + F11» в Excel, чтобы открыть окно «Microsoft Visual Basic for Applications».
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», чтобы запустить этот код. В появившемся окне выберите диапазон данных, который вы хотите объединить и просуммировать. Затем нажмите «ОК».
   
2. А в следующем окне выберите ячейку, куда вы хотите вывести результат, и нажмите «ОК».
   

Результат:

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

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

Связанные статьи: