Clever-Excel-Forum

Hallo,

für eine Abrechnung brauche ich Hilfe, dazu habe ich eine Tabelle angehängt.

Zur Erklärung:

In der Tabelle Frachten sind die Raten eingetragen. Die gelb markierten Felder sind Beträge pro Lieferung. Die grünen Felder sind Raten per 100 kg -> z.B. Shipper/Lieferant C - Fracht Gewicht 300 kg = Frachtrate € 21,00 x 3 = € 61,00.

Wenn in den weißen Feldern nichts steht, ist die Frachtrate für dieses Gewicht die nächst höhere Rate.

Benötige Formeln so, dass Excel ausrechnen soll welche Rate für die Frachtberechnung genommen werden soll, speziell bei den grünen Feldern wo die Rate p. 100 kg ist.

Abrechnung:

Spalte H "Frachtgewicht" soll berechnet werden.
Spalte I "Total" soll berechnet werden.

Vor allem wichtig ist bei der Abrechnung, wenn die Rate für 1750 kg oder 2250 kg genommen werden soll.

Schon mal Dank dafür, wenn ich entsprechende Hilfe bekommen würde.

Gruß

Rkabi1307

Moin,

bei deinem jetzigen Aufbau ist eine automatische Berechnung nicht möglich. Excel kann nicht unterscheiden - und wäre es nicht farblich markiert, ich auch nicht - ob der Pauschalpreis oder der Preis per 100 kg gilt. Zudem haben Leerzellen in der Tabelle nichts verloren.

Du solltest dich also erst einmal um eine vernünftige Tabelle bemühen, dann kannst du diese auch auswerten. SVERWEIS() oder INDEX/VERGLEICH() wären dann mögliche Optionen.

Moin

Zwei Lösungen inside.

Hallo,

erst einmal Danke für die Antworten.

Habe jetzt in meiner Tabelle die leeren Zellen ausgefüllt.

Hoffe dann auf Lösungen.

Bis dann

Rkabi1307

Moin,

du bist leider weder auf meinen Einwand des ungünstigen Tabellenaufbaus eingegangen noch auf die Lösungsvorschläge von shift-del, der eine exzellente Lösung mittels PowerQuery angeboten (die erste Lösung mit FILTER() ist bei dir aufgrund deiner Excelversion nicht möglich). PQ hingegen ist in dieser bereits enthalten (Daten/Daten abrufen und transformieren).

Hallöchen,

den Vorschlag mit den Leerzellen hast Du ja umgesetzt Wenn da nix drin steht weil das nicht vorkommen kann, wäre es bei meinem Ansatz nicht relevant. Hier also der Lösungsansatz, Formel und Ergebnis zum Vergleich in Spalte J. Für die Farbe habe ich einen "Namen" erstellt. Ich habe mal in Spalte K rein informativ die Farbnummer ausgegeben, das kannst Du weglassen. In Spalte L habe ich den zugehörigen Wert. Die Formel für den Wert kannst Du gleich in die Formel integrieren und dann auch diese Spalte leer machen .

Als Bezug für die Kostenzeile hab ich den Shipper genommen. Falls die Kosten jedoch landesabhängig sind, müsstest Du die Formel entsprechend ändern.

Hinweis: Wenn Du die Farbe wechselst, werden die Kosten nicht automatisch neu berechnet, du müsstest F9 drücken. Wenn Dir irgendwann das Hellgrün nicht gefällt und Du nimmst rot oder was anderes, müsstest Du die Formel anpassen.

Wegen der für den Namen verwendeten excel4macro - Funktion ZELLE.ZUORDNEN müsstest Du die Datei als xlsb oder xlsm abspeichern.

Arbeitsblatt mit dem Namen 'Frachtraten und Abrechnung' A B C D E F G H I J K L 17 Land PLZ Lieferant Anzahl Verpackung Gewicht kg CBM Frachtgewicht kg Total (EUR) 18 ES 43800 Shipper A 2 Paletten 1375,0 2,500 1400,0 430,92 € 430,92 35 30,78 19 AT 2170 Shipper B 4 Paletten 1650,0 4,800 1750,0 495,00 € 495 36 495 20 CZ 12345 Shipper C 1 Paletten 221,8 0,871 300,0 63,00 € 63,011925 35 21,003975 21 CZ 98765 Shipper D 5 Paletten 2100,4 6,750 2250,0 482,18 € 482,125 35 21,427778 22 DE 10000 Shipper E 3 Paletten 800,0 3,457 800,0 144,08 € 144,04239 35 18,005299

Name Bezug Rate =ZELLE.ZUORDNEN(63;INDIREKT(ADRESSE(VERGLEICH('Frachtraten und Abrechnung'!$C17;'Frachtraten und Abrechnung'!$A$1:$A$10;0);VERGLEICH('Frachtraten und Abrechnung'!$H17;'Frachtraten und Abrechnung'!$A$4:$ZZ$4;0);1;0);))

Zelle Formel J18 =WENN(Rate=35;H18/100*L18;L18) K18 =Rate L18 =INDEX($A$1:$ZZ$10;VERGLEICH($C18;$A$1:$A$10;0);VERGLEICH($H18;$A$4:$ZZ$4;0))

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

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