Werte je Bereich ermitteln oder berechnen

[eda305]

Hallo,

ich entschuldige mich im Voraus für die wahrscheinlich nicht treffende Titelbeschreibung, Problembeschreibung und meine mangelnden Kenntnisse. Gerne kann der Admin den Titel anpassen,
oder ich passe ihn nach eurem Vorschlag an, wenn das nach der Anlage noch möglich ist.
Ich hoffe, ihr helft mir dennoch weiter und findet relativ einfache Lösungen.

Problem 1

Wert A soll für den Bereich 0-999 gelten
Wert B soll für den Bereich 1000-1399 gelten
Wert C soll für den Bereich 1400 - 9999 gelten

Ich gebe dann eine Zahl zwischen 0-9999 ein und es soll mir entweder A, B oder C ausgegeben und mit dieser Zahl werden.

Eine direkte Zuweisung für einen exakten Wert kenne ich bei SVERWEIS, aber nicht für Bereiche. Natürlich kann ich deine extra Tabelle mit allen möglichen Werten 0-9999 und der entsprechenden A, B, C Zuweisung anlegen mir dann die Werte mittels SVERWEIS abgreifen, aber elegant ist anders.

Problem 2

Es gelten weiterhin die Bereiche

0-999 mit Wert A
1000-1399 mit Wert B
1400-9999 mit Wert C

Nun gibt es einen Grundwert von bspw. 800, davon wird mit Prozentsatz 40% ein Prozentwert 320 errechnet. Nun sollen die definierten Bereiche jeweils "aufgefüllt" werden,
beginnend ab dem Grundwert, d.h. ab einem Grundwert von 1000 kann 0-999 nicht mehr aufgefüllt werden und ab einem Grundwert von 1400 sowohl nicht mehr 
0-999 und 1000-1399, sondern nur noch 1400-9999.
In o.g. Fall könnte man also noch 0-999 von 800 bis 999 mit 199 auffüllen, Rest 320 - 199 = 121. Damit kann wiederum der Bereich 1000-1399 belegt werden.
Für den Bereich 1400-9999 bleibt nichts mehr übrig.

0-999 mit Wert A * 199
1000-1399 mit Wert B * 121
1400-9999 mit Wert C * 0 nichts mehr übrig



Oder Grundwert 1000 / Prozentsatz 40% / Prozentwert 400

0-999 mit Wert A * 0 darf nicht mehr belegt werden, da Grundwert größer diesem Wertebereich
1000-1399 mit Wert B * 399
1400-9999 mit Wert C * 1
 
Oder Grundwert 1400 / Prozentsatz 40% / Prozentwert 560

0-999 mit Wert A * 0 darf nicht mehr belegt werden, da Grundwert größer diesem Wertebereich
1000-1399 mit Wert B * 0 darf nicht mehr belegt werden, da Grundwert größer diesem Wertebereich
1400-9999 mit Wert C * 560


Wie kann man hier eine Formel entwickeln, für die man nach Eingabe des Grundwerts nicht nur den Prozentwert, sondern auch die entsprechende Verteilung in die 
verschiedenen Bereiche erhält?

1000 Dank für jede Lösung.

Viele Grüße,
Edan

[shift-del]

Moin

Für den ersten Teil:

Code:

=VERWEIS(D1;{0;1000;1400};{"A";"B";"C"})

[steve1da]

Hola,

Problem 1 kann ebenfalls mit dem Sverweis erledigt werden, der 4. Parameter muss dabei WAHR sein.

Problem 2 hab ich ehrlich gesagt nicht wirklich verstanden. Was meinst du denn mit "belegen"?

Gruß,
steve1da

[eda305]

@shift-del: super, vielen Dank, deine Lösung für Problem 1 funktioniert! Kannte die Funtion "VERWEIS" ohne "S" nicht und das dann Werte "ab" gelten.

@steve1da: ich bekomme einen Prozentwert (W) aus Grundwert (G) * 40% (P). Anschließend darf ich diesen Prozentwert verteilen, beginnend ab dem Bereich, der auf den Grundwert zutrifft, aber maximal
bis zum Limit des Bereichs. Ich hoffe die Beispiele helfen.

Bereich A 0      -   999 = max Wert G+W = 999, wenn mehr dann Rest W aus Bereich A zu Bereich B
Bereich B 1000 - 1399 = max Wert G + (Rest) W aus Bereich A = 1399, wenn mehr dann Rest W aus Bereich B zu Bereich C
Bereich C 1400 - 9999 = max Wert G + (Rest) W aus Bereich B = 9999


Grundwert 500 / Prozentwert W 200 

Bereich A = 200 (Rest 0)
Bereich B = 0 
Bereich C = 0

Grundwert 800 / Prozentwert W 320  

Bereich A = 199 da 800 + 199 = max Wert 999 (Rest von W aus A 121)
Bereich B = 121 (Rest 0)
Bereich C = 0

Grundwert 1100 / Prozentwert W 440

Bereich A = 0 da G über 999
Bereich B = 299 da 1100 + 299 = max Wert 1399 (Rest W aus B 141)
Bereich C = 141

Grundwert 1500 / Prozentwert W 600

Bereich A = 0 da G über 999
Bereich B = 0 da G über 1399
Bereich C = 600

[Fred11]

Hallo Edan,

zu deinem Problem 2:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
1	von	bis	Bereich	Aufteilung		Grundwert	500
2	0	999	A	200,00		Prozentsatz	40%
3	1000	1399	B	0,00		Prozentwert	200
4	1400	9999	C	0,00
5			Kontrollsumme:	200

Zelle	Formel
D2	=MIN(MAX(B2-$G$1;0);$G$3)
D3	=MIN(B3-A3;$G$3-D2;MAX(B3-$G$1;0))
G3	=G1*G2
D4	=$G$3-(D2+D3)
D5	=SUMME(D2:D4)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß
Fred

[eda305]

Hallo Fred,

du bist super!!! Alles funktioniert, wie es soll und die Formeln sind auch sehr übersichtlich.  :28:

Ich muß mich nur noch ein wenig mit den Funktionen beschäftigen, da ich diese bislang nicht kannte und komplett verstehen möchte.
Evtl. frage ich hier nochmal nach, falls ich nicht weiterkomme, ich hoffe das ist ok.

Vielen 1000 Dank!

[eda305]

Hallo Fred,

ich habe versucht mich in die Formeln in Spalte D reinzufinden, habe aber doch starke Probleme. In Erklärungsvideos zu Min oder Max Funktionen wird einfach aus einer Spalte der Höchste oder niedrigste Wert ermittelt und fertig. 
Generell habe ich unabhängig von den Formeln Probleme, den generellen Lösungsansatz zu verstehen, wie du dich dem Problem logisch annimmst. In den Formeln verstehe ich besonders beim Max Teil nicht, wieso die 0 extra als Wert aufgeführt wird.
Ich würde es wirklich gerne verstehen und mich sehr freuen, wenn du deine Formellösung und generelle Logik zur Problemlösumg erklären würdest.

Vielen Dank,
Edan

[Fred11]

Hallo Edan,

hier ein paar Erläuterungen:

Die Syntax der Funktion MAX (und MIN analog) lautet:
=MAX(Zahl1;Zahl2;...) 

Man kann als Argumente für MAX entweder einen Bereich, oder aber auch direkt zwei (oder mehr) einzelne Werte eingeben
(Wie z.B. bei der Funktion SUMME ja auch...)


Beispiel:
MIN(5;0;-3) 
ergibt -3

Das funktioniert natürlich auch mit Zellbezügen
MIN(B3;B4)

und auch mit Berechnungen:
MAX(B2-$G$1;0)
Wenn die Differenz von B2 und G1 größer 0 ist, dann wird die Differenz von B2 und G1 ausgegeben
Ist die Differenz kleiner 0 (also negativ), wird 0 ausgegeben
("gib den größeren der beiden Werte zurück" )
Durch MAX(irgendwas;0) wird also einfach nur verhindert, dass ein negativer Wert rauskommt

Man kann das ganze statt mit MIN/MAX auch mit der WENN-Funktion machen (da ist es dann vielleicht etwas einleuchtender/verständlicher):
WENN(B2-G1>0;B2-G1;0)
Das macht exakt dasselbe

und
=MIN(MAX(B2-$G$1;0);$G$3)
beduetet dann: 
"gib den kleineren der beiden Werte (B2-G1) und G3 zurück - aber nicht wenn (B2-G1) negativ ist; dann gib den Wert 0 zurück"

Die Alternative Schreibweise mit WENN für die Formel in D2 würde dann lauten:
=WENN(WENN(B2-$G$1>0;B2-$G$1;0)<$G$3;WENN(B2-$G$1>0;B2-$G$1;0);$G$3)

Wie du siehst, ist diese Formel länger wie die MIN/MAX-Formel; deshalb habe ich die MIN/MAX-Variante verwendet.

Und auf diese Weise habe ich dann halt alle deine angegebenen Bedingungen entsprechend verarbeitet.

Gruß
Fred

[shift-del]

Moin

Die Formel in D2 geht noch kürzer:

Code:

=MEDIAN(0;B2-G1;G3)

[eda305]

Hallo Fred 

ich habe es jetzt verstanden, vielen Dank das du dir so viel Zeit für die ausführliche
Erklärung genommen hast. Respekt, wie du die Min/Max Logik aufgebaut hast.
Asche auf mein Haupt wegen der Wenn Funktion, ich konnte mal die lange verschachtelte Form,
habe es aber schon länger nicht mehr benötigt, und muss mir das mal wieder richtig aneignen.

Hallo Shift-Del,

danke, diese Lösung schaue ich mir auch nochmal an!

Hallo Steve1da,

deine Lösung zu Problem 1 schaue ich mir auch nochmal an.

Danke Euch Dreien und viele Grüße,
Edan