Přejít k hlavnímu obsahu

V Excelu je běžným scénářem setkání s datovou množinou s duplicitními položkami. Často se můžete ocitnout s řadou dat, kde je klíčovou výzvou efektivně zkombinovat tyto duplicitní řádky a současně sečíst hodnoty v odpovídajícím sloupci, jak je znázorněno na následujícím snímku obrazovky. V této souvislosti se ponoříme do několika praktických metod, které vám mohou pomoci konsolidovat duplicitní data a agregovat jejich přidružené hodnoty, čímž se zvýší jak přehlednost, tak užitečnost vašich excelových sešitů.


Zkombinujte duplicitní řádky a sečtěte hodnoty pomocí funkce Consolidate

Consolidate je pro nás užitečný nástroj pro konsolidaci více listů nebo řádků v Excelu, s touto funkcí můžeme kombinovat duplicitní řádky a rychle a snadno sčítat jejich odpovídající hodnoty. Proveďte prosím následující kroky:

Krok 1: Vyberte cílovou buňku

Vyberte, kde se mají konsolidovaná data zobrazovat.

Krok 2: Otevřete funkci konsolidace a nastavte konsolidaci

  1. klikněte Data > Konsolidujte , viz screenshot:
  2. v Konsolidujte dialogové okno:
    • (1.) Vyberte Součet od funkce rozbalovací seznam;
    • (2.) Klepnutím vyberte rozsah, který chcete sloučit Odkaz krabice;
    • (3.) Zkontrolovat Horní řádek a Levý sloupec od Použít štítky v volba;
    • (4.) Nakonec klikněte OK .

Výsledek:

Excel zkombinuje všechny duplikáty nalezené v prvním sloupci a sečte jejich odpovídající hodnoty v sousedních sloupcích, jak je znázorněno na následujícím obrázku:

Poznámky:
  • Pokud rozsah nezahrnuje řádek záhlaví, ujistěte se, že zrušte zaškrtnutí horního řádku z Použít štítky v volba.
  • Pomocí této funkce lze výpočty konsolidovat pouze na základě prvního sloupce (zcela vlevo) dat.

Kombinujte duplicitní řádky a sečtete hodnoty pomocí výkonné funkce – Kutools

Pokud jste nainstalovali Kutools pro Excel, to je Pokročilé kombinování řádků Tato funkce vám umožňuje snadno kombinovat duplicitní řádky a poskytuje možnosti sčítání, počítání, průměrování nebo provádění dalších výpočtů na vašich datech. Navíc tato funkce není omezena pouze na jeden klíčový sloupec, dokáže zpracovat více klíčových sloupců, takže složité úkoly konsolidace dat jsou mnohem snazší.

Poznámka: Pokud chcete použít toto Pokročilé kombinování řádků funkce, prosím stáhnout a nainstalovat Kutools pro Excel jako první.

Po instalaci Kutools pro Excel, vyberte rozsah dat a poté klikněte na Kutools > Sloučit a rozdělit > Pokročilé kombinování řádků.

v Pokročilé kombinování řádků dialogové okno, nastavte prosím následující operace:

  1. Klikněte na název sloupce, na základě kterého chcete zkombinovat duplikáty, zde kliknu na Produkt a poté vyberte Primární klíč z rozevíracího seznamu v Operace sloupec;
  2. Potom vyberte název sloupce, do kterého chcete sečíst hodnoty, a poté vyberte Součet z rozevíracího seznamu v Operace sloupec;
  3. Pokud jde o ostatní sloupce, můžete si vybrat operaci, kterou potřebujete, jako je kombinace hodnot s konkrétním oddělovačem nebo provedení určitého výpočtu; (tento krok lze ignorovat, pokud máte pouze dva sloupce)
  4. Nakonec si můžete prohlédnout kombinovaný výsledek a poté kliknout OK .

Výsledek:

Nyní jsou duplicitní hodnoty ve sloupci klíče zkombinovány a další odpovídající hodnoty jsou sečteny podle následujícího obrázku:

Tip:
  • Pomocí této užitečné funkce můžete také kombinovat řádky na základě duplicitní hodnoty buňky, jak ukazuje následující ukázka:
  • Tato funkce podporuje Undo, pokud chcete obnovit původní data, stačí stisknout Ctrl + Z.
  • Chcete-li použít tuto funkci, prosím stáhnout a nainstalovat Kutools pro Excel jako první.

Zkombinujte duplicitní řádky a sečtěte hodnoty s kontingenční tabulkou

Kontingenční tabulka v Excelu poskytuje dynamický způsob, jak přeskupovat, seskupovat a sumarizovat data. Tato funkce se stává neuvěřitelně užitečnou, když čelíte datové sadě plné duplicitních záznamů a potřebujete sečíst odpovídající hodnoty.

Krok 1: Vytvoření kontingenční tabulky

  1. Vyberte rozsah dat. A pak přejděte na Vložit kartu a klepněte na tlačítko Pivot Table, viz screenshot:
  2. Ve vyskakovacím dialogovém okně vyberte, kam chcete umístit sestavu kontingenční tabulky, můžete ji podle potřeby umístit na nový list nebo existující list. Poté klikněte OK. Viz snímek obrazovky:
  3. Nyní se do vybrané cílové buňky vloží kontingenční tabulka. Viz snímek obrazovky:

Krok 2: Konfigurace kontingenční tabulky:

  1. v Pole kontingenční tabulky přetáhněte pole obsahující duplikáty do Řádek plocha. Tím seskupí vaše duplikáty.
  2. Dále přetáhněte pole s hodnotami, které chcete sečíst Hodnoty plocha. Ve výchozím nastavení Excel sečte hodnoty. Podívejte se na ukázku níže:

Výsledek:

Kontingenční tabulka nyní zobrazuje vaše data se zkombinovanými duplikáty a jejich sečtenými hodnotami, což nabízí jasné a stručné zobrazení pro analýzu. Viz snímek obrazovky:


Zkombinujte duplicitní řádky a sečtěte hodnoty pomocí kódu VBA

Pokud vás zajímá kód VBA, v této části vám poskytneme kód VBA pro konsolidaci duplicitních řádků a sečtení odpovídajících hodnot v dalších sloupcích. Proveďte prosím následující kroky:

Krok 1: Otevřete editor modulu listu VBA a zkopírujte kód

  1. Podržte stisknuté tlačítko ALT + F11 klávesy v aplikaci Excel k otevření Microsoft Visual Basic pro aplikace okno.
  2. klikněte Vložit > Modula vložte následující kód do Modul Okno.
    Kód VBA: Zkombinujte duplicitní řádky a sečtěte hodnoty
    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
    

Krok 2: Spusťte kód

  1. Po vložení tohoto kódu stiskněte F5 klíč ke spuštění tohoto kódu. V poli výzvy vyberte rozsah dat, který chcete zkombinovat a sečíst. A pak klikněte OK.
  2. A v poli další výzvy vyberte buňku, do které budete mít výstup výsledku, a klikněte OK.

Výsledek:

Nyní jsou duplicitní řádky sloučeny a jejich odpovídající hodnoty byly sečteny. Viz snímek obrazovky:


Kombinování a sčítání duplicitních řádků v Excelu může být jednoduché a efektivní. Vyberte si ze snadné funkce Consolidate, pokročilých Kutools, analytických kontingenčních tabulek nebo flexibilního kódování VBA a najděte řešení, které vyhovuje vašim dovednostem a potřebám. Pokud máte zájem prozkoumat další tipy a triky pro Excel, naše webové stránky nabízejí tisíce výukových programů pro přístup k nim klikněte sem. Děkujeme za přečtení a těšíme se, že vám v budoucnu poskytneme další užitečné informace!


Související články:

  • Spojte více řádků do jednoho na základě duplikátů
  • Možná máte řadu dat, ve sloupci Název produktu A jsou nějaké duplicitní položky a nyní musíte odstranit duplicitní položky ve sloupci A, ale zkombinovat odpovídající hodnoty ve sloupci B. Jak by mohl tento úkol v Excelu dosáhnout ?
  • Vlookup a vrátit více hodnot bez duplikátů
  • Někdy můžete chtít vlookup a vrátit více shodných hodnot do jedné buňky najednou. Ale pokud se do vrácených buněk naplní několik opakovaných hodnot, jak byste mohli ignorovat duplikáty a zachovat pouze jedinečné hodnoty při vracení všech odpovídajících hodnot jako následující snímek obrazovky zobrazený v aplikaci Excel?
  • Zkombinujte řádky se stejným ID/názvem
  • Například máte zobrazenou tabulku, jak je uvedeno níže, a potřebujete kombinovat řádky s ID objednávky, nějaké nápady? Zde vám tento článek představí dvě řešení.
Comments (30)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Em planilha sem cálculo número, onde eu tenho uma lista de prestadores de serviço para determinadas empresas, como faço para deixar classificado por empresas, por ordem de empresas? Se é possível.
Na planilha eu tenho, o nome da pessoa, razão social e empresa. Neste caso algumas empresas repetem, gostaria de classificar automático, sem precisar refazer um por um.
This comment was minimized by the moderator on the site
is there a way to save the specific merging and combining settings so that i can reuse them for future workbooks?
This comment was minimized by the moderator on the site
Thanks for your help.

This comment was minimized by the moderator on the site
SN SAD No Unit Item No Description Qty CIF_Value ID_EXD AID CSF ARF ECS RCF RDF IFT IDP AIT VAT
1 M200 UNT 1 Pen 194 500 50 0 0 0 0 0 0 0 0 0 65
2 M200 UNT 2 Pencil 241 250 25 0 0 0 0 0 0 0 0 0 32.5
3 M200 UNT 3 Cutter 204 400 40 0 0 0 0 0 0 0 0 0 52
4 M200 UNT 4 Copy 171 600 60 0 0 0 0 0 0 0 0 0 78
5 M300 KGM 1 Cup 220 250 25 0 500 0 0 0 0 0 0 0 32.5
6 M300 KGM 2 Plate 40 350 35 155 0 0 0 0 0 0 0 0 45.5
7 M300 UNT 3 Bottle 2 150 15 131 0 0 0 0 0 0 0 0 19.5
8 M300 UNT 4 Glass 2 90 9 34 0 0 0 0 0 0 0 0 11.7
9 M400 null 1 Shirt 20 800 80 0 0 0 0 0 0 0 0 0 104
10 M400 KGM 2 Pant 5 5000 500 0 0 0 0 0 0 0 0 0 650
11 M400 null 3 Shoe 12 7200 720 0 0 0 0 0 0 0 0 0 936
12 M400 MTR 4 Sandle 40 1600 160 0 0 0 0 0 0 0 0 0 208
13 M400 UNT 5 Belt 100 2000 200 0 0 0 0 0 0 0 0 0 260
how to sum cif value and remove duplicate No. (SAD No.) Please help me.
This comment was minimized by the moderator on the site
I am sooooo happy & glad with your tips. Allah bless you.
This comment was minimized by the moderator on the site
Sub MergeSameCells()
Application.DisplayAlerts = False

Dim rng As Range

MergeCells:

For Each rng In Selection
If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then
Range(rng, rng.Offset(1, 0)).Merge
GoTo MergeCells
End If
Next

End Sub
This comment was minimized by the moderator on the site
An absolute mess of an explantation. Thanks for the effort but it did nothing to help.
This comment was minimized by the moderator on the site
LOVE IT!!! YOUR SAVE MY LIFE!!
This comment was minimized by the moderator on the site
Ifsum=(columns include,start point row,sum column)
Example ifsum=(A:D,B:2,D:D)
WAY EASIER!
This comment was minimized by the moderator on the site
Hi Am chinnaraju

can u please assist for this. Any one?

=VLOOKUP(M5,E:F,2,)


Thanks in advance.
This comment was minimized by the moderator on the site
Needs to be:
=VLOOKUP(M5,E:F,2,FALSE)
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations