Excel-Mehrfachnennungen addieren & auflisten

Computer: Software & Programmierung Office Software
#1

Hallo liebe WER-WEISS-WAS community!
Für die Arbeite möchte ich gerne eine excel-Tabelle erstellen mit zwei Blättern. Auf dem ersten Blatt liste ich Namen auf mit den dazugehörigen Warengruppen und deren Anzahl:
NAME A - Warengruppe eins - 45
NAME A - Warengruppe zwölf- 4
NAME B - Warengruppe drei - 67
NAME B - Warengruppe vier - 3
NAME B - Warengruppe eins - 4
NAME C - Warengruppe eins - 1

Auf dem zweiten Blatt sollen dann automatisch alle Warengruppen nur noch einmal aufgelistet werden und dahinter die zusammengezählte Werte. Diese aber dann aber mal 7 (Anzahl der Wochentage):
Warengruppe ein - 350 (eben 45+4+1 und dann *7)
Warengruppe drei - 67
Warengruppe zwölf - 4
usw…

Wäre schön, wenn es nicht ganz so schwer gehen würde, also über eine „=Funktion“
Danke Euch, Gruß Claus

#2

Hallo Claus,
es ist einfach, wenn Deine Ursprungstabelle immer gleich ist, d.h. die Namen und Warengruppen sind unveränderlich. Aber ich vermute mal, das dem nicht so ist. Dann wirst Du um ein kleines VB-Script nicht umhin können. Eine Funktion, die die Seite überwacht, fällt mir dazu nicht ein.

Viel Glück
Horst

#3

Hallo lieber Wollitzer ,
ich fürchte meine Excel Kenntnisse reichen hier nicht aus. Wenn du auf der zweiten Tabelle auch die Detailinformation haben willst, wird es schwierig. Sofern nur die Warengruppe und deren Anzahl der Produkte * 7 benötigst, wäre es mit der Formel =Summewenn(Bereich; Suchkriterien;Summenbereich)machbar.
Dafür müsstest Du aber sämtliche Warengruppen auf der zweiten Tabelle einmal definieren, da sie der Bezug für die Tabelle 1 sein sollen.
Formel für den Summenbezug
Spalte B2 =SUMMEWENN(Tabelle1!C2:blush:D$7;$A$2;Tabelle1!$D$2:blush:D$7)
Spalte B3 =SUMMEWENN(Tabelle1!C3:blush:D$7;$A$2;Tabelle1!$D$2:blush:D$7)
um nun die Mulitplikation mit 7 vorzunehmen definierst du einmal die Zahl 7 in Spalte
=G2*$H$1

Spalte A SpalteB SpalteC
Tabelle 2 7
Warengruppe 1 350 2450
Warengruppe 12 4 28
Warengruppe 3 67 469
Warengruppe 4 3 21
Warengruppe 1 50 350
Warengruppe 1 50 350

Bei der Tabelle 1 müsste bei meiner Formel aber die Warengruppen getrennt in der Spalte 3 stehen.
Hoffe du kommst mit meinen Erläuterungen klar. Leider wurde in meiner Aufstellung die Formatierung total verschoben hoffe aber du kommst trotzdem klar damit.
Viel Glück
Haupt

#4

Warengruppe ein - 350 (eben 45+4+1 und dann *7)
Warengruppe drei - 469
Warengruppe zwölf - 28
muß es natürlich heißen, wegen der mal 7 eben

#5

Excel-Mehrfachnennungen addieren & auflisten
=INDEX(B$2:B99;MIN(WENN(B1:B99"";ZEILE(1:99))))
=SUMMEWENN($B$2:blush:B$100;D1;$C$2:blush:C$100)
Aber mit den Funktionen werden die Warengruppen noch mit Mehrfachnennung angezeigt, aber die Werte werden zusammengezählt.
Wie schaffe ich es, diese Funktionen zu ändern, sodaß jede Warengruppe bei Mehrfachnennung nur noch einmal angezeigt wird; alle Werte trotzdem noch in der nächsten Spalte zusammengezählt angezeigt werden UND vor allem, wie kann ich diese letzt genannte Tabelle auf einem zweiten Tabellenblatt angezeigt bekommen :smile: ?

#6

Hallo Claus

Ich würde folgendes vorschlagen:

Dein erstes Blatt, mit den Werten, die Du Sumieren willst, nenne ich einfachhalthalber „Daten“
Das zweite Blatt „Auswertung“.

Du machst ein Drittes Blatt mit Namen „Listen“.

In dieses Blatt nimmst Du einen Bereich, in den Du die Namen, die Du den Warengruppen geben möchtest, hinterlegst.
B4 = Namen_A
B5 = Namen_B
B6 = Namen_C
und so weiter.

Dies dient als Grundlage für eine Auswahlliste (damit Excel später weiß, wass es summieren soll)
Du markierst Dir den Bereich (B4:B5 in diesen Beispiel)
Und vergibst einen Namen:
Drücke die Taste Strg + F3 (der Bereich muss aber noch markiert sein!)
Nun vergibst Du den Namen „Listen100.Namen“
Jetzt bitte mit OK bestättigen

Jetzt bitte in das Blatt „Daten“
Markiere die Spalte, in die Du deine Namen schreiben möchtest (am besten nicht die ganze Spalte, dass führt später zu Problemen, wenn Du eine Zeile einfügen möchtest). Sagen wir Spalte D
Nun gehst Du auf das Hauptmenü „Daten“
Datengültigkeit
Karteikarte „Einstellungen“
Bei „Zulassen“ wählst Du „Liste“ aus.
Jetzt mit dem Curser in „Quelle“ gehen.
Taste F3 klicken und den Namen auswhählen.
Nun hast Du eine Auswahlliste erstellt.
Wenn Du nun in D5 gehst, kannst Du den Namen aus deiner Liste auswählen, die Du erstellt hast.
In die nächsten Spalten schreibst Du deine anderen werte.
Spalte D = deine Namen einschreiben
Spalte E = Deine Warengruppe (z.B. Warengruppe eins)
Spalte F = Deine zu Summierenden Werte (45 usw.)

Den ganzen Bereich (wegen mir D5: F300) markierst Du wieder und vergibst auch hier einen Namen. =„Daten100.Gesamt“
In Spalte F (F5:F300) stehen deine Werte, die Du zusammen zählen willst.
Bitte auch hier einen Namen vergeben =„Daten200.Werte“

Nun gehst Du ins Blatt „Auswertung“
Auch machst Du Dir eine Hilfspalte, Spalte B

Du Markierst den Bereich B5:B20 und schreibst (während der Bereich markiert ist) „=B4+1“ und drückst Strg + Enter.
Nun hast Du die Zahlen 1 - 15 dort stehen
Du markierst C5:C20 und schreibst in C5 „Index(Listen100.Namen;$B5;1)“ auch während der Bereich markiert ist. Wieder Strg + Enter.

Nun D5:smiley:20 markieren und folgende Formel:
„=(Summewenn(Daten100.Gesamt;$B5;Daten200.Werte))*7“
Auch wieder Strg + Enter.

Übrigens: Vorhandene Namen kannst Du ganz einfach in eine Formel einfügen: Taste F3. Hier werden Dir alle vergebenen Namen angezeigt. Nun den namen auswählen, den eingefügt werden soll, und mit OK bestättigen.

Hoffe, ich konnte Dir weiter helfen.

#7

Sorry, aber mit der u.g. Funktion habe ich selbst noch keine Erfahrung. Ändert sich die Warengruppe denn dauernd oder ist eine feste Warengruppe? Wenn eine feste WG besteht, gebe sie einfach auf dem zweiten Tabellenblatt vor und wende dann in der Spalte daneben einfach meine u.g. Formel an. Sollte es wechselnde Warengruppen geben musst du dich leider an einen Experten mit Visual Basic Erfahrung wenden. Denn ich denke so einfach ist es dann nicht in Excel.

#8

Hallo Claus,
das sollte mit der Funktion SVERWEIS funktionieren.
Syntax: SVERWEIS(Suchkriterium; Matrix; Spaltenindex; :[Bereich_Verweis])

Gruß - Wolfgang

#9

Hallo Claus,
mit einer Pivot Tabelle ginge es auch noch.
Gruß - Wolfgang

#10

Hallo, da Du explizit nach einer Formel gefragt hast (die Alternative wäre Pivot-Tabelle):

Man kann „Summewenn“ für diese Funktion nutzen.

Annahme: Du hast auf dem ersten Tabellenblatt:
Spalte A = Name
Spalte B = Warengruppe
Spalte C = Menge

Dann wäre die gesuchte Formel auf dem zweiten Tabellenblatt
=SUMMEWENN(Tabelle1!A:C;„Warengruppe 1“;Tabelle1!B:B)*7

Das „Warengruppe 1“ durch den tatsächlich zu suchenden Eintrag ersetzen und für alle Warengruppen kopieren.

#11

Hallo,

das Zusammenfassen der Anzahl auf dem neuen Arbeitspaltz machst Du mit Hilfe dem „Gruppieren“. Da können Kategorien, die gleich sind zusammengefasst werden und die Anzahl z.B. automatisch summiert werden.

Damit muss kann dann weiter gerechnet werden.

VG Mike

#12

Hallo Wollitzer,

das ist sehr leicht mit hilfe einer Pivottabelle zu lösen.
Für Excel 2010:
Gesamte Tabelle mit Produkt, Warengruppe und Anzahl (mi Überschriften!) markieren und dann im Ribbon [Einfügen], [PivotTabelle] und bestätigen.
Dann die Warengruppe in die Zeilenbeschriftungen ziehen und ein berechnetes Feld einfügen:
[PivotTable-Tools] --> [Optionen] --> [Felder, Elemente und Gruppen] --> [Berechnetes Feld …]
Irgendwie benennen und in der Formel: =Anzahl * 7

Dieses berechnete Feld muss dann nach Summe Werte.

Achtung: Wenn die Ausgangstabelle neuen Zeilen erhält, muss man in der Pivottabelle die Datenquelle auf die neue gesamte Ausgangstabelle ausweiten.

[PivotTable-Tools] --> [Optionen] --> [Datenquelle ändern]

Das passiert leider nicht automatisch.

Ansonsten: Viel Erfolg.

Gruß Merlin

#13

Hallo Claus,
Sorry fuer die Verzoegerung…

Ich habe da eine Idee:
Auf dem 2ten Blatt hast du eine Auflistung deiner Warengruppen in Spalte A.
In Spalte B verwendest du die Formel „summewenn(…)“ und ergaenzt „*7“ am Ende fuer die Anzahl deiner Wochentage.

Leider habe ich gerade kein Excel zur Verfuegung, rufe bitte daher die Hilfe fuer die „Summewenn“-Formel selbst auf.
Wichtig ist, dass die Namen der Warengruppen identisch sind.

Hoffe es hilft dir erstmal weiter.

Gruss,
Patrick

#14

Ja, die Warengruppen ändern sich regelmäßig, wie auch die dazugehörigen Mengen.

#15

Mir raucht jetzt der Kopf :smile:
Aber ich gebe nicht auf, ich versuche einmal die ganzen Hilfen, dann werde ich mich wieder melden.
Gruß Claus

#16

Kann die da leider nicht helfen, da mir die Aufgabenstellung nicht ganz klar ist.

Sry, Kai