Artikelsuche mit Restriktionen

[radagast]

heje excekfreunde,

die frage ist doch: drei Bedingungen = A, B und C....
wie sind diese untereinander verknüpft....  and,  or,  nor und so weiter.
diese exakten angaben fehlen....

[Exceluser123]

Hi Ego,

ich sehes gerade selber einige Ergebnisse sind fehlerhaft...

Aber Danke für deine Hilfe!!!

Versuche jetzt erstmal die Formel zu verstehen und beim nächsten mal selbst anzuwenden :19:

[Rabe]

Hi,

Du darfst dir gerne mal den Unterschied zwischen Minimum und minimal ansehen:

das eine ist ein Substantiv (das: geringstes, niedrigstes Maß) und das andere ein Adjektiv (sehr klein, sehr gering), es führt aber für mich zum gleichen Ergebnis als Wunschwert:
die Differenz zwischen den beiden Werten soll möglichst klein sein bei verschiedenen Kombinationen

[Gast 123]

Hallo

ich habe mir beide Beispieldatein angesehen und denke das es in beiden noch Fehler gibt.  Im Beispiel (1)
Werkstück  250/120 = Artikel 2     ** statt kein Ergebnis !!
Werkstück  450/280 = Artikel 9

Bei der Formel von Ego blicke ich nicht durch wie sie funktioniert, Formeln sind nicht mein Fachgebiet.
Ich dachte an eine Vba Lösung, dann fiel mir aber auf das die Einkauf Masse nicht fortlaufend sind. 
Nach Artikel 7 mit 600 mm kommt noch mal 250 - 600 mm.  Das erfordert einen mehrfach Suchlauf.
Da ist es günstiger die Daten vorher in einer Hilfsspalte nach Grösse zu sortieren.  Sind Hilfspalten erlaubt?

Ich warte mal ab ob es eine Formel Lösung gibt, könnte einfacher sein als programmieren.

mfg  Gast 123

[Jockel]

Hallo, wieso soll hier 9 herauskommen..? Sind das keine UND Bedingungen..?

Arbeitsblatt mit dem Namen 'Tabelle1'
	C	D	E	F	G	H	I
3	Mögliche Einkäufe				Werkstückmaße
4	Artikel Nr.	DAEK	DIEK		DAWE	DIWE	Auswahl
5	[1]	[mm]	[mm]		[mm]	[mm]	[1]
6	1	200	100		250	120	kein Erg.
7	2	250	150		300	200	3
8	3	300	200		300	220	3
9	4	330	200		400	250	9
10	5	350	220		450	280	kein Erg.
11	6	360	240		450	300	10
12	7	600	400		500	350	11
13	8	250	200		520	350	12
14	9	400	300		550	400	13
15	10	450	300		600	450	13
16	11	500	320		620	420	13
17	12	520	340		630	480	13
18	13	650	500		650	490	13
19
20
21		Restritktion
22		DAEK >= DAWE
23		DIEK<= DIWE
24		DAEK-DAWE ->MIN
25

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

[Ego]

Hallo,

@ Gast 123

Ich kann deine Fehlermeldung nicht nachvollziehen. In beiden von dir vorgeschlagenen Ergebnissen ist die zweite Nebenbedingung nicht erfüllt.


Bei Artikel 2 ist DIEK gleich 150 und so eindeutig grösser als 120.
Bei Artikel 9 ist DIEK gleich 300 und so eindeutig grösser als 280.

@ zur Formelauswertung:

Das sollte man immer von innen nach aussen machen.

A := ($D$6:$D$18-G6)
hier wird eine Liste mit der Differnz aller Aussendurchmesser der Einkäufe von der gesuchten Aussendurchmesser zur Bestimmung des späteren Minimums gebildet.


B:= (A*1000+$E$6:$E$18)
Zur eindeutigen Identifizierung der Artikelnummer benötige ich bei mehrfach vorkommenden gleichen Aussendurchmessern noch den Innendurchmesser der Einkäufe.
Wenn ich alle Einträge der Liste A mit 1000 multipliziere und einen Wert < 1000 addiere ändert sich nicht das Minimum.

C:=(($D$6:$D$18>=G6)*($E$6:$E$18<=H6))
Hier werden die Nebenbedingungen der Einkaufsliste ausgewertet. Ich bekomme in den einzelnen Listen ein Wahr, wenn die Bedingung erfüllt ist, und ein Falsch, wenn sie nicht erfüllt ist.
Bei der Multiplikation wird Wahr durch 1 und Falsch durch 0 ersetzt und ich erhalt also eine Liste von Nullen und Einsen. Bei Einsen sind beide Nebenbedingungen erfüllt.

D:= B / C
Das ist ein häufig genutzter Trik (besonders bei der Aggregat-Funktion). In den Fällen in denen in C eine 1 steht bleibt im Ergebnis der Wert von B erhalten, in den anderen Fällen erhalte ich einen Fehlerwert, da durch 0 dividiert wird.

E:= AGGREGAT(15;6;D;1)
Die Aggregatfunktion ist eine Zusammenfassung von mehreren in Excel vorhandenen Funktionen. Wichtig ist hierbei der zweite Parameter. Mit ihm kann man festlegen welche Einträge in den Listen nicht berücksichtigt werden sollen.
Zu den Parametern:
15 := Funktion KKLEINSTE (man könnte hier auch 5:= MINIMUM nutzen und benötigt dann nicht mehr den 4. Parameter)
6:= Listeneinträge mit Fehlerwerten sollen nicht berücksichtigt werden.
1 := zweiter Parameter für die Funktion Kkleinste (1 also das Minimum)

F:= VERGLEICH(E;($D$6:$D$18-G6)*1000+$E$6:$E$18;0))
Hier wird die Zeile des Bereiches ermittelt die gleich dem gefundenen Minimum E ist.

G:= INDEX($C$6:$C$18;F)
Es wird der Eintrag aus den Artikelnummern in der gleichen Zeile gesucht.

H:= WENNFEHLER(G;"kein Erg.")
Falls kein Einkaufsartikel beide Nebenbedinungen erfüllt wird der Text ausgegeben.

[Jockel]

Hallo helmut, aber einige deiner (und meiner) Ergebnisse passen nicht zu den händisch eingetragenen Wunschergebnissen...!!!

[Ego]

Hallo Jockel,

ich hatte Exceluser... in seinem Beitrag #12 so verstanden, dass einige seiner Beispielergebnisse fehlerhaft waren.

[Jockel]

Hallo, also ich komme auf die selben Ergebnisse (trotz oder zufälligerweise weil) ohne die Minimum-Geschichte mit..:

Arbeitsblatt mit dem Namen 'Tabelle1 (2)'
	C	D	E	F	G	H	I
3	Mögliche Einkäufe				Werkstückmaße
4	Artikel Nr.	DAEK	DIEK		DAWE	DIWE	Auswahl
5	[1]	[mm]	[mm]		[mm]	[mm]	[1]
6	1	200	100		250	120	kein Erg.
7	2	250	150		300	200	3
8	3	300	200		300	220	3
9	4	330	200		400	250	kein Erg.
10	5	350	220		450	280	kein Erg.
11	6	360	240		450	300	10
12	7	600	400		500	350	11
13	8	250	200		520	350	12
14	9	400	300		550	400	7
15	10	450	300		600	450	7
16	11	500	320		620	420	kein Erg.
17	12	520	340		630	480	kein Erg.
18	13	650	500		650	490	kein Erg.

Zelle	Formel
I6	=WENNFEHLER(AGGREGAT(15;6;$C$6:$C$18/($D$6:$D$18>=G6)/($E$6:$E$18<=H6);1);"kein Erg.")

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

 
... eine Abweichung mit..:

Arbeitsblatt mit dem Namen 'Tabelle1 (3)'
	C	D	E	F	G	H	I
3	Mögliche Einkäufe				Werkstückmaße
4	Artikel Nr.	DAEK	DIEK		DAWE	DIWE	Auswahl
5	[1]	[mm]	[mm]		[mm]	[mm]	[1]
6	1	200	100		250	120	kein Erg.
7	2	250	150		300	200	3
8	3	300	200		300	220	3
9	4	330	200		400	250	kein Erg.
10	5	350	220		450	280	kein Erg.
11	6	360	240		450	300	10
12	7	600	400		500	350	11
13	8	250	200		520	350	12
14	9	400	300		550	400	kein Erg.
15	10	450	300		600	450	7
16	11	500	320		620	420	kein Erg.
17	12	520	340		630	480	kein Erg.
18	13	650	500		650	490	kein Erg.
19
20
21		Restritktion
22		DAEK >= DAWE
23		DIEK<= DIWE
24		DAEK-DAWE ->MIN

Zelle	Format	Wert
I6	[=0] "kein Erg.";Standard	0

Zelle	Formel
I6	=SUMMENPRODUKT((ZEILE($C$6:$C$18)-5)*($D$6:$D$18>=G6)*($E$6:$E$18<=H6)*($D$6:$D$18=G6))

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

[Ego]

Hallo Jockel,

natürlich ist es nur Zufall, wenn du ohne die Berücksichtigung der dritten Bedingung die richtigen Ergebnisse bekommst.

Im ersten Beispiel nimmst du die kleinste Artikelnummer für die die ersten beiden Nebenbedingungen erfüllt sind.
Gegenbeispiele lassen sich hier einfach aufstellen.

Im zweiten Beispiel ist der vierte Faktor keine Nebenbedingung und wenn doch, wäre der zweite überflüssig. Wenn zufällig mehrere Artikel die jetzt drei Nebenbedingungen erfüllen würde die Summe der Artikelnummer ausgewiesen werden.

Aber der zweite Vorschlag ist schon sehr fraglich (bzw. gemein). Wie kann man anhand einer Beispieldatei, in der die Artikelnummer zufällig fortlaufend nummeriert sind diesen Vorschlag mit der Umrechnung der Zeilennummer in Artikelnummer machen?