Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Berechnung unter Erfüllung mehrerer Bedingungen
#1
Hallo liebe Excel BrainsSmile

ich brauch mal wieder Eure Hilfe.   05

Anbei findet Ihr meine Beispieldatei. 


.xlsx   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
Antwortento top
#2
Moin,

hab' ich dich richtig verstanden?

Arbeitsblatt mit dem Namen 'Berechnung'
ABCDEF
1origin airport
code
destination
airport code
service coderoutingGewichtBetrag
2ALL-DECNPEK40100_NO DGRDE3820,0070,00
3ALL-DECNPEK40100_NO DGRDE3850,00250,00
4ALL-DECNPEK40100_NO DGRDE3942,0070,00

ZelleFormel
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'
ABCDEF
1routingCurrencyMinimumFlatGewichtPreis
2DE38EUR70,00455,00
3DE39EUR70,002,001004,50
43004,00
55003,50
610003,00
730002,50
8999992,00
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) www.zitate.net
Antwortento top
#3
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))
Gruß Werner
.. , - ...
Antwortento top
#4
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
Antwortento top
#5
Hallo SteBen ,

so wie Du jetzt geschrieben hast:

Zitat: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
Zitat:... 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.
Gruß Werner
.. , - ...
[-] Folgende(r) 1 Benutzer sagt Danke an neopa für diesen Beitrag:
  • SteBen
Antwortento top
#6
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.  Confused

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


Angehängte Dateien
.xlsx   sheet.xlsx (Größe: 102,16 KB / Downloads: 5)
Antwortento top
#7
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.
Gruß Werner
.. , - ...
[-] Folgende(r) 1 Benutzer sagt Danke an neopa für diesen Beitrag:
  • SteBen
Antwortento top
#8
Hallo Werner,

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

Wünsche ebenfalls ein schönes WochenendeSmile

Viele Grüße
SteBen
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste