Wie kann ich in Excel mein Warenlager mit vorhandene Rezepturen abgleichen. Um zu wissen wieviel ich Herstellen kann?
Guten Tag
Ich hab eine etwas komplexes Problem und hoffe es halbwegs vernüftig erklären zu können. Damit mir jemand helfen kann.
Ich möchte das Excel mein Warenlager mit verschiedenen Rezepturen abgleicht und mir sagt, wieviel komplette Rezepturen man herstellen kann. Das Bild sollte ungefähr ein Einblick zu meiner Frage geben.
Im Bild sieht man das unter der Rezeptur 1 ab B4 bis B7 Zahlen stehen die Excel mit dem Warenlager rechts ab I4 abgleichen soll. Wenn von allen Zutaten genug für eine Herstellung ist, sollte in der Mitte bei D4 eine ganze natürliche Zahl dargestellt werden.
Rezept 2 beginnt bei B10 bis B14 gleicht das identische Lager ab und gibt bei E4 die maximal mögliche Menge an.
Bei D4 und E4 sollten im Idealfall nun jeweils die Menge erscheinen die man herstellen kann.
Sofern das erreicht ist!
Nun eine Erweiterung des Ganzen:
Wenn bei D4 als Beispiel 8 steht, also ich 8mal die Rezeptur 1 herstellen kann. Möchte ich das sobald ich bei D6 (Wieviel ich tatsächlich hergestellt habe) einen Wert eingeben sich die beiden obrigen (D4 & E4) ändern. Weil sich ja nun das Lager ändert, wegen die nun hergestellten Ware.
Ich hoffe das es jemand gibt der versteht was ich damit sagen möchte. Ergänze auch gerne sofern unklarheiten aufkommen.
Liebe Grüße Akki
3 Antworten
Deine Aufgabe ist noch viel umfangreicher, als Du das formuliert hast, aber der Reihe nach:
Schreib mal in zB
C4: =Index(i:i;Vergleich(A4;H:H;0))/B4
Das liefert den Faktor, für wieviel Ansätze Rezeptur1 das Mehl reichen würde. Nun kopierst Du die Formel runter bis Ende alle Rezepturen und löscht die Formeln zwischen den Rezepturen wieder(sind ohnehin nur Fehler).
Verschiebe erst mal den Bereich D2:F6 weit nach rechts, zB bis Spalte M oder noch weiter, Du brauchst vorne noch ein paar Spalten.
Als nächstes schreibst Du in
D3: =Min(C4:C7)
Das ergibt den Faktor, wieviel Rezeptur1 Du produzieren könntest.
Als nächstes kopierst du diie Min-Formel nach D9, änderst aber die letzte Zelle ab auf hier C14, also =Min(c10:C14), so viele Ansätze Rezeptur 2 wären möglich.
Mach das für alle Rezepte.
Nun schreibst Du in E2: =Max(D:D) und in
E1: =index(A:A;Vergleich(Max(D:D;D:D;0)), das liefert den Namen der Rezeptur, von der du die meisten Ansätze herstellen könntest. (den Namen der Rezeptur mit den 2-meisten Ansätzen bekommst Du mit
=index(A:A;Vergleich(KGrösste(D:D;2);D:D;0))
Insoweit kann xl dich "beraten"), aber Du hast für Deine Entscheidung mit Sicherheit ja noch weitere Kriterien: Kannst du die mögliche Menge verbrauchen/abverkaufen? Gibt es Vorräte nahe des Best-before-Datums, die vorrangig weg sollten? Wieviel bleibt von den anderen Vorräten über: Du hast ja nichts davon, alles billige Mehl zB in Germ-Knödel zu stecken, und dafür die teuren Nüsse und Trockenfrüchte im Lager liegen zu lassen und auf den Verderb von Sahne und Milch zu warten! Ausserdem gibt es sicher noch finanzielle Aspekte: wieviel ist an einem Produkt verdient?
Zurück zur xl-Technik: Änderung des Bestandes:
Erst mal musst Du Dich also entscheiden, wovon Du wieviel Ansätze machst.
Und: die Änderung geht nicht in der gleichen Spalte (i:i), denn sonst gäbe es Zirkelbezug
Schreibe in Spalte F neben jede Komponente, für wieviele Ansätze Du Dich entschieden hast, das geht mit einem Trick sehr schnell: Beispiel 2,5xRezept2:
markiere den Bereich F10:F14 und schreib in die aktive(oberste) Zelle 2,5 (in die Bearbeitungszeile, andernffalls zerstörst du ja die Auswahl!). Nun gibst Du diese Zahl nicht, wie gewohnt, mit Enter ein, sondern mit Strg+Shift+Enter(gleichzeitig) und am besten mit der rechten Strg- und Shift-Taste, dann gehts mit einer Hand und lässt sich auch leichter merken). Schwupps: in allen markierten Zellen steht 2,5.
für alle anderen Rezepte bleibt F leer, allerdings kannst du gleichzeitig mehrere Rezepte addieren, zB F10:F14 2,5 und F4:F7 1,5 (da ich das hier als "Trockenübung" mache, hab ich das mit Deinen Vorratszahlen jetzt nicht abgeglichen)
Nun setzt Du in Spalte j, in J4 die Formel:
=i4-Summenprodukt((A:A=H4)*(B:B)*(F:F)), das ergibt den Restbestand der Einzelvorräte (Summiert das Produkt aller Zeilen, in denen in A:A Mehl=H4 steht mit der rezeptorischen Menge x Zahl der geplanten Ansätze und zieht es vom vorhandenen Vorrat ab).
Formatiere die Zahlen in J:J als negativ=rot und alles Fett, damit ein nicht ausreichender Vorrat sofort auffällt.
Formel jetzt runterziehen: ich hoffe, Du hast Dein Wunschergebnis!
Nachdem alles entschiedenj und gelaufen ist, kopierst Du die Spalte J:J und fügst sie ALS WERTE in i:i wieder ein, überschreibst also die alten Vorräte mit den neuen Beständen.
Da das doch recht kompliziert und länglich war (GF hat mich zwischenzeitlich rausgeworfen, gottseidank hatte ich die AW gesichert und musste nur die Formatierung nacharbeiten), ich stehe am Dienstag wieder für Rückfraggen zur Verfügung (diesen Mo nicht!)
noch eine Ergänzung auf die Schnelle:
- Die Hintergrundfarbe des Warenbestandes solltest Du hell und nicht in einem Rotton machen(schlage aus weitere Farben einen sehr hellen Grau- oder Grünton vor: bester Kontast) , damit negative rote Zahlen auch dort sofort auffallen würden (manchmal übersieht man sowas ja vor der Aktualisierung (genanntes Einfügen als Werte) bzw vergisst, dem Warnhinweis nachzugehen.
- wenn du in Spalte K:K Neueingange im Warenlager verbuchst und in den Formeln in J:J ein +Kn hinzufügst, werden auch die Bestände nach Neueingang sofort aktualisiert.
- Die (anfangs wegverschobene) Optik musst Du nach Geschmack neu erstellen, damit hab ich mich jetzt nicht mehr beschäftigt.
Da ist kein Bild drin, aber wenn du jedes benötigte Teil oder Materialien vergleichst, also (Menge an Lager)/(benötigte Menge um 1s herzustellen) hast du ja die Anzahl für wie viel die Teile reichen würden, nun diese Anzahl von jedem Teil vergleichen und die kleinste dieser Anzahl ist dann die Menge die du herstellen kannst.
Sorry das mit dem Bild ging wohl schief :(
Also alles seperat vergleichen und dann den geringsten Wert als Maximal mögliche Herstellungen. Das klingt einfach, warum bin ich das selbst nicht drauf gekommen xD
Damit bin ich ein Stück weiter, dank dir!
Kannst du mir sagen wie man mehrere Rechnungen in einer Formel unterbekommt. Damit das ganze dann auch in einer Zelle steht.
Mit Deinem Bild wäre es (für Dich) vermutlich einfacher nachzuvollziehen. Jetzt gibt es eben nur mein Bild
Es ist ja einmal die Maximalmenge zu ermitteln je Rezept (abhängig vom Warenbestand). Dann legst Du je Rezept fest wieviel tatsächlich produziert wird und davon ist wiederum die Restmenge abhängig, die für andere Rezepte zur Verfügung steht.
Formel für C2 bis C5: =B2-SUMMENPRODUKT((D2:F2)*(D$9:F$9))
Das berücksichtigt schon, welche Mengen in Zeile 9 eingetragen wurden, die tatsächlich produziert werden sollen. Am Anfang ist das natürlich überall noch 0.
D8 bis F8 ist =MIN(($C2:$C5)/(WENN(D2:D5>0;D2:D5;0,01)))
Das ist eine Matrixformel - nach dem Eingeben STRG+SHIFT+Enter drücken, damit sich noch die { } um die Formel bilden. Das ist wichtig, sonst funktioniert das ganze nicht.
Die Maximale Produktionsmenge wäre einfach zu ermitteln: Jeweils Vorrat / Anteil der Zutat im Rezept. Der kleinste Wert wäre also die Obergrenze, wieviel davon hergestellt werden kann. Vermutlich werden aber nicht alle Zutaten in jeder Rezeptur benötigt. Das würde bei der einfachen Formel zur Fehlern führen. Daher die Wenn-Abfrage ob größer Null. Heißt aber, dass Zutaten, die nicht benötigt werden, nicht einfach leer sind, sondern mit 0 angegeben werden. Sonst liefert die Formel wiederum einen Fehler.
Ganz großes Dankeschön für deine Zeit und Mühe, hat ein wenig gedauert aber ich hab es hinbekommen.
Herzlichen Dank nochmal ;)
noch eine Ergänzung: Du kannst noch Warnhinweise einfügen, zB in H2: =WENN(MIN(J4:J9)<0;ZÄHLENWENN(J4:J9;"<0")&" reichen nicht!";"ok")
und in H3:
=WENN(MIN(J:J)<0;"am meisten fehlt "&WENN(KKLEINSTE(J:J;1)<0;INDEX(I:I;VERGLEICH(KKLEINSTE(J:J;1);J:J;0));"")&", "&WENN(KKLEINSTE(J:J;2)<0;INDEX(I:I;VERGLEICH(KKLEINSTE(J:J;2);J:J;0));"")&", "&WENN(KKLEINSTE(J:J;3)<0;INDEX(I:I;VERGLEICH(KKLEINSTE(J:J;3);J:J;0));"");"alles vorhanden")
Die weisen Dich darauf hin, ob/dass Du Vorräte überzogen hast, zum einen welche Anzahl an Vorräten und zum zweiten, WELCHE genau (bis zu 3 werden namentlich genannt)