Clever-Excel-Forum

Hallo liebe Forumsmitglieder,

anbei eine Beispieldatei mit einer für mich kniffligen Aufgabe. 
In Tabelle 1 habe ich eine Spalte A mit Zonen und eine Spalte B mit Gewicht.

Dazu gibt es eine Preismatrix im anderen Tabellenblatt.

Ich möchte gerne aufgrund des Gewichtes und der Zone den richtigen Schnittpunkt in der
Preismatrix ermitteln.

Ich war nicht untätig  eine Summenprodukt Formel wie in C3 bereits ausgegeben, damit arbeite ich öfter und
das ist kein Problem...wenn, wie hier, der Wert in der Matrix 1:1 wiederzufinden ist. Im Beispiel habe ich die Formel
dafür in C3 schon stehen, das funktioniert natürlich recht einfach. Da der Wert in der Preismatrix immer für 100 KG gilt und
hier 1.700 KG stehen, habe ich hinten das Ergebnis durch 100 dividiert und mit dem Gewicht multipliziert. Korrektes Ergebnis.

Nun seht ihr die Fragezeichen ab C4 und darunter. Die Herausforderung ist, einen nicht eindeutig zu findenden Wert mit der
Formel zu ermitteln. 
Als Beispiel: Die Zahl 3.112 gibt es in der Preismatrix nicht als eindeutigen Wert. Hier ist die Regel, immer
wenn ein Wert erreicht wurde springt der Preis auf die höhere KG-Einheit. Also bei dem Beispiel C4 wäre der Preis für 3.112 KG
der Wert bei 4.000 KG. 

Ein Wert von 101 KG würde entsprechend auch aufgerundet auf den 200 KG Preis springen.
Unter 100 KG wird der Preis mit dem Wert aus Zeile 5 berechnet.

Ich wäre sehr dankbar, wenn jemand einen Weg kennt, wie man in der Matrix in der o.g. Logik die Formel ergänzt.
Vielleicht ist Summenprodukt da ja auch nicht mehr hilfreich und es gibt eine andere Lösung?

Vielen Dank im Voraus für eure immer guten Ideen und Hilfe.
Liebe Grüße, Excelmonsta

Hola,
das jeweilige Gewicht erhältst du mit:
Gruß,
steve1da

Hallo E...,

ich habs so verstanden:

=XVERWEIS(B8;Preismatrix!$A$5:$A$23;XVERWEIS(A8;Preismatrix!$B$4:$K$4;Preismatrix!$B$5:$K$23;;0;2);;1;2)*B8/100


ps. Auch die möglichen Kombinationen aus dem 5. und 6. Parameter machen den Xverweis mächtiger(bzw.häufig einfacher) als die S/Wverweise und Kombinationen aus Index und Vergleich.

Hi,

hier mal mit 3Möglichkeiten:

[attachment=49263]

Hallo an euch beide,

super - das ist ja toll. Vielen Dank für die Unterstützung. 
@Steve1da: Eigentlich wollte ich tatsächlich direkt den Preis finden, der dann der von dir ermittelten KG-Grenze entspricht.
Trotzdem ist die Formel eine große Hilfe, denn ich könnte die Formel jetzt nutzen als "Hilfsspalte" und dann auf das Gewicht
mit Summenprodukt sauber den Gesamtpreis ermitteln. Von daher hilft mir das weiter. Danke.

@Ego
Die Formel ist auch super, eigentlich eine Top Lösung. Ich hatte mich erst gewundert warum die Formel bei mir nicht funktioniert,
dann habe ich festgestellt, dass der XVERWEIS erst ab Excel 2019 funktioniert. Das habe ich leider noch nicht, trotzdem superinteressant.
Den Verweis kannte ich noch nicht und der ist natürlich mächtig! Vielen Dank.

Liebe Grüße, Excelmonsta

Zitat:@Steve1da: Eigentlich wollte ich tatsächlich direkt den Preis finden, der dann der von dir ermittelten KG-Grenze entspricht.

Das ist mir schon klar. Das war auch eher als Hilfe gedacht, die du direkt in deine Summenprodukt Formel an Stelle von =Tabelle1!$B3 einbaust, ganz ohne Hilfsspalte.

Hallo Edgar,

super - vielen Dank. Das ist natürlich die Lösung in einer Formel. Ganz toll, habe die Formel sogar
"grob" verstanden
Der XVERWEIS ist auch ein toller Verweis, der funktioniert aber leider nicht bei mir mit der Excel-Version.
Aber deine Lösung nutze ich jetzt, die ist perfekt.

Herzlichen Dank

---

@steve1da

Ah, alles klar - super. Habe ich jetzt verstanden
Danke nochmal für die Erläuterung.

LG

Hallo E...,

in deinem Profil steht Office 365.