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
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.
(02.05.2019, 20:39)WillWissen schrieb: [ -> ]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.
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 |
(02.05.2019, 21:38)WillWissen schrieb: [ -> ]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?
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.
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 :)