エクセルでの予測方法と数式

By Guillaume Saint-Jacques, 2008-06-18 (last revised, 2010-02-22)

このガイドは、Microsoft Excelスプレッドシートに簡単に適用可能な基本的な予測手法を解説します。このガイドは、顧客の需要を予測する必要があるマネージャーや幹部に向けて作成されています。理論はMicrosoft Excelを用いて説明されます。理論をカスタムアプリケーションに再現したいソフトウェア開発者向けに、詳細な注記も用意しています。

予測のメリット

予測は、正しい意思決定を下し、利益を生み出したり節約したりするのに役立ちます。以下はその一例です。

在庫を最適に配置する

時間はお金です。スペースもお金です。もちろん在庫切れを避けるために、あらゆる手段を講じて在庫を削減すべきです。

どうやって？予測によって！

簡単にするために: ラベル、コメント、ファイル名

時間の経過とともにデータが蓄積されると、混乱したりミスを犯したりしやすくなります。解決策は？整理整頓を心がけることです。ラベル、コメント、そしてファイルの命名を適切に行うことで、多くのトラブルを回避できます。

常に列にラベルを付ける。各列の最初の行を使い、その列のデータ内容を説明してください。
異なるデータは異なる列に分ける。たとえば、費用と売上など、異なる数値を同じ列に入れると混乱しやすく、計算やデータ処理が困難になります。
各ファイルに分かりやすい名前を付ける。少しの手間で作業効率が向上し、視覚的に識別しやすく、Windowsの検索機能でも簡単に見つけられます。
コメントを使用する.

大量のデータを扱わない場合でも、混乱は起こりやすいものです。特に、長い時を経た後に再びデータに取り組む場合に当てはまります。Excelは素晴らしい解決策、すなわちコメントを提供しています。

コメントの有用性

コメントを挿入したいセルを右クリックし、「insert comment」を選択するだけです。

それらは次の目的で使用できます:

セルの内容を説明するため（例: Mr Doeの見積もりに基づく単価）
将来のユーザーへの注意喚起として（例: この計算について疑問があります…）

高度な在庫予測は、当社のinventory forecastingウェブアプリによって実現します。Lokadは需要予測を通じた在庫最適化を専門としています。このチュートリアルの内容―その他多くの機能も―は、当社のforecasting engineのネイティブ機能です。

はじめに: 傾向線を用いた簡単な予測例

Viewing your data

では、最初の予測に進みましょう。このパートでは、以下のファイルを使用します: Example1.xls。自分で手順を繰り返したい場合は、ファイルをダウンロードしてください。このデータはあくまで一例です.

我々のデータ: 最初の列には、類似製品の単価に関するデータ（単価は製品の品質を反映しています）があり、2列目には販売数量のデータがあります.

知りたいこと: もし別の商品を販売し、その品質が単価150ドルに相当するとしたら、どれだけの数量が売れると予測できるでしょうか？

その方法: 単価と売上の間に単純な数学的関係性を見出し、その関係性を予測に活用します.

まずは、Excelでグラフを作成し、データを目で確認することが有用です。あなたの目は、数秒で傾向を見抜く優れたツールです.

これを行うには、データを選択し、「挿入 > グラフ」を使い、XY（散布図）オプションを選びます。品質の関数として売上を見積もるため、単価を水平軸に、売上を垂直軸に配置します.

さて、数秒間立ち止まり、見えるものをじっくり観察しましょう：関係は上昇傾向にあり、線形のように見えます.

正確な関係の形状を把握するために、グラフを右クリックし、「Trendline」オプションを選択します.

Creating a trendline

次に、データに「合う」関係（つまり最も適した説明となる関係）を選択します。ここでも目視で判断します。この場合、点はほぼ一直線上にあるため「linear」設定を使用します。後ほど、より複雑で実際に即した「exponential」などの設定も使用します.

傾向線がグラフに表示され、右クリックで正確な関係（例: y = 102.4x - 191.64）が確認できます.

理解してください: 販売数量 = 102.4 × 単価 - 191.64.

したがって、単価が150ドルの場合、予測販売数量は 102.4×150 - 191.64 = 15168 単位となります.

A linear trendline

これで最初の予測が無事完了しました.

しかし、注意してください: ソフトウェアは常に2つの列の間に何らかの関係性を見出しますが、実際にはその関係性が非常に弱い場合もあります！したがって、堅牢性の確認が必要です。以下はその手順です:

まず、常にグラフを確認する。上記の例のように点が傾向線に密接している場合、関係性が堅牢である可能性が高いです。しかし、点がほぼランダムに散らばり、全体的に傾向線から大きく外れている場合は注意が必要です。相関が低い場合、推定された関係を盲目的に信頼すべきではありません.

The dots are everywhere: no evident relationship, unreliable forecasts

The dots “make sense”, and allow more reliable forcasting

グラフを確認した後、CORREL関数を使用できます。上記の例では、CORREL(A2:A83,B2:B83) と表記されます。結果が0に近ければ相関は低く、本当の傾向は存在しないと結論づけられます。1に近ければ相関は強く、これは発見した関係の説明力を高めるものです.

相関が強いかどうかを判断する、より繊細な方法もあります；この件については後ほど説明します.

もちろん、これらの最終ステップは自動化可能です：関係式をメモしたり、電卓で計算する必要はありません。Analysis Toolpakが必要です!

Forecasting using the Analysis Toolpak

進む前に、Excel ATP（Analysis Toolpak）がインストールされているか確認してください。詳細は、Installing the Analysis Toolpakのセクションを参照してください.

Unfortuntaley such perfect sales data with such a nice, simple linear relationship is quite uncommon in real life. Let us have a look at what Excel has to offer for more complicated situations, with more complicated data.

Going further: the example of exponential fitting

想像できるように、データの線形モデルが常に最適とは限りません。実際、指数モデルに従うべきだと考えられる理由は多々あります。経済における多くの挙動は、（例として利息の複利計算が挙げられるように）指数関数の方程式によって説明されます.

ここでは、指数関数フィッティングの実行方法を説明します:

データを確認する。簡単なグラフを作成して眺めるだけです。もしデータが指数関数的な進展を示すなら、以下のように見えるはずです:

perfect exponential shape

これは理想的なケースです。もちろん、データが完全にこの形に一致することはありません。しかし、点がこの分布に概ね従っている場合は、指数関数フィッティングを検討する価値があります.

Using trendlines

前の例と同様に、データのグラフを作成して傾向線を追加し、線形ではなく「exponential」を選び、表示された方程式を確認します.

幸いなことに、これらはAnalysis Toolpakを使って直接実行することも可能です。すべてのデータを空白のExcelシートに入力し、Tools => Data Analysis に進みます.

Installing the Analysis Toolpak (ATP)

ATPはMicrosoft Excelに付属するアドインですが、必ずしもデフォルトでインストールされているわけではありません。これをインストールするには、以下の手順に従ってください:

OfficeのCDを手元に用意してください。ATPファイルをインストールするために、ExcelがCDの挿入を要求する場合があります.
Excelシートを開き、Toolsメニューに進み、Add-Insを選択します。ウィンドウの最初のチェックボックス、「2.Analysis Toolpak」にチェックを入れてください.
ソフトウェアが求めた場合は、OfficeのCDを挿入してください.
以上です！「Tools」メニューに多くの機能が追加され、「Data Analysis」オプションも含まれるようになります。これが最も頻繁に使用される機能です.

Using the Analysis Toolpak (ATP)

… in a linear setting

さて、線形の例に戻りましょう。データが「良好に見える」（上記の図参照）場合、傾向線のプロセスを経ずにATPを使用して関数形を直接推定できます.

データシートを開き、Toolsメニューから「Data Analysis」を選択します。ウィンドウが表示され、実行する分析の種類を尋ねられます。線形設定の場合は「regression」を選んでください.

次に、Excelに2つの引数、「Y range」と「X range」を指定する必要があります。Y rangeは推定したいデータ（例: 売上）を示し、X rangeは売上を説明できると考えるデータ（この例では単価）を含みます。例えば（example1.xls参照）、売上データは列Bの3行目から90行目にあるため、Y rangeとして「$B$3:$B$90」、X rangeとして「$A$3:$A$90」を指定してください。完了したら「ok」をクリックします.

A new sheet appears, containing the « regression results ».

The Analysis Toolpak Output, in the case of an Ordinary Least Squares regression

最も重要な結果は、シート下部の「Coefficients」列に含まれています。切片が定数となり、「X variable」の係数がXの係数（ここでは単価）です。したがって、傾向線機能で得られた方程式と同じ式が得られます。Sales = Intercept + Xcoefficient * unit costSales = -126 + 100 * unit cost

このシートには、推定の精度を示す有用な数値「R Square」も含まれています。1に近ければ推定は良好で、方程式がデータをかなりよく表現していることを意味します。0に近ければ推定は不十分であり、恐らく別のフィッティング（以下のexponential fitting参照）を試すべきです.

この方法は、おそらく「trendline」技法よりも速いでしょう。しかし、技術的で視覚的でないため、データのプロットや目視チェックの手間を省きたい場合でも、少なくとも「R Square」値は確認してください.

… using exponential fitting

もし線形推定がうまくいかない場合（例: R Squareが0.1のように低い場合）、Exponential Fittingの使用を検討してください.

通常通り、Analysis Toolpakを起動します。まずデータシートを開き、Toolsメニューから「Data Analysis」を選択します。ウィンドウが表示され、実行する分析の種類を問われます.

我々の指数関数設定では、「exponential」を選択します.

Excelは1つの入力範囲のみを要求します。予測したいデータが入っている列（例: 単価）を選択し、「smoothing factor」を設定してください.

どのモデルを選べばよいか？

全ての推定手法を試す必要はありません。最適な手法は、自動化を通じてのみ見出せます。利用可能な手法が非常に多数存在するため、全てのモデルをデータに対してベンチマークしたい場合は、Lokadへ送ることを検討してください。当社には、全てのモデルを「テスト」し、ビジネスデータに最適なものだけを選別する強力なコンピューターシステムがあります.