チャート(グラフ)の表示範囲を動的に指定する

投稿者: | 2016年6月24日

6.チャート(グラフ)の表示

 

 

チャート(グラフ)の表示範囲を動的に指定

 

 

応用編の第1弾として、チャートを追加し、レートの上がり下がりや移動平均線のクロスなどを視認できる様にします。

 




 

では、これまでに作成したファイルを開きます。
ここまでで下記の状態まで作成できているかと思います。

早速、チャートを追加してきます。

 

 

 

 

 

エクセルの機能を使用してロウソク足のチャートを表示することは簡単に出来ますが、その状態ではデータの量が多くなってもチャートに表示される部分は変わらないことになります。
そのため、チャートに表示されているデータも可変する様に、「名前の定義」を使用して、定義した名前によってチャートに表示するデータを設定します。

まず、チャートに表示する内容ですが、4本値と移動平均線、それに売買サインを一つのチャートに表示させます。

これらのデータを可変で表示できる様にしなくてはなりませんが、まずは表示されるデータの範囲がどのように指定されるかを考えましょう。

チャートに表示する範囲は、例えば4本値で考えると、下図の様に一定数のデータを表示しますが、データ数が増えると、この範囲も移動しなくてはなりません。

そのために、どの範囲をチャートに表示する範囲にするかの定義が必要となります。

範囲の指定は、データ数(チャートに表示する足の数)と、データの開始位置が必要で、例えば、100本の足をデータ数とすると、データの開始位置は最終行から100本前となります。

入力されているデータの総数が100以下の場合は、1行目~100行目までがチャートに表示する範囲です。

ということは、入力されているデータの総数が100以上の場合と100未満の場合で条件分岐を行い、それによって表示開始位置を計算するという処理が必要になります。

この処理は、VBAを使用しなくてもエクセルの関数にて対応可能です。

 

 

 

「名前の定義」でチャートの表示範囲を可変にする

 

 

まず、データの開始位置と表示本数を指定する場所を確保しましょう。

下図の赤丸部分の、「行数」と「移行タイミング」の下のセルに、データ数と開始位置と表題をつけ、場所を確保します。

データ数はチャートに表示するロウソク足の本数で、とりあえず100にしておきましょう。

開始位置は、データの最終行から、表示するロウソク足の本数分マイナスした、エクセルの行番号となりますが、データの総数が100よりも少ない場合は、データ入力部分の最初の行(エクセルの6行目)となりますので、数値は6となります。

この、開始位置は、その上にある「行数」から算出することが出来ます。

行数は現在入力を行っている行数ですので、その値から一番最初の行番号(6行目)6を引いて、答えが100以上なら、入力されているデータの総数が100本より多いということですので、「行数」引く100が開始位置となります。

「行数」から6を引いて答えが100未満の場合は、入力されているデータの本数が100本に満たないということですので、開始位置はデータ入力の最初の行である6が入ります。

そこで、エクセルのワークシート関数であるIFを使用してセルに直接、計算式を入力します。
上の条件ですと、下記の様になります。

= IF ( 「行数」- 6 >= 100 , 「行数」- 100 , 6)

この式をエクセルのセルにあわせ、「行数」はセルE2に入力されていますので、上記式の「行数」をE2に変更し、

= IF ( E2 – 6 >= 100 , E2 – 100 , 6 )

となります。

更に、100というのはデータ数ですので、データ数が入力されているF3を使用して、上記式の100をF3に置き換えます。

= IF (E2 – 6 >= F3 , E2 – F3 , 6)

これで、チャートに表示するデータの開始位置が計算されます。

この状態ですと、表示するデータの本数とデータの開始位置がセルに入力されただけですので、表示する範囲を指定しなくてはなりません。

範囲の指定は、「名前の定義」を使用して、例えば始値の範囲ですと、OPENなどの名前で、「B列」の「開始位置」から「データ数」の分、下に行ったところが範囲となります。(次図)

この範囲の指定には、OFFSETを使用します。

OFFSETは「基準のセル」から、「範囲開始位置まで行数」、「範囲開始位置までの列数」、「範囲の高さ」、「範囲の幅」を指定して、ある一定の範囲を設定することが出来る関数です。

これを使用して「名前の定義」を始値~終値の4本値と、日時、移動平均線1~2、買サイン、売サインのチャート表示範囲を指定します。

 

 

指定の方法は、まず通常のエクセル関数同様に「=OFFSET()」の中に引数を入れていきます。

引数は、基準、行のオフセット数、列のオフセット数、高さ、幅となります。

始値の場合は、まず基準のセルはB5の「始値」と入力のあるセルになります。

行のオフセット数ですが、データの始まりの位置となりますので、「開始位置」の値を使用します。データ数が100以下(設定されているデータ数以下)の場合は、開始位置は7行目の行番号1の行となりますので、この場所を指定します。

但し、この開始位置は可変にしなければなりませんので、「データ開始位置」の値を使用して、基準のセルからいくつオフセットするかを計算します。

開始位置は、基準のセルからひとつオフセットした場所になりますので、セルF4に入力されている「開始位置」から5を引いた値と同じ値となります。

そのため、OFFSETの中に入力する行数の値はセルF4-5となります。

 

 

次に、OFFSETの3番目の引数「列」については、列のオフセットの必要は無いため、0となり、4番目の引数、「高さ」はそのままデータ数となり、セルF3の値を参照させます。

最後の「幅」は、参照する範囲の列数は1なので、幅も1となり、セルF3、セルF4に入力されている「データ数」「開始位置」を使用した始値のチャートへの表示範囲は、

=OFFSET(B5,F4-5,0,F3,1)

となります。
そして、絶対参照にするために、式内の各アルファベットを“$”で囲み

=OFFSET($B$5,$F$4-5,0,$F$3,1)

とします。

これを使用して、チャートに表示する内容は、始値、高値、安値、終値、日付、移動平均線1、移動平均線2、買いサイン、売りサインの9個のデータとなりますので、名前の定義に追加する内容も上記9個を設定します。

では、名前の定義を設定していきます。

 

 

 

まず、エクセルの上部メニューの「挿入」より、「名前」、「定義」を開きます。

名前の定義は下記のようなウィンドウになります。

このウィンドウの一番上の「名前」の欄に範囲指定の名前を、一番したの「参照範囲」の欄に、参照する範囲の計算式を記入します。

例えば、始値の場合は、名前を「OPEN」とし、参照範囲は、先述の通り、

=OFFSET($B$5,$F$4-5,0,$F$3,1)

です。

これは始値の場合ですが、高値、安値・・・も基準のセルが変わるだけで行のオフセット数や高さ、幅などは変わりません。

そのため、B5がC5、D5、と横にずれていくだけとなります。

名前の定義の設定は、名前を入力し、参照範囲に計算式を入力し、最後に「追加」をクリックします。

始値の場合は、「名前」の部分に「OPEN」を入力し、参照範囲に
「=OFFSET($B$5,$F$4-5,0,$F$3,1)」を入力し、「追加」をクリックします。

すると、下図の様に一覧にOPENが追加されます。

これを必要なデータ数分繰り返し、名前の定義の設定を行います。

それぞれの名前の定義は下記の通りです。

設定データ 名前 参照範囲
始値 OPEN =OFFSET($B$5,$F$4-5,0,$F$3,1)
高値 HIGH =OFFSET($C$5,$F$4-5,0,$F$3,1)
安値 LOW =OFFSET($D$5,$F$4-5,0,$F$3,1)
終値 CLOSE =OFFSET($E$5,$F$4-5,0,$F$3,1)
日時 TIME =OFFSET($F$5,$F$4-5,0,$F$3,1)
移動平均1 MA1 =OFFSET($G$5,$F$4-5,0,$F$3,1)
移動平均2 MA2 =OFFSET($H$5,$F$4-5,0,$F$3,1)
買サイン BUY =OFFSET($I$5,$F$4-5,0,$F$3,1)
売サイン SALE =OFFSET($J$5,$F$4-5,0,$F$3,1)

名前の定義の設定が完了したら、次はチャートの表示に移ります。

 

 

 

自動売買システムの作り方トップページへ

次のページ:定義した名前を使用して範囲可変のチャートを表示