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....
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:
Hi,
(17.07.2017, 13:59)WillWissen schrieb: [ -> ]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
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
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 |
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.
Hallo helmut, aber einige deiner (und meiner) Ergebnisse passen nicht zu den händisch eingetragenen Wunschergebnissen...!!!
Hallo Jockel,
ich hatte Exceluser... in seinem Beitrag #12 so verstanden, dass einige seiner Beispielergebnisse fehlerhaft waren.
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 |
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?