PDA

Vollständige Version anzeigen : einen Arbeitsturnus in einen dynamischen Excel Kalender eintragen


philippedv
20.08.2017, 15:01
Hallo,

ich erstelle gerade eine Übersicht in einem Excel Kalender.
Folgende Bedingungen möchte ich ohne VBA erfüllen:
Der Kalender soll sich über ein Rollfeld an weitere Jahre automatisch anpassen (gelöst)
Samstag und Sonntag sollen dargestellt werden (gelöst)
Es gibt allerdings eine Turnusserie für Termine am
Montag, Dienstag, Mittwoch, Donnerstag, Freitag und Samstag, sowie den Einsatz von Hilfen 2x in der Woche, 3x in der Woche, 4x in der Woche, usw.

Arbeitsbeginn ist immer der Wochenbeginn, also Montag

Excel sollte über den Turnus in Spalte G im Kalender den Wert an der richtigen Stelle platzieren ( siehe Tabelle 2)
Wenn das Jahr über das Rollfeld in Zelle H1 verändert wird, dann sollen sich die Werte im Kalender entsprechend anpassen.

Ist das überhaupt ohne VBA möglich? Ich habe mich jetzt längere Zeit mit diesem Problem beschäftigt; aber habe keine Lösung gefunden.

Herzlichen Dank schon mal für eure Hilfe

Herzliche Grüße
Thomas

Anbei eine Beispieldatei

Hajo_Zi
20.08.2017, 15:15
Hallo Thomas,

ich habe mal nur die erste Bedingung gemacht. Die anderen analog.
<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11px; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /><col style="width:15px;" /></colgroup><tr style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td><td>P</td><td>Q</td><td>R</td><td>S</td><td>T</td><td>U</td><td>V</td></tr><tr><td style="background-color:#99CCFF; font-size:11px; text-align:center; font-weight:bold; ">11</td><td style="; color:#000000; border-color:#000000; font-size:11px; background-color:#FFFFCC ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; font-size:11px; background-color:#808080 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000 ;; text-align:left; ">&nbsp;</td><td style="; color:#000000 ;; text-align:left; ">&nbsp;</td><td style="; color:#000000 ;; text-align:left; ">&nbsp;</td><td style="; color:#000000 ;; text-align:left; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; font-size:11px; background-color:#FFFFCC ;; text-align:left; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; font-size:11px; background-color:#FFFFCC ;; text-align:left; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; font-size:11px; background-color:#808080 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000 ;; text-align:right; ">&nbsp;</td></tr></table><b>die bedingte Formatierung Fülleffekte, Symbole, Datenbalken</b><br/><b>werden in dieser Tabelle nicht dargestellt</b><br/><b>Schriftart wird in dieser Tabelle nicht dargestellt</b><br/><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11px; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td><td>N/A</td></tr><tr><td>L11:Q11</td><td>=WENN(L4="Mo";abc;"")</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11px; width:800px; background-color:#ffffff; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="11" > Bedingte Formatierung Haupttabelle 1 </td><td>&nbsp;</td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td > Wird angewendet auf &nbsp; </td><td> Nr. Bed. *Regeltyp&nbsp; </td><td> Operator &nbsp; </td><td> Formel1 &nbsp; </td><td> Formel2 &nbsp; </td><td> Format Schrift Füll-farbe &nbsp; </td><td> Unterstrichen &nbsp; </td><td> Schrift- farbe </td><td> Muster &nbsp; </td><td> Muster-farbe </td><td> Typ </td><td> Bereich </td></tr><tr><td>$J$11:$AN$11</td><td style="text-align:left; ">01.Bed.: Formel ist</td><td>&nbsp;</td><td style="text-align:left; ">=UND(ZEILE()=11;WOCHENTAG(J$5;2)=1)&nbsp;</td><td>&nbsp;</td><td style="background-color:#808080; ;text-align:right " >8421504</td><td style="text-align:left; "> &nbsp; </td><td>&nbsp;</td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">$J$11:$AN$11</td></tr><tr><td>$J$4:$AN$5,$J$11:$AN$22</td><td style="text-align:left; ">02.Bed.: Formel ist, Füllfarbe</td><td>&nbsp;</td><td style="text-align:left; ">=SVERWEIS(J$5;'C:UsershpDesktop[Raumbuch 01_Vers_mit_Dienstplan.xlsb]Tabelle der Feiertage'!#BEZUG!;1;0)&nbsp;</td><td>&nbsp;</td><td style=";text-align:left " > Test</td><td style="text-align:left; "> &nbsp; </td><td>&nbsp;</td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">$J$4:$AN$5,$J$11:$AN$22</td></tr></table><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11px; width:800px; background-color:#ffffff; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="5" > Bedingte Formatierung Haupttabelle 2 </td><td colspan="3" > oberere/unterer Bereich </td><td> &nbsp; </td><td>&nbsp;</td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td> Wird angewendet auf &nbsp; </td><td> Nr. Bed.&nbsp;</td><td> Format Zelle </td><td> Anhalten </td><td> Auswahl &nbsp; </td><td> Anzeige &nbsp; </td><td> Anzahl &nbsp; </td><td> Durchschnitt &nbsp; </td><td> Typ &nbsp; </td><td> Bereich </td></tr><tr><td>$J$11:$AN$11</td><td>01.Bed.</td><td> &nbsp; </td><td style="text-align:left; ">Falsch&nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">$J$11:$AN$11</td></tr><tr><td>$J$4:$AN$5,$J$11:$AN$22</td><td>02.Bed.</td><td> &nbsp; </td><td style="text-align:left; ">Falsch&nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">$J$4:$AN$5,$J$11:$AN$22</td></tr><tr><td>$J$4:$AN$7,$J$11:$AN$22</td><td>03.Bed.</td><td> &nbsp; </td><td style="text-align:left; ">Falsch&nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">$J$4:$AN$7,$J$11:$AN$22</td></tr></table><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11px; width:800px; background-color:#ffffff; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="8" > Füllfarbe </td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td > Wird angewendet auf &nbsp; </td><td> Nr. Bed. &nbsp; </td><td> Art &nbsp; </td><td> Richtung &nbsp; </td><td>1. Farbe &nbsp; </td><td> 2. Farbe &nbsp; </td><td> 3. Farbe &nbsp; </td><td> Bereich </td></tr><tr><td>$J$4:$AN$5,$J$11:$AN$22</td><td>02.Bed.</td><td>Aus der Mitte</td><td></td><td style="text-align:right; background-color:#FFFFCC;" >13434879&nbsp; </td><td style="text-align:right; background-color:#00FFFF;" >16776960&nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">$J$4:$AN$5,$J$11:$AN$22</td></tr></table><b>Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.</b><br/><br/><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000;border-width: 1px; font-size:11px; width:800px;background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCCCCC; text-align:center; font-weight:bold; "><td colspan="9" > Schriftformate </td><td>&nbsp;</td></tr><tr valign="top" style="background-color:#CCCCCC; text-align:center; font-weight:bold; "><td> Zelle </td><td> Rot </td><td> Grün </td><td> Blau </td><td> Color </td><td> Stil </td><td> Unterstreichung </td><td> Effekte </td><td> Durchgestrichen </td><td> Schriftart </td></tr><tr><td>J11:V11 </td><td style="text-align:right; ">0&nbsp;</td><td style="text-align:right; ">0&nbsp;</td><td style="text-align:right; ">0&nbsp;</td><td style="text-align:right; background-color:#000000">0&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; "> &nbsp; &nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">&nbsp;</td><td style="text-align:left; ">Calibri&nbsp;</td></tr></table><b>Zellen mit Schriftformatierung automatisch werden nicht dargestellt</b><br/><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 25.12 einschl. 64 Bit</td></tr></table><br/>

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

lupo1
20.08.2017, 15:28
J11: =N(TEXT(REST(J$5;7);"TTTT")=$G11)+WENNFEHLER(TEIL("000101000101010011011001111110111111111111";($G11-2)*7+REST(J$5;7)+1;1);)

mit "x";; benutzerdefiniert formatieren

philippedv
20.08.2017, 16:28
Hallo Lupo,

die Funktion funktioniert in der Beispieldatei; im wahren Leben habe ich allerdings mehr Zeiträume definiert.

Ich habe versucht, die Formel nachzuvollziehen, bin aber dazu nicht in der Lage. Könntest du ein paar Erklärungen geben; vor allem
=N(TEXT(REST(J$5;7);"TTTT")=$G11)+WENNFEHLER(TEIL("000101000101010011011001111110111111111111";($G11-2)*7+REST(J$5;7)+1;1);)

Herzliche Grüße

Thomas

lupo1
20.08.2017, 16:58
Das sind die von Dir gesetzten Kreuze von Sa bis So (7 Tage), für 6 Auswahlen, 1=x, 0=nix, einfach nebeneinander, und dann mit dem Ausdruck rechts davon rausgepickt.

Wenn es mehr Auswahlen gibt, lagert man diese Muster besser in eine Tabelle aus.

J11: =--TEIL(SVERWEIS($G11;$A$1:$B$12;2;);REST(J$5;7)+1;1)

A1[:B12]:
2 0001010
3 0010101
4 0011011
5 0011111
6 1011111
7 1111111
MONTAG 0010000
DIENSTAG 0001000
MITTWOCH 0000100
DONNERSTAG 0000010
FREITAG 0000001
SAMSTAG 1000000

Ich denke, ab jetzt kommst Du allein weiter.