XVERWEIS nach Datum – Excel
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial zeigen wir Ihnen, wie Sie die XVERWEIS-Funktion mit Datumsangaben in Excel verwenden können.
In Excel werden Datumsangaben als fortlaufende Nummern gespeichert, wobei jede Nummer ein eindeutiges Datum darstellt (die Anzahl der Tage ab dem imaginären Tag 00.01.1900).
Später in diesem Tutorial werden wir besprechen, wie man mit Datumsangaben umgeht, die als Text gespeichert sind, aber für den Moment gehen wir davon aus, dass alle Datumsangaben als fortlaufende Nummern gespeichert sind.
XVERWEIS mit der DATUM-Funktion
Eine Möglichkeit, ein Datum in eine XVERWEIS-Formel einzugeben, ist die Verwendung der DATUM-Funktion.
=XVERWEIS(DATUM(2021;1;1);B3:B7;C3:C7)
Die DATUM-Funktion erzeugt ein Datum aus einem gegebenen Jahr, Monat und Tag.
XVERWEIS mit der Funktion DATWERT
Eine andere Methode zur Erstellung eines Datums ist die Verwendung der DATWERT-Funktion.
=XVERWEIS(DATWERT("1/1/2021");B3:B7;C3:C7)
Die DATWERT-Funktion wandelt eine Textzeichenfolge, die ein Datum darstellt, in ein Datum um, mit dem Excel arbeiten kann (ein Datum als fortlaufende Nummer).
XVERWEIS mit in Zellen gespeicherten Daten
Alternativ können Sie auf eine Zelle verweisen, die ein Datum enthält, um ein XVERWEIS durchzuführen.
=XVERWEIS(E3;B3:B7;C3:C7)
XVERWEIS mit mehreren Datumsangaben
Wir können auf eine Liste von Nachschlagedaten verweisen, wodurch die Formel in eine dynamische Array-Formel umgewandelt wird.
=XVERWEIS(E3:E4;B3:B7;C3:C7)
Hinweis: Wenn eine Liste von Werten in ein Argument eingegeben wird, das normalerweise nur einen einzigen Wert annehmen kann, wird ein dynamisches Array oder eine Formel (Engl. Spill Formula) erstellt. Die Formel läuft durch, um die Berechnungen auf dem gesamten Array von Werten durchzuführen.
XVERWEIS mit ungefährer Übereinstimmung
Standardmäßig führt der XVERWEIS einen exakten Abgleich durch. Wird den Vergleichsmodus (5. Argument) auf -1 oder 1 geändert, wird stattdessen nach einer ungenauen Übereinstimmung gesucht.
XVERWEIS mit dem nächst kleinerem Element
Wir können nach dem genauen Datum oder dem letzten Datum vor dem Nachschlagedatum suchen, indem wir den Vergleichsmodus auf -1 ändern.
=XVERWEIS(E3;B3:B7;C3:C7;;-1)
Hinweis: Wenn der XVERWEIS keine exakte Übereinstimmung findet, gibt er den größten Wert zurück, der kleiner als der Suchwert ist.
XVERWEIS mit dem nächst größerem Element
Wenn wir einen ungefähren Wert zurückgeben wollen, der größer als der Suchwert ist, müssen wir Vergleichsmodus = 1 anwenden.
=XVERWEIS(E3;B3:B7;C3:C7;;1)
Hinweis: Wenn die XVERWEIS-Funktion keine exakte Übereinstimmung findet, wird der kleinste Wert ermittelt, der größer als der Suchwert ist.
XVERWEIS mit dem letzten Datum
Standardmäßig beginnt die XVERWEIS-Funktion mit der Suche von oben nach unten. Wenn Sie den Suchmodus (6. oder letztes Argument) auf -1 setzen, wird die Suchrichtung auf unten Bottom-Up (unten nach oben) geändert.
Wir können nach dem letzten Vorkommen des Suchdatums innerhalb der Liste suchen, indem wir -1 in das letzte Argument eingeben.
=XVERWEIS(E3;B3:B7;C3:C7;;1;-1)
XVERWEIS mit dem Datentyp Datum/Uhrzeit
Wie bereits erwähnt, speichert Excel Datumsangaben als ganze fortlaufende Zahlen. Die Zeit wird als Dezimalwert gespeichert, der den Bruchteil des Tages darstellt.
Um nur die fortlaufende Nummer zu erhalten, können wir die GANZZAHL-Funktion verwenden.
Die GANZZAHL-Funktion gibt nur den ganzzahligen Teil (Datum) einer Zahl zurück.
XVERWEIS – Datumsprobleme
Normalerweise entstehen Probleme mit Datumsnachschlagewerten durch Probleme mit dem Datentyp der Rohdaten (z.B. Text, Zahl, Datum). Eine wichtige Regel für die XVERWEIS-Funktion ist die Tatsache, dass der Datentyp des Suchwertes mit dem der Werte in der Suchliste übereinstimmen muss. Wenn die Daten im Datensatz als Text vorliegen und der Suchwert einem Datum entspricht, gibt die XVERWEIS-Funktion den Fehler #NA zurück. 
Datumsangaben als Texte
Am schnellsten lässt sich überprüfen, ob die Daten als Text vorliegen, indem man die Ausrichtung der Daten betrachtet. Wenn sie links ausgerichtet sind, handelt es sich um Text. Wenn sie rechts ausgerichtet sind, handelt es sich um eine Zahl.
Eine andere Möglichkeit besteht darin, sich die Zahlenformatierung anzusehen. Gehen Sie einfach zu Start > Zahl > Zahlenformat und prüfen Sie, ob die Formatierung Text lautet.
Um die als Text gespeicherte Zahl in einen Zahlenwert umzuwandeln, können Sie die Funktion DATWERT oder eine andere der im verlinkten Tutorial beschriebenen Optionen verwenden.
Importierte oder kopierte Daten
Daten, die aus anderen Quellen (z. B. Web, CSV) importiert oder kopiert werden, haben in den meisten Fällen nicht den Datentyp Datum. Um dieses Problem zu beheben, können wir das Tool Text in Spalten von Excel verwenden, um unser Datumsproblem zu beheben.
Schritte:
- Markieren Sie die Daten und gehen Sie zu Daten > Datentools > Text in Spalten
- Wählen Sie „Getrennt“ im Popup-Fenster und klicken Sie auf „Weiter“.
- Wählen Sie im nächsten Schritt „Tabstopp“ und klicken Sie auf „Weiter“.
- Wählen Sie im letzten Schritt „Datum“ und das Format aus. Anschließend klicken Sie auf „Fertig stellen“.
- Das Datum wird in einen Datumsdatentyp konvertiert.
