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?
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 |
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.
(21.02.2017, 18:52)neopa schrieb: [ -> ]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()...
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.
(21.02.2017, 19:36)neopa schrieb: [ -> ]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...
Hallo Jörg,
Zitat:... 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
(21.02.2017, 18:52)neopa schrieb: [ -> ]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:
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);"")
(22.02.2017, 10:48)neopa schrieb: [ -> ]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.