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
- - Dienstplan in
kalendarischer Darstellungsform für bis zu 45 Mitarbeiter
- -
Ewigwährender
Dienstplan, mit einer kurzen Änderung werden Jahre,
gesetzliche und christliche Feiertage, Samstage und Sonntage angepasst
- -
Schaltjahre werden zu 100 % in Darstellungsform berücksichtigt
- -
Urlaubstage, Anwesenheitstage, entschuldigte Fehltage und
unentschuldigte Fehltage in Monatsauswertung und Jahresauswertung
- -
automatischer
Übertrag von Tabellenblatt Daten von Mitarbeitern.
- -
Zählen von Krankheitstagen im Block für Übersicht einer
Krankhengeldregelung nach 42 Tagen (sechs Wochen).
- -
Masterleiste über Januar: Sie können sämtliche Kürzel für
Anwesenheitstage, Urlaubstage...nach gutdünken verändern; Rechnungen
passen sich an
- - Infoleiste mit Feiertagen,
Geburtstagen und Feiertagen, passt sich automatisch jedes Jahr an - Die Infoleiste wird mit
Einträgen im Blatt Daten gespeist.
- -
Christliche Feiertage werden nach Osterformel im abhängigkeit vom Jahr
berechnet und eingetragen
- - Resturlaubzählung
- Samstag
und Sonntag hervorgehoben
- Farbformatierungen für
Urlaubstage, Krankentage, Fakultativ: Anwesenheitstage
Tabellenblatt
2 Daten
- - Eingabe von Mitarbeitern
und Geburtstage - Daten werden automatisch in Plan eingetragen
- - Berechnung der
Urlaubstage nach TVÖD - kann natürlich angepasst werden, Nachname und
Geburtsdatum werden automatisch im Tabellenblatt 1 eingetragen
- - Sonderurlaub bzw. 5 Tage
für Behinderte können in vorgegebene Felder eingetragen werden und
werden in Rechnung berücksichtigt
- - Infoleiste mit bis 180
Einträgen, die automatisch im Plan nach Datum zugeordnet werden können:
Geburtsdatum und Feiertag oder Info möglich, dank raffinierter
S-Verweistechnologie (normalerweise ist nur ein Eintrag möglich!)
- - Möglichkeit der Eingabe
von Krankheitsblöcken vor dem 1. Januar für Krankentagegeld
Tabellenblatt
3 Rechnungen
- Berechnungen der
Urlaubstage nach TVÖD - kann hier leicht an Altersgruppen angepasst
werden.
- Controllerfunktion: Der
Kalender wurde in geschlossener Reihe wiederholt, um die Krankheitstage
ausrechnen zu können
- Februartrick für
Schaltjahre: Egal ob 28 oder 29 Tage - der Plan sollte immer
funktionieren!
- Berechnung der christlichen
Feiertage in Abhängigkeit vom Jahr des Planes
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:
- 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
- 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
- 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