Kalenderdaten vervollständigen
21.07.2022, 08:00 Uhr
Excel: Fehlende Kalendertage per Formel ergänzen
Sie haben eine Liste mit Kalenderdaten und einem zugehörigen Wert. Nur: An manchen Tagen wurden keine Werte erfasst. Etwa für ein Diagramm möchten Sie aber die Tage vollständig haben, auch wenn für manche davon keine Werte vorliegen.
An den rot markierten Stellen (links) fehlt jeweils mindestens ein Kalendertag
(Quelle: PCtipp.ch)
Lösung: Sie können zu SVERWEIS oder XVERWEIS greifen. Angenommen, im ersten Blatt namens «Ursprungsdaten» stehen in Spalte A die Kalenderdaten und in Spalte B die zugehörigen Werte. Wie Sie sehen, beginnen unsere Beispieldaten hier nicht mit dem 1.1.2022. Ausserdem fehlen Einträge, beispielsweise am 4., 7. oder 9. Januar und weiteren Kalenderdaten.
Das sind die Werte, die wir haben. Aber es fehlen Einträge an manchen Daten
Quelle: PCtipp.ch
Wollen Sie eine Liste mit allen Kalenderdaten haben und bei jenen, die keinen Eintrag haben, einfach nichts eintragen?
Erstellen Sie ein separates Tabellenblatt. Dort listen Sie die Kalenderdaten z. B. in Spalte A mittels AutoAusfüllen vollständig auf, also alle vom 1.1.2022 bis 31.1.2022. Jetzt geht es darum, in Spalte B aus dem Blatt «Ursprungsdaten» die zugehörigen Werte zuzuordnen, sofern vorhanden.
Variante SVERWEIS
Früher hätte man dies wohl mit SVERWEIS gelöst. Im Grundsatz hiesse die Formel so:
=SVERWEIS($A2;Ursprungsdaten!$A:$AA;SPALTE(B$1);FALSCH)
Das bedeutet: Nachschlagen des Wertes, der in A2 steht, in Spalte A des Blattes «Ursprungsdaten». Wenn gefunden, den Wert aus dem Bereich Spalte B zurückgeben (und zwar aus B selbst, hierfür steht die 1). Das abschliessende FALSCH bedeutet, es wird keine ungefähre Übereinstimmung geduldet.
Das Problem mit dieser Formel ist, dass ihr eine Fehlerprüfung fehlt. Wenn nichts gefunden wird, erscheint ein hässliches #NV in den Zellen. Das fangen Sie ab, indem Sie die SVERWEIS-Formel in eine Fehlerprüfung packen:
=WENNFEHLER(SVERWEIS($A2;Ursprungsdaten!$A:$AA;SPALTE(B$1);FALSCH);"")
Das heisst: Wenn bei der SVERWEIS-Formel ein Fehler auftritt (also z. B. nichts gefunden wird), dann soll Excel einfach nichts ("") eintragen.
Moderner: Variante XVERWEIS
In neueren Excel-Versionen (ungefähr seit 2020) gibts auch die neue Funktion XVERWEIS. Ihr Vorteil ist hier, dass sie eine eigene Fehlerprüfung enthält. Das ist eleganter und ginge damit so:
=XVERWEIS($A2;Ursprungsdaten!$A:A;Ursprungsdaten!$B:B;"")
Die Formel bedeutet: Den Wert aus A2 schlage bitte in Spalte A im Blatt «Ursprungsdaten» nach. Liefere den Wert aus der dortigen Spalte B zurück. Wird nichts gefunden, dann trage nichts ein.
Die drei Formelvarianten im direkten Vergleich
Quelle: PCtipp.ch
24.07.2022