Liste mit VON / BIS zusammenfassen

[shift-del]

Natürlich werden immer alle Werte der Spalten übergeben.
Das sieht man doch an den Zellbezügen.

[ws-53]

Die Tabelle ist Murks, da das Jahr fehlt und somit die Folge bei Jahreswechsel falsch wird und von bis über einen Jahreswechsel nicht ermittelt werden kann.

[oee]

Vergiss die Formel - LET, MINWENNS, MAXWENNS und SPALTENWAHL kennt dein Excel ja auch nicht.

[Fred11]

Hallo Michael,

hier eine Formellösung mit einer Hilfsspalte, die auch in Escel 2016 funktioniert:

Arbeitsblatt mit dem Namen 'Formellösung'
	A	B	C	D	E	K	L	M	N
1	Name	Bereich	KW	Hilfsspalte		Name	Bereich	Von KW	Bis KW
2	Name 1	A	25	1		Name 1	A	25	27
3	Name 1	A	26			Name 1	B	30	30
4	Name 1	A	27			Name 1	C	32	32
5	Name 1	B	30	4		Name 2	A	30	30
6	Name 1	C	32	5		Name 2	B	34	35
7	Name 2	A	30	6		Name 3	A	31	31
8	Name 2	B	34	7		Name 4	B	32	32
9	Name 2	B	35			Name 4	C	33	35
10	Name 3	A	31	9
11	Name 4	B	32	10
12	Name 4	C	33	11
13	Name 4	C	34
14	Name 4	C	35

Zelle	Formel
D2	=WENN(ZÄHLENWENNS($A$2:A2;A2;$B$2:B2;B2)=1;ZEILE(A1);"")
K2	=WENNFEHLER(INDEX(A$2:A$14;KKLEINSTE($D$2:$D$14;ZEILE($A1)));"")
L2	=WENNFEHLER(INDEX(B$2:B$14;KKLEINSTE($D$2:$D$14;ZEILE($A1)));"")
M2	=WENN(K2<>"";INDEX($C$2:$C$14;VERGLEICH(K2&L2;$A$2:$A$14&$B$2:$B$14;0));"")
N2	=WENNFEHLER(VERWEIS(2;1/(($A$2:$A$14=K2)*($B$2:$B$14=L2));$C$2:$C$14);"")

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 2021

Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

Die Formel in Spalte M
=WENN(K2<>"";INDEX($C$2:$C$14;VERGLEICH(K2&L2;$A$2:$A$14&$B$2:$B$14;0));"")
muss in Excel 2016 höchstwahrscheinlich als Matrixformel mit Strg+Shiuft+Enter eingegeben werden

Und mit einer Pivottabelle ergibt sich mit den richtigen Einstellungen genau dein angegebenes Wunschergebnis.

Siehe angehängte Beispieldatei

Gruß
Fred

[Luschi]

Hallo Michaeel,

mit Power Query ist das total easy-peasy.

Gruß von Luschi
aus klein-Paris

[Mikecell]

Hallo, speziell an Fred und Luschi,

ich habe inzwischen leichte Selbstzweifel und vermutlich auch meinen Fehler erkannt. Es gibt eine kleine Einschränkung bei der Zusammenfassung der Woche, die ich nicht richtig betont haben. Eure Vorschläge funktionieren beide, bis zu einem bestimmten Punkt. Und zwar sollen Kalenderwochen, die direkt aneinander grenzen als ein Zeitraum erkannt werden. Sollte der Mitarbeiter jedoch später (Abstand >1 Woche) nochmal in der gleichen Abteilung sein, dann benötige ich eine neue Zeile.

Beispiel:

Name 1 - Bereich 1 - KW 30
Name 1 - Bereich 1 - KW 31
Name 1 - Bereich 1 - KW 32
Name 1 - Bereich 1 - KW 40

soll ergeben:
Name 1 - Bereich 1 - KW 30 bis KW 32
Name 1 - Bereich 1 - KW 40 bis KW 40

Sorry für die schlechte Beschreibung.

Grüße Michael

[ws-53]

... die Sortierung ergibt sich ohne manuellen Eingriff aus der Reihenfolge der Namen und der Kalenderwochen einer anderen Tabelle. Ich plane in einer Kreuztabelle und entpivotisiere diese. 

Hallo Luschi,

das ist hallt nur bedingt riichtig. Ich habe nun mal "Klaus Jahreswechsel" die KW 52 (ab 23.12.) und KW 1 (ab 30.12.) dem Bereich JW zugewiesen. Da wird das Ergebnis dann halt ein ganz klein wenig falsch.

Und selbst, wenn es über den Jahreswechsel Werksferien gäbe, das alte Jahr mit KW 51 enden und da neue erst mit KW 2 beginnen würde, wird das Ergebnis falsch.

[Fred11]

Hallo Michael,

Sollte der Mitarbeiter jedoch später (Abstand >1 Woche) nochmal in der gleichen Abteilung sein, dann benötige ich eine neue Zeile

Das habe ich schon befürchtet, dass dieser Fall auch noch berücksichtigt werden soll.

Das macht dann die Auswertung deutlich schwieriger und die Formellösung entsprechend komplexer.

Die einzige Möglichkeit, die mir hierfür einfällt, ist eine weitere Hilfsspalte, in der die die zusammenhängenden Bereiche fortlaufend durchnumeriert werden.
Diese Spalte kann dann in der Auswertungstabelle als weiteres Kriterium (neben Name und Bereich) herangezogen werden.

Arbeitsblatt mit dem Namen 'Formellösung'
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Bereich	KW	Hilfsspalte 1	Hilfsspalte 2			Name	Bereich	Von KW	Bis KW
2	Name 1	A	25	1	1		1	Name 1	A	25	27
3	Name 1	A	26	1			2	Name 1	A	30	30
4	Name 1	A	27	1			3	Name 1	B	30	30
5	Name 1	A	30	2	4		4	Name 1	C	32	32
6	Name 1	B	30	3	5		5	Name 2	A	30	30
7	Name 1	C	32	4	6		6	Name 2	B	34	35
8	Name 2	A	30	5	7		7	Name 3	A	31	31
9	Name 2	B	34	6	8		8	Name 4	B	32	32
10	Name 2	B	35	6			9	Name 4	C	33	34
11	Name 3	A	31	7	10		10	Name 4	C	36	36
12	Name 4	B	32	8	11		11	Name 4	C	38	39
13	Name 4	C	33	9	12
14	Name 4	C	34	9
15	Name 4	C	36	10	14
16	Name 4	C	38	11	15
17	Name 4	C	39	11

Zelle	Formel
D3	=WENN(UND(A3=A2;B3=B2;C3=C2+1);D2;D2+1)
E2	=WENN(ZÄHLENWENNS($A$2:A2;A2;$B$2:B2;B2;$D$2:D2;D2)=1;ZEILE(A1);"")
G2	=WENN(H2<>"";G1+1;"")
H2	=WENNFEHLER(INDEX(A$2:A$17;KKLEINSTE($E$2:$E$17;ZEILE($A1)));"")
I2	=WENNFEHLER(INDEX(B$2:B$17;KKLEINSTE($E$2:$E$17;ZEILE($A1)));"")
J2	=WENN(H2<>"";INDEX($C$2:$C$17;VERGLEICH(H2&I2&G2;$A$2:$A$17&$B$2:$B$17&$D$2:$D$17;0));"")
K2	=WENNFEHLER(VERWEIS(2;1/(($A$2:$A$17=H2)*($B$2:$B$17=I2)*($D$2:$D$17=G2));$C$2:$C$17);"")

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 2021

Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

Siehe angehängte Beispieldatei.
(für die Pivottabelle habe ich keine Lösung gefunden….)

Gruß
Fred

[Luschi]

Hallo WS-53,

woher soll PQ bei der undeutlichen Datenlage wissen, daß zwischen KW52/53 und KW1 ein Jahreswechsel liegt.
Hier sollte sich der Fragesteller mehr Gedanken um seine Daten machen, die er auswerten will.

Gruß von Luschi
aus klein-Paris

[ws-53]

Guten Morgen Luschi, 

da hast du völlig recht. Deshalb hatte ich ja schon vorher auf diese Problematik aufmerksam gemacht.