XVERWEIS – Doppelte Werte

In diesem Tutorial zeigen wir Ihnen, wie Sie XVERWEIS mit doppelten Werten in Excel durchführen können. Wenn Ihre Excel-Version XVERWEIS nicht unterstützt, lesen Sie, wie Sie stattdessen die SVERWEIS-Funktion für doppelte Werte verwenden können.

Doppelte Werte suchen

In Excel 365 ist die Suche nach doppelten Werten dank der FILTER-Funktion einfacher. Wir werden zunächst die FILTER-Funktion demonstrieren und dann zeigen wir, wie man mit XVERWEIS nach doppelten Werten suchen kann.

Mit der FILTER-Funktion geben Sie einfach das Array (z. B. Punktzahlen), das wir zurückgeben möchten, und den Kriterienbereich (z. B. Studenten-ID) mit den Kriterien (z. B. 2021-A) ein.

=FILTER(C3:C7;B3:B7=E2)

Wir können die FILTER-Funktion auch innerhalb der INDEX-Funktion verschachteln und eine Zeilennummer (z. B. F3) eingeben, um die gewollte n-te Übereinstimmung zurückzugeben, anstatt alle Übereinstimmungen zurückzugeben.

=INDEX(FILTER(C3:C7;B3:B7=E3);F3)

Die obigen Szenarien zeigen, dass die FILTER-Funktion die bequemere Lösung für diese Arten von Problemen in Excel 365 ist. Suchszenarien, die eine Liste von Nachschlagewerten erfordern, lassen sich jedoch oft besser mit XVERWEIS-Formeln lösen, da wir sie in dynamische Array-Formeln umwandeln können, was sie flexibler als die FILTER-Funktion macht.

Schauen wir uns zunächst die nicht-dynamische Array-Lösung an, um zu erklären, wie sie funktioniert, bevor wir den dynamischen Array-XVERWEIS verwenden.

XVERWEIS – Doppelte Suchwerte

Nehmen wir an, Sie möchten mit der XVERWEIS-Funktion eine Liste mit doppelten Werten nachschlagen. Unten rechts sehen Sie unsere Suchwerte. Auf der linken Seite sehen Sie die Nachschlagetabelle. Wir möchten jeden doppelten Wert nachschlagen und in separaten Zeilen ausgeben.

Wir können die folgende nicht-dynamische Array-Formellösung verwenden:

=XVERWEIS(F3&"-"&ZÄHLENWENN($F$3:F3;F3);$D$3:$D$7;$C$3:$C$7)

Gehen wir die Formel einmal durch:

Eindeutige ID – ZÄHLENWENN

Zunächst verknüpfen wir die ursprüngliche ID (z. B. die Studenten-ID) mit der ZÄHLENWENN-Funktion, um eine Liste eindeutiger IDs zu erstellen, die den ersten Eintrag eines Elements von seinen Duplikaten unterscheidet.

=B3&"-"&ZÄHLENWENN($B$3:B3;B3)

Das Sperren eines Teils der Bereichsreferenz innerhalb der ZÄHLENWENN-Funktion ermöglicht es uns, die Auftrittshäufigkeit eines Wertes zu bestimmen, wenn sich der Bereich erweitert.

=ZÄHLENWENN($B$3:B3;B3)

Jetzt verwenden wir den &-Operator, um die ZÄHLENWENN-Funktion an die ursprüngliche ID anzuhängen. Außerdem fügen wir ein Trennzeichen ein (z. B. „-„).

=B3&"-"&D3

Kombiniert wir alles zusammen, erhalten wir die Formel für die eindeutige ID:

=B3&"-"&ZÄHLENWENN($B$3:B3;B3)

Doppelte Suchwerte – N-te Übereinstimmung

Genau wie bei der ursprünglichen ID müssen wir auch hier eine Liste eindeutiger IDs für die Suchwerte erstellen. Dazu wenden wir die gleiche Methode an:

=G3&"-"&ZÄHLENWENN($G$3:G3;G3)

Die XVERWEIS-Funktion

Nun geben wir das neue Nachschlage-Array und die Liste der Suchwerte in die XVERWEIS-Funktion ein:

=XVERWEIS(H3;$E$3:$E$7;$C$3:$C$7)

XVERWEIS – Doppelte Suchwerte (dynamisches Array)

Anstatt neue Spalten hinzuzufügen und Formeln zu kopieren oder zu verschieben, können wir die bisherigen XVERWEIS-Formeln in eine dynamische Array-Formel umwandeln, die dieselbe Ausgabe erzeugen kann.

Es sieht folgendermaßen aus:

=XVERWEIS(E3:E7&"-"&ZÄHLENWENN(BEREICH.VERSCHIEBEN(E3;0;0;SEQUENZ(ZEILEN(E3:E7)));E3:E7);
B3:B7&"-"&ZÄHLENWENN(BEREICH.VERSCHIEBEN(B3;0;0;SEQUENZ(ZEILEN(B3:B7)));B3:B7);
C3:C7)

Gehen wir die obige Formel durch:

Genau wie bei der vorherigen Methode müssen wir zunächst eine Liste eindeutiger IDs mit der ZÄHLENWENN-Funktion erstellen. Die Herausforderung besteht darin, eine Array-Formel zu erstellen, die ein Array von Bereichsreferenzen erzeugt (z. B. B3, B3:B4, B3:B5 usw.).

Die BEREICH.VERSCHIEBEN-SEQUENZ-ZEILEN-Formel

Wir können die Kombination der Funktionen BEREICH.VERSCHIEBEN, SEQUENZ und ZEILEN verwenden, um ein Array von Bereichsreferenzen zurückzugeben.

Beginnen wir mit der Liste des Nachschlage-Arrays (z.B. B3:B7):

=BEREICH.VERSCHIEBEN(B3;0;0;SEQUENZ(ZEILEN(B3:B7))

Hinweis: Die obige Formel funktioniert nicht von selbst. Excel kann kein Array von Bereichen oder Arrays zurückgeben, aber es kann sie auswerten, wie wir später sehen werden.

Die ZEILEN-Funktion

Zunächst müssen wir die Gesamtzahl der Zeilen mit der Funktion ZEILEN ermitteln.

=ZEILEN(B3:B7)

Die SEQUENZ-Funktion

Anschließend geben wir das Ergebnis der ZEILEN-Funktion in die SEQUENZ-Funktion ein, um eine Liste von Zählnummern zu erzeugen, die auch die Anzahl der Zellen pro Bereich in der Reihe der Bereiche darstellt.

=SEQUENZ(C2)

Die Array-BEREICH.VERCHIEBEN-Formel

Anschließend geben wir die Ergebnisse der SEQUENZ-Funktion in die Höhe (4. Argument) der BEREICH.VERSCHIEBEN-Funktion ein, um die Bereiche zu bilden.

=BEREICH.VERSCHIEBEN(B3;0;0;C3:C7)

Wie bereits erwähnt, wird dies nicht funktionieren, aber so soll das Array der Bereiche aussehen:

Wir beginnen mit B3 und erweitern es für jede Höhe (z. B. C3:C7).

Hinweis: BEREICH.VERSCHIEBEN ist eine volatile Funktion, d. h., sie wird bei jeder Änderung in der Kalkulationstabelle neu berechnet, auch wenn diese nicht mit den Eingaben von BEREICH.VERSCHIEBEN selbst zusammenhängt.

Die Array-ZÄHLENWENN-Funktion

Da wir nun eine Reihe von Bereichen haben, geben wir diese in die ZÄHLENWENN-Funktion ein und erhalten eine Reihe von n-ten Vorkommen zurück.

=ZÄHLENWENN(BEREICH.VERSCHIEBEN(B3;0;0;C3:C7);B3:B7)

Array von Eindeutigen IDs

Schließlich wird die Array-Ausgabe der ZÄHLENWENN-Funktion mit dem Nachschlage-Array (z. B. B3:B7) verkettet, um das Array der eindeutigen IDs zu erzeugen.

=B3:B7&"-"&F3:F7

Die Kombination aller Funktionen führt zu unserer Array-Formel der eindeutigen IDs:

=B3:B7&"-"&ZÄHLENWENN(BEREICH.VERSCHIEBEN(B3;0;0;SEQUENZ(ZEILEN(B3:B7)));B3:B7)

Das Gleiche gilt auch für die Suchwerte:

=I3:I7&"-"&ZÄHLENWENN(BEREICH.VERSCHIEBEN(I3:I7;0;0;SEQUENZ(ZEILEN(I3:I7));I3:I7)

Wenn wir alles miteinander kombinieren, erhalten wir unsere ursprüngliche Array-Formel:

=XVERWEIS(E3:E7&"-"&ZÄHLENWENN(BEREICH.VERSCHIEBEN(E3;0;0;SEQUENZ(ZEILEN(E3:E7)));E3:E7);
B3:B7&"-"&ZÄHLENWENN(BEREICH.VERSCHIEBEN(B3;0;0;SEQUENZ(ZEILEN(B3:B7)));B3:B7);
C3:C7)