岡三RSS活用編動画 チャート系

岡三RSS活用編動画! 動画を見て岡三RSSを使いこなそう!

チャート系 岡三RSSでチャートを描画しよう! vol.3

岡三RSSを使ってエクセルにチャートを描く 難易度 初級クラス

日々の株価をもとにチャート用の株価や移動平均データを自動作成する~Offset関数を活用

このビデオの主なポイントとタイムライン

(TIME:1.00~14:10)
位置や範囲を指定するOffset関数の基礎知識
(TIME:14:10~30:50)
日々の株価データからチャート用のデータを自動作成する
(TIME:30.51~38:14)
何日の移動平均線でも瞬時に表示する方法

Vol.3のビデオでは、チャートで使用するもとになる日付や株価のデータ群を作っていきます。その用例として、エクセルシート内に並んでいるデータの順番を自動的にひっくり返す(降順・昇順の自動入れ替え)、移動平均線のデータを自動で表示する方法を解説します。

データ表示を自動化する際に便利なのが、「Offset関数」です。まずはOffset関数の基礎知識から解説します。

位置や範囲を指定するOffset関数の基礎知識(TIME:1.00~14:10)

セルの位置を指定して表示するOffset関数の基本(TIME:1.00)

Offset関数は、検索関数の一種です。エクセルのシート内で指定したセルに入っているものを引っ張ってきて、表示する関数と言えます。

具体的には、あるセルを基準として、そこから下にn個、右にi個の位置にある内容をOffset関数が書かれているセルに引っ張ってきて表示します。その際のOffset関数の書き方は以下のとおりです。

=OFFSET(基準のセル,n,i)

基準のセルをもとに、nが下方向、iが右方向にいくつめのセルかを示しています。なお、Vol.3ビデオのエクセルシートには、各セルに次の文字が書かれています。

  A列 B列 C列
行1      
行2
行3 ねこ いぬ うさぎ
行4 ぱんだ くま しろくま

B3セル(いぬ)を基準としたOffset関数は以下のようになります。

=OFFSET(B3,n,i)

さらに、「=OFFSET(B3,1,0)」ならB3から下に1、右に0を意味しており、その場所には「くま」があります。すると、「=OFFSET(B3,1,0)」という関数が書かれたセルには「くま」と表示されます。

次に「しろくま」を表示したい場合、「くま」の位置からひとつ右にありますから、「=OFFSET(B3,1,1)」と書きます。たとえば、この「=OFFSET(B3,1,1)」をA6のセルに書き込めば、A6のセルには「しろくま」と表示されます。

上下左右をを逆にしたい場合は、「-(マイナス)」を使います。たとえば、「=OFFSET(B3,-1,0)」ならば、「下に-1、右に0」となりますが、これは「上に1、右に0」と同じ意味です。上記の表の場合は「いぬ」のひとつ上にある「蛇」が表示されます。

では、練習問題として、基準を変えてみましょう。「ねこ(A3)」を基準として、「上に1、右に0」の場所にある「龍」を表示したい場合は、Offset関数をどう書けばいいでしょうか……。

答えは「=OFFSET(A3,-1,0)」となります。そして、「=OFFSET(A3,-1,1)」なら「蛇」が、「=OFFSET(A3,-1,2)」なら「鬼」が表示されます。

Offset関数で指定した範囲を合計する(TIME:7.53)

これまでひとつのセルを指定するOffset関数について説明しましたが、Offset関数は範囲の指定も可能です。セル範囲の基準を最初の3つのパラメーターで決めます。そこからはじめて下に何行分、右に何列分。そのかけ算を行った範囲が指定されます。

このときの基準が左の上の端の位置をこの3つで決めた。その基準から下方向何行、右方向何列の範囲のものを一括処理するよということができます。

基準の横、縦の範囲を示すパラメータをふたつ追加します。

=OFFSET(基準のセル,n,i,r,c)

rとcのパラメータが2つ増えました。「前3つで決めたパラメータを新基準として、そこから下にr行、右にc列の範囲を指定する」という意味です。指定する内容は「=OFFSET(参照,行数,列数,高さ,幅)」となります。

Offset関数でパラメータが3つならば、あるひとつのセルを示し、5つならば範囲を示すことになります。

  A列 B列 C列
行1      
行2
行3 ねこ いぬ うさぎ
行4 ぱんだ くま しろくま

最初の基準をA1にして、新基準をB1の「蛇」の位置とすると、下に1、右に1が蛇の位置で新たな基準です。

=OFFSET(A1,1,1,r,c)

ここまでは今までの話と同様です。「蛇」を(新)基準として下に3行(くま)、右に2列(しろくま)を指定します。すると「蛇」「鬼」「いぬ」「うさぎ」「くま」「しろくま」が入力された6つの範囲(B2~C4)を指定したことになります。この時のOffset関数が以下のとおりです。

「=OFFSET(A1,1,1,3,2)」

B2:C4の範囲を指定した意味になります。通常、範囲を指定する場合、「=AVERAGE()」(平均)や、「=SUM()」(合計)などの関数とよく一緒に使われます。

そこで、表の中の文字列を以下のとおりに数字に置き換えてみました。

  A列 B列 C列
行1      
行2 13日 2 3
行3 14日 3 2
行4 15日 1 4

Offset関数は以前と同じ「=OFFSET(A1,1,1,3,2)」だとします。さらに外側に合計を示すSUMを加えます。

=SUM(OFFSET(A1,1,1,3,2))

Offsetと指定している範囲はB2セルからC4セルですから上記の内容は以下と同義です。

=SUM(B2:C4)

上記の内容を、各セルに入っている数値の足し算として、すべて書き出すと……

=SUM(2,3,3,2,1,4)

となり、答えは「15」です。このように合計や平均、標準偏差などの関数の内側にOffset関数を書いて範囲を指定するといっ使い方が可能です。

日々の株価データからチャート用のデータを自動作成する(TIME:14:10~30:50)

データの降順→昇順の操作を自動化する(TIME:14.10)

Offset関数で日経平均株価の株価データベースが並んでいる順番をひっくり返す作業から具体的に行ってみます。降順のデータを昇順に並べ替える作業がOffset関数ならば簡単にできます。

ビデオ画面上には、エクセルシート上に2011年の日経平均の終値の時系列データが表示されています。今、大納会12月30日のデータがいちばん上、いちばん下が大発会の1月4日です。データは「降順」(いちばん新しい日付がいちばん上に、古い日付がいちばん下に、)並んでいます。

一般的にこの順番をひっくり返したい時には、エクセルのツールバーにある「昇順」「降順」ボタンを利用します。日付とデータの範囲を指定してから、「昇順」ボタンをクリックすれば、左側のセルの数値が小さい(日付が古い)順番に並び変わります。この操作を関数を使って自動化しようというのが今回の趣旨です。

今、日付のデータがB4から下に入力されており、B3は空欄となっています。これまで覚えてきたOffset関数では、データが書いてあるひとつ上のところを基点としてきました。そこで、今回もB3を基点とます。

日付の範囲をすべて指定すると、「245R×1C」という表示が現れ、データの個数が245個とわかります。降順と昇順を入れ替えるために、いちばん古いデータをいちばん上の行に持ってきます。基点から数えていちばん古いデータは245番目ですから、E4セルに245と入力しておきます。そして、F4セルにOffset関数を以下のように入力します。

=OFFSET($B$3,E4,0)

上記の関数の意味は「基点をB3セルとして、下に245、右に0」という意味です。なお、入力時は、B3やE4のセルをマウスでクリックすれば、各セルが入力されます。

なお、B3セルには「$」(絶対参照)記号がついています。セル指定後にF4キーを押すことで「$」自動的入力されます。絶対参照は「表示位置が変更されても、指定したセルを固定して参照する」という意味です。この場合、「常にB3セルを基点とする」という意味になります。

入力を終えると、「40547」という数値が現れました。これは日付を表すシリアル番号(1900年1月1日を1として、そこから数えた番号)なので、F列の書式を日付に変更します。

[F]をクリックしF列を選択-選択された範囲内で右クリック-[セルの書式設定]-[日付]-[種類]でいちばん上の形式を選び[OK]

これで、シリアル番号が日付(2011/1/4)に変わります。

次にE4セルのひとつ下、E5セルに244と入力しF4セルをコピーして、F5セルに貼り付けると、(2011/1/5)と、日付も1日新しく表示されます。

次にF4(日付)の右隣(G4)に日経平均株価を入力する場合、B3セルを基点にして、下に245個、右に1個の値を表示すればいいわけです。

そこで、いったんF4のOffset関数をコピーしてG4に貼り付けますが、そのままコピーすると、E列(連番)の位置がF列にひとつずれてしまうので、連番の列を固定する意味で、Eの前に「$」(絶対参照)を入れます(下記のOffset関数)。その後でG4にコピーします。

なお、Eの前に「$」を入れる場合、直接入力してもいいですが、Eの前にカーソルを合わせて、F4キーを何度か押すと、「$E4」に変わります。

=OFFSET($B$3,$E4,0)

G4にコピーした時点では、書式が日付になっているため、以下の手順で、書式を日経平均株価を表示するように変更します。

[G]をクリックしG列を選択-選択された範囲内で右クリック-[セルの書式設定]-[分類]欄で[数値]選択-[小数点以下の桁数]を[2]に設定、[桁区切り]にチェックを入れて[OK]

最後にOffset関数の3つ目の桁を0から1に変更します。これでG4セルに「2011/1/4」の日経平均株価が表示されます。

=OFFSET($B$3,$E4,1)

実際にG4セルに表示された数値が合っているか、左側のいちばん下に移動して、確認してみましょう。

G4セルをG5にコピーし、あとは、これまで入力した式をいちばん下の行までコピーします。その前に、E5のセルは先ほど244と手入力していました。ここは数字がひとつずつ減っていくように、以下のように、式を入れておきます。

=E4-1

これで、E5、F5、G5セルを3つ選択してコピーし、次にE6からデータが入っているいちばん下のセル(E248)までドラッグして範囲選択し、貼り付けます。

チャートで表示する日数を固定する(TIME:22.54)

チャートで表示する日数を決めたいと思いますが、その前に、管理データ(左側のデータ群)データ数を確認しておきましょう。

データ数が知りたい時は、Count関数を使い、B2セルに表示させます。今データが入っている範囲だけでなく、これからデータが入ってきそうな範囲まで、マウスをドラッグして範囲指定します。たとえば、1000データを入れたいならば、範囲のいちばん下を「B1003」と手入力してもいいと思います。

=COUNT(B4:B1003)

と入力すると、現在、範囲内のデータの数が245あると自動的に表示されます。

チャートを作るためのデータですから、チャートの範囲を常に180日分にするならば、「E4」セルに「180」と入力します。すると今日から遡って180営業日前をスタートとしたデータが表示されます。

そして、左側の降順のデータを毎日書き加えた時に、右側の昇順のデータは1日新しいものからスタートすることになります。

ただし、こうすると180個以降のデータは、連番でマイナスになります。そこで、連番が「1」よりも大きい時だけ、計算をする設定にします。そこで0の位置(セルF184)にカーソルを合わせます。

=OFFSET($B$3,$E184,0)

現在、上記のような関数が入っていますが、以下のように書き換えます。

=IF($E184>0,OFFSET($B$3,E184,0),NA())

上記の式は意味は、IF関数で、もしもE列の数値が0よりも大きければ、Offset関数による計算を行う。それ以外の場合は、「NA」(何も入れない、という答えを出す)として、かっこを閉じます。

すると、F184セルは、「#N/A」と表示されます。入力した計算式をG184セルにコピーしますが、その際に、日付欄は日付の書式になっていますので、[形式を選択して貼り付け]から[数式]にチェックして[OK]ボタンをクリックして貼り付けます。

そして、Offset関数の中を右に1つずらす意味で[0]→[1]に変更します。G184セルの関数は以下のようになります。

=IF($E184>0,OFFSET($B$3,$E184,1),NA())

表示は、やはり「#N/A」となります。これでF184とG184のセルをマウスドラッグでコピーし、F4の位置から日付とデータが入った全範囲を指定して貼り付けます。

改めて連番が0以下のセルを確認すると、すべて「#N/A」と表示されています。決まった長さのチャートを作る場合のデータは、これで一応完成です。

チャートの右側に余白を作る(TIME:27.34)

前のビデオでも紹介したとおり、チャートの右側に若干の余白を作っておくと何かと便利です。現在、日付の欄で「#N/A」と表示されている部分(連番が0以下)を選び、Offset関数内の「NA()」の部分を、ひとつ上のセルを意味する「(F184+1)」に書き換えます。

=IF($E184>0,OFFSET($B$3,$E184,0),F183+1)

こうすると、連番0以下の日付を延長して表示できます(ただし、連番0以下の場合は、土日祝日も入ります)。そして、上記のセルをコピーして、日付欄すべてを範囲指定して貼り付けます。これで、連番が0以下の箇所にも日付が入りました。

右側の余白をたとえば10営業日分にしてグラフを作りたい場合は、連番が「-9」の位置以上の日付とデータを範囲指定してからグラフを作成してみましょう。折れ線グラフを作ってみると、右側に余白ができているはずです。

ところで、連番が0以下のセルには、わざわざ「#N/A」と入れるよりも、「0」を入れた方が簡単にも思えます(29:30)。ところが、実際に「0」を入れてみると、折れ線グラフが0に向かって伸びてしまい、データなしの余白になりません。これを避けるために、「#N/A」を利用します。これは「データなし」の意味です。

何日の移動平均線でも瞬時に表示する方法(TIME:30.51~38:14)

Average関数を利用した移動平均線(TIME:30.51)

移動平均線を関数を使って自動入力します。先ほど作成した左側のデータ(降順のデータ)の右端に移動平均線を足していきます。そのスペースを作るために、D列、E列、F列の3列分を選択して、[挿入]-[列]で3列分空白のセルを挿入します。

D列に5日移動平均のデータを入れていきます。その際に平均を表すAverage関数を使います。「D4」のセルには5日分のデータを選択して次のように関数を入力します。

=AVERAGE(C4:C8)

ここでは、上記のようにセルを相対参照(セルの位置変化に応じて、参照するセルも変化する)するように範囲していします。

相対参照の意味は、Average関数を入力したD4セルを別のセル(たとえば、D11)にコピーしてみるとわかります。コピーされた関数は以下のようになります(セルの指定範囲が変化している)。関数が入力されたセルの左側の日付から5日分の平均値が計算されます。

=AVERAGE(C11:C15)

そこで、D4セルに入力したAverage関数を単純にいちばん下の行までコピーすれば5日移動平均線のデータができあがります。

日数を入力すればデータが自動で変わる移動平均線~Offset関数を活用(TIME:33.19)

ところが、単純にAverage関数を使った移動平均線では、移動平均線の日数を変えたいときに手間がかかります。その都度、関数入力やコピーをしなければなりません。

そこで、これまで学んできたOffset関数を利用して範囲(何日の移動平均線か)を変えられるしくみを作ります。

先ほどAverage関数を利用したD列をいったん消します。たとえば、3日移動平均線を作るために、D2セルに「3」と入力します。D4セルには以下のとおり入力します。

=AVERAGE(OFFSET(C4,0,0,D$2,1))

上記の関数では、頭に平均を示すAverage関数が書かれていますが、範囲指定でOffset関数を使います。Offset関数の意味は、基準となる位置がC4セル、行数、列数は「0」、そこから先が範囲指定になりますが、範囲の「高さ」がD2セルを選択(この場合は「3」)です。この時、参照位置(D2セルの)の2行目を変更したくないため「D$2」と絶対参照にしておきます。そして、範囲の幅が「1」です。

これでかっこを閉じることで、3日移動平均がひとつ完成しました。これならば何日の移動平均線を作るかで、毎回関数を入力する必要はありません。D2セルの数値だけ変更すればいいのです。試しにD2セルを変更して、移動平均の値(D4セル)が変化するか確認してみましょう。

D4セルをコピーして、いちばん下の行まで範囲指定して貼り付ければ、3日移動平均線のデータが完成します。

他の移動平均線も作ることを考えて、D4のOffset関数を少し書き換えます。参照位置をC列に固定する意味で、「C4」のCの前に絶対参照「$」を入れておきます。

=AVERAGE(OFFSET($C4,0,0,D$2,1))

この式(D4セル)をコピーして、DEF列の3列を範囲指定して、最終行までコピーします。この時点でE列、F列は「#REF!」と表示されています。これは、まだ2行目に高さ(何日の移動平均か)を示す数字が入っていないためです。そこで、D2セルに「5」(5日移動平均線)、E2セルに「25」(25日移動平均線)、F2セルに「75」(75日移動平均線)と入力すると、各列にそれぞれのデータが出現します。

このようにAverage関数とOffset関数を組み合わせると、範囲指定がフレキシブルになります。特にチャートで何日移動平均を使うか、何個のサンプルを使って標準偏差を計算するといったことが簡単にできるようになります。

その他、サンプルシートでは、データが75個以下の場合の関数も入っていますが、そこまで勉強したい方はサンプルシートを見て解析してもらいたいと思います。

目次

テーマ レベル 動画の内容  
岡三RSSでチャートを描画しよう!

サンプルシート

vol.1

岡三RSSを使ってエクセルシートでチャートを描く

  • エクセルでの株価データベース構築のコツ
  • 岡三RSSでの時系列データ取得の実際の解説
視聴
vol.2

エクセル2007や2003でのグラフ機能を使い株価チャートを作ってみる。

  • 移動平均付きローソク足を作る
視聴
vol.3 ☆~
☆☆

チャートづくりに便利な関数

  • 「OFFSET関数」の解説
  • 昇順降順の順番変え・移動平均の日数を可変に
視聴
vol.4-1 ボリンジャーバンドの解説 + 具体的な作り方 視聴
vol.4-2 一目均衡表の具体的な作り方 視聴
「RSS総合チャート管理」(解説)

サンプルシート

「サンプルシートダウンロード」解説 チャート・ポイント計算とチャート描画をあわせた総合シート

  • チャートの総合シートとチャートポイントを一覧で見ることが出来るエクセルシートのデザイン提案
    移動平均・ボリンジャーバンド・一目均衡表・平均足チャート
視聴
チャートポイント警告

サンプルシート

チャートポイント警告(先物用・個別株用)

  • 注文関数と連携も視野に 移動平均とボリンジャーバンドを例に
  • 「ゴールデンクロス・デッドクロス」警告 「ボリバンの指定σ到達」警告
視聴
「簡易マーケットプロファイル」(解説)

サンプルシート

「解説」マーケットプロファイルの簡単な説明と描画シートの使用方法解説

  • Type-A とType-B
視聴
簡易型ボリンジャーバンドバックテスト(解説)

サンプルシート

「セミナー」テクニカル分析に基づいてトレードをしたときの検証をエクセルを使って行う

  • どこまで検証できるのかの限界を最初に明確にする。
  • ボリンジャーバンドを使ったトレーディングの一例の解説
  • 累積損益の最大化 最大ドローダウンの最小化
  • 順張り/逆張り 強制ロスカット・スリッページ
視聴