Mittelwerte einer Kategorie

[AndyRandy691]

Hallo zusammen,


ich stehe vor einer gefühlt unlösbaren Aufgabe. Ich habe eine Tabelle "Waren", in der sämtliche Aritkel eines Supermarkts zeilenweise aufgelistet sind. Zu jedem Artikel lässt sich die Kategorie in einer eigenen Spalte über eine Dropdown-Liste auswählen, z.B. "Gemüse" für den Artikel "Tomaten" oder "Schreibwaren" für den Artikel "Kugelschreiber". In einer zweiten Tabelle "Artikelbewertung" können Kundenbewertungen zu den einzelnen Artikeln eingegebn werden. Jeder Artikel wird dabei in Sachen Optik, Qualitätseindruck, Benutzerfreundlichkeit und Preis-Leistungsverhältnis beurteilt. Am Ende findet sich dort also eine Liste aller Artikel mit Schulnoten für diese vier Bewertungskriterien. Nun sollen diese Bewertung für die Artikelkategorien zusammengefasst werden, sodass in einer dritten Tabelle nur noch die Artikelkategorien mit den Bewertungen für Optik, Qualitätseindruck, Benutzerfreundlichkeit und Preis-Leistungsverhältnis zu finden sind statt alle Artikel einzeln. Anhand der Tabelle „Waren“ soll also aus der Tabelle „Artikelbewertung“ ein Mittelwert aller Artikel derselben Kategorie für die vier Bewertungskriterien gebildet werden. Leider habe ich keine Ahnung wie ich das vernünftig umsetzen kann. Meine ersten Versuche mit MITTERLWERTWENN und SVERWEIS waren leider wenig erfolgreich...
Vielen Dank schonmal im Voraus.
 
Gruß Andy

[steve1da]

Hola,

lade bitte eine Beispieldatei hoch.

Gruß,
steve1da

[OoDex]

Hallo,

das mag nun nicht genau das sein was du willst, aber vielleicht reicht dir das ja.

Angehängt: Beispiel.xlsx

Tabelle 1: 
Waren mit Warennamen und Kategorie
Tabelle 2: Aritkelbewertung mit Waren und Kategorien (ich habe hier die Kategorie nochmal hingeschrieben per SVerweis, also kein Zusatzaufwand, damit man es leichter zuordnen kann, ansonsten müsste die Tabelle genauso aufgelistet sein wie die Tabelle 1) und einen Mittelwert hinzugefügt.
Tabelle 3: Die erste Zeile gibt das Suchwort an, hier Gemüse. Er sucht sich alles mit Gemüse und addiert die Mittelwerte, teilt sie dann durch die Anzahl. Habe es mit Mittelwert aufgrund von fehlenden Argumenten nicht geschafft.


Alles was du hierbei einpflegen musst ist: 
Tabelle 1 muss alle Artikel und dazugehörige Kategorien haben.
Tabelle 2 benötigt die Artikel und die Bewertung.
Tabelle 3 benötigt lediglich den Kategorie-Namen (nächster wäre in O1) und dass du die Formel in O2 kopierst.

[AndyRandy691]

Hi nochmal,

die Lösung von OoDex geht definitiv in die richtige Richtung. Danke schonmal dafür. Einzig die Bewertungskriterien sollten auch für die Zusammenfassung der Artikel in Kategorien erhalten bleiben, also z.B. Kategorie Gemüse hat Bewertungen für Optik, Qualität, Haltbarkeit und Preis-Leistungsverhältnis, deren Werte die Mittelwerte der jeweiligen Bewertungskriterien aller Aritkel der Kategorie Gemüse sein soll. Ein Beispiel ist dieses Mal mit angehangen.

Gruß Andy

[Jonas0806]

Hallo,

Du solltest deinen gesamten Tabellenaufbau überdenken. Wie Du siehst, macht deine Datenstruktur eine vernünftige Auswertung sehr schwierig.

Gleiche Daten gehören in eine Tabelle. Mach dir also zusätzliche Spalten in deine Tabelle für die entsprechenden Bewertungskriterien. Dann kannst Du ganz einfach Auswertungen fahren, z.B. mit PivotTables.

Das verhindert z.B. auch Schreibfehler (Und damit Zuordnungsfehler) wie z.B. bei "Zahnbürtse". Auch auf verbundene Zellen solltest Du verzichten.

Gruß Jonas

[OoDex]

Naja, ich habe nur gemacht was in deinem Text stand
"soll also aus der Tabelle „Artikelbewertung“ ein Mittelwert aller Artikel derselben Kategorie für die vier Bewertungskriterien gebildet werden"

Ich schaue mir mal eben deine Tabellen an...

Ich habe nun eine Datei angehängt. Damit es nicht hässlich wird habe ich in Tabelle 2 wieder die Kategorie eingefügt - diese kannst du auch in ZZ schreiben (ist ja nur eine Formel), oder ausblenden, aber es muss existieren. 

In Tabelle 3 wird der Wert in A2 verwendet, da du es mit verbundenen Zeilen schreiben willst musst du bei jeder neuen Kategorie die Formel anpassen.

Code:

=SUMMEWENNS(Artikelbewertung!B:B;Artikelbewertung!D:D;Kategoriedurchschnitt!$A$2;Artikelbewertung!A:A;Kategoriedurchschnitt!B2)/ZÄHLENWENNS(Artikelbewertung!D:D;Kategoriedurchschnitt!$A$2;Artikelbewertung!A:A;Kategoriedurchschnitt!B2)

Verständlich erklärt:

Code:

=SUMMEWENNS(Artikelbewertung!B:B;Artikelbewertung!D:D;Kategoriedurchschnitt!$A$2;Artikelbewertung!A:A;Kategoriedurchschnitt!B2)

Mehrere Kriterien sind möglich. Die Formel sucht in Artikelbewertung Spalte B alles, was in Artikelbewertung D das Wort aus Kategoriedurchschnitt A2 (Gemüse) hat UND sucht was gleichzeitig in Artikelbewertung A den Wert aus Kategoriedurchschnitt B2 (Optik) hat. Durch das herunterziehen der Formel wird B2 verändert - soll ja so sein). Gemüse (A2) bleibt, muss aber bei einer neuen Kategorie angepasst werden, da du sonst immer nur alles für Gemüse bekommst.

Code:

/ZÄHLENWENNS(Artikelbewertung!D:D;Kategoriedurchschnitt!$A$2;Artikelbewertung!A:A;Kategoriedurchschnitt!B2)

Hier wird im Endeffekt genau das Selbe gemacht, da wir ja die Anzahl dieser Felder haben wollen. Also wie oft gibt es die Zellen Gemüse - Optik, um aus der Summe einen Mittelwert zu machen (Summe/Anzahl). Das Einzige was weggefallen ist ist "Artikelbewertung!B:B;" da wir ja nun keine Matrix mehr verwenden, aus der Werte zusammengerechnet werden sollen.

Edit: gerade gesehen das Problem mit verbundenen Zeilen wurde schon angesprochen, aber solange die oben genannte Zelle anpasst ist das kein Problem.

Grüße,
Dex

[AndyRandy691]

Ja genau das habe ich gesucht. Die Lösung funktioniert sehr gut, auch wenn die Sache eher umständlich ist. Danke für eure Tipps  :28:

Gruß Andy

[OoDex]

Nun ja, eine Lösung kann niemals besser sein als die Anfrage selbst, wenn man sich an die Anfrage halten soll :p

[AndyRandy691]

Nun ja, eine Lösung kann niemals besser sein als die Anfrage selbst, wenn man sich an die Anfrage halten soll :p

Ja das stimmt. Sollte keinesfalls Kritik an der Lösung sein. Die Vorgaben machen es umstädnlich wäre passender formuliert gewesen

[GMG-CC]

Moin,
beim ersten Mal gewiss etwas umständlicher als eine Formel-Lösung, auf Dauer gesehen aber um Längen leistungsfähiger in Sachen Auswertungs-Möglichkeiten: Power Query -> http://www.excel-ist-sexy.de/power-query-das-add-in/