08
18
19
20
21
22
23
24
25
26
27
28
29
30
31
   

日経平均採用銘柄を時価総額順に並べる その11

キャプチャ
図1:日経平均採用銘柄一覧

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その10でみなし額面一覧から日経平均採用銘柄一覧を作成しました。


日経平均採用銘柄一覧と上場株式数の一覧が作成できましたので、2つの表から日経平均採用銘柄の上場株式数一覧表を作成します。

--みょ 225銘柄も数字を拾うのは大変だみょ

うん。手動ではやっていられません。ややこしいことはエクセルに任せます。今回は証券コードでデータを統合できます。エクセルでは「VLOOKUP」関数が使えます。

--みょ 聞いたことがあるみょ

うん。個人投資家には便利な関数です。書式は「VLOOKUP (検索キー, 検索する範囲, 戻り値の列番号, 完全一致か近似一致か)」となります。


ということで、日経平均採用銘柄一覧に上場株式数を追加します。日経平均採用銘柄一覧シートの「C1」セルに見出しとして「上場株式数」を入力します。

「C2」セルを選択して入力窓左の「fx」をクリックします。

「関数の挿入」ウィンドウで「関数の検索」窓に「VLOOKUP」と入力して「検索開始」ボタンをクリックして、「関数名」から「VLOOKUP」を選択して「OK」ボタンをクリックします。

キャプチャ
図2:関数の挿入

「関数の引数」ウィンドウで「検査値」を同じ行のコード「A2」、「範囲」は上場株式数シートの証券コードと上場株式数をすべて指定します。範囲窓を指定して上場株式シートの証券コード「B2」セルをクリックして「Ctrl」キーと「Shift」キーを押したまま右矢印「→」キーを押して続いて下矢印キー「↓」を押して証券コードと上場株式数をすべて選択して「F4」キーを押して絶対参照とします。

「列番号」は2列目の「2」、検索方法は完全一致を指定する「0」を入力して「OKボタンをクリックします。

キャプチャ
図3:関数の引数


計算式ができましたので、その12から表を仕上げていきます。

日経平均採用銘柄を時価総額順に並べる その10

キャプチャ
図1:日経平均プロフィル指数一覧日経平均株価>関連データ

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その9でみなし額面一覧をダウンロードすることとしました。


日経平均プロフィル指数一覧日経平均株価>関連データから「みなし額面一覧」をダウンロードします。

キャプチャ
図2:みなし額面一覧

--みょ ほしいのは日経平均の銘柄一覧だみょ みなし額面はいらないみょ


うん。いらない列はバッサリと削除して、日経平均採用銘柄一覧を作成します。

キャプチャ
図3:みなし額面一覧から不要行を削除

--みょ 日経平均採用銘柄の一覧ができたみょ

うん。列幅を自動調整して、先頭行を固定してみました。

この日経平均採用銘柄一覧を元に時価総額順に並べていきますので、時価総額のシートと一緒にして保存しておきます。

--みょ シートを一緒に保存?

うん。シートのタブ部分で右クリックして「移動またはコピー」を選択してシートを一つのブックにまとめた後に保存します。

キャプチャ
図4:移動またはコピー


日経平均銘柄一覧と、上場株式数一覧が作成できました。

その11から株価と合わせて時価総額を算出していきます。

日経平均採用銘柄を時価総額順に並べる その9

キャプチャ
図1:上場株式数のシート

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その8でPDFから上場株式数のエクセルシートを作成しました。


次に、日経平均採用銘柄のリストを作成します。

--みょ ダウンロードするみょ

うん。上手に一覧表がダウンロードできればいいのですが、一覧表そのものはなかなかダウンロードできません。

日経平均のことは日経の指数公式サイトである日経平均プロフィルで調べるのがよさそうです。

キャプチャ
図2:日経平均プロフィル

「指数一覧」ってのをクリックします。「指数一覧」から「日経平均株価」を選択します。

キャプチャ
図3:指数一覧

--みょ 「関連データ」ってところに「銘柄一覧」ってのがあるみょ

キャプチャ
図4:関連データ

うん。クリックしてみます。

キャプチャ
図5:銘柄一覧

--みょ またエクセルにコピペしていろいろややこしいことするんだみょ 面倒だみょ

う~ん。もう一度「関連データ」に戻ってよく見てみます。図4を見ると「銘柄一覧」にはダウンロードデータが書かれていません。他の項目にはPDFやCSVのダウンロードデータのリンクが用意されています。

そして、どうせダウンロードするのならCSVファイルが扱いやすい。CSVファイルが用意されているのは「日次データ」と「月次データ」と「みなし額面一覧」の3つです。

日次や月次は日経平均そのもののデータですので、採用銘柄はリストアップされていません。ということで、「みなし額面一覧」をダウンロードすることとします。


その10から「みなし額面一覧」のCSVファイルをダウンロードして日経平均採用銘柄一覧シートを作成していきます。

日経平均採用銘柄を時価総額順に並べる その8

キャプチャ
図1:証券コードで並べ替えたシート

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その7でPDFのテキストをエクセルにコピペして、「証券コード」順に並べ替えました。


不要な行を削除します。シートは証券コード順に並べましたので、証券コードが入っていない行は下のほうに固まっています。ということで、証券コードが入っている最終行の次の行からまとめて削除します。

「B1」セルをクリックして「Ctrl」キーを押したまま「↓」を押します。

個別銘柄最終行の次の行を選択して、「Ctrl」キーと「Shift」キーと「↓」キーを押して最終行まで選択して「Delete」を押して不要行をすべて削除します。

キャプチャ
図2:不要行の削除

不要な行が削除できました。


余分な列があっても問題なさそうですが、気持ち悪いので、不要な列も削除しておきます。

まずは空白の「C列」は余分ですので、列頭文字「C」を選択して右クリックで「削除」します。

キャプチャ
図3:C列の削除

お好みで「D列」以降も削除しておきます。


このままでもいいのですが、何のデータかわからななくなると困りますから、項目名を入力しておきます。

--みょ そんなうっかりさんはもきち♪だけだみょ

うん。でも、今日は覚えていても1か月後1年後には忘れちゃうかもしれません。やっぱり項目名は入力しておいたほうがよさそうです。

1行目で右クリックして「挿入」を選択して「A1」セルに「会社名」、「B1」セルに「証券コード」、「C1」セルに「上場株式数」を入力します。

キャプチャ
図4:項目名の入力


上場株式数のシートが完成しました。

その9から終値と組み合わせて時価総額を求めていきます。

日経平均採用銘柄を時価総額順に並べる その7

キャプチャ
図1:上場株式数の抽出

日経平均採用銘柄を時価総額順に並べる手順を確認しています。その6でPDFのテキストをエクセルにコピペして、「会社名」と「証券コード」と「上場株式数」を抽出しました。


PDFからコピペしていますので、余分な行がたくさん含まれています。もともとお手紙から抽出しているのです。

--みょ 余分な行いらないみょ

うん。でも、もともとPDFなので、本来は必要な説明なのです。でも、今回は使いません。バッサリと削除します。

まずは、証券コードが入力されている行と証券コードのない行とに分けます。


--みょ ややこしいのは嫌だみょ

うん。証券コードで並べ替えて、証券コードの有り無しも一気に解決します。

行頭文字の「1」の上、列頭文字の「A」の左をクリックしてシート全体を選択して、「データ」タブの「並べ替え」を選択します。

「並べ替えウィンドウ」で「最優先されるキー」を「B列」に指定して「OK」ボタンをクリックします。

キャプチャ
図2:並べ替えウィンドウ

証券コードで並び変えられました。

キャプチャ
図3:証券コードで並べ替えたシート


証券コードで並び変えられました。そして、証券コードが入っていない行はひとまとめにできました。

ということで、その8から不要なセルを削除していきます。

プロフィール

もきち♪

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

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

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

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

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

きほんゆるめに。。。

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


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

スピンオフサイト

ブログ内リンク

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

ブログ内検索

過去の記事

全ての記事一覧

カテゴリ

最新記事








使っている証券会社

スポンサードリンク

おすすめ



カンファレンスバナー

アフィリエイトフレンズ

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

広告