Auswertung Einsparung

[MuHA1981]

Hallo zusammen,


ich bin neu hier und hoffe, dass mir jemand helfen kann. Folgendes Problem:

Ich habe hier eine Tabelle mit ca. 1200 Zeilen. Jede Zeile ist ein einzelner Vorgang bzw. eine Bestellung. Nun gibt es folgende Spalten: A - Artikelnummer (diese kommt häufig mehrfach vor, wenn der Artikel mehrfach bestellt wurde), B - Lieferant, C - Bestellmenge, D - Preis pro Stück

Nun will ich Excel dazu bringen, dass es aus dieser Masse an Zahlen eine Tabelle macht wo in Spalte A die Artikelnummer nur einmal steht und ab Spalte B sollen dann die unterschiedlichen Vorgänge stehen.

Ich hab da mal ein Beispiel gebaut. Ich denke, dass das nur mit VBA machbar ist. Hat da wer einen guten Ansatz?

[Jockel]

Hallo, meinst du so..? Aber ich bekomme zum Teil andere Erbegnisse..!?!?

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J
22	Art.-Nr.	Lieferant	Menge	Preis	Lieferant	Menge	Preis	Lieferant	Menge	Preis
23	123456	Alpha	500	1000	Alpha	7000	87	Bravo	2500	356
24	341527	Charlie	78	125
25	412589	Bravo	2500	498
26	562348	Bravo	2500	30
27	987654	Alpha	500	851	Charlie	250	1250	Alpha	5000	385

Zelle	Formel
A23	=MIN(B3:B11)
B23	=SVERWEIS(A23;$B$3:$C$11;2;0)
C23	=INDEX($D$2:$D$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($C$2:$C$11=B23);1))
D23	=INDEX($E$2:$E$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A23);1))
E23	=WENNFEHLER(INDEX($C$2:$C$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A23);2));"")
F23	=WENNFEHLER(INDEX($D$2:$D$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A23);2));"")
G23	=WENNFEHLER(INDEX($E$2:$E$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A23);2));"")
H23	=WENNFEHLER(INDEX($C$2:$C$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A23);3));"")
I23	=WENNFEHLER(INDEX($D$2:$D$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A23);3));"")
J23	=WENNFEHLER(INDEX($E$2:$E$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A23);3));"")
A24	=WENN(MAX($B$3:$B$11)=MAX(A$23:A23);"";KGRÖSSTE($B$3:$B$11;ZÄHLENWENN($B$3:$B$11;">"&A23)))
B24	=SVERWEIS(A24;$B$3:$C$11;2;0)
C24	=INDEX($D$2:$D$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($C$2:$C$11=B24);1))
D24	=INDEX($E$2:$E$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A24);1))
E24	=WENNFEHLER(INDEX($C$2:$C$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A24);2));"")
F24	=WENNFEHLER(INDEX($D$2:$D$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A24);2));"")
G24	=WENNFEHLER(INDEX($E$2:$E$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A24);2));"")
H24	=WENNFEHLER(INDEX($C$2:$C$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A24);3));"")
I24	=WENNFEHLER(INDEX($D$2:$D$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A24);3));"")
J24	=WENNFEHLER(INDEX($E$2:$E$11;AGGREGAT(15;6;ZEILE($A$2:$A$11)-1/($B$2:$B$11=A24);3));"")

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

[neopa]

Hallo,

das geht z.B. mit nur zwei Matrixfunktion(alität)sformeln die im Gegensatz zu echten Matrixformeln keines spez. Formelabschlusses bedürfen, aber wie solche "arbeiten" nur mE einfacher zu konstruieren und schneller auswertend

Bezogen auf Dein Beispieldaten so:

In A15: =WENNFEHLER(AGGREGAT(15;6;B$3:B$12/(ZÄHLENWENN(A$14:A14;B$3:B$12)=0)/(B$3:B$12>0);1);"")

und Formel nach unten ziehend kopieren.

in B15: =WENNFEHLER(INDEX($C:$E;AGGREGAT(15;6;ZEILE(C$3:C$12)/($B$3:$B$12=$A15);KÜRZEN(SPALTE(C1)/3;));1+REST(SPALTE(C1);3));"")

und Formel nach unten und rechts ziehend kopieren.

Bei 1200 Datensätzen könnten es natürlich über 6000 Formeln werden, die natürlich etwas Zeit brauchen werden.

[Jockel]

Hallo,

das geht z.B. mit nur zwei Matrixfunktion(alität)sformeln ...

Hallo Werner, das sehe ich nicht unbedingt so, wenn die Artikelnummern wirklich richtige Zahlen sind, braucht man für deren Auflistung kein AGGREGAT()...

[neopa]

Hallo Jörg,

natürlich könnte man die Auflistung der duplettenfreien Artikelnummern auch ohne AGGREGAT() mit nur einer Formel lösen (man könnte die Artikelnummern auch ganz ohne Formel mit PIVOT lösen, wäre aber hier mE ein "Stilbruch").
Für mich persönlich war/ist es aber so die einfachste Lösung (außerdem hatte ich erst vor, die ganze Tabelle anstelle mit zwei mit nur einer Formel zu lösen, was ich dann aber verworfen habe, weil   so eine schnellere Auswertung möglich sein dürfte)

In Deiner Formel für Spalte C  hast Du noch einen inkorrekten Bezug , so dass Du da Abweichungen von der Vorgabe hast.

[Jockel]

Hallo Jörg,
...
In Deiner Formel für Spalte C  hast Du noch einen inkorrekten Bezug , so dass Du da Abweichungen von der Vorgabe hast.

Hi Werner, jupp habe ich später auch bemerkt, das war mit heißer Nadel gestrickt... ... nun habe ich aber keine Lust das Ganze nochmal zu ändern - das sollte {bei Interesse} der TE hinkriegen...

[neopa]

Hallo Jörg,

... nun habe ich aber keine Lust das Ganze nochmal zu ändern -

brauchst Du ja auch nicht, der TE kann ja auch gleich auf meine 2 Formel-Lösung zurückgreifen

[MuHA1981]

Hallo,

das geht z.B. mit nur zwei Matrixfunktion(alität)sformeln die im Gegensatz zu echten Matrixformeln keines spez. Formelabschlusses bedürfen, aber wie solche "arbeiten" nur mE einfacher zu konstruieren und schneller auswertend

Bezogen auf Dein Beispieldaten so:

In A15: =WENNFEHLER(AGGREGAT(15;6;B$3:B$12/(ZÄHLENWENN(A$14:A14;B$3:B$12)=0)/(B$3:B$12>0);1);"")

und Formel nach unten ziehend kopieren.

in B15: =WENNFEHLER(INDEX($C:$E;AGGREGAT(15;6;ZEILE(C$3:C$12)/($B$3:$B$12=$A15);KÜRZEN(SPALTE(C1)/3;));1+REST(SPALTE(C1);3));"")

und Formel nach unten und rechts ziehend kopieren.

Bei 1200 Datensätzen könnten es natürlich über 6000 Formeln werden, die natürlich etwas Zeit brauchen werden.

Hallo zusammen und vielen Dank für die Antworten. Im Beispiel klappt das auch ganz super. :18:  Jetzt hab ich versucht, das Ganze einfach unter meine Liste, also in Zelle A1171 zu beginnen und die Formel umgebaut in:

=WENNFEHLER(AGGREGAT(15;6;B$2:B$1167/(ZÄHLENWENN(A$2:A2;B$2:B$1167)=0)/(B$2:B$1167>0);1);"")

Leider funktioniert das dann nicht mehr. Beim runterziehen wird mir immer die gleiche Artikelnummer angezeigt. :16:

[neopa]

Hallo,

Deine Formelanpassung war auch nicht korrekt. Richtig muss es in A1171 so lauten:

=WENNFEHLER(AGGREGAT(15;6;B$2:B$1167/(ZÄHLENWENN(A$1170:A1170;B$2:B$1167)=0)/(B$2:B$1167>0);1);"")

[MuHA1981]

Hallo,

Deine Formelanpassung war auch nicht korrekt. Richtig muss es in A1171 so lauten:

=WENNFEHLER(AGGREGAT(15;6;B$2:B$1167/(ZÄHLENWENN(A$1170:A1170;B$2:B$1167)=0)/(B$2:B$1167>0);1);"")

Danke auch hierfür noch mal. Das hat super geklappt. Problem ist wohl, dass ich nicht ganz verstehe, was die Formel macht. Selbstverständlich habe ich auch gleich versucht, die zweite Formel dann in Zelle B1171 zu bauen. Leider komme ich auch hier nicht weiter. Hier mein Entwurf:

=WENNFEHLER(INDEX($C:$E;AGGREGAT(15;6;ZEILE(C$2:C$1167)/($B$2:$B$1167=$A2);KÜRZEN(SPALTE(C1)/3;));1+REST(SPALTE(C1);3));"")

Die Formel wird zwar gefressen, die Zelle(n) bleiben aber leer. Tut mir echt leid, dass ich so nervig bin.