Listen nicht nach Datum, sondern nach Tag im Jahr sortieren? Na geht doch!
Bei allen Jubiläumslisten, aber auch Wartungslisten u.v.m. kommt es darauf an, die Daten mit Datum und allen Attributen zu erfassen, sie dann aber nach Monaten gruppiert darzustellen und innerhalb der Gruppe nach dem Tag im Jahr zu sortieren. Die Liste entspricht also dem Jahresverlauf:
| Januar |
| Anna |
2.1.1928 |
| Konrad |
18.1.1986 |
| Beatrice |
21.1.1956 |
| Februar |
| Uwe |
8.2.2005 |
| Walter |
11.2.1968 |
| Gerda |
29.2.2000 |
u.s.w.
Es sollen also zwei Dinge übersichtlich gelöst werden: Das Erfassen der Daten und eine leicht zu filternde Darstellung. Für das erste bietet sich in Excel eine intelligente Tabelle an, für das zweite wird es eine Pivot-Tabelle sein.
Schritt 1 – Anlegen der Datentabelle
Sie können eine Tabelle an einer beliebigen Stelle im Blatt beginnen. Ich bevorzuge Blätter mit Blattüberschriften, einer zusammenfassenden Auswertung und ggfs. auch Grafiken, bevor die schiere Datenmenge beginnt.
Allerdings gibt es zwei Gründe, eine Tabelle in $A$1 zu beginnen:
- Auf sie soll mit ODBC von außen zugegriffen werden (z. B. bei einer Adressliste für einen Serienbrief)
- Es soll die Standard-Maske für Datenbanken zur erleichterten Eingabe von neuen Datensätzen und zur Suche verwendet werden
In diesem Fall richte ich gewöhnlich ein Übersichtsblatt mit Verlinkungen und ein Extrablatt mit den Rohdaten ein.
In unserem Beispiel möchte ich die Datenbank-Maske einsetzen, die mit dem VBA-Befehl
Sheets("Daten").Activate
ActiveSheet.ShowDataForm
ohne weitere Parameter aufgerufen wird. Also beginne ich in $A$1. Alternativ kann der Masken-Aufruf in die Symbolleiste für den Schnellzugriff integriert werden, dann reicht es, wenn der Cursor in der Tabelle steht. Sie brauchen das Makro nicht.
Die Tabelle hat die Spalten Merkmal (Name, Person, …) , Datum, sowie eine Hilfsspalte Tage_im_Jahr.
Schritt 2 – Tageszahl als Hilfsspalte
Für letztere benötigen wir eine Funktion, die die abgelaufenen Tage im Jahr für die Sortierung berechnet. Diese Funktion war der eigentliche Anlass für den Artikel, weil es mehrere Ansätze gibt, diese zu berechnen – eine kleine Knobelaufgabe: Hier folgen die Varianten, die Lukas, Günter und ich zusammengetragen habe. [@Datum] ist dabei der Bezug auf das Datum-Feld in der jeweiligen Zeile.
=TAGE([@Datum];DATUM(JAHR([@Datum]);1;0)) ' Tagesdifferenz als eigene Funktion
=DATEDIF(DATUM(JAHR([@Datum]);1;0);[@Datum];"d") ' beliebte Funktion, hier mit Parameter "d"
=DATUM(JAHR(HEUTE());MONAT([@Datum]);TAG([@Datum]))-DATUM(JAHR(HEUTE());1;0) ' Vorschlag bei ExcelNova.de
=DATUM(JAHR([@Datum]);MONAT([@Datum]);TAG([@Datum]))-DATUM(JAHR([@Datum]);1;0) ' besser, gilt auch für Schaltjahre
=BRTEILJAHRE(DATUM(JAHR(B2);1;0);B2;3)*365 ' Mal ausprobiert, wichtig ist der Parameter 3 = Actual/365
=[@Datum]-DATUM(JAHR([@Datum]);1;0) ' tut es sehr einfach! (Danke, Günter)
Schritt 3 – Pivottabelle
Beim Erstellen setzen Sie das Datum und das Merkmal in die Zeilenbeschriftung, lassen das Datum gruppieren (Tag und Monat werden gebraucht, aber den Tag aus dem Zeilenfilter entfernen), und setzen Datum und Tage_im_Jahr in das Wertefeld. Da jedes Datum mit seinem Merkmal nur ein einziges Mal vorkommt, kann man die Wertezusammenfassung bei Summe belassen, ich habe mich hier für die Funktion Max entschieden. Sortieren Sie nach der Spalte Tage_im_Jahr und blenden Sie die Spalte dann aus.
Ich habe der Datentabelle noch eine Spalte Halbjahr spendiert, um dieses Feld in den Seitenfilter zu nehmen. Mit drei Klicks lässt sich dann die Tabelle in zwei Teilen darstellen und ausdrucken. Wird sie noch wesentlich länger als im Beispiel, geht das natürlich auch mit Monaten: Eine weitere Monatsspalte ist notwendig, da die Spalte aus der Gruppierung in den Zeilenbeschriftungen nicht gleichzeitig im Seitenfilter verwendet werden kann.
Peppen Sie die neu geordnete Liste mit einem Datenschnitt nach Monaten auf. Wählen Sie statt des Standards Kurzformat bei den Entwurfseinstellungen/Layout/Berichtsformat das Gliederungsformat aus, so haben Sie für die Merkmale/Namen eine eigene Spalte und können darin nach einem bestimmten Merkmal suchen.
Nicht vergessen: Sind neue Daten in der Tabelle hinzugefügt worden, muss die Pivottabelle aktualisiert werden.
Zum Schluss
Link zur Datei: 2018-09-05_THEMA_Geburtstagsliste_TageFunktionen
Die Makros brauchen nicht aktiviert werden, wenn Sie den Maskenaufruf und die Aktualisierung sowie den Berichtsseitenabruf über Menüband und Schnellzugriffsleiste aufrufen.
Dankeschön an Lukas Rohr und https://excelnova.org/2018/09/referenzlisten-verwalten-in-excel-geburtstage-telefonlisten-kunden-produkte-etc/ für die Anregung zu diesem Artikel.
Diesen Spaß wünsche ich Ihnen mit meinem Tannenbaum und einer lustigen Formel!
Neue Spalten bekommen automatisch das Tabellenformat.
