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í.