Berechnung unter Erfüllung mehrerer Bedingungen

[SteBen]

Hallo liebe Excel Brains :)

ich brauch mal wieder Eure Hilfe.   :05:

Anbei findet Ihr meine Beispieldatei. 


  Mappe2.xlsx (Größe: 45,56 KB / Downloads: 7)

Was möchte ich tun?

Ich möchte, dass Excel mir anhand der Bedingungen in D2 & E2 aus dem Reiter "Kosten" die dem Gewicht entsprechende richtige Rate sucht & diese mit dem Gewicht multipliziert. Dabei soll Excel ebenfalls prüfen, ob das Ergebnis dieser Rechnung über dem Minimum liegt. Wenn ja, dann soll dieser Betrag in Zelle F2 erscheinen, wenn nein, dann soll der Min. Betrag in Zelle F2 erscheinen. Gleiches soll auch passieren, wenn es für das Gewicht, egal wie hoch, nur eine Rate gibt, das wäre dann Bedingung "DE39" in Zelle D2, statt "DE38".

Ich hoffe, Ihr könnt mir helfen.

Vielen Dank vorab & Viele Grüße
SteBen

[WillWissen]

Moin,

hab' ich dich richtig verstanden?

Arbeitsblatt mit dem Namen 'Berechnung'
	A	B	C	D	E	F
1	origin airport code	destination airport code	service code	routing	Gewicht	Betrag
2	ALL-DE	CNPEK	40100_NO DGR	DE38	20,00	70,00
3	ALL-DE	CNPEK	40100_NO DGR	DE38	50,00	250,00
4	ALL-DE	CNPEK	40100_NO DGR	DE39	42,00	70,00

Zelle	Formel
F2	=WENNFEHLER(WENN(SVERWEIS($D2;Kosten!$A$1:$D$3;4)="";VERWEIS(E2;Kosten!$E$2:$F$8)*E2;SVERWEIS($D2;Kosten!$A$1:$D$3;3;0));Kosten!$C$2)
F3	=WENNFEHLER(WENN(SVERWEIS($D3;Kosten!$A$1:$D$3;4)="";VERWEIS(E3;Kosten!$E$2:$F$8)*E3;SVERWEIS($D3;Kosten!$A$1:$D$3;3;0));Kosten!$C$2)
F4	=WENNFEHLER(WENN(SVERWEIS($D4;Kosten!$A$1:$D$3;4)="";VERWEIS(E4;Kosten!$E$2:$F$8)*E4;SVERWEIS($D4;Kosten!$A$1:$D$3;3;0));Kosten!$C$2)

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

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

Du musst deine Hilfstabelle aber umbauen!

Arbeitsblatt mit dem Namen 'Kosten'
	A	B	C	D	E	F
1	routing	Currency	Minimum	Flat	Gewicht	Preis
2	DE38	EUR	70,00		45	5,00
3	DE39	EUR	70,00	2,00	100	4,50
4					300	4,00
5					500	3,50
6					1000	3,00
7					3000	2,50
8					99999	2,00

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

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

[neopa]

Hallo SteBen,

für Deine angestrebte Preisermittlung hab ich angenommen, dass für ein beliebiges "routing" der Preis immer abnimmt und der Preis für Gewichte kleiner gleich 45 kg dem entspricht für größer 45 plus 1€ Zuschlag.

Dann in F2 folgende Formel:

Code:

=MAX(SVERWEIS(D2;Kosten!A:D;4;0)*E2;AGGREGAT(15;6;Kosten!E$2:K$9/(Kosten!A$2:A$99=D2)
/(E2>WECHSELN(WECHSELN(WECHSELN(Kosten!D$1:K$1;">";"");"Kg";"");"Flat";SVERWEIS(D2;Kosten!A:E;5;0)+1)+0);1)
*E2;SVERWEIS(D2;Kosten!A:C;3;0))

[SteBen]

Hallo Günter,

ich glaube, das stimmt nicht ganz.
Das Gewicht is variabel, aber der Aufbau des Arbeitsblattes "Berechnung" soll sich nicht verändern.
Sprich, D2 & E2 können variabel getauscht werden & in F2 wird dann der entsprechende Betrag gebracht. Es bleibt dabei aber bei nur einer Zeile.

Die Berechnung muss dann wie folgt erfolgen:

D2 = DE38
E2 = 20 Kg >> E2*Kosten!F2 = 100

od. 

D2 = DE38
E2 = 50 Kg >> E2*Kosten!F3 = 225

od. 

D2 = DE39
E2 = 42 KG >> E2*Kosten!D2 = 84 

Die Gewichtsklassen sind immer Minimum bis 45 Kgs, 45 Kgs bis 100, usw.

Viele Grüße
SteBen

---

Hallo Werner,

danke für Deine Antwort.

Der Preis für ein beliebiges routing nimmt nicht immer ab. Er ist ein komplett variierender Betrag & kann mal weniger, aber auch mehr werden, je nachdem wie hoch das Gewicht ist & welches routing wir haben.

Wenn es beim Preis eine Flat gibt, dann ist dieser gültig für alle Gewichtsklassen.

Vielen Dank & 
Viele Grüße
SteBen

[neopa]

Hallo SteBen ,

so wie Du jetzt geschrieben hast:

Die Gewichtsklassen sind immer Minimum bis 45 Kgs, 45 Kgs bis 100, usw.

geben Deine Angaben auch mehr Sinn, denn sie stehen im Widerspruch zu Deinen Angaben in der Datei und da in Kosten!1:1. Denn z.B. ">45 kg" wird normalerweise nicht als "bis 45 kg" sondern als "größer 45 kg" von mir interpretiert.

Dadurch verkürzt sich meine Formel einerseits etwas und andererseits durch Deine neue Angabe

... komplett variierender Betrag & kann mal weniger, aber auch mehr werden...

muss ich jetzt dafür noch  INDEX(), SPALTE() und VERGLEICH() einfügen.

Mein neuer Formelvorschlag für F2 lautet jetzt:

Code:

=WENNFEHLER(MAX(SVERWEIS(D2;Kosten!A:D;4;0)*E2;INDEX(Kosten!$1:$99;VERGLEICH(D2;Kosten!A:A;0);
AGGREGAT(15;6;SPALTE(Kosten!D$2:K$2)/(E2<=WECHSELN(WECHSELN(WECHSELN(Kosten!D$1:K$1;">";"");"Kg";"");
"Flat";0)+0);1))*E2;SVERWEIS(D2;Kosten!A:C;3;0));"")

Auf Rückfragen hierzu kann ich frühestens am späteren Abend oder gar erst morgen antworten.

[SteBen]

Guten Morgen Werner,

Vielen Dank, das ist genau das, was ich gesucht habe!!

Leider tue ich mich sehr schwer, die Formel aus meiner Beispieltabelle in das richtige Arbeitsblatt zu übernehmen. 

Kannst Du mir die Formel vllt. so anpassen, dass die Berechnung in der beigefügten Datei funktioniert?

Gewicht >> 'shipmentdetails'J10
Bedingung ("routing") >> 'shipmentdetails'G15

Datenbezug aus dem Reiter "airfreight"

Berechnung soll in 'calculator'I18 erfolgen.

Vielen Dank vorab & Viele Grüße
SteBen

[neopa]

Hallo SteBen

... wenn ich Deine neuen Angaben jetzt richtig interpretiert habe, dann in 'calculator'I18:

Code:

=WENNFEHLER(MAX(SVERWEIS('shipment details'!G15;airfreight!A:Q;17;0)*'shipment details'!J10;
INDEX(airfreight!$1:$999;VERGLEICH('shipment details'!G15;airfreight!A:A;0);
AGGREGAT(15;6;SPALTE(airfreight!Q$2:X$2)/('shipment details'!J10<=
WECHSELN(WECHSELN(airfreight!Q$1:X$1;">";"");"Flat";0)+0);1))*'shipment details'!J10;
SVERWEIS('shipment details'!G15;airfreight!A:P;16;0));"")

Unabhängig davon schlage ich Dir vor, in =airfreight!R1:X1 die ">" zu entfernen und dafür dort ein benutzerdefiniertes Zahlenformat z.B. wie folgt:

Code:

"<="0

oder auch z.B.  

Code:

" bis "0

zu definieren.

Dies gibt mE richtiger Deinen geschilderten Sachverhalt wieder. Auf die Formel hat dies keinen wirklichen Einfluss, nur das  darin ein WECHELN()-Formelteil eingespart werden könnte, da es ja dann keine ">"-Zeichen mehr zum Auswechseln gibt.

Demzufolge kannst Du natürlich auch Deine jetzige Darstellung belassen, wenn Du das so gewohnt sein solltest. Ist Deine Entscheidung.

Bei evtl. weiteren Rückfragen könnte ich nicht vor Montag reagieren. Wünsche Dir auch ein schönes WE.

[SteBen]

Hallo Werner,

SUPER!! Vielen Dank dafür - genau das wollte ich.

Wünsche ebenfalls ein schönes Wochenende :)

Viele Grüße
SteBen