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

Impressum
Datenschutz