Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Zählen, wenn Zeitraum in bestimmter KW liegt
#1
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?
to top
#2
Hola,

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

Gruß,
steve1da
to top
#3
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

ABCDEFGHIJKLMNOPQRSTUVWXY
1MAU-BeginnU-EndeKW-AKW-EKW 6KW 7KW 8KW 9KW 10KW 11KW 12KW 13
21Di, 27.01.2015Mo, 30.03.20156136789101112131 MA1 MA2 MA3 MA3 MA3 MA3 MA1 MA
32Do, 12.02.2015Sa, 28.03.201581289101112
43Fr, 20.02.2015Do, 26.03.20159129101112
Formeln der Tabelle
ZelleFormel
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.
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#4
Hi,

hier ein verbesserter Vorschlag, der insgesamt etwas automatischer funktioniert.

Tabelle3

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1MAU-BeginnU-EndeKW-AKW-EKW 2KW 3KW 4KW 5KW 6KW 7KW 8KW 9KW 10KW 11KW 12KW 13KW 14
21Di, 27.01.2015Mo, 30.03.2015614678910111213141 MA1 MA1 MA1 MA2 MA2 MA3 MA4 MA3 MA3 MA3 MA3 MA3 MA
32Do, 12.02.2015Sa, 28.03.2015814891011121314
43Fr, 20.02.2015Do, 26.03.201591491011121314
54Do, 01.01.2015Di, 17.02.20152923456789
65Di, 10.03.2015Fr, 13.03.2015
Formeln der Tabelle
ZelleFormel
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
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#5
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.
to top
#6
Hi,

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

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#7
Hallo,

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

Tabelle1

ABCDEFGH
1127.01.201530.03.2015KW 1109.03.201515.03.20153
2212.02.201528.03.2015
3320.02.201526.03.2015
Formeln der Tabelle
ZelleFormel
H1=SUMMENPRODUKT((B:B<=F1)*(C:C>=G1))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#8
Leider nein, wenn jemand nur einen Tag hat, dann sieht der Datensatz so aus

z.B.:

27.03.2015 27.03.2015
to top
#9
(27.03.2015, 11:46)Excel82 schrieb: 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. Wink
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#10
Hallo,

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


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


Edgar

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


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Makro code funktioniert nicht - Woran liegt es? FR-CGI 3 68 30.11.2016, 18:53
Letzter Beitrag: RPP63
  Bestimmter teil aus Zelle in eine andere Kopieren markaay 3 57 17.11.2016, 11:13
Letzter Beitrag: BoskoBiati
  Werte in Userform unter bestimmter Bediengung michel34497 5 98 12.11.2016, 20:50
Letzter Beitrag: schauan
Question Anzahl der Einträge in einer Zeile zählen (nicht Zahlen) Olivenbaum2013 6 88 11.11.2016, 10:35
Letzter Beitrag: Olivenbaum2013
  Zelle so oft potenzieren bis bestimmter Wert erreicht ist? ChillAlta 17 224 09.11.2016, 19:57
Letzter Beitrag: shift-del
  Zählen wenn Bernd 6 174 08.10.2016, 14:57
Letzter Beitrag: Bernd
  Unterschiedliche Anzahl an Messpunkten über gleichen Zeitraum synchronisieren manuel 10 277 03.10.2016, 14:34
Letzter Beitrag: manuel
  Zählen, wenn kazzz 1 78 20.09.2016, 08:39
Letzter Beitrag: steve1da
  VBA Zählen wenn herbert0803 2 204 17.09.2016, 07:08
Letzter Beitrag: schauan
  Bedingte Formatierung - Zeitraum Fred B 4 120 16.09.2016, 08:09
Letzter Beitrag: Fred B

Gehe zu:


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