☰

Führen Sie in Excel eine SVERWEIS-Funktion für mehrere Datenzeilen mit demselben Nachschlagewert (Primärschlüssel) durch.

tag-icon • tag-icon microsoft-excel
Führen Sie in Excel eine SVERWEIS-Funktion für mehrere Datenzeilen mit demselben Nachschlagewert (Primärschlüssel) durch.

Ich möchte eine vertikale Suche für Namen durchführen, die mehrere Zeilen für denselben Primärschlüssel haben:

Deal ID (Primary Key) | Name
437                   | Tom Jones
437                   | Frank Thomas
437                   | Mary Smith

Die Vlookup-Funktion gibt nur den Vornamen im Datensatz zurück (Tom Jones). Ich möchte, dass es so aussieht: Tom Jones, Frank Thomas, Mary Smith, alle in derselben Zeile.

Antwort1

Es sieht so aus, als würden Sie versuchen, Excel als relationale Datenbank zu verwenden, was es nicht besonders gut kann. Und um das Ganze noch zu toppen: Sofern es in den letzten Jahren keine Änderungen gab (ich hatte nicht das Vergnügen, etwas später als Excel 2010 zu verwenden), gibt es keine Arbeitsblattfunktion, um einen Bereich oder ein Array in eine abgegrenzte Zeichenfolge umzuwandeln. Sie müssen sich eine VBA-Arbeitsblattfunktion ausdenken, die das kann.

Sie können eine Kombination aus Array-Formeln und VBA verwenden, um das zu tun, was Sie möchten. Bevor Sie fortfahren, würde ich Ihnen dringend empfehlen, ein echtes Datenbankprogramm zu verwenden, wenn Sie solche Dinge regelmäßig tun. Ich habe Excel in der Vergangenheit missbraucht, um einfache relationale Datenbank-ähnliche Aufgaben auszuführen, aber nur einfache. Wenn ich etwas Komplexeres hätte tun müssen, wäre das äußerst mühsam gewesen.

Angenommen, Ihre zu suchende ID befindet sich in Zelle F1und Ihre Tabelle heißt Table1, geben Sie Folgendes alsArray-Formel(verwenden Sie Strg-Umschalt-Eingabe anstelle der Eingabetaste). SimpleCatist Ihre VBA-Verkettungsarbeitsblattfunktion.

=SimpleCat(IF(Table1[Deal ID (Primary Key)]=$F$1, Table1[Name], ""))

In SimpleCatdiesem konkreten Fall funktioniert Folgendes:

Function SimpleCat(Args() As Variant) As Variant
    Dim a As Variant
    SimpleCat = ""
    For Each a In Args
        If a <> "" Then SimpleCat = SimpleCat & a & ", "
    Next
    If Len(SimpleCat) > 0 Then SimpleCat = Left$(SimpleCat, Len(SimpleCat) - 2)
End Function

Ich überlasse es Ihnen als Übung, SimpleCates zu erweitern und allgemeiner zu gestalten, da eine allgemeinere Verkettungsfunktion mit umfassenderen Funktionen in jedem Werkzeugkasten nützlich ist.

Erläuterung:

Bei der Auswertung als Array-Formel erzeugt der Vergleich einer ganzen Tabellenspalte mit einem einzelnen Wert ein Array aus TRUEs und FALSEs. Die Verwendung von IFerstellt ein neues Array, wobei Werte von der richtigen Position des von Ihnen verwendeten Arrays auf der Seite „Wert, wenn wahr“ von abgerufen werden IFund die von Ihnen verwendete leere Zeichenfolge auf der Seite „Wert, wenn falsch“ von eingefügt wird IF. Die SimpleCatFunktion fügt alle nicht leeren Werte in diesem neuen Array zusammen und trennt sie durch Kommas.

Antwort2

Zwei Probleme. Erstens handelt es sich dabei nicht um einen Primärschlüssel. Zweitens gibt es in Excel keine integrierte Funktion, die Ihnen das gewünschte Ergebnis liefert.

Ein Primärschlüssel sollte ein eindeutiger Bezeichner sein, der an einen einzelnen Datensatz gebunden ist und sich niemals wiederholt. Die meisten Nachschlagefunktionen in Excel sind dafür ausgelegt, weshalb sie anhalten, wenn sie eine Übereinstimmung finden – es spart Ressourcen, wenn man nicht jedes Mal den gesamten Bereich durchsuchen muss, wenn bereits eine Übereinstimmung gefunden wurde.

Die einzige Möglichkeit, die von Ihnen gesuchte Konsolidierung durchzuführen, bei der mehrere Werte in eine einzige Zeichenfolge gezogen werden, wäre meiner Meinung nach die Verwendung eines Makros oder einer benutzerdefinierten Funktion. Welchen Code Sie genau benötigen, hängt vom gewünschten Ergebnis ab und liegt leider außerhalb des Rahmens dieser Antwort.

Antwort3

Ich würde diese Funktion nehmen....

Function ConcatRange(inputRange As Range, Optional delimiter As String) As String
    Dim oneCell As Range
    Dim usedRange As Range

    Set usedRange = Application.Intersect(inputRange.Parent.usedRange, inputRange.Cells)
    If Not (usedRange Is Nothing) Then
        For Each oneCell In usedRange
            If oneCell.Text <> vbNullString Then
                ConcatRange = ConcatRange & delimiter & Trim(oneCell.Text)
            End If
        Next oneCell
        ConcatRange = Mid(ConcatRange, Len(delimiter) + 1)
    End If
End Function

und ändern Sie es, um eine neue Funktion zu erstellen, die etwa so aussieht:

Function ConcatIf(KeyRange as Range, KeyValue as Variant, DataColumnOffset As Integer)

Dabei wird jede Zelle innerhalb von KeyRange durchlaufen und nur dann, wenn der Wert gleich KeyValue ist, der Wert vononeCell.Offset(0, DataColumnOffset)

verwandte Informationen