Das Clever-Excel-Forum.de - Treffen
... 14.-16. September 2018 im Allgäu ...

Hilfe beim Dienstplan
#1
Hallo,

ich habe eine Frage zu meinem Dienstplan (siehe Link 1).
http://imgur.com/vL9yzNe

Ich würde gerne die Zeile 3 von C bis G mit einem Wert von 11 (Stunden) festlegen.
Die Zeile 4 von B bis G mit 6 Stunden.
Die Zeile 5 von B bis G mit 9 Stunden.
Die Zeile 6 von B bis G mit 8 Stunden.
Die Zeile 8 von B bis G mit 9 Stunden.
Und nur die Zeile 3 B mit 11,5 Stunden.

Wir haben für die Tabelle die Namen an der Seite so verlinkt, dass sie über einen Reiter für jede Zelle auszuwählen sind. ( siehe Link 2)
http://imgur.com/5K0XICS

Wenn ich dann den Arbeitsplan fertig mit allen Namen eingetragen habe, würde ich gerne für den jeweiligen Namen (BSP: Eva) die gesamte Stundenanzahl, die ja hinter der jeweiligen Zelle hinterlegt worden ist, wie oben beschrieben, angezeigt bekommen. Am besten würden die Stunden dann in Spalte J hinter dem jeweiligen Namen angezeigt werden.

Meine Frage:
Kann ich Zellen bestimmten Werten (Stunden) zuordnen und wenn ja wie geht das?
Kann ich am Ende der Arbeitsplanerstellung einem Namen die Stunden zuordnen bzw. ausrechnen lassen, so dass ich von dem Monat die gesamte Stundenanzahl für eine Person angezeigt bekomme?

Vielen Dank für Tipps und Antworten!
to top
#2
Hi Susanne,

Bilder sind nicht hilfreich. Schon gar nicht über einen Freehoster. Lies dir bitte mal diese beiden Links durch:

http://www.clever-excel-forum.de/Thread-...einstellen
http://www.clever-excel-forum.de/Thread-...ng-stellen

Aus deiner Beschreibung ist für mich nicht ersichtlich, was du wo haben willst. Erstelle bitte eine Mustertabelle mit einem händisch eingetragenen Wunschergebnis. Oder beschreibe dein Problem so, dass auch ein Nichteingeweihter damit klar kommt.

Ist jede Zelle innerhalb deines Wochenplans mit einem DropDown versehen oder nur B3?
Was meinst du mit Stunden in Zeile hinterlegen?
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#3
Hi Susanne,

einen Versuch starte ich auch ohne Mustertabelle oder detailliertere Beschreibung. Hoffe, dein Problem erkannt zu haben.

Füge neben jedem Tag eine Hilfsspalte ein (farbig markiert), in der du die Stunden hinterlegst. Die Hilfsspalten kannst du ausblenden - dann stören sie nicht mehr.

Tabelle3

ABCDEFGHIJKLMNOP
2MontagDienstagMittwochDonnerstagFreitagSamstagMeier18
39:00-20:30Müller11,5Vogel11Müller11Vogel11Vogel11Schwarz11Müller66,5
49:00-15:00Huber6Schwarz6Huber6Schwarz6Schwarz6Braun6Huber21
Formeln der Tabelle
ZelleFormel
P2=SUMMENPRODUKT(($C$3:$C$8)*($B$3:$B$8=$O2)+($E$3:$E$8)*($D$3:$D$8=$O2)+($G$3:$G$8)*($F$3:$F$8=$O2)+($I$3:$I$8)*($H$3:$H$8=$O2)+($K$3:$K$8)*($J$3:$J$8=$O2)+($M$3:$M$8)*($L$3:$L$8=$O2))
P3=SUMMENPRODUKT(($C$3:$C$8)*($B$3:$B$8=$O3)+($E$3:$E$8)*($D$3:$D$8=$O3)+($G$3:$G$8)*($F$3:$F$8=$O3)+($I$3:$I$8)*($H$3:$H$8=$O3)+($K$3:$K$8)*($J$3:$J$8=$O3)+($M$3:$M$8)*($L$3:$L$8=$O3))
P4=SUMMENPRODUKT(($C$3:$C$8)*($B$3:$B$8=$O4)+($E$3:$E$8)*($D$3:$D$8=$O4)+($G$3:$G$8)*($F$3:$F$8=$O4)+($I$3:$I$8)*($H$3:$H$8=$O4)+($K$3:$K$8)*($J$3:$J$8=$O4)+($M$3:$M$8)*($L$3:$L$8=$O4))
Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
A2Liste=#BEZUG!
B3Liste=$O$2:$O$10
D3Liste=$O$2:$O$10
F3Liste=$O$2:$O$10
H3Liste=$O$2:$O$10
J3Liste=$O$2:$O$10
L3Liste=$O$2:$O$10
B4Liste=$O$2:$O$10
D4Liste=$O$2:$O$10
F4Liste=$O$2:$O$10
H4Liste=$O$2:$O$10
J4Liste=$O$2:$O$10
L4Liste=$O$2:$O$10

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#4
Hallo,

das geht auch einfacher:

Arbeitsblatt mit dem Namen 'Tabelle1'
 BCDEFGHIJ
3EvaEvaCarla Carla 11Eva36,5
4  Eva   6Carla49
5Carla     9  
6   Eva  8  
7         
8 Carla Carla  9  

ZelleFormel
J3=SUMMENPRODUKT(($B$3:$G$8=I3)*((ZEILE($B$3:$G$8)=3)*11+(ZEILE($B$3:$G$8)=4)*6+(ZEILE($B$3:$G$8)=5)*9+(ZEILE($B$3:$G$8)=6)*8+(ZEILE($B$3:$G$8)=8)*9))+($B$3=$I3)*0,5
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg


Die Stunden könnte man auch in eine eigene Übersicht packen:

Arbeitsblatt mit dem Namen 'Tabelle1'
 BCDEFGHIJKLM
3EvaEvaCarla Carla 11Eva+6,5 Schicht 111
4  Eva   6Carla49 Schicht 26
5Carla     9   Schicht 39
6   Eva  8   Schicht 48
7          Küche9
8 Carla Carla  9   Montag Schicht 1+0,5

ZelleFormel
J3=SUMMENPRODUKT(($B$3:$G$8=I3)*((ZEILE($B$3:$G$8)=M3)*11+(ZEILE($B$3:$G$8)=M4)*6+(ZEILE($B$3:$G$8)=M5)*9+(ZEILE($B$3:$G$8)=M6)*8+(ZEILE($B$3:$G$8)=M7)*9))+($B$3=$I3)*M8
J4=SUMMENPRODUKT(($B$3:$G$8=I4)*((ZEILE($B$3:$G$8)=3)*11+(ZEILE($B$3:$G$8)=4)*6+(ZEILE($B$3:$G$8)=5)*9+(ZEILE($B$3:$G$8)=6)*8+(ZEILE($B$3:$G$8)=8)*9))+($B$3=$I4)*0,5
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#5
Hallo Edgar,

vielen Dank!! Ich habe die Tabelle so gestalten, wie in deiner Abbildung 1. Ich habe jedoch einen Arbeitsplan über 4 Wochen. Die eine Tabelle wiederholt sich vier Mal, also quasi von B3 bis G32.
Ich habe die Formel eigenständig geändert, aber leider funktioniert das nicht so wie ich das gerne hätte. Hinter dem Namen sollten dann die Stunden für die ganzen vier Wochen angezeigt werden.
Meine Veränderung: =SUMMENPRODUKT(($B$3:$G$32=I3)*((ZEILE($B$3:$G$32)=3)*11+(ZEILE($B$3:$G$32)=4)*6+(ZEILE($B$3:$G$32)=5)*9+(ZEILE($B$3:$G$32)=6)*8+(ZEILE($B$3:$G$32)=8)*9))+($B$3=$I3)*0,5

EDIT: Nach ein wenig ausprobieren habe ich herausgefunden, dass ich "(ZEILE($B$3:$G32)=11)*11+" eingeben kann.
Meine Frage jetzt, geht es nicht auch einfacher, dass man Zeilen zusammenfasst, ansatt für jede Zeile einen neuen Befehl einzugeben?
Zeile 3,11,19,27 benötigen 11 Stunden
Zeile 4,12,20,28 benötigen 6 Stunden
Zeile 5,13,21,29 benötigen 9 Stunden
Zeile 6,14,22,30 benötigen 8 Stunden
Zeile 8,16,24,32 benötigen 9 Stunden

Und noch mal der extra Hinweis, B3, B11, B19, B27 benötigen keine 11 sondern 11,5 Stunden.

Sie sehen, ich bin da totaler Anfänger :/ Ich hoffe Sie können mir noch einmal kurz helfen!! Vielen vielen Dank schon mal!

Beste Grüßen
Susanne
to top
#6
Hi Susanne,

was spricht denn gegen meine Hilfsspaltenlösung? Hier für 4 Wochen. Optisch abgetrennt durch eine fette Linie, die allerdings im Tabellenausschnitt nicht sichtbar ist.

Tabelle4

ABCDEFGHIJKLMNOPQ
2Woche 1MontagDienstagMittwochDonnerstagFreitagSamstagMeier72
39:00-20:30Müller11,5Schwarz11Müller11Fischer11Vogel11Schwarz11Müller266
49:00-15:00Huber6Schwarz6Huber6Schwarz6Schwarz6Braun6Huber84
511:00-20:30Becker9Müller9Becker9Müller9Müller9Meier9Schwarz160
612:00-20:30Schmied8Becker8Schmied8Becker8Becker8Müller8Braun60
7Schmied64
8KücheBecker9Braun9Huber9Vogel9Meier9Müller9Becker204
9Woche 29:00-20:30Müller11,5Schwarz11Müller11Fischer11Vogel11Schwarz11Fischer44
109:00-15:00Huber6Schwarz6Huber6Schwarz6Schwarz6Braun6Vogel80
1111:00-20:30Becker9Müller9Becker9Müller9Müller9Meier9
1212:00-20:30Schmied8Becker8Schmied8Becker8Becker8Müller8
13
14KücheBecker9Braun9Huber9Vogel9Meier9Müller9
15Woche 39:00-20:30Müller11,5Schwarz11Müller11Fischer11Vogel11Schwarz11
169:00-15:00Huber6Schwarz6Huber6Schwarz6Schwarz6Braun6
Formeln der Tabelle
ZelleFormel
Q2=SUMMEWENN(C:C;$P2;D:D)+SUMMEWENN(E:E;$P2;F:F)+SUMMEWENN(G:G;$P2;H:H)+SUMMEWENN(I:I;$P2;J:J)+SUMMEWENN(K:K;$P2;L:L)+SUMMEWENN(M:M;$P2;N:N)
Q3=SUMMEWENN(C:C;$P3;D:D)+SUMMEWENN(E:E;$P3;F:F)+SUMMEWENN(G:G;$P3;H:H)+SUMMEWENN(I:I;$P3;J:J)+SUMMEWENN(K:K;$P3;L:L)+SUMMEWENN(M:M;$P3;N:N)
Q4=SUMMEWENN(C:C;$P4;D:D)+SUMMEWENN(E:E;$P4;F:F)+SUMMEWENN(G:G;$P4;H:H)+SUMMEWENN(I:I;$P4;J:J)+SUMMEWENN(K:K;$P4;L:L)+SUMMEWENN(M:M;$P4;N:N)
Q5=SUMMEWENN(C:C;$P5;D:D)+SUMMEWENN(E:E;$P5;F:F)+SUMMEWENN(G:G;$P5;H:H)+SUMMEWENN(I:I;$P5;J:J)+SUMMEWENN(K:K;$P5;L:L)+SUMMEWENN(M:M;$P5;N:N)
Q6=SUMMEWENN(C:C;$P6;D:D)+SUMMEWENN(E:E;$P6;F:F)+SUMMEWENN(G:G;$P6;H:H)+SUMMEWENN(I:I;$P6;J:J)+SUMMEWENN(K:K;$P6;L:L)+SUMMEWENN(M:M;$P6;N:N)
Q7=SUMMEWENN(C:C;$P7;D:D)+SUMMEWENN(E:E;$P7;F:F)+SUMMEWENN(G:G;$P7;H:H)+SUMMEWENN(I:I;$P7;J:J)+SUMMEWENN(K:K;$P7;L:L)+SUMMEWENN(M:M;$P7;N:N)
Q8=SUMMEWENN(C:C;$P8;D:D)+SUMMEWENN(E:E;$P8;F:F)+SUMMEWENN(G:G;$P8;H:H)+SUMMEWENN(I:I;$P8;J:J)+SUMMEWENN(K:K;$P8;L:L)+SUMMEWENN(M:M;$P8;N:N)
Q9=SUMMEWENN(C:C;$P9;D:D)+SUMMEWENN(E:E;$P9;F:F)+SUMMEWENN(G:G;$P9;H:H)+SUMMEWENN(I:I;$P9;J:J)+SUMMEWENN(K:K;$P9;L:L)+SUMMEWENN(M:M;$P9;N:N)
Q10=SUMMEWENN(C:C;$P10;D:D)+SUMMEWENN(E:E;$P10;F:F)+SUMMEWENN(G:G;$P10;H:H)+SUMMEWENN(I:I;$P10;J:J)+SUMMEWENN(K:K;$P10;L:L)+SUMMEWENN(M:M;$P10;N:N)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top
#7
Hallo Günter,


ich persönlich hätte kein Problem mit einer Hilfsspaltenlösung, aber die ist hier absolut nicht notwendig, da es ohne und dazu noch mit einer kürzeren Formel, als in Deinem ersten Beitrag, geht.

@Susanne,

Du hast wieder einmal bewiesen, dass eine unvollständige Darstellung eines Problems zu Fehlern führt! Dazu hast Du eine Formel verändert, ohne zu wissen, was die einzelnen Formelteile bewirken!


Arbeitsblatt mit dem Namen 'Tabelle1'
 BCDEFGHIJ
3EvaEvaCarla Carla 11Eva134,5
4  Eva   6Carla124
5Carla     9  
6   Eva  8  
7         
8 Carla Carla  9  
9         
10         
11 EvaCarla Carla    
12  Eva      
13Carla        
14   Eva     
15         
16 Carla Carla     
17         
18         
19EvaEvaCarla Carla    
20  Eva      
21Carla        
22   Eva     
23         
24 Carla Carla     
25         
26         
27EvaEvaCarla Carla    
28  Eva      
29Carla        
30   Eva     
31         
32 Carla Carla     

ZelleFormel
J3=SUMMENPRODUKT(($B$3:$G$32=I3)*((REST(ZEILE($B$3:$G$32);8)=3)*11+(REST(ZEILE($B$3:$G$32);8)=4)*6+(REST(ZEILE($B$3:$G$32);8)=5)*9+(REST(ZEILE($B$3:$G$32);8)=6)*8+(REST(ZEILE($B$3:$G$32);8)=8)*9))+SUMMENPRODUKT(($B$3:$B$32=$I3)*(REST(ZEILE($B$3:$B$32);8)=3))*0,5
J4=SUMMENPRODUKT(($B$3:$G$32=I4)*((REST(ZEILE($B$3:$G$32);8)=3)*11+(REST(ZEILE($B$3:$G$32);8)=4)*6+(REST(ZEILE($B$3:$G$32);8)=5)*9+(REST(ZEILE($B$3:$G$32);8)=6)*8+(REST(ZEILE($B$3:$G$32);8)=8)*9))+SUMMENPRODUKT(($B$3:$B$32=$I4)*(REST(ZEILE($B$3:$B$32);8)=3))*0,5
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg




Meine Formel berücksichtigt auch, dass Schicht 1 am Montag 11,5h hat!!!!:


Arbeitsblatt mit dem Namen 'Tabelle1'
 BCDEFGHIJ
3EvaEvaCarla Carla 11Eva22,5
4      6Carla22

ZelleFormel
J3=SUMMENPRODUKT(($B$3:$G$32=I3)*((REST(ZEILE($B$3:$G$32);8)=3)*11+(REST(ZEILE($B$3:$G$32);8)=4)*6+(REST(ZEILE($B$3:$G$32);8)=5)*9+(REST(ZEILE($B$3:$G$32);8)=6)*8+(REST(ZEILE($B$3:$G$32);8)=8)*9))+SUMMENPRODUKT(($B$3:$B$32=$I3)*(REST(ZEILE($B$3:$B$32);8)=3))*0,5
J4=SUMMENPRODUKT(($B$3:$G$32=I4)*((REST(ZEILE($B$3:$G$32);8)=3)*11+(REST(ZEILE($B$3:$G$32);8)=4)*6+(REST(ZEILE($B$3:$G$32);8)=5)*9+(REST(ZEILE($B$3:$G$32);8)=6)*8+(REST(ZEILE($B$3:$G$32);8)=8)*9))+SUMMENPRODUKT(($B$3:$B$32=$I4)*(REST(ZEILE($B$3:$B$32);8)=3))*0,5
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#8
Hallo,

da spricht an sich nichts gegen, bin nur froh, dass ich das jetzt so hinbekommen habe.
Habe die Formel von Edgar jetzt so verändert, dass sie auf alle 4 Wochen passt. Ich glaube sie ist aber unnötig lang dadurch geworden.

=SUMMENPRODUKT(($B$3:$G$32=I3)*((ZEILE($B$3:$G$32)=3)*11+(ZEILE($B$3:$G$32)=11)*11+ (ZEILE($B$3:$G$32)=19)*11+(ZEILE($B$3:$G$32)=27)*11+(ZEILE($B$3:$G$32)=4)*6+(ZEILE($B$3:$G$32)=12)*6+(ZEILE($B$3:$G$32)=20)*6+(ZEILE($B$3:$G$32)=28)*6+(ZEILE($B$3:$G$32)=5)*9+(ZEILE($B$3:$G$32)=13)*9+(ZEILE($B$3:$G$32)=21)*9+(ZEILE($B$3:$G$32)=29)*9+(ZEILE($B$3:$G$32)=6)*8+(ZEILE($B$3:$G$32)=14)*8+(ZEILE($B$3:$G$32)=22)*8+(ZEILE($B$3:$G$32)=30)*8+(ZEILE($B$3:$G$32)=8)*9+(ZEILE($B$3:$G$32)=16)*9+(ZEILE($B$3:$G$32)=24)*9+(ZEILE($B$3:$G$32)=32)*9))+($B$3=$I3)*0,5+($B$11=$I3)*0,5+($B$19=$I3)*0,5+($B$27=$I3)*0,5+($G$4=$I3)*5+($G$12=$I3)*5+($G$20=$I3)*5+($G$28=$I3)*5+($B$4=$I3)*0,5+($B$12=$I3)*0,5+($B$20=$I3)*0,5+($B$28=$I3)*0,5

Jetzt rechnet es die Stunden für alle 4 Wochen aus mit den folgenden Sonderausnahmen:

B3, B11, B19, B27 haben keine 11 sondern 11,5 Stunden
B4, B12, B20, B28 haben keine 6 sondern 6,5 Stunden
G4, G12, G20, G24 haben keine 6 sondern 11 Stunden

Vielen Dank für alles, ich habe es jetzt so wie ich es wollte!
Nur so als Excelanfänger würde mich halt interessieren, ob man die Formel hätte einfacher gestalten können fürs nächste Mal Smile
to top
#9
Hallo,

dann sieh Dir meinen Beitrag an!


Code:
B3, B11, B19, B27 haben keine 11 sondern 11,5 Stunden
B4, B12, B20, B28 haben keine 6 sondern 6,5 Stunden
G4, G12, G20, G24 haben keine 6 sondern 11 Stunden


Und schon wieder Ausnahmen, die Einfluß auf die Formelgestaltung haben!!! Angry Angry Angry


Code:
=SUMMENPRODUKT(($B$3:$G$32=I3)*((REST(ZEILE($B$3:$G$32);8)=3)*11+(REST(ZEILE($B$3:$G$32);8)=4)*6+(REST(ZEILE($B$3:$G$32);8)=5)*9+(REST(ZEILE($B$3:$G$32);8)=6)*8+(REST(ZEILE($B$3:$G$32);8)=8)*9))+SUMMENPRODUKT(($B$3:$B$32=$I3)*(REST(ZEILE($B$3:$B$32);8)={3.4}))*0,5+SUMMENPRODUKT(($G$3:$G$32=$I3)*(REST(ZEILE($B$3:$B$32);8)=4))*5
Gruß
Opa Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#10
Tja,

jede Medaille hat halt zwei Seiten.


Zitat:@Susanne,

Du hast wieder einmal bewiesen, dass eine unvollständige Darstellung eines Problems zu Fehlern führt! Dazu hast Du eine Formel verändert, ohne zu wissen, was die einzelnen Formelteile bewirken!

Wenn auch Hilfsspalten nicht so elegant sind, bei den Ausnahmen von den Ausnahmen hätte meine Formel nicht angefasst werden müssen. Die Stundenanpassung wäre ausschließlich in den HS vonstatten gegangen.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) http://www.sprueche-zum-nachdenken.eu
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste