Wo ist der Schlüssel für den Konferenzraum? Wer hat ihn zuletzt genommen? Meist wird die Schlüsselausgabe noch auf Listen in Papierform erfasst. Das lässt sich digitalisieren. Wie das mit Excel und der Funktion XVERWEIS geht, zeige ich in diesem Beitrag.
Digitale Schlüsselausgabe mit Suchfunktion in Excel mit der Funktion XVERWEIS aufbauen
Eine digitale Schlüsselausgabe aufbauen
- In der unten gezeigten Tabelle mit dem Namen tbl_Ausgabe wird ab Zeile 11 jede Schlüsselausgabe protokolliert [1]. Hier im Beispiel werden pro Zeile jeweils fünf Informationen erfasst. In der Praxis lassen sich weitere Angaben wie die Uhrzeiten der Ausgabe und Rückgabe ergänzen.
- Wer nach einem Schlüssel sucht, wählt in Zelle C2 und C3 per Dropdown das Gebäude und die jeweilige Schlüsselnummer [2].
- Im Ergebnis dessen soll automatisch in den Zellen C4 bis C6 angezeigt werden, wann und an wen der Schlüssel zuletzt ausgegeben und ob er zurückgebracht wurde [3].
Soweit der Plan für den Aufbau der digitalen Schlüsselausgabe.
Unten die Ausgabeliste [1], oben der Suchbereich mit zwei Dropdowns [2] und die drei noch leeren Ergebnisfeldern [3]
Perfekt geeignet: Die Funktion XVERWEIS
Als Nachfolger vom SVERWEIS bietet die Funktion XVERWEIS mehr Möglichkeiten beim Suchen und Abgleichen von Informationen. Sechs Argumente stehen dafür zur Verfügung. Die folgende Abbildung zeigt Nutzen und Syntax von XVERWEIS und beschreibt die einzelnen Argumente.
Kompakte Info zur Funktion XVERWEIS, zu den verfügbaren Argumenten und deren Abfolge
Die Herausforderung: Zwei Suchkriterien und demzufolge zwei Suchvektoren
In beiden Gebäuden gibt es Schlüssel mit der gleichen Nummer, beispielsweise die 101, 102, 103, 104. Wie lässt sich bei der Suche in der Schlüsselvergabeliste trotzdem der richtige Schlüssel finden?
Das löse ich in Zelle C4 wie folgt:
- Ich füge die beiden Angaben zu Gebäude und zu Schlüsselnummer zu einem Suchkriterium zusammen, indem ich sie mit & verkette.
- Da die Angaben zu Gebäude und Schlüsselnummer in zwei unterschiedlichen Spalten stehen, muss ich auch für das Argument Suchmatrix zwei Teile verketten – hier die Spalten Gebäude und SchlüsselNr.
- Demzufolge beginne ich die XVERWEIS-Formel so:
Für die Argumente Suchkriterium und Suchmatrix werden jeweils zwei Angaben mit & kombiniert
Weitere Herausforderung: Den passsenden Suchmodus einstellen und Fehler abfangen
- Nachdem ich die Argumente für Suchkriterium und Suchmatrix eingeben habe, wähle ich den Bereich für das Argument Rückgabematrix.
Dazu markiere ich die Spalte Ausgabedatum und erhalte tbl_Ausgabe[Ausgabedatum]. - Für das Argument wenn_nicht_gefunden gebe ich den Text „nicht ausgeliehen“ ein.
- Beim Argument Vergleichsmodus wähle ich 0. Sie steht für Genaue Übereinstimmung.
- Das letzte Argument Suchmodus ist besonders hilfreich. Es erlaubt mir, in der chronologisch nach Ausgabedatum geführten Liste von unten an zu suchen. Dies gebe ich mit -1 an und erhalte damit immer die letzte Ausgabezeile eines Schlüssels.
- Nach Eingabe aller Argumente ergibt sich die folgende Formel:
Die fertige Formel für Zelle C4 mit dem Hinweis im Falle von Fehlern und dem Suchmodus -1
Für die Zellen C5 und C6 gehe ich ähnlich wie für Zelle C4 vor. Nur den Bereich für das Argument Rückgabematrix passe ich an.
In der folgenden Abbildung lässt sich der Aufbau der drei Formeln in C4, C5 und C6 leicht nachvollziehen und vergleichen, da die Bestandteile der XVERWEIS-Formeln Zeile für Zeile nachzulesen sind.
Anhand der gelben Markierung ist zu erkennen, dass sich die drei Formeln lediglich bei der Rückgabematrix unterscheiden.
- Zeile 1: Name der Funktion und die öffnende Klammer
- Zeile 2: Das Argument Suchkriterium, hier aus zwei Elementen zusammengesetzt
- Zeile 3: Das Argument Suchmatrix, das ebenfalls aus zwei Teilen besteht
- Zeile 4: Das Argument Rückgabematrix
- Zeile 5: Das Argument wenn_nicht_gefunden
- Zeile 6: Das Argument Vergleichsmodus
- Zeile 7: Das Argument Suchmodus
- Zeile 8: Die schließende Klammer für die XVERWEIS-Formel
Die drei XVERWEIS-Funktionen für C4 bis C6 auf einen Blick – nur die Angabe für das Argument Rückgabematrix in Zeile 4 unterscheidet sich
FAZIT
Die XVERWEIS-Funktion erweist sich als ideal, um Daten aus einer Liste zu ermitteln, die mehreren Kriterien entsprechen müssen.
- Ohne Hilfsspalte lassen sich für Suchkriterium und Suchmatrix Angaben verketten, die eine eindeutige Suche möglich machen.
- Bei mehrmaligem Vorkommen eines Eintrags wird mit -1 für den Suchmodus das Problem gelöst, den chronologisch letzten Eintrag zu bekommen.
- Bei fehlendem Vorkommen eines Eintrags kann mit Hilfe des Arguments wenn_nicht_gefunden ein passender Hinweis angezeigt werden. Eine zusätzliche Fehlerbehandlung ist somit nicht mehr nötig.
Kurs zu Verweisfunktionen bei den Office-Kompetenztagen
Wer mehr über die Verweisfunktionen lernen will, um Listen abzugleichen und zu verknüpfen, hat dazu bei den Office-Kompetenztagen Gelegenheit, und zwar in meinem Kurs am Vormittag des 27. September.
Kursplan der Office-Kompetenztage
