Wie extrahiert man eindeutige Werte aus mehreren Spalten in Excel?
Angenommen, Sie haben mehrere Spalten mit mehreren Werten, wobei einige Werte innerhalb derselben Spalte oder über verschiedene Spalten hinweg wiederholt werden. Nun müssen Sie die Werte identifizieren, die über alle Spalten hinweg eindeutig sind. Gibt es schnelle Methoden, um diese eindeutigen Werte aus mehreren Spalten in Excel zu extrahieren? Lassen Sie uns einige effiziente Lösungen erkunden.
Einzigartige Werte aus mehreren Spalten mit Formeln extrahieren
Dieser Abschnitt behandelt zwei Formeln: eine verwendet eine Array-Formel, die für alle Excel-Versionen geeignet ist, und die andere nutzt eine dynamische Array-Formel speziell für Excel 365.
Einzigartige Werte aus mehreren Spalten mit Array-Formel für alle Excel-Versionen extrahieren
Für Benutzer mit jeder Excel-Version können Array-Formeln ein leistungsstarkes Werkzeug sein, um eindeutige Werte aus mehreren Spalten zu extrahieren. So geht’s:
1. Angenommen, Ihre Werte befinden sich im Bereich A2:C9, geben Sie bitte die folgende Formel in Zelle E2 ein:
=INDIREKT(TEXT(MIN(WENN(($A$2:$C$9<>"")*(ZÄHLENWENN($E$1:E1;$A$2:$C$9)=0);ZEILE($2:$9)*100+SPALTE($A:$C);7^8));"R0C00");)&""
2. Drücken Sie dann gleichzeitig die Tasten Umschalt + Strg + Eingabetaste, und ziehen Sie den Ausfüllkästchen nach unten, um die eindeutigen Werte zu extrahieren, bis leere Zellen erscheinen. Siehe Screenshot:
- $A$2:$C$9: Dies gibt den zu überprüfenden Datenbereich an, also die Zellen von A2 bis C9.
- WENN(($A$2:$C$9<>"")*(ZÄHLENWENN($E$1:E1;$A$2:$C$9)=0); ZEILE($2:$9)*100+SPALTE($A:$C); 7^8):
- $A$2:$C$9<>"" überprüft, ob die Zellen im Bereich nicht leer sind.
- ZÄHLENWENN($E$1:E1;$A$2:$C$9)=0 bestimmt, ob die Werte dieser Zellen noch nicht im Bereich von E1 bis E1 aufgeführt wurden.
- Wenn beide Bedingungen erfüllt sind (d.h., der Wert ist nicht leer und noch nicht in Spalte E aufgeführt), berechnet die WENN-Funktion eine eindeutige Zahl basierend auf ihrer Zeile und Spalte (ZEILE($2:$9)*100+SPALTE($A:$C)).
- Wenn die Bedingungen nicht erfüllt sind, gibt die Funktion eine große Zahl (7^8) zurück, die als Platzhalter dient.
- MIN(...): Findet die kleinste Zahl, die von der oben genannten WENN-Funktion zurückgegeben wurde, was der Position des nächsten eindeutigen Werts entspricht.
- TEXT(...;"R0C00"): Konvertiert diese kleinste Zahl in eine R1C1-Stiladresse. Der Formatcode R0C00 gibt die Umwandlung der Zahl in das Excel-Zellreferenzformat an.
- INDIREKT(...): Verwendet die INDIREKT-Funktion, um die im vorherigen Schritt generierte R1C1-Stiladresse wieder in eine normale A1-Stil-Zellreferenz umzuwandeln. Die INDIREKT-Funktion ermöglicht die Zellreferenzierung basierend auf dem Inhalt einer Textzeichenfolge.
- &"": Das Anhängen von &"" am Ende der Formel stellt sicher, dass die endgültige Ausgabe als Text behandelt wird, sodass selbst Zahlen als Text angezeigt werden.
Einzigartige Werte aus mehreren Spalten mit Formel für Excel 365, Excel 2021 und neuere Versionen extrahieren
Excel 365, Excel 2021 und neuere Versionen unterstützen dynamische Arrays, was das Extrahieren eindeutiger Werte aus mehreren Spalten viel einfacher macht:
Geben Sie bitte die folgende Formel in eine leere Zelle ein, wo Sie das Ergebnis platzieren möchten, und drücken Sie dann die Eingabetaste, um alle eindeutigen Werte auf einmal zu erhalten. Siehe Screenshot:
=UNIQUE(TOCOL(A2:C9,1))
Einzigartige Werte aus mehreren Spalten mit Kutools AI-Assistent extrahieren
Nutzen Sie die Kraft des "Kutools AI-Assistent", um nahtlos eindeutige Werte aus mehreren Spalten in Excel zu extrahieren. Mit nur wenigen Klicks durchsucht dieses intelligente Tool Ihre Daten, identifiziert und listet eindeutige Einträge über einen beliebigen ausgewählten Bereich auf. Vergessen Sie die Komplexität von komplizierten Formeln oder VBA-Code; Nutzen Sie die Effizienz von "Kutools AI-Assistent" und verwandeln Sie Ihren Excel-Workflow in ein produktiveres und fehlerfreies Erlebnis.
Nach der Installation von Kutools für Excel klicken Sie bitte auf "Kutools AI" > "AI-Assistent", um den Bereich "Kutools AI-Assistent" zu öffnen:
- Geben Sie Ihre Anforderung in das Chatfenster ein und klicken Sie auf die Schaltfläche "Senden" oder drücken Sie die Eingabetaste, um die Frage zu senden;
"Extrahieren Sie eindeutige Werte aus dem Bereich A2:C9, ignorieren Sie leere Zellen und platzieren Sie die Ergebnisse ab E2:" - Nach der Analyse klicken Sie auf die Schaltfläche "Ausführen", um fortzufahren. Kutools AI-Assistent verarbeitet Ihre Anfrage mithilfe von KI und gibt die Ergebnisse direkt in der angegebenen Zelle in Excel zurück.
Einzigartige Werte aus mehreren Spalten mit PivotTable extrahieren
Wenn Sie mit der Pivot-Tabelle vertraut sind, können Sie ganz einfach die eindeutigen Werte aus mehreren Spalten mit den folgenden Schritten extrahieren:
1. Fügen Sie zunächst eine neue leere Spalte links neben Ihren Daten ein. In diesem Beispiel füge ich Spalte A neben den ursprünglichen Daten ein.
2. Klicken Sie auf eine Zelle in Ihren Daten und drücken Sie die Tasten "Alt+D", dann sofort die Taste "P", um den Assistenten "PivotTable und PivotChart" zu öffnen. Wählen Sie "Mehrere Konsolidierungsbereiche" im ersten Schritt des Assistenten aus, siehe Screenshot:
3. Klicken Sie dann auf die Schaltfläche "Weiter", aktivieren Sie die Option "Ein einzelnes Seitenfeld für mich erstellen" im zweiten Schritt des Assistenten, siehe Screenshot:
4. Klicken Sie weiter auf "Weiter", wählen Sie den Datenbereich aus, einschließlich der neuen linken Spalte, und klicken Sie auf die Schaltfläche "Hinzufügen", um den Datenbereich zur Liste "Alle Bereiche" hinzuzufügen, siehe Screenshot:
5. Nachdem Sie den Datenbereich ausgewählt haben, klicken Sie weiter auf "Weiter". Im dritten Schritt des Assistenten wählen Sie aus, wo Sie den PivotTable-Bericht platzieren möchten.
6. Klicken Sie abschließend auf "Fertig", um den Assistenten abzuschließen, und eine Pivot-Tabelle wird im aktuellen Arbeitsblatt erstellt. Deaktivieren Sie dann alle Felder im Abschnitt "Felder zum Bericht hinzufügen", siehe Screenshot:
7. Aktivieren Sie dann das Feld "Wert" oder ziehen Sie den Wert in das Label "Zeilen". Nun erhalten Sie die eindeutigen Werte aus den mehreren Spalten wie folgt:
Einzigartige Werte aus mehreren Spalten mit VBA-Code extrahieren
Mit dem folgenden VBA-Code können Sie auch die eindeutigen Werte aus mehreren Spalten extrahieren.
1. Halten Sie die Tasten "ALT + F11" gedrückt, und das Fenster "Microsoft Visual Basic for Applications" öffnet sich.
2. Klicken Sie auf "Einfügen" > "Modul" und fügen Sie den folgenden Code im Modulfenster ein.
VBA: Einzigartige Werte aus mehreren Spalten extrahieren
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. Drücken Sie dann F5, um diesen Code auszuführen, und ein Dialogfeld wird angezeigt, das Sie auffordert, den zu verwendenden Datenbereich auszuwählen. Siehe Screenshot:
4. Klicken Sie dann auf OK, und ein weiteres Dialogfeld erscheint, das Sie auffordert, einen Ort für das Ergebnis auszuwählen, siehe Screenshot:
5. Klicken Sie auf OK, um dieses Dialogfeld zu schließen, und alle eindeutigen Werte wurden auf einmal extrahiert.
Weitere relevante Artikel:
- Anzahl der eindeutigen und unterschiedlichen Werte aus einer Liste zählen
- Angenommen, Sie haben eine lange Liste von Werten mit einigen doppelten Einträgen, und Sie möchten nun die Anzahl der eindeutigen Werte (die Werte, die nur einmal in der Liste vorkommen) oder der unterschiedlichen Werte (alle verschiedenen Werte in der Liste, was bedeutet: eindeutige Werte + erste doppelte Werte) in einer Spalte zählen, wie im linken Screenshot gezeigt. In diesem Artikel werde ich darüber sprechen, wie man diese Aufgabe in Excel bewältigt.
- Einzigartige Werte basierend auf Kriterien in Excel extrahieren
- Angenommen, Sie haben den folgenden Datenbereich, aus dem Sie nur die eindeutigen Namen in Spalte B basierend auf einem bestimmten Kriterium in Spalte A auflisten möchten, um das Ergebnis wie im folgenden Screenshot gezeigt zu erhalten. Wie könnten Sie diese Aufgabe in Excel schnell und einfach lösen?
- Nur eindeutige Werte in Excel zulassen
- Wenn Sie nur eindeutige Werte in einer Spalte eines Arbeitsblatts eingeben und Duplikate verhindern möchten, wird dieser Artikel einige schnelle Tricks vorstellen, um diese Aufgabe zu bewältigen.
- Summe eindeutiger Werte basierend auf Kriterien in Excel
- Beispiel: Ich habe einen Datenbereich, der Name- und Bestellspalten enthält. Nun möchte ich nur die eindeutigen Werte in der Bestellspalte basierend auf der Namensspalte summieren, wie im folgenden Screenshot gezeigt. Wie kann man diese Aufgabe in Excel schnell und einfach lösen?
Beste Büroproduktivitätswerkzeuge
Verbessern Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über300 erweiterte Funktionen zur Steigerung der Produktivität und Zeitersparnis. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt eine Registerkartenoberfläche zu Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie die Bearbeitung und das Lesen mit Registerkarten in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters, anstatt in neuen Fenstern.
- Steigert Ihre Produktivität um50 % und reduziert täglich hunderte von Mausklicks für Sie!