Wie kann ich in Excel mein Warenlager mit vorhandene Rezepturen abgleichen. Um zu wissen wieviel ich Herstellen kann?

4 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

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!)

40

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)

1
40
@Iamiam

noch eine Ergänzung auf die Schnelle:

  1. 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.
  2. 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.
  3. Die (anfangs wegverschobene) Optik musst Du nach Geschmack neu erstellen, damit hab ich mich jetzt nicht mehr beschäftigt.

0

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.

2

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.

0
26

einfach die einzelnen Rechnungen in Klammer setzen müsste klappen.

1

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.

 - (Mathematik, Windows, Microsoft)
2

Ganz großes Dankeschön für deine Zeit und Mühe, hat ein wenig gedauert aber ich hab es hinbekommen.

Herzlichen Dank nochmal ;)

0

Werte in Google Spreadsheet abhängig von Text in anderer Spalte summieren

Guten Mittag,

ich habe folgende Tabllen Struktur:

Datum - Konto - Betrag - Zweck

Da steht zum beispiel folgendes drinne:

01.01.2014 - PayPal - 10,00€ - anmeldung 05.05.2014 - Sparkasse - 15,00€ - essen

Jetzt möchte ich abhängig vom Konto die Summe bilden und diese in einer extra Zelle anzeigen!

Geht das? Wenn ja wie?

Vielen Dak für eure Hilfe.

gruß Freddy

...zur Frage

Mit welcher Tastenkombination kann man in Excel einen markierten Bereich um eine Spalte nach rechts/links verschieben?

...zur Frage

Excel 2010: erste Zeile festmachen

Hallo, ich suche die Funktion für Excel 2010 bei der man die erste Zeile einfriert, das heißt dass man sortieren kann, ohne dass die erste Zeile in die restlichen zeilen "wegrutscht"

(ich meine nicht das mit dem Frame, sodass die fest ist und man scrollen kann und immer die erste Zeile sieht)

...zur Frage

Komplexe Vergleich/Sverweis funktion in Excel?

Hallo, Community.

Ich habe eine recht umfassende Tabelle für die Ausleihe von Geräten auf Arbeit.

  • In dieser Tabelle gibt es eine Spalte mit Gerätetyp (TV, Computer, Maus, etc) in welcher nur festgelegte Werte durch Datenüberprüfung ausgewählt werden können.
  • Zusätzlich gibt es eine Spalte mit Ausleihbeginn und Ausleihende (FEST!) Ich plane die Ausleihen also und es wird davon ausgegangen, dass z.b. eine Maus am 01.01.17 abgeholt und am 14.07.17 wiedergebracht wird.
  • noch dazu werden Ausleihen weit vorher angemeldet und in der Liste (unten angefügt) vermerkt welche nicht nach irgendwelchen Kriterien Sortiert ist oder werden soll

Nun möchte ich eine "Auflistung" machen, Wie viele Geräte des jeweiligen Typs ich besitze (fester schon bekannter Wert) und wie viele AKTUELL ausgeliehen bzw schon verplant sind.

Problem dabei ist nur, dass wenn ich ZÄHLENWENN() in der Spalte des Typs anwende, auch die Geräte mitgezählt werden, die noch nicht ausgeliehen wurden, oder schon zurückgegeben wurden (liste geht also in Vergangenheit und Zukunft)

Ich muss also davon Trennen, dass

TYP     START       ENDE
Maus    01.01.17    14.07.17
Maus    16.07.17    24.08.17
Maus    01.09.17    12.12.17

Nur ein Gerät ist und nicht (wie aktuell) 3, weil ZählenWENN 3x Maus liest

Wie stelle ich das an? habt ihr da eine Formel für mich?

Typ befindet sich in der Spalte F(ab 4) und Start in Spalte H(ab 4) und Ende in Spalte I(ab 4)

Zählerstand kann in A50:A60 ausgegeben werden (10 verschiedene ausleihbare Typen vorhanden)

VIELEN DANK!

...zur Frage

Wie kann man die Nullen in der Tabelle sichtbar machen?

Ich erstelle im Programm "Microsoft Works" ähnlich wie Excel eine Tabelle. Eine Art Telefonliste. Wenn ich jetz z.b eine Vorwahl eingebe 0203 dann verschwindet die erste Null nachdem ich auf Enter gedrückt habe. Ich will aber dass sie sichtbar ist. Wie stellt man das ein?

...zur Frage

Exel Zutatenliste

Hallo, kennt sich hier jemand so gut mit Excel aus, dass er mir helfen kann?? Ich möchte gerne eine Tabelle erstellen in die ich Zutaten eintrage, z.B. Zucker, Mehl etc. Dann möchte ich dazu schreiben, wie viel 100Gramm kosten. Im nächsten Schritt möchte ich dann eine Berechnung machen, z.B. Mamorkuchen: enthät 300 Gramm Mehl, 200 Gramm Zucker und am Schluss möchte ich dann eine Gesamtsumme (Preis) haben. Oder gibt es so eine Tabelle/Programm vielleicht schon fertig? Wisst ihr was ich meine?

Freue mich über Rückmeldungen

...zur Frage

Was möchtest Du wissen?