SVERWEIS mit mehreren Ergebnissen, wenn Lagerbestand ins Minus geht - HILFE

[AshleyxD]

Hallo,

ich benötige dringend Hilfe bei einer Excelformel.
Ich möchte gerne mithilfe einer Formel in Spalte O im Tabellenblatt "Basis" die Auftragsnummern aus Tabellenblatt "offene Produktionen" eintragen lassen, sofern der Bestand in Spalte L im Minus ist.
In der darauffolgenden Zeile sollte dann der nächste Fertigungsauftrag (aus Tabellenblatt "offene Produktionen") angezeigt werden, wenn der Lagerbestand + die neuen Zugänge (Spalte M) ins Minus geht.
Hat jemand eine Idee, mit welcher Formel ich das Problem lösen kann?

Anbei die Datei.

Vielen Dank bereits vorab! :)

[Elex]

Hi,

manchmal ist weniger mehr. Weniger Daten dafür mehr Wunschergebnisse in den Zellen würde den Helfern sicher viel Zeit sparen sich in dein Vorhaben zu denken.
Was mich bei deinen Beschreibungen verwundert, das Datum scheint dabei wohl keine Rolle spielen.
Also ich kann dein Anliegen so noch nicht ganz nachvollziehen.

Gruß Elex

[Ralf A]

Hi,

ich schau mir Deine Datei jetzt nicht an, kann Dir aber sagen, SVerweis liefert immer nur einen Treffer. Mehr ist da nicht drin. Du könntest aber mit Filtern arbeiten. Da würde dann u.U. 1 Tabellenblatt genügen. Kommt halt immer auf die Struktur an...

[AshleyxD]

Hallo Elex,



danke für deine Antwort.




Ich habe die Datei auf zwei Artikel gekürzt und in Spalte O (Tabellenblatt "Basis" - Spalte "Aufträge") meine Wunschergebnisse reingeschrieben, welche sich aus dem Tabelltenblatt ("offene Produktionenen") ziehen.




Die Produktionstermine sind insofern nur relevant, dass die Aufträge, sobald der Bestand in Spalte N ins minus geht, der nächste Auftrag ausgegeben wird (im Reiter "offene Produktionen" sind die Aufträge aber schon terminlich in aufsteigender Reihenfolge sortiert). Sobald der Bestand inkl. der vorherigen Zugänge auf Null geht, soll der nächste Auftrag in Spalte O ausgegeben werden.




Beispiel: Material 86515615


Der Bestand wird am 06.04. leer laufen, weshalb der nächste Auftrag des Materials in Spalte O (1675359) eingegeben werden soll.


Ich habe bereits eine Formel eingegeben, die die Produktionsmenge in Spalte M (Zugänge) einträgt. Und in Spalte N rechnet es den Zugang auf den Bestand mit drauf.


Der nächste Auftrag (1675360) würde dann am 15.04. benötigt werden, da der Bestand dann wieder ins Minus läuft.




Ich benötige eine Formel, die die Fertigungsaufträge per Artikel einträgt, sobald der Bestand ins Minus läuft, da ich sehr große Datenmengen habe.




Ich würde mich sehr freuen, wenn du dir mein Problem nochmal ansehen würdest.

LG

[Elex]

Moin

Du arbeitest noch mit dem alten xls Format.
Warum, muss die Datei in älteren Excel(vor 2007) laufen? Es geht hier darum welche Funktionen eingesetzt werden können für eine Lösung.

Auch der ganze Aufbau mit den zwei Spalten für die Verrechnung von Bestand, Zu und Abgang ist irgendwie fraglich. Ist das nicht in einer besser?

Ich hänge einfach mal was an.

[AshleyxD]

Hallo Elex,

vielen Dank für deine Hilfe.
Die Datei kann auch in einer neueren Excelversion gespeichert werden.
Generell wäre eine Spalte für den Bestand ausreichend, ich hatte das nur für mich als Hilfsspalte zur Berechnung.
Gibt es eine Möglichkeit, dass die Auftragsnummer eine Zeile weiter unten ausgegeben wird (in der gleichen Zeile, wie die Zugänge), ansonsten wäre die Formel perfekt?
Falls der Bestand nämlich gleich in der ersten Zeile im Minus ist, wird erst in der darauffolgenden Zeile der Auftrag ausgegeben.

LG

[Elex]

Dann so.

[AshleyxD]

Hallo Elex,

leider gibt es noch zwei Probleme bei der Formel:

1. wenn der Bestand schon in der ersten Zeile ins Minus geht (z. B. wenn kein Lagerbestand vorhanden ist), wird der Auftrag erst in der darauffolgenden Zeile ausgegeben.
2. Wenn der neue Zugang z. B. 10000 ist, aber der Bestand > 10.000 ins Minus geht, wird der Auftrag zweimal ausgegeben. --> In dem Fall müsste ein Auftrag eine Zeile vorher ausgegben werden.

Siehe Beispiel anbei.
Wäre super, wenn du mir dabei noch behilflich sein könntest.

LG

[Elex]

Hallo

Versuch die in O21.

Code:

=WENNFEHLER(WENN((D20<>D21)*(I21>=K21);INDEX('offene Produktionen'!$B$1:$B$10000;AGGREGAT(15;6;ZEILE($A$1:$A$10000)/('offene Produktionen'!$A$1:$A$10000=D21);1));WENN((D20=D21)*((L20-I21)<0);INDEX('offene Produktionen'!$B$1:$B$10000;AGGREGAT(15;6;ZEILE($A$1:$A$10000)/('offene Produktionen'!$A$1:$A$10000=D20);ZÄHLENWENNS($D20:D$20;D20;$O$20:O20;">0")+1));""));"")

Gruß Elex

[AshleyxD]

Hallo Elex,

vielen lieben Dank!!! Jetzt funktioniert es perfekt! :)
Du glaubst gar nicht wie lange ich schon selbst nach dieser Lösung gesucht habe und wie viele Stunden Zeit ich mit Kopfzerbrechen verbracht habe.

Darf ich dich noch Fragen, wie die Formel funktioniert?
ZÄHLENWENNS UND die Funktion Zeile kenn ich ein bisschen, aber mit der Aggregatfunktion habe ich noch nie gearbeitet.

=WENNFEHLER(WENN((D23<>D24)*(I24>=K24);INDEX('offene Produktionen'!$B$1:$B$10000;AGGREGAT(15;6;ZEILE($A$1:$A$10000)/('offene Produktionen'!$A$1:$A$10000=D24);1));WENN((D23=D24)*((L23-I24)<0);INDEX('offene Produktionen'!$B$1:$B$10000;AGGREGAT(15;6;ZEILE($A$1:$A$10000)/('offene Produktionen'!$A$1:$A$10000=D23);ZÄHLENWENNS($D$20:D23;D23;$O$20:O23;">0")+1));""));"")

Und warum wird am Schluss der Formel noch +1 gezählt?

LG
Ashley