Knifflig - optimalen Karton mit Excel berechnen

[JANS2017]

Hallo Zusammen,

ich möchte für meine Artikel den optimalen Karton ermitteln.
Hierfür spielen Maße und Gewichte eine Rolle

   

Bsp. Artikel 1 hat folgende Werte
Länge = 13,5 cm
Breite = 13,5 cm
Höhe = 7,5 cm
Gewicht mit Karton = 983 g

Für diesen Artikel würde in meinem Fall der Karton mit der Gebührenzeile 8 in Frage kommen. (Hier passen erstmalig Maße und Gewichte)
Wie kann ich dies nun ermitteln?

Mein Ansatz. Ich prüfe zunächst für jedes einzelne Maß, welcher Karton in Frage kommt. Für Jeden Wert wird der kleinst mögliche Karton ermittelt. Also :
Länge 13,5 --> würde in Karton 1 passen
Breite 13,5 --> würde auch in Karton 1 passen
Höhe 7 --> hierfür brauche ich mindestens Karton 6-20
Gewicht 983 g --> hier kommen Karton 5 und 8-20 in Frage

Je größer die Kartonnummer, um so teurer der Versand. Von daher gilt es den kleinsten passenden Karton zu ermittlen.
In meinem Beispiel wäre das letztlich Karton Nr. 8. Hier passt der Artikel erstmalig von den Maße und vom Gewicht

Nun die Frage: Welche Formel müsste ich in die Zelle D38 eintragen, so dass automatisch Karton 8 ermittelt wird?

Bin sehr gespannt, was Ihr für Lösungsansätze habt. 
Danke fürs Lesen und Mitmachen

Ciao JANS

[WillWissen]

Hi,

als erstes solltest du, damit die Helfer auch testen können, dein Bildchen in eine echte Datei umwandeln und hier vorstellen.

[JANS2017]

Sorry, das ist natürlich ein guter Hinweis.
Hier noch die Datei.

[lupo1]

Bis auf die zwischengestreuten Briefe bestimmt ALLEIN das Gewicht Dein Paket. Länge, Breite, Höhe egal.

Denn Du führst ein MIN und ein MAX an. Oder Du drückst Dich damit falsch aus.

[JANS2017]

Die Briefe, einfach wie Pakete behandeln.

[Ego]

Hallo Jans,

A) hier zwei mögliche Formeln:

1) für "benötigter Karton":

Code:

{
=INDEX(B4:B23;VERGLEICH(1;(E4:E23>C27)*(F4:F23>C28)*(G4:G23>=C29)*((I4:I23-D4:D23)>=C32);0))
}

Achtung! Matrixformel. Die geschweiften Klammern werden nicht mit eingegeben, sondern die Bearbeitung jedes Mal durch Strg+Shift+Enter abgeschlossen.

2) für "Verpackungsgewicht"

Code:

=INDEX(D4:D23;VERGLEICH(C36;B4:B23;0))

B) Die Spalte "Gesamtgewicht min..." wird nicht benötigt. Das würde eher zu einem Fehler führen falls ein Artikel brutto zB. 4000,5 g wiegen würde.

[JANS2017]

Hallo EGO,

perfekt. :19:
Sehr schlanke Lösung.
Vielen Dank für das schnelle Feedback.

Ciao JANS

[BoskoBiati]

Hi,

das ginge mit einem kleinen Umbau auch so:

Arbeitsblatt mit dem Namen 'Tabelle1'
	B	C	D	E	F	G	H	I
3	Karton	Verpackungstyp	Eigengewicht des Kartons	Länge in cm	Breite in cm	Höhe in cm	Gesamtgewicht max in g	0
4	1	kleiner Briefumschlag	20	20	15	1	100,00	80,00
5	2	Standard-Briefumschlag	40	33	23	2,5	100,00	60,00
6	3		40	33	23	2,5	250,00	210,00
7	4		40	33	23	2,5	500,00	460,00
8	5	Großer Briefumschlag	40	33	23	5	1000,00	960,00
9	6	Standard Paket	100	45	34	26	250,00	150,00
10	7		100	45	34	26	500,00	400,00
11	8		100	45	34	26	1000,00	900,00
12	9		100	45	34	26	1500,00	1400,00
13	10		100	45	34	26	2000,00	1900,00
14	11		100	45	34	26	3000,00	2900,00
15	12		100	45	34	26	4000,00	3900,00
16	13		100	45	34	26	5000,00	4900,00
17	14		100	45	34	26	6000,00	5900,00
18	15		100	45	34	26	7000,00	6900,00
19	16		100	45	34	26	8000,00	7900,00
20	17		100	45	34	26	9000,00	8900,00
21	18		100	45	34	26	10000,00	9900,00
22	19		100	45	34	26	11000,00	10900,00
23	20		100	45	34	26	12000,00	11900,00
24
25
26		Maße und Gewichte	benötigter Karton
27	Länge	13,5
28	Breite	13,5
29	Höhe	7
30	Mindestkartongröße aufgrund Maße
31
32	Artikelgewicht inkl. Artikel-Verpackung	883
33	Verpackungsgewicht (Karton)	100
34	Gewicht der ausgehenden Sendung	983	8
35
36	benötigter Karton		8

Zelle	Formel
I4	=H4-D4
D36	=INDEX(B:B;MIN(23;AGGREGAT(15;6;ZEILE(A4:A23)/(E4:E23>=C27)/(F4:F23>=C28)/(G4:G23>=C29)/(H4:H23>=C32);1)))

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

[Rabe]

Hi,

für den Auswertungsbereich noch zwei Formeln mehr:
Tabelle1

	B	C	D
26		Maße und Gewichte	benötigter Karton
27	Länge	13,5
28	Breite	13,5
29	Höhe	7
30	Mindestkartongröße aufgrund Maße		6
31
32	Artikelgewicht inkl. Artikel-Verpackung	883
33	Verpackungsgewicht (Karton)	100
34	Gewicht der ausgehenden Sendung	983
35
36	benötigter Karton		8

verwendete Formeln
Zelle	Formel	Bereich	N/A
D30	=INDEX(B:B;MIN(23;AGGREGAT(15;6;ZEILE(A4:A23)/(E4:E23>=C27)/(F4:F23>=C28)/(G4:G23>=C29);1)))
C33	=SVERWEIS($D$30;$B$4:$D$23;3;0)
C34	=C32+C33
D36	=INDEX(B:B;MIN(23;AGGREGAT(15;6;ZEILE(A4:A23)/(E4:E23>=C27)/(F4:F23>=C28)/(G4:G23>=C29)/(H4:H23>=C32);1)))

Zahlenformate
Zelle	Format	Wert
C27	'0,00 "cm"	'13,50 cm
C28	'0,00 "cm"	'13,50 cm
C29	'0,00 "cm"	7
C32	'0 "g"	883
C33	'0 "g"	100
C34	'0 "g"	983

Zellen mit Format Standard werden nicht dargestellt

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

gelb: Eingabefelder, weiß: berechnete Felder, grün: Ausgabefeld