Office 2016 Verschachtelte Wenn-Funktion mit mehreren Bedingungen und Ranking

[SimoEL]

Hallo Zusammen
Ich habe eine relativ komplexe Angelegenheit. Gegeben ist ein Performance Report mit folgenden Spaltenangaben:

Marke  Artikel   Farbe    Standort     Abverkaufsquote

In der Matrix befinden sich etwa 50 Marken, die jeweils 200 Artikel in bis zu 6 unterschiedlichen Farben anbieten.Zudem werden die Artikel an 10 Verkaufsstandorten verkauft - mit unterschiedlichem Erfolg der als Abverkaufsquote in % bemessen wird.
Ziel ist, via Formel (und ggf. Hilfstabelle), aus dem Report alle Artikel in entsprechender Farbe, jeder in der Matrix enthaltenen Marken zu filtern, die eine Abverkaufsquote von >30% aufweisen. Diesen soll entsprechend die 5-TOP-Performer-Standorte versus die 5-LOW-Performer zugeordnet und die entsprechende Abverkaufsquote ausgegeben werden.

Hat hier jemand eine Idee?
Ich doktore hier bereits seit zwei Tagen herum.

Vielen Dank und liebe Grüsse

Simone

[steve1da]

Hola,

zeig doch mal eine Beispieldatei.

Gruß,
steve1da

[SimoEL]

Also hier ist das Daten-File.
Habe es mal über den PIVOT-Weg versucht, - um hier eine TOTAL-Abverkaufsquote pro Sammelartikel zu erhalten, und somit das Kriterium AVQ>20% zu bekommen. Dies wäre ja eigentlich mal meine erste Bedingung, nur fehlte mir ab hier das Wissen, wie weiter zu rechnen ist...
Auf dem Datenblatt bezieht sich die Abverkaufsquote immer auf den entsprechenden Standort.

[gmg-cc]

Moin Simone,
sorry, aber mir ist das alles zu wirr (obwohl/weil?) ich Kaufmann bin.
Mal sind es 30%, mal 20%.
Woher sollen die LOW-5-Performer -Daten kommen?
Worauf beziehen sich die Ziel-Daten?

Du kennst dein Metier gewiss, aber ich verstehe da mehr oder weniger Bahnhof.Wenn du nicht jeden einzelnen Schritt chronologisch beschreibst, dann ist das eher eine Rätselstunde, wo wir Helfer mehr für den Papierkorb als für dich arbeiten.

[creamhh]

So wie ich das sehe, ist das tatsächlich ein Fall für eine Pivottabelle. Deine Tabelle hat bereits die richtige Struktur, aber ich würde sie noch erweitern. Füge hinten noch eine Spalte K ein und nenne sie "Gesamt". Hier gilt offensichtlich: Umsatz (H) + Lager (I) = Gesamt (K). Ferner gilt: Umsatz (H) / Gesamt (K) = AVQ ME (J).
In der Pivottabelle gruppierst Du nach Herzenslust und steckst eventuelle Einschränkung in den Filter.
Du könntest also die "Farbe" in den Filter schieben und dann bei den Spalten nach Marken und Artikel gruppieren. In das Ergebnisfeld schiebst Du "Summe von Umsatz" und "Summe von Gesamt". Dann fügst Du noch ein berechnetes Deld hinzu und rechnest die Abverkaufsquote aus: Summe von Umsatz / Summe von Gesamt. Dann nach dem neuen Feld sortieren.
Siehe Anhang.

[SimoEL]

Hallo Zusammen und vorab, vielen Dank für die Hilfe bis anhin.

Habe wohl wirklich mein Anliegen, bzw. meine Ziel-Formatierung falsch beschrieben.

Grundsätzlich geht es um folgende Kriterien

- Kriterium 1: Marke z.B. Zimmerli, muss zwingend immer Auswahlkriterium sein, denn ich möchte von jeder Marke den Output der Artikel, mit einer Abverkaufsquote von >30%
-Kriterium 2: (Sammel)Artikel je Farbe d.h. ich möchte nur die Sammelartikel in der entsprechenden Farbe als Output, mit einer Abverkaufsquote von >30%
-Kriterium 3: Standort= Verkausstellen mit einer individuellen Performance; - hier möchte ich zu allen Artikel, die Kriterium 1 und 2 erfüllen, die 5 Standorte ausgegeben, die die höchste Abverkaufsquoten vorweisen also sprich meine TOPperformer, denen die 5 Standorte gegenüberstehen, die die schlechteste respektive tiefste Abverkaufsquote haben. So gesehen benötige ich hier ein Ranking Platz 1 Standort steht dem letzten Standort gegenüber u.s.w.

In der Daten-Tabelle geben die Spalten A-F alle Marken resp. Produktinformationen wieder. Spalte G definiert den Standort, der eine individuelle Performance erbringt, die in Spalte J als Abverkaufsquote AVQ ME in % ausgegeben wird. Je Zeile wird folglich immer die Performance eines einzelnen Standortes, des in A-F-definierten Produkts wiedergegeben.

Chronologisch, würde ich sagen, muss als
erstes der Artikel je Farbe  definiert werden, welcher TOTAL über alle Filialen einedurchschnittliche AVQ von >30% hat. Dies wahrscheinlich über PIVOT, da die PIVOT die Daten aus der Daten-Übersicht auf Artikel-ebene komprimiert

zweitens muss dann dem Artikel jeweils die 5 Standorte zugeordnet werden, deren individuelle AVQ des spezifischen Artikels am grössten sind (diese nach Grösse absteigend gerankt) Hierzu muss wahrscheinlich mit Verlinkung zur Daten-Übersicht gerechnet werden, da sich hier ja wiederum die Performances je Standort befinden

drittens sollte diesen 5 Standorte die Low-Performer Standorte gegenüberstehen. Analog Topperformer, jedoch nach Grösse aufsteigend

Hoffe dies ist nun weniger verwirrend?!
Dank euch schon im Voraus für die Hilfe

[gmg-cc]

Moin,
MIR ist das immer noch nicht klar.

- Kriterium 1: Marke z.B. Zimmerli, muss zwingend immer Auswahlkriterium sein, denn ich möchte von jeder Marke den Output der Artikel, mit einer Abverkaufsquote von >30%

Ich nehme mal HANRO, Zeile_4:9. - Welche Zeilen sollen da raus fallen? 6 und 9, weil (leer) und somit <30? Und ich nehme an, dass du die Zahlen in Spalte_J als % definierst, richtig?

Wenn das so ist, dann verstehe ich folgendes nicht:

-Kriterium 2: (Sammel)Artikel je Farbe d.h. ich möchte nur die Sammelartikel in der entsprechenden Farbe als Output, mit einer Abverkaufsquote von >30%

... denn dann sind ja so wie so nur noch Werte >30 vorhanden.

Dass in Spalte_J auch Texte stehen ("x") macht die Sache auch nicht leichter.

Wenn ich da schlüssige Erklärungen habe, mache ich weiter.

[SimoEL]

Hi Günther
Danke für deinen Effort soweit.
Gesetzt dein Beispiel Zeile 4-9 (leider eigentlich ein unglückliches Beispiel, da es dabei um 2 Artikel geht, die nur an 3 Standorten vertrieben werden):
Anyways:
Zeile 4-6 gibt die Performance des Artikels 1108451200, in der Farbe "schwarz kleingemustert", an 3 Standorten aus.
Zeile 4 hat demzufolge diese Daten: Standort S101 (Spalte G)  hat eine Abverkaufsquote von 100%(Zeile J)
Zeile 5 : Standort S116 hat die Abverkaufsquote von 83.3 %
Zeile 6: Standort S100 hat die Abverkaufsquote 0%

Zeile 5 - 6 definiert den Artikel 1108451400 in Farbe "schwarz kleingemustert"...


Zuerst müsste ermittelt werden, was Artikel 1108451200 in der Farbe "schwarz kleingemustert" für eine Gesamtabverkaufsquote hat, sprich der Mittelwert der Abverkaufsquote der 3 Standorte (Zeile 4-6; in Zeile J). Ist diese Gesamtabverkaufsquote >30% dann möchte ich folgendes ausgegeben:

HANRO - Artikel 1108451200 - Standort S101 100% - Standort S100 0%


Ein besseres Beispiel findet sich dagegen, wenn wir die Tabelle nach Marke und von A-Z sortieren. In Zeile 462 - 475 haben wir von der Marke EMPORIO ARMANI den Artikel 1108445600 in Farbe grau Melange (Zeile 462- 468) und Farbe marine (Zeile 469-475). Gleiches Spiel wie oben, 462-468 ist der Artikel in der Farbe grau Melange am Standort S116 mit der Abverkaufsquote 30.8% usw.

Zuerst muss also ermittelt werden, ob die Gesamtabverkaufsquote der Farbe grau Melange (Zeile 462-468, Spalte J) durchschnittlich >30% beträgt. Gesetzt der Fall ja, - möchte ich dass der Artikel in der Farbe ausgegeben wird. Hat hingegen der gleiche Artikel in Farbe Marine (Zeile 469-475; Spalte J) im Durchschnitt eine geringere Abverkaufsquote als 30%, so möchte ich den Artikel in dieser Farbe NICHT ausgegeben bekommen.

Folglich rechnen wir mit Artikel 1108445600 in Farbe grau Melange weiter und ordnen ihm, den besten Standort aus 462-468 zu und setzen die schlechtesten daneben.

Ein X in Spalte J bedeutet nichts. Das müsste man entsprechend umformatieren, da hast du Recht.

Ist das so verständlich?
Liebe Grüsse

Simone

[SimoEL]

Daher kommt eben auch meine Vermutung mit der verschachtelten wenn-Formel.

Irgendwie muss das doch so gehen, =index(Standort; vergleich(kgrösste(wenn(Durchschnittliche Abverkaufsquote des Artikels aus B in der Farbe aus D >30%; dann Artikel B; sonst 0); Ranking 1-x)) und dann irgendwie das Gleiche mit Kkleinste...

Aber ja,- diese Formel bekomme ich irgendwie nicht zusammen...