Объединение Дублирующая строка и суммирование их значений в 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. Наконец, вы сможете просмотреть объединённый результат и нажать кнопку «OK».
5. 

Результат:

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

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

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

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

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

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

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

Результат:

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

Использование кода 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», чтобы запустить его. В появившемся окне выберите диапазон данных, которые нужно объединить и просуммировать, затем нажмите «OK».
   
2. В следующем окне выберите ячейку для вывода результата и нажмите «OK».
   

Результат:

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

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

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