Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1400to1404
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1400to1404
1400to1404
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Versandpreis ermitteln anhand Größe & Gewicht

Versandpreis ermitteln anhand Größe & Gewicht
19.01.2015 17:05:47
Dominic

Hallo zusammen,
ich benötige eine Formel/Funktion in der mir Excel anhand einen Versandpreis anhand Größe und Gewicht einer Sendung ermittelt.
https://www.herber.de/bbs/user/95100.xlsx
Mit der Index Funktion bekomme ich dies nicht ganz hin...
Die 4 benötigten Werte, damit Excel nach den Versandkosten "suchen" kann, kann der User in folgenden Zellen eintragen:
B11 = Produktgewicht
B13 = Breite
B14 = Höhe
B15 = Tiefe
Wenn ich nun die folgenden Daten eintrage:
B11 = 50
B13 = 26,5
B14 = 13,5
B15 = 2,0
Soll Excel mir in der angehängten Tabelle in Zeile B17 den Wert 0,40 € ausgeben.
Wie bekomme ich das hin? Ich stehe gerade echt auf dem Schlauch, unter anderem auch da die Werte in Spalte H nicht korrekt aufsteigend sortiert sind, dadurch geht es mit "Index" nicht richtig, oder?
Vielen Dank im Voraus!

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
mit INDEX(), VERGLEICH() und MAX() ...
19.01.2015 17:55:41
neopa
Hallo Dominic,
... ich nehme an, dass immer der größte Teil-Vergleichswert maßgebend ist, dann so:
Tabelle1

 ABCDEFG
1Verpackungsgewicht in gBreite in cmHöheTiefeGewicht von in gGewicht bis  in gPreis
225201010500,25 €
3503020205000,40 €
4753020501.0000,90 €
512545342605001,16 €
61254534265012.0001,81 €
71254534262.00012.0003,06 €
8       
9farblich unterlegte Felder bitte ausfüllen      
10       
11Produktgewicht50     
12       
13Breite26,5     
14Höhe13,5     
15Tiefe2,0     
16       
17Die Versandkosten betragen0,40 €     
18       

Formeln der Tabelle
ZelleFormel
B17=INDEX(G:G;MAX(VERGLEICH(MAX(B2;B13); B1:B7); VERGLEICH(MAX(C2;B14); C1:C7); VERGLEICH(MAX(D2;B15); D1:D7); VERGLEICH(B11;F:F)))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: mit INDEX(), VERGLEICH() und MAX() ...
19.01.2015 17:59:51
Dominic
Hi Werner,
vielen Dank, ich probiere es gleich aus.
Ja genau, der größte Wert zählt - entweder vom Gewicht oder von den Abmessungen her und dieser Wert wird dann herangezogen. :)
z. B. auch wenn das Gewicht eines Produktes nur 20g beträgt und es damit 0,25€ im Versand kosten würde, kostet der Versand 0,90 € weil die Maße des Produktes 26,5 x 13,5 x 2,5 beträgt.

mir stellt sich da jetzt noch die Frage ...
19.01.2015 18:11:59
neopa
Hallo Dominic,
... inwieweit muss das Verpackungsgewicht noch berücksichtigt werden?
Gruß Werner
.. , - ...

AW: mir stellt sich da jetzt noch die Frage ...
19.01.2015 18:26:20
Dominic
Für dieses Beispiel gar nicht. In einer anderen Abfrage will ich äquivalent zum € Betrag das anfallende Verpackungsgewicht errechnen / bzw. ausgeben lassen.

Anzeige
da reicht dann INDEX() und VERGLEICH() ...
19.01.2015 18:34:06
neopa
Hallo Dominic,
... so: =WENN(B17="";"";INDEX(A:A;VERGLEICH(B17;G:G;)))
Analog hier, würde ich übrigens auch in B17 eine Abfrage auf Vollständigkeit der Eingaben vornehmen. Z.B. derart: =WENN(ANZAHL(B11;B13:B15) Gruß Werner
.. , - ...

AW: da reicht dann INDEX() und VERGLEICH() ...
20.01.2015 08:53:54
Dominic
Hallo Werner,
funktioniert leider nicht. Beide Formelvorschläge.
Wenn du folgende Testeingaben tätigst kommen immer falsche Versandkosten raus:
Produktgewicht: 50
Breite 26,5
Höhe 13,5
Tiefe 2,0
korrekt 0,40 €
============
Beispiel 2:
Produktgewicht: 50
Breite 31,5
Höhe 13,5
Tiefe 2,0
korrekt 0,90 €
===== Fehlerhafte Berechnungen ===
Beispiel 3:
Produktgewicht: 50
Breite 46,5
Höhe 13,5
Tiefe 2,0
falsch 3,06 € korrekt wäre 1,16€ (Aufgrund der Breite von 46,5 cm aber dem geringen Gewicht sind es nur 1,16€)
===== Fehlerhafte Berechnungen ===
Beispiel 4:
Produktgewicht: 50
Breite 26,5
Höhe 13,5
Tiefe 2,5
falsch0,40 € korrekt wäre 0,90€ (Vom Gewicht her wären 0,40 € korrekt, aber die Abmessungen (Tiefe) überschrieten die max. zulässige Grenze von 2,0 cm, daher müssen hier 0,90€ herangezogen werden.
Ich habe keine Ahnung wie die Formel erweitert / geändert werden müsste.

Anzeige
dann für VERGLEICH() besser MIN() ...
20.01.2015 09:49:55
neopa
Hallo Dominic,
... sorry, meine gestrige Lösungsformel war nicht richtig durchdacht.
Besser (und kürzer) wohl so:
 ABCDEFG
1Verpackungsgewicht in gBreite in cmHöheTiefeGewicht von in gGewicht bis  in gPreis
225201010500,25 €
3503020205000,40 €
4753020501.0000,90 €
512545342605001,16 €
61254534265012.0001,81 €
71254534262.00012.0003,06 €
8       
9farblich unterlegte Felder bitte ausfüllen      
10       
11Produktgewicht50     
12       
13Breite46,5     
14Höhe13,5     
15Tiefe2     
16       
17Die Versandkosten betragen1,16 €     
18 125     
19       

Formeln der Tabelle
ZelleFormel
B17{=WENN(ANZAHL(B11;B13:B15)<4;"";INDEX(G:G;MIN(WENN((MIN(MAX(B2;B13); B7)<=B2:B7)*(MIN(MAX(C2;B14); C7)<=C2:C7)*(MIN(MAX(D2;B15); D7)<=D2:D7)*(MIN(MAX(F2;B11); F7)<=F2:F7); ZEILE(Z2:Z7)))))}
B18=WENN(B17="";"";INDEX(A:A;VERGLEICH(B17;G:G;)))
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: dann für VERGLEICH() besser MIN() ...
20.01.2015 11:52:12
Dominic
Hallo Werner,
och super, perfekt. DAnke! Funktioniert einwandfrei! Vergiss daher meinen vorherigen / übereilten Nachtrag. :)

AW: da reicht dann INDEX() und VERGLEICH() ...
20.01.2015 11:46:28
Dominic
WENN(ANZAHL(B11;B13:B15)<4;"";INDEX(G1:G7;VERGLEICH((B2;B13);B1:B7;1);VERGLEICH((C2;B14); C1:C7;1);VERGLEICH((D2;B15);D1:D7;1);VERGLEICH(B11;F1:F7))))
Ich hab es nun über "Vergleich" allein versucht, ohne "max" und dem Vergleichstyp 1 für "kleiner als"....
die Formel hier funktioniert aber noch nicht ganz.

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige