ADRESSE() als Bezug greift nicht

Hallo Forumsteilnehmer,
ich wende mich im Auftrag meiner Kollegen an Euch mit der Bitte um Unterstützung.
Wir haben ein kleines Problemchen, und zwar bekommen wir über Datenimport vier Spalten in ein Tabellenblatt kopiert, die ersten beiden sind Mitarbeiternamen, die zwei anderen die Umsatzzahlen.
Der Aufbau ist eher suboptimal, deswegen kopieren wir die Spalten dann untereinander für die Auswertung der Summe der letzten 3 Umsätze von Mitarbeiter x.
Jetzt sind wir, bzw. der Kollege, der sich etwas besser mit Excel auskennt, bei excelformeln auf eine Formel gestoßen, die uns eigentlich das ganze "Umbauen" ersparen könnte, aber die Formel akzeptiert die richtig erstellten Zellen (oder Bezüge?) in der Summenformel nicht.
Zwei Beispiele:

	A	B	C	D	E	F
1	a	a	36	31		0
2	x	a	44	52		0
3	a	x	18	55		120
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

Formeln der Tabelle

Zelle	Formel
F1	{=SUMME(ADRESSE(REST(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($A:$B)999); ZEILE($1:$3)); 999); KÜRZEN(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($C:$D)999); ZEILE($1:$3))/999); 4))}
F2	{=SUMME(ZEICHEN(KÜRZEN(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)999); ZEILE(1:3))/999)+66)&REST(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)999); ZEILE(1:3)); 999))}
F3	=D5+D3+C8

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Oder auch so:

	A	B	C	D	E	F
1	a	a	36	31		0
2	a	a	44	52		0
3	a	x	18	55		1140
4	a	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	x	a	4	45
12	a	x	51	999
13	x	a	50	49
14	x	x	32	100

Formeln der Tabelle

Zelle	Formel
F1	{=SUMME(ADRESSE(REST(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($A:$B)999); ZEILE($1:$3)); 999); KÜRZEN(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($C:$D)999); ZEILE($1:$3))/999); 4))}
F2	{=SUMME(ZEICHEN(KÜRZEN(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)999); ZEILE(1:3))/999)+66)&REST(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)999); ZEILE(1:3)); 999))}
F3	=D14+D12+D5

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Also es sollte immer die Summe in Spalte C:D errechnet werden, abhängig von den letzten Mitarbeiter x in Spalte A:B
Danke für Denkanstöße
Es grüßt Jörg und Kollegen...

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo,
erstmal Danke für die Antworten!
zum Verständnisproblem:
das ertse Beispiel Nach Import original:

	A	B	C	D
1	a	a	36	31
2	x	a	44	52
3	a	x	18	55
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

Und dann wird es von uns so untereinander kopiert:

	A	B	C
1	a		36
2	x		44
3	a		18
4	x		19
5	a		46
6	x		10
7	a		30
8	x		24
9	a		12
10	a		18
11	a		4
12	a		51
13	a		50
14	a		32
15	a		31
16	a		52
17	x		55
18	a		12
19	x		41
20	a		53
21	a		17
22	a		87
23	a		16
24	a		36
25	a		45
26	a		999
27	a		49
28	a		100

Somit können wir dann die letzten drei Werte des Mitarbeiter "x" auswerten!
Bsp. 2:

	A	B	C	D
1	a	a	36	31
2	a	a	44	52
3	a	x	18	55
4	a	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	x	a	4	45
12	a	x	51	999
13	x	a	50	49
14	x	x	32	100

wird zu diesem "zurecht kopiert":

	A	B	C
1	a		36
2	a		44
3	a		18
4	a		19
5	a		46
6	x		10
7	a		30
8	x		24
9	a		12
10	a		18
11	x		4
12	a		51
13	x		50
14	x		32
15	a		31
16	a		52
17	x		55
18	a		12
19	x		41
20	a		53
21	a		17
22	a		87
23	a		16
24	a		36
25	a		45
26	x		999
27	a		49
28	x		100

Es werden also die Spalten hintereinander "verkettet", da die Daten fortlaufend sind, und somit sind die letzten drei "x" eben manchmal verteilt, entweder in beiden Spalten, nur in der ersten oder nur in der zweiten!
Mit Indirekt um die Summe() haben wir das jetzt mal probiert, da gibt die Formel #Bezug! wieder, nur Indirekt() liefert die Formel aber immer nur den Wert vom letzten "x", und vernachlässigt die beiden vorletzten.
Da diese Auswertung mehrmals am Tag kommt, und immer unterschiedlich lang ist, wollten wir halt das manuelle kopieren ersparen und es mit einer von den zwei Formeln berechnen.
Mittlerweile haben wir ein bisschen gegoogelt, aber eine Lösung haben wir noch nicht, dass wir Excel "klarmachen können" die richtigen Textwerte der Formeln auch als Bezug anzunehmen! :-(
Hoffentlich ist jetzt unser Anliegen besser beschrieben, ansonsten versucht sich mal ein Kollege mit der Beschreibung!
Nochmals Danke für die Aufmerksamkeit und evtl. Lösungsansätze!
Jörg und Kollegen

Beliebteste Forumthreads (12 Monate)

	A	B	C	D	E
1	ertses x	a	55	79
2	a	a	18	85
3	zweites x	a	95	19
4	a	a	26	18
5	a	a	97	97
6	a	a	74	92
7	a	viertes x	34	69
8	a	a	47	76
9	a	fünftes x	28	84
10	a	a	87	81
11	a	a	95	50
12	a	sechstes y	68	13
13	drittes x	a	87	79
14	a	siebentes x	92	65	die letzten 3 x
15
16	oder
17
18	ertses x	a	55	79
19	a	a	18	85
20	zweites x	a	95	19
21	a	a	26	18
22	a	a	97	97
23	a	a	74	92
24	a	viertes x	34	69
25	a	a	47	76
26	a	fünftes x	28	84
27	a	a	87	81
28	a	a	95	50
29	a	a	68	13
30	drittes x	a	87	79
31	a	a	92	65	die letzten 3 x
32
33	oder
34
35	ertses x	a	55	79
36	a	a	18	85
37	zweites x	a	95	19
38	a	a	26	18
39	a	a	97	97
40	a	a	74	92
41	a	a	34	69
42	a	a	47	76
43	a	a	28	84
44	a	a	87	81
45	a	a	95	50
46	a	a	68	13
47	drittes x	a	87	79
48	a	a	92	65	die letzten 3 x

	A	B	C	D	E	F
1	a	a	36	31		0
2	x	a	44	52		0
3	a	x	18	55		120
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

	A	B	C	D	E	F	G
1	a		36	31		x	x
2	a	a	44	52		120	1195
3	a	x	18	55
4	a	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x		24	87
9	a	a	12	16
10	a	a	18	36
11	x	a	4	45
12	a	x	51	999
13	x	a	50	49
14	x	x	32	100

	A	B	C	D
1	a	a	36	31
2	x	a	44	52
3	a	x	18	55
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

	A	B	C
1	a		36
2	x		44
3	a		18
4	x		19
5	a		46
6	x		10
7	a		30
8	x		24
9	a		12
10	a		18
11	a		4
12	a		51
13	a		50
14	a		32
15	a		31
16	a		52
17	x		55
18	a		12
19	x		41
20	a		53
21	a		17
22	a		87
23	a		16
24	a		36
25	a		45
26	a		999
27	a		49
28	a		100

	A	B	C	D
1	a	a	36	31
2	a	a	44	52
3	a	x	18	55
4	a	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	x	a	4	45
12	a	x	51	999
13	x	a	50	49
14	x	x	32	100

	A	B	C
1	a		36
2	a		44
3	a		18
4	a		19
5	a		46
6	x		10
7	a		30
8	x		24
9	a		12
10	a		18
11	x		4
12	a		51
13	x		50
14	x		32
15	a		31
16	a		52
17	x		55
18	a		12
19	x		41
20	a		53
21	a		17
22	a		87
23	a		16
24	a		36
25	a		45
26	x		999
27	a		49
28	x		100

	A	B	C	D	E	F
1	a	a	36	31		120
2	x	a	44	52		120
3	a	x	18	55		120
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

	A	B	C	D	E	F
1	a	a	36	31		0
2	x	a	44	52		0
3	a	x	18	55		120
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

	A	B	C	D
1	a	a	36	31
2	x	a	44	52
3	a	x	18	55
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

	A	B	C
1	a		36
2	x		44
3	a		18
4	x		19
5	a		46
6	x		10
7	a		30
8	x		24
9	a		12
10	a		18
11	a		4
12	a		51
13	a		50
14	a		32
15	a		31
16	a		52
17	x		55
18	a		12
19	x		41
20	a		53
21	a		17
22	a		87
23	a		16
24	a		36
25	a		45
26	a		999
27	a		49
28	a		100

	A	B	C	D
1	a	a	36	31
2	a	a	44	52
3	a	x	18	55
4	a	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	x	a	4	45
12	a	x	51	999
13	x	a	50	49
14	x	x	32	100

	A	B	C
1	a		36
2	a		44
3	a		18
4	a		19
5	a		46
6	x		10
7	a		30
8	x		24
9	a		12
10	a		18
11	x		4
12	a		51
13	x		50
14	x		32
15	a		31
16	a		52
17	x		55
18	a		12
19	x		41
20	a		53
21	a		17
22	a		87
23	a		16
24	a		36
25	a		45
26	x		999
27	a		49
28	x		100

	A	B	C	D	E	F
1	a	a	36	31		120
2	x	a	44	52		120
3	a	x	18	55		120
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

	A	B	C	D	E	F
1	a	a	36	31		0
2	x	a	44	52		0
3	a	x	18	55		120
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

	A	B	C	D
1	a	a	36	31
2	x	a	44	52
3	a	x	18	55
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100

	A	B	C
1	a		36
2	x		44
3	a		18
4	x		19
5	a		46
6	x		10
7	a		30
8	x		24
9	a		12
10	a		18
11	a		4
12	a		51
13	a		50
14	a		32
15	a		31
16	a		52
17	x		55
18	a		12
19	x		41
20	a		53
21	a		17
22	a		87
23	a		16
24	a		36
25	a		45
26	a		999
27	a		49
28	a		100

	A	B	C	D
1	a	a	36	31
2	a	a	44	52
3	a	x	18	55
4	a	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	x	a	4	45
12	a	x	51	999
13	x	a	50	49
14	x	x	32	100

	A	B	C
1	a		36
2	a		44
3	a		18
4	a		19
5	a		46
6	x		10
7	a		30
8	x		24
9	a		12
10	a		18
11	x		4
12	a		51
13	x		50
14	x		32
15	a		31
16	a		52
17	x		55
18	a		12
19	x		41
20	a		53
21	a		17
22	a		87
23	a		16
24	a		36
25	a		45
26	x		999
27	a		49
28	x		100

	A	B	C	D	E	F
1	a	a	36	31		120
2	x	a	44	52		120
3	a	x	18	55		120
4	x	a	19	12
5	a	x	46	41
6	x	a	10	53
7	a	a	30	17
8	x	a	24	87
9	a	a	12	16
10	a	a	18	36
11	a	a	4	45
12	a	a	51	999
13	a	a	50	49
14	a	a	32	100