Ich habe die letzten paar Stunden damit verbracht, etwas Ähnliches wie mein Problem zu finden, und mir kommt gerade Rauch aus den Ohren!
Ich habe zwei Blätter, eines mitAufträgeder zweite mitVerkäufe.
Ich brauche automatisch HilfeSubtrahierenausAufträgebezogen aufVerkäufe.
Ich versuche ein Skript zu entwickeln, das bei jedem Verkauf ausgeführt wird.StandortUndArtikelsind genau gleich, ziehen Sie die verkaufte Menge von der bestellten Menge ab, um die „neue fällige“ Menge zu erhalten.
Es wird Verkäufe ohne Bestellung geben, was kein Problem darstellt, aber es wäre schön, wenn diese Ergebnisse auf eine andere Registerkarte verschoben werden könnten. Nachfolgend finden Sie eine Zusammenfassung der Anforderungen in Anfängersprache.
Sheet1Istverkauft, Sheet2IstBefehl.
WennSheet1 C2undF2sind dieselben wie jede Zeile in Sheet2(A2,T2 - Der zweite Tagsind korrelierende Felder) subtrahierenSheet1 G2, ausSheet2 F2.
Ich habe ein Beispiel hinzugefügt, das Sie hier ansehen könnenSubtrahieren, wenn mehrere Bedingungen erfüllt sind https://app.box.com/s/2m8nfjo8lieh5mfb9wgspy73f1bvzp0e
Auf der Registerkarte „Ergebnisse“ ist alles, was gelb hervorgehoben ist, das, was sich geändert hätte. Artikel, die nicht gelb hervorgehoben sind, wurden nicht verkauft. Die Formatierung in Grün und Rot diente als Referenz und ist nicht erforderlich, wenn sie zu schwierig ist. (Wäre ideal) Spalte H zeigt, ob dies überhaupt möglich ist, sobald die Bestellmenge 0 erreicht, damit das Skript das Blatt weiter nach der nächsten Bestellung mit denselben Kriterien durchsucht und die verbleibende Menge abzieht. Alles hilft, ich mache das derzeit manuell über das Wochenende und habe im Allgemeinen über 150 Verkäufe pro Woche und es ist sehr zeitaufwändig.
Einfach ausgedrückt versuche ich, ein Skript jede Verkaufszeile durchgehen zu lassen und die Menge vom Bestellschein abzuziehen, wenn Werk und Teilenummer identisch sind. Ich sortiere den Bestellschein danach, wann dies geschieht, sodass die Reihenfolge nicht mehr stimmt.
Vielen Dank für jede Hilfe!
Antwort1
Ich glaube nicht, dass Sie VBA brauchen.
Schauen Sie sich SUMIFS() an
Ich darf mir deine Screenshots nicht ansehen, da der Reverseproxy meiner Organisation keinen Zugriff auf app.box erlaubt, aber ich gehe davon aus:
- Die Spalte „C“ von Blatt1 enthält Standorte.
- Die Spalte „F“ von Blatt1 enthält Elemente.
- Die Spalte „G“ von Blatt1 enthält die verkaufte Menge.
- Die Spalte „A“ von Blatt2 enthält Standorte.
- Die Spalte „D“ von Blatt2 enthält Elemente.
- Die Spalte „G“ von Blatt2 enthält die bestellte Menge.
- Die Spalte „H“ in Blatt2 enthält die fällige Menge
Auf Blatt 2 beträgt die Gesamtverkaufsmenge für den in Zeile 2 aufgeführten Artikel und Standort:
=IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))
Die für Sheet2!H2 verwendete Formel besteht also einfach darin, dies von der bestellten Menge abzuziehen:
=G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))
Wenn die Bestellung jedoch ausgeführt wird und eine nachfolgende Bestellung für denselben Standort und Artikel zumindest teilweise ausgeführt wurde, ist die Gesamtverkaufssumme möglicherweise höher als die Bestellung in Zeile 2, und „Bestellt - Verkauft“ wäre kleiner als Null! Aber davor kann man sich leicht schützen …
=MAX(0,G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))
Dabei werden jedoch keine Bestellungen über der aktuellen Zeile berücksichtigt! Wir haben MIN(0, ThisOrderQuantity- LocationItemQtySold), obwohl wir MIN(0, ThisOrderQuantity+ PreviouslyOrderedQuantities- LocationItemQtySold) haben sollten. Die Summe aller Bestellungen über der aktuellen Zeile in Sheet2 beträgt
=OFFSET(G2,0,0,ROW()-2, 1)
... also... das wird in Zeile 2 kaputt gehen. Wir wollen etwas, das in allen Zeilen funktioniert, einschließlich der ersten. Schützen wir uns also in Zeile 2.
=IF(ROW()<3,0,OFFSET(G2,0,0,ROW()-2, 1))
aber das sind alle vorherigen Bestellungen, obwohl wir nur vorherige Bestellungen für denselben Standort und Artikel möchten. Wir brauchen ein weiteres SUMIFS(). Für Zeile 10 von Sheet2 wäre das
=IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A10,OFFSET(D:D,0,0,ROW()-2,1),D10))
Okay, jetzt können wir sie kombinieren. Für H2, das ganz nach unten durch die Spalte gezogen werden kann, lautet die Formel:
=MAX(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))
Wenn Sie jedoch drei Bestellungen für einen Standort/Artikel haben und nur die erste ausgeführt wird, ist die „fällige“ Menge der dritten Bestellung diese Bestellung plus die Menge der zweiten Bestellung! Wir müssen sicherstellen, dass unsere „fällige“ Menge nicht durcheinander gerät, wenn alle vorherigen Bestellungen noch nicht vollständig ausgeführt wurden. Daher sollten wir eine MAX-Funktion hinzufügen, damit die fällige Menge nie größer ist als die bestellte Menge.
=MIN(G2,MIN(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))))
Fügen Sie das in H2 ein, ziehen Sie die Formel über den Rest von H, debuggen Sie, was ich getan habe (weil, Spoiler, ich habe es nicht getan), und fertig! Kein VBA erforderlich.
(Warnung: Sie haben ein „Nicht-Thema“ von Verkäufen ohne Bestellungen erwähnt. Bitte bedenken Sie, dass Sie in einem solchen Fall, wenn Siespäterhaben Sie eine Bestellung, diese wird sofort ausgeführt!)
Update 3. Februar 2017: Problem mit MIN und MAX behoben; es hätte MAX und MIN sein sollen.