Ich habe mehrere „Mischungen“, für die ich Kosten erstellen möchte. Derzeit wird jede Mischung der Übersichtlichkeit halber in Komponentenform in einer Zelle angezeigt, die Anzahl der Einheiten steht in einer separaten Spalte innerhalb der Tabelle und die Kosten pro Einheit werden in einem separaten Blatt in der Arbeitsmappe in einzelne Komponenten aufgeschlüsselt.
Beispiel: Auf Blatt 1 habe ich Folgendes: Zelle C2 ist „3“ (Anzahl der Einheiten), C3 ist „2“ und C4 ist „4“. Zelle D2 ist „1 Tasse Mehl, 0,25 Tassen Milch, 1 Ei“. Zelle D3 ist „2 Tassen Mehl, 0,33 Tassen Öl, 2 Eier“. Zelle D4 ist „1 Tasse Mehl, 1 Ei“. Ich möchte, dass Spalte E eine Formel zur Berechnung der Kosten basierend auf den Preisen in Blatt 2 enthält, die die Kosten für Mehl in Zelle B2 mit „0,05“, die Kosten für Milch in Zelle B3 mit „0,08“, die Kosten für Eier in Zelle B4 mit „0,10“ und die Kosten für Öl mit „0,04“ angibt. Ich habe auch allgemeine Mischkosten von „0,25“, die ich pro Einheit anwenden möchte und die nirgendwo anders als in Zelle B16 von Blatt 2 aufgeführt sind.
Derzeit konfiguriere ich jede Formel in Spalte E manuell, was zeitaufwändig ist und zu Fehlern führen kann. Ein Beispiel hierfür für Zelle E2 ist „=3*(1*.05+.25*.08+1*.1+.25)“
Zwei Optionen, die mir eingefallen sind, sind die Erstellung einer Mix-Spalte, die Mix 1, Mix 2 und Mix 3 anzeigt, für die ich dann Stückkosten festlege, mit denen ich multiplizieren und für jede Kombination einen einzigartigen Mix erstellen kann (derzeit etwa 15 Mixe und es werden mehr). Beispiel: Spalte F ist der Mix-Preis, also wird die Formel in E2 zu „=C2*F2“. Meine Frage ist, ob ich eine IF()-Anweisung verwenden kann, um diese Spalte automatisch basierend auf den Daten in Spalte D zu füllen, da ich Spalte F beim Drucken von Daten ausblenden möchte und es auch einfacher wäre, im Blatt zu navigieren, wenn die Spalte beim Eingeben von Daten ausgeblendet ist.
Die andere Möglichkeit besteht darin, eine Formel zu erstellen, die Spalte D in einzelne Komponenten aufteilt und dann mit den einzelnen Werten multipliziert und dann alle Daten der einzelnen Spalten ausblendet. Die Formel würde dann etwa lauten: „=Einheiten*(Datenpunkt 1+Datenpunkt 2+Datenpunkt 3+Mischdatenpunkt)“. Meine Sorge bei diesem Ansatz ist, dass das Blatt sehr groß und schwer zu navigieren wird, wenn ich die Mischwerte 3-4 Mal pro Zeile berechne und 5-6 Zutaten habe.
Alle anderen Ideen oder Ansätze wären hilfreich.
Antwort1
Ihre Beschreibung ist wirklich umständlich,
aber ich glaube, das Folgende gibt Ihnen eine Vorstellung davon, wie man es machen kann ...
--- als CSV-Datei speichern und in Excel oder LibreOffice öffnen ---
,,,, ,Artikel A,Artikel B,Artikel C,Summe ,100,10,1, ,,,, Mischen,,,, "=DEC2BIN(ZEILEN($A$6:A6),SPALTEN($B$2:$D$2))","=WERT(MITTEL($A6,SPALTEN($B$2:B$2),1))","=WERT(MITTEL($A6,SPALTEN($B$2:C$2),1))","=WERT(MITTEL($A6,SPALTEN($B$2:D$2),1))","=SUMMENPRODUKT($B$3:$D$3,B6:D6)"--- Ende der Datei ---
Kopieren Sie Zeile 6 (letzte Zeile) nach unten, um sieben Zeilen (sechs Kopien) zu erhalten.
Ändern Sie die Zahlen in Zeile 3 und sehen Sie, was passiert.
Die Tabelle unter „Mix“ tut dies:
Spalte A – erstellt eine Binärzahl basierend auf der Zeilenposition (001 für die erste Zeile, 111 für die siebte Zeile).
Spalten B, C und D – wählt die Binärziffer an der Position aus, die der Spalte entspricht, und macht daraus eine „Zahl“, damit wir Berechnungen damit durchführen können.
Spalte E – wird verwendet SUMPRODUCT(), um die „Mix“-Kosten zu berechnen.
SUMPRODUCT(Array1, Array2, ...)
nimmt das erste Element in Array1 und Array2, multipliziert sie, macht dasselbe mit Element 2 und addiert das, dann Element 3 und so weiter …
Es kann eine Reihe von Arrays verarbeiten, nicht nur zwei, es gibt jedoch eine Grenze.
In der obigen CSV-Datei ist die dritte Zeile ein Array mit „Kosten“, dann werden die Zeilen ab Zeile 6 als „Auswahl“-Arrays verwendet, um die sieben möglichen Mix-Kosten für die drei Elemente zu erstellen (Zeilen 2 hoch drei für drei Elemente, von denen mindestens eines ausgewählt ist).
*Formeln:
Eine Spalte kann geändert werden, um mehr Elemente zu verarbeiten, indem einfach das COLUMNS-Argument geändert wird.
Spalte B kann in „beliebige“ Spaltenanzahl kopiert werden (drei sind in der CSV-Datei vorhanden).
„beliebig“ ist jedoch in Spalte A durch „DEC2BIN“ begrenzt, es können in Excel nur zehn Ziffern erzeugt werden.
Spalte E muss dementsprechend ebenfalls angepasst werden – das überlasse ich als letzte Aufgabe, die Sie selbst erledigen müssen; wirklich nicht sehr schwer.
Wenn Sie diese sechste Zeile haben, können Sie sie für die erforderliche Zeilenanzahl nach unten kopieren. Die letzte muss in der Mix-Tabelle nur „1“ enthalten.