Kombinujte duplicitní řádky a sečtete hodnoty v Excelu (jednoduché triky)
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
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
- klikněte Data > Konsolidujte , viz screenshot:
- 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:
- 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ší.
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:
- 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;
- 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;
- 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)
- 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:
- 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
- Vyberte rozsah dat. A pak přejděte na Vložit kartu a klepněte na tlačítko Pivot Table, viz screenshot:
- 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:
- Nyní se do vybrané cílové buňky vloží kontingenční tabulka. Viz snímek obrazovky:
Krok 2: Konfigurace kontingenční tabulky:
- v Pole kontingenční tabulky přetáhněte pole obsahující duplikáty do Řádek plocha. Tím seskupí vaše duplikáty.
- 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
- Podržte stisknuté tlačítko ALT + F11 klávesy v aplikaci Excel k otevření Microsoft Visual Basic pro aplikace okno.
- 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 hodnotySub 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
- 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.
- 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í.
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!