Excelでの予測手法と数式
このガイドでは、Microsoft Excelスプレッドシートに簡単に適用できる基本的な予測手法について説明します。このガイドは、顧客の需要を予測する必要があるマネージャーやエグゼクティブに適用されます。この理論はMicrosoft Excelで説明されています。ソフトウェア開発者は、この理論をカスタムアプリケーションに再現したい場合に使用できる詳細なノートも利用できます。
予測の利点
予測は正しい意思決定を支援し、お金を稼ぐ/節約するのに役立ちます。以下に一つの例を示します。
- 在庫を最適にサイズ調整する
時間はお金です。スペースもお金です。したがって、もちろんストックアウトを経験せずに在庫を減らすためには、手元にあるすべての手段を利用する必要があります。
どうやって？予測によって！
作業を簡単にする方法：ラベル、コメント、ファイル名
データが蓄積されるにつれて、混乱しやすくなり、ミスを犯しやすくなります。解決策は？乱雑にしないことです：ラベルを適切に使用し、コメントを追加し、ファイル名を正しく命名することで、多くのトラブルを回避できます。
- 常に列にラベルを付ける。各列の最初の行を使用して、その列に含まれるデータを説明します。
- 異なるデータには異なる列を使用する。異なる数値（例：コストと売上）を同じ列に入れないでください。混乱する可能性が非常に高くなり、計算やデータの処理がより困難になります。
- 各ファイルにわかりやすい名前を付ける。それにはほとんど労力がかかりませんが、作業をスピードアップします。視覚的に識別しやすくし、Windowsの検索機能を使用して見つけやすくします。
- コメントを使用する。
大量のデータで作業しない場合でも、混乱することは非常に簡単です。特に以前に作成したデータに戻る場合には、これが当てはまります。Excelには、コメントという素晴らしい解決策があります。
コメントを追加したいセルを右クリックし、「コメントの挿入」を選択します。
以下のように使用できます：
- セルの内容を説明する（例：Mr Doeの見積もりに基づく単位コスト）
- シートの将来のユーザーに対して警告を残す（例：この計算について疑問があります…）
はじめに：傾向線を使用した簡単な予測の例
データの表示
さあ、最初の予測を行いましょう。このパートでは、このファイルを使用します：Example1.xls。手順を自分で繰り返すために、ファイルをダウンロードすることができます。このデータは単なる例です。
データ: 最初の列には、類似製品の単位コストに関するデータがあります（単位コストは製品の品質を反映しています）。2番目の列には、販売数量に関するデータがあります。
知りたいこと: もしも品質がコスト150ドル/単位に対応する製品を販売した場合、どのくらいの数量を売れるのでしょうか？
どのようにしてそれを実現するか: ここでは、非常にシンプルです。単位コストと販売の間に単純な数学的な関係を見つけ、その関係を使用して予測を行いたいと思います。
まず、データを見るためにExcelでグラフを作成すると常に便利です。目で見ることは、数秒でトレンドを特定するのに非常に優れたツールです。
これを行うには、データを選択し、[挿入] > [グラフ]を使用し、XY（散布図）オプションを選択します。品質を横軸、販売を縦軸として、販売を品質の関数として推定したいと思います。
さあ、数秒間立ち止まって見てみましょう：関係は増加していて、直線的です。
関係の正確な形を把握するために、チャートを右クリックし、「トレンドライン」オプションを選択します。
これで、データに「適合」（つまり最もよく説明する）関係を選択する必要があります。ここでも、目を使います：この場合、点はほぼ直線上にあるため、「線形」の設定を使用します。後で、「指数関数」などの他のより複雑で現実的な設定も使用します。
トレンドラインがチャートに表示されます。別の右クリックで、関係の正確な形を表示することもできます：y = 102.4x - 191.64。
理解する：販売数量 = 単位コストの102.4倍 - 191.64。
したがって、単位コスト150ドルで生産することを決めた場合、15168個の単位を売ることができると予想されます。
これで、最初の予測が成功裏に完了しました。
ただし、注意してください。ソフトウェアは常に2つの列の間の関係を見つけることができますが、この関係が実際には非常に弱い場合でもです！したがって、堅牢性のチェックが必要です。以下は、これを素早く行う方法です：
- まず、チャートを常に確認してください。上記の例のように、ドットがトレンドラインに近く配置されている場合、関係が堅牢である可能性が高いです。ただし、ドットがほぼランダムに配置され、一般的にトレンドラインからかなり遠い場合は注意が必要です。相関は弱く、推定された関係を盲目的に信頼してはいけません。
- チャートを確認した後、CORREL関数を使用できます。この例では、関数は次のようになります：CORREL(A2:A83,B2:B83)。結果が0に近い場合、相関は低く、結論は次のとおりです：実際のトレンドは単に存在しない。1に近い場合、相関は強いです。後者は役に立ちます。なぜなら、見つけた関係の説明力が高まるからです。
相関が高いことを確認するためのより微妙な方法もありますが、後でこれに戻ります。
もちろん、これらの最後のステップは自動化できます。関係をメモする必要はなく、ポケット電卓を使用して計算を行う必要はありません。分析ツールパックが必要です！
分析ツールパックを使用した予測
進む前に、Excel ATP（分析ツールパック）がインストールされているかどうかを確認してください。詳細については、「分析ツールパックのインストール」セクションを参照してください。
残念ながら、このような素晴らしい売上データと素晴らしい単純な線形関係は現実の生活ではかなり珍しいです。より複雑なデータを持つより複雑な状況に対してExcelが提供するものを見てみましょう。
さらに進む：指数適合の例
おそらく想像できるように、データのこのような線形モデルは常に起こるわけではありません。実際、経済の多くの動きは指数関数のモデルに従うと考えられています（例えば、利子の複利計算は古典的な例です）。
指数適合を行う方法は次のとおりです：
1）データを確認してください。単純なグラフを描いて、それを見てください。指数的な進化に従っている場合、次のように見えるはずです：
これが完璧なケースです。もちろん、データは決して完全にこれに似ることはありません。しかし、点がおおよそこの分布に従っているように見える場合、指数適合を考慮することをお勧めします。
前の例と同様に、データのチャートを描いて、トレンドラインを要求し、線形の代わりに「指数」を選択することができます。その後、通常どおりに表示される方程式を集めます。
2）幸いなことに、これを直接行うこともできます。Analysis Toolpakを使用して、すべてのデータを空のExcelシートに入力し、ツール => データ分析に移動します。
Analysis Toolpak（ATP）のインストール
ATPはMicrosoft Excelに付属しているアドインですが、デフォルトで常にインストールされているわけではありません。インストールするには、次の手順を実行できます：
- OfficeのCDを持っていることを確認してください。ExcelはATPファイルをインストールするためにCDの挿入を要求する場合があります。
- Excelシートを開き、ツールメニューに移動し、その後アドインを選択します。ウィンドウの最初のボックスである「2. Analysis Toolpak」のチェックボックスをオンにします。
- ソフトウェアによって求められた場合は、OfficeのCDを挿入します。
- 以上です！「ツール」メニューには、データ抽出パイプラインを含む多くの機能が追加されていることに注意してください。これが最も使用するものです。
Analysis Toolpak（ATP）の使用
… 線形設定で
さて、線形の例に戻りましょう。データが「良い」ように見える場合（上記のイラストを参照）、ATPを使用して機能形式の直接的な推定を行うことができます。トレンドラインのプロセスを経ずに済みます。
データシートを開き、次に「ツール」メニューを開き、「データ分析」を選択します。ウィンドウが表示され、実行したい分析の種類を尋ねられます。線形設定の場合は「回帰」を選択します。
これで、Excelに2つの引数を指定する必要があります。「Y範囲」と「X範囲」です。Y範囲は推定したいもの（つまり、売上）を示し、X範囲には売上を説明できると考えられるデータ（ここでは単位コスト）が含まれています。例えば（example1.xlsを参照）、売上データは列Bにあり、行3から行90までなので、Y範囲には「$B$3:$B$90」、X範囲には「$A$3:$A$90」を入力する必要があります。完了したら、「OK」をクリックします。
「回帰結果」が表示される新しいシートが表示されます。
最も重要な結果は、シートの一番下にある「係数」列に含まれています。切片は定数であり、「X変数」の係数はX（ここでは単位コスト）の係数です。したがって、私たちは「トレンドライン」関数を使用して見つけた同じ方程式を見つけます。売上 = 切片 + X係数 * 単位コスト売上 = -126 + 100 * 単位コスト
このシートには、推定の良さに関する情報を提供する便利な数値も含まれています。「R Square」と呼ばれます。これが1に近い場合、推定が良いことを意味し、見つけた方程式がデータのかなり良い表現であることを示します。0に近い場合、推定が良くないため、他の適合方法を試すことをお勧めします（以下の指数適合を参照）。
この方法はおそらく「トレンドライン」の技術よりも速いです。ただし、少し技術的で視覚的ではありません。したがって、データのプロットや目視に手間をかけたくない場合は、少なくとも「R square」の値を確認してください。
…指数適合を使用する
線形推定がうまくいかない場合（例えば、R-Squaredが低い場合、つまり0.1を取得した場合）、指数適合を使用することができます。
通常どおり、解析ツールパックを起動します。データシートを開き、「ツール」メニューを開き、「データ分析」を選択します。ウィンドウが表示され、実行したい分析の種類を尋ねられます。
指数設定では、選択するのは「指数」です。
Excelは1つの入力範囲のみを要求することに注意してください。予測したいデータ（つまり、単位コスト）が含まれている列を選択し、「平滑化係数」を選択します。
どのモデルを選択すればよいかわかりますか？
最適な推定方法を見つけるために、各推定方法を試す必要はありません。利用可能な方法が非常に多いため、これは自動化によってのみ達成できます。すべてのモデルをデータに対してベンチマークテストしたい場合は、Lokadに送信することを検討してください。私たちの強力なコンピュータシステムは、すべてのモデルを「テスト」し、ビジネスのデータに最適なモデルのみを選択します。