Office: (Office 2007) ... ist Summenprodukt die richtige Vorgehensweise ...

Guten Morgen allerseits,

ich habe da ein Problem, welches ich nicht so richtig beschreiben kann und wo ich schon einige Zeit mit verbracht habe. *frown.gif*

Ich habe eine Tabelle mit ca. 1.000 PNr. Diese PNr. (kommen) können mehrfach vorkommen.
Nun möchte ich pro PNr. (für das Jahr 2009) eine Summe errechnen, welche dann in einer separaten Tabelle (mit weiteren Angaben) aufgelistete werden soll.
Anbei mal ein Auszug zum besseren Verständnis.

Habe es schon mit Summenprodukt versucht, aber ohne Erfolg.

Hat jemand eine Idee?
Besten Dank vorab

:)

 

Moin,

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	IST-Zustand	*	*	*	*	*	*	*	*	*	SOLL	*	*	*	*	*
2	Bereich	Nr.	Jahr	Art	Anspruch	Verbrauch	Rest1	geplant	Rest2	*	Bereich	Nr.	Jahr	Rest1	geplant	Rest2
3	A	4711	2007	TU	30	30	0	0	0	*	A	4711	2009	32	0	32
4	A	4711	2008	TU	30	28	2	0	2	*	A	4712	2009	35	1	34
5	A	4711	2009	TU	30	0	30	0	30	*	B	4813	2009	50	7	43
6	A	4712	2008	TU	30	25	5	1	4	*	*	*	*	*	*	*
7	A	4712	2009	TU	30	0	30	0	30	*	*	*	*	*	*	*
8	B	4813	2007	TU	30	30	0	0	0	*	*	*	*	*	*	*
9	B	4813	2007	TU	30	30	0	0	0	*	*	*	*	*	*	*
10	B	4813	2008	TU	30	15	15	0	15	*	*	*	*	*	*	*
11	B	4813	2009	TU	30	0	30	2	28	*	*	*	*	*	*	*
12	B	4813	2009	SU	5	0	5	5	0	*	*	*	*	*	*	*

Zelle	Formel
N3	=SUMMENPRODUKT(($A:$A=K3)*($B:$B=L3)*$G:$G)
O3	=SUMMENPRODUKT(($A:$A=K3)*($B:$B=L3)*$H:$H)
P3	=SUMMENPRODUKT(($A:$A=K3)*($B:$B=L3)*$I:$I)
N4	=SUMMENPRODUKT(($A:$A=K4)*($B:$B=L4)*$G:$G)
O4	=SUMMENPRODUKT(($A:$A=K4)*($B:$B=L4)*$H:$H)
P4	=SUMMENPRODUKT(($A:$A=K4)*($B:$B=L4)*$I:$I)
N5	=SUMMENPRODUKT(($A:$A=K5)*($B:$B=L5)*$G:$G)
O5	=SUMMENPRODUKT(($A:$A=K5)*($B:$B=L5)*$H:$H)
P5	=SUMMENPRODUKT(($A:$A=K5)*($B:$B=L5)*$I:$I)

Oder evtl. als Pivot

*	A	B	C	D	E
3	*	*	Daten	*	*
4	Bereich	Nr.	Summe - Rest1	Summe - geplant	Summe - Rest2
5	A	4711	32	0	32
6	*	4712	35	1	34
7	A Ergebnis	*	67	1	66
8	B	4813	50	7	43
9	B Ergebnis	*	50	7	43
10	Gesamtergebnis	*	117	8	109

 

Moin Thomas,

vielen Dank für die schnelle Hilfestellung!
So ähnlich hatte ich es (für die Berechnung) auch probiert, nur ohne Erfolg.

Mein Problem geht aber noch weiter. *frown.gif*
Die Basisdaten in der NEUEN Tabelle wie Bereich, Nr. und Jahr sind so nicht vorgegeben, sondern müssen erst aus der Basisliste übernommen werden.
Kannst du mir da auch weiterhelfen?

Besten Dank!

PS:
Die Pivot-Lösung kommt hier leider nicht zum tragen, da ich mit den Werten der einzellnen PNr. und der Bereiche noch weiter rechnen muss!

 

Hi,

Ich würde mir Bereich und Nr. per Spezialfilter ohne Duplikate ermitteln lassen.

 

Hallo Thomas,

...per Spezialfilter...!?

Ich hatte einen Lösungsweg über BereichVerschieben versucht.
Ist dies (auch) (nicht) möglich?

Kannst du mir da BITTE unter die Arme greifen?!

Danke *Smilie

 

Hi,

hier mal ein vorschlag mit Hilfsspalte

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	*	IST-Zustand	*	*	*	*	*	*	*	*	*	*	SOLL	*	*	*	*
2	*	Bereich	Nr.	Jahr	Art	Anspruch	Verbrauch	Rest1	geplant	Rest2	*	*	Bereich	Nr.	Jahr	Rest1	geplant
3	A4711	A	4711	2007	TU	30	30	0	0	0	*	A4711	A	4711	2009	32	0
4	A4711	A	4711	2008	TU	30	28	2	0	2	*	A4712	A	4712	2009	35	1
5	A4711	A	4711	2009	TU	30	0	30	0	30	*	B4813	B	4813	2009	50	7
6	A4712	A	4712	2008	TU	30	25	5	1	4	*	*	*	*	*	*	*
7	A4712	A	4712	2009	TU	30	0	30	0	30	*	*	*	*	*	*	*
8	B4813	B	4813	2007	TU	30	30	0	0	0	*	*	*	*	*	*	*
9	B4813	B	4813	2007	TU	30	30	0	0	0	*	*	*	*	*	*	*
10	B4813	B	4813	2008	TU	30	15	15	0	15	*	*	*	*	*	*	*
11	B4813	B	4813	2009	TU	30	0	30	2	28	*	*	*	*	*	*	*
12	B4813	B	4813	2009	SU	5	0	5	5	0	*	*	*	*	*	*	*

Zelle	Formel
A3	=B3&C3
L3	{=INDIREKT("A"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A;;;ZEILE(:0));A:A0)=1;ZEILE(:0));ZEILE(A1)))}
M3	=SVERWEIS(L3;$A:$B;2;0)
N3	=SVERWEIS(L3;$A:$C;3)
O3	{=MAX(($A:$A=L3)*$D:$D)}
P3	=SUMMENPRODUKT(($B:$B=M3)*($C:$C=N3)*$H:$H)
Q3	=SUMMENPRODUKT(($B:$B=M3)*($C:$C=N3)*$I:$I)
A4	=B4&C4
L4	{=INDIREKT("A"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A;;;ZEILE(:0));A:A0)=1;ZEILE(:0));ZEILE(A2)))}
M4	=SVERWEIS(L4;$A:$B;2;0)
N4	=SVERWEIS(L4;$A:$C;3)
O4	{=MAX(($A:$A=L4)*$D:$D)}
P4	=SUMMENPRODUKT(($B:$B=M4)*($C:$C=N4)*$H:$H)
Q4	=SUMMENPRODUKT(($B:$B=M4)*($C:$C=N4)*$I:$I)
A5	=B5&C5
L5	{=INDIREKT("A"&KKLEINSTE(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A;;;ZEILE(:0));A:A0)=1;ZEILE(:0));ZEILE(A3)))}
M5	=SVERWEIS(L5;$A:$B;2;0)
N5	=SVERWEIS(L5;$A:$C;3)
O5	{=MAX(($A:$A=L5)*$D:$D)}
P5	=SUMMENPRODUKT(($B:$B=M5)*($C:$C=N5)*$H:$H)
Q5	=SUMMENPRODUKT(($B:$B=M5)*($C:$C=N5)*$I:$I)
A6	=B6&C6
A7	=B7&C7
A8	=B8&C8
A9	=B9&C9
A10	=B10&C10
A11	=B11&C11
A12	=B12&C12

Achtung, Matrixformel enthalten!

<span>Die geschweiften Klammern{} werden </span><span style='text-decoration:underline'>nicht</span><span> eingegeben.</span>

<span>Verlassen Sie den Zelleneditor mit </span><span style='font-weight:bold; color:#FF0000'>Strg+Shift + Enter</span><span>, statt Enter alleine.</span>

 

Hallo Thomas,

vielen Dank für DEINE Bemühungen, ist echt *top
Deinen Lösungsweg habe ich noch etwas angepasst, da ich ohne Hilfspalte arbeiten muss!
Anbei die Lösung, für alle die es interessiert.

Noch mal vielen Dank! *Smilie

 

Hallo allerseits,

ich noch mal *wink.gif*

Habe jetzt mal den Produktivlauf mit über 3.500 Zeilen und mehr als 800 verschiedene PNr. gestartet.
Ergebnis war ein Komplettabsturz von Excel! *frown.gif*

Nach diversen Recherchen habe ich jetzt gelesen, dass die Formel Indirekt(... sich jedesmal neu berechnet, was zu Folge haben kann, dass die Rechenleistung teilweise nicht ausreicht!
Ich gehe davon aus, dass dieser Fall bei mir eingetreten ist ...

... und daher meine Frage in die Runde,
gibt es noch Alternativen die zum Ergebnis führen?

In voller Hoffnung verbleibe ich
bis später

 

Hi,

ich denke mal, daß das weniger an INDIREKT liegt, als daran, dass hier mit Matrixformeln gearbeitet wird und die sind sehr rechenintensiv

 

Hallo Thomas,

...ich gehe dann mal davon aus, dass es sich hier um eine unglückliche Konstellation handelt!

Gibt es eine Lösung ohne Matrixformel und/oder INDIREKT ?

 

Hi,

versuch das mal. Die Änderung betrifft jetzt nur die Spalte L und sollte eigentlich schneller laufen

*	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	IST	*	*	*	*	*	*	*	*	*	SOLL	*	*	*	*	*
2	Bereich	Nr.	Jahr	Art	Anspruch	Verbrauch	Rest1	geplant	Rest2	*	Bereich	Nr.	Jahr	Rest1	geplant	Rest2
3	A	4711	2007	TU	30	30	0	0	0	*	A	4711	2009	32	0	32
4	A	4711	2008	TU	30	28	2	0	2	*	C	4712	2009	35	1	34
5	A	4711	2009	TU	30	0	30	0	30	*	B	4813	2009	50	7	43
6	C	4712	2008	TU	30	25	5	1	4	*	*	*	*	*	*	*
7	C	4712	2009	TU	30	0	30	0	30	*	*	*	*	*	*	*
8	B	4813	2007	TU	30	30	0	0	0	*	*	*	*	*	*	*
9	B	4813	2007	TU	30	30	0	0	0	*	*	*	*	*	*	*
10	B	4813	2008	TU	30	15	15	0	15	*	*	*	*	*	*	*
11	B	4813	2009	TU	30	0	30	2	28	*	*	*	*	*	*	*
12	B	4813	2009	SU	5	0	5	5	0	*	*	*	*	*	*	*

Zelle	Formel
K3	=INDEX(A:A;VERGLEICH(L3;B:B;0))
L3	=MIN(B:B)
M3	{=MAX(($B:$B=L3)*$C:$C)}
N3	=SUMMENPRODUKT(($B:$B=L3)*$G:$G)
O3	=SUMMENPRODUKT(($B:$B=L3)*$H:$H)
P3	=SUMMENPRODUKT(($B:$B=L3)*$I:$I)
K4	=INDEX(A:A;VERGLEICH(L4;B:B;0))
L4	=WENN(MAX(B:B)=MAX(L:L3);"";KGRÖSSTE(B:B;ZÄHLENWENN(B:B;">"&L3)))
M4	{=MAX(($B:$B=L4)*$C:$C)}
N4	=SUMMENPRODUKT(($B:$B=L4)*$G:$G)
O4	=SUMMENPRODUKT(($B:$B=L4)*$H:$H)
P4	=SUMMENPRODUKT(($B:$B=L4)*$I:$I)
K5	=INDEX(A:A;VERGLEICH(L5;B:B;0))
L5	=WENN(MAX(B:B)=MAX(L:L4);"";KGRÖSSTE(B:B;ZÄHLENWENN(B:B;">"&L4)))
M5	{=MAX(($B:$B=L5)*$C:$C)}
N5	=SUMMENPRODUKT(($B:$B=L5)*$G:$G)
O5	=SUMMENPRODUKT(($B:$B=L5)*$H:$H)
P5	=SUMMENPRODUKT(($B:$B=L5)*$I:$I)

Achtung, Matrixformel enthalten!

<span>Die geschweiften Klammern{} werden </span><span style='text-decoration:underline'>nicht</span><span> eingegeben.</span>

<span>Verlassen Sie den Zelleneditor mit </span><span style='font-weight:bold; color:#FF0000'>Strg+Shift + Enter</span><span>, statt Enter alleine.</span>

 

Hallo Thomas,

Dank DEINER "kleinen" Änderung/Anpassung läuft die Berechnung jetzt rund! *biggrin.gif*
Ich bekomme keine Abstürze mehr!

Manchmal ist einfach einfach einfacher *mrcool

Besten Dank!!!!