Stückliste zusammenfassen im Excel

quack83 · 12. November 2019 um 07:07

Hallo zusammen!

Ich brüte gerade über einem Probelm, dessen Lösung vielleicht ganz einfach ist, aber ich komme einfach nicht darauf.

Es geht um folgendes, aus unseren verschiedenen Programmen werden Stücklisten erstellt, allerdings fassen diese die Artikelnummern die öfter vorkommen nicht gleich zusammen. Mein Kollege hat mich jetzt damit beauftragt etwas zu „zaubern“. Es soll so sein das die Stückliste in die Tabelle1 kopiert wird, dann soll alles mit der gleichen Artikelnummer zusammengefasst werden, das heißt in der Tabelle2 steht dann nur noch die Artikelnummer, die Bezeichnung, die gesamte Menge und der Hersteller.

Also In Tabelle1 steht

Art-Nr Bezeichnung Menge Hersteller
1234 Hundekuchen 1 dfgjhk
2345 Katzenfutter 2 l-asjrf
3456 Vogelfutter 3 ldiegs
1234 Hundekuchen 4 dfgjhk
3456 Vogelfutter 1 ldiegs

Und in Tabelle 2 soll stehen
Art-Nr Bezeichnung Menge Hersteller
1234 Hundekuchen 5 dfgjhk
2345 Katzenfutter 2 l-asjrf
3456 Vogelfutter 4 ldiegs

Es wäre schön wenn jemand von euch eine Lösung mit Formeln wüsste, denn von VBA und Makros habe ich keinen Plan.

Vielen Dank für eure Hilfe.

Liebe Grüße
quack

Reinhard_fb8cb7 · 12. November 2019 um 07:07

Liste ohne Doppelte

soll so sein das die Stückliste in die Tabelle1 kopiert wird,
dann soll alles mit der gleichen Artikelnummer zusammengefasst
werden, das heißt in der Tabelle2 steht dann nur noch die
Artikelnummer, die Bezeichnung, die gesamte Menge und der
Hersteller.

Hallo Quack,

Tabellenblatt: [Mappe1]!Tabelle2
 │ A │ B │ C │ D │
──┼────────┼──────────────┼───────┼────────────┤
1 │ Art-Nr │ Bezeichnung │ Menge │ Hersteller │
──┼────────┼──────────────┼───────┼────────────┤
2 │ 1234 │ Hundekuchen │ 5 │ dfgjhk │
──┼────────┼──────────────┼───────┼────────────┤
3 │ 2345 │ Katzenfutter │ 2 │ l-asjrf │
──┼────────┼──────────────┼───────┼────────────┤
4 │ 3456 │ Vogelfutter │ 4 │ ldiegs │
──┼────────┼──────────────┼───────┼────────────┤
5 │ │ │ │ │
──┼────────┼──────────────┼───────┼────────────┤
6 │ │ │ │ │
──┴────────┴──────────────┴───────┴────────────┘
Benutzte Formeln:
A1: =Tabelle1!A1

B1: =Tabelle1!B1
B2: =WENN($A2="";"";SVERWEIS($A2;Tabelle1!$A:blush:D;SPALTE();0))
B3: =WENN($A3="";"";SVERWEIS($A3;Tabelle1!$A:blush:D;SPALTE();0))
B4: =WENN($A4="";"";SVERWEIS($A4;Tabelle1!$A:blush:D;SPALTE();0))
B5: =WENN($A5="";"";SVERWEIS($A5;Tabelle1!$A:blush:D;SPALTE();0))
B6: =WENN($A6="";"";SVERWEIS($A6;Tabelle1!$A:blush:D;SPALTE();0))

C1: =Tabelle1!C1
C2: =WENN($A2="";"";SUMMEWENN(Tabelle1!A:A;A2;Tabelle1!C:C))
C3: =WENN($A3="";"";SUMMEWENN(Tabelle1!A:A;A3;Tabelle1!C:C))
C4: =WENN($A4="";"";SUMMEWENN(Tabelle1!A:A;A4;Tabelle1!C:C))
C5: =WENN($A5="";"";SUMMEWENN(Tabelle1!A:A;A5;Tabelle1!C:C))
C6: =WENN($A6="";"";SUMMEWENN(Tabelle1!A:A;A6;Tabelle1!C:C))

D1: =Tabelle1!D1
D2: =WENN($A2="";"";SVERWEIS($A2;Tabelle1!$A:blush:D;SPALTE();0))
D3: =WENN($A3="";"";SVERWEIS($A3;Tabelle1!$A:blush:D;SPALTE();0))
D4: =WENN($A4="";"";SVERWEIS($A4;Tabelle1!$A:blush:D;SPALTE();0))
D5: =WENN($A5="";"";SVERWEIS($A5;Tabelle1!$A:blush:D;SPALTE();0))
D6: =WENN($A6="";"";SVERWEIS($A6;Tabelle1!$A:blush:D;SPALTE();0))

Benutzte Matrixformeln:
A2: {=WENN(SUMME(ZÄHLENWENN(Tabelle1!A$1:A$99;A$1:A1))\>=SUMME((Tabelle1!A$1:A$99"")\*1);"";INDEX(Tabelle1!A:A;VERGLEICH(1;(ZÄHLENWENN(A$1:A1;Tabelle1!A$1:A$99)=0)\*(Tabelle1!A$1:A$99"");0)))}
A3: {=WENN(SUMME(ZÄHLENWENN(Tabelle1!A$1:A$99;A$1:A2))\>=SUMME((Tabelle1!A$1:A$99"")\*1);"";INDEX(Tabelle1!A:A;VERGLEICH(1;(ZÄHLENWENN(A$1:A2;Tabelle1!A$1:A$99)=0)\*(Tabelle1!A$1:A$99"");0)))}
A4: {=WENN(SUMME(ZÄHLENWENN(Tabelle1!A$1:A$99;A$1:A3))\>=SUMME((Tabelle1!A$1:A$99"")\*1);"";INDEX(Tabelle1!A:A;VERGLEICH(1;(ZÄHLENWENN(A$1:A3;Tabelle1!A$1:A$99)=0)\*(Tabelle1!A$1:A$99"");0)))}
A5: {=WENN(SUMME(ZÄHLENWENN(Tabelle1!A$1:A$99;A$1:A4))\>=SUMME((Tabelle1!A$1:A$99"")\*1);"";INDEX(Tabelle1!A:A;VERGLEICH(1;(ZÄHLENWENN(A$1:A4;Tabelle1!A$1:A$99)=0)\*(Tabelle1!A$1:A$99"");0)))}
A6: {=WENN(SUMME(ZÄHLENWENN(Tabelle1!A$1:A$99;A$1:A5))\>=SUMME((Tabelle1!A$1:A$99"")\*1);"";INDEX(Tabelle1!A:A;VERGLEICH(1;(ZÄHLENWENN(A$1:A5;Tabelle1!A$1:A$99)=0)\*(Tabelle1!A$1:A$99"");0)))}

(Matrixformeln nicht mit "Enter" sondern mit "Strg+Shift+Enter" eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von Excel erzeugt.)

A1:smiley:6
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

Thomas_Ramel · 12. November 2019 um 07:07

Grüezi Quack

Also In Tabelle1 steht

Art-Nr Bezeichnung Menge Hersteller
1234 Hundekuchen 1 dfgjhk
2345 Katzenfutter 2 l-asjrf
3456 Vogelfutter 3 ldiegs
1234 Hundekuchen 4 dfgjhk
3456 Vogelfutter 1 ldiegs

Und in Tabelle 2 soll stehen
Art-Nr Bezeichnung Menge Hersteller
1234 Hundekuchen 5 dfgjhk
2345 Katzenfutter 2 l-asjrf
3456 Vogelfutter 4 ldiegs

Es wäre schön wenn jemand von euch eine Lösung mit Formeln
wüsste, denn von VBA und Makros habe ich keinen Plan.

Markiere einfach die 4 Spalten in der ersten Tabelle und erstelle damit eine Pivot-Tabelle. Dort dann die Menge in den Datenbereich und die anderen 3 Felder in den Zeilenbereich, dann sieht das so aus:

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
 1 │ Art-Nr │ Bezeichnung │ Menge │ Hersteller │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
 2 │ 1234 │ Hundekuchen │ 1 │ dfgjhk │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
 3 │ 2345 │ Katzenfutter │ 2 │ l-asjrf&nbsp; │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
 4 │ 3456 │ Vogelfutter │ 3 │ ldiegs │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
 5 │ 1234 │ Hundekuchen │ 4 │ dfgjhk │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
 6 │ 3456 │ Vogelfutter │ 1 │ ldiegs │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
 7 │ │ │ │ │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
 8 │ │ │ │ │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
 9 │ │ │ │ │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
10 │ Summe von Menge │ │ │ │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
11 │ Art-Nr │ Bezeichnung │ Hersteller │ Ergebnis │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
12 │ 1234 │ Hundekuchen │ dfgjhk │ 5 │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
13 │ 2345 │ Katzenfutter │ l-asjrf&nbsp; │ 2 │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
14 │ 3456 │ Vogelfutter │ ldiegs │ 4 │ │
───┼─────────────────┼──────────────┼────────────┼────────────┼───┤
15 │ │ │ │ │ │
───┴─────────────────┴──────────────┴────────────┴────────────┴───┘

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

quack83 · 12. November 2019 um 07:08

Hallo Thomas,

das mit der Pivottabelle habe ich jetzt zusammen bekommen. Wenn ich icn der Ursprungsdatei jetzt etwas ändere und in der Pivottabelle auf Aktualisieren gehe wird das aber nicht übernommen.

Zum Beispiel wenn ich in der Ursprungstabelle angebe das ein Hundefutter dazugekommen ist bei einer der Positionen und gehe dann in die Pivottabelle und gehe mit Klick auf die rechte Maustaste auf Aktualisieren ändert sich das leider nicht.

Woran kann das liegen?

Ich arbeite mit Word 2010 muss das ganze aber als Microsoft Excel 97-2003-Arbeitsblatt abspeichern, da es manche Kollegen sonst nicht öffnen können.

Vielen Dank im voraus.

Viele Grüße
quack

Thomas_Ramel · 12. November 2019 um 07:08

Grüezi Quack

das mit der Pivottabelle habe ich jetzt zusammen bekommen.

Fein

Wenn ich icn der Ursprungsdatei jetzt etwas ändere und in der
Pivottabelle auf Aktualisieren gehe wird das aber nicht
übernommen.

Zum Beispiel wenn ich in der Ursprungstabelle angebe das ein
Hundefutter dazugekommen ist bei einer der Positionen und gehe
dann in die Pivottabelle und gehe mit Klick auf die rechte
Maustaste auf Aktualisieren ändert sich das leider nicht.

Woran kann das liegen?

Wie gross ist denn der Quellbereich der PT gewählt?

Standardmässig wird da automatisch der Bereich definiert der zum Zeitpunkt als die PT erstellt wird an Daten vorhanden ist.
Kommen dann neue Daten hinzu liegen diese ausserhalb des Bereiches und werden daher nicht aktualisiert.

Darum meinte ich, du sollest die ersten 4 Spalten komplett als Datenbereich erfassen.
Damit ergeben sich dann zwar leere Einträge in der PT, die jedoch über die Filter ausgeblendet werden können.

Passe daher den Quellbereich mal auf die kompletten Spalten an, dann sollte das auch mit dem Aktualisieren klappen.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

quack83 · 12. November 2019 um 07:08

Hallo Thomas,

jetzt hats klick gemacht.

Ich muss also unten weiterschreiben und nicht einfach in der Tabelle aus einer 1 z.B. eine 2 machen.

Vielen Dank!!!

Viele Grüße

quack

Thomas_Ramel · 12. November 2019 um 07:08

Grüezi Quack

Ich muss also unten weiterschreiben

Ja, das gilt für Daten, die neu dazu kommen…

und nicht einfach in der Tabelle aus einer 1 z.B. eine 2 machen.

…und dies für bereits bestehende Daten sie sich verändern (das sollte aber in der Regel ja nicht der Fall sein).

Beides wird von der PT dann beim Aktualisieren berücksichtigt (halt auch je nach Festlegung der Aggregierungsfunktionen im Wertebereich).

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -