過去のイベントの日付が 2 ～ 6 個あり、各イベント間の平均分散に基づいて、次のイベントがいつ発生するかを予測する必要があります。
C4-D4スクリーンショットから、基本的には ( )、( D4-E4)、( E4-F4)の平均を取り、( F4-G4) は空白なのでスキップします。次に、平均日数を最新の値 ( ) に追加して、予測される次の発生C4( ) を導き出します。A4
B4平均日数を算出し、セルの 1 つまたは両方が空白の場合は計算をスキップする数式を 1 つ用意したいと思います。
私は試したMax-Min/CountIf：
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/COUNTA(C4:G4),"")
しかし、行 の場合は であるべきところ5、が毎回低すぎる数値になり、行 はであるべきです。日付全体で を使用しようとすると、日数は取得されず、平均日付が取得されました。1592146337AVERAGE
答え1
数えたいのは実際の数値ではなく差なので、数式では分母から 1 を引く必要があります。
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
ヘルパー列をスキップする場合:
=IFERROR(MAX(C4:G4) + (MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
FORCAST を使用することもできます:
=FORECAST(0,C4:G4,ROW($1:$5))
あるいは INTERCEPT:
=INTERCEPT(C4:G4,ROW($1:$5))
これら 2 つは平均ではなく傾向を使用するため、差異が大きく異なる場合は異なる値が得られます。
答え2
Scott Craner の回答は、質問で尋ねられたタスク、つまり平均間隔に基づいて次の日付を予測するタスクをカバーしています。また、トレンドを使用する代替案も提案しています。これは、データの意味に応じて、より良いアプローチにも、より悪いアプローチにもなり得ます。この回答では、読者が適切な種類のソリューションを適用できるように、その違いに焦点を当てます。
質問とスコットの回答では、(Max - Min)/(interval count)平均間隔を見つけるために を使用しました。これは問題ありませんが、効果を説明するために、間隔を計算してそれを使用します。そうすることで、グラフで簡単に確認できるようになります。行 6 のデータを使用します。これは、5 つの値を持つ最初の行だからです。そのデータは次のようになります。
列 C の 5 番目と 6 番目のイベント間の推定間隔から、イベント 6 の日付がわかります。間隔をプロットすると、次のようになります。
平均間隔は次のようになります。
平均はどの時点でも同じで、単なる値です。この場合は です225.5。これを最終日付に追加すると、 に予測される次の発生日が得られます7/13/2019。
問題はここにあります。パターンに従うプロセス、またはランダムに近いものを記録していますか? ランダム イベントは、鋸歯のように、連続する各イベントで上下する予測可能なパターンに従いません。同じ方向の観測の実行が含まれます。データが実際にランダムである場合、パターンの可能性を統計的にテストできますが、人間の脳はパターンを認識するように配線されているため、データ内のパターンは意味があると見なされることがよくあります。データ パターンはロールシャッハ テストのようなもので、実際には存在しない可能性のある意味を投影します。
パターンを調査している場合は、データを調べて、パターンのように見えるものをテストするかどうかを決定できます。ただし、データがランダムであると予想する場合、または次のイベントの偏りのない推定が必要な場合は、パターンの仮定から始めることは望ましくありません。盲目的にトレンド ラインを使用すると、それが行われます。質問で提案されているように、この状況では平均を使用して作業するのが正しい方法です。
次の例を見てみましょう。データを見ると、あなたの脳はデータが曲線を描いているとあなたを納得させようとします。曲線は横ばいになっているように見えますが、全体的には増加しているように見えます。それでは、他の情報がない場合、パターンを調整する最善の方法は何でしょうか? 次々に高次の適合に基づいて次の間隔を予測すると、次の結果が起こります。
一次近似は直線であり、単純な傾向で得られるものです。
これは値が一般的に増加していると認識し、次の区間は になると推定します259.5。2 次近似は次のようになります。
これは、最後の区間を最高点と見なし、次の区間はより低くなると予測します。3232次近似は、4 つの区間で到達できる最高のものであり、次のようになります。
3 次直線は 4 つのポイントに完全に適合します。複数の変曲点が見つかり、最後のポイントの後に上昇し、253次の間隔を予測します。
したがって、「パターン」を生成している基礎プロセスを最もよく表すと思われる線の種類に応じて、次のイベントは から の範囲になる可能性があり7/13/2019ます8/16/2019。
これらの「傾向」のいずれかを拡張して 7 番目のイベントを予測すると、結果はさらに大きく異なります。これらの結果は 5 つのデータ ポイントで得られます。データがパターンに従っていると信じていても、推定するためのデータはそれほど多くありません。多くのデータ行のようにデータ ポイントがさらに少ない場合、どのような形式の推定も危険です。データがパターンに従っていると信じる理由があり、データが一般的にそのパターンに適合している場合は、適切な形状 (つまり、数式の種類) の傾向線を使用すると、「最善の」推定値が得られる可能性がありますが、その場合は、ポイント推定値ではなく、またはポイント推定値に加えて信頼区間を使用します。これにより、少なくとも、予測がどの程度ずれているかを把握できます。
どのような形式のトレンド ラインでも、基礎となるパターンがあり、そのパターンがデータに反映されていることを前提としていることに留意してください。実際にパターンがある場合、通常、いくつかのデータ ポイントではパターンを推定するのに十分ではありません。ただし、パターンがまったく存在せず、偶然の観測シーケンスだけである場合もあります。その場合、パターンに基づいて推定すると、恣意的な方向に進み、予測に大きな誤差が生じる可能性があります。
しかし、別の可能性もあります。多くのものは周期に従います。観察は実際にはパターンの一部かもしれませんが、パターンのほんの一部にすぎません。この例では、それらの観察は正弦波のように見える数十年にわたる周期の一部である可能性があります。それらの観察は周期の頂点に近づいていることを正確に反映している可能性があるため、後続のパターンは上昇ではなく下降する可能性があります (上記の 2 次近似と同様)。したがって、パターンが本物であっても、パターンの背後にある基本的なプロセスについて何も知らずにデータの範囲外を推定するのは危険です。