10
23
24
25
26
27
28
29
30
31
   

価格帯別出来高をエクセルでグラフにする その5

キャプチャ
図1:日経平均日足6か月チャート+価格帯別出来高

エクセルで価格帯別出来高を作成する方法を確認しています。その4でSUMIFS関数を使って価格帯別出来高を集計することとしました。


今回使用する4本値と出来高のデータは例によって「A1」セルから250営業日分の日付と4本値と出来高を入力して、半年以前の行(2行目から130行目まで)を非表示にしてあります。

キャプチャ
図2:ダウンロードして加工した4本値と出来高

--みょ 非表示にせずに消しちゃえばいいんだみょ

う~ん。そうなのですが、移動平均や一目均衡表を計算したくなった時にそのまま計算できるほうが嬉しいのです。非表示にしておけば75MAとかの移動平均も一目均衡表も計算できます。

ということで、今回は131行目から251行目までのデータを利用します。


以上を踏まえて「S283」セルに価格帯別出来高集計式を入力します。使用するのはSUMIFS関数です。書式はSUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)です。

合計対象範囲は出来高が入力されている「F131」セルから「F251」セルまでです。この範囲を固定して指定しますので、範囲を指定する絶対参照「$F$131:$F$251」となります。

条件範囲は今回終値を利用します。条件1で区切りの下限、条件2で上限を指定します。こちらも「,$E$131:$E$251」と絶対参照で範囲を指定します。

条件1で下限以上、条件2で上限未満を指定します。


例では、「S283」セルをクリックして、入力窓左の「f(x)」をクリックします。

「関数の引数」ウィンドウで「合計対象範囲」に対象の出来高を選択して「F4」キーを押して絶対参照とします。条件範囲も同様に選択したら「F4」キーを押して絶対参照とします。

「条件1」には「">="&R283」、「条件2」には「"<"&R284」を入力します。

計算式は「=SUMIFS($F$131:$F$251,$E$131:$E$251,">="&R283,$E$131:$E$251,"<"&R284)」となります。例と同じセル位置ならコピペでも大丈夫です。

キャプチャ
図3:関数の引数ウィンドウ

入力した「S283」セルをコピーして、「S284」から「S303」まで貼り付けます。

キャプチャ
図4:価格帯別出来高の集計


数字がそろいましたので、その6からチャートを作成していきます。

価格帯別出来高をエクセルでグラフにする その4

キャプチャ
図1:価格帯別出来高集計表

エクセルで価格帯別出来高を作成する方法を確認しています。その3で価格帯別出来高集計表の枠組みを作成しました。


ここで、終値がどの範囲に入っているかを調べて、その終値の出来高を集計していけばいいのですが、一つ一つ手作業で集計するのは大変です。とてもやっていられません。

ということで、関数で範囲ごとの集計をしたいと思います。エクセルでは対象範囲の数値を特定の条件に一致するものだけを集計するSUMIFS関数が用意されています。

--みょ SUMIF関数ってのもあるみょ

うん。条件が一つの場合はSUMIF関数で、複数の条件を指定するときはSUMIFS関数です。今回は下限と上限を指定しますので、SUMIFS関数となります。


SUMIFS関数の書式はSUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)となります。

今回は、合計対象範囲は出来高で、条件範囲が終値となります。

条件は、区切り以上、一つ上の区切り未満とします。


ということで、その5から具体的に数式を入力していきます。

価格帯別出来高をエクセルでグラフにする その3

キャプチャ
図1:ローソク足

エクセルで価格帯別出来高を作成する方法を確認しています。その2で価格帯を見るためにローソク足チャートを作成しました。


--みょ ここから形を整えてお化粧していくみょ

う~ん。今回それは後回しにして、ここでは価格帯別出来高を集計する範囲と区切りを確認します。

ローソク足チャートを基準に範囲を決めるのです。

図1を見ると、21,000円と25,000円の範囲とするのがよさそうです。

チャートの縦軸は500円単位ですので、区切りが250円だとちょっと粗そうです。

逆に1目盛りを10に分割する50円単位だと細かすぎると思います。今回は100円か200円単位がよさそうです。


空いている場所に価格帯別出来高集計表を確保します。空いている場所ならどこでもいいのですが、例ではR282~S303を利用します。

R282セルに「価格」、S282セルに「出来高」と入力して価格の下R283セルに21000、その下のR284セルに200を足した21200を入力して、21000と21200をドラッグして選択して、21200右下をクリックして下にドラッグしていって、25000までオートフィルで埋めていきます。

キャプチャ
図2:集計表の作成

21000から25000を200刻みで入力できました。


価格帯別出来高を集計する範囲と区切りが設定できましたので、その4から価格帯別出来高の計算式を入力していきます。

価格帯別出来高をエクセルでグラフにする その2

キャプチャ
図1:日経平均日足6か月チャート+価格帯別出来高

エクセルで価格帯別出来高を作成する方法を確認しています。その1で4本値と出来高をダウンロードしました。


さっそく価格帯別出来高の計算と行きたいところですが、その1でも確認したように、どの範囲でどれだけの区切りで出来高を集計するかを決める必要があります。

--みょ 終値の最大値と最小値を求めていい塩梅で区切るんだみょ

うん。それも二度手間になりますので、とりあえずローソク足を作成してチャート全体の基本を決めてしまいましょう。

まずは全体の基本デザインなので、ざっくりとローソク足を作ってしまいます。

もきち♪はローソク足は6か月ぐらいが好きなので、日足6か月チャートを例に進めます。


6か月分の「日付」「始値」「高値」「安値」「終値」を選択します。

キャプチャ
図2:日付と4本値の選択

挿入タブの「おすすめグラフ」とかを選択して「グラフの挿入」ウィンドウで「すべてのグラフ」タブを選択して、「株価」からローソク足チャートのアイコン「株価チャート(始値ー高値ー安値ー終値)」を選択します。

キャプチャ
図3:グラフの挿入ウィンドウ

少し大きめにして、縦軸の数字で右クリックして、ローソク足を見ながら最小値と最大値を設定します。今回は最小値を21,000、最大値を25,000に設定しました。

キャプチャ
図2:縦軸の設定


最小値と最大値が設定できましたので、その3から価格帯別出来高を集計していきます。

価格帯別出来高をエクセルでグラフにする その1

キャプチャ
図1:日経平均日足6か月チャート+価格帯別出来高

ということで価格帯別出来高を作成する方法を確認します。


価格帯別出来高といっても、実際の価格を調べるにはティック足ごとの出来高を調べる必要があります。

--みょ ティック足のデータをダウンロードするみょ

うん。確かに、数日分ならティック足ごとの出来高をダウンロードすることも可能です。でも、6か月となると話は別です。膨大な数量になる。

それに、日経平均というのは直接取引されていません。ティック足というのは存在しないのです。

ということで、今回は便宜的に日足終値ごとの出来高で代用します。

--みょ 日足終値じゃぁ正確じゃないみょ

うん。正確ではありませんが、便宜的なものだとわかって活用すればいいのです。代用するとなると終値かVWAPということになります。今回は終値を使います。


ということで、日足終値と出来高をダウンロードします。今回はSBI証券のトレードツールHYPER SBIから日足4本値と出来高をダウンロードしました。

キャプチャ
図2:ダウンロードした4本値と出来高


価格帯別出来高だけではチャートとしてはそっけなくてあまり意味がありません。あくまでもローソク足と同時に表示するから意味が出てきます。

図1のようにローソク足チャートと価格帯別出来高を表示したいのです。そこで必要なことは、どの範囲でどれだけの区切りで出来高を集計するかということです。

ローソク足と同時に表示するのですから、ローソク足と同じ範囲となります。そして細かすぎず粗すぎずという区切りを設定します。

図1では21,000円から100円刻みで25,000円まで集計しました。200円刻みでもいいかもしれません。

キャプチャ
図3:図1の区切りを100円刻みから200円刻みに変更

--みょ 200円刻みのほうがいいみょ

うん。この区切りは後で変更が可能です。


その2からダウンロードした4本値と出来高から、価格帯別出来高を集計していきます。

プロフィール

もきち♪

Author:もきち♪
個人事業主ですが株式投資のほうが主体になっています。

投資スタイルは逆張りナンピン。チキンになりきれないひよこ投資家™でピヨピヨトレードです。

2007年に投資信託を始めて、2009年に国内株式の個別銘柄投資を始めました。

中小企業診断士(診断業務休止中)でオンライン情報処理技術者です。

ブログでは株式投資とコンピュータの話を中心に書いています。

きほんゆるめに。。。

【FISCOソーシャルレポーター】ってのに公認されました。


キャプチャ
もきち♪への直通メール

スピンオフサイト

ブログ内リンク

全ての記事を表示する
データをダウンロードできるサイト一覧  シーズン2
エクセルでチャートを描いてみよう

ブログ内検索

過去の記事

全ての記事一覧

カテゴリ

最新記事








使っている証券会社

スポンサードリンク

おすすめ



カンファレンスバナー

アフィリエイトフレンズ

クラウドソーシング「ランサーズ」

広告