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

Комбинирайте дублиращи се редове и сумирайте стойностите

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

Комбинирайте дублиращи се редове и сумирайте стойностите с функцията Consolidate

Consolidate е полезен инструмент за консолидиране на множество работни листове или редове в Excel, с тази функция можем да комбинираме дублиращи се редове и да обобщим съответните им стойности бързо и лесно. Моля, направете следните стъпки:

Стъпка 1: Изберете целева клетка

Изберете къде искате да се показват консолидираните данни.

Стъпка 2: Достъп до функцията за консолидиране и настройка на консолидацията

1. Кликнете Дата > Консолидиране, вижте екранна снимка:
   
2. в Консолидиране диалогов прозорец:
   • (1.) Изберете Сума от функция падащ списък;
   • (2.) Щракнете, за да изберете диапазона, който искате да консолидирате в препратка кутия;
   • (3.) Проверка Горния ред намлява Лявата колона от Използвайте етикети в опция;
   • (4.) Накрая щракнете OK бутон.
   • 

Резултат:

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 код, в този раздел ще дадем VBA код за консолидиране на дублирани редове и сумиране на съответните стойности в други колони. Моля, направете следните стъпки:

Стъпка 1: Отворете редактора на модула на лист VBA и копирайте кода

1. Задръжте надолу ALT + F11 клавиши в Excel, за да отворите Microsoft Visual Basic за приложения прозорец.
2. Кликнете Поставете > Модулии поставете следния код в Модули Window.
   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 може да бъде лесно и ефективно. Изберете от лесната функция Consolidate, усъвършенстваните Kutools, аналитичните обобщени таблици или гъвкавото VBA кодиране, за да намерите решение, което отговаря на вашите умения и нужди. Ако се интересувате да проучите повече съвети и трикове за Excel, моля, нашият уебсайт предлага хиляди уроци щракнете тук за достъп до тях. Благодарим ви, че прочетохте и очакваме с нетърпение да ви предоставим още полезна информация в бъдеще!

Свързани членове: