Vereinfachen Sie eine Formel mit vielen IF / AND / OR-Anweisungen, indem Sie möglicherweise bedingte Formatierungen erkennen

Question

Vereinfachen Sie eine Formel mit vielen IF / AND / OR-Anweisungen, indem Sie möglicherweise bedingte Formatierungen erkennen

347

Mic Fitzgerald 2018-06-05 в 17:40

Ich versuche, eine Formel zu entwickeln, die ein bestimmtes Material (entsprechend einem Part NumberWert) analysiert, das mehrere Werte MSeiner Hierarchie enthält. Dies geschieht in vielen verwandten ERP-Unternehmen aufgrund falscher Dokumentation und ordnungsgemäßer Pflege im Materialstamm. Der korrekte Wert kann nur aus den entsprechenden MRPcnund X-PlantWerten bestimmt werden, die immer konsistent sind, da sie auf der Client-Ebene in SAP basieren.

Ich habe die MRPcnSpalte bedingt formatiert, sodass die Werte gemäß der Legende nach Farbe kategorisiert werden. Die bedingte Formatierung wurde vorgenommen, da es mehr als 500 eindeutige MRPcnWerte gibt. Auf diese Weise könnte ich bei Bedarf eine Formel anhand der Farben filtern oder schreiben.

Es gibt 20 eindeutige X-PlantWerte. 7 davon werden für die MSWerte verwendet. Dies sind P2(Produktion), N2(Engineering), 18/ 19/ 4/ 1(Veraltet) und 15(Global Ops / Service). Diese wurden auch bedingt formatiert. Beachten Sie, dass einige X-PlantWerte nicht nur unter diese Kategorien fallen, sondern diese vier Werte sind für das gesamte Dataset relevant und konsistent.

Ich habe versucht, IF/ AND/ OR-Anweisungen zu verwenden, und habe zuvor einige fortgeschrittene Arbeiten mit diesen ausgeführt, aber die Formel wird am Ende groß und unhandlich sein, da viele verschiedene eindeutige Werte berücksichtigt werden müssen. Außerdem habe ich auch Probleme, eine Teilformel mit nur wenigen Werten zu erstellen.

Da der korrekte Wert von MStatsächlich von der Kategorie des MRPcnWerts und der Kategorie des X-PlantWerts abhängt, hoffte ich, die Hintergrundfarbe der Zellen (die der Kategorie entsprechen) irgendwie zu nutzen, um die Formel zu vereinfachen.

Wenn zum Beispiel die MSWerte eines Teils P2und N2, und MRPcn= C43(Produktionsplanung) und X-Plant= sind P2, wissen wir, dass es richtig MSist P2. (Der Grund, warum ein Material MSWerte von P2und haben kann N2, die polare Gegensätze sind, liegt darin, dass das Teil während des NPI als Engineering-Planungsteil in SAP initiiert wurde, dann aber später auf der Straße in die Produktion eines Fertigungswerkes überging und nicht in gepflegt wurde die MM.)

Hier sind weitere Beispieldaten, die einige Korrekturen zeigen MS:

Wenn Sie sich die Zeilen ansehen 85:86, hat ein Teil 1301386zwei verschiedene MSWerte von 18und P2bezeichnet Discontinued / Obsolete und Production, ein MRPcnEngineering und ein X-PlantDiscontinued. Daher ist es wahrscheinlich, dass dieser Teil sein MSmuss 18(Obsolete).

Abkürzungsdefinitionen:

MM = Materialstamm
MRPcn = Controller für Materialressourcenplanung
MS = Materialstatus
NPI = Einführung neuer Produkte.
X-Plant MS - Materialstatus auf Kundenebene, der eine Blockierung oder den Status eines Materials in Bezug auf die Supply Chain-Planung identifiziert

1

Ahh ich verstehe. Vielen Dank für die Erklärung Ihrer Verwirrung. Dieses Problem mit den leeren Zeilen ist nicht vorhanden. Wenn dies der Fall ist, wäre es ideal, um unterschiedliches Material zu trennen. Da dies nicht der Fall ist, würden beim Erstellen der Formel und beim Flash-Füllen unterschiedliche MS-Materialien berücksichtigt und daher ein falscher Code ausgegeben. Das heißt, wenn die Anweisungen für Zelle G2 und G3 ein Material analysieren, dann bezieht sich die Ausgabe für G3, falls falsch, auf die Zellreferenzen in G4 (Bezug auf ein anderes Material). Dies geschah in meinen Iterationen der Formel, von denen ich weiß, dass sie falsch sind. Mic Fitzgerald vor 5 Jahren 0

Auf dem Bild, das ich hinzugefügt habe, kann man sehen, dass es verschiedene Fälle gibt. In Zeile 85-86 hat 1301386 einen MS von 18 / P2, der die Einstellung oder Einstellung der Produktion, ein MRPcn als Engineering und eine X-Anlage, die eingestellt wird, bezeichnet. Daher ist es wahrscheinlich, dass dieses Teil als 18 oder in gekennzeichnet sein muss andere Wörter veraltet. Diese Formel hat sich bewährt, um viele davon abzumildern, aber wenn ein Material drei oder vier verschiedene MS aufwies, würde es das folgende Material durcheinander bringen. Verstehen? Mic Fitzgerald vor 5 Jahren 0

Richtig, jede Teilenummer muss sich auf einen einzelnen Materialstatus und nicht auf mehrere beziehen. Es gibt vier verschiedene Instanzen, zu denen ein Teilematerialstatus gehören kann: ZB P2 für die Produktion, N2 für das Engineering, 18/19/4/1 für veraltet und 15 für globale Operationen / Service. Diese können für einen Teil basierend auf der Logik für zwei verschiedene Datenpunkte dargestellt werden: MRPcn und X-Plant MS. @robinCTS Mic Fitzgerald vor 5 Jahren 0

Richtig, es kann immer anhand der oben angegebenen Logik berechnet werden. Es gibt einige Fälle, in denen die korrekte Formel die falsche MS basierend auf den referenzierten Daten ausgibt. Aber das werden geringfügige Ausnahmen sein. Es gibt über 500 MRPcn's und nur 20 x-plant ms, jedoch fallen alle nur in die oben genannten vier Kategorien. Dies ist der Grund, warum ich sie farbbeschichtet habe. Die Frage ist also, ob es eine Möglichkeit gibt, das Material gegen die referenzierten Farben zu stellen. Weil ich weiß, dass es 500 verschiedene Iterationen von if-Anweisungen ist, ist absurd lol @robinCTS. Mic Fitzgerald vor 5 Jahren 0

@robinCTS Ich kann Makros ausführen, aber momentan sind keine aufgezeichnet. Dies ist eine Aktivität, die nur einmal über einen langen Zeitraum von 5-10 Jahren ausgeführt werden muss. Ich kann eine UDF oder ein beliebiges Add-In installieren, das benötigt wird, aber zur Warnung, Sie sprechen jetzt außerhalb meiner Liga. Mic Fitzgerald vor 5 Jahren 0

Wenn ich die gesamte Liste der MRPcns und X-Plants automatisch ausfüllen und ihnen einen numerischen Wert zuweisen kann, der verschiedene Bereiche für die vier MS-Typen festlegt, glauben Sie, dass ein CountIf oder eine andere ähnliche Funktion für meine Aufgaben geeignet ist ? @robinCTS Verdammt, sorry, ich habe so lange gebraucht! Mic Fitzgerald vor 5 Jahren 1

@robinCTS korrekt, in den Bildern scheint es nicht so zu sein, als wären alle X-Pflanzen-Szenarien dargestellt, aber natürlich gibt es 30k + -Zeilen. Natürlich gibt es solche, die speziell sind und sich nicht in den typischen vier Kategorien befinden, die ich habe oben eingestuft. In diesen Szenarien liegt die Antwort dann im mrpcn. Wenn nötig, muss sie jedoch nicht identifiziert werden, denn wenn sich Schlimmeres verschlechtert, gibt es nur eine Handvoll, die ich nach dem Filtern manuell ausführen kann. Vlookups Ich verstehe besser als jede andere Formel. Wie schlagen Sie vor, dass ich eine verschachtelte Anweisung mit dem richtigen Wert aussteige? Mic Fitzgerald vor 5 Jahren 0

1 Antwort auf die Frage

0

Accepted Answer · 2018-06-06 10:10:35

Da bereits eine bedingte Formatierung vorhanden ist, fallen mir drei Möglichkeiten ein, dieses Problem zu lösen:

Schreiben Sie eine UDF, um die bedingte Formatfarbe einer Zelle zu ermitteln, und erstellen Sie dann eine Formel mit mehr als sechzehn verschachtelten IF(AND(),…)Anweisungen oder etwas Äquivalentem.
- Diese Methode hat keine wirklichen Vorteile
- Die Nachteile sind, dass die bedingte Formatierung langsam ist, die UDF nicht einfach ist und die bedingten Formatierungsformeln manuell neu bewertet werden muss und dass die erforderliche Formel lang ist, wobei die "Regeln" eingebettet sind, so dass sie schwer zu sehen / ändern sind
Erstellen Sie neun (zum Teil sehr lange) definierte Namensketten und schreiben Sie eine Formel, die nur 4 verschachtelte IF(…)s erfordert
- Der Vorteil dieser Methode ist, dass weder eine bedingte Formatierung noch zusätzliche Zellen erforderlich sind
- Der Nachteil ist, dass die "Regeln" in definierten Namen versteckt sind und sehr schwer zu verstehen / zu ändern sind
Verwenden Sie drei Tabellen, um die Zuordnungen zu definieren, und verwenden Sie eine Formel, für die nur drei verschachtelte VLOOKUP(…)Funktionen erforderlich sind
- Die Profis sind, dass es keine bedingte Formatierung erfordert, und die Regeln sind kompakt und sehr leicht zu sehen / zu ändern
- Der einzige Nachteil ist, dass drei Tabellen benötigt werden

Ich werde zeigen, wie die dritte Methode implementiert wird.

Dies ist ein Test-Arbeitsblatt, das ein Beispiel der Daten aus den mitgelieferten Screenshots sowie die drei erforderlichen Tabellen mit einigen der ausgefüllten Daten zeigt (einige davon sind zusammengestellt):

Die erste Tabelle enthält eine einfache Zuordnung von MRPcnWerten zu den entsprechenden Kategorien.

Die zweite Tabelle enthält die Zuordnungen von X-PlantWerten zu den entsprechenden Kategorien. Wenn ein Wert, z. B. einer der hypothetischen Zs, nicht zu einer der vier Kategorien gehört, muss der Kategoriewert auf einen eindeutigen Wert gesetzt werden. (Ich habe den X-PlantWert selbst verwendet.)

In der dritten Tabelle wird das "Kreuzprodukt" der beiden Kategorien in den beiden vorherigen Tabellen den entsprechenden MSWerten zugeordnet. Hier werden die "Regeln" definiert. Das Kreuzprodukt ist einfach die Verkettung jedes der eindeutigen Werte der CategorySpalte von Tabelle 1 mit jedem der eindeutigen Werte der CategorySpalte von Tabelle 1.

Beachten Sie, dass die Kreuzprodukte nicht in einer bestimmten Reihenfolge sein müssen. Beachten Sie außerdem, dass für jeden nicht kategorisierbaren Eintrag in Tabelle 2 vier Einträge in Tabelle 3 erstellt werden müssen. Fügen Sie

schließlich, wie im Screenshot zu sehen, die folgende Formel ein G2:

=VLOOKUP(VLOOKUP(D2,$I:$J,2,FALSE)&VLOOKUP(F2,$L:$M,2,FALSE),$O:$P,2,FALSE)

Vereinfachen Sie eine Formel mit vielen IF / AND / OR-Anweisungen, indem Sie möglicherweise bedingte Formatierungen erkennen

1 Antwort auf die Frage

Verwandte Probleme