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

Wie kann ich mehrere entsprechende Werte in Excel anzeigen und verketten?

Wie wir alle wissen, ist die Vlookup Die Funktion in Excel kann uns helfen, einen Wert zu suchen und die entsprechenden Daten in einer anderen Spalte zurückzugeben. Im Allgemeinen kann sie jedoch nur dann den ersten relativen Wert erhalten, wenn mehrere übereinstimmende Daten vorhanden sind. In diesem Artikel werde ich darüber sprechen, wie mehrere entsprechende Werte in nur einer Zelle oder einer vertikalen Liste angezeigt und verkettet werden.

Suchen Sie und stimmen Sie mehrere übereinstimmende Werte vertikal mit der Formel ab

Suchen und verketten Sie mehrere übereinstimmende Werte in einer Zelle mit der benutzerdefinierten Funktion

Mit Kutools for Excel mehrere übereinstimmende Werte in einer Zelle anzeigen und verketten


Angenommen, ich habe den folgenden Datenbereich, um alle entsprechenden Werte basierend auf einem bestimmten Wert vertikal zu erhalten, wie im folgenden Screenshot gezeigt, können Sie eine Array-Formel anwenden.

doc vlookup verketten 1

1. Geben Sie diese Formel ein: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") in eine leere Zelle, in die Sie das Ergebnis einfügen möchten, z. B. E2, und drücken Sie dann Strg + Umschalt + Enter Tasten zusammen, um die relative Wertebasis für ein bestimmtes Kriterium zu erhalten, siehe Screenshot:

doc vlookup verketten 2

Hinweis: In der obigen Formel:

A1: A16 ist der Spaltenbereich, der den spezifischen Wert enthält, nach dem Sie suchen möchten;

D2 Gibt den spezifischen Wert an, den Sie anzeigen möchten.

B1: B16 ist der Spaltenbereich, aus dem Sie die entsprechenden Daten zurückgeben möchten;

$ 1: $ 16 Gibt die Zeilenreferenz innerhalb des Bereichs an.

2. Wählen Sie dann Zelle E2 aus und ziehen Sie den Füllpunkt nach unten zu den Zellen, bis Sie leere Zellen erhalten. Alle übereinstimmenden Werte werden in der Spalte wie folgt aufgelistet:

doc vlookup verketten 3


Anstatt die relativen Werte vertikal abzurufen, möchten Sie manchmal die übereinstimmenden Werte in einer Zelle zusammenfassen und sie mit einem bestimmten Trennzeichen verknüpfen. In diesem Fall kann Ihnen die folgende benutzerdefinierte Funktion einen Gefallen tun.

1. Halten Sie die ALT + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.

2. Klicken Sie Insert > Modulund fügen Sie den folgenden Code in das Feld ein Modul Fenster.

VBA-Code: Mehrere übereinstimmende Werte in einer Zelle anzeigen und verketten

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Speichern und schließen Sie diesen Code, kehren Sie zum Arbeitsblatt zurück und geben Sie die folgende Formel ein: = cusvlookup (D2, A1: B16,2) in eine leere Zelle, in die Sie das Ergebnis einfügen möchten, und drücken Sie Weiter Schlüssel, alle entsprechenden Werte basierend auf bestimmten Daten wurden in eine Zelle mit Leerzeichen zurückgegeben, siehe Screenshot:

doc vlookup verketten 4

Hinweis: In der obigen Formel: D2 gibt die Zellenwerte an, nach denen Sie suchen möchten. A1: B16 ist der Datenbereich, in dem Sie die Daten abrufen möchten, die Nummer 2 Ist die Spaltennummer, von der der übereinstimmende Wert zurückgegeben werden soll, können Sie diese Verweise nach Bedarf ändern.


Wenn Sie Kutools for ExcelMit seinen Erweiterte Zeilen kombinieren Mit dieser Funktion können Sie diesen Auftrag schnell und einfach abschließen. Mit dieser Funktion können Sie alle übereinstimmenden Werte mit einem bestimmten Trennzeichen kombinieren, das auf denselben Daten in einer anderen Spalte basiert.

Kutools for Excel : Mit mehr als 300 praktischen Excel-Add-Ins können Sie diese innerhalb von 30 Tagen ohne Einschränkung testen.

Nach der Installation Kutools for ExcelBitte gehen Sie wie folgt vor:

1. Wählen Sie den Datenbereich aus, für den Sie die entsprechenden Werte basierend auf den spezifischen Daten erhalten möchten.

2. Dann klick Kutoolen > Zusammenführen & Teilen > Erweiterte Zeilen kombinieren, siehe Screenshot:

3. In dem Erweiterte Zeilen kombinieren Klicken Sie im Dialogfeld auf den Spaltennamen, auf dessen Grundlage Sie kombinieren möchten, und klicken Sie dann auf Primärschlüssel Schaltfläche, siehe Screenshot:

doc vlookup verketten 6

4. Klicken Sie dann auf einen anderen Spaltennamen, für den Sie die übereinstimmenden Werte zurückgeben möchten, und klicken Sie auf Kombinieren Um ein Trennzeichen zum Trennen der kombinierten Werte auszuwählen, siehe Screenshot:

doc vlookup verketten 7

5. Und dann klick Ok Schaltfläche, alle entsprechenden Werte, die auf denselben Werten basieren, wurden mit einem bestimmten Trennzeichen kombiniert, siehe Screenshots:

doc vlookup verketten 8 2 doc vlookup verketten 9

 Laden Sie Kutools for Excel jetzt herunter und testen Sie es kostenlos!


Kutools for Excel: Mit mehr als 300 praktischen Excel-Add-Ins können Sie es innerhalb von 30 Tagen ohne Einschränkung testen. Jetzt herunterladen und kostenlos testen!

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

Kutools für 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-2019 und 365. Unterstützt alle Sprachen. Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation. Vollständige Funktionen 30 Tage kostenlose Testversion. 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 täglich Hunderte von Mausklicks für Sie!
officetab unten
Kommentare (16)
Noch keine Bewertungen. Bewerten Sie als Erster!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
So erhalten Sie das Ergebnis. Bitte helfen Sie. Daten Daten1 Ergebnis a 1 a1 b 2 a2 c b1 b2 c1 c2
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wenn Sie cusvlookup verwenden, gibt es eine Möglichkeit, auch den Nachnamen mit einem Komma dazwischen hinzuzufügen, das möglicherweise in Spalte C erscheint
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Liebte die Funktion für Excel 2013, änderte sie aber leicht, um das Trennzeichen in „;“ zu ändern. anstelle von " " und entfernen Sie dann das vorangestellte ";" aus den verketteten Werten Ergebnisse, die in meinem Beispiel mit Werten übereinstimmen, hätten ;Ergebnis01 oder ;Ergebnis01;Ergebnis02 . Zusätzliches If Left(xResult, 1) = ";" hinzugefügt um jedes zusätzliche ";" zu entfernen am Anfang der Zeichenfolge, wenn es das 1. Zeichen ist. Ich bin mir sicher, dass es einen ordentlicheren Weg gibt, aber bei mir hat es funktioniert. :) Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" For Each rng In pWorkRng If rng = pValue Then xResult = xResult & ";" & rng.Offset(0, pIndex - 1) If Left(xResult, 1) = ";" Dann xResult = MID(xResult, 2,255) End If End If Next CusVlookup = xResult End Function
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Machen Sie eine if-Bedingung für das Ergebnis, wenn es leer ist.

Funktion CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x als Bereich
Dim-Ergebnis als String
Ergebnis = ""
Für jedes x im Suchbereich
Wenn x = lookupval Then
Wenn kein Ergebnis = "" Dann
result = result & " " & x.Offset(0, indexcol - 1)
sonst
result = x.Offset(0, indexcol - 1)
End If
Nächstes x
CusVlookup = Ergebnis
End Function
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das ist erstaunlich, aber ich suche nach etwas anderem, ich habe eine Tabelle mit RollNo StudentName sub1, sub2, sub3 ... Gesamtergebnis, wenn ich Rollnummer eingebe, sollte es ein Ergebnis wie "SName Sub1 64, sub2 78, ... Gesamt 389, Ergebnis bestanden“, ist das möglich
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Gibt es eine Möglichkeit, die doppelten Werte in der Verkettung zu löschen?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Jakob,
Vielleicht hilft Ihnen der folgende Artikel bei der Lösung Ihres Problems.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Bitte versuchen Sie es, ich hoffe, es kann Ihnen helfen!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Gibt es eine Möglichkeit, die doppelten Werte nur einmal aufzulisten, indem Sie den VBA-Code und die obige Formel verwenden? Ich bin mir nicht sicher, wo ich die countif>1-Anweisung in die Formelleiste oder in die vba selbst einfügen soll. Bitte helfen Sie
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Sie können zwei zusätzliche Bedingungen hinzufügen, um leere Zellen zu überspringen und Duplikate zu überspringen: For i = 1 To CriteriaRange.Count
Wenn CriteriaRange.Cells(i).Value = Condition Then
Wenn ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
End If
Weiter ich
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich muss sagen, ich versuche seit 2 Tagen, eine Formel zu finden, um mehrere Werte zu kombinieren und sie in eine einzelne Zelle zurückzugeben. Dieses "How To" hat mich gerettet!! Vielen Dank! Ohne Ihr Modul hätte ich es nie hinbekommen!
Ich habe aber 2 Fragen. Ich habe das Trennzeichen als Komma anstelle eines Leerzeichens und deshalb beginnt es mit einem Komma. Gibt es eine Möglichkeit, das Startkomma zu verhindern, aber den Rest beizubehalten?
Meine zweite Frage ist; Wenn ich das Ausfüllkästchen verwende, ändert es die Bereichswerte sowie den Zellenwert, den ich nachschlagen möchte. Ich möchte, dass es weiterhin die Zellennummer ändert, die ich nachschlagen möchte, aber die gleichen Bereichswerte beibehält. Wie kann ich das erreichen?

Vielen Dank für deine Hilfe!!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das cusVlookup hat bei mir super funktioniert. Eine andere Möglichkeit, ein anderes Trennzeichen zu haben, besteht darin, zwei Ersatzfunktionen einzuschließen. Das erste (von innen nach außen) ersetzt das erste Leerzeichen ohne Leerzeichen, das zweite ersetzt alle anderen Leerzeichen durch ein "/" in meinem. Könnte "," verwenden, wenn Sie Kommas wollen.
=WECHSELN(WECHSELN(cusVlookup(D2,Tabelle1,2)," ","",1)," "," / ")

Wenn Ihr Suchwert nicht die erste Spalte ist, können Sie auch 0 oder negative Zahlen verwenden, um zur linken Spalte zu wechseln.
=WECHSELN(WECHSELN(cusVlookup(D2,Tabelle1,-1)," ","",1)," "," / ")
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Jeff,
Danke für Ihr Teilen, Sie müssen ein warmherziger Mann sein.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das funktioniert großartig für mich - gibt es irgendwie zu ändern, dass es prüft, ob die Zelle eher als eine vollständige Übereinstimmung enthält? Grundsätzlich habe ich eine Liste von Aufgaben, wo:
Spalte A: Abhängigkeiten (zB 10003 10004 10008)
Spalte B: Aufgabenreferenz (z. B. 10001)
Spalte C: Abhängige Aufgaben (die Spalte für das Formelergebnis) – wo es die Aufgabenreferenz nachschlagen würde, um zu sehen, welche Zeilen sie in Spalte A enthalten, und dann die Aufgabenreferenz dieser Aufgaben auflisten würde.

Z.B:

Zeile | Spalte A | Spalte B | Spalte C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Sie möchten die Funktion Instr() verwenden, die nach etwas in einer Textzeichenfolge in einer Zelle sucht. Sie können auch Left() und Right() verwenden, wenn Sie nach Anfangs- oder Enddetails suchen.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Gibt es eine Möglichkeit, den eindeutigen "Namen" für "Klasse1" zu erhalten?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Sym-John,
Vielleicht kann der folgende Artikel Ihr Problem lösen, bitte lesen Sie ihn:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
Es sind noch keine Kommentare vorhanden
Hinterlassen Sie Ihre Kommentare
Als Gast posten
×
Bewerte diese Nachricht:
0   Figuren
Vorgeschlagene Standorte