Office: (Office 2010) Abwesenheitsliste in Kalenderform

Hallo zusammen,

zur besseren Visualisierung will ich Ausfallzeitzen wie Urlaub, Krankheit, Weiterbildung etc. gerne in Form einer Zeitleiste im Kalenderformat darstellen. Jeder Ausfallgrund bekommt eine bestimmte Farbe und wird dann entsprechend den Zeitraum in den Kalender übertragen.

Siehe Bild:


Einen funktionierenden Kalender habe ich mir schon gebaut und mit den Funktionen zur Übernehme der Rohdaten in das Kalenderformat beschäftige ich mich jetzt schon einige Zeit komme aber nicht voran. Es will mir einfach nicht die verschiedenen Gründe mit den hinterlegten Zeiten (von - bis) in den Kalender übernehmen.

Ich bitte euch daher um Hilfe, mir den nötigen Denkanstoß in die richtige Richtung zu geben da ich Exceltechnisch leider am Ende mit meinen Latein bin :-/

Hier zum Verständnis die Datei:
Den Anhang Kalender 2015 Vorlage .xlsx betrachten

Aufbau ist recht simpel. Ein Tabellenblatt hat die fest Formatierten Rohdaten, dass andere Tabellenblatt soll die Ausfallzeiten einfach nur ähnlich wie eine Zeitleiste in unterschiedlichen Farben im Kalenderformat darstellen.

Würde mich sehr freuen wenn es sich mal jemand anschauen kann....meine Formeln funktionieren einfach nicht....sind evtl auch einfach zu billig geschrieben.

Vielen Dank im voraus.


Florian

 

Hallo Florian,

in der beiliegenden Excel-Datei habe ich ein paar Änderungen durchgeführt:
1) Im Arbeitsblatt "OPEN-Ausdruck" habe ich in Spalte O "Kal.Tage" eine einfache Formel eingebaut.
2) Im Arbeitsblatt "Kalender 2015" habe ich mehrere Änderungen vorgenommen:

• In der Kopfzeile mit der Monatsbeschriftung eine Datumsformel
• In den ausgeblendeten Spalten jeweils eine Datumsformel für die Tagesermittlung, die von der Kopfzeilenformel ausgeht.
• Ferner habe ich eine Lösung für das Problem eingebaut, falls ein 29.2. anfällt oder aber eben kein solcher enthalten ist.
• Deine zentrale Formellösung für die weißen Monatsspalten habe ich geändert - die Formel ist jetzt sogar kürzer: Z.B. steht jetzt in Zelle D6 (für 1.1.): =WENNFEHLER(INDEX('OPEN-Ausdruck'!$R$2:$R$281;SUMMENPRODUKT(('OPEN-Ausdruck'!$M$2:$M$281<=C6)*(C6<='OPEN-Ausdruck'!$N$2:$N$281)*ZEILE($2:$281))-1);"")

Die Funktionsweise der blauen Formel ist relativ einfach:
1) In C6 steht das Datum, nach dem im Arbeitsblatt "OPEN-Ausdruck" gesucht wird.
2) Mit der Formel SUMMENPRODUKT(('OPEN-Ausdruck'!$M$2:$M$281<=C6)*(C6<='OPEN-Ausdruck'!$N$2:$N$281)*ZEILE($2:$281)) wird die Nummer der Zeile ermittelt, falls das Datum aus C6 in einem der Datumsintervalle der Spalten M und N steht.
3) Dann wird mit der Formel INDEX('OPEN-Ausdruck'!$R$2:$R$281;ZeilenNr-1) aus der Spalte R der Eintrag in der Zeile mit Nummer ZeilenNr geholt.
4) Das wäre aber auch schon alles, wäre da nicht der Fall zu lösen, dass keine Zeilennummer gefunden wurde, weil der Tag in keinem Datumsintervall der Spalten M und N enthalten ist: Formel: =WENNFEHLER("KeinEintragGefunden";""): Wenn keine Zeile gefunden werden konnte, gib eine leere Zeichenkette "" zurück.

 

Hallo Anton,

vorab schon mal tausend Dank für deine geniale Lösung! Versuche gerade deine Formel zu verstehen, damit ich mir künftig auch selber helfen kann.
Das es mit einer Index Formel irgendwie funktionieren müsste war ich mir beinahe sicher, nur bin ich auf dem Gebiet echt noch hinterm Mond....

Bin noch mitten im verstehen aber mir brennt da direkt noch etwas unter den Nägel.

1) Wenn du auf den 2. - 3. März schaust, findest du da eine "0" wie darf ich diese verstehen? Bzw. wie könnte ich das Problem umgehen wenn Ausfallgrund ein Wochenende mit einschließt. Das Wochenende gilt ja als Freizeit und daher sollte dort kein Ausfall angezeigt werden.

2) In der finalen Form gibt es etwa 30 verschiedene Ausfallgründe die wiederum den 5 verschiedenen Gruppen in den Feldern AF1 - AL4 zugeordnet werden. Bisher scheint es ja als bedingte Formatierung gelöst zu sein. Wäre es aber Möglich, dass wenn im Tabellenblatt "OPEN-Ausdruck" in Spalte "R" Erholungsurlaub steht im Tabellenblatt "Kalender 2015" nur "EU" (wie in den 5 Gruppen "AF1 - AL4) angezeigt wird?
Ginge das auch über bedingte Formatierungen oder müsste da eine zusätzliche Wenn Funktionen eingebaut werden?

Vielen Vielen Dank für deine Zeit und Hilfe!

Florian

 

Hallo Florian,

Das ist nur auf eine kleine Schlamperei meinerseits zurückzuführen: Ich hatte nämlich selber mehrere Zeiträume getestet und dazu im Arbeitsblatt "OPEN-Ausdruck" ab Zeile 16 in den Spalten M und N Datumsintervalle eingegeben, die ich alle vor dem Hochladen der Datei löschte, bis auf den Eintrag in Zeile 16 (Zellen M16 und N16) - diesen habe ich leider vergessen zu löschen. Dort befindet sich in N16 als 'Bis-Datum' der 3.3.2013.
Somit liefert die SUMMENPRODUKT-Formel für den 2.3. und für 3.3.2013 die Zeilennummer 16.
Wenn du weiter oben nachsiehst, findest du in Zeile 7 in M7 und N7 das Intervall 2.3.2013 bis 9.3.2013. Da die beiden Tage 2.3.+3.3.2013 auch in dieses Tagesintervall fallen, lieferte die SUMMENPRODUKT-Formel auch die Zeilennummer 7 als zutreffende Zeile zurück. Da diese Formel, wie ihr Name bereits sagt, Summen bildet, lieferte die SUMMENPRODUKT-Funktion die Summe aus 16+7 = 23 als quasi gefundene Zeilennummer zurück - nur in Zeile 23 von Spalte R (also Zelle R23) ist nichts enthalten, wobei die INDEX-Funktion aus dem Leer-Wert eine 0 daraus machte.

Wenn du also diese Zeile 16 entfernst, verschwinden auch die Nuller im 2.3.+3.3.2013 (Zellen 'Kalender 2015'!J7:J8). Da es keine überlappenden bzw. doppelten Tagesintervalle im Arbeitsblatt 'OPEN-Ausdruck' geben darf/kann, sollte diese Art von Fehler künftig nicht auftreten.

Damit an Samstagen/Sonntagen kein Eintrag angezeigt wird, auch wenn solche Tage mitten in den Von-Bis-Intervallen des Arbeitsblattes 'OPEN-Ausdruck' liegen, habe ich in der Formel einen kleinen Zusatz zur Zeilennummern-Ermittlung hinzugefügt (rot):
=WENNFEHLER(INDEX('OPEN-Ausdruck'!$R$2:$R$281;SUMMENPRODUKT(('OPEN-Ausdruck'!$M$2:$M$281<=C6)*(C6<='OPEN-Ausdruck'!$N$2:$N$281)*ZEILE($2:$281))*(REST(C6;7)>1)-1);"")
Die Formel liefert, falls es sich um einen Samstag oder Sonntag handelt, nicht die Zeilennummer an die INDEX-Funktion, sondern die Zeile 0. Da davon noch die 1 subtrahiert wird, führt dies zu einer Zeilennummer von -1, die an die INDEX-Funktion übergeben wird, und das wiederum erzeugt in der INDEX-Funktion einen Fehler, der bewirkt, dass die WENNFEHLER-Funktion die leere Zeichenkette "" als Resultat zurückgibt.

Das ist möglich. Ich habe dir das ins bedingte Format für den "Erholungsurlaub" eingebaut. Wenn du dir das Zahlenformat für den bedingten Formelwert Zellwert = "Erholungsurlaub" ansiehst, wirst du, wenn du die Kategorie "Benutzerdefiniert" auswählst, folgenden Eintrag finden: ";;;EU". Wie du wissen wirst, kann man jeder Zelle gleichzeitig 4 Formate zuordnen, die durch Strichpunkte zu trennen sind:
Das 1.Format bezieht sich auf Zahlen >0, das 2.Format auf Zahlen <0, das 3.Format auf Zahlen =0 und das 4.Format, das für uns wichtig ist, auf Zeichenkettenwerte. Da Zahlenformate für uns in diesem Fall bedeutungslos sind, sind deshalb zuerst 3 Strichpunkte enthalten und dann als 4.Format 'EU'. Ist also in der bedingt formatierten Zelle der Wert "Erholungsurlaub" enthalten, wir statt dieses Wertes "EU" angezeigt.

Wenn du im Arbeitsblatt "OPEN-Ausdruck" 30 verschiedene Ausfallgründe verwenden möchtest, die aber im Arbeitsblatt "Kalender 2015" nur als 5 verschiedene Einträge angezeigt werden sollen, musst du irgendwo in einem Arbeitsblatt (am besten in "Kalender 2015" selbst) eine Umschlüsselungstabelle einbauen, bestehend aus 2 Spalten:
In der 1.Spalte sind deine 30 Ausfallgründe enthalten, in der 2.Spalte - jeweils korrekt zugeordnet - deine 5 Anzeigegründe. Mittels einer SVERWEIS-Funktion. die du in die bisher erstellte Anzeigefunktion zwischen der WENNFEHLER-Funktion und der INDEX-Funktion einfügst, erreichst du diese Abbildung:
=WENNFEHLER(SVERWEIS(INDEX(.....);UmschlüsselungsTabelle;2;FALSCH);"")
Statt UmschlüsselungsTabelle musst du den Zellbereich dieser Tabelle angeben, zB. AQ1:AR30, falls sie sich dort befindet.

 

Hallo Anton,

habe das jetzt mal alles nachvollzogen und selbst etwas mit den Formeln gespielt um zu verstehen wie du da logisch vorgehst! Richtig genial gelöst die Problemstellung! Tausend Dank dafür!
Taste mich da gerade langsam ran und baue soweit ich kann selbst weiter. Nutze noch sehr viel deine Formeln und variiere dann entsprechend der neuen Aufgabenstellung. Danke aber für die (sogar für mich) nachvollziehbare Erklärung zu den einzelnen Funktionen. Hilfe zur Selbsthilfe klappt dabei richtig gut :-)

Dir einen schönen Abend


Grüße Florian

 

Servus ihr Zwei,

folge den Thread meines Namensvetters gerade mit Spannung weil ich beinahe etwas identisches baue :-) War wohl Gedankenübertragung :-)

@Exl, wenn ich dich auch etwas fragen dürfte?

Diese Formel:

Rein zum Verständnis: Das reine Ausblenden des Sonntages würde dann wie realisiert werden können?

Richtig interessant wäre jetzt allerdings die "Zentrale Formel"

Diese fragt ja eigentlich nur den von Postmann geforderten Abwesenheitsgrund einer Person ab, oder? Was wäre aber wenn mehrere Personen in der Daten Tabelle (Open-Druck) vorkommen und ich je nachdem welchen Namen ich im Kalender bei D1 & N1 eingebe nur die jeweiligen Abwesenheitsgründe der gewünschten Person angezeigt bekomme?

Könnte das vielleicht an dem Beispiel erklärt werden? Das wäre dann genau die Lösung die ich brauchen würde um meinen Lehrplan zu kompletieren.

Viele Grüße und bis bald

 

Hallo @Florian87,

Code:

[COLOR="#A52A2A"]=WENNFEHLER(
[COLOR="#00CC00"]   INDEX('OPEN-Ausdruck'!$R$2:$R$281;
      [COLOR="#0000FF"]SUMMENPRODUKT(('OPEN-Ausdruck'!$M$2:$M$281<=C6)*(C6<='OPEN-Ausdruck'!$N$2:$N$281)*ZEILE($2:$281))[/COLOR]*
      [COLOR="#0000CC"][b](REST(C6;7)>1)[/b][/COLOR]
   -1)
[/COLOR];"")
[/COLOR]

Vorgenannte Formel besitzt 3 Ebenen (Farben: 3.Braun / 2.Grün / 1. a)Blau + b)BlauFett).

1. a) Blau enthält die SUMMENPRODUKT-Funktion. Diese ist geeignet, Arrays (=Tabellenspalten-Bereiche) zu verarbeiten. Sie bildet pro Tabellenzeile je 1 Zahlenwert und addiert diese so ermittelten Zahlenwerte der Tabellenzeilen.
   In diesem Fall werden pro Tabellenzeile 3 Zahlen gebildet, die miteinander multipliziert werden und so den Zahlenwert je Tabellenzeile ergeben:
   ('OPEN-Ausdruck'!$M$2:$M$281<=C6)
   Hier wird das Kalenderdatum aus C6 mit dem VonDatum (Datum der momentanen Zeile der Spalte M der Tabelle): Falls das Kalenderdatum größer/gleich dem VonDatum ist, wird ein logisches WAHR (Numerisch =1) ermittelt, sonst ein logisches FALSCH (Numerisch =0).
   (C6<='OPEN-Ausdruck'!$N$2:$N$281)
   Hier wird das Kalenderdatum aus C6 mit dem BisDatum (Datum der momentanen Zeile der Spalte N der Tabelle): Falls das Kalenderdatum kleiner/gleich dem BisDatum ist, wird ein logisches WAHR (Numerisch =1) ermittelt, sonst ein logisches FALSCH (Numerisch =0).
   ZEILE($2:$281)
   Enthält ein konstantes Array und liefert zu jeder Tabellenzeile die momentane Zeilennummer (ZeileNr. 2 bis ZeileNr. 281).
   Das Multiplikationsergebnis (= Zahl je Tabellenzeile) ist entweder 0 oder die Zeilennummer: Die Zeilennummer erhält man genau dann, wenn das Kalenderdatum (C6) im Datumsintervall (VonDatum, BisDatum) liegt; in allen anderen Fällen ergibt sich eine 0.
   '
   Da aufgrund der Aufgabenstellung das Kalenderdatum (C6) höchstens in einer einzigen Tabellenzeile im VonDatum/BisDatum-Intervall liegen darf, liefert somit die SUMMENPRODUKT-Funktion als Ergebnis die Zeilennummer der Tabellenzeile, in der das zu testende Kalenderdatum (C6) im Datumsintervall liegt. Liegt C6 in keinem Datumsintervall der Tabellenzeilen, liefert die SUMMENPRODUKT-Funktion die Zahl 0 zurück.
   b) BlauFett
   Dieser Formelteil ist zuständig für das Testen des Wochentags des Kalenderdatums (Zelle C6): Er enthält die REST-Funktion, der das Kalenderdatum und die Zahl 7 übergeben werden. Die REST-Funktion führt eine Division Kalenderdatum/7 durch. Das Ergebnis dieser Division wird nur ganzzahlig ermittelt (also ohne Nachkommastellen, falls 7 nicht in Kalenderdatum enthalten ist). Es bleibt somit ein Rest im Bereich 0 bis 6. Dabei erhält man, falls Kalenderdatum ein Samstag ist, als Rest eine 0, falls ein Sonntag vorliegt, als Rest eine 1.
   Somit liefert dieser Formelteil genau dann den logischen Wert FALSCH (numerisch =0), falls im Kalenderdatum von Zelle C6 ein Samstag oder ein Sonntag enthalten ist; in allen übrigen Fällen (Montag bis Freitag) erhält man den logischen Wert WAHR (numerisch =1).
   '
   Beide blaue Formelteile werden schließlich multipliziert. Dabei erhält man genau dann eine Zahl >0, falls das Kalenderdatum (C6) in einem der Datumsintervalle der Tabelle liegt und es sich beim Kalenderdatum um einen Montag..Freitag handelt; in allen anderen Fällen erhält man ein 0. Falls die Ergebniszahl >0 ist, so ist dies die Zeilennummer der Tabelle, in deren Datumsintervall das Kalenderdatum enthalten ist.
2. Grün
   Die INDEX-Funktion enthält als 1. Parameter den Spaltenbereich der Tabelle, aus der mit Hilfe einer bestimmten Zeilennummer ein Zellwert abgeholt wird. Dabei wird aber die Zeilennummer, die mit den blauen Formeln ermittelt wurde, um 1 vermindert, weil der Spaltenbereich des 1.Parameters erst in der 2. Zeile des Arbeitsblattes beginnt.
   Dieser Formelteil liefert also im Falle einer gefundenen Zeile den gesuchten Text aus der richtigen Zeile der Spalte R der Tabelle.
   Ergab das Ergebnis der blauen Formeln jedoch eine 0, so wird hier im gründen Teil eine -1 daraus. Da es eine (-1).Zeile in der Suchspalte der INDEX-Funktion nicht geben kann, wird ein Fehler erzeugt.
3. Braun
   Hier wird überprüft, ob im blauen oder grünen Formelabschnitt ein Fehler aufgetreten ist. Falls dies der Fall ist, wird eine leere Zeichenkette "" zurückgegeben, ansonsten der gefundene Text.

Ich hoffe, ich konnte damit die Funktion obiger Formel detailiert genug darstellen.

Aufgrund des vorher Gesagten müsste klar sein, wie dies umzusetzen wäre: im 1.blauen Formelteil muss ein weiterer Multiplikationsfaktor hinzugefügt werden:

Code:

SUMMENPRODUKT(('OPEN-Ausdruck'!$M$2:$M$281<=C6)*(C6<='OPEN-Ausdruck'!$N$2:$N$281)*('OPEN-Ausdruck'!$A$2:$A$281=D1&N1)*ZEILE($2:$281))

Dabei habe ich angenommen, dass die Zeichenkette D1&N1 mittels Spalte A der Datentabelle getestet werden kann, um so die zutreffende Zeile in der Datentabelle zu finden.

 

Guten Morgen Exl,

also ich hab das jetzt einfach mal anhand des obigen Beispiels versucht nachzuvollziehen.

Code:

/=WENNFEHLER(INDEX('OPEN-Ausdruck'!$R$2:$R$281;SUMMENPRODUKT(('OPEN-Ausdruck'!$M$2:$M$281<=C6)*(C6<='OPEN-Ausdruck'!$N$2:$N$281)*('OPEN-Ausdruck'!$T$2:$T$281=D1&N1)*ZEILE($2:$281))*(REST(C6;7)>1)-1);"")

Dabei habe ich mit der Formel in Spalte T den aus Spalte K & J zusammengesetzten Namen abgefragt

Code:

=Verketten(K2;J2)

Ergebnis dieser Abfrage ist dann im Beispiel "MustermannMax" (ohne Leerzeichen zwischen Vor- und Nachname)

Zum Erfolg hat mich das erstmal nicht geführt. Also habe ich versucht die Formel und die Eingabe zu vereinfachen. Habe also entweder nur nach den Nachnamen gefragt oder eben durch Vor- und Nachnamen der in einer Zelle zusammen stand.

Code:

('OPEN-Ausdruck'!$J$2:$J$281=D1)*

Beides hat leider auch nicht zum gewünschten Ergebnis geführt.

Entweder habe ich also die Formel nicht verstanden oder ich mache grundlegend etwas falsch. Komme nur leider nicht darauf wie ich die Sache angehen muss damit es klappt.

Könntest du mir dieses kleine Problem nochmals zeigen sodass es im obigen Beispiel klappt? Das Umbauen auf meine Bedürfnisse sollte ich dann irgendwie hinbekommen.

Einen schönen Tag dir.

MFG