らくらくIT活用術

PC活用講座 - Excel

「Excelの便利機能活用術」
関数ひとつで過去の実績から売上を予測する

ITライター 立山秀利

ビジネスの現場では、過去の業績から今後の売上を予測するなど、過去のデータから未来を予測したい場面は多い。Excelにはそのための関数が複数種類用意されている。今回はその中でもっともシンプルな関数であるFORECAST.LINEAR関数の使い方を解説する。

FORECAST.LINEAR関数で未来の結果を予測

FORECAST.LINEAR関数は統計の仕組みを利用し、過去のデータから未来の結果を予測するための関数である。予測の元となるデータと、予測したいデータが直線の関係にあるという前提で傾向を読み取り予測を行う。

同関数の使い方について具体例を挙げて解説しよう。図1のように2017~2021年の売上実績のデータがあり、A列に年、B列に売上の数値(単位:百万円)が4~8行目に入力されているとする。これら過去のデータから、2022~2024年の売上を予測したいとする。A9~A11セルには年を入力しておき、その右隣りのB9~B11セルに各年の予測売上をFORECAST.LINEAR関数で求めるとする。

図

図1

FORECAST.LINEAR関数の書式は以下である。

-------------------------------------------
FORECAST.LINEAR(X, 既知のy, 既知のx)
X           予測の元となるデータ
既知のy  予測したい結果の過去のデータ
既知のx  予測の元となる過去のデータ
-------------------------------------------

まずは2022年の売上をB9セルに予測してみよう。FORECAST.LINEAR関数の第1引数「X」には、予測の元となるデータとして、2022という年の数値が入ったA9セルを指定する。

第2引数「既知のy」には、2017~2021年の売上データが入力されたB4~B8セルを指定する。今回そのセル番地は絶対参照で指定しておくとする。このあと2023~2024年の予測のためのFORECAST.LINEAR関数をB10セルとB11セルに入力する際、オートフィルでコピーするためだ。

第3引数「既知のx」には、2017~2021年の年の数値が入ったA4~A8セルを指定する。こちらも絶対参照で指定しておく。

-------------------------------------------
FORECAST.LINEAR(A9,$B$4:$B$8,$A$4:$A$8)
-------------------------------------------

以上のような各引数でFORECAST.LINEAR関数をB9セルに指定した画面が図2だ。この画面では「関数の引数」ダイアログボックスで指定しているが、もちろん数式バーに直接入力しても構わない。

図

図2

[OK]をクリックするか[Enter]キーを押して入力を確定すると、図3のように、B9セルには2022年の売上予測が求められる。年と売上のデータが直線の関係にあるという前提のもと、2017~2021年のデータから予測された売上だ。

図

図3

2023~2024年の売上も予測してみよう

これで2022年の売上が予測できた。続けて、2023~2024年の売上予測もB10~B11セルに求めてみよう。先ほどB9セルに入力したFORECAST.LINEAR関数をB10~B11セルにオートフィルなどでコピーすれば、図4のように求められる。

図

図4

B9セルでは、FORECAST.LINEAR関数の第1引数「X」は絶対参照ではない通常の形式で指定しているため、コピーしたB10~B11セルでは場所にあわせて変更され、A10セルとA11セルが指定される。一方、第2引数「既知のy」と第3引数「既知のx」は絶対参照で指定したため、コピーしても変更されず、A4~A8セルおよびB4~B8セルのままだ。このように絶対参照をうまく使うと、過去のデータの範囲を固定したままコピー可能となる。

また、注意点として、第2引数「既知のy」と第3引数「既知のx」のデータ数を必ず揃えなければエラーになる。例えば、第2引数「既知のy」をB4~B8セル(セル5つ分)と指定したのに、第3引数「既知のx」をA4~A8セル(セル6つ分)と指定してしまうと、データ数が揃っていないのでエラーになる。

なお、FORECAST系の関数にはFORECAST.LINEAR関数以外にも、季節性の変動も考慮して予測できるFORECAST.ETS関数などがある。たとえば、アイスクリームの売上を月単位で記録したデータから予測する際、夏場には売上が上がるという季節性の変動を考慮できる。

このようにFORECAST.LINEAR関数を使えば、過去のデータから未来を予測できる。同関数は予測の元となるデータは1種類のみだが、複数のデータから予測したいケースも少なくない。その場合に使うのがTREND関数だ。次回はTREND関数の使い方を紹介する。

その他の【Excel】を見る

PC活用講座 一覧へ

立山秀利(たてやま・ひでとし)

カーナビのソフトウェア開発、Webプロデュース業務を経て、現在は、システムやネットワーク、Microsoft Officeを中心に執筆中。 主な著書に『Excel VBAのプログラミングのツボとコツがゼッタイにわかる本』などがある。

(監修:日経BPコンサルティング)