80. Kalender und Terminer in Excel

Einleitung

Diese Abhandlung gibt wie üblich Tipps zur Erstellung eines Kalenders und TerminKalenders ohne Makros.

Da der Sternenhimmelstuermer bekanntlich nicht mehr als eine Handvoll Formeln kann -:) , eine beachtliche Leistung und Fleißarbeit.



In Gedenken an von zu Guthenberg....: Excel: Gaußsche Formel für Excel gibt es hier.


Das  Ergebnis  können Sie als Exceldatei für 2007 hier in einem Zip-Archiv downloaden (Version 1).

Der Excelkalender/Terminer bietet folgende Komfortfunktionen:


Dazu gibt es eine erweiterte Fassung mit der Möglichkeit Kontaktdaten von Outlook 2007 einzupflegen. Gibt keine Garantie und ist eher für den fortgeschritten User. Zusätzliche Funktionen: Geburtstag, Adressenanzeige und halbautomatischer E-Mail-versandt. Ist dann das letzte Kapitel mit neuem Inhaltsverzeichnis:

Download Version 2

Auch hier gilt: ohne Makros


Version 3 ist eine Design-Variante von Kalender 1 mit numerologischer Berechnung - ggf können Sie die anderen Kalender ihrem individuellen Stil anpassen. Durch Ausschneiden und wieder einfügen können Sie die Daten beliebig verschieben. Excel passt sämtliche Berechnungen in den Zellen umgehend an. Weiterhin können Sie beliebig Zellen Ein- und Ausblenden. Das geht, weil eben reine Berechnungen in den Zellen durchgeführt werden - mit absoluten und relativen Zellen. Ausnahmsweise keine Erklärungen zu den teilweise komplizierten numerologischen Berechnungen, da es keine weiteren Abhandlungen mehr im Tippbereich gibt!

Inhaltsangabe

Grundaufbau des Kalenders (Tabellenblatt 1)
Synchronisation von Daten (Termine und Anzeigefläche) (Tabellenblatt 1)
Die Berechnung der Feiertage (Tabellenblatt 1)
Die Eingabehilfe zum schnellen Sprung in die Datentabelle für einen neuen Termineintrag/Bemerkung (Tabellenblatt 1)
Verkettung von Datenbankfeldern (Tabellenblatt 1)
Nur Version 2 des Kalenders: Integration von Outlookdaten und Auswertung (Tabellenblatt 2)



Grundaufbau des Kalenders

Der Kalender besteht auf der Tabellenseite 1(Calendar) aus einer Ansichtsfläche und einer danebenliegenden kleinen Datenbank als Eingabefläche für Termine. In der Regel sollten in der Ansichtsfläche keine Änderungen vorgenommen werden. Für Termineinträge sind in erster Linie die Datenbankfelder, die aus einem Steuersatz in der ersten Zeile und den darunterliegenden Datenfeldern in der Spalte H bestehen, reserviert.

Die Ansichtsfläche besteht fast nur aus Verknüpfungen, um Termine (Überschrift: Eintrag, Spalte H) anzuzeigen, neben denen das Datum und  eine fortlaufende Nummer in der Spalte G (die aus optischen Gründen ausgeblendet wurde -  F und H markieren und mit rechter Maustaste einblenden wählen) bestehen. Die  laufende Nummer unterteilt die Tage dabei in zwei-Stunden-Schritte. Damit die Numerierung für jeden Tag eindeutig ist, wurde eine fortlaufende Numerierung in Spalte G gewählt (1,2,3,4...4940).

Um den Aufbau des Kalenders zu verstehen, machen wir zuerst einen Sprung in einen Ausschnitt der Ansichtsfläche:



   fx=01.01.2011
 CD
52011Tag/Feiertag
611.03.2011
Heute()
Freitag
Wochentag (C6;1)
7

Die markierte Zelle C5 ist die grundlegende Zelle für die Berechnung auf der Eingabeebene für das Datum (Spalte F). In der Formelzeile sehen Sie den 01.01.2011 als Startdatum des Kalenders. In C5 wird nur 2011 angezeigt, weil eine benutzerdefinierte Ansicht gewählt wurde (Registerkarte Start - Dropdownmenü (unter Registerkartenreiter Ansicht) Benutzerdefiniert - mehr - Typ JJJJ - eben Jahresanzeige).

Im Hintergrund (in der Formelzeile) bleibt das für Excel die zuvor eingegebene Zahl 40544. Die Zahl 1 entspricht dem Datum 01.01.1900. Danach zählt Excel immer einen Tag hinzu. Geben Sie doch einfach in eine Zelle 1 ein und formatieren das über das o. a. Menü als Datum. Wenn Sie dieses System begriffen haben, so werden die folgenden Ausführungen erheblich leichter sein!

Wollen Sie den Kalender auf ein anderes Jahr umstellen, so gehen Sie in die Formelzeile (fx) und ändern dort die Zahl in 2012.

Auf der Datenbankebe ändert sich nun das jeweilige Datum (Spalte:F5). Warum? Klicken Sie mal  auf Zelle F15, also den 02.01.2011. In der Formelzeile steht nun: =$C$5+1 .

Zelle C5 ist unser o. a. Datum. Mit $$ wurde diese Zelle absolut gesetzt. Was das bedeutet, soll hier erstmal nicht interessieren. +1 verändert also unseren Kalender um einen Tag. Dementsprechend +2 um zwei Tage. Wenn Sie sich wieder das Dropdownmenü betrachten, so sehen Sie, das aus 40544 eben 40545 wird. Für Excel sind das nur Zahlen, aber diesmal im Datumsformat angezeigt oder ein wenig akribischer: TT.MM.YYYY. Mehr hier.

So, damit wäre geklärt, wie das Datum in einem Arbeitsschritt für das ganze Jahr geändert wird. Eben so einfach wie genial...

In der o. a. Zelle C6 wird per Formel in der Formelzeile das heutige Datum errechnet. Sobald Sie den Kalender öffnen, werden Sie daher mit der Ansicht für den heutigen Tag empfangen. So ist das auch üblich! Natürlich können Sie sich jeden beliebigen Tag anzeigen lassen, müssen aber dazu den Zellwert in das betreffende Datum in der Form: TT.MM.YYYY eingeben, also z. B. 31.01.2011, um sich die Einträge dieses Tages in der Ansichtsfläche anzuzeigen zu lassen. Wie das funktioniert, wird im nächsten Kapitel erläutert. Wichtig ist nur, dass Sie am Ende wieder in die Formelzeile von C6 die Formel zur Berechnung des heutigen Tages eintragen heute (), um wieder den aktuellen Tag anzeigen zu lassen.

Sie bekommen natürlich nur für das laufende Jahr nach Zelle c5 ein vernünftiges Ergebnis! Alles außerhalb des Bereiches der Datenbank wird nicht berücksichtigt. Ein abgelaufenes Jahr archivieren Sie also in einer Kopie des Kalenders. Danach können Sie heute () aus C6 beliebig mit einem Datum besetzen. Es wäre sogar unsinnig dieses nicht zu tun.

Bis hierher war es einfach, aber es wird auch nicht wirklich viel komplizierter...


-----

Synchronisation von Daten (Termine und Anzeigefläche)


Die Synchronisation der Termine unter Eintrag (Spalte H) in der Eingabeebene ist keine Hexerei. Es ist eine feste Regel für Sie: Dateneinträge nie in der Ansicht, da sonst die Formeln in den Zellen zerstört werden. Also Einträge immer brav in die Datenbank in der Spalte H! Gezielt einen Termin anspringen können Sie in der Eingabehilfe: Das voreingestellte Weihnachtsdatum kann (soll sogar!) durch ihr Suchdatum ausgetauscht werden. Dann in D17 den Hyperlink aktivieren, der sich am Suchdatum ausrichtet. Das war es. Jetzt folgt der technische Teil für Fortgeschrittene:

Es wurde ein sogenannter SVerweis verwendet, um den Eintrag anhand der eindeutigen Nummer in der Ansichtsfläche anzeigen zu lassen.

Das System folgt dabei folgendem vom Sternenhimmelstuermer ausgeklügelten System:

In der Spalte G, die sie nach der beschriebenden Methode im letzten Kapitel einblenden, finden Sie die Nummern von 1-4940 entsprechend den Daten und darauf folgenden Stunden, Terminen und Nummern.

In der Spalte B6 (einblenden) befindet sich eine Kombination aus Bedingung und  Verweis, der die laufende Nummer des Anzeigedatums aus Zelle C6, dem heutigen Datum (Heute()- so weit dort das heutige Datum verwendet wird) generiert:

=WENN(C6<>"";SVERWEIS(C6;$F$2:$G$4940;2;FALSCH);"")

Also übersetzt: Wenn C6 (das Datum des Anzeigetages in der Ansicht) gleich dem Datum in der Spalte F (F2-F4940) ist, dann die Nummer anzeigen, die sich daneben in der Spalte G in den Zellen G2-G4940 befindet, ansonsten bitte eine Falschmeldung, was hier nicht vorkommen sollte. Die Schreibweise ergibt sich aus der Syntax für Excel. F2-G4940 ist die sogenannte Suchmatrix, also der definierte Bereich des Vergleiches. Die Werte wurden mit Dollarzeichen absolut gesetzt. 2 und Falsch sind die Parameter für den Verweis - die Suche nach einen wahren Wert (falsch heißt in diesem Fall ein genau entsprechender Wert und wahr ein ungefährer Wert) in Spalte 2 (Spaltenindex). Die Microsoft Corporation erklärt das ganz brauchbar.

Lassen Sie sich die Formelzeile anzeigen und setzen den Cursor in Formelbestandteile, dann sehen Sie jeweils Markierungen um die betroffenen Zellen!

Durch diesen Schritt haben Sie also in der Datenbank zu einem Datum die eindeutige Nummer ermittelt.

Im nächsten Schritt ermitteln Sie die fortlaufenden Nummern für die jeweligen Stunden 2,4,6,8,10 - 24. Das können wir nun autark in den Zellen der Ansicht für jede Zelle berechnen, da wir wissen, das die Nummern in der Spalte G fortlaufend sind.

Also ist B7 die Formel wie B6 +1. B8 wie B6+2 bis B18.

Für B6 ist also die Formel
=$B$6+1
Für B18 ist also die Formel =$B$6+12

Der Sternenhimmelstuermer setzt Formeln oft mit der Taste F4 mit einem Dollarzeichen absolut - egal...

Die Zahlen in der Spalte A sind demnach völlig irrrelevant und dienen nur der Anzeige. Dasselbe gilt auch für den Datenbankbereich. Wichtig sind nur die vollen Datumseinträge in der Spalte F, die sich aus Zelle C5 berechnen - Sie erinnern sich?

Auch die Wochentageanzeigen unter dem Datum in der Spalte F spielen keine Rolle. Sie dienen lediglich zur Orientierung der doch recht langen Jahresbank. 

Am 01.01.2011 war ein Samstag. Dementsprechend steht unter dem Datum (Zelle F2) in zelle F3 die Formel

=WOCHENTAG(F2;1)
.

Also die Aufforderung den Wochentag Samstag anzuzeigen. Die nächsten Zeilen dasselbe Spiel. Die rote Anzeige der Samstage und Sonntage entspricht einer Regel.

Gehen Sie mal probehalber in die Spalte F des Kalenders, markieren eine oder mehrere Zellen der Wochentage (genau so erstellen Sie auch eine Regel für eine oder mehrere Zellen)  und wählen die bedingte Formatierung,  den Punkt Regeln verwalten. Sie werden zwei Regeln finden, eine für Samstag und eine für Sonntag. Zellwert =1 für Sonntag und Zellwert >6 für Samstag.

Durch Anklicken der betreffenden Regel kommen Sie ins Bearbeitungsmenü für Regeln. Dort den Button Formatieren, aber nun die Registerkarte ausfüllen wählen.
Nachdem Sie eine Farbe gewählt haben, ändert sich am Sa oder So die Farbe.


Zurück zum Anzeigefeld. Bisher haben wir dort ja nur die Nummern, was ja nicht sehr ergiebig ist. Also setzen wir nun nach dem Schema für die Nummern in der Spalte C wieder für die Nummern und zugehörigen Einträge in der Datenbank einen Sverweis, in der Matrix befindet sich nun aber die Nummer und der Eintrag für die Stunden 0-2 Uhr in der Zelle C7:

=WENN(B7<>"";SVERWEIS(B7;$G$2:$H$5123;2;FALSCH);"")

Die entspricht wieder der oben beschriebenen Formel, nur das hier G2 bis H51123 verwendet wird, was längenmäßig ein leichter Fehler ist, aber irgendwo auch egal ist, da es immer noch die regulären Datenbankzellen von Daten und Terminen erfasst und dort der Vergleich mit der Nachbarzelle stattfindet.

Die Regel wurde dann in der Spalte C für jedes Zweierstundenpaar kopiert.

Auch hier wurde wie bereits zuvor eine farbliche Regel angewendet.

Ist zwar ein kleiner Umweg erst das Datum, dann die Nummern und dann daraus die Einträge abzuleiten, aber das funktioniert doch toll!

-----

Die Berechnung der Feiertage

Hier wurden die gesetzlichen festen Feiertage, Samstage, Sonntage und sämtliche wichtigen Kirchenfeiertage berücksichtigt.

In der Spalte D wird unter Tag / Feiertag in Zelle D6 wieder mit der Wochentagsformel  =WOCHENTAG(C6;1) in Excel in Bezug auf das heutige Datum [=heute()  oder besser gesagt c6, wenn sie den Ansichtstag manuell ändern] der Wochentag angezeigt und wie im vorherigen Kapitel mit einer Regel zur Anzeige in einerr roten Schrift versehen. Diese Anzeige wird also unabhängig von der Datenbank berechnet.

In D7 wird zum X-ten Mal wieder ein S-Verweis verwendet:

 =WENN(C6<>"";SVERWEIS(C6;$J$4:$K$9;2;FALSCH);"")

Also werden die Spalten nach einer Übereinstimmung der Spalten J und K von der Reihe 4-9 durchgeführt. Entspricht das Datum C6 einem Datum in der Suchmatrix , so wird der nebenstehende Feiertag der Suchmatrix angezeigt, also 25.12.2011= erster Weihnachtstag.

Das Ganze wurde wieder mit einer Regel farblich markiert.

Die Berechnung der kirchlichen Feiertage ist ein wenig komplizierter. O. K. , in Zelle D8 ist mal wieder der obligatorische SVerweis

=WENN(C6<>"";SVERWEIS(C6;$L$10:$M$19;2;FALSCH);"")
,

der keiner weiteren Erklärung bedarf. Die Feiertage wurden jedoch zuvor nach der Gaußchen Formel berechnet.

Der Sternenhimmelstuermer passte dazu die Formel für diesen Kalender ein wenig an:

In zelle M4 steht das Datum von C5 (für das laufendes Jahr 2011, also 01.01.2011 im Hintergrund). Wir brauchen aber das ganze Jahr für die Formel von Gauß.

Also wurde in Zelle M4 das Datum aus Zelle C5 in eine Zahl umgewandelt. Die Zahl 40544 für diesen Tag, wird in Zelle L4 über die Formel =Jahr(M4) ins Jahr 2011 umgewandelt. Diese Brücke muss leider gebaut werden, da 01.01.2011 eben nicht 2011 entspricht, was wir aber für die Formel benötigen.

Ist ganz schön tricky und der frustierte Sternenhimmelstuermer fand diese Lösung komischerweise nicht im Net, sondern durch wildes Ausprobieren....

-----------

Die Eingabehilfe zum schnellen Sprung in die Datentabelle für einen neuen Termineintrag/Bemerkung

In Zelle E15 können Sie durch Eingabe eines Datums einen Hyperlink kreieren, um innerhalb der Datenbank über den Hyperlink in Zelle D17 an das entsprechende Datum zu springen. Diese Vorgehensweise erspart lästiges scrollen.

Eine ausführliche Beschreibung der Funktion finden Sie hier.

Wichtig für Sie : Immer die Form tt.mm.jjjj oder als Beispiel 03.03.2011

----

Verkettung von Datenbankfeldern (Tabellenblatt 1)

Im Bereich Tarot werden Sternzeichen, Tarotkarte (kleine Arkana), Planet, Dekade und Deutung im Datenbereich eines jeden Tages angezeigt. Im Feld Datum (Zelle F2) vom 01.01.2011 finden Sie daneben in den Zellen J2 - M2 die einzelnen Einträge für die Zelle H2.

In Zelle H2 steht die Formel: =VERKETTEN($J$2; $K$2; $L$2; $M$2;$N$2)

Dadurch wird der Wert jeder Zelle nacheinander angezeigt. Durch die rechte Maustaste - Zelle formatieren - und ein Häkchen setzen bei Zeilenumbruch in der Registerkarte Ansicht, kommt der Zeilenumbruch zustande. Wichtig ist nach jedem Wort in der Formel ein Leerzeichen zu setzen, da sonst ein langes Wort ohne Trennzeichen steht.

Diese Verkettung finden Sie an jedem Tag. Wenn Sie lieber etwas anderes eintragen wollen, so können Sie das manuell ändern. Einfach die anderen Bezeichnungen in den Zellen J2-M2 ändern. Der verkettete Eintrag wird in die Ansichtsfläche übernommen. Hier ändern Sie das Wort Tarot in ? (nach Ihren Wünschen) um.

Sie können beliebige Verknüpfung in beliebigen Dateneingabefeldern unter Eintrag - also auch zu jeder anderen Zelle in dieser Tabelle (Calendar) oder Tabelle 2 (Kontakte) anlegen. Das kann sinnvoll sein, um z. B. die Adresse oder Telefonnummer zu einem Termin anzuzeigen.

-----


Integration von Outlookdaten und Auswertung

Gilt nur für Downloadversion 2 des Kalenders mit erweiterten Berechnungen für Outlook!

Inhaltsangabe
Einleitung
Einrichtung der Datenbank
Geburtstagssuche
Adressenanzeige und E-Mailversandt

Einleitung

Jetzt machen  wir  dasselbe, was Facebook und Co. macht, Daten sammeln und auswerten, nur mit dem Unterschied, dass es sich um unsere persönlichen Kontaktdaten handelt und die Daten dort bleiben, wo Sie sollen - in unserem Kalender und nicht in der Cloud! Alles andere ist aus datenschutzrechtlichen Gründen ein Witz!

Dazu legen wir ein weiteres Tabellenblatt an, um einen neuen Abschnitt zu beginnen und neue interessante Funktion von Excel kennenzulernen.

Bisher waren alle Verknüpfungen - natürlich nicht ohne Absicht - auf einem Tabellenblatt. Excel ist jedoch eine mehrdimensinale Anwendung und unser Terminkalender soll ja auch etwas besonderes sein. Die Funktionen bleiben dabei fast dieselben.

In der Ansicht greifen wir die Kontaktdaten aus dem zweiten Tabellenblatt mit Namen Kontakte ab und stellen diese dar. Das Tabellenblatt Kontakte ist wiederum nichts weiter als eine Datenbank mit Steuersatz (Überschriften) und darunterstehenden Zeilen, in denen die Personen stehen.

Durch diese Vorgehensweise rüsten wir den Kalender um folgende Funktionen auf:

A) Geburtstagssuche

B) halbautomatischer E-Maiversandt

C) Erstellung eines Quelldokumentes für einen Serienbrief in Word
(das Herz eines jeden Spammers dürfte hier höher schlagen, wenn es rechtlich zulässig wäre, bräuchte Facebook also nur wenige Mausklicks, um aus ihren hochgeladenen Daten Spams ohne Ende zu produzieren....)...und das kann jeder Anfänger mit Excel und Word!

D) Mit nur wenigen Mausklicks können Sie die Daten wieder in Outlook oder andere Dienste exportieren - inklusive neu erfasster Daten.

-----

Die Einrichtung der Datenbank

Der Sternenhimmelstuermer hat kein Outlook 2007, borgte sich aber eine Konttaktdatendatei in Format *.CSV. Wie Sie Ihre Daten über die gleichnamige Funktion exportieren, können Sie über eine bebilderte Anleitung hier nachlesen.

Als erstes gehen Sie in die Tabellenblätteranzeige links unten, dort wurde durch rechten Mausklick auf den Button Tabelle 1 und Auswahl des Punktes Umbenennen der Name: Kalender gemacht. Dasselbe wurde mit dem Tabellenblatt 2 gemacht, nur wurde hier der Name Kontakte vergeben.

Warum ist das wichtig? Calendar bleibt im Hintergrund Tabelle1  und Kontakte Tabelle 2. Dieses ist für Formeln später ein nicht unerhebliches Kriterium. Es wird zwar in der Formel der originale Name Kontakte!A1 wäre also die Adressierung für die Zelle A1 in der zweiten Tabelle.

Auf der Tabelle Kontakte sehen Sie eine Probedatei. Diese können Sie bedenkenlos löschen. Der Zugriff auf die Daten erfolgt aus Tabelle 1 (Calendar).
Nur eins sollten Sie vermeiden, die Steuersatzzeile muss bei A1 beginnen, da sonst sämtliche Zugriffe aus dem Tabellenblatt Calendar falsche Datenwerte Anzeigen bzw. nichts.

Danach setzen Sie eine Markierung bei A1 im Tabellenblatt 2 bzw. den Namen Kontakte. Gehen Sie in die Registerkarte Daten. Dann in den Bereich Externe Daten abrufen. Dort wählen Sie den Button aus Text. Hier laden Sie Ihre CSV-Datei im Speicherdialog.

Der Textkonvertierungsassistent wird gestartet. Das erste Dialogfeld beinhaltet auch schon die erste Hürde. Sie müssen den Button getrennt als Dateityp aktivieren und weiter klicken. Nun kommt es darauf an, ob Sie eine Kommagetrennte CSV oder eine durch Tabstopp (Leerzeichen) getrennte Datei verwenden.
Wählen Sie diesen Punkt aus. Keine Sorge: im unteren Feld des Dialogfensters ist eine Vorschau. Sie sehen sehr schnell, ob die Zellen stimmen oder nicht.

Nun können Sie schon Fertigstellen drücken. Das folgende Dialogfenster zeigt ein absolutes §A§1 mit Dollarzeichen. Dort steht der Anfang der Tabelle. Das ist korrekt. Bestätigen Sie und das war es auch schon....

Leider sind die Sortierungen absolut unübersichtlich - nicht ohne Grund, weil jeder User, der mit einem Excelkalender arbeitet, jede Menge unnützer Spalten bekommt. Für unseren Kalender, der den Regeln der Nonprofitbewegung entspricht, sind lediglich die Spalten Nachname, Geburtsdatum, E-Mailadresse und die private Anschrift, Postleitzahl, Ort und Telefonnummern wichtig.  

---

Geburtstagssuche


Die Spalten O-S im Calendar sind der Datumssuche gewidmet. In D11 in der Ansichtsfläche wird ein Geburtsdatum am betreffenden Tag angezeigt. In Zelle D12 wird angezeigt, wieviele Geburtstage an diesem Tag sind. Diese Brückenfunktion ist notwendig, da die S-Verweisfunktion nur jeweils einen eindeutigen Wert einem anderen zuordnen kann. Haben zwei Personen am selben Tag Geburtstag, so zeigt der SVerweis die erste Person an und hört auf weiter zu suchen.

Die Geburtsdaten werden aus den Kontakten der Tabelle Kontakte generiert. Dabei werden 75 Zeilen berücksichtigt. Die Berechnung findet also in Tabelle 1 - Calendar statt und die Datenbank befindet sich in der Tabelle 2 Kontakte in der Spalte BN. Sollten Sie Outlook nicht benutzen, so kommen Sie um diese Spalte nicht herum, es sei denn Sie ändern die Berechnungen in O-S.

Die Berechnungen sind sogar für den Sternenhimmelstuermer kompliziert - er ist leider kein Profi und hat das für sich alleine so ausklamüsert:

=WENN(TEXT(Kontakte!BN2;"TT.MM")=TEXT($C$6;"TT.MM");Kontakte!BN2;"kein Geburtstag")

steht in Zelle O5.

Hier wurde eine Textvergleichsformel in eine Wenn-Bedingung integriert. Übersetzt: Wenn In der Tabelle II Kontakte in der Zelle BN2 (Geburtsdatum) die Tage und Monate mit dem Tag $C$6 (Anzeigetag in der Ansicht)  übereinstimmen, dann zeige den Inhalt der Zelle an, ansonsten schreibe "kein Geburtstag".

Kontakte!BN2 ist also die Adressierung für die Zelle im Datenblatt Kontakte. Das Ausrufezeichen signalisiert Excel den Sprung in die andere Tabelle. Genau so springen Sie von einer Tabelle ohne Namen in die andere, z. B. Tabelle2!A1 würde in einer nicht benannten Tabelle 2 den Wert von Zelle A1 der Tabelle 2 verwenden...

In P5 steht

=Kontakte!BN2

Das ist das erste Datum (unter dem Steuersatz, daher !BN2) der Tabelle 2 mit Namen Kalender. Hier werden Ihnen also die puren Geburtsdaten angezeigt. Danach zog der Sternenhimmelstuermer die Reihe am rechten kleinen Häkchen nach unten und kopierte diese Zelle. Excel weiß automatisch, dass nun !BN3, !BN4....kommt. Diese Zelle ist relativ und nicht absolut, da ansonsten immer wieder dieselbe Zelle mit demselben Inhalt kopiert werden würde. Der Sternenhimmelstuermer setzt gerne Zellen absolut mit Dollarzeichen, aber hier ist das nicht sinnvoll, sondern wäre falsch.

Umgekehrt muss $C$6 absolut gesetzt werden, da sonst beim Kopieren in er nächsten Zeile in der Formel C7, in der nächsten C8 usw. stehen würde. Was in einem Fall sinnvoll ist, ist für einen anderen Fall verkehrt. Viele User scheitern an relativen und absoluten Bezügen -  ist aber nur Übungssache...

Dieselbe Kopiermethode gilt übrigens für alle Spalten von O-S.

In Q6 steht:

=WENN(O5=P5;$C§6;"kein Geburtstag")

Das ist nun endlich mal eine einfache Bedingungsformel mit dem Zweck den Geburtstg einer Person in der Anzeigenansicht als Geburtstag anzuzeigen.

Übersetzt heißt die Bedingung: Wenn der Inhalt von 05 (also ein angezeigtes Geburtsdatum vom Ansichtstag - ansonsten würde dort "kein Geburtstag" stehen) mit dem Inhalt von P5 (also dem Inhalt des Datums Kontakte!BN2, der Sternenhimmelstuermer hätte auf dieses Blatt verweisen können, aber es ist genau so gut möglich die Geburtsdaten in die Reihe P direkt zu schreiben!!!) übereinstimmt, dann schreibe das heutige Datum, ansonsten schreibe "kein Geburtstag".

Dafür hätte man auch eine andere Begrifflichkeit nehmen können, aber "kein Geburtstag" bleibt "kein Geburtstag".

In Zelle R5 steht wieder eine einfache Bedingung:

=WENN(TEXT(Kontakte!BN2;"TT.MM")=TEXT($C§6;"TT.MM");Kontakte!BF2;"kein Geburtstag")

Wenn das Datum in der Datenbank (Monat und Jahr) mit dem  Datum (Monat und Jahr) der Ansicht übereinstimmt, dann zeige den Namen der Person aus der Datenbank an, sonst "kein Geburtstag".

Also dieselbe Formel wie zuvor in grün, nur das hier der Name des Geburtstagkindes (Kontate!BF2) angezeigt wird.

Zur Information: Es sind 75 Datensätze vorgesehen, haben Sie mehr, dann müssen Sie diesen Bereich mit ziehen am unteren rechten Rand an der untersten Zelle in der Spalte von O-S anpassen.  Die Nullen bzw. 00.01.1900 sind die Ffolge von Leereinträgen in der Datentabelle Kontakte!

In der Ansichtsfläche greifen wir nun in D11 auf den eben so mühsam berechneten Namen zurück.
Das ist ein wenig komplizierter, weil wir jetzt mit einem SVerweis die Leute heraussuchen müssen die heute Geburtstag haben:

=WENN(C6<>"";SVERWEIS(C6;$Q$5:$R$80;2;FALSCH);"") 

Übersetzt: Wenn C6 (das Aneigedatum in der Ansicht) gleich eines Datums in der Spalte Q ist, dann zeige den danebenstehenden Wert (den Namen des Geburtstagkindes), ansonsten gebe eine Fehlermeldung aus.

Problem: Haben zei Menschen an diesem Tag Geburtstag, so wird der erste gefundene Wert angezeigt und der andere ignoriert.

Deshalb ist in D12 eine einfache Zählformel, wieviele Geburtsdaten in Q  stehen:

=ZÄHLENWENN(Q5:Q80;C6)

Zählenwenn zählt also im Bereich de Spalte Q die Zellen Q5-Q80, wie oft der Inhalt der Zelle C6 (also dem Datum in der Anzeigeansicht) angezeigt wird.

Sie können also in C6 das =heute() (Ansicht des heutigen Tages) in ein beliebiges Datum umwandeln. Ihnen wird zuverlässig der richtige Geburtstag angezeigt!

Trotz der Begeisterung für diese Methode: Es ist irgendwie persönlicher, wenn Sie den Termin in die Eintragsdatenbank in der Spalte H erfassen. Dadurch entgeht Ihnen dann absolut sicher kein Geburtstagseintrag!

------

Adressenanzeige und E-Mailversandt

Der Sternenhimmelstuermer integrierte eine Adressansicht mit einer Hyperlinkfunktion für Emails in den Kalender. Eine schöne Spielerei und leider bei zwei gleichen Nachnamen nicht befriedigend. Das Problem kennen Sie bereits vom Geburtsdatum. Als Alternative könnte man in die Suchfunktion für die Adresse eine Verknüpfung von Vornamen mit Nachnamen unter Anwendung des Befehls Verketten vornehmen, aber der Sternenhimmelstuermer sieht für einen Privatkalender keinen Bedarf!

Die Adressanzeigeansicht wurde unter der regulären Ansicht des Tages angelegt. In der Zelle C22 Geben Sie den Nachnamen der Person in der Tabelle in Kontakte ein. In der Zelle C33 steht die Berechnung in der Formelzeile, die zur Anzeige des Vornamen, Nachnamen, Straße, PLZ , Stadt und E-Mail führt.

=WENN(C22<>"";SVERWEIS(C22;$V$4:$W$80;2;FALSCH);"")

Übersetzt heißt die Funktion, dass der eigegebene Suchname in der Spalte V, Zelle 4-80 mit der Spalte W, Zelle 4-49, verglichen wird (Suchmatrix). Der gefundene Wert wird angezeigt.
 
Das ist zum wiederholten mal ein SVerweis auf einen Übertrag in die Tabelle Kontakte. In der Spalte V stehen lediglich Verweise nach Kontakte!D, der Spalte für Nachnamen der importierten Outlooktabelle in Zelle V4 wird auf Zelle Kontakte!D2 verwiesen und das wird dann wieder durchgängig nach unten kopiert (rechter unterer Anfasser, Werte werden jeweils um eine Zelle tiefer automatisch angepasst, also wieder ein relativer Bezug).

Das Ganze als Formel für Zelle V5 : =Kontakte!BD3
Für Formel Zelle V6: =Kontakte!BD4
usw.

In Spalte W stehen die Verknüpften Adressbestandteile aus den importierten Kalenderdaten von Outlook in Kontakte:

=VERKETTEN(Kontakte!B2;"    ";Kontakte!D2;"    ";Kontakte!P2;"    ";Kontakte!U2;"    ";Kontakte!S2;"    ";Kontakte!AL2; Kontakte!AG2;"    ";Kontakte!AO2;"    ";Kontakte!BD2)

Verketten heißt die Funktion, um Text von verschiedenen Zellen zusammenzufügen.
Die Zellen wurden wieder kopiert. Also stehen nun die Zellen für den Sverweis mit kompletter Anschrift als Inhalt zur Verfügung.

Das war es schon.

Die E-Mailfunktion ist fast dasselbe!

In Zelle C30 steht:

=WENN(C22<>"";SVERWEIS(C22;$T$4:$U$80;2;FALSCH);"")

Also wird der eingegebene Suchname in Zelle C22 diesmal mit E-Mail-Adressen in der Suchmatrix in der Spalte T verglichen und die zugehörige E-Mailadresse aus der Spalte U angezeigt.

Die Spalten T und U in der Tabelle Calendar wurden wieder mit Verweisen auf die entsprechenden Spalten in   !Kontakte angelegt.

C31 ist ein beliebiger Text für den Betrefftext der E-Mail. Zelle C 32 ein beliebiger Text für den Textkörper.

In C33 steht:

=HYPERLINK("mailto:"&C30&"?subject="&C31&"&body="&C32;"Betreffzeile + Nachricht")

Hier wird der anklickbare Hyperlink kreiert. Betreffzeile + Nachricht ist der Text, durch die &-Zeichen wird der Inhalt der Zellen zusammengefügt. Ist so ähnlich wie Verketten...

Damit ist das Kalenderprojekt beendet. Mit den Hinweisen dieser Impulshandlung können Sie weiter Funktionen hinzufügen.

Mit der Kontaktedatenbank können Sie auch mit Word Serienbriefe erstellen.

Wer es schlichter mag, kann den einfachen Kalender benutzen...

Der Kalender ist ein wenig zeitlos, daher die 80 Abhandlung, Quersumme 8 - Die quergestellte 8 ist das Zeichen der Unendlichkeit...

Formelreferenz:

Im Kalender wurden folgenden Befehle verwendet:

Sverweis: Beispiel:
   =WENN(C22<>"";SVERWEIS(C22;$T$4:$U$80;2;FALSCH);""), immer mit Bedingung wenn..dann...sonst...
Wenn:        Beispiel:  
=WENN(O5=P5;$C§6;"kein Geburtstag")
Verketten: Beispiel:   =VERKETTEN($J$2; $K$2; $L$2; $M$2;$N$2)
Hyperlink: Beispiel:   =HYPERLINK("mailto:"&C30&"?subject="&C31&"&body="&C32;"Betreffzeile + Nachricht")
Text:          Beispiel:   =WENN(TEXT(Kontakte!BN2;"TT.MM")=TEXT($C§6;"TT.MM");Kontakte!BF2;"kein Geburtstag")   
Zählewenn: Beispiel:  =ZÄHLENWENN(Q5:Q80;C6)
Wochentag: Beispiel:      =WOCHENTAG(C6;1)
Heute:         Beispiel       =heute()
Jahr:            Beispiel       =Jahr(M4)

O. K., dann sind mit der Gaußchen Formel zusammen doch zwei Hände Formeln zusammengekommen....



Zusatztipp: Mit Hyperlink beliebige Dokumente aus der Ansicht öffnen

Dieser Tipp dürfte dann der letzte dieser Tippseite insgesamt sein, obwohl sich der Autor dieser Abhandlung das Recht vorbehält, diese Abhandlung bzw. den Kalender noch ein wenig auszubauen.
Wie dem auch sei, es ist möglich, von Excel Hyperlinks auf Dokumente oder andere Dateien mit der Kombination Steuerung k (strg k oder englisch ctrl + k zu legen). Gehen Sie in eine beliebige Zelle, markieren Sie diese und drücken Sie diese Kombination und ein Dialogfeld öffnet sich.

In Text Anzeigen als: geben Sie den Text ein, der als Hyperlink in der Zelle angezeigt wird. Auf der linken Seite sehen Sie in Link zu die vielfältigen Möglichkeiten: Ob E-Mail, Webseite oder andere Datei, alles ist möglich. Danach wählen Sie die Datei im Explorer des Dialogfensters aus.

Die Datei wird bei Betätigung des Hyperlinks mit dem Programm geöffnet, dass im Betriebssystem als Standardanwendung verknüpft ist. Im Falle des Kalenders tragen Sie also z. B. einen Hyperlink in ein Feld der Datenbank des Kalenders ein. Dieser wird am betreffenden Ansichtsag in der Ansicht gespiegelt. Outlookfans kennen diese Funktion bestimmt, aber es geht eben auch in Excel.

Diese Funktion ist vielfältig einsetzbar und dank der einfachen Linkerstellungsprozedur über das Dialogfeld schnell und sicher. E-Mails mit Betreffzeile können im Bereich E-Mail erstellt werden.

Früher sagte der Sternenhimmelstuermer: Word ist eine eierlegende  Wollmilchsau - heute sage ich: Es war ein Irrtum, Excel ist noch besser...






Impressum
Datenschutz