SSブログ

明日のために~その4~ [システム作成講座]

株価データファイル(参照元)へのセル参照を行なうことで、任意のブック(参照先)上に株価データを反映させることができます。
しかし、参照元のセルが空欄の場合、参照先のセルでは"0"と表示されてしまうことが分かりました。これでは、何かと不便です。

エクセルでトレーディングシステムを作成するに当たって、空欄にも意味を持たせる場合が多々あります。
例えば、システムの起点日を全ての銘柄で統一した場合、比較的最近上場した銘柄では、共通の起点日における株価データが存在しない可能性があります。

そのような時に、株価データ欄が空欄であれば、データが存在しないことを示せますが、それが"0"であったとすると、0円という株価データが存在することになってしまいます。
これは、システムを構築する上で、非常に厄介な問題です。もちろん、銘柄毎に起点日を変えてもいいのですが、そうした場合でも次のような問題が生じます。

例えば、株価の移動平均をインジケータとするシステムを考えた場合、最初に移動平均が算出されるまでには、(移動平均期間-1期間)分の空白期間が生じます。
これを、空欄で表現するのか、"0"で表現するのかでは、その後のシステム構築に大きな違いが生じてきます。

そのように考えると、データが存在しない場合は全て空欄とした方が、システム全体の整合性が取りやすくなります。
すなわち、参照元にデータが存在しない(空欄)場合は、参照先も空欄にするべきです。

そこで、参照先に表示された"0"を、空欄として表す方法を考えます。

ここで、参照先の書式を変更すれば、"0"を空欄にできそうに思えますが、実はその方法は上手くいきません。
参照元の空欄を参照した時点で、参照先には数値としての"0"が設定されてしまうからです。"0"はどのように書式をいじっても、"0"以外にはなりません。

ではどうすればいいかと言いますと、参照元が空欄の場合は参照先も空欄にし、参照元が空欄ではない場合は、参照先が参照元と同じになるようにしてやれば良いのです。
そんなこと言われても、具体的にどうすればいいか分からない、と思われる方がいらっしゃるかもしれませんが、実は上記の内容が答えそのものなんです。

もう一度繰り返して記しますと、「もし参照元が空欄の場合は参照先を空欄にし、そうでない場合は参照先を参照元と同じにする」ということになります。
これは、エクセルのif関数で表現できることと、全く同じことを言っているに過ぎません。

if関数は、エクセルでトレーディングシステムを作成するに当たって、恐らく最も使用頻度の高い命令です。
基本的には、if関数とoffset関数、そして四則演算を使いこなせるようになれば、大抵のシステムは作成することができるようになります。

今、参照先のA2セルから、参照元のA2セルを参照することを考えます。先回作成した参照先のブックを開くと、A2セルには"01/00/00"と表示されていることと思います。
これを空欄にしてみましょう。そのためには、A2セルに入力された数式をもう一度確認する必要があります。

そこには例えば、「='D:¥株式¥株価データ2¥[1301極洋01.xls]Sheet1'!A2」と入力されているものとします。なお、''内の表記は、皆さんのファイル環境によって異なります。
ここで、上記の数式の「=」以降が参照元を表しているわけです。

したがって、参照元が空欄ということは、「'D:¥株式¥株価データ2¥[1301極洋01.xls]Sheet1'!A2」が空欄ということです。
エクセルで空欄を表すには、""を用います。そうすると、上記の「」内が空欄である、というのは、次のように表されます。

 'D:¥株式¥株価データ2¥[1301極洋01.xls]Sheet1'!A2="" ・・・条件

そして、参照先のA2セルが空欄ということは、同様に次のようになります。

 A2="" ・・・分岐1:条件が正しい場合

更に、参照先のA2セルが参照元と同じ、というのは、次のようになるわけです。

 A2='D:¥株式¥株価データ2¥[1301極洋01.xls]Sheet1'!A2
                        ・・・分岐2:条件が正しくない場合

これらを、if関数でつなぎ合わせてみましょう。if関数の使い方については、今更述べるまでもないでしょうが、次のようになります。

 =if(条件, 分岐1, 分岐2)

したがって、上式にそれぞれの数式を当て嵌めてやれば、参照先のA2セルに記述する数式は、次式のようになるわけです。
ただし、通常、自分のセルを表す「A2=」は省略します(入力すると循環参照エラーになるのでご注意ください)。

 =if('D:¥株式¥株価データ2¥[1301極洋01.xls]Sheet1'!A2="",""
      ,'D:¥株式¥株価データ2¥[1301極洋01.xls]Sheet1'!A2)

非常に長ったらしくなりましたが、このように表記することで、参照元が空欄ならば、参照先も空欄にすることができるようになります。
あとは、この数式をA1~G40の全てのセルにコピーしましょう。

最初にA2セルを選択した状態で、編集メニューの「コピー」を実行し、続いてA1~G40セルを選択した状態で、編集メニューから「形式を選択して貼り付け」をクリックし、「数式」にチェックを入れて「OK」ボタンを押します。

これで、選択した全てのセルに、対応した数式(if関数)が設定され、参照元にデータがあるセルではその内容が、参照元が空欄のセルには空欄が表示されたことと思います。
これで、ようやく株価データファイルを、そのまま参照することができるようになりました。

しかし、銘柄を変更する度に、コピーを繰り返していたのでは面倒です。そこで、次回は、エクセルの機能を用いて、簡単に参照元を変更する方法について考えてみます。

nice!(0)  コメント(4)  トラックバック(0) 

nice! 0

コメント 4

たかとび

お疲れ様です。IF関数とかカッコ内にさらにカッコがあったりして
長くて英語と数字だらけで難しいです。。
by たかとび (2011-03-01 09:31) 

Kフロー

if関数につきましては、今後もしつこく取り上げていくつもりですので、まずはそういうものだという程度の理解でよいかと思います。
何度も繰り返し目にしていくうちに、いずれは慣れてくるものと思います。

by Kフロー (2011-03-01 19:47) 

たかとび

簡単なものならばいいのですが
IF関数の中にさらに関数とか難しいのもあるようですね。。
でも少しずつではありますが勉強してますので
練習問題とかできると嬉しくなります。。
by たかとび (2011-03-01 20:40) 

Kフロー

if関数は単なる入れ物に過ぎませんので、基本的にはどんな関数でも取り込むことができます。
でも、それはif関数+関数として覚えるのではなく、if関数と他の関数は別物として理解した方がいいと思います。

if関数は、箱の内側に1つの仕切りをつけて収納部を2つにしたようなもので、そのどちらに物を入れるかの判定機構を箱の入口に付けただけのものです。
判定結果に応じて、2つの部分のうちどちらか一方に振り分けることになります。その箱の中に入れるものは何でもよく、それが関数であったり、場合によってはif関数そのものであってもいいのです。

by Kフロー (2011-03-02 10:01) 

コメントを書く

お名前:
URL:
コメント:
画像認証:
下の画像に表示されている文字を入力してください。

Facebook コメント

トラックバック 0

この広告は前回の更新から一定期間経過したブログに表示されています。更新すると自動で解除されます。