Drop-Down Auswahl mit Sverweis und verschachtelte Wenn-Funktion??

[SBieteh]

Hallo liebe Excel-Community,

dies hier ist mein erster Beitrag und ich hoffe mir kann einer von Euch helfen. Ich kenne mich mit den Grundfunktionen von Excel ganz gut aus, komme mit meinem Problem leider nicht weiter.

Szenario:

Es gibt drei verschiedene Bereiche (A1, A2, A3)

Der monatliche prozentuale Erfüllungsgrad kann sich in 5 Stufen unterscheiden. 

Beispiel:


>95%
> = 90,0 % - < 95,0 %
> = 85,0 % - < 90,0 %
> = 80,0 % - < 85,0 %
< 80,0 %

Je nachdem wie hoch die prozentuale Erfüllung ist, soll der Umsatz von A1, A2 und A3, mit einem bestimmten Wert multipliziert werden.

Der Umsatz beträgt für A1 beträgt z.B. 5.000 €
Der Prozentuale Erfüllungsgrad liegt bei 87%, d.h. zwischen 85% und 90%
Für diesen Wert, beträgt der Anteil am Umsatz (5.000) zehn Prozent.


Der Umsatz beträgt für A2 beträgt z.B. 8.000 €
Der Prozentuale Erfüllungsgrad liegt bei 75%, d.h. unter 80%
Für diesen Wert, beträgt der Anteil am Umsatz (7.000) 15%

Ich möchte das zusätzliche mit einem Drop-Down-Menü auswählen können.

Wähle ich also Januar aus, soll für die verschiedenen Bereiche A1, A2 und A3 überprüft werden, wie hoch die prozentuale Erfüllung ist und aufgrundlage der Tabelle, der richtige Anteil am Umsatz berechnet werden.

Eine Beispieldatei habe ich beigefügt womit es vielleicht einfach zu verstehen ist.

In Zeile B15, C15 und D16 sollen die jeweiligen Werte berechnet werden.

In B15 soll überprüft werden, welchen Wert habe ich in B3 für Monat X, in welchem Bereich der Zellen B7-B11 liegt dieser Wert aus B3 und multipliziere den Wert aus C7-C11 (je nach Wert) mit dem Wert aus G3 für den gleichen Monat
Ich hoffe das war nicht allzu unmissverständlich.

Vielen Dank schon mal vorab.

SBieteh

[echo]

Hallo
wenn ich es richtig verstanden habe, müßten die Werte der Stufen A1-A3 in aufsteigender Reihenfolge sortiert sein. (umgekehrt)
drehe die Reihenfolge der Stufen A1-A3 und füge links nur die aufsteigenden Grenzwerte ein, dann kannst du diese per SVerweis() abfragen.

Damit du die Formeln kopierbar werden habe ich die Bereiche ST.A1 bis ST.A3 benannt (Daten/Namensmanager) und verzweige per Indirekt auf sie.
Oben in B3-D3 sind die Werte Dezimal also um den Faktor 100 größer wie in den Stufen
Gruß Holger

[SBieteh]

Danke ich werde mir das morgen in Ruhe anschauen. Bis jetzt habe ich nur Bahnhof verstanden

[SBieteh]

Hallo
wenn ich es richtig  verstanden habe, müßten die Werte der Stufen A1-A3 in aufsteigender Reihenfolge sortiert sein. (umgekehrt)
drehe die Reihenfolge der Stufen A1-A3 und füge links nur die aufsteigenden Grenzwerte ein, dann kannst du diese per SVerweis() abfragen.

Damit du die Formeln kopierbar werden habe ich die Bereiche ST.A1 bis ST.A3 benannt (Daten/Namensmanager) und verzweige per Indirekt auf sie.
Oben in B3-D3 sind die Werte Dezimal also um den Faktor 100 größer wie in den Stufen
Gruß Holger

Hallo Holger,

Dein Tipp mit den Grenzwerten war super.

Ich habe das Ganze jetzt mit einer WENN(UND) Formel gelöst.

VG
SBieteh

[SBieteh]

Hallo Zusammen,

Ich habe jetzt ein weiteres Problem:

Zum ursprünglichen Szenario kommt nun hinzu, dass auch die Erfüllung des Vormonats angeschaut werden muss.

Wie bereits erwähnt, wird der monatliche prozentuale Erfüllungsgrad in 5 Stufen unterschieden.

Beispiel:


Stufe 1 Erfüllung >95% - Kosten in % = 0
Stufe 2 Erfüllung > = 90,0 % - < 95,0 % - Kosten in % = 10%
Stufe 3 Erfüllung > = 85,0 % - < 90,0 % - Kosten in % = 12%
Stufe 4 Erfüllung > = 80,0 % - < 85,0 % - Kosten in % = 15%
Stufe 5 Erfüllung < 80,0 % - Kosten in % = 18%


Wenn wir im Monat 1 eine Erfüllung zwischen 95% und 90% haben, betragen die Kosten gemessen am Umsatz 10%.

Wenn wir allerdings im Monat 2 wieder eine Erfüllung zwischen 95% und 90% haben, wird die darunter liegende Stufe betrachtet, da zwei Mal in Folge die Erfüllung unter 95% lag. Somit betragen die Kosten gemessen am Umsatz 12% und nicht 10% wie es laut der Tabelle eigentlich sein sollte.

Wenn wir im Monat 3 eine Erfüllung zwischen 90% und 85% haben, betragen die Kosten gemessen am Umsatz nicht 12%, sondern 15%, da hier erneut die darunter liegende Stufe betrachtet wird.
Wäre die Erfüllung im Monat 2 >95%, würde die Kosten gemessen am Umsatz im dritten Monat[attachment=20946] 12% betragen und nicht 15%

Ich habe meine Beispieldatei mit angehängt.

Das ursprüngliche Szenario hatte ich mit einer WENN(UND) Formel gelöst. Ich weiß jetzt leider nicht, wie ich das hier nun lösen kann.


Vielen Dank schon mal!

Sbieteh

[echo]

Hallo
Das wird ein ziemlicher "Rattenschwanz"
Es steht dir ja frei so viele Grenzwerte in einer Liste aufzunehmen wie du willst,
du kannst also auch prozentuale Zwischenschritte einzufügen in der sich in dieser Spalte gerade mal nichts ändert.

Vorschlag, lass Excel danach suchen:
Ich würde eine einzige Liste mit allen Prozent- und Grenzweten erstellen und diese um weitere Spalten erweitern.

Spalte 1 Suchspalte : Alle benötigten Grenzwerte (%), aufsteigend sortiert
Spalte 2 %-Werte von A1
Spalte 3 %-Werte von A1 mit 1. Bedingung
Spalte 4 %-Werte von A1 mit 2. Bedingung
Spalte 5 %-Werte von A2
Spalte 6 %-Werte von A2 mit 1. Bedingung
Spalte 5 %-Werte von A2 mit 2. Bedingung
Spalte 6 %-Werte von A3
Spalte 7 %-Werte von A3 mit 1. Bedingung
usw.

Du kannst dann für Sverweis() immer die gleich Matrix über die gesamte Liste verwenden und mußt nur noch die relevante Spalte einstellen.
Wenn die Spalten Überschriften haben (A1-A3) kann verweis() die Spalte suchen/liefern, egal welche es ist.
Für die Bedingung 1 oder Bedingung2 macht man eine Prüfung ggf Spalte +1 oder +2

Gruß Holger

[SBieteh]

Hallo Holger,

Ich steh auf dem Schlauch.

"Spalte 1 Suchspalte : Alle benötigten Grenzwerte (%), aufsteigend sortiert

Spalte 2 %-Werte von A1"

Das habe ich soweit verstanden :19:

Wie meinst Du das mit

"Spalte 3 %-Werte von A1 mit 1. Bedingung

Spalte 4 %-Werte von A1 mit 2. Bedingung"

Klar kenne ich die Bedingungen, aber wie soll ich das in der Tabelle eintragen?

Ich habe jetzt nochmal die Datei beigefügt. So wie ich die ersten zwei Punkte eingefügt habe, hast Du es ja gemeint, oder?

Danke & VG

SBieteh

[echo]

Hallo
Du hast im Tabellenblatt 1 eigendlich 5 verschiedene Grenzwerte: die Level A1 mit Gw1 & Gw2, Level A2 Gw1 & Gw2, sowie Level A3

Das alles in einer Tabelle mit Überschrift läßt sich dann spielend leicht abfragen, siehe Anhang

Gruß Holger

PS Beachte die doppelt eingefügten Werte wenn der Grenzwert in diesen Level nicht Zutrifft

[SBieteh]

Hallo
Du hast im Tabellenblatt 1 eigendlich 5 verschiedene Grenzwerte: die Level A1 mit Gw1 & Gw2, Level A2 Gw1 & Gw2, sowie Level A3

Das alles in einer Tabelle mit Überschrift läßt sich dann spielend leicht abfragen, siehe Anhang

Gruß Holger

PS Beachte die doppelt eingefügten Werte wenn der Grenzwert in diesen Level nicht Zutrifft

Hallo Holger,

Ich habe verstanden was Du mir sagen möchtest und was Du in die ursprüngliche Datei eingefügt hast. Vielen Dank schon mal dafür!!

Mir stellt sich jetzt aber trotzdem noch die Frage, wie ich die Formel im Blatt "Tabelle 1" dahingehend ändern kann, sodass die Kosten per Level richtig kalkuliert werden (unter der Berücksichtung der bestehenden Regeln, sprich wenn "Erfüllung in %" gleich Wert X dann "Kosten in %" abhängig vom Umsatz gleich X, außer "Erfüllung in % des Vormonats" für Level A1 <95% und Level A2-A3 unter >90%, dann Stufe der "Kosten in %" eins niedriger als es eigentlich sein soll.)

Nochmal vielen Dank im Voraus!

Gruß

SBieteh

[SBieteh]

Hallo Zusammen,

Wenn hier jemand eine Idee hätte, wäre das echt super :)

Danke
Sbieteh