Fehlende Formel für Berechnung von Beträgen

[echselll]

Guten Abend zusammen,

ich bin betriebsintern kürzlich in eine andere Abteilung gewechselt und komme mittlerweile immer mehr mit Excel in Kontakt.


Folgende Situation bringt mich etwas zum Grübeln (sh. Anhang).


Links steht also mein Budget bestehend aus der Postleitzahl und dem zu ermittelnden Gewicht meiner Sendung.
Rechts die Frachtmatrix bestehend aus den Postleitzahlen und 100 kg spitzen Frachtsätzen.

In B2 (gelbes Feld) soll das Ergebnis ausgegeben werden.

Kennt jemand eine gescheite Formel?

Bin für jede Hilfe dankbar!   :18:

Liebe Grüße
echselll

[WillWissen]

Hallo,

erweitere deine Hilfstabelle: aus Spalte G (11-16) mache 6 Spalten (11, 12, 13, 14, 15, 16) mit den jeweils dazugehörenden Werten. Mit den anderen verfahre ebenso. Dann kannst du mit INDEX/VERGLEICH deine gesuchten Werte auslesen.

[echselll]

Hallo,

erweitere deine Hilfstabelle: aus Spalte G (11-16) mache 6 Spalten (11, 12, 13, 14, 15, 16) mit den jeweils dazugehörenden Werten. Mit den anderen verfahre ebenso. Dann kannst du mit INDEX/VERGLEICH deine gesuchten Werte auslesen.

Hallo WillWissen,

danke für deine Antwort.

Die Spalten habe ich erweitert.
Allerdings gibt es den gesuchten Betrag (im Falle des gelben Feldes sind es ja 150 kg) nicht in der Frachtmatrix rechts.

[WillWissen]

Hi,

du musst ja in dem Fall davon ausgehen, dass es immer Werte von...bis sind. In meinem Beispiel ist der Suchwert 250; als Ergebnis muss es in dem Fall heißen, die Kosten von 200kg bis 300kg für den jeweiligen PLZ-Bereich sind...

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
1	plz	250			plz	11	12
2	11	30			100	20	25
3	12	35			200	30	35
4					300	40	34
5					400	50	55

Zelle	Formel
B2	=INDEX($E$1:$G$5;VERGLEICH($B$1;$E$1:$E$5;1);VERGLEICH(A2;$E$1:$G$1;0))
B3	=INDEX($E$1:$G$5;VERGLEICH($B$1;$E$1:$E$5;1);VERGLEICH(A3;$E$1:$G$1;0))

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

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

[echselll]

Hi,

du musst ja in dem Fall davon ausgehen, dass es immer Werte von...bis sind. In meinem Beispiel ist der Suchwert 250; als Ergebnis muss es in dem Fall heißen, die Kosten von 200kg bis 300kg für den jeweiligen PLZ-Bereich sind...

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
1	plz	250			plz	11	12
2	11	30			100	20	25
3	12	35			200	30	35
4					300	40	34
5					400	50	55

Zelle	Formel
B2	=INDEX($E$1:$G$5;VERGLEICH($B$1;$E$1:$E$5;1);VERGLEICH(A2;$E$1:$G$1;0))
B3	=INDEX($E$1:$G$5;VERGLEICH($B$1;$E$1:$E$5;1);VERGLEICH(A3;$E$1:$G$1;0))

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

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

Aber ich möchte ja nicht den Wert ermitteln, der zwischen 200 und 300 kg steht (so wie in deinem Beispiel die 30).

Mein Ziel ist, den Wert zu ermitteln, welcher sich aus dem Suchkriterium (250 kg) und dem Wert in der Frachtmatrix ergibt.
Und da es sich um 100 kg spitze Sätze handelt werden diese beiden Werte miteinander multipliziert.

In deinem Beispiel: (250 kg x 40€) / 100 kg= 100€

Die Werte in Spalte E sind immer "bis-Werte"
Bei den gesuchten 250 kg wird also die Zeile mit 300 kg genutzt.
Und der Wert, der dann dort in F4 angegeben ist, soll multipliziert werden mit dem Suchkriterium von 250 kg.

Im Anhang habe ich das mal deutlicher dargestellt. Vielleicht wird daraus klar, was ich meine.

 Ist das so verständlich?

[WillWissen]

Hallo,
dann so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
1	plz	150	222	568		plz	11
2	11	7,13 €	8,88 €	9,94 €		600	1,75
3						500	2,5
4						400	3,25
5						300	4
6						200	4,75
7						100	5,5

Zelle	Formel
B2	=INDEX($F$1:$G$7;VERGLEICH(B$1;$F$1:$F$7;-1);VERGLEICH($A$2;$F$1:$H$1;0))*B1/100
C2	=INDEX($F$1:$G$7;VERGLEICH(C$1;$F$1:$F$7;-1);VERGLEICH($A$2;$F$1:$H$1;0))*C1/100
D2	=INDEX($F$1:$G$7;VERGLEICH(D$1;$F$1:$F$7;-1);VERGLEICH($A$2;$F$1:$H$1;0))*D1/100

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

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

Beachte den Vergleichsparameter -1 und die Sortierung in der Hilfstabelle.
Bitte das nächste Mal anstelle Bildchen die entsprechende Beispieldatei hochladen. Dann können Vorschläge darin getestet werden, ohne jedes Mal alles nachbauen zu müssen.

[echselll]

Super, das hat jetzt funktioniert. Vielen Dank für deine Mühe!

In Ordnung, ich denke beim nächsten Mal dran, direkt Dateien zur Verfügung zu stellen  :)