エクセルと漸化式（２）

昨日のコラムでは、AVERAGE関数は容易に漸化式に置き換えることができるものの、例えばSLOPE関数は直接的に漸化式に置き換えることができない、といったことを述べました。
ただし、ちょっとした工夫によって、SLOPE関数も漸化式で表現することが可能です。

まず、エクセルにおいてSLOPE関数がどのように計算されているのか、見てみることにしましょう。それは、次式で計算されます。

(1)　　b={n∑xy-(∑x)(∑y)}/{n∑x^2-(∑x)^2}

bが回帰直線の傾き（SLOPE関数の計算結果）、nが標本数、xが独立変数（例えば日付）の値、yが従属変数（例えば株価）の値、∑は総和を表します。
詳細は、エクセルのヘルプに"SLOPE"と入力し、検索してご確認ください。

ここで、bはnの関数とも捉えることができ、それを明示的に示すためにb[n]と表記することにします。(1)式の漸化式を求めるということは、b[n]をそれ以前の結果であるb[n-1]やb[n-2]などを含んだ関数として表すことに他なりません。

それが容易ではないことは、(1)式をいろいろとこねくり回してみれば分かります。これをどのように変形しても、b[n]＝f(b[n-1],b[n-2])などといった式（f()はカッコ内を変数とした関数を表します）で表すことはできません。

もしもb[n]が漸化式で表せるのであれば、エクセルにおいて前行（および前々行）の計算結果と、いくつかの個別の値を用いることで、b[n]を求めることができます。
nが大きい場合は、(1)式を普通に計算するよりも遥かに小さい計算負荷となります。

では、いったいどのようにすれば、エクセルにおける統計計算の計算負荷を低減することができるのでしょうか？
その答えは、(1)式の各項を個別に漸化式で表すということです。

例えば、∑xyは容易に漸化式で表すことができます。これを例えばc[n]とすると、

(2)　　c[n]=c[n-1]+x[n]y[n]

で求められます。
同様に、∑xや∑y、∑x^2も、漸化式で表すことができます。また、(∑x)^2は、単に∑xの計算結果を2乗するだけです。

そして、最後にそれらを用いて(1)式を計算すれば、b[n]が求まります。b[n]を構成する各項は漸化式によって求められますので、b[n]そのものの計算負荷も低減されることになります。
それらを計算するためにはいくつかの列を必要としますが、それを補って余りあるだけの処理速度向上効果が得られます。

ＫＦシステムクリエイターにおいて、各行に渡る全ての統計関数を全て漸化式に置き換えたところ、最適化演算において3割もの処理速度向上が実現できました。
もちろん、上記以外にも改善は行っていますが、機能向上による負荷増大も合わせると、処理速度向上の大部分は漸化式化によるものです。

顕著な例では、200列以上にもおよぶシートの中で、わずか2列を漸化式に置き換えたところ、それ以前と比べて2割ほどの処理速度向上となりました。
統計関数が、全体の処理にいかに負担を掛けていたかが分かります。

なお、各行において標本数が固定の場合、漸化式化を行うにはちょっとした工夫が必要ですが、処理の流れを考えれば容易に実現できると思います。
興味のある方は、チャレンジしてみてはいかがでしょうか。

タグ：エクセル 漸化式 SLOPE関数 株 システムトレード