エクセルのちょっといい話（１）

エクセルでトレーディングシステムを作成したりする場合、計算に使用したい関数がなかったり、使用条件が限定されていたりすることがあります。
そのような時には、マクロを作成するのが一般的ですが、中にはちょっとした工夫をすることにより、ワークシート上で直接記述できる場合があります。

今回は、そんなちょっとした工夫について、お話ししたいと思います。

株価データなどを統計処理するに当たって、標準偏差などを求める場合があります。これが全データに対する単一の値であれば問題はないのですが、例えば、いくつかのデータ群があって、それらの標準偏差を個別に求め、最後にそれらの平均を求めたいとします。

この場合、まずは平均値としてどのような平均を求めるかが問題となります。

平均値には、一般に3つの種類があります。一つは相加平均（単純平均）で、これはもっとも一般的に用いられるものです。
x1、x2、・・・、xnのn個のデータを考えた時、相加平均は次式で示されます。

　　相加平均＝(x1+x2+･･････+xn)/n

2つ目が相乗平均（幾何平均）で、これは次式で示されます。

　　相乗平均＝(x1*x2*･･････*xn)^(1/n)

3つ目が調和平均で、次式で示されます。

　　調和平均＝n/(1/x1+1/x2+･･････+1/xn)

これらには、相加平均≧相乗平均≧調和平均、という関係があります。これについては、逆ドルコスト平均法に関するコラムの中で解説しています。

標準偏差という統計量は、基本的にはマイナスの値を取りません。したがって、相加平均（単純平均）の他に、相乗平均（幾何平均）を考えることができます。
調和平均については、標準偏差が0となる可能性がある以上、無条件で採用することはできません。

さて、標準偏差とは、分散の平方根のことです。ここでは詳しくは説明しませんが、分散という、データのバラツキを示す指標のルートを取っているわけです。
数学的には、ルート同士の加算は一般的ではありません。その演算結果を整理しようとした場合に、極めて複雑になってしまうからです。

一方、ルート同士の積算は容易です。ルートの中身を全て掛け合わせて、最後に大きなルートで一まとめにすればいいからです。
そのように考えると、標準偏差の平均値を取るには、相乗平均の方が合理的であることが分かります。

それでは、エクセルで相乗平均を計算してみましょう。それには、GEOMEAN関数を用います。しかし、それではすぐに行き詰まってしまいます。
GEOMEAN関数は、実は最大で30個のデータしか計算できないのです。データ数が30個を超える場合、エラーとなってしまいます。

では、相乗平均の定義に戻って、全データを掛け合わせた後に、そのn乗根を求めればいいのでしょうが、それは事実上不可能です。
データの種類にもよりますが、計算結果はすぐにオーバーフローに陥ってしまうでしょう。

それでは、どうしたらいいのでしょう。実は、非常に簡単に相乗平均を求めることが可能です。それには、対数を用いればいいのです。
各データの（10を底とする）対数（LOG）を取り、それらの平均値を求めて、最後にそれを10の肩に乗せればいいのです。

すなわち、次のようになります。

　　相乗平均＝10^((LOG(x1)+LOG(x2)+･･････+LOG(xn))/n)

これなら、オーバーフローを起こさずに計算が可能です。ただし、データに0が含まれる場合は、エラーとなります。しかし、その場合は、相乗平均そのものが0となってしまいますので、相乗平均を求めること自体に問題があるということになります。

さらには、配列数式を用いることにより、これを一度に計算することが出来ます。

データがA列の1～100行目にあり、これらの相乗平均を求めようとする場合、結果を求めるセルに次式を入力し、"Shift"+"Ctrl"+"Enter"キーを押します。

　　=10^(AVERAGE(LOG(A1:A100)))

すると、上式全体が{}で囲まれ、結果が表示されます。式が{}で囲まれているのは、それが配列数式であることを示しています。

この式は、LOG(A1)からLOG(A100)までを計算してそれらの平均値を取り、その値を10の指数として計算する、ということを行なっています。
配列数式を用いないと、少なくとも2列が必要となるのですが、最初のデータ列だけで済んでいます。

なお、この配列数式は様々な使用方法が可能です。例えば、出来高加重平均を計算する場合には、通常であれば、別の列で株価×出来高を計算させてやる必要があるのですが、配列数式を用いると、株価データをA1～A100セル、出来高データをB1～B100セルとして、次式で求めることができます。

　　=SUM(A1:A100*B1:B100)/SUM(A1:A100)

ここで、"Shift"+"Ctrl"+"Enter"キーを押すことで、この式が配列数式であることを確定します。すると、1行目から100行目までのデータの出来高加重平均が計算されます。

以上のように、ちょっとした工夫で、エクセルにおける演算を簡略化することができます。他にも様々なテクニックがありますが、それはまた次の機会にお話しします。