Office: Aus Entfernung und Gewicht Tarif ermitteln (SVERWEIS,INDEX,VERGLEICH)

Hallo zusammen,

ich habe folgendes Problem: ich arbeite an einer Datei mit 2 Tabellenblättern. Im ersten Tabellenblatt stehen Orte in Deutschland und England. Zwischen beiden Orten wird die Entfernung und das Gewicht einer beliebigen Sendungen ausgewiesen.

Im zweiten Tabellenblatt befindet sich eine Matrix: In Spalte A werden die Kilometer angegeben (Entfernung) und in Zeile 3 das gestaffelte Gewicht von +500 kg bis +2000 kg.

Im ersten Tabellenblatt in Spalte P möchte ich eine Formel verwenden, welche in derselben Zeile des Tabellenblatts Entfernung und Gewicht mit der Matrix im 2. Tabellenblatt abgleicht und den korrekten Tarif einträgt. Ich habe die entsprechende Zelle in der beigefügten Beispieldatei gelb eingefärbt.

Ich habe hier bereits mit SVERWEIS, INDEX und VERGLEICH herumexperimentiert, aber ich komme leider nicht weiter. Kann mir jemand helfen?

Viele Grüße,
Andreas

:)

 

Hallo Andreas,

Code:

Dafür musst du aber unbedingt im Blatt "DE-GB Tarif" die Spalten mit den Gewichten absteigend ordnen, sprich 2000 kg in Spalte B, 1900 in Spalte C usw.

 

Hallo Andreas,

hier mal mein Lösungsvorschlag mit Hilfe der Aggregat-Funktion.

Gruß von Luschi
aus klein-Paris

 

Vielen Dank, das probiere ich gleich mal aus und melde mich, falls es noch Fragen gibt.

Viele Grüße,
Andreas

 

Melde dich bitte auch, wenn es keine Fragen gibt. Denn dann weiß man, ob das Thema erledigt ist.

 

Hallo Berni,

klar, das mache ich auf jeden Fall.

Grüße,
Andreas

 

Hallo Luschi,

ich muss gestehen, dass ich noch nie mit der Aggregat-Funktion gearbeitet habe, Wenn ich Deine Formel in mein Ratenblatt übertrage, erhalte ich in der gesamten Tabelle "-", also kein Ergebnis oder den Fehler #ZAHL!
Ich habe auch gesehen, dass Du bei der Matrix eine Spalte mit 3500 hinzugefügt hast - welche Bedeutung hat dies?

Viele Grüße,
Andreas

 

Dafür musst du aber unbedingt im Blatt "DE-GB Tarif" die Spalten mit den Gewichten absteigend ordnen, sprich 2000 kg in Spalte B, 1900 in Spalte C usw.

Hallo Mister Burns,

ich habe die Tabelle entsprechend absteigend geordnet und die INDEX-Formel übertragen, erhalte aber leider die Fehlermeldung #NV
Hast Du hier vielleicht noch einen Tipp für mich?

Viele Grüße,
Andreas

 

Jup... Lade die neue Tabelle hoch.

 

Hallo Berni,

sehr gerne - aufgrund der Größenbeschränkung kann ich die komplette Datei leider nicht hochladen (+2000 Zeilen) - daher anbei eine gekürzte Version. Das Ergebnis soll im ersten Tabellenblatt in Spalte P ausgewiesen werden.

Viele Grüße,
Andreas

 

Hallo Andreas,

wenn ich die Mappe öffen und die Entfernung auf Werte ändere, die in der Preistabelle enthalten sind, dann sehe ich sofrt das erwartete Ergebnis.

Die Aggregat-Funktion gibt es noch nicht so lange. Eventuell ist deine Excelversion für diese zu alt.

 

Ich vermute, dass meine Formel nicht funktioniert hat, da du die Tabelle "DE-GB OB" als "intelligente" Tabelle formatiert hast. Scheinbar ist es nicht möglich, in so einer Tabelle die Überschrift als Zahl zu definieren, zumindest habe ich es nicht geschafft. Wenn du diese Tabelle wieder in einen Bereich umwandelst und die Sortierung der Spalten machst wie von mir bereits geschrieben, klappt es entsprechend.
Für deine letzte Beispielmappe wäre die Formel
Code:

Allerdings war dein Beispiel ein schlechtes, da die Distanzen alle über 600 km sind, in der Frachtenliste aber nur bis 99 km aufgelistet sind.

 

Hall WS-53,

Was hast Du denn geändert, um die Nullwerte wegzubekommen? Ich habe Excel 2016 und somit auch die Aggregat-Funktion. Ich habe durch ändern der Parameter teilweise auch Ergebnisse erhalten, die waren aber leider nicht korrekt.

Viele Grüße,
Andreas

 

Hallo Luschi,

Kannst Du mir hier evtl. noch einmal helfen? Siehe mein letzter Post..

Viele Grüße,
Andreas

 

Allerdings war dein Beispiel ein schlechtes, da die Distanzen alle über 600 km sind, in der Frachtenliste aber nur bis 99 km aufgelistet sind.



Hallo Berni,

die Begrenzung der Tabelle auf 99 km war mein Problem beim Upload - leider ist die normale Tabelle mit 2000 Zeilen zu groß, deshalb musste ich diese etwas begrenzen.

Ich habe die Formatierung nun im Blatt DE-GB OB vorgenommen und dieses in einen Bereich formatiert und die Gewichte in der Überschrift als Zahl formatiert. Es werden nun Ergebnisse angezeigt, aber die stimmen wohl nicht alle. Ich überprüfe die Daten jetzt und melde mich ggfs. noch einmal.

Schon mal vielen Dank.

Grüße,
Andreas