Office: Urlaubsplanung betrachtet halbe Tage

Einen schönen Gruß an alle!
nach langer Suche muss ich eine extra Nachricht schreiben.
Habe folgendes Problem:
ich habe einen Kalender in dem ich die Urlaubsplanung mache. Jeder Tag hat rechts 3 leere Zellen die ich mit dem Kürzel der MA fülle. die Monate folgen senkrecht nach rechts.
Eine Zusammenfassung mit einer einfachen Suchfunktion (zählenden()) zählt wie oft jeder MA in Urlaub ist.
Der Betrieb entscheidet jedes Jahr an welche Tage nur 1/2 Tag gearbeitet wird.
Diese liste von 5-6 Tage ist in einem Bereich "half" in meinem Worksheet gespeichert.
Ich versuche seit Tagen eine Matrixfunktion zusammenzusetzen aber mit keinen Erfolg. Der kalkulierten Summe der Tage sollte die Anzahl der halben Tage indem der MA in Urlaub ist abgezogen werden.
Hat jemand eine Idee für mich? Danke
LG aus Südtirol
Marco

 

Hallo!

Meinst du es so?

Tabelle1

[TABLE]
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:87px;"><col style="width:91px;"><col style="width:80px;"><col style="width:93px;"><col style="width:160px;"></colgroup><tbody>[TR="bgcolor: #cacaca"]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Tage[/TD]
[TD]Feiertage[/TD]
[TD]halbe und ganze Feiertage[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]Fr 01.04.2011[/TD]
[TD="align: center"]Do 26.05.2011[/TD]
[TD="bgcolor: #ffcc99, align: right"]36,5[/TD]
[TD="align: right"]Do 14.04.2011[/TD]
[TD="align: center"]0,5[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]Mo 18.04.2011[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]Di 19.04.2011[/TD]
[TD="align: center"]0,5[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]Mo 02.05.2011[/TD]
[TD="align: center"]0,5[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]Mi 25.05.2011[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]Di 05.07.2011[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<tbody>[TR]
[TD]Formeln der Tabelle[/TD]
[/TR]
[TR]
[TD][TABLE]
<tbody>[TR="bgcolor: #cacaca"]
[TD]Zelle[/TD]
[TD]Formel[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(A3&":"&B3));2)<6)*1)-SUMMENPRODUKT(($D$3:$D$10>=A3)*($D$3:$D$10<=B3)*(WOCHENTAG($D$3:$D$10;2)<6)*($E$3:$E$10))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß
Wilfried

 

Danke für deine Antwort Wilfried.
die Tabelle schaut ungefähr so aus

Vielleicht ist das noch klarer.
Danke
LG Marco

 

hi Marco, du solltest besser (d)eine (beispiel)datei posten als ein bild... hier sieht man nämlich nicht, was in z.b. F3:F33 steht... sind das benutzerdefinierte datumswerte, oder einfach nur zahlen..?

 

Hallo Hiob! Du hast einfach recht.
ich versuche die Datei zu posten
Den Anhang Probe_Kalender.xlsx betrachten
Danke!
LG Marco

 

hi Marco, nächste frage: die mitarbeiterkürzel stehen doch hoffentlich seperat in den spalten und jeder hat eine eigene pro monat...? oder sehe ich das falsch..?

 

HiHiob!
ich habe die endgültige Tabelle etwas gekürzt. Die richtige hat das ganze Jahr und die Tage sind mit bedingter Formatierung gefärbt. Pro Woche habe ich 3 Spalten weil maximal 3 MA gleichzeitig in Urlaub sein können. Hoffe klar genug gewesen zu sein
LG Marco

 

Hallo Marco,

bezugnehmend auf Deine Beispieldatei mit den beiden Monaten (Januar, Februar), sähe die Formel in Zelle R3 (für Max Mustermann) wie folgt aus:
{=SUMMENPRODUKT(($G$3:$I$33=$Q3)*(1,5-WENNFEHLER(VERGLEICH($F$3:$F$33;$C$3:$C$7;0)>0;0,5)) )+SUMMENPRODUKT(($K$3:$M$30=$Q3)*(1,5-WENNFEHLER(VERGLEICH($J$3:$J$30;$C$3:$C$7;0)>0;0,5)) )}
die Formel in R4 (Henry Schmid):
{=SUMMENPRODUKT(($G$3:$I$33=$Q4)*(1,5-WENNFEHLER(VERGLEICH($F$3:$F$33;$C$3:$C$7;0)>0;0,5)) )+SUMMENPRODUKT(($K$3:$M$30=$Q4)*(1,5-WENNFEHLER(VERGLEICH($J$3:$J$30;$C$3:$C$7;0)>0;0,5)) )}
usw.
Wie man aus den beiden Formeln erkennen kann, braucht man für jeden Monat eine SUMMENPRODUKT(...)-Funktion. Also um 1 ganzes Jahr damit zu erfassen, braucht man die Summe von 12 SUMMENPRODUKT-Funktionen (statt der 2 in den vorgenannten Beispielen). Darüber hinaus muss jede dieser Formeln als Matrixformel eingegeben werden (bei Eingabe der Formel in der Formeleditierzeile darf diese Eingabe nicht mit der ENTER-Taste allein, sondern mit der Tastenkombination SHIFT-CTRL-ENTER = UMSCHALT-STRG-EINGABE abgeschlossen werden).

Diese Formeln sind deshalb so umfangreich (bzw. umständlich), weil
1) keine Zwischenresultate (Hilfsresultate) vorgesehen sind
2) die Anordnung des Datenbestandes nicht gerade vorteilhaft, was die Berechnung betrifft, vorgenommen wurde.

 

Danke Anton! die Formel funktioniert tadellos!
Mein Resultat habe ich erreicht, aber wie könnte ich die Tabelle noch leistungsvoller gestalten? Wenn das MA-Team größer wird zum Beispiel. Du hast von Zwischen- Hilfsresultate gesprochen. Wie sollten sie etwa aussehen?
Dieser Forum ist sehr gut konzipiert und bietet eine tolle Möglichkeit die eigene Arbeit und Kenntnisse produktiv zu fördern.
Danke.
Marco

 

Hallo Marco,

in der beiliegenden Excel-Datei habe ich Dir 3 benannte Zellbereiche angelegt:
1) Name "Half" für Bereich C3:C7 (Datumstabelle für die halben Urlaubstage)
2) Name "KalJahr" für Zelle C1 (Kalenderjahr der Daten für "Half")
3) Name "AbDatum" für Zelle E2 (Das Datum des ersten Monats/Jahres für die Urlaubsplanung in F2:BA33)

Das "AbDatum" wird verwendet, um im Bereich F2:F33 den ersten Monat+Jahr festzulegen. Ausgehend von diesem Monat werden noch weitere 11 Monate in den weiteren Spalten der Urlaubsplanung angezeigt. Dabei wird automatisch (Formeln+bedingte Formatierung) ermittelt, wie viele Tage die einzelnen Monate besitzen. Die überzähligen Tage eines Monats werden ohne Tageszahl mit grauem Hintergrund dargestellt.

Unterhalb des Urlaubskalendariums (F2:BA33) habe ich die monatlichen Abfragen (F35:BA38) der Urlaubstage für 4 Arbeitnehmer (D35:E38) dargestellt (MM,HS,RK,RF) und schließlich in BC35:BC38 die Gesamturlaubstage für obige 12 Monate für die 4 Arbeitnehmer.
Es wird also für jeden Arbeitnehmer eine monatliche Summe (mit nur einer SUMMENPRODUKT-Formel) erstellt und die so erstellten Zwischensummen zu einer Jahressumme addiert, wodurch sich eine starke Vereinfachung und Ermöglichung einer monatlichen Urlaubsdarstellung ergibt.

 

Hallo Anton,
ich bin wortlos. Ich bin sehr dankbar. Das ist viel viel mehr als ich am Anfang erwartet habe.
Danke nochmals.
Marco

 

Anton,
würde die Formel SUMMENPRODUKT in deinen Augen auch die Unterscheidung zwischen Mitarbeiter Groß geschrieben (1 Tag Urlaub) und Mitarbeiter klein geschrieben (1/2 Tag Urlaub) managen?

Könnte man zB die Formel istzahl(finden(klein(E35);G3:G33)))/2) in der Matrixfunktion einbetten?


LG Marco

 

Hallo Experten!
zur Formel: in meinem Beispiel hat Anton ein * zwischen den 2 Argumenten der Formel Summenprodukt(). Warum nicht ein +? die Formel sucht den Treffer unabhängig ob Groß- kleingeschrieben; könnte man die Suche auch "case sensitive" machen?

Die Formel: Summenprodukt(((istzahl(Finden($C35;E3:G33)))+(istzahl(finden(klein($C35);E3:G33)))/2)) funktioniert aber ich kann den Teil *(1,5-wennfehler(Vergleich(c$3:c$33;Half;0)>0;0,5)) einfach nicht hinzufügen: wird nicht berechnet.
Ist das möglich für euch?
Jede Idee ist wertvoll
LG Marco

 

Hallo Marco,

in der beiliegenden Excel-Datei habe ich Dir ein weiteres Arbeitsblatt "Folio2" eingefügt, in dem ich die Formeln unterhalb der Monate geändert habe, sodass sie jetzt auf Groß/Kleinschreibung reagieren.
Dies habe ich erreicht, indem ich die FINDEN-Funktion verwendet habe anstelle des gewöhnlichen Zeichenkettenvergleiches. Da aber die FINDEN-Funktion außer der Groß/Kleinschreibung nach Teilzeichenketten sucht, musste ich zusätzlich in die SUMMENPRODUKT-Formel einen weiteren Multiplikationsausdruck aufnehmen, der die Länge der Zeichenketten vergleicht.

(Anmerkung: Da eine Multiplikation etwas anderes bewirkt als eine Addition, darf man nicht einfach ein "*" durch ein "+" ersetzen).

 

Hi Anton!
habe probiert aber bei mir funktioniert nicht. Könntest du mir bitte näher erklären warum du die Formel Länge() verwendet hast? Es geht ja um groß-kleingeschrieben. bei mir werden die Tage nur wenn grossgeschrieben gerechnet und die Feiertage als halbe Feiertage. bei kleinschreiben werden die Tage gar nicht betrachtet, sei es voll sowie halbe.
danke für die Zeit die du mir widmest.
LG Marco