Zählen, wenn Zeitraum in bestimmter KW liegt

[Excel82]

Ich habe einen kniffligen Fall:

ich habe Zeiträume, in denen Mitarbeiter Urlaub haben:

MA Beginn Ende
1 27.01.2015 30.03.2015
2 12.02.2015 28.03.2015
3 20.02.2015 26.03.2015

usw.


Nun möchte ich für das Jahr 2015 wissen, wieviele MA in den ganzen KWs Urlaub haben, also z.B. will ich als Ergebnis: in der KW 15 haben 25 Mitarbeiter Urlaub. In der KW 20 haben 45 Mitarbeiter Urlaub.

Die KWs habe ich auch exakt definiert:

KW 11 09.03.2015 15.03.2015
KW 12 ....



Ich habe schon vieles probiert, aber ich komme einfach nicht drauf, wie ich zwei Zeiträume abprüfe.

Kann mir jemand helfen?

[steve1da]

Hola,

das klingt im ersten Moment nach Zählenwenns().

Gruß,
steve1da

[WillWissen]

Hi,

wenn ich dich richtig verstanden habe, willst du nur die Anzahl der Mitarbeiter, die gleichzeitig in einer kompletten Woche (also von Montag bis Sonntag) im Urlaub sind?

Dann habe ich diesen Vorschlag für dich:

Tabelle3

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

1

MA

U-Beginn

U-Ende

KW-A

KW-E

KW 6

KW 7

KW 8

KW 9

KW 10

KW 11

KW 12

KW 13

2

1

Di, 27.01.2015

Mo, 30.03.2015

6

13

6

7

8

9

10

11

12

13

1 MA

1 MA

2 MA

3 MA

3 MA

3 MA

3 MA

1 MA

3

2

Do, 12.02.2015

Sa, 28.03.2015

8

12

8

9

10

11

12

4

3

Fr, 20.02.2015

Do, 26.03.2015

9

12

9

10

11

12

Formeln der Tabelle

Zelle Formel D2 =WENN(WOCHENTAG(B2;2)=1;KALENDERWOCHE(B2;21);KALENDERWOCHE(B2;21)+1) E2 =WENN(WOCHENTAG(C2;2)=7;KALENDERWOCHE(C2;21);KALENDERWOCHE(C2;21)-1) F2 =D2 G2 =WENN(F2="";"";WENN(F2=$E2;"";F2+1)) H2 =WENN(G2="";"";WENN(G2=$E2;"";G2+1)) I2 =WENN(H2="";"";WENN(H2=$E2;"";H2+1)) J2 =WENN(I2="";"";WENN(I2=$E2;"";I2+1)) K2 =WENN(J2="";"";WENN(J2=$E2;"";J2+1)) L2 =WENN(K2="";"";WENN(K2=$E2;"";K2+1)) M2 =WENN(L2="";"";WENN(L2=$E2;"";L2+1)) N2 =WENN(M2="";"";WENN(M2=$E2;"";M2+1)) O2 =WENN(N2="";"";WENN(N2=$E2;"";N2+1)) P2 =WENN(O2="";"";WENN(O2=$E2;"";O2+1)) Q2 =WENN(P2="";"";WENN(P2=$E2;"";P2+1)) R2 =ZÄHLENWENN($F$2:$Q$4;R1) S2 =ZÄHLENWENN($F$2:$Q$4;S1) T2 =ZÄHLENWENN($F$2:$Q$4;T1) U2 =ZÄHLENWENN($F$2:$Q$4;U1) V2 =ZÄHLENWENN($F$2:$Q$4;V1) W2 =ZÄHLENWENN($F$2:$Q$4;W1) X2 =ZÄHLENWENN($F$2:$Q$4;X1) Y2 =ZÄHLENWENN($F$2:$Q$4;Y1) D3 =WENN(WOCHENTAG(B3;2)=1;KALENDERWOCHE(B3;21);KALENDERWOCHE(B3;21)+1) E3 =WENN(WOCHENTAG(C3;2)=7;KALENDERWOCHE(C3;21);KALENDERWOCHE(C3;21)-1) F3 =D3 G3 =WENN(F3="";"";WENN(F3=$E3;"";F3+1)) H3 =WENN(G3="";"";WENN(G3=$E3;"";G3+1)) I3 =WENN(H3="";"";WENN(H3=$E3;"";H3+1)) J3 =WENN(I3="";"";WENN(I3=$E3;"";I3+1)) K3 =WENN(J3="";"";WENN(J3=$E3;"";J3+1)) L3 =WENN(K3="";"";WENN(K3=$E3;"";K3+1)) M3 =WENN(L3="";"";WENN(L3=$E3;"";L3+1)) N3 =WENN(M3="";"";WENN(M3=$E3;"";M3+1)) O3 =WENN(N3="";"";WENN(N3=$E3;"";N3+1)) P3 =WENN(O3="";"";WENN(O3=$E3;"";O3+1)) Q3 =WENN(P3="";"";WENN(P3=$E3;"";P3+1)) D4 =WENN(WOCHENTAG(B4;2)=1;KALENDERWOCHE(B4;21);KALENDERWOCHE(B4;21)+1) E4 =WENN(WOCHENTAG(C4;2)=7;KALENDERWOCHE(C4;21);KALENDERWOCHE(C4;21)-1) F4 =D4 G4 =WENN(F4="";"";WENN(F4=$E4;"";F4+1)) H4 =WENN(G4="";"";WENN(G4=$E4;"";G4+1)) I4 =WENN(H4="";"";WENN(H4=$E4;"";H4+1)) J4 =WENN(I4="";"";WENN(I4=$E4;"";I4+1)) K4 =WENN(J4="";"";WENN(J4=$E4;"";J4+1)) L4 =WENN(K4="";"";WENN(K4=$E4;"";K4+1)) M4 =WENN(L4="";"";WENN(L4=$E4;"";L4+1)) N4 =WENN(M4="";"";WENN(M4=$E4;"";M4+1)) O4 =WENN(N4="";"";WENN(N4=$E4;"";N4+1)) P4 =WENN(O4="";"";WENN(O4=$E4;"";O4+1)) Q4 =WENN(P4="";"";WENN(P4=$E4;"";P4+1))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Die gelb hinterlegten Spalten sind Hilfsspalten und können ausgeblendet werden.

[WillWissen]

Hi,

hier ein verbesserter Vorschlag, der insgesamt etwas automatischer funktioniert.

Tabelle3

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	MA	U-Beginn	U-Ende	KW-A	KW-E													KW 2	KW 3	KW 4	KW 5	KW 6	KW 7	KW 8	KW 9	KW 10	KW 11	KW 12	KW 13	KW 14
2	1	Di, 27.01.2015	Mo, 30.03.2015	6	14	6	7	8	9	10	11	12	13	14				1 MA	1 MA	1 MA	1 MA	2 MA	2 MA	3 MA	4 MA	3 MA	3 MA	3 MA	3 MA	3 MA
3	2	Do, 12.02.2015	Sa, 28.03.2015	8	14	8	9	10	11	12	13	14
4	3	Fr, 20.02.2015	Do, 26.03.2015	9	14	9	10	11	12	13	14
5	4	Do, 01.01.2015	Di, 17.02.2015	2	9	2	3	4	5	6	7	8	9
6	5	Di, 10.03.2015	Fr, 13.03.2015

Formeln der Tabelle

Zelle Formel R1 =MIN(D:D) S1 =WENN(R1="";"";WENN(R1=MAX($E:$E);"";R1+1)) T1 =WENN(S1="";"";WENN(S1=MAX($E:$E);"";S1+1)) U1 =WENN(T1="";"";WENN(T1=MAX($E:$E);"";T1+1)) V1 =WENN(U1="";"";WENN(U1=MAX($E:$E);"";U1+1)) W1 =WENN(V1="";"";WENN(V1=MAX($E:$E);"";V1+1)) X1 =WENN(W1="";"";WENN(W1=MAX($E:$E);"";W1+1)) Y1 =WENN(X1="";"";WENN(X1=MAX($E:$E);"";X1+1)) Z1 =WENN(Y1="";"";WENN(Y1=MAX($E:$E);"";Y1+1)) AA1 =WENN(Z1="";"";WENN(Z1=MAX($E:$E);"";Z1+1)) AB1 =WENN(AA1="";"";WENN(AA1=MAX($E:$E);"";AA1+1)) AC1 =WENN(AB1="";"";WENN(AB1=MAX($E:$E);"";AB1+1)) AD1 =WENN(AC1="";"";WENN(AC1=MAX($E:$E);"";AC1+1)) AE1 =WENN(AD1="";"";WENN(AD1=MAX($E:$E);"";AD1+1)) AF1 =WENN(AE1="";"";WENN(AE1=MAX($E:$E);"";AE1+1)) D2 =WENN($C2-$B2<7;"";WENN(WOCHENTAG(B2;2)=1;KALENDERWOCHE(B2;21);KALENDERWOCHE(B2;21)+1)) E2 =WENN($C2-$B2<7;"";WENN(WOCHENTAG(C2;2)=1;KALENDERWOCHE(C2;21);KALENDERWOCHE(C2;21)+1)) F2 =D2 G2 =WENN(F2="";"";WENN(F2=$E2;"";F2+1)) H2 =WENN(G2="";"";WENN(G2=$E2;"";G2+1)) I2 =WENN(H2="";"";WENN(H2=$E2;"";H2+1)) J2 =WENN(I2="";"";WENN(I2=$E2;"";I2+1)) K2 =WENN(J2="";"";WENN(J2=$E2;"";J2+1)) L2 =WENN(K2="";"";WENN(K2=$E2;"";K2+1)) M2 =WENN(L2="";"";WENN(L2=$E2;"";L2+1)) N2 =WENN(M2="";"";WENN(M2=$E2;"";M2+1)) O2 =WENN(N2="";"";WENN(N2=$E2;"";N2+1)) P2 =WENN(O2="";"";WENN(O2=$E2;"";O2+1)) Q2 =WENN(P2="";"";WENN(P2=$E2;"";P2+1)) R2 =WENN(R1="";"";ZÄHLENWENN($F$2:$Q$10;R1)) S2 =WENN(S1="";"";ZÄHLENWENN($F$2:$Q$10;S1)) T2 =WENN(T1="";"";ZÄHLENWENN($F$2:$Q$10;T1)) U2 =WENN(U1="";"";ZÄHLENWENN($F$2:$Q$10;U1)) V2 =WENN(V1="";"";ZÄHLENWENN($F$2:$Q$10;V1)) W2 =WENN(W1="";"";ZÄHLENWENN($F$2:$Q$10;W1)) X2 =WENN(X1="";"";ZÄHLENWENN($F$2:$Q$10;X1)) Y2 =WENN(Y1="";"";ZÄHLENWENN($F$2:$Q$10;Y1)) Z2 =WENN(Z1="";"";ZÄHLENWENN($F$2:$Q$10;Z1)) AA2 =WENN(AA1="";"";ZÄHLENWENN($F$2:$Q$10;AA1)) AB2 =WENN(AB1="";"";ZÄHLENWENN($F$2:$Q$10;AB1)) AC2 =WENN(AC1="";"";ZÄHLENWENN($F$2:$Q$10;AC1)) AD2 =WENN(AD1="";"";ZÄHLENWENN($F$2:$Q$10;AD1)) AE2 =WENN(AE1="";"";ZÄHLENWENN($F$2:$Q$10;AE1)) AF2 =WENN(AF1="";"";ZÄHLENWENN($F$2:$Q$10;AF1)) D3 =WENN($C3-$B3<7;"";WENN(WOCHENTAG(B3;2)=1;KALENDERWOCHE(B3;21);KALENDERWOCHE(B3;21)+1)) E3 =WENN($C3-$B3<7;"";WENN(WOCHENTAG(C3;2)=1;KALENDERWOCHE(C3;21);KALENDERWOCHE(C3;21)+1)) F3 =D3 G3 =WENN(F3="";"";WENN(F3=$E3;"";F3+1)) H3 =WENN(G3="";"";WENN(G3=$E3;"";G3+1)) I3 =WENN(H3="";"";WENN(H3=$E3;"";H3+1)) J3 =WENN(I3="";"";WENN(I3=$E3;"";I3+1)) K3 =WENN(J3="";"";WENN(J3=$E3;"";J3+1)) L3 =WENN(K3="";"";WENN(K3=$E3;"";K3+1)) M3 =WENN(L3="";"";WENN(L3=$E3;"";L3+1)) N3 =WENN(M3="";"";WENN(M3=$E3;"";M3+1)) O3 =WENN(N3="";"";WENN(N3=$E3;"";N3+1)) P3 =WENN(O3="";"";WENN(O3=$E3;"";O3+1)) Q3 =WENN(P3="";"";WENN(P3=$E3;"";P3+1)) D4 =WENN($C4-$B4<7;"";WENN(WOCHENTAG(B4;2)=1;KALENDERWOCHE(B4;21);KALENDERWOCHE(B4;21)+1)) E4 =WENN($C4-$B4<7;"";WENN(WOCHENTAG(C4;2)=1;KALENDERWOCHE(C4;21);KALENDERWOCHE(C4;21)+1)) F4 =D4 G4 =WENN(F4="";"";WENN(F4=$E4;"";F4+1)) H4 =WENN(G4="";"";WENN(G4=$E4;"";G4+1)) I4 =WENN(H4="";"";WENN(H4=$E4;"";H4+1)) J4 =WENN(I4="";"";WENN(I4=$E4;"";I4+1)) K4 =WENN(J4="";"";WENN(J4=$E4;"";J4+1)) L4 =WENN(K4="";"";WENN(K4=$E4;"";K4+1)) M4 =WENN(L4="";"";WENN(L4=$E4;"";L4+1)) N4 =WENN(M4="";"";WENN(M4=$E4;"";M4+1)) O4 =WENN(N4="";"";WENN(N4=$E4;"";N4+1)) P4 =WENN(O4="";"";WENN(O4=$E4;"";O4+1)) Q4 =WENN(P4="";"";WENN(P4=$E4;"";P4+1)) D5 =WENN($C5-$B5<7;"";WENN(WOCHENTAG(B5;2)=1;KALENDERWOCHE(B5;21);KALENDERWOCHE(B5;21)+1)) E5 =WENN($C5-$B5<7;"";WENN(WOCHENTAG(C5;2)=1;KALENDERWOCHE(C5;21);KALENDERWOCHE(C5;21)+1)) F5 =D5 G5 =WENN(F5="";"";WENN(F5=$E5;"";F5+1)) H5 =WENN(G5="";"";WENN(G5=$E5;"";G5+1)) I5 =WENN(H5="";"";WENN(H5=$E5;"";H5+1)) J5 =WENN(I5="";"";WENN(I5=$E5;"";I5+1)) K5 =WENN(J5="";"";WENN(J5=$E5;"";J5+1)) L5 =WENN(K5="";"";WENN(K5=$E5;"";K5+1)) M5 =WENN(L5="";"";WENN(L5=$E5;"";L5+1)) N5 =WENN(M5="";"";WENN(M5=$E5;"";M5+1)) O5 =WENN(N5="";"";WENN(N5=$E5;"";N5+1)) P5 =WENN(O5="";"";WENN(O5=$E5;"";O5+1)) Q5 =WENN(P5="";"";WENN(P5=$E5;"";P5+1)) D6 =WENN($C6-$B6<7;"";WENN(WOCHENTAG(B6;2)=1;KALENDERWOCHE(B6;21);KALENDERWOCHE(B6;21)+1)) E6 =WENN($C6-$B6<7;"";WENN(WOCHENTAG(C6;2)=1;KALENDERWOCHE(C6;21);KALENDERWOCHE(C6;21)+1)) F6 =D6 G6 =WENN(F6="";"";WENN(F6=$E6;"";F6+1)) H6 =WENN(G6="";"";WENN(G6=$E6;"";G6+1)) I6 =WENN(H6="";"";WENN(H6=$E6;"";H6+1)) J6 =WENN(I6="";"";WENN(I6=$E6;"";I6+1)) K6 =WENN(J6="";"";WENN(J6=$E6;"";J6+1)) L6 =WENN(K6="";"";WENN(K6=$E6;"";K6+1)) M6 =WENN(L6="";"";WENN(L6=$E6;"";L6+1)) N6 =WENN(M6="";"";WENN(M6=$E6;"";M6+1)) O6 =WENN(N6="";"";WENN(N6=$E6;"";N6+1)) P6 =WENN(O6="";"";WENN(O6=$E6;"";O6+1)) Q6 =WENN(P6="";"";WENN(P6=$E6;"";P6+1))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[Excel82]

Vielen dank für den Vorschlag, aber ich denke, das kann ich so nicht umsetzen.
Es handelt sich um ca. 500 Mitarbeiter und ich habe nur die Urlaubsdaten. Ich kann nicht bei 500 Leuten die Datensätze nochmal aufdröseln.

Spalten D bis Q kann ich nicht liefern.

Gibt es noch eine andere Lösung.

[WillWissen]

Hi,

stell doch bitte deine Tabelle (abgespeckt und anonymisiert) vor. Vllt. findet sich noch eine Möglichkeit.

[BoskoBiati]

Hallo,

wenn die MA immer die ganze Woche Urlaub haben, dann ginge das:

Tabelle1

	A	B	C	D	E	F	G	H
1	1	27.01.2015	30.03.2015		KW 11	09.03.2015	15.03.2015	3
2	2	12.02.2015	28.03.2015
3	3	20.02.2015	26.03.2015

Formeln der Tabelle

Zelle Formel H1 =SUMMENPRODUKT((B:B<=F1)*(C:C>=G1))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[Excel82]

Leider nein, wenn jemand nur einen Tag hat, dann sieht der Datensatz so aus

z.B.:

27.03.2015 27.03.2015

[WillWissen]

Leider nein, wenn jemand nur einen Tag hat, dann sieht der Datensatz so aus

z.B.:

27.03.2015    27.03.2015

Du hast meine Bitte schon gelesen?

Gestern, 13:36

Ich kann mir nämlich, ohne den Aufbau deiner Datei zu kennen, nicht vorstellen, dass bei 1048576 Zeilen und 16384 Spalten nicht irgendwo ein kleines Plätzchen ist, um Hilfsspalten/zeilen einzufügen. ;)

[BoskoBiati]

Hallo,

hatte vorhin einen Beitrag abgeschickt, der wohl nicht angekommen ist.

Code:

=SUMMENPRODUKT((B:B<=G1)*(C:C>=F1))