Maske in Excel für einen immerwährenden Kalender
als Beispiel
Inhaltsverzeichnis:
1. immerwährender Kalender Fertiggestellt und Beschreibung
2. Überlegungen zur Maske und deren Verwendung (veraltete Abhandlung)
Download: Kalender.zip, ca. 44 KB, Inhalt: Kalender.xlsm: Download,
Enthält ein Makro, Text des Makros wird hier veröffentlicht:
Sub Datenerhebung()
'
' Datenerhebung Makro
' Einfügen von Datum und Notiz mit Leerzeile
'
'
Range("B38:C38").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Cut
Range("A41:B41").Select
ActiveSheet.Paste
Range("B38:C38").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Rows("41:41").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
Das Makro ist ungefährlich und ist für die Eingabe erforderlich. Es
werden keine Daten außerhalb des Dokuments erhoben oder gebraucht.
Wenn sie mir misstrauen, dann diese Version ohne Makro herrunterlanden.
Danach können Sie den o. a. Makrotext kopieren und das Makro einfügen!
Das geht so:
(Sollten die Entwicklertools als Registerkarte nicht eingestellt sein, so in start- Exel-Optionen dort häufig verwendet und dann rechts Entwicklerregisterkarte...anzeigen aktivieren) :
Dann in die Registerkarte Entwicklungstools wechseln.
Dort als Name Datenerhebung eingeben (so heißt das Makro). Dann erstellen dücken.
Dann sämtlichen bereits Code löschen bzw. mit dem o. a. Code überschreiben.
Das Ergebnis wird dann durch Drücken von Speichern und Bestätigung der >Meldung mit "nein" auswählen.
Nach Speicherung in einem neuen Format in dem automatisch geöffneten speichern Unter-Dialog (Wählen sie hier im Speichern unter Dateityp: Excel-Arbeitmappe mit Makros (*.xlsm im Menü: Speichern) übernommen.
Fertig: Vielleicht haben sie gerade ihr erstes Macro erstellt?
Programmieren ist gar nicht so schwer. Das o. a. Makro wurde übrigens
mit dem Makrorecorder aufgestellt.
Ansonsten ist die Schaltfläche Hinzufügen bedeutungslos. Mit dem Makro
von oben klappt es dann. Oder Sie löschen hinzufügen und geben dann in
die Datenbank oder mit Hilfe einer Maske ein, wie das geht können sie in der alten Abhandlung nachlesen.
----
1. immerwährender Kalender Fertiggetellt und Beschreibung
Beschreibung
Dieser Kalender und Ihre Notizen sind zeitlos, da die Notizen in einer
Datenbank im Dokument abgelegt werden. Er ist leicht exportierbar
als Monatsansicht oder Jahresansicht mit sämtlichen Notizen, die angezeigt werden
Feiertage werden berücksichtigt und angepasst. Die Notizen werden in einer Datenbank im Kalender abgespeichert.
Es gibt eine Monatseinsicht zum übersichtlichen Eintragen von Notizen.
Zur Erleichterung der Einträge wird hier ein Makro verwendet.
Das ist harmlos und wird hier erklärt. Die Datei wurde gezippt, damit Browser die Kalender.xlsm beim Download nicht verweigern.
Es ist anzuraten, bei der Datumseingabe von der Notiz die Schreibweise
tt.mm.jjj zu tätigen. Natürlich kann hier auch bei z. B.
immerwiederkehrenden Geburtstagen die Formel:
=DATUM(A2;1;1) bei Neujahr eingegeben werden, wobei in Zelle A2 das Jahr steht.
Vielleicht zwei Bilder des Kalenders von der Monatsansicht und Jahresansicht:
Monatsansicht, Quelle Excel 2007, Snipping-Tool:

In Zelle A2 wird das Jahr angezeigt Sie können das Jahr beliebig
ändern, dann wird der zugehörige Monat in B3 eingetragen. In B3 sollte
eine Dropdownliste mit der Auswahl der Monate stehen. Bei der
Konvertierung in höhere Excelversionen wird diese Liste zuweilen nicht
übernommen.
Dann müssen Sie selbst Hand anlegen:
Listenfeld, wo man sich den Monat
auswählen kann: Sollte ihr Office das nicht annehmen, so Erstellen
geben Sie den Monat per Hand ein oder benutzen die Zellen E/F 5 - 16 (Zellen sind ausgeblendet, also wieder einblenden), dazu A3
markieren, Registerkarte Daten> Datenüberprüfung>
In Datenüberprüfung: Registerkarte Einstellung: jeden Wert in Liste
umwandeln und dann als Quelle E/F 5-16 durch markieren eintragen.
Die Sicherheitsmeldung, dass dieses Dokument Makros enthält unter Optionen der Sicherheitswarnung den Satz "Diesen Inhalt aktivieren", diesen aktiviereb und nach Bestätigung durch O.K.
geht es los!
Unter Datum und Notiz tragen Sie ein Datum in der Form TT.MM.JJJJ oder
eine Formel ein, um z. B. einen wiederkehrenden Geburtstag ein.
Bezugsjahr sollte dabei immer Zelle A2. mit dem Jahr sein: =DATUM(A2;1;1) bei Neujahr.
Die Notiz ist freigestellt.
Beim Drücken von Hinzugen werden Datum und Notiz kopiert und in die
Datenbank eingetragen, die ab Zelle a42 und b42 beginnt. Ich blendete
die Zellen von AB 44 bis 77 aus.
Wichtig:
Es können von $A$40:$B$1136 Zellen ausgelesen werden, dieses ist die
absolute Begrenzung der Suchmatrix des SVerweises. Die Datenbank kann
erweitert werden, das muss dann in der Monatsansicht wie Jahresansicht
geschehen mit Suchen und ersetzen
Registerkarte start > ersetzen. , dann als Suche einsetzen: $A$40:$B$1136 und wenn es meinetwegen 5000 Zeilen werden sollen: $A$40:$B$5000
Keine Sorge, dass funktioniert!
Wir waren aber gerade beim Kopieren: Es wird eine neue Zeile eingefügt,
die Daten oben eingetragen und die Daten in den Eingabefeldern
gelöscht. Sie könnten natürlich ohne Makro immer wieder Datum und
Notizen selbst eintragen, aber so ist es einfacher!
Wichtig:
Ein SVerweis kann jeweils nur eine Notiz hinzufügen, was mache ich
also, wenn ich eine weitere Notiz für eine Datenbank am selben Tag
eintragen möchte: Ich sehe zum Beispiel am 01.01.xxxx den Eintrag
Neujahr. Dann gehe ich in den Datenbankeintrag Neujahr und füge dort
die zweite Notiz ein. Ach ja, Neujahr wiederholt sich ja jedes Jahr,
also nicht vergessen die Notiz zu entfernen, wenn man sie nicht im
Neujahr haben will...
Wichtig: Nie
auf die Idee kommen: etwas in die grauen Felder von c5 - c35 eine Notiz
einzutragen: Grund da ist eine Formel drin, die auf die Einträge in der
Datenbank verweist: In der Datenbank hingegen können oder sollen sie
sogar Änderungen machen. Auch nicht die Zelle B4 ändern.
A2 und A3 (Jahr und Monat müssen Sie hingegen ändern, um das Jahr und den Monat zu bestimmen.
Das war es zur Monatsansicht, jetzt kommen wir zur Jahresansicht:

Die Jahresansicht ist schnell erklärt, weil die Werte der Datenbank
hier übernommen werden und das Jahr sich aus ihrer Einstellung in der
Monatsansicht ergibt.
Ansonsten sollte nichts verändert werden.
Design ist ja bekanntlich Geschmackssache und wenn Sie es ändern wollen
(würde das sofort mit einer Kopie machen, um das Original nicht zu
zerstören....), vielleicht noch ein paar Hinweise:
Ein verschieben der Monatsblöcke geht (habe das ausprobiert): Die
Formeln passen sich an. Es gibt bedingte Formatierungen Samstag und
Sonntag sind so als Formel grau hinterlegt.
Nachschauen: start - bedingte Formatierung Regeln verwalten.
Vorsicht bedingte Formatierungen gehen vor. Die grauen leeren Formatierungen sind ebenfalls bedingte Formatierungen: Wenn kein Eintrag
irgendwo als Notiz steht ,dann wird diese Zelle mit grauen Hintergrund
hinterlegt und die Schriftbekommt dieselbe Farbe, welches den visuellen
Eindruck von grau ergibt.
Das grün ist schnöde gefüllter Farbhintergrund, kann also geändert werden. Ebenso die blauen Kopfzeilen.
Die blaue Hintergrundfarbe der Notizen ist ein Thema für sich: Die
Zellen sind ganz normal mit blau ausgefüllt, aber weil bedingte
Formatierung vorgeht, sieht man halt kein Eintrag in grau auf grauem Hintergrund. Also allgemein andere Farbe der anderen
Notizen einfach die gewünschte Farbe als Hintergrundfarbe ausfüllen!
verschiedene Farben bei den Notizen zu vergeben, würde ich über
eine bedingte Formatierung, vielleicht mit einem Textinhalt wie
Gebburtstag verknüpft.
Sie wollen weitere Farbeinteilungen, z. B. orange für Feiertage: Dann
machen Sie es mit der bedingten Formatierung. Dazu gehen Sie in die
Datenbank und ändern den Notizeintrag leicht ab.
Setzen Sie z. B. drei Leerzeichen mit der Tab-Taste am Ende hinter dem
letzten Eintrag. Danach gehen Sie in die bedingten Formatierungen und
wählen dann neue Regel. Dann nur Zellen, formatieren, die enthalten:
Dann in der Registerkarte aus dem Dropdownmenü mit Zellwert in
bestimmter Textinhalt) umändern. In mit Inhalt wieder drei Leerzeichen
eingeben.
Dann den Button Formatieren drücken und dort die gewünschte Hintergrundfarbe wählen.
Ups, ich vergaß die Zeilen zuvor auszuwählen, dass hole ichjetzt
nach-:) : Erst einmal Farbe auswählen und dann bestätigen. Schließen
und mit O.K. bestätigen.
Dann noch einmal in die bedingte Formatierung, wo ich dann unter Regel
verwalten alle Regeln aufrufen kann. Dann dort erstmal
Formatierungsregeln in dieses Arbeitsblatt umstellen, um alle Regeln zu
sehen. Da steht u. a. Zellwert enthält ` `, dass ist die Regel die wir
suchen. Dort rechts steht dann ein Fenster unter der Rubrikwird
angewendet auf: Daneben ist ein Bildchen und das drücke ich und leere
den Inhalt. Danach markiere ich auf der Monatsansicht die Notizenreihe
des Kalenders. Dann übernehme ich das und fertig.
Für die Jahresansicht dasselbe Spiel nur das ich beim Markieren noch
die strg-Taste halte. Dieses bewirkt, dass sie nur die Notizen in
allen zwölf Monaten selektieren können.
Wenn sie etwas fauler sind, dann markieren Sie die ganze Tabelle:
Normalerweise sind keine drei Leerzeichen hintereinander zu finden.
Dieses Prinzip können Sie nun übertragen und für vier Leerzeichen eben eine andere Formatierung erwirken.
Wie bereits gesagt: Dieser Kalender mit seinen Notizen kann über Jahre weitergeführt werden.
Ich wünsche viel Spaß und das schöne ist, dass sie diesen Kalender mit
Einträgen wirklich über Jahre Führen können und auf die Notizen ein
leben lang zurückgreifen können
2. Überlegungen zur Maske und deren Verwendung
Achtung, diese Abhandlung wurde zur Erklärung der Verwendung
einer Kaske geschrieben. Der folgende Kalender war eine Probedatei und
ist bei weitem nicht so gut - ist bloß zur Erklärung geignet
Die Exceldatei ganz ohne Makros können Sie hier herrunterladen
(Vorsicht, ich bin auf Office 2007 und zuweilen wird die Listenanzeige
auf höheren Versionen nicht übernommen - keine Sorge wird noch
erklärt). Es geht nicht um einen Kalender in dieser kurzen Erklärung,
sondern auf die Beantwortung der Frage, ob ich Eine feste Eingabe
machen kann und der Inhalt anderen Zellen automatisch zugeordnet werden
kann und die Grundeingabe erhalten bleibt.
Aber schauen wir uns erst einmal den Kalender an:

Quelle: Microsoft Excel 2007 und Snipping Tool vom Autor erstellt.
Diese Abhandlung ist für fortgeschrittene User konzipiert, daher im
Schnelldurchlauf:
In A2 können Sie ein beliebiges Jahr einstellen, also in diesem Jahr
2023. Darunter sehen Sie ein Listenfeld, wo man sich den Monat
auswählen kann: Sollte ihr Office das nicht annehmen, so Erstellen
geben Sie den Monat per Hand ein oder benutzen E/F 5 - 16, dazu A3
markieren, Registerkarte Daten> Datenüberprüfung>
Datenüberprüfung: Registerkarte Einstellung: jeden Wert in Liste
umwandeln und dann als Quelle E/F 5-16 durch markieren eintragen.
Würde
ein falscher Wert außerhalb Jan -Dez eingetragen, welches ja dank Liste
unmöglich ist, würde hier ein Fehler angezeigt werden
Darunter sind von A/B: 5-35 nur die ermittelten Tage und das Datum des
Monats.
Die B-Reihe ist in der Anzeige nur benutzerdefiniert auf den Tag der
Nachbarzelle zur Anzeige des Tages, dass Datum kommt aus der
Nachbarzelle der C-Reihe und wird erstmalig in Zelle B5 generiert,
wobei Jahr und Datum wiederum aus Monat und Jahr Ihrer Eingabe erstellt
wird.
Vorsicht in der c-Reihe ist ein S-Verweis. Hier werden Kopien von
Einträgen mit der Maske erstellt. Die Grundlage der Datenbank sind die
Felder A/B 40 - 103.
Trotzdem der SVERWEIS kurz erklärt, Anhand der ersten Notiz in c5:
=WENN(ISTNV(SVERWEIS(B5;Tabellenblatt1!$A$40:$B$103;1;FALSCH));"Kein
Eintrag";SVERWEIS(B5;Tabellenblatt1!$A$40:$B$103;2;FALSCH))
Wenn es keinen Eintrag in Der Datenbank mit der Überschrift Datum
Funktion gibt in den Zellen $A$40:$B$103, also in der ersten
Spalte kein betreffendes Datum steht, dann gibt es die Meldung
kein Eintrag, sonst Suche mir den zweiten Eintrag in der zweiten
Spalte, der genau mit dem Eintrag in der zweiten Spalte übereinstimmt.
Die Funktion wurde nach unten kopiert, deshalb wurde die Datenbank als
absolut definiert. Excel passt ja bekanntlich alles sonst an...
Anmerkung :So, dann habe ich noch für Samstag und Sonntag eine bedingte
Formatierung gesetzt - Fenster o. a. Wochentage sind auf 1-5 gesetzt, 6
und 7 sind Sa und So, wird bei Start und bedingte Formatierung
eingetragen.
So, jetzt kommen wir zur Datenmaske: Die muss man sich erst
über die Optionseinstellungen (Start Exceloptionen - Anpassen -
Häufig verwendete Befehle - Maske raussuchen und hinzufügen). Jetzt
kommt der eigentliche Hauptteil:

Quelle: wie o. a.
Ich markiere einfach ab A/B40 den Raum der Datenbank: Trick: Habe ich
eine größere Datenbank, dann Blende ich die unteren Spalten einfach
aus. Beim späteren Eintragen der Maske besteht kein Unterschied, ob die
Zellenausgeblendet oder eingeblendet sind. Ich kann dann bei einer
größeren Datenbank leichter die Zellen markieren. Ich blendete bei der
Probetabelle Zellen aus. Nach 46 kommt dann 100.
Dann rufe ich die Maske auf und siehe da: Die Überschriften Datum
und Funktion sind als erste markierte Zellen die Überschrift. Es
empfiehlt sich ein Datum oder eine eindeutige Nummer zu verwenden.
Problem: Beim Suchen mit S-Verweis wird nur der erste Eintrag eines
gleichen Datums registriert. Lösung. Sie sehen in der Kalenderdatei ja,
ob an einem Tag ein Eintrag existiert. Existiert ein Eintrag z. B. am
20.02.2023, dann suchen Sie in der Datenbankanfrage nach dem Eintrag
und Fügen den entsprechenden Eintrag hinzu.
Ansonsten wird mit jedem Eintrag der o. a. Sverweis aktiv und das Datum
muss deshalb in der o. a. Form eingegeben wird, während die Notiz egal
ist.
Fazit
Ach ja, die Maske wird temporär erstellt und deren Existenz wird
nirgends in Excel vermerkt. Die temporäre Maske arbeitet quasi
wie ein Makro, mit dessen Hilfe Einträge in einer Datenbank gemacht,
gelöscht, gesucht oder neu erstellt werden.
Den Neueintrag finde ich bei einer großen Datenbank nicht so leicht
wieder oder kann keine Bestimmung über deren genauen Ort. Das
kann ich aber über den SVerweis als Krücke ausgleichen, indem ich eben
eine Zahl oder ein Datum verwende.
Jede Eingabe wird eben dokumentiert - ganz ohne zusätzliches Makro!
Mit etwas Fantasie können Sie diese Kombination aus SVERWEIS und Maske
viele Projekte Umsetzen oder Rechnungen Dokumentieren und haben eine
valide Datenbank erstellt!
Impressum