Przejdź do głównej zawartości

W programie Excel częstym scenariuszem jest napotkanie zestawu danych ze zduplikowanymi wpisami. Często możesz spotkać się z zakresem danych, dla którego kluczowym wyzwaniem jest efektywne połączenie tych zduplikowanych wierszy przy jednoczesnym sumowaniu wartości w odpowiedniej kolumnie, jak pokazano na poniższym zrzucie ekranu. W tym kontekście przyjrzymy się kilku praktycznym metodom, które mogą pomóc w konsolidacji zduplikowanych danych i agregowaniu powiązanych z nimi wartości, zwiększając zarówno przejrzystość, jak i użyteczność skoroszytów programu Excel.


Połącz zduplikowane wiersze i zsumuj wartości za pomocą funkcji Konsoliduj

Konsolidacja to dla nas przydatne narzędzie do konsolidacji wielu arkuszy lub wierszy w programie Excel. Dzięki tej funkcji możemy łączyć zduplikowane wiersze i szybko i łatwo sumować odpowiadające im wartości. Wykonaj następujące czynności:

Krok 1: Wybierz komórkę docelową

Wybierz, gdzie mają się pojawiać skonsolidowane dane.

Krok 2: Uzyskaj dostęp do funkcji konsolidacji i skonfiguruj konsolidację

  1. Kliknij Dane > Konsolidowaćzobacz zrzut ekranu:
  2. W Konsolidować Okno dialogowe:
    • (1.) Wybierz Suma od Funkcjonować Lista rozwijana;
    • (2.) Kliknij, aby wybrać zakres, który chcesz skonsolidować Numer Referencyjny pudełko;
    • (3.) Sprawdź Górny rząd i Lewa kolumna od Użyj etykiet w opcja;
    • (4.) Na koniec kliknij OK przycisk.

Wynik:

Excel połączy wszystkie duplikaty znalezione w pierwszej kolumnie i zsumuje odpowiadające im wartości w sąsiednich kolumnach, jak pokazano na poniższym zrzucie ekranu:

Uwagi:
  • Jeśli zakres nie zawiera wiersza nagłówka, upewnij się, że tak odznacz Górny rząd z Użyj etykiet w opcja.
  • Dzięki tej funkcji obliczenia można konsolidować jedynie na podstawie pierwszej kolumny (najbardziej lewej) danych.

Połącz zduplikowane wiersze i zsumuj wartości za pomocą potężnej funkcji – Kutools

Jeśli masz zainstalowany Kutools dla programu Excel, jego Zaawansowane wiersze łączenia Funkcja umożliwia łatwe łączenie zduplikowanych wierszy, zapewniając opcje sumowania, liczenia, uśredniania lub wykonywania innych obliczeń na danych. Co więcej, ta funkcja nie ogranicza się do jednej kluczowej kolumny, może obsługiwać wiele kluczowych kolumn, co znacznie ułatwia złożone zadania konsolidacji danych.

Note: Jeśli chcesz tego użyć Zaawansowane wiersze łączenia cecha, proszę pobierz i zainstaluj Kutools dla Excela pierwszy.

Po zainstalowaniu Kutools dla programu Excel, wybierz zakres danych i kliknij Kutools > Połącz i podziel > Zaawansowane wiersze łączenia.

W Zaawansowane wiersze łączenia w oknie dialogowym, ustaw następujące operacje:

  1. Kliknij nazwę kolumny, na podstawie której chcesz połączyć duplikaty, tutaj kliknę Produkt, a następnie wybierz Główny klucz z listy rozwijanej w Działanie kolumna;
  2. Następnie wybierz nazwę kolumny, w której chcesz zsumować wartości, a następnie wybierz Suma z listy rozwijanej w Działanie kolumna;
  3. Jeśli chodzi o pozostałe kolumny, możesz wybrać potrzebną operację, np. połączenie wartości z określonym separatorem lub wykonanie określonego obliczenia; (ten krok można zignorować, jeśli masz tylko dwie kolumny)
  4. Wreszcie możesz wyświetlić podgląd połączonego wyniku i kliknąć OK przycisk.

Wynik:

Teraz zduplikowane wartości w kolumnie klucza są łączone, a inne odpowiadające im wartości są sumowane, jak pokazano na poniższym zrzucie ekranu:

Porady:
  • Dzięki tej przydatnej funkcji możesz także łączyć wiersze na podstawie zduplikowanej wartości komórki, jak pokazano poniżej:
  • Ta cecha obsługuje Cofnij, jeśli chcesz odzyskać oryginalne dane, po prostu naciśnij Ctrl + Z.
  • Aby zastosować tę funkcję, proszę pobierz i zainstaluj Kutools dla Excela pierwszy.

Połącz zduplikowane wiersze i zsumuj wartości za pomocą tabeli przestawnej

Tabela przestawna w programie Excel umożliwia dynamiczną zmianę układu, grupowanie i podsumowywanie danych. Ta funkcja staje się niezwykle przydatna, gdy masz do czynienia ze zbiorem danych wypełnionym zduplikowanymi wpisami i musisz zsumować odpowiadające im wartości.

Krok 1: Tworzenie tabeli przestawnej

  1. Wybierz zakres danych. A następnie przejdź do wstawka kartę i kliknij Pivot Tablezobacz zrzut ekranu:
  2. W wyskakującym oknie dialogowym wybierz miejsce, w którym chcesz umieścić raport tabeli przestawnej. Możesz umieścić go w nowym lub istniejącym arkuszu, według potrzeb. Następnie kliknij OK. Zobacz zrzut ekranu:
  3. Teraz w wybranej komórce docelowej zostanie wstawiona tabela przestawna. Zobacz zrzut ekranu:

Krok 2: Konfiguracja tabeli przestawnej:

  1. W Pola tabeli przestawnej przeciągnij pole zawierające duplikaty do panelu Rząd obszar. Spowoduje to pogrupowanie duplikatów.
  2. Następnie przeciągnij pola z wartościami, które chcesz zsumować Wartości obszar. Domyślnie Excel sumuje wartości. Zobacz demo poniżej:

Wynik:

Tabela przestawna wyświetla teraz dane wraz z połączonymi duplikatami i zsumowanymi ich wartościami, oferując jasny i zwięzły widok do analizy. Zobacz zrzut ekranu:


Połącz zduplikowane wiersze i zsumuj wartości z kodem VBA

Jeśli interesuje Cię kod VBA, w tej sekcji podamy kod VBA do konsolidacji zduplikowanych wierszy i sumowania odpowiednich wartości w innych kolumnach. Wykonaj następujące czynności:

Krok 1: Otwórz edytor modułu arkusza VBA i skopiuj kod

  1. Przytrzymaj przycisk ALT + F11 klucze w programie Excel, aby otworzyć plik Microsoft Visual Basic for Applications okno.
  2. Kliknij wstawka > Modułi wklej następujący kod w Moduł Okno.
    Kod VBA: połącz zduplikowane wiersze i zsumuj wartości
    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: Wykonaj kod

  1. Po wklejeniu tego kodu naciśnij F5 klucz do uruchomienia tego kodu. W polu zachęty wybierz zakres danych, który chcesz połączyć i zsumować. A następnie kliknij OK.
  2. W następnym polu zachęty wybierz komórkę, w której chcesz wyprowadzić wynik, i kliknij OK.

Wynik:

Teraz zduplikowane wiersze zostały scalone, a odpowiadające im wartości zostały zsumowane. Zobacz zrzut ekranu:


Łączenie i sumowanie zduplikowanych wierszy w programie Excel może być proste i wydajne. Wybierz łatwą funkcję Consolidate, zaawansowane Kutools, analityczne tabele przestawne lub elastyczne kodowanie VBA, aby znaleźć rozwiązanie odpowiadające Twoim umiejętnościom i potrzebom. Jeśli chcesz poznać więcej porad i wskazówek dotyczących Excela, nasza witryna internetowa oferuje tysiące samouczków kliknij tutaj, aby uzyskać do nich dostęp. Dziękujemy za przeczytanie i mamy nadzieję, że w przyszłości przekażemy Ci więcej przydatnych informacji!


Podobne artykuły:

  • Połącz wiele wierszy w jeden na podstawie duplikatów
  • Być może masz zakres danych, w kolumnie Nazwa produktu A znajdują się zduplikowane pozycje i teraz musisz usunąć zduplikowane wpisy w kolumnie A, ale połączyć odpowiednie wartości w kolumnie B. Jak można ominąć to zadanie w Excelu ?
  • Wyszukaj i zwróć wiele wartości bez duplikatów
  • Czasami możesz chcieć pominąć i zwrócić wiele dopasowanych wartości jednocześnie do jednej komórki. Ale jeśli w zwracanych komórkach znajdują się powtarzające się wartości, w jaki sposób można zignorować duplikaty i zachować unikalne wartości tylko podczas zwracania wszystkich pasujących wartości, jak pokazano na zrzucie ekranu w programie Excel?
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