エクセルと漸化式（３）

一昨日、昨日と、2回にわたって、エクセルで漸化式を用いる方法について、述べてきました。しかし、内容がやや抽象的だった感があり、分かりにくかったかもしれません。
そこで今回は、漸化式を用いる具体的な方法について、述べたいと思います。

エクセルで漸化式を用いることは、何も特殊なことではありません。私たちが当たり前のように行っている、ある列の各行において前行の値を参照するという計算式も、立派な漸化式です。
一般に、統計関数はその参照する範囲が広いために、重複計算が増えて計算負荷が増大するわけです。

例えば、過去25日間の平均値を順次求めていく方法を考えてみます。元データはA列の1行目以降に記述されているものとし、B列においてA列の直近25行間の平均値を求めるものとします。
すると、B24セルまでは空欄が続き、B25セルに初めてA1～A25セルの平均値が計算されることになります。普通は、B25セルには「=AVERAGE(A1:A25)」などと入力します。

続いて、B26セルには「=AVERAGE(A2:A26)」、B27セルには「=AVERAGE(A3:A27)」などと入力していきます。これらは、B25セルを以降のセルにコピーすれば、自動的に設定されます。
これらを見ますと、B列の各セルにおいて、それぞれ25個のセルを参照していることが分かります。

AVERAGE関数（統計関数）を、対象列全体に対して一度だけ用いる分には問題はないのですが、これを各セルにおいて使用すると、想像以上の計算負荷の増大になってしまいます。

その重複状態を解消し、計算負荷を大幅に低減する方法が、漸化式を用いるということになるわけです。漸化式を用いることにより、必要最小限のセル参照だけで計算を行うことができます。
以下に、上述の平均値を求めていく例における、具体的な方法について説明いたします。

まず、最初の平均値を求めます。これは直接計算するしかありませんので、B25セルに「=AVERAGE(A1:A25)」と入力します。
次に、B26セルで2番目の平均値を求めるわけですが、この時に漸化式を用います。

B26セルに、「=B25+(A26-A1)/25」と入力すると、26行目における直近25行間のA列の値の平均値が求まります。

AVERAGE関数を用いると、25個のセルを参照してしまうのですが、上記のように漸化式を用いることで、たった3個のセル参照で済むわけです。
あとは、B26セルを以降のセルにコピーすれば、直近25行の平均値が順次求まっていきます。

これが25行の平均値程度であるならば、漸化式を用いる場合と比べて計算負荷は10倍弱で済みますが、例えば300行の平均値を求めていくとなると、漸化式を用いる場合と比べて、その負荷は100倍ほどにもなってしまうのです。

トレーディングシステムでは、統計関数は意外と多く使うものです。それらを全て漸化式で表すことにより、大幅な計算負荷の低減が期待できます。
昨日のコラムで述べましたように、AVERAGE関数に限らず、ほとんどの統計関数は漸化式で表すことができます。その基本的な方法は、上述のやり方とほとんど変わりません。

タグ：エクセル 漸化式 株 平均値 トレーディングシステム