Połącz zduplikowane wiersze i zsumuj wartości w Excelu (proste triki)
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
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ę
- Kliknij Dane > Konsolidowaćzobacz zrzut ekranu:
- 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:
- 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.
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:
- 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;
- Następnie wybierz nazwę kolumny, w której chcesz zsumować wartości, a następnie wybierz Suma z listy rozwijanej w Działanie kolumna;
- 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)
- 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:
- 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
- Wybierz zakres danych. A następnie przejdź do wstawka kartę i kliknij Pivot Tablezobacz zrzut ekranu:
- 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:
- Teraz w wybranej komórce docelowej zostanie wstawiona tabela przestawna. Zobacz zrzut ekranu:
Krok 2: Konfiguracja tabeli przestawnej:
- W Pola tabeli przestawnej przeciągnij pole zawierające duplikaty do panelu Rząd obszar. Spowoduje to pogrupowanie duplikatów.
- 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
- Przytrzymaj przycisk ALT + F11 klucze w programie Excel, aby otworzyć plik Microsoft Visual Basic for Applications okno.
- Kliknij wstawka > Modułi wklej następujący kod w Moduł Okno.
Kod VBA: połącz zduplikowane wiersze i zsumuj wartościSub 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
- 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.
- 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?
- Połącz wiersze o tym samym identyfikatorze/nazwie
- Na przykład masz tabelę pokazaną na poniższym zrzucie ekranu i musisz połączyć wiersze z identyfikatorami zamówień, jakieś pomysły? W tym artykule przedstawimy Ci dwa rozwiązania.
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!