90 kalendarischer Anwesenheits-/Urlaubs-/ Fehltageplaner in Excel

Einleitung

Eigentlich wollte der Sternenhimmelstuermer mit Open-Officeprodukten weiterarbeiten, aber da der Sternenhimmelstuermer und Excel eine symbiotische Einheit sind, zur Zeit eher ein frommer Wunsch. Wie dem auch sei - dieser Plan hat es wie immer bei Sternenhimmelstuermerprojekten in sich. Wie immer ist der Aufbau dieser Abhandlung: Download, Beschreibung der Features und dann für den Excelfan Beschreibung der Lösungen. Die Exceltabelle ist mit Kommentaren gespickt, so dass Sie den Aufbau halb intuitiv auch so hinbekommen müssten. Der Urlaubsplaner ist so weit fertig, die Abhandlung am entstehen - das ist hier so üblich:-). Wie immer ist das Projekt im Freewaregedanken quelloffen erstellt, keine Makros oder andere Haken. Veröffentlichung auf anderen Seiten nur mit Verweis auf diese Seite, Verwertung in geldwerter Form ein absolutes "no go!".

Download (ca. 430 KB, ab Excel 2007 - wegen Formatierung so empfohlen - entpackt ca. 648 KB).


Features:

Tabellenblatt 1 Plan in kalendarischer Darstellung und Einträge


Tabellenblatt 2 Daten

Tabellenblatt 3 Rechnungen


Kurze Beschreibung

Die ausführliche Anleitung für den Excel-Fan folgt bald. Hier erstmal grundlegende Anleitung für den User. Die Exceltabelle ist mit Kommentaren gespickt. Die können Sie über das rechte Maustastenmenü mit Kommentar löschen schnell entfernen

Erste Schritte:

Der erste Schritt dürfte eine Kopie des Plans sein. Vorsicht ist die Mutter der Porzelankiste. Außerdem sollte für jedes Jahr eine Kopie vorliegen, damit Ihre Einträge ordnungsgemäß abgelegt werden können.

Der zweite Schritt dürfte Sie in Zelle A8 führen. Nach Anklicken der Zelle Januar 12 sehen Sie in der Adressleiste den 01.01.2012. Das Datum können Sie nun beliebig in der nebenstehenden Form ändern. Es sollte mit 01.01. + z. B. 2011 geschehen. Alles andere bringt Darstellungsschwierigkeiten - die für einen Fortgeschrittenen nicht wirklch ein Problem sind, aber Mehrarbeit bedeuten.

Danach schauen Sie sich die Masterleiste ein wenig an. Generell gilt: Text/Überschriften einschließlich der o. a. Kürzel wirken sich auf allen Text, Überschriften und Kürzeln auf die anderen 11 Monate aus. Deshalb heißt die Januarleiste auch Masterleiste, klar?

Die Berechnungen aller Zellen beziehen sich in allen Tabellenblättern auf die Zellen, aber nicht auf deren Inhalt. Das nennt man Arbeiten mit Variablen. Es gilt weiterhin: Graue Zellen sind in der Regel änderbar und beinhalten keine Formel.

Die eingetragenen Probemitarbeiter auf der linken Seite sind hingegen mit blauer Farbe hinterlegt - genau wie die Infoleiste: Das heißt: Finger weg! Einträge im Plan hingegen a la uf, ef, a und u sind erwünscht. Sie arbeiten also grundsätzlich visuell orientiert auf der Tabellenseite Plan!

Dazu müssen Sie natürlich einmalig den Nachnamen und das Geburtsdatum im Tabellenblatt Daten in den Spalten B und C eingeben. Daneben in Su/Beh Sonderurlaubstage bzw. Zusatztage wegen Behinderung. Daneben den Resturlaub vom vorherigen Jahr. Wirkung: Fortan werden Ihnen die Resturlaubstage im Plan angezeigt. Tragen Sie nach alter Nomenklatur ein u ein, so wird der Tag in der Spalte Resturlaub für den jeweiligen Mitarbeiter angezeigt. Steht da eine -1, dann hat sich der Mitarbeiter wohl einen Tag zu viel Urlaub genommen.

Die Urlaubstage werden in der Infoleiste im Plan umgehend übernommen. Außerdem dürften dort gesetzliche Feiertage eingetragen sein. Schauen Sie im Tabellenblatt Daten nach, ob alles so weit stimmt.

Im Tabellenblatt Daten können Sie ab den Reihen H und  I jeweils ab Zelle 71 das Datum einer Info und die Info für einen beliebigen Kalendertag eintragen. Darüber sind die gesetzlichen Feiertage und Geburtstage.

Es gilt:

Ein Geburtstag und gesetzlicher Feiertag werden gemeinsam in der Infoleiste des Plans ohne Problem angezeigt.
Ein Geburtstag und eine Info am selben Tag sind O. k.
Zwei Geburtstage am selben Tag von Mitarbeitern sind tödlich, der erste wird angezeigt, der zweite nicht. Kleiner Trick: zweiten Geburtstag in Daten der Infoleiste wiederholt eintragen - Notfalls in Verbindung mit einer weiteren Info - das wäre wieder erlaubt.
Drei Einträge nicht möglich - am besten einen Mitarbeiter entlassen - ne, war nur ein Scherz, aber ein gestandener Sachbearbeiter findet dafür auch eine Lösung...

Wie dem auch sei zur Not kontrollieren...Ach ja, die Zellen sind grau hinterlegt - sichere Anzeichen für eine Eingabe.

Ab der Zelle k1 im Tabellenblatt Daten stehen dann die Resttage des letzten Jahres eines durchgehenden Krankheitsblocks. Wird der Mitarbeiter am 30.12.2011 also krank, so ist eine zwei einzutragen, insoweit er nicht wieder am 31.12.2011 erscheint. Ach ja, im Plan müssen Samstage und Sonntage mit einem Krankheitstag versehen werden, wenn der Mitarbeiter dort krank ist. Das entspricht eigentlich auch den Regeln im Arbeitsleben - jedenfalls findet sonst eine Unterbrechung eines Krankheitsblockes statt und Sie können die Zählung der Krankheitstage für das Krankengeld vergessen! Das dürfte aber für eingefleischte Personalsachbearbeiter kein Problem sein.

Warum ist das so? Der Sternenhimmelstuermer verwendet zwei SVerweise, um einen Zweiteintrag zu ermöglichen. Das ist ziemlich raffiniert, weil ein Sverweis allein nur ein Datum mit zugehörigen Eintrag finden kann. Nach Excel-Logik der erste Eintrag. Wer das weiß, kann durch einen zweiten Verweis in Kombination mit einer Verknüpfung einen weiteren Eintrag hinzufügen. Diese Technik ist vom Sternenhimmelstuermer entworfen - wie die Technik zur Zählung der Krankengeldtage. Es gibt dafür keine Lösungen im Netz ohne Matrixformeln oder Makros.

Das Tabellenblatt Rechnungen dürfte nur in drei Fällen interessant werden:

  1. Sie arbeiten in einer Firma und nicht Behörde, dann gehen Sie in Rechnung in die Spalten H, I und J und ändern dort die Altersgruppen und dazu gehörigen Urlaubstage (sind mit gelber Farbe hinterlegt - in diesem Fall heißt das: zur Änderung freigegeben). Ändern Sie dort die drei Zahlenreihen nach belieben vom jüngsten bis zum ältesten Lebensalter und schreiben die Urlaubstage hinzu. Die Rechnung findet in den linken Nachbarzellen statt. Der Inhalt der Zellen ist Bezugspunkt für die Berechnung. Die sollte immer ganzen Jahren erfolgen
  2. Sie wollen noch Karfreitag oder Fronleichnam hinzufügen - ist die Rechnung gleich daneben. Einfach mal die Zellen mit rotem Dreieck (Kommentar) anklicken, das wird dann erklärt
  3. Sie vermuten einen Fehler, dann scrollen Sie nach ganz rechts zu den Spalten NS folgende: Dort finden Sie einen Vergleich der Fehltageberechnung in Bezug auf ihre verwendeten Kürzel im Tabellenblatt Plan (haben Sie die Kürzel verändert, so ist das schnurzpiepegal - passt sich im Tabellenblatt Rechnung an). Sehen Sie dort ein OK, so ist ein Fehler relativ unwahrscheinlich. Es werden zwei unterschiedliche Berechnungen im Gesamtplan durchgeführt. so dass ein Fehler relativ unwahrscheinlich ist - kommt jedoch eine Fehlerwarnung, dann den Sternenhimmelstuermer bitte informieren!

Fehlerquelle: Wenn Sie am 29 Februar ein Krankheitstrag eintragen (obwohl außer der 29 kein Wochentag da steht, dann wird der Krankheitstag gezählt. Nicht in der Krankheitsgeldtagerechnung, aber in der normalen Zählung auf dem Tabellenblatt Plan. Viel mir gerade so auf, aber bin zu faul, dass zu ändern. Vielleicht am Ende dieser Abhandlung. Ein Grund wiederzukommen :-)


Für den Excelfan

Inhaltsangabe
Einleitung
Grundaufbau
Themenschwerpunkt Masterleiste
Themenschwerpukt Masterzellen und Berechnung anhand des Beispiels unentschuldigter Fehltage
Themenschwerpunkt Krankengeldtage/Schaltjahr
Themenschwerpunkt Informationsleiste

Einleitung

Die Projekte des Sternenhimmelstuermers haben immer Schwerpunkte, die eine Hilfestellung für das eigene Erstellen - in diesem Fall eine Anwesenheitsliste - einer Tabelle bieten. Die anderen Projekte des Sternenhimmelstuermers waren immer von A - Z dokumentiert - zum Leidwesen der fortgeschrittenen User. Wegen Zeitmangel und  der besseren Lesbarkeit werden also nur die Problemschwerpunkte dieses Projektes beleuchtet. Da gerade die Wenn-Formel und Sverweis des öfteren im Mittelpunkt der Abhandlungen des Sternenhimmelstuermers stehen - bei Unverständnis einfach die Abhandlung über die  Ernährungstabelle aufsuchen - das sind so um die 24 Seiten....

Grundaufbau

Der Sternenhimmelstuermer entschied sich für 12 Monate auf Tabellenseite Plan! Die Januarleiste wurde zur Masterleiste auserkoren, da die anderen Monate sich in Ihrer Art wiederholen. In der Datumsberechnung hat Excel den Vorzug, dass Daten in Formeln berechnet werden. Gehen wir die Masterleiste einmal kurz durch, dann wird der Aufbau klarer:

Wir befinden uns auf dem Tabellenblatt Plan!

In Zelle A 8 Steht das Datum des Jahres TT.MM.JJJJ, also z. B. 01.01.2011. Die Ansicht als Januar 11 ist eine benutzdefinierte Ansicht, im Ribbenmeü - Start - Dropdownmenü in der Mitte mit Eintrag Benutzerdefiniert - Dropdownmenü mehr - Registerkarte Zahlen von dem Dialogfenster Zellen formatieren - linke Seite Benutzerdefiniert - rechte Seite im Typenauswahlfeld MMMM JJ.

Im Monat Februar (A 61) steht in der Zelle =DATUM(JAHR(A8);MONAT(A8)+1;TAG(A8))

Es wird also das Datum aus Zelle A8 generiert. Datum ist die Formel zur Datumsanzeige , Jahr (A8) das Jahr aus Zelle A8, beim Monat wird eine 1 addiert, damit wir im Februar landen. Wozu so kompliziert? Später soll das Jahr durch eine Umstellung im ersten Monat Januar auf alle anderen Monate übertragen werden. Das ist wichtig, da wir spätestens im Februar sonst Probleme bekommen, da dieser in Schaltjahren 29 Tage hat. Was noch wichtiger ist - die Wochentage im Monat generieren sich aus diesem Datum.

Nachdem also die 12 Monate angelegt wurden , geht es nun zu den Wochentagen.

Im Januar 2001 war der erste Wochentag ein Samstag. In der Reihe 8 befinden sich die Wochentage. Hier mal die ersten zwei Zellen, der Rest wird kopiert:

In B8 steht:  =TEXT(A8;"TTTT") Also wird hier mit der Formel Text die Zelle A8 als Wochentag dargestellt. Die Syntax der Formel Text verlangt vor dem Semikolon die Bezugszelle und hinter dem Semikolon die Darstellungsform des Textes.

Erinnern Sie sich an die benutzerdefinierte Darstellung des Januar 2011 in Zelle A8? Die Funktion stellt hier TTTT nach, was als Konvention die Darstellung des Wochentages heißt. Also könnten Sie, falls Sie eine andere Darstellungsform haben wollen, einfach mal in den benutzerdefinierten Einstellungen schmulen - kein Mensch kann diese Konventionen auswendig - man muss nur wissen, wo man nachschaut!

Am 02. Januar wählte der Sternenhimmelstuermer: =TEXT(A8+B9;"TTTT")

Der Sternenhimmelstuermer setzte also jede Zelle in Bezug auf A8. B9 ist nur die Eingabe des ersten Tages. in der darunterliegenden Reihe, also eine 1. Das geht dann beim kopieren schneller, da diese Bezüge relativ sind und so nach unten weiterkopiert werden können, also im nächsten Monat passt sich das dann automatisch an die Februarzeile an.
Hm, besser wäre natürlich ein Dollarzeichen vor dem A8 gewesen - hilfe ich werde alt...probieren Sie das mal aus!

Wie dem auch sei - Samstag und Sonntag wurden einer Regel unterworfen, damit Sie farblich markiert werden. Gehen Sie bei markierter Zelle zur Registerkarte Start des Menüs zur bedingten Formatierung in der Gruppe Formatvorlagen. Gehen Sie dort zu Regeln verwalten. Im Manager zum Verwalten von Regeln finden Sie die Regeln. Die zwei Regeln Zellwert enthält Samstag bzw. Zellwert enthält Sonntag sind die entscheidenden Regeln. Doppelklicken Sie die grauen Balken eines Tages. Im Dialogfenster Formatierungsregel bearbeiten sehen Sie die erstellten Regeln. Bei Regeltyp auswählen ist: Nur Zellen, die enthalten markiert. Bei Regelbeschreibung wurde in den Dropdownfeldern ausgewählt bestimmter Text, mit Inhalt und dann Sonntag eingetragen.

Jetzt wird die Sache Rund. Der Sternenhimmelstuermer stellte die Wochentage als Text in der Form "TTTT" da. Dort steht also für Excel ein Sonntag. Die Regel sagt: Steht in einer Zelle Sonntag, dann formatiere diese Zelle (Button Formatieren, dort können Sie im folgenden Dialogfenster einstellen, wie die Zelle oder der Tag denn nun aussehen soll).

Doch woher weiß Excel nun den Bereich, wo die Regel angewendet wird? Schließen Sie das Femster der Regel und schauen im Manager nach. Dort steht: =$B$165:$AF$165;$B$218:$AF$218;$B$271:$AF$271;
$B$325:$AF$325;$B$378:$AF$378;$B$431:$AF$431;$B$484:$AF$484;$B$537:$AF$537;
$B$8:$AF$8;$B$113:$AF$113;$B$61:$AD$61;$AF$61

Das musste der Sternenhimmelstuermer nicht eintragen. Er markierte einfach die Tabelle, nachdem er das Icon hinter den Angaben drückte, worauf man den Bereich aussuchen kann. Durch den Button Neue Regel kann man auch gerne neue Regeln hinzufügen.

Das ist hier nicht Themenschwerpunkt: Merken Sie sich nur: Man kann ein Datum am besten als Wochentag anzeigen, in dem man es mit der Textformel konvertiert und dann den Text mit einer Regel belegt.



Themenschwerpunkt Masterleiste

Dieser Themenschwerpunkt wird Anhand eines Beispieles durchexerziert UF - Unentschuldigte Fehltage.

Ziel ist eine hohe Flexibilität. Der User kann durch die Änderung einer Zelle den gesamten Plan in der Darstellung ändern. Er kann z. B. UF in K umbenennen und alle folgenden Zellen zeigen ein k wie Krank an und die Berechnungen interessiert das nicht im geringsten. Das nennt man im Fachjargon arbeiten mit Variablen. Die Variable ist die Zelle - der Inhalt ist egal.

In der Masterzelle (B7) steht einfach nur uf. Richtig, die Sklaven richten sich nach dem Master. Deshalb ist da nichts weiter nötig. Gehen wir in Zelle B60. Dort verrichtet der erste Sklave über der Frebruartabelle seine Arbeit, Formel: =$B$7

Ja, auch das ist eine Formel. Das Gleichheitszeichen leitet die Formel ein B7 ist die Masterzelle und die Dollarzeichen setzten B7 absolut, was nur darum interessant ist, weil die Zelle auf diesem Tabellenblatt kopiert wurde. Die anderen 11 Sklaven bei den Monatsnamen sind also nur kosmetischer Natur. Tragen Sie in die Masterzelle k ein, so wird das übernommen.
Das spart mächtig Zeit und ist eben vorausschauendes programmieren. Arbeiten Sie bei sich wiederholenden Werten nur mit Masterzellen und Sklaven - das lohnt sich in vielfacher Hinsicht.

Wie dem auch sei - in der Masterleiste der Tabellen sind sämtliche Überschriften und Kürzel nach diesem Prinzip aufgebaut. Doch wie wirkt sich das nun auf die Berechnungen aus? Das wird im nächsten Kapitel behandelt.



Themenschwerpukt Masterzellen und Berechnung anhand des Beispiels unentschuldigter Fehltage

Die einfachen Berechnungen werden im Plan durchgeführt. In der Jahresübersicht und Statistik Monat sehen Sie neben jeden Monat eine einfache Auszählung nach Monat und Jahr eines Mitarbeiters.

In Zelle AN 10 des Tabellenblattes Plan! steht: =ZÄHLENWENN(B10:AF10;$B$7)

Die Mitarbeiter stehen ganz links in Reihe A untereinander im Plan. Alle zwölf Monate werden Sie wiederholt und sind, um im Sprachgebrauch zu bleiben, sämtlich Sklaven der Masterzelle im Tabellenblatt Daten! Dort werden Sie eingetragen, um die komplizierteren Berechnungen und Einträge seperat durchzuführen. Da diese Einträge einmalig sind, ein vertretbare Lösung ohne lästiges scrollen oder Blättern. Die Mitarbeiter werden an dieser Stelle stiefmütterlich behandelt - eine Frage der Didaktik.

Die Formel ist interessant: Zählenwenn dürfte jedem bekannt sein: Das Gleichheitszeichen leitet die Formel ein. Nach dem Formelnamen die obligatorische Klammeröffnung. Zwischen den Klammern die Aufforderung vom 1 bis 31 Tag des Monats Januars die Einträge uf - so weit vorhanden - auszuzählen.

B10:AF10 ist also der Bereich, wo Excel zählt und nach dem Semikolon steht in B7, was gezählt wird. Der Inhalt der Zelle B7. Richtig, da steht kein uf und das ist so gewollt. Ein Anfänger oder viele ungeschickte Fortgeschrittene würden hier "uf" eintragen. Das ist natürlich nicht falsch, würde aber gegen das Gesetz Master und Sklave verstoßen.

Würde also jemand hier uf eintragen und ein User in B7 das uf gegen k austauschen, dann würde das k nicht gezählt werden.

Da aber die Variable B7 dort steht, wird jeder Inhalt gezählt, egal ob k, xyz.

Die Formel wurde wieder teilweise absolut gesetzt $B$7. Das ist immer ein Anzeichen dafür, dass die Formel kopiert werden soll. Für die anderen Mitarbeiter wurde die Formel nach unten kopiert. Das spart enorm Zeit. Der Bereich verschiebt sich automatisch um eine Zelle nach unten und B7 bleibt als Bezugszelle bestehen. Lernen Sie relative und absolute Bezüge - ansonsten kommen Sie über ein paar Zellen nicht hinaus oder brauchen Jahre für ein anspruchsvolles Projekt!

In AI10 steht eine Ausnahmeformel für die Jahresberechnung von Fehltagen:

=SUMME(ZÄHLENWENN(INDIREKT({"B10:AF10"."B63:AF63"."B115:AF115"."B167:AF167"."B220:AF220".
"B273:AF273"."B327:AF327"."B380:AF380"."B433:AF433"."B486:AF486"."B539:AF539"."B592:AF592"});$B$7))

Ja, der Sternenhimmelstuermer kann auch Matrixformeln, die angeblich professioneller sind, aber in diesem Kontext schwachsinn. Eigentlich sollten Sie die Berechnung erleichtern. In diesem Fall wurde die Summe sämtlicher uf`s im Jahr für den Mitarbeiter Athus ausgezählt. Leider geht die Formel nicht zu kopieren - da der Inhalt der Bereiche einer Matrixformel absolut gesetzt sind - und das ohne Dollarzeichen - dafür stehen die {}. Deshalb geht der Sternenhimmelstuermer nicht weiter auf die Formel ein.

In der nächsten Zeile steht dafür die kopierbare Alternative - AI11:

=SUMME(AN11;AN64;AN116;AN168;AN221;AN274;AN328;AN381;AN434;AN487;AN540;AN593)

Das ist die Summenformel der Monatsauswertung von der zuvor berechneten Zählewennfunktion. Die Problematik, dass eine Zählewenn-Formel nur einen Teilbereich auszählen kann (eine bekannte Schwäche in der Excelwelt, dass nur ein Bereich in der Zählewenn-Formel berücksichtigt werden kann - der Sternenhimmelstuermer bittet die Microsoft Corperation um Nachbesserung - eine Matrixformel ist kein adäquater Ersatz und führt zu Konfusion) wurde umgangen, in dem Die Zählewennformel auf einzelne Bereiche angewendet wurde und mit der Summenformel die Werte addiert wurden.

Wie dem auch sei... dieser Themenschwerpunnkt ist damit abgearbeitet und die anderen Auswertungen der Anwesenheitstage, Entschuldigte Fehltage, und teilweise Urlaubstage damit abgearbeitet.



Themenschwerpunkt Krankengeldtage/Schaltjahr

Sie habe also alle Werte in die Tabelle Daten! eingetragen (inklusive der Restfehlzeiten vor 1. Januar) und Excel zählt die Blöcke der Krankheitstage eines Mitarbeiters, also wird das erste Erreichen von 42 Tagen mit jedem unentschuldigten oder regulären Fehltag angezählt und in der danebenliegenden Spalte in der Tabelle Plan! angezeigt , wie oft 42 Tage erreicht werden.

Exemplarisch wird der Mitarbeiter Arthus durchexerziert. Im Tabellenblatt Plan! finden Sie nur zwei Sklaveneinträge: =Rechnung!NZ2 in Zelle AQ10 und  =Rechnung!OA2 in Zelle AR10.

Also gehen wir in das Tabellenblatt Rechnung! Ab Zeile L 9 wurden die Datumseinträge für das Jahr gespiegelt.
Wie, soll egal sein, sehen Sie ruhig nach. Also 365 Einträge. Zu dem Problem mit Schaltjahren im Februar kommen wir noch.In der Reihe darunter eine Spiegelung der Kürzel, die Sie beim Mitarbeiter Arthus eingetragen haben.

Die dritte Zeile ist eine Zwischenergebniszeile und wird hier erörtert. Dabei müssen im ersten und zweiten Zellenbezug die Formeln nochmal einzeln erklärt werden. Warum? Der Mitarbeiter kann vor dem 01.01 eines Jahres schon einen zusammenhängenden Block gefehlt haben. Deshalb müssen diese zusammenhängende Tage im Blatt Daten eingegeben werden. Ist nur ein liebevolles Detail.

Daher wird erstmal die Zelle N11 erklärt, die durchgehend kopiert werden konnte. Ja, fast durchgehend, da für die Umstellung auf ein Schaltjahr eine Weiche implementiert wurde.

In M11 steht also:  =WENN(ODER(N10=Plan!$B$7;N10=Plan!$R$7);1+M11;0)

Die Formel gehört zu den eher einfachen:

Wenn ein entschuldigter oder unentschuldigter Fehltag (eigentlich Inhalt von Zelle B7) steht, dann Rechne die vorherige Zelle plus 1, ansonsten (also wenn die vorherige Zelle keinen Fehltag enthält), Trage eine Null ein. Die Oder-formel ist nötig, da es ja leider nun mal entschuldigte oder unentschuldigte Fehltage gibt.

Was haben wir davon?

Es entstehen Zahlenreihen mit Maximalwerten. Am ersten Fehltag eine 1. Weil in der vorherigen Zelle erstmal eine 0 steht. Durch das Kürzel für Fehltage wird die Formel "Rechne die vorherige Zelle und den heutigen Fehltag dazu" aktiviert.

Am zweiten Fehltag steht in der Nachbarzelle genau dieselbe Formel. Das Kürzel des Fehltages wird als 1 gezählt und mit der vorherigen Zelle des 1. Krankheitstage addiiert: 1 (Ergebnis der vorherigen Zelle) und 1 macht 2.

Neben wir an, der Mitarbeiter fehlt drei Tage. Dann steht in der dritten Zelle Wieder dieselbe Formel, die das Ergebnis der Nachbarzelle, also eine 2, mit 1 addiert. Das Ergebnis ist also 3.

In der vierten Zelle, wo der Mitarbeiter wieder erscheint, steht nun ein Anwesenheitskürzel oder fakultativ bleibt die Zelle leer. Also ist die Bezugsgröße B7 der wennformel nicht mehr vorhanden. Nun wird der zweite Teil der Formel aktiv. Im Sonst-Teil steht eine 0. Das heißt, das Ergebnis wird wieder genullt und nicht wie in den voerherigen Tagen zur vorherigen Zelle hinzugezählt, was in unserem Fall einer vier entsprechen würde.

Wir der Mitarbeiter irgendwann wieder krank, dann wiederholt sich das Spielichen.

Wo liegt bei dieser lustigen Kindergartenaddition der Sinn ? Wir bekommen einen Maximalwert der Krankheitstage und bei Excelfans wird spätestens jetzt der Groschen...meinte natürlich Cent...fallen. Maximalwerte kann man doch aus einer Reihe mit Excel mit einer Formel heraussuchen. Richtig! 1 und 2 interessieren uns nicht wirklich, aber die drei ist der Maximalwert der Krankheitstage in dieser Reihe, wenn der Mitarbeiter nich nochmal mit mehr Krankheitstagen fehlt.

In Zelle NQ des Tabellenblattes Rechnungen steht daher die Formel: =MAX(M11:NN11). Damit schließt sich der Kreis. Dieses ist der Wert, der gespiegelt wird in den Plan! für den Mitarbeiter Arthus. Das wäre nun ein schönes Ende der Ausführungen, aber zwei Probleme bleiben offen: Schaltjahre und Fehltage vor dem ersten Januar.

Fehltage vor dem ersten Januar sind leicht:

=WENN(ODER(M10=Plan!$B$7;M10=Plan!$R$7);1+Plan!AP10;0)

steht in der ersten abweichenden Zelle M11, die gerade diesem Umstand Rechnung trägt.

Der erste Teil bis zum Semikolon nach der Oderformel (Bedingung der Wennformel) ist wieder Baugleich mit der ersten o. a. Formel, danach ändert sich nur im Dann-Teil der Bezug. Dieses sind dann die Fehltage vor dem 1. Januar, die im Tabellenblatt Daten eingetragen wurden, danach in das Tabellenblatt Plan! gespiegelt wurden und schließlich hier abgegriffen werden. Wird kein Übertrag eingetragen, so entspricht das einer Null. Also wird der Fehltag am 01.01.xx dann brav als 1 gezählt oder bei fehlendem Eintrag als Null...total einfach....

Dann bleibt noch das Problem mit dem Schaltjahr. Mal gibt es 28 Tage und mal 29 Tage - das ist für eine Reihe tötlich. Wenn Sie die vorherigen Ausführungen verstanden haben, dann wissen Sie auch warum. Man kann nicht den 29 einfach immer stehen lassen, weil die Wennfunktion stur nach dem obigen Schema handelt. Dann wäre der Plan nur alle vier Tage richtig.

In der Zelle K9 schräg über Arthus im Tabellenblatt steht aber schon eine Andeutung: Februartrick.

In K11 steht dann die Weiche für den 29. Februar:

 =WENN(Plan!$AE$61=MONATSENDE(Plan!$AE$61;0);BS11;BT11)

Das sieht wieder kompliziert aus, aber entwirren wir erstmal die Formel:

Es wird ein absoluter Bezug zur Zelle Plan!AE61 gesetzt. Leider müssen wir dazu die Formel in dieser Zelle auseinanderklamüsern: In Plan! AE61 steht:

=WENN(AE61=MONATSENDE(AE61;0);"";TEXT(A61+AE62;"TTTT"))

Das sagt nichts anderes aus, dass wenn der Ende des Monats  nur 28 Tage hat, dann schreibe dort nichts hin, ansonsten den betreffenden Wochentag 58 Tage nach dem 1. Januar.

Die Formel Monatsende wird so schön auf dieser Seite erklärt, dass da nichts hinzuzufügen ist.

Der Rest sagt nur aus, dass der Wochentag wieder als Text angezeigt wird. Wer diese Abhandlung chronologisch liest, ist da klar im Vorteil. Wurde bereits bei den Grundlagen dieser Tabelle abgehandelt.

Zurück zur groß formatierten Formel. Hier werden also die Zellen BS11 beim vorhandensein des Monatsendes in einem Schaltjahr oder BT 11 als Alternative in einem Jahr mit 28 Tagenfür den Mitarbeiter Arthus angeboten.

Das sind die Ergebniszellen vom 28 und 29 Februar, in denen die Auszählung der Krankheitstage des Mitarbeiters stattfand. Ist ein Mitarbeiter also vom 28.02 an Krank, so steht beim Eintrag für den 29 eine 2. , wenn dort ein Eintrag gemacht wurde. Die Weiche, mit der o. a. dicken Formel selektiert also je nach Schaltjahr oder kein Schaltjahr einen Wert.

Das bist ja schön und Gut - aber im Augenblick fehlt uns noch der Punkt, wo die Weiche eingebaut wird, damit die Reihe geschlossen bleibt. Die Antwort ist einfach. Die Zelle des 1. Märzes im Tabellenblatt Rechnung beinhaltet die Weiche:

In BU 11 steht also:

=WENN(ODER(BU10=Plan!$B$7;BU10=Plan!$R$7);1+K11;0)

Die Formel hatten wir schon diverse Male. Wenn also wieder ein entschuldigter oder unentschuldigter Fehltag des Mitarbeiters Arthur am 1. März im Plan eingetragen wurde, dann addiere diesen Wert mit unserem zuvor berechneten Weichenwert (also den Inhalt der Zelle des 28 Tages oder im Schaltjahr den Wert des 29 Tages), sonst nulle wieder das Ganze.

Der Sternenhimmelstuermer gibt es zu, dieser Logik zu folgen ist schwierig, aber einen echten Excelfan machen Herausforderungen nichts aus!

Danach wurde das Ganze wieder kopiert - für jeden Mitarbeiter.

Die zweite Auswertung - wie oft ein Mitarbeiter 42 Tage gefehlt hatte, ist eine Notwendigkeit. Fehlte der Mitarbeiter einmal 50 Tage, dann ist der Maximalwert 50. Durch die Zählweise des Sternenhimmelstuermers würde also bei einer Fehlzeit von 43 Tagen des Mitarbeiters, die 42 zweimal existieren. Einmal vom Block der 50 Tage und einmal von den 43 Tagen der zweiten Krankheitstage.

Deshalb ist es auch nicht möglich den zweitkleinsten Wert zu zählen. Bei 50 Tagen würden 49 Tage in der vorherigen Zelle stehen (Addition des vorherigen Zellenwertes plus 1).

Als Trostpflasterwerden wie gesagt die Zellen gezählt, wo eine 42 drin steht mit der Formel:

=ZÄHLENWENN(M11:NN11;42) beim Mitarbeiter Arthur. Wer die Abhandlung bis hierher gelesen hat, weiß auch, wo de zugehörigen Zellen stehen:-) 



Themenschwerpunkt Informationsleiste

Der letzte Themenschwerpunkt ist die Infoleiste und das Problem, wie man mit SVerweisen mehrere Einträge in einer Zelle unterbringen kann. Dem Sternenhimmelstuermer ist keine Webseite bekannt, die einen ähnlichen Lösungsansatz auch nur annährend beschreibt und Foren scheiden durch die Komplexität einfach aus. Diese Beschreibung ist nur für Fortgeschrittene. Anfänger verstehen das folgende nicht - es sei denn Sie lesen sich das Grundwissen in anderen Projekten über S-Verweise an.

In Style! in der Reihe 57 ist die Infoleiste vom Januar. Diese wieder exemplarisch für die ganze Tabelle.

In Zelle B57 steht  die Zelle für den Eintrag am 01. Januar eines Jahres.

In Zelle B57 steht:

=WENN(ISTNV(SVERWEIS(B56;Daten!$H$3:$J$250;3;FALSCH));" ";SVERWEIS(B56;Daten!$H$3:$J$250;3;FALSCH))

Gehen wir mal die Zelle langsam durch:

Es wurde eine Wennformel verwendet, die nichts weiter besagt, dass wenn der Inhalt der ausgeblendeten Zelle B56 (das Datum 01.01.2011 oder 2012...) im Tabellenblatt Daten! in der Matrix in der dritten Spalte (3) mit dem genauen (FALSCH) Datum nicht vorhanden ist (IstNV), dann dann lasse die Zelle leer (""), sonst drucke den Inhalt der dritten (3) Spalte des genau übereinstimmenden (Falsch) Wertes.

Bis hierher eine normale wennformel verknüpft mit ist nicht vorhanden und einem Sverweis, also eher nichts aufregendes.

Im Tabellenblatt Daten! müssen wir uns jetzt die Reihen H und ausgeblendete Reihe J, also die dritte Spalte des SVerweises, ein wenig näher ansehen, deren Wert im Tabellenblatt Plan! angezeigt wird.

In der Reihe H stehen also die Geburtsdaten der Mitarbeiter, darunter die Daten der Feiertage und darunter die allgemeinen Einträge. Alles im Suchbereich des ersten Sverweises (H3:I250). Diese sucht der erste SVerweis des Tabellenblattes Plan!.

Am 01.01.2011 Fallen zwei Ereignisse zusammen der Mitarbeiter Arthus hat Geburtstag (Zelle H3 ist Datum und I3 zugehöriger Geburtstagseintrag) und es ist Neujahr (Zelle H54 das Datum und I54 der Neujahrseintrag)..

Im Tabellenblatt in der Reihe J (also der Ergebnisreihe des ersten Sverweises von Zelle B57 im Tabellenblatt Plan!) steht nun die schöne Formel in J3:

=WENN(ISTNV(SVERWEIS(H3;Daten!$H$53:$I$250;2;FALSCH));I3;
SVERWEIS(H3;Daten!$H$53:$I$250;2;FALSCH)&" "&I3)

Jetzt wissen Sie wenigstens, warum der Sternenhimmelstuermer das nicht Anfängern erklärt...

 In der Reihe H stehen also die Geburtsdaten der Mitarbeiter, darunter die Daten der Feiertage und darunter die allgemeinen Einträge. Alles im Suchbereich des ersten Sverweises. Diese sucht der erste SVerweis des Tabellenblattes Plan!. Wird er fündig, greift der zweite Sverweis in Zelle J3, der unabhängig in seinem Bereich nach dem vorkommen desselben Datums sucht, in dem der Mitarbeiter Geburtstag hatte.

Ein SVerweis reicht bekanntlich nur bis zum ersten Vergleichswert, also z. B. einem Geburtstag wie beim Mitarbeiter Arthur. Deshalb fragt der erste fett gedruckte o. a.  SVERWEIS im Plan! auch das Datum und die nebenstehenden Angaben in der dritten Spalte J ab. Bei Arthur bleibt der erste SVWERWEIS im Matrixbereich gleich in der Zelle H3 hängen und zeigt den Inhalt des zweiten Sverweises in Zelle J3 an, der die Weitersuche nach Einträgen übernimmt.

Die erste Hälfte des SVerweises von J3 ist mal wieder die nicht vorhanden Variante, wenn kein weiterer Eintrag vorhanden ist. Dann wird I3, also die Zelle, in dem das Geburtstag von Arthur vorhanden is,t allein als Wert in die Zelle ausgedruckt. Der erste Sverweis ist zufrieden und übernimmt nur diesen Wert. Er übernimmt also nur den Inhalt der im zweiten Sverweis angezeigt wird.

Der zweite Fall ist, das in der Suchmatrix ist von H53 bis H250 (also fast derselben Suchmatrix wie vom ersten Verweis, nur mit dem riesigen Unterschied, dass die Geburtstage hier nicht erfasst werden, die in den Zeilen von H3 bis H50 stehen) ein weiteres Datum steht:
Der zweite SVerweis steigt also bei den Daten der gesetzlichen Feiertage ein. Hier wird er im Falle des Mitarbeiters Arthur auch fündig. Der 01.01.JJJJ ist nämlich als gesetzlicher Feiertag in Zelle H54 eingetragen. Also kommt beim sonst-Teil eine kleine Abwandlung. 

Der gefundene Eintrag Neujahrstag aus der Suchmatrix wird "addiert" mit dem Geburtstagseintrag. Also das Ergebnis der Matrixformel mit einem &" " für ein Leerzeichen (ist nur für die Optik, da das sonst rechtschreibmäßig blöd aussieht), und einem weiteren & mit dem Geburtstag des Mitarbeiters (I3 für Arthur).

Der erste SVerweis im Blatt Plan! greift sich dieses Ergebnis ab. Das Wunder ist vollbracht. Zwei gleiche Werte wurden durch zwei SVerweise mit einem leicht unterschiedlichen Suchbereich zusammengefügt und im Plan angezeigt.

Die Formel konnte wieder 250 mal kopiert werden, da die Matrix absolut gesetzt wurde. Also findet in jeder Datumszelle in der Reihe H eine Überprüfung durch eine Zelle der Reihe J statt. Trägt ein User ein Datum (Reihe H) und eine Information (Reihe I) ein, so ist im Hintergrund immer die vorgefertigte Matrixformel in. Reihe J aktiv.

Will jemand also in Excel zwei gleiche Werte oder mehr mit einer Suchmatrix suchen und gehört dieser Wert zwei unterschiedlichen Suchgruppen an, so ist dieses möglich. Ist nur ein Wert in der Suchmatrix vorhanden, so greift die erste umfassende Mastermatrix, die den ganzen Bereich absucht. Für einen weiteren Treffer wird eine Hilfsmatrix benötigt, die im Prinzip der Ergebniswert der Mastermatrix ist. (Das ganze Spiel könnte man mit einer weiteren Hilfsmatrix weitertreiben, aber da Zweidrittel der Fortgeschrittenen schon am Ende ihrer Kapazität sein dürften....).

Ausgenutzt wird dabei der Umstand, das die erste Matrix beim ersten gefundenen Wert hängen bleibt. Erst durch dieses berechenbare Verhalten, wird die Verfahrensweise einfach, da die Mastermatrix überhaupt einen Wert finden muss, damit die Hilfsmatrix bei einem zweiten gleichen Wert der Ursprungsmatrix angezeigt werden kann. Ansonsten wid das Ergebnis der zweiten Matrix nicht im Urlaubsplan angezeigt und fristet ein unbeachtes Eigenleben. Die Zelle weiß an sich überhaupt nicht, dass Sie eine Hilfsmatrix ist. Für sich alleine.  

Ach ja, der Masterverweis muss immer den ganzen Bereich abdecken und eine Hilfsmatrix nur einen Teilbereich. Warum? Die Mastermatrix braucht erstmal den Überblick, ob in unserem Fall ein Datum existiert, was Sie in die Infoleiste eintragen kann. Findet Sie einen Eintrag, dann bleibt Sie stehen und nimmt den Wert dieses Eintrages. Der Wert ist gekoppelt mit dem Sverweis, der einen Teilbereich hinter dem Fund lokalisiert, ob es einen weiteren Matrixeintrag gibt.
Willkommen in der Matrix :-)


Anmerkung

Die Sternenhimmelstuermerhomepage bietet weitere geniale Excelprojekte:

Ernährungstabelle
Numerologie
Kalender
Verexcelung von Paragrafen 






Impressum
Datenschutz