販売データの確認。特定の顧客および製品に対する注文が新規注文か再注文かを判断するにはどうすればよいでしょうか。

Question 1

おそらく私は何かを誤解しているのでしょうが、これはかなり単純なようです。質問に対する私の理解は、現在の行の上に、現在の行と同じ顧客名 (列 B)、同じ商品名 (列 ) 、および現在の取引日から過去 6 か月以内の D取引日 (列 ) を持つ行が少なくとも 1 つある場合、行は再注文を表すということです。列、およびは無視できます。行は取引日で並べ替えられていると想定しています (ただし、その想定をする必要はないと思います)。ACEF

取引日の基準は「最も厳しい」（私はこの言葉を大まかに使っている）です。過去の日付とは、取引日から6ヶ月以内の日付を指します A2。

> EDATE(A2,-6)

したがって、現在の行から3つの条件を満たす行を数えるには、

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

このA$2:A2表記は興味深いものです。これは、行 2 から始まり、現在の行で終わる範囲を表します。つまり、現在の行まで (現在の行を含む) のすべてです。現在の行がカウントされるため、このカウントは常に少なくとも 1 になります。1 より大きい場合は、一致する前の行が少なくとも 1 つあります。したがって、答えは次のように入力します。

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

H2（または任意の場所）にドラッグ/塗りつぶしします。

行の順序が間違っている可能性がある場合は、テーブル全体を検索し、日付が現在の日付より小さいことをテストする必要があります。

=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")

99ここで、データの最後の行を表すためにを使用しています。テストでは現在の行が削除されるため、テストを<=1からに変更しました。データに同じ顧客名と製品名、およびまったく同じ取引日を持つ複数の行が含まれる可能性がある場合は、それらをどのように処理するかを指定してください。=0< A2

Answer

おそらく私は何かを誤解しているのでしょうが、これはかなり単純なようです。質問に対する私の理解は、現在の行の上に、現在の行と同じ顧客名 (列 B)、同じ商品名 (列 ) 、および現在の取引日から過去 6 か月以内の D取引日 (列 ) を持つ行が少なくとも 1 つある場合、行は再注文を表すということです。列、およびは無視できます。行は取引日で並べ替えられていると想定しています (ただし、その想定をする必要はないと思います)。ACEF

取引日の基準は「最も厳しい」（私はこの言葉を大まかに使っている）です。過去の日付とは、取引日から6ヶ月以内の日付を指します A2。

> EDATE(A2,-6)

したがって、現在の行から3つの条件を満たす行を数えるには、

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

このA$2:A2表記は興味深いものです。これは、行 2 から始まり、現在の行で終わる範囲を表します。つまり、現在の行まで (現在の行を含む) のすべてです。現在の行がカウントされるため、このカウントは常に少なくとも 1 になります。1 より大きい場合は、一致する前の行が少なくとも 1 つあります。したがって、答えは次のように入力します。

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

H2（または任意の場所）にドラッグ/塗りつぶしします。

行の順序が間違っている可能性がある場合は、テーブル全体を検索し、日付が現在の日付より小さいことをテストする必要があります。

=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")

99ここで、データの最後の行を表すためにを使用しています。テストでは現在の行が削除されるため、テストを<=1からに変更しました。データに同じ顧客名と製品名、およびまったく同じ取引日を持つ複数の行が含まれる可能性がある場合は、それらをどのように処理するかを指定してください。=0< A2

Question 2

ああ、これはきっと気に入るよ。

最終的な式はかなり複雑になるため、ステップごとに分解します。

設定

指定された形式でワークシートを作成しました。必要な作業を整理するために 3 つの列を追加しました。各列については、以下で個別に説明します。この最初のスクリーンショットでは、一番上の行を固定し、少し下にスクロールしていることに注意してください。

ステップ1: 検索範囲を設定する

まず、6 か月前がいつだったか (および、その日付がスプレッドシートのどこにあるのか) を知る必要があります。各レコードで、を使用してEDATE調べます。

セル内G2:

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

これは素晴らしいです。これにより、6 か月以上前の最新の販売記録がわかります。販売頻度が低い場合 (つまり、販売記録間のギャップが大きい場合)、私がこの方法で記述すると、問題のアイテムの最後の注文が、たとえば 6 か月 1 週間前であり、6 か月 1 週間前とちょうど 6 週間前の間にまったく注文がなかった場合、誤検知が発生する可能性があります。これは「十分に近い」と判断しました。

唯一の欠点は、シートの上部近くに見栄えの悪いエラーがいくつかあることです。これは、最初のレコード (つまり、行2) に 6 か月前のレコードがまったく存在しないためです。そこで、でラップしましょうIFERROR。

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

つまり、6 か月以上前の最新の注文がいつだったかを教えてください。最新の注文がない場合は、記録されている最初の注文を教えてください。

この数式は下にコピーされます。ここです:

ステップ2: 範囲開始データをセル位置に変換する

セル内H2:

=MATCH(G2,A:A,0)

Aこれは簡単です。ステップ 1 で設定した 6 か月前の日付は、列のどの位置にありますか?

この数式は下にコピーされます。次のようになります。

ステップ3: 実行する

先に進んで、これをに入れて、どのようにI21動作するかを見てみましょう。

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

これは基本的に単純なINDEX MATCH検索です。ただし、ここで行ったのは、これらの関数の配列のセル参照を、INDIRECT列にシードした値への参照に置き換えることですH。

私のサンプルデータでは、行は21日付がである売上記録です10/1/2018。列はG、その日付より 6 か月以上前の最新の売上記録を検索します。私のサンプルでは、日付はです4/1/2018。列は、H列のその日付の (最初の) 場所をA8 行目に固定します。つまり、の値で識別されるINDIRECT("A"&H21&の行から検索の配列を開始するということです。いいですね! のもう半分は、現在いるレコードの 1 行上にあるの行で配列を終了することを示しています。AHINDIRECT":D"&ROW()-1)D

言い換えれば、次のように書くことができます

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

売上台帳全体から製品コードの最初の出現箇所を見つけるのですDが、これはやりたくありません。そこで、セル参照を、前の 2 つの手順で生成した動的範囲に置き換えます。それでいいですか?

つまり次のようになります:

IFERRORその数式（見た目のために別の数式で囲まれています）を上の行にコピーし2、下の行に戻すと、次のようになります。

最初の行に誤検知があります。これも受け入れるつもりです。

ステップ4: 組み合わせる

したがって、の式はをI参照しH、はを参照しますG。逆展開すると、最終的な式は次のようになります。

=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")

そして次のようになります:

したがって、必要に応じて、その数式を行の「並べ替え」列にコピーして2、下にコピーすることができます。

注意。

出力フィールドでは書式のコピー/貼り付けを使用します。これは、記述された数式がテキストの日付をA日付のシリアル番号として扱い、シリアル番号も返すためです。
ステップ1の弱点に注意してください。ステップ3のフィールドのスクリーンショットを見ると、helper1私のサンプルデータでは、6か月前の10/1の日付は4/1で、6か月前の10/5の日付はまた4/1 は、4/1 から 4/5 の間に注文がまったくなかったためです。これにより、誤検知が発生する可能性があります。
繰り返しになりますが、ステップ 3 で説明したように、この方法を使用すると、最初の販売レコードで誤検知が発生することもあります。

Answer

ああ、これはきっと気に入るよ。

最終的な式はかなり複雑になるため、ステップごとに分解します。

設定

指定された形式でワークシートを作成しました。必要な作業を整理するために 3 つの列を追加しました。各列については、以下で個別に説明します。この最初のスクリーンショットでは、一番上の行を固定し、少し下にスクロールしていることに注意してください。

ステップ1: 検索範囲を設定する

まず、6 か月前がいつだったか (および、その日付がスプレッドシートのどこにあるのか) を知る必要があります。各レコードで、を使用してEDATE調べます。

セル内G2:

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

これは素晴らしいです。これにより、6 か月以上前の最新の販売記録がわかります。販売頻度が低い場合 (つまり、販売記録間のギャップが大きい場合)、私がこの方法で記述すると、問題のアイテムの最後の注文が、たとえば 6 か月 1 週間前であり、6 か月 1 週間前とちょうど 6 週間前の間にまったく注文がなかった場合、誤検知が発生する可能性があります。これは「十分に近い」と判断しました。

唯一の欠点は、シートの上部近くに見栄えの悪いエラーがいくつかあることです。これは、最初のレコード (つまり、行2) に 6 か月前のレコードがまったく存在しないためです。そこで、でラップしましょうIFERROR。

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

つまり、6 か月以上前の最新の注文がいつだったかを教えてください。最新の注文がない場合は、記録されている最初の注文を教えてください。

この数式は下にコピーされます。ここです:

ステップ2: 範囲開始データをセル位置に変換する

セル内H2:

=MATCH(G2,A:A,0)

Aこれは簡単です。ステップ 1 で設定した 6 か月前の日付は、列のどの位置にありますか?

この数式は下にコピーされます。次のようになります。

ステップ3: 実行する

先に進んで、これをに入れて、どのようにI21動作するかを見てみましょう。

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

これは基本的に単純なINDEX MATCH検索です。ただし、ここで行ったのは、これらの関数の配列のセル参照を、INDIRECT列にシードした値への参照に置き換えることですH。

私のサンプルデータでは、行は21日付がである売上記録です10/1/2018。列はG、その日付より 6 か月以上前の最新の売上記録を検索します。私のサンプルでは、日付はです4/1/2018。列は、H列のその日付の (最初の) 場所をA8 行目に固定します。つまり、の値で識別されるINDIRECT("A"&H21&の行から検索の配列を開始するということです。いいですね! のもう半分は、現在いるレコードの 1 行上にあるの行で配列を終了することを示しています。AHINDIRECT":D"&ROW()-1)D

言い換えれば、次のように書くことができます

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

売上台帳全体から製品コードの最初の出現箇所を見つけるのですDが、これはやりたくありません。そこで、セル参照を、前の 2 つの手順で生成した動的範囲に置き換えます。それでいいですか?

つまり次のようになります:

IFERRORその数式（見た目のために別の数式で囲まれています）を上の行にコピーし2、下の行に戻すと、次のようになります。

最初の行に誤検知があります。これも受け入れるつもりです。

ステップ4: 組み合わせる

したがって、の式はをI参照しH、はを参照しますG。逆展開すると、最終的な式は次のようになります。

=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")

そして次のようになります:

したがって、必要に応じて、その数式を行の「並べ替え」列にコピーして2、下にコピーすることができます。

注意。

出力フィールドでは書式のコピー/貼り付けを使用します。これは、記述された数式がテキストの日付をA日付のシリアル番号として扱い、シリアル番号も返すためです。
ステップ1の弱点に注意してください。ステップ3のフィールドのスクリーンショットを見ると、helper1私のサンプルデータでは、6か月前の10/1の日付は4/1で、6か月前の10/5の日付はまた4/1 は、4/1 から 4/5 の間に注文がまったくなかったためです。これにより、誤検知が発生する可能性があります。
繰り返しになりますが、ステップ 3 で説明したように、この方法を使用すると、最初の販売レコードで誤検知が発生することもあります。

Question 3

私はOPのスレッドを選んだので、問題を解決するための私のアプローチは少し異なります、、

新規注文とは、顧客が過去 6 か月間またはこれまでに注文したことのない注文のことです。
再注文は、顧客が過去 6 か月以内に特定の製品を購入した場合に発生します。

セル内の配列（CSE）数式H41、終了Ctrl+Shift+Enter。

{=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")}

状況1:

新しい牽引日: 03/26/19.

顧客名：Bob。

製品名: Cake。

注文状況: Order before 12 months.

状況2:

新しい牽引日: 03/26/19.

顧客名：Bob。

製品名: Milk。

注文状況: New Order.

注意

旧取引日（10/01/18）と新取引日（03/26/19）の差が6か月未満であるためです。

状況3:

新しい牽引日: 03/26/19.

顧客名：Bob。

製品名: Wheat。

注文状況: Order before 6 months.

状況4:

新しい牽引日: 03/26/19.

顧客名：Bob。

製品名: Fruit。

注文状況: Cust's. New Pro.Order.

注記：

新しい顧客の名前と、古い製品または新しい製品と日付を入力すると、Cust's . New Pro. Orderステータスが表示されます。

それでは、この数式がどのように機能するかを説明します。

式は 2 つの部分に分けられます。

Part 1

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

Old Transaction Date基本的に、を検索する 2 つの基準ルックアップでありCustomer & the Product、数式Start DateではDATEDIF、がの範囲内にあるとみなされますA41:A47。

Part 2

原文DATEDIFは、

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

ここで、A41:A47は数式に置き換えられ、はセル内にあります。Part 1Start DateEnd DateI41

そして、両方のパーツはできれいに包まれていますIFERROR。

注意

必要に応じてセル参照を調整できます。
数式を含むメッセージも、選択に応じて変更できます。

Answer

私はOPのスレッドを選んだので、問題を解決するための私のアプローチは少し異なります、、

新規注文とは、顧客が過去 6 か月間またはこれまでに注文したことのない注文のことです。
再注文は、顧客が過去 6 か月以内に特定の製品を購入した場合に発生します。

セル内の配列（CSE）数式H41、終了Ctrl+Shift+Enter。

{=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")}

状況1:

新しい牽引日: 03/26/19.

顧客名：Bob。

製品名: Cake。

注文状況: Order before 12 months.

状況2:

新しい牽引日: 03/26/19.

顧客名：Bob。

製品名: Milk。

注文状況: New Order.

注意

旧取引日（10/01/18）と新取引日（03/26/19）の差が6か月未満であるためです。

状況3:

新しい牽引日: 03/26/19.

顧客名：Bob。

製品名: Wheat。

注文状況: Order before 6 months.

状況4:

新しい牽引日: 03/26/19.

顧客名：Bob。

製品名: Fruit。

注文状況: Cust's. New Pro.Order.

注記：

新しい顧客の名前と、古い製品または新しい製品と日付を入力すると、Cust's . New Pro. Orderステータスが表示されます。

それでは、この数式がどのように機能するかを説明します。

式は 2 つの部分に分けられます。

Part 1

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

Old Transaction Date基本的に、を検索する 2 つの基準ルックアップでありCustomer & the Product、数式Start DateではDATEDIF、がの範囲内にあるとみなされますA41:A47。

Part 2

原文DATEDIFは、

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

ここで、A41:A47は数式に置き換えられ、はセル内にあります。Part 1Start DateEnd DateI41

そして、両方のパーツはできれいに包まれていますIFERROR。

注意

必要に応じてセル参照を調整できます。
数式を含むメッセージも、選択に応じて変更できます。

Question 4

次の式を使用できます。

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

これは、単純な「範囲=」テストという昔ながらの手法を使用して、各情報 (顧客の名前は列 A の範囲内のセルにあるか? 商品名は列 D の範囲内にあるか? 列 A の範囲内の日付は今日の注文から 183 日以内か?) を示す配列を生成し、それらを乗算して最終的な配列を取得します。

生の配列値は TRUE/FALSE 値ですが、それらを掛け合わせると、Excel はそれらを 1/0 値に変更し、うまく掛け合わせます。最終結果は、上記の 3 つの条件がすべて満たされる場合は 1 になり、満たされない場合は 0 になる配列になります。Excel は配列の要素を TREU/FALSE 値に戻さないため、配列全体が数値になります。

SUM次に、それらをすべて合計して 1 つの値にします。結果が 0 以外の場合、過去 183 日間に少なくとも 1 つの同じ注文が存在します。0 の場合、そのような注文は存在しません。単にIF結果がどれであるかをチェックし、「新規」または「再注文」を通知します。

本当に興味深いのは、範囲のアドレス指定における絶対性と相対性の混合です。範囲の開始点はすべて完全に絶対的であるため、各範囲の左上隅は固定され、動きません。範囲の終了点は参照の列のみを絶対的にするため、行を追加すると範囲は拡大しますが、現在の行は含まれません。つまり、上から始まり、現在の行の 1 行上で終了します。

最初から明確でない場合は、入力したばかりの注文の行の列 G セルに情報が表示されると仮定します。ただし、同じ行や同じシートではない他の場所にある可能性があり、そのように行のセルの色を変更して「新規/並べ替え」を表示する条件付き書式設定テストを作成する際に使用できます。

Answer

次の式を使用できます。

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

これは、単純な「範囲=」テストという昔ながらの手法を使用して、各情報 (顧客の名前は列 A の範囲内のセルにあるか? 商品名は列 D の範囲内にあるか? 列 A の範囲内の日付は今日の注文から 183 日以内か?) を示す配列を生成し、それらを乗算して最終的な配列を取得します。

生の配列値は TRUE/FALSE 値ですが、それらを掛け合わせると、Excel はそれらを 1/0 値に変更し、うまく掛け合わせます。最終結果は、上記の 3 つの条件がすべて満たされる場合は 1 になり、満たされない場合は 0 になる配列になります。Excel は配列の要素を TREU/FALSE 値に戻さないため、配列全体が数値になります。

SUM次に、それらをすべて合計して 1 つの値にします。結果が 0 以外の場合、過去 183 日間に少なくとも 1 つの同じ注文が存在します。0 の場合、そのような注文は存在しません。単にIF結果がどれであるかをチェックし、「新規」または「再注文」を通知します。

本当に興味深いのは、範囲のアドレス指定における絶対性と相対性の混合です。範囲の開始点はすべて完全に絶対的であるため、各範囲の左上隅は固定され、動きません。範囲の終了点は参照の列のみを絶対的にするため、行を追加すると範囲は拡大しますが、現在の行は含まれません。つまり、上から始まり、現在の行の 1 行上で終了します。

最初から明確でない場合は、入力したばかりの注文の行の列 G セルに情報が表示されると仮定します。ただし、同じ行や同じシートではない他の場所にある可能性があり、そのように行のセルの色を変更して「新規/並べ替え」を表示する条件付き書式設定テストを作成する際に使用できます。

販売データの確認。特定の顧客および製品に対する注文が新規注文か再注文かを判断するにはどうすればよいでしょうか。

答え1

答え2

設定

ステップ1: 検索範囲を設定する

ステップ2: 範囲開始データをセル位置に変換する

ステップ3: 実行する

ステップ4: 組み合わせる

注意。

答え3

答え4

関連情報