Suche

Vereinfachter (S-)Verweis - Anwendung der Formel XVERWEIS

Roman Formeln , , , ,

Die Formel "SVERWEIS" ist eine der bekanntesten in Excel - da sie mächtige Überprüfungen durchführen und Ergebnisse liefern kann, aber eben auch manch einem Benutzer Probleme bereitet. Diverse Angaben und Regeln müssen erfüllt werden, damit man zum gewünschten Resultat gelangt. Ein Nachteil der Formel ist zudem, dass die Ergebnisspalte rechts von dem Suchwert stehen muss (siehe hierzu eine ebenfalls valide Lösung SVERWEIS nach links).

In diesem Beitrag wird eine neuere Formel "XVERWEIS" vorgestellt. Diese Funktion ist deutlich intuitiver, wie die ersten drei Argumente der Syntax zeigen (nur diese drei sind notwendig, die Angaben in den eckigen Klammern können ignoriert werden):

XVERWEISSyntax

Die erste Angabe verlangt analog dem SVERWEIS ein Suchkriterium - nach welchem Wert soll gesucht werden? Die Suchmatrix ist z.B. die Spalte, in welcher das Suchkriterium steht. Beim SVERWEIS wird es bereits an dieser Stelle kompliziert, da die Matrix sowohl das Suchkriterium wie auch das Resultat enthalten muss. In dieser Formel ist das dritte Argument die Rückgabematrix (z.B. die Spalte, in der das Resultat steht). Wie erwähnt hat die Formel drei weitere Argumente in eckigen Klammern - diese sind fakultativ und bieten weitere Möglichkeiten, auf die wir hier nicht eingehen.

Schauen wir uns ein konkretes und simples Beispiel an. Eine Tabelle enthält die Regionalleiter und deren E-Mail-Adressen. In Spalte F suchen wir nach der Region, um die E-Mail zu erhalten:

XVERWEISSpalten1

Wechseln wir das Suchkriterium von "Nordeuropa" zu "Nordamerika", erhalten wir die entsprechende E-Mail-Adresse.

XVERWEISSpalten2

Dies ist die effektive Formel in der Zelle "G3":

=XVERWEIS(F3;B3:B8;D3:D8)

Selbstverständlich könnte man auch hier Werte "absolut" setzen (Dollarzeichen), damit man die Formel vereinfacht kopieren könnte - hier wird allerdings nur ein einziger Wert gesucht.

Beachte zudem: Mit der Formel ist nicht nur der "SVERWEIS" (Spalten) möglich, sondern auch der "waagrechte" Verweis (WVERWEIS). Die oben aufgeführte Tabelle habe ich transponiert, um den XVERWEIS auf ein solches Format anzuwenden. Unten wird die "gedrehte" (transponierte) Tabelle sowie die angepasste XVERWEIS-Formel dargestellt. Um die Abbildung übersichtlicher zu gestalten, habe ich gewisse Spalten (D bis G) gruppiert und ausgeblendet.

XVERWEISZeilen1

Die Formel in Zelle "C7" lautet:

=XVERWEIS(B7;C2:H2;C4:H4)

Live-Suche in der Excel-Suchmaschine

Roman VBA (Makros) , , , , , , , , , , ,

Wie Du in Excel eine simple Suchmaschine programmieren und einrichten kannst, wurde in einem älteren Beitrag (siehe Suchmaschine programmieren) illustriert. Hier wird nun ergänzt, wie Du eine Live-Suche etablierst, damit direkt nach Eingabe eines Suchkriteriums die Liste auf die entsprechenden Einträge gefiltert wird.

Nehmen wir nach wie vor an, dass in der Zelle "B1" das Suchkriterium steht. Falls sich also diese Zelle ändert, hat die Suchmaschine direkt zu reagieren. Das Makro aus dem Beitrag "Suchmaschine programmieren" ist hierzu nicht in einem Modul abzulegen, sondern direkt im entsprechenden Tabellenblatt (=Tabelle1 (Suchmaschine))!

vbaprojekttabelle

Zudem ist der Code leicht anzupassen, respektive  um nachfolgende zusätzliche Angaben zu ergänzen. Der vollständige Code wird dann weiter unten aufgeführt.

Anstelle von "Sub Suchmaschine()" zu Beginn hat die erste Codezeile zu lauten:

Private Sub worksheet_change(ByVal target As Range)

Zudem ist eine Wenn-Dann-Kondition einzubauen, die auf die Zelle "B1" verweist - und eben beim Anpassen dieser Zelle das Makro automatisch auslöst.

If target.Row = 1 And target.Column = 2 Then

Darauf folgt der gesamte Code der Suchmaschine, abgeschlossen (vor "End Sub") mit dem Befehl:

End If

Der vollständige Code ...

... (nicht in Modul1 sondern im Tabellenblatt direkt, wie oben erwähnt) lautet demnach:

Private Sub worksheet_change(ByVal target As Range)

If target.Row = 1 And target.Column = 2 Then

Range("D2").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$D$8").AutoFilter Field:=4, Criteria1:="<>0"

AnzahlTreffer = Application.WorksheetFunction.Subtotal(3, Range("A:A")) - 2

MsgBox ("Insgesamt " & AnzahlTreffer & " Treffer gefunden.")

End If

End Sub

Die Eingabe des Platzhalters/Sternchens (*) erübrigen

Beim Programmieren der Suchmaschine habe ich Dir aufgezeigt, dass das Suchkriterium jeweils mit einem Sternchen vor- und nachher versehen werden sollte - beispielsweise "*Pop*". Um diesem vorzubeugen (es ist also nur noch der Begriff einzugeben, ohne Platzhalter-Symbol), ist die WVERWEIS-Formel minim anzupassen. Wir kombinieren jeweils den Text "*" mit dem Zellbezug auf das Suchkriterium in "B1". Für die Zeile 3 lautet die Formel wie folgt (und kann auf sämtliche weiteren Zeilen herunterkopiert werden):

=WENNFEHLER(WVERWEIS("*"&$B$1&"*";A3:C3;1;FALSCH);0)