Betrachten von Verkaufsdaten. Wie kann man feststellen, ob es sich bei einer Bestellung um eine Neubestellung oder eine Nachbestellung für einen bestimmten Kunden und ein bestimmtes Produkt handelt?

Question 1

Vielleicht verstehe ich etwas falsch, aber das scheint ziemlich einfach zu sein. Mein Verständnis der Frage ist, dass eine Zeile eine Neubestellung darstellt, wenn es mindestens eine Zeile über der aktuellen gibt, die denselben Kundennamen (Spalte B) wie die aktuelle Zeile, denselben Produktnamen (Spalte D) und ein Transaktionsdatum (Spalte A) innerhalb der letzten sechs Monate vor dem aktuellen Transaktionsdatum hat. Spalten C, Eund Fkönnen ignoriert werden. Ich gehe davon aus, dass die Zeilen nach Transaktionsdatum sortiert sind (obwohl ich diese Annahme wohl nicht machen muss).

Das Kriterium des Transaktionsdatums ist das „härteste“ (ich verwende diesen Begriff locker). Ein vergangenes Datum liegt innerhalb der letzten sechs Monate, A2wenn es

> EDATE(A2,-6)

Um also die Zeilen bis zur aktuellen zu zählen, die die drei Kriterien erfüllen, verwenden wir

=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)

Die A$2:A2Notation ist interessant. Sie stellt einen Bereich dar, der in Zeile 2 beginnt und in der aktuellen Zeile endet; also alles bis zur aktuellen Zeile (und einschließlich dieser). Diese Zahl wird immer mindestens 1 sein, da die aktuelle Zeile zählt. Wenn sie größer als 1 ist, gab es mindestens eine vorherige Zeile, die ebenfalls übereinstimmte. Die Antwort lautet also:

=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")

hinein H2(oder wo immer Sie es haben möchten) und nach unten ziehen/ausfüllen.

Wenn die Zeilen möglicherweise nicht in der richtigen Reihenfolge sind, müssen wir die gesamte Tabelle durchsuchen und testen, ob das Datum vor dem aktuellen Datum liegt:

=IF(COUNTIFS(A$2:A$99, ">" & EDATE(A2,-6), A$2:A$99, "<" & A2,
                                           B$2:B$99, B2, D$2:D$99, D2)=0, "New Order", "Reorder")

wobei ich verwende, 99um die letzte Datenzeile darzustellen. Ich habe den Test von <=1in geändert =0 , da der < A2Test die aktuelle Zeile eliminiert. Wenn Ihre Daten mehrere Zeilen mit demselben Kundennamen und Produktnamen und genau demselben Transaktionsdatum enthalten könnten, geben Sie bitte an, wie diese behandelt werden sollen.

Answer

Vielleicht verstehe ich etwas falsch, aber das scheint ziemlich einfach zu sein. Mein Verständnis der Frage ist, dass eine Zeile eine Neubestellung darstellt, wenn es mindestens eine Zeile über der aktuellen gibt, die denselben Kundennamen (Spalte B) wie die aktuelle Zeile, denselben Produktnamen (Spalte D) und ein Transaktionsdatum (Spalte A) innerhalb der letzten sechs Monate vor dem aktuellen Transaktionsdatum hat. Spalten C, Eund Fkönnen ignoriert werden. Ich gehe davon aus, dass die Zeilen nach Transaktionsdatum sortiert sind (obwohl ich diese Annahme wohl nicht machen muss).

Das Kriterium des Transaktionsdatums ist das „härteste“ (ich verwende diesen Begriff locker). Ein vergangenes Datum liegt innerhalb der letzten sechs Monate, A2wenn es

> EDATE(A2,-6)

Um also die Zeilen bis zur aktuellen zu zählen, die die drei Kriterien erfüllen, verwenden wir

=COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)

Die A$2:A2Notation ist interessant. Sie stellt einen Bereich dar, der in Zeile 2 beginnt und in der aktuellen Zeile endet; also alles bis zur aktuellen Zeile (und einschließlich dieser). Diese Zahl wird immer mindestens 1 sein, da die aktuelle Zeile zählt. Wenn sie größer als 1 ist, gab es mindestens eine vorherige Zeile, die ebenfalls übereinstimmte. Die Antwort lautet also:

=IF(COUNTIFS(A$2:A2, ">" & EDATE(A2,-6), B$2:B2, B2, D$2:D2, D2)<=1, "New Order", "Reorder")

hinein H2(oder wo immer Sie es haben möchten) und nach unten ziehen/ausfüllen.

Wenn die Zeilen möglicherweise nicht in der richtigen Reihenfolge sind, müssen wir die gesamte Tabelle durchsuchen und testen, ob das Datum vor dem aktuellen Datum liegt:

=IF(COUNTIFS(A$2:A$99, ">" & EDATE(A2,-6), A$2:A$99, "<" & A2,
                                           B$2:B$99, B2, D$2:D$99, D2)=0, "New Order", "Reorder")

wobei ich verwende, 99um die letzte Datenzeile darzustellen. Ich habe den Test von <=1in geändert =0 , da der < A2Test die aktuelle Zeile eliminiert. Wenn Ihre Daten mehrere Zeilen mit demselben Kundennamen und Produktnamen und genau demselben Transaktionsdatum enthalten könnten, geben Sie bitte an, wie diese behandelt werden sollen.

Question 2

Oh, das wird dir gefallen.

Ich werde es in Schritte aufteilen, da die endgültige Formel ziemlich komplex sein wird.

Aufstellen

Ich habe ein Arbeitsblatt im von Ihnen angegebenen Format erstellt. Ich habe drei Spalten hinzugefügt, um herauszufinden, was wir tun müssen. Ich werde dies unten einzeln beschreiben. Beachten Sie, dass ich in diesem ersten Screenshot die oberste Zeile eingefroren und ein wenig nach unten gescrollt habe.

Schritt 1: Suchbereich festlegen

Zuerst müssen wir wissen, wann vor 6 Monaten war (und wo in der Tabelle sich dieses Datum befindet). In jedem Datensatz verwenden wir, EDATEum dies herauszufinden.

In der Zelle G2:

VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)

Das ist großartig – damit erhalten wir den letzten Verkaufsdatensatz, der mehr als 6 Monate zurückliegt. Beachten Sie, dass Sie bei seltenen Verkäufen (d. h. großen Lücken zwischen den Verkaufsdatensätzen) aufgrund meiner Schreibweise möglicherweise falsche Ergebnisse „erwischen“ können, wenn die letzte Bestellung des betreffenden Artikels beispielsweise vor 6 Monaten und einer Woche erfolgte UND zwischen 6 Monaten und einer Woche und vor genau 6 Wochen überhaupt keine Bestellungen vorlagen. Ich habe entschieden, dass dies für Sie „nahe genug“ ist.

Der einzige andere Fehler besteht darin, dass es oben im Blatt einige unschön aussehende Fehler gibt, weil für den ersten Datensatz (also Zeile 2) überhaupt keine Datensätze von vor 6 Monaten vorhanden sind. Also schließen wir mit einem ab IFERROR:

=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)

Mit anderen Worten: Sagen Sie mir, wann die letzte Bestellung vor mehr als sechs Monaten erfolgte, es sei denn, es gibt keine. In diesem Fall nennen Sie mir einfach die erste Bestellung, die uns bekannt ist.

Diese Formel kopiert nach unten. Das ist hier:

Schritt 2: Wandeln Sie die Bereichsstartdaten in einen Zellenstandort um

In der Zelle H2:

=MATCH(G2,A:A,0)

Das ist einfach. An welcher Ordinalstelle in der Spalte Afinden wir das Datum vor 6 Monaten, das wir in Schritt 1 ermittelt haben?

Diese Formel wird nach unten kopiert. Hier sind wir:

Schritt 3: Tun Sie es

Lassen Sie uns einen Sprung nach vorne machen und dies in, wie wäre es mit, einfügen I21, damit wir sehen können, wie es funktioniert.

=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)

Dies ist im Grunde nur eine einfache INDEX MATCHSuche. Aber was wir getan haben, ist, die Zellreferenzen der Arrays dieser Funktionen durch INDIRECTReferenzen auf den Wert zu ersetzen, den wir gerade in die Spalte eingefügt haben H.

In meinen Beispieldaten 21ist die Zeile ein Verkaufsdatensatz mit einem Datum von 10/1/2018. Die Spalte Gsucht nach dem aktuellsten Verkaufsdatensatz, der mehr als 6 Monate vor diesem Datum liegt, was in meinem Beispieldatum zufällig ist 4/1/2018. Die Spalte Hlegt die (erste) Position dieses Datums in der Spalte Ain der 8. Zeile fest. Das INDIRECT("A"&H21&bedeutet, dass das Array für die Suche in der Zeile in beginnen soll, die Adurch den Wert in identifiziert wird H. Gut! Und die andere Hälfte von INDIRECT, ":D"&ROW()-1)bedeutet, dass das Array in der Zeile in enden soll, Ddie eine Zeile über dem Datensatz liegt, in dem wir uns gerade befinden.

Mit anderen Worten: Sie könnten schreiben

=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)

Um das erste Vorkommen Ihres Produktcodes im DGESAMTEN Verkaufsbuch zu finden – aber das wollen wir nicht. Also ersetzen wir die Zellreferenzen durch dynamische Bereiche, die wir in den beiden vorherigen Schritten generiert haben. Mit mir?

Das sieht dann so aus:

Wenn wir diese Formel (die der IFERROROptik halber in eine andere Formel eingebettet ist) bis zur Zeile 2und wieder nach unten kopieren, erhalten wir:

In der ersten Zeile gibt es ein falsches Positiv. Auch damit werde ich leben müssen.

Schritt 4: Kombinieren

Die Formel in Iverweist also auf H, die auf verweist G. Die umgekehrte Erweiterung ergibt eine endgültige Formel, die lautet:

=IFERROR(INDEX(INDIRECT("A"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),MATCH(D2,INDIRECT("D"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),0),1),"NEW ORDER")

Und sieht aus wie:

Wenn Sie möchten, können Sie diese Formel einfach in die Spalte „Neu anordnen“ in der Zeile kopieren 2und nach unten kopieren.

Achtung.

Sie werden die Funktion „Format übertragen“ auf Ihr Ausgabefeld anwenden, da die Formeln in der vorliegenden Form die Textdaten Aals Datumsseriennummern behandeln und ebenfalls eine Seriennummer zurückgeben.
Beachten Sie die Schwäche in Schritt 1 - Sie können im Screenshot des helper1Felds in Schritt 3 sehen, dass in meinen Beispieldaten das Datum vor 6 Monaten für den 1.10. der 1.4. war und das Datum vor 6 Monaten für den 5.10.Auch4/1, da zwischen dem 1.4. und 5.4. überhaupt keine Bestellungen eingegangen sind. Dies könnte möglicherweise zu Fehlalarmen führen.
Wie in Schritt 3 besprochen, kommt es bei dieser Methode auch beim ersten Verkaufsdatensatz zu einem falschen Positivergebnis.

Answer

Oh, das wird dir gefallen.

Ich werde es in Schritte aufteilen, da die endgültige Formel ziemlich komplex sein wird.

Aufstellen

Ich habe ein Arbeitsblatt im von Ihnen angegebenen Format erstellt. Ich habe drei Spalten hinzugefügt, um herauszufinden, was wir tun müssen. Ich werde dies unten einzeln beschreiben. Beachten Sie, dass ich in diesem ersten Screenshot die oberste Zeile eingefroren und ein wenig nach unten gescrollt habe.

Schritt 1: Suchbereich festlegen

Zuerst müssen wir wissen, wann vor 6 Monaten war (und wo in der Tabelle sich dieses Datum befindet). In jedem Datensatz verwenden wir, EDATEum dies herauszufinden.

In der Zelle G2:

VLOOKUP(EDATE(A2,-6),A:A,1,TRUE)

Das ist großartig – damit erhalten wir den letzten Verkaufsdatensatz, der mehr als 6 Monate zurückliegt. Beachten Sie, dass Sie bei seltenen Verkäufen (d. h. großen Lücken zwischen den Verkaufsdatensätzen) aufgrund meiner Schreibweise möglicherweise falsche Ergebnisse „erwischen“ können, wenn die letzte Bestellung des betreffenden Artikels beispielsweise vor 6 Monaten und einer Woche erfolgte UND zwischen 6 Monaten und einer Woche und vor genau 6 Wochen überhaupt keine Bestellungen vorlagen. Ich habe entschieden, dass dies für Sie „nahe genug“ ist.

Der einzige andere Fehler besteht darin, dass es oben im Blatt einige unschön aussehende Fehler gibt, weil für den ersten Datensatz (also Zeile 2) überhaupt keine Datensätze von vor 6 Monaten vorhanden sind. Also schließen wir mit einem ab IFERROR:

=IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2)

Mit anderen Worten: Sagen Sie mir, wann die letzte Bestellung vor mehr als sechs Monaten erfolgte, es sei denn, es gibt keine. In diesem Fall nennen Sie mir einfach die erste Bestellung, die uns bekannt ist.

Diese Formel kopiert nach unten. Das ist hier:

Schritt 2: Wandeln Sie die Bereichsstartdaten in einen Zellenstandort um

In der Zelle H2:

=MATCH(G2,A:A,0)

Das ist einfach. An welcher Ordinalstelle in der Spalte Afinden wir das Datum vor 6 Monaten, das wir in Schritt 1 ermittelt haben?

Diese Formel wird nach unten kopiert. Hier sind wir:

Schritt 3: Tun Sie es

Lassen Sie uns einen Sprung nach vorne machen und dies in, wie wäre es mit, einfügen I21, damit wir sehen können, wie es funktioniert.

=INDEX(INDIRECT("A"&H21&":D"&ROW()-1),MATCH(D21,INDIRECT("D"&H21&":D"&ROW()-1),0),1)

Dies ist im Grunde nur eine einfache INDEX MATCHSuche. Aber was wir getan haben, ist, die Zellreferenzen der Arrays dieser Funktionen durch INDIRECTReferenzen auf den Wert zu ersetzen, den wir gerade in die Spalte eingefügt haben H.

In meinen Beispieldaten 21ist die Zeile ein Verkaufsdatensatz mit einem Datum von 10/1/2018. Die Spalte Gsucht nach dem aktuellsten Verkaufsdatensatz, der mehr als 6 Monate vor diesem Datum liegt, was in meinem Beispieldatum zufällig ist 4/1/2018. Die Spalte Hlegt die (erste) Position dieses Datums in der Spalte Ain der 8. Zeile fest. Das INDIRECT("A"&H21&bedeutet, dass das Array für die Suche in der Zeile in beginnen soll, die Adurch den Wert in identifiziert wird H. Gut! Und die andere Hälfte von INDIRECT, ":D"&ROW()-1)bedeutet, dass das Array in der Zeile in enden soll, Ddie eine Zeile über dem Datensatz liegt, in dem wir uns gerade befinden.

Mit anderen Worten: Sie könnten schreiben

=INDEX(A1:D999,MATCH(D21,D1:D9999,0),1)

Um das erste Vorkommen Ihres Produktcodes im DGESAMTEN Verkaufsbuch zu finden – aber das wollen wir nicht. Also ersetzen wir die Zellreferenzen durch dynamische Bereiche, die wir in den beiden vorherigen Schritten generiert haben. Mit mir?

Das sieht dann so aus:

Wenn wir diese Formel (die der IFERROROptik halber in eine andere Formel eingebettet ist) bis zur Zeile 2und wieder nach unten kopieren, erhalten wir:

In der ersten Zeile gibt es ein falsches Positiv. Auch damit werde ich leben müssen.

Schritt 4: Kombinieren

Die Formel in Iverweist also auf H, die auf verweist G. Die umgekehrte Erweiterung ergibt eine endgültige Formel, die lautet:

=IFERROR(INDEX(INDIRECT("A"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),MATCH(D2,INDIRECT("D"&MATCH(IFERROR(VLOOKUP(EDATE(A2,-6),A:A,1,TRUE),$A$2),A:A,0)&":D"&ROW()-1),0),1),"NEW ORDER")

Und sieht aus wie:

Wenn Sie möchten, können Sie diese Formel einfach in die Spalte „Neu anordnen“ in der Zeile kopieren 2und nach unten kopieren.

Achtung.

Sie werden die Funktion „Format übertragen“ auf Ihr Ausgabefeld anwenden, da die Formeln in der vorliegenden Form die Textdaten Aals Datumsseriennummern behandeln und ebenfalls eine Seriennummer zurückgeben.
Beachten Sie die Schwäche in Schritt 1 - Sie können im Screenshot des helper1Felds in Schritt 3 sehen, dass in meinen Beispieldaten das Datum vor 6 Monaten für den 1.10. der 1.4. war und das Datum vor 6 Monaten für den 5.10.Auch4/1, da zwischen dem 1.4. und 5.4. überhaupt keine Bestellungen eingegangen sind. Dies könnte möglicherweise zu Fehlalarmen führen.
Wie in Schritt 3 besprochen, kommt es bei dieser Methode auch beim ersten Verkaufsdatensatz zu einem falschen Positivergebnis.

Question 3

Mein Ansatz zur Lösung des Problems ist etwas anders, da ich den Thread des OP ausgewählt habe.

Unter einer Neubestellung versteht man alles, was der Kunde in den letzten 6 Monaten oder noch nie bestellt hat.
Eine Nachbestellung läge vor, wenn der Kunde das entsprechende Produkt in den letzten 6 Monaten gekauft hätte.

Eine Array (CSE)-Formel in Zelle H41, beenden mitStrg+Umschalt+Eingabe.

{=IFERROR(LOOKUP(DATEDIF(IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match"),I41,"m"),{0,6,12},{"New Order","Order before 6 month","Order before 12 months"}),"Cust's. New Pro. Order")}

Situation 1:

Neues Traktionsdatum: 03/26/19.

Kundenname: Bob.

Produktname: Cake.

Sataus bestellen: Order before 12 months.

Situation 2:

Neues Traktionsdatum: 03/26/19.

Kundenname: Bob.

Produktname: Milk.

Sataus bestellen: New Order.

Achtung

Weil der Unterschied zwischen dem alten Transaktionsdatum ( 10/01/18) und dem neuen Transaktionsdatum ( 03/26/19) weniger als 6 Monate beträgt.

Situation 3:

Neues Traktionsdatum: 03/26/19.

Kundenname: Bob.

Produktname: Wheat.

Sataus bestellen: Order before 6 months.

Situation 4:

Neues Traktionsdatum: 03/26/19.

Kundenname: Bob.

Produktname: Fruit.

Sataus bestellen: Cust's. New Pro.Order.

Notiz:

Wenn Sie den Namen des neuen Kunden und entweder das alte oder das neue Produkt und Datum eingeben, erhalten Sie Cust's . New Pro. Orderden Status.

Lassen Sie mich nun erklären, wie die Formel funktioniert.

Die Formel kann in zwei Teile aufgeteilt werden.

Part 1

 {=IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match")}

Im Grunde handelt es sich um eine Suche mit zwei Kriterien, die Old Transaction Datenach sucht Customer & the Product, und die Formel betrachtet es, als Start Dateliege DATEDIFdie Formel innerhalb von A41:A47…

Part 2

Das Original DATEDIFist,

{=LOOKUP(DATEDIF(A41:A47,I41,"m"),{0,6,12},{"New order","Order before 6 month","Order before 12 months"})}

Wobei durch die Formel A41:A47ersetzt wurde und sich in Zelle befindet .Part 1Start DateEnd DateI41

Und beide Teile sind hübsch verpackt mit IFERROR.

Achtung

Sie können die Zellbezüge nach Bedarf anpassen.
Nachrichten mit der Formel können auch nach Wunsch geändert werden.

Answer

Mein Ansatz zur Lösung des Problems ist etwas anders, da ich den Thread des OP ausgewählt habe.

Unter einer Neubestellung versteht man alles, was der Kunde in den letzten 6 Monaten oder noch nie bestellt hat.
Eine Nachbestellung läge vor, wenn der Kunde das entsprechende Produkt in den letzten 6 Monaten gekauft hätte.

Eine Array (CSE)-Formel in Zelle H41, beenden mitStrg+Umschalt+Eingabe.

{=IFERROR(LOOKUP(DATEDIF(IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match"),I41,"m"),{0,6,12},{"New Order","Order before 6 month","Order before 12 months"}),"Cust's. New Pro. Order")}

Situation 1:

Neues Traktionsdatum: 03/26/19.

Kundenname: Bob.

Produktname: Cake.

Sataus bestellen: Order before 12 months.

Situation 2:

Neues Traktionsdatum: 03/26/19.

Kundenname: Bob.

Produktname: Milk.

Sataus bestellen: New Order.

Achtung

Weil der Unterschied zwischen dem alten Transaktionsdatum ( 10/01/18) und dem neuen Transaktionsdatum ( 03/26/19) weniger als 6 Monate beträgt.

Situation 3:

Neues Traktionsdatum: 03/26/19.

Kundenname: Bob.

Produktname: Wheat.

Sataus bestellen: Order before 6 months.

Situation 4:

Neues Traktionsdatum: 03/26/19.

Kundenname: Bob.

Produktname: Fruit.

Sataus bestellen: Cust's. New Pro.Order.

Notiz:

Wenn Sie den Namen des neuen Kunden und entweder das alte oder das neue Produkt und Datum eingeben, erhalten Sie Cust's . New Pro. Orderden Status.

Lassen Sie mich nun erklären, wie die Formel funktioniert.

Die Formel kann in zwei Teile aufgeteilt werden.

Part 1

 {=IFERROR(INDEX($A$41:$A$47,MATCH(1,($B$41:$B$47=J41)*($D$41:$D$47=K41),0)),"No Match")}

Im Grunde handelt es sich um eine Suche mit zwei Kriterien, die Old Transaction Datenach sucht Customer & the Product, und die Formel betrachtet es, als Start Dateliege DATEDIFdie Formel innerhalb von A41:A47…

Part 2

Das Original DATEDIFist,

{=LOOKUP(DATEDIF(A41:A47,I41,"m"),{0,6,12},{"New order","Order before 6 month","Order before 12 months"})}

Wobei durch die Formel A41:A47ersetzt wurde und sich in Zelle befindet .Part 1Start DateEnd DateI41

Und beide Teile sind hübsch verpackt mit IFERROR.

Achtung

Sie können die Zellbezüge nach Bedarf anpassen.
Nachrichten mit der Formel können auch nach Wunsch geändert werden.

Question 4

Sie können die folgende Formel verwenden:

=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )

Dabei wird die altbewährte Technik einfacher „Bereichstests“ verwendet, um Arrays zu erstellen, die Ihnen alles mitteilen (Befindet sich der Name des Kunden in einer beliebigen Zelle im Bereich der Spalte A? Befindet sich der Produktname im Bereich der Spalte D? Liegen die Daten im Bereich der Spalte A innerhalb von 183 Tagen nach der heutigen Bestellung?), und diese werden dann multipliziert, um ein endgültiges Array zu erhalten.

Die Rohwerte des Arrays sind TRUE/FALSE-Werte, aber wenn sie multipliziert werden, wird Excel gezwungen, sie in 1/0-Werte umzuwandeln, die sich gut multiplizieren lassen. Das Endergebnis ist ein Array mit 1en, wenn alle drei der oben genannten Bedingungen erfüllt sind, und 0en, wenn dies nicht der Fall ist. Excel konvertiert die Elemente des Arrays nicht zurück in TRUE/FALSE-Werte, sodass das gesamte Array numerisch ist.

SUMdann werden sie alle zu einem einzigen Wert zusammengefasst. Wenn das Ergebnis irgendetwas anderes als 0 ist, dann gab es in den letzten 183 Tagen mindestens eine gleiche Bestellung. Wenn 0, dann gab es keine solche. Dann IFwird einfach geprüft, welches Ergebnis es ist und Ihnen wird „Neu“ oder „Neu bestellen“ angezeigt.

Der wirklich einzig interessante Punkt ist die Mischung aus Absolutheit und Relativität bei der Bereichsadressierung. Alle Bereichsstartpunkte sind vollständig absolut, sodass die obere linke Ecke jedes Bereichs verankert und unbeweglich ist. Die Bereichsendpunkte machen nur die Spalte der Referenz absolut, sodass der Bereich wächst, wenn Sie Zeilen hinzufügen, aber niemals die aktuelle Zeile einschließt ... beginnt also oben und endet eine Zeile über der aktuellen Zeile.

Wenn dies nicht von Anfang an klar ist, gehe ich davon aus, dass die Informationen in der Zelle Spalte G der gerade eingegebenen Bestellung erscheinen. Sie könnten jedoch auch woanders stehen, nicht einmal in derselben Zeile oder demselben Blatt, und könnten für einen Test der bedingten Formatierung verwendet werden, um die Farben der Zeilenzellen zu ändern und auf diese Weise „Neu/Neu bestellen“ anzuzeigen.

Answer

Sie können die folgende Formel verwenden:

=IF( SUM( ($B$2:$B9=A10) * ($D$2:$D9=D10) * ($A$2:$A9>($A10-183)) ) = 0, "New", "Reorder" )

Dabei wird die altbewährte Technik einfacher „Bereichstests“ verwendet, um Arrays zu erstellen, die Ihnen alles mitteilen (Befindet sich der Name des Kunden in einer beliebigen Zelle im Bereich der Spalte A? Befindet sich der Produktname im Bereich der Spalte D? Liegen die Daten im Bereich der Spalte A innerhalb von 183 Tagen nach der heutigen Bestellung?), und diese werden dann multipliziert, um ein endgültiges Array zu erhalten.

Die Rohwerte des Arrays sind TRUE/FALSE-Werte, aber wenn sie multipliziert werden, wird Excel gezwungen, sie in 1/0-Werte umzuwandeln, die sich gut multiplizieren lassen. Das Endergebnis ist ein Array mit 1en, wenn alle drei der oben genannten Bedingungen erfüllt sind, und 0en, wenn dies nicht der Fall ist. Excel konvertiert die Elemente des Arrays nicht zurück in TRUE/FALSE-Werte, sodass das gesamte Array numerisch ist.

SUMdann werden sie alle zu einem einzigen Wert zusammengefasst. Wenn das Ergebnis irgendetwas anderes als 0 ist, dann gab es in den letzten 183 Tagen mindestens eine gleiche Bestellung. Wenn 0, dann gab es keine solche. Dann IFwird einfach geprüft, welches Ergebnis es ist und Ihnen wird „Neu“ oder „Neu bestellen“ angezeigt.

Der wirklich einzig interessante Punkt ist die Mischung aus Absolutheit und Relativität bei der Bereichsadressierung. Alle Bereichsstartpunkte sind vollständig absolut, sodass die obere linke Ecke jedes Bereichs verankert und unbeweglich ist. Die Bereichsendpunkte machen nur die Spalte der Referenz absolut, sodass der Bereich wächst, wenn Sie Zeilen hinzufügen, aber niemals die aktuelle Zeile einschließt ... beginnt also oben und endet eine Zeile über der aktuellen Zeile.

Wenn dies nicht von Anfang an klar ist, gehe ich davon aus, dass die Informationen in der Zelle Spalte G der gerade eingegebenen Bestellung erscheinen. Sie könnten jedoch auch woanders stehen, nicht einmal in derselben Zeile oder demselben Blatt, und könnten für einen Test der bedingten Formatierung verwendet werden, um die Farben der Zeilenzellen zu ändern und auf diese Weise „Neu/Neu bestellen“ anzuzeigen.

Betrachten von Verkaufsdaten. Wie kann man feststellen, ob es sich bei einer Bestellung um eine Neubestellung oder eine Nachbestellung für einen bestimmten Kunden und ein bestimmtes Produkt handelt?

Antwort1

Antwort2

Aufstellen

Schritt 1: Suchbereich festlegen

Schritt 2: Wandeln Sie die Bereichsstartdaten in einen Zellenstandort um

Schritt 3: Tun Sie es

Schritt 4: Kombinieren

Achtung.

Antwort3

Antwort4

verwandte Informationen