Hilfe beim Dienstplan

[Susanne Keuneke]

Hallo,

ich habe eine Frage zu meinem Dienstplan (siehe Link 1).
[url=
Dateiupload bitte im Forum! So geht es: Klick mich!
]

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)
[url=
Dateiupload bitte im Forum! So geht es: Klick mich!
]

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!

[WillWissen]

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?

[WillWissen]

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

2

Montag

Dienstag

Mittwoch

Donnerstag

Freitag

Samstag

Meier

18

3

9:00-20:30

Müller

11,5

Vogel

11

Müller

11

Vogel

11

Vogel

11

Schwarz

11

Müller

66,5

4

9:00-15:00

Huber

6

Schwarz

6

Huber

6

Schwarz

6

Schwarz

6

Braun

6

Huber

21

Formeln der Tabelle

Zelle Formel 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

Zelle Zulassen Daten Eingabe 1 Eingabe 2 A2 Liste =#BEZUG! B3 Liste =$O$2:$O$10 D3 Liste =$O$2:$O$10 F3 Liste =$O$2:$O$10 H3 Liste =$O$2:$O$10 J3 Liste =$O$2:$O$10 L3 Liste =$O$2:$O$10 B4 Liste =$O$2:$O$10 D4 Liste =$O$2:$O$10 F4 Liste =$O$2:$O$10 H4 Liste =$O$2:$O$10 J4 Liste =$O$2:$O$10 L4 Liste =$O$2:$O$10

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[BoskoBiati]

Hallo,

das geht auch einfacher:

Arbeitsblatt mit dem Namen 'Tabelle1'
	B	C	D	E	F	G	H	I	J
3	Eva	Eva	Carla		Carla		11	Eva	36,5
4			Eva				6	Carla	49
5	Carla						9
6				Eva			8
7
8		Carla		Carla			9

Zelle	Formel
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'

B

C

D

E

F

G

H

I

J

K

L

M

3

Eva

Eva

Carla

Carla

11

Eva

+6,5

Schicht 1

11

4

Eva

6

Carla

49

Schicht 2

6

5

Carla

9

Schicht 3

9

6

Eva

8

Schicht 4

8

7

Küche

9

8

Carla

Carla

9

Montag Schicht 1

+0,5

Zelle	Formel
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

[Susanne Keuneke]

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

[WillWissen]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Woche 1		Montag		Dienstag		Mittwoch		Donnerstag		Freitag		Samstag			Meier	72
3		9:00-20:30	Müller	11,5	Schwarz	11	Müller	11	Fischer	11	Vogel	11	Schwarz	11		Müller	266
4		9:00-15:00	Huber	6	Schwarz	6	Huber	6	Schwarz	6	Schwarz	6	Braun	6		Huber	84
5		11:00-20:30	Becker	9	Müller	9	Becker	9	Müller	9	Müller	9	Meier	9		Schwarz	160
6		12:00-20:30	Schmied	8	Becker	8	Schmied	8	Becker	8	Becker	8	Müller	8		Braun	60
7																Schmied	64
8		Küche	Becker	9	Braun	9	Huber	9	Vogel	9	Meier	9	Müller	9		Becker	204
9	Woche 2	9:00-20:30	Müller	11,5	Schwarz	11	Müller	11	Fischer	11	Vogel	11	Schwarz	11		Fischer	44
10		9:00-15:00	Huber	6	Schwarz	6	Huber	6	Schwarz	6	Schwarz	6	Braun	6		Vogel	80
11		11:00-20:30	Becker	9	Müller	9	Becker	9	Müller	9	Müller	9	Meier	9
12		12:00-20:30	Schmied	8	Becker	8	Schmied	8	Becker	8	Becker	8	Müller	8
13
14		Küche	Becker	9	Braun	9	Huber	9	Vogel	9	Meier	9	Müller	9
15	Woche 3	9:00-20:30	Müller	11,5	Schwarz	11	Müller	11	Fischer	11	Vogel	11	Schwarz	11
16		9:00-15:00	Huber	6	Schwarz	6	Huber	6	Schwarz	6	Schwarz	6	Braun	6

Formeln der Tabelle

Zelle Formel 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

[BoskoBiati]

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'
	B	C	D	E	F	G	H	I	J
3	Eva	Eva	Carla		Carla		11	Eva	134,5
4			Eva				6	Carla	124
5	Carla						9
6				Eva			8
7
8		Carla		Carla			9
9
10
11		Eva	Carla		Carla
12			Eva
13	Carla
14				Eva
15
16		Carla		Carla
17
18
19	Eva	Eva	Carla		Carla
20			Eva
21	Carla
22				Eva
23
24		Carla		Carla
25
26
27	Eva	Eva	Carla		Carla
28			Eva
29	Carla
30				Eva
31
32		Carla		Carla

Zelle	Formel
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'
	B	C	D	E	F	G	H	I	J
3	Eva	Eva	Carla		Carla		11	Eva	22,5
4							6	Carla	22

Zelle	Formel
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

[Susanne Keuneke]

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 :)

[BoskoBiati]

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!!! :@ :@ :@

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

[WillWissen]

Tja,

jede Medaille hat halt zwei Seiten.

@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.