Note: The other languages of the website are Google-translated. Back to English
Einloggen Anmelden

Wie extrahiere ich eindeutige Werte basierend auf Kriterien in Excel?

Angenommen, Sie haben den linken Datenbereich, in dem Sie nur die eindeutigen Namen von Spalte B basierend auf einem bestimmten Kriterium von Spalte A auflisten möchten, um das Ergebnis wie im folgenden Screenshot zu erhalten. Wie können Sie diese Aufgabe in Excel schnell und einfach erledigen?

Extrahieren Sie eindeutige Werte basierend auf Kriterien mit der Array-Formel

Extrahieren Sie eindeutige Werte basierend auf mehreren Kriterien mit der Array-Formel

Extrahieren Sie eindeutige Werte aus einer Liste von Zellen mit einer nützlichen Funktion

 

Extrahieren Sie eindeutige Werte basierend auf Kriterien mit der Array-Formel

Um diesen Job zu lösen, können Sie eine komplexe Array-Formel anwenden. Gehen Sie dazu wie folgt vor:

1. Geben Sie die folgende Formel in eine leere Zelle ein, in der Sie das Extraktionsergebnis auflisten möchten. In diesem Beispiel werde ich es in Zelle E2 einfügen und dann drücken Umschalt + Strg + Eingabetaste Schlüssel, um den ersten eindeutigen Wert zu erhalten.

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. Ziehen Sie dann den Füllgriff nach unten auf die Zellen, bis leere Zellen angezeigt werden. Jetzt wurden alle eindeutigen Werte aufgelistet, die auf dem spezifischen Kriterium basieren (siehe Abbildung):

Hinweis: In der obigen Formel: B2: B15 Ist der Spaltenbereich die eindeutigen Werte, aus denen Sie extrahieren möchten? A2: A15 Ist die Spalte enthält das Kriterium, auf dem Sie basieren? D2 gibt das Kriterium an, nach dem Sie die eindeutigen Werte basierend auf und auflisten möchten E1 ist die Zelle über Ihrer eingegebenen Formel.

Extrahieren Sie eindeutige Werte basierend auf mehreren Kriterien mit der Array-Formel

Wenn Sie die eindeutigen Werte basierend auf zwei Bedingungen extrahieren möchten, können Sie mit einer anderen Array-Formel einen Gefallen tun: Gehen Sie wie folgt vor:

1. Geben Sie die folgende Formel in eine leere Zelle ein, in der Sie die eindeutigen Werte auflisten möchten. In diesem Beispiel werde ich sie in Zelle G2 einfügen und dann drücken Umschalt + Strg + Eingabetaste Schlüssel, um den ersten eindeutigen Wert zu erhalten.

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. Ziehen Sie dann den Füllgriff nach unten auf die Zellen, bis leere Zellen angezeigt werden. Nun wurden alle eindeutigen Werte aufgelistet, die auf den beiden spezifischen Bedingungen basieren (siehe Abbildung):

Hinweis: In der obigen Formel: C2: C15 Ist der Spaltenbereich die eindeutigen Werte, aus denen Sie extrahieren möchten? A2: A15 und E2 sind der erste Bereich mit den Kriterien, anhand derer Sie eindeutige Werte extrahieren möchten. B2: B15 und F2 sind der zweite Bereich mit den Kriterien, nach denen Sie eindeutige Werte basierend auf und extrahieren möchten G1 ist die Zelle über Ihrer eingegebenen Formel.

Extrahieren Sie eindeutige Werte aus einer Liste von Zellen mit einer nützlichen Funktion

Manchmal möchten Sie nur die eindeutigen Werte aus einer Liste von Zellen extrahieren. Hier empfehle ich ein nützliches Tool.Kutools for ExcelMit seinen Zellen mit eindeutigen Werten extrahieren (einschließlich des ersten Duplikats) Dienstprogramm können Sie schnell die eindeutigen Werte extrahieren.

Hinweis:Um dies anzuwenden Zellen mit eindeutigen Werten extrahieren (einschließlich des ersten Duplikats)Zunächst sollten Sie die herunterladen Kutools for Excelund wenden Sie die Funktion dann schnell und einfach an.

Nach der Installation Kutools for ExcelBitte machen Sie Folgendes:

1. Klicken Sie auf eine Zelle, in der Sie das Ergebnis ausgeben möchten. (Hinweis: Klicken Sie nicht auf eine Zelle in der ersten Zeile.)

2. Dann klick Kutoolen > Formel-Helfer > Formel-Helfer, siehe Screenshot:

3. In dem Formeln Helfer Im Dialogfeld führen Sie bitte die folgenden Vorgänge aus:

  • Auswählen Text Option von der Formel Typ Dropdown-Liste;
  • Dann wähle Zellen mit eindeutigen Werten extrahieren (einschließlich des ersten Duplikats) von dem Wähle eine Fromula Listenfeld;
  • Im rechten Eingabe von Argumenten Wählen Sie im Abschnitt eine Liste der Zellen aus, die Sie eindeutige Werte extrahieren möchten.

4. Dann klick Ok Klicken Sie auf die Schaltfläche. Das erste Ergebnis wird in der Zelle angezeigt. Wählen Sie dann die Zelle aus und ziehen Sie den Füllpunkt auf die Zellen, in denen Sie alle eindeutigen Werte auflisten möchten, bis leere Zellen angezeigt werden. Siehe Screenshot:

Kostenloser Download von Kutools für Excel jetzt!

Weitere relative Artikel:

  • Zählen Sie die Anzahl der eindeutigen und eindeutigen Werte aus einer Liste
  • Angenommen, Sie haben eine lange Liste von Werten mit einigen doppelten Elementen. Jetzt möchten Sie die Anzahl der eindeutigen Werte (die Werte, die nur einmal in der Liste angezeigt werden) oder der unterschiedlichen Werte (alle unterschiedlichen Werte in der Liste bedeutet dies eindeutig) zählen Werte + 1. doppelte Werte) in einer Spalte wie links gezeigt. In diesem Artikel werde ich darüber sprechen, wie man mit diesem Job in Excel umgeht.
  • Summe eindeutiger Werte basierend auf Kriterien in Excel
  • Zum Beispiel habe ich einen Datenbereich, der jetzt Spalten für Name und Reihenfolge enthält, um nur eindeutige Werte in der Spalte Reihenfolge basierend auf der Spalte Name zu summieren, wie im folgenden Screenshot gezeigt. Wie kann man diese Aufgabe schnell und einfach in Excel lösen?
  • Verketten Sie eindeutige Werte in Excel
  • Wenn ich eine lange Liste von Werten habe, die mit doppelten Daten gefüllt sind, möchte ich jetzt nur die eindeutigen Werte finden und sie dann zu einer einzigen Zelle verketten. Wie kann ich dieses Problem in Excel schnell und einfach beheben?

Die besten Tools für die Office-Produktivität

Kutools for Excel löst die meisten Ihrer Probleme und erhöht Ihre Produktivität um 80%

  • Wiederverwendung: Schnell einfügen komplexe Formeln, Diagramme und alles, was du vorher benutzt hast; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
  • Super Formelriegel (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
  • Zellen / Zeilen / Spalten zusammenführen ohne Daten zu verlieren; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen / Spalten... doppelte Zellen verhindern; Bereiche vergleichen...
  • Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
  • Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
  • Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
  • Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
  • Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
  • Mehr als 300 leistungsstarke Funktionen. Unterstützt Office / Excel 2007-2021 und 365. Unterstützt alle Sprachen. Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation. 30-tägige kostenlose Testversion mit allen Funktionen. 60 Tage Geld-zurück-Garantie.
kte tab 201905

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
officetab unten
Kommentare (40)
Noch keine Bewertungen. Bewerten Sie als Erster!
Jake
Jake
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, danke für dieses Tutorial, es funktioniert perfekt. Ich versuche, es so zu ändern, dass es mit einer ODER-Bedingung funktioniert, aber es scheint nicht zu funktionieren - ist das möglich? zB =INDEX($B$2:$B$17, MATCH(0, IF(OR($D$2=$A$2:$A$17,$D$2=$B$2:$B$17), COUNTIF($E$1 :$E1, $B$2:$B$17), ""), 0))
antworten
0
0
Aileen
Aileen
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Vielen Dank für dieses Tutorial! Ich versuche auch, die Formel zu ändern, wie der obige Kommentator, aber mit einer UND-Bedingung, damit sie ein anderes Bedingungskriterium erfüllt (z. B. möchte ich für dieses Beispiel nur Dinge über einem bestimmten Schwellenwert sehen). können Sie mir bitte einen Ratschlag geben? Danke!
antworten
0
0
Konfis
Konfis     Aileen
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hey, eine Möglichkeit, es zu tun: Ersetzen Sie die if-Formel durch sumproduct((condition1=rng1)+(condition2=rng2))*countif(... Es hat bei mir funktioniert. Viel Glück! Indem Sie das + durch ein * ersetzen, können Sie Machen Sie es zu einer ODER-Bedingung, aber achten Sie gut auf die Klammern!
antworten
0
0
JeteMc
JeteMc
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Danke, das ist großartig!
antworten
0
0
Leichte
Leichte
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Danke dafür Ich habe das ausprobiert und scheint zeitweise gut zu funktionieren. Das Problem, das sich immer wieder wiederholt, ist, dass manchmal nur der erste übereinstimmende Wert zurückgegeben wird und dann dupliziert wird, wenn ich nach unten ziehe, um alle übereinstimmenden Werte zurückzugeben. Wie verhindere ich das? Irgendwelche Vorschläge?
antworten
0
0
Barrett
Barrett
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das funktioniert wirklich gut, aber wenn der eingegebene Wert dupliziert wird, wird der Wert nur einmal platziert. Wenn Ihre Liste beispielsweise zwei Lucys enthält, bringt sie nur eine Lucy an den neuen Tisch. Gibt es eine Möglichkeit, dies zu beheben?
antworten
0
0
Amandas
Amandas
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wenn Sie diese Formel verwenden, wird der erste Wert wiederholt. Wie können Sie das stoppen und die Liste der Werte bereitstellen, die dem Produkt in D2 entsprechen?
antworten
0
0
Ryan
Ryan
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, um zu verhindern, dass sich der erste Wert wiederholt, wenn Sie nach unten ziehen, müssen Sie die Zelle ÜBER der Zelle ZÄHLEN, in die Sie die Formel einfügen. Wenn die Formel beispielsweise in E2 geht, müssen Sie countif ($ E $ 1: $ E1 ...) eingeben.
antworten
0
0
Andre
Andre     Ryan
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Ryan. Formeln funktionieren hervorragend, aber wenn Sie den ersten Wert nach unten ziehen, wiederholt er sich immer wieder. Ich habe sichergestellt, dass COUNTIF auf die Zelle ÜBER der Zelle mit der Formel verweist, aber beim Herunterziehen immer noch den ersten Wert wiederholt? (z. B. wenn die Matrixformel in C2 steht, dann zeigt COUNTIF auf Zelle $C$1:$C$1)
antworten
0
0
Andre
Andre
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Ryan. Formeln funktionieren hervorragend, aber wenn Sie den ersten Wert nach unten ziehen, wiederholt er sich immer wieder. Ich habe sichergestellt, dass COUNTIF auf die Zelle ÜBER der Zelle mit der Formel verweist, aber beim Herunterziehen immer noch den ersten Wert wiederholt? (z. B. wenn die Matrixformel in C2 steht, dann zeigt COUNTIF auf Zelle $C$1:$C$1)
antworten
0
0
Camilla
Camilla     Andre
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Funktioniert wahrscheinlich nicht, weil Sie die Zellen gesperrt haben - Versuchen Sie, $C$1:$C$1 durch $C$1:$C1 zu ersetzen
antworten
0
0
Michael
Michael
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das war super hilfreich, aber ich bekomme immer wieder doppelte Namen wie diese:
Damhirschkuh, Jane
Damhirschkuh, Jane
Hoover, Tom
Hoover, Tom

Wie kann ich das stoppen?
antworten
0
0
aditya dhavale
aditya dhavale     Michael
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, ich erhalte den Fehler „#N/A“ bei der „Match-Funktion“, können Sie mir bitte weiterhelfen?
antworten
Report
0
0
Sundari
Sundari
Dieser Kommentar wurde vom Moderator auf der Website minimiert
=IF(AL2="AP","AP",IF(AK2="AD","AD",IF(Z2>500000,"Yes","No"))) Ich möchte, dass "alle Bedingungen" erfüllt sind um ja zu sagen ... Excel, der Fehler in dieser Formel widerspiegelt ... pls raten
antworten
0
0
Sundari
Sundari
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Eigentlich möchte ich, dass die Zelle "JA" widerspiegelt, wenn (AL2 = "AP" und AK2 = "AD" und Z2> 500000)
antworten
0
0
aditya
aditya
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich erhalte den Fehler #N/A bei der Match-Funktion mit dieser Formel. Können Sie mir bitte helfen?
antworten
0
0
aditya dhavale
aditya dhavale
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, ich erhalte den Fehler „#N/A“ bei der „Match-Funktion“, können Sie mir bitte weiterhelfen?
antworten
Report
0
0
Mujardin
Mujardin
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wenn Sie den Fehler #NV erhalten, gehen Sie zu Ihrer Formel und verwenden Sie Strg + Umschalt + Eingabe anstelle von Eingabe.
antworten
0
0
gon
gon
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich erhalte 0 anstelle der erwarteten Ergebnisse, die Formel eignet sich hervorragend für Daten in demselben Blatt. Haben Sie eine Lösung für Daten in einem anderen Blatt?

das ist meine formel

=IFERROR(INDEX('Switching Data'!$B$7:$B$204,MATCH(0,IF($A$2='Switching Data'!$A$7:$A$204,COUNTIF($A$4:A4,'Switching Data'!$B$7:$B$204),""),0)),0)
antworten
0
0
skyyang
skyyang     gon
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Gon,
Nach dem Einfügen der Formel sollten Sie die Tasten Strg + Umschalt + Eingabe zusammen drücken, nicht nur die Eingabetaste.
Bitte versuchen Sie es, danke!
antworten
Report
0
0
ANIBAL LUCICHE
ANIBAL LUCICHE     gon
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Gon, ich hoffe es geht dir gut. Ich frage mich, ob Sie dieses Problem lösen können. Ich erhalte denselben Fehler, wenn die Formel aus einem anderen Blatt stammt. Ich freue mich, die Lösung zu teilen, wenn Sie sie haben.
antworten
Report
0
0
Me
Me
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Danke!
antworten
0
0
Joe Jerz
Joe Jerz
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wie würde ich diese Formel dazu bringen, jedes der Duplikate anstelle von jeweils einem der Namen zurückzugeben? Wie würde ich zum Beispiel im obigen Beispiel die Ergebnisspalte (B:B) dazu bringen, Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom zurückzugeben? Ich verwende dies als Budget-Tool, um bestimmte Kontozusammenfassungen aus einem Hauptbuch abzurufen. Einige der Beträge und Transaktionsbeschreibungen sind jedoch Duplikate im Hauptbuch. Sobald der erste der duplizierten Werte abgerufen wurde, werden keine weiteren abgerufen.
antworten
Report
0
0
skyyang
skyyang     Joe Jerz
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Joe,
Um alle entsprechenden Werte basierend auf einem bestimmten Zellkriterium zu extrahieren, kann Ihnen die folgende Array-Formel helfen, siehe Screenshot:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Drücken Sie nach dem Einfügen der Formel die Tasten Umschalt + Strg + Eingabe zusammen, um das richtige Ergebnis zu erhalten, und ziehen Sie dann den Füllpunkt nach unten, um alle Werte zu erhalten.
Hoffe das kann dir helfen, danke！
Anhänge anzeigen (1 / 5)
antworten
Report
0
0
Joe Jerz
Joe Jerz     skyyang
Dieser Kommentar wurde vom Moderator auf der Website minimiert
So weit, ist es gut. Ich kann die Ergebnisse im Testblatt duplizieren, Änderungen am Array vornehmen und dann die Formel korrigieren, um die von mir vorgenommenen Änderungen zu berücksichtigen. Ich habe vor, dies heute in das Mastersheet zu verschieben und zu sehen, wie es funktioniert. Danke für die Hilfe!
antworten
Report
0
0
Joe Jerz
Joe Jerz     skyyang
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ok, also funktioniert es in der Master-Arbeitsmappe. Es gibt eine Ausnahme, deren Ursache ich nicht ermitteln konnte: Wenn das Array (in meinem Fall das Hauptbuch, das ich in Zeile 3 hatte) nicht in Zeile 1 beginnt, sind die zurückgegebenen Werte falsch. Was verursacht dieses Problem und welcher Begriff in der Formel behebt es? Nochmals vielen Dank für Ihre Hilfe dabei!
antworten
Report
0
0
Joe Jerz
Joe Jerz     skyyang
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Letzte Frage: Wenn ich möchte, dass die Ergebnisspalte alle Werte zurückgibt, die nicht mit KTE oder KTO verknüpft sind (also wäre D:D Tom, Nocol, Lily, Angelina, Genna), wie würde ich das machen?
antworten
Report
0
0
ewiki
ewiki
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Bei mir funktioniert die Formel nicht. Ich drücke ctrl shift enter und erhalte immer noch einen Fehler N/A. Ich möchte hinzufügen, dass ich genau die gleichen Daten wie im Tutorial vorbereitet habe. Was ist der Grund, warum es nicht funktioniert?
antworten
0
0
Ed
Ed
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das hat bei mir mit einem bestimmten Lookup-Wert hervorragend funktioniert. Wenn ich jedoch einen Platzhalter verwenden möchte, um Teilwerte nachzuschlagen, wie würde ich das tun? Wenn ich zum Beispiel alle Namen nachschlagen möchte, die mit KT verbunden sind?

Ich verwende diese Funktion, um Zellen nachzuschlagen, die mehrere Texte enthalten. Zum Beispiel, wenn jedes Produkt auch ein Unterprodukt in derselben Zelle hätte, ich aber nur nach Namen suchte, die mit dem Unterprodukt "Elf" verbunden sind.

KTE - elf
KTE-Ball
KTE - Klavier
KTO - elf
KTO-Kugel
KTO - Klavier
antworten
0
0
Konstantin
Konstantin
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Gibt es eine Möglichkeit, dies zum Laufen zu bringen und gleichzeitig doppelte Werte zuzulassen? Zum Beispiel möchte ich, dass alle Instanzen von Lucy in den Ergebnissen aufgelistet werden.
antworten
0
0
skyyang
skyyang     Konstantin
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Konstantin,
Um alle entsprechenden Werte einschließlich der Duplikate basierend auf einem bestimmten Zellkriterium zu extrahieren, kann Ihnen die folgende Array-Formel helfen, siehe Screenshot:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Drücken Sie nach dem Einfügen der Formel die Tasten Umschalt + Strg + Eingabe zusammen, um das richtige Ergebnis zu erhalten, und ziehen Sie dann den Füllpunkt nach unten, um alle Werte zu erhalten.
Hoffe das kann dir helfen, danke！
Anhänge anzeigen (1 / 5)
antworten
Report
0
0
Es sind noch keine Kommentare vorhanden
Mehr laden
Hinterlassen Sie Ihre Kommentare
GUEST
Als Gast posten
Einloggen
×
Bewerte diese Nachricht:
Zurücksetzen
0   Figuren
Vorgeschlagene Standorte

Folgen Sie uns

Copyright © 2009 - www.extendoffice.com. | Alle Rechte vorbehalten. Unterstützt von ExtendOffice. | Sitemap
Microsoft und das Office-Logo sind Marken oder eingetragene Marken der Microsoft Corporation in den USA und / oder anderen Ländern.
Geschützt durch Sectigo SSL