Excel～データ取得と整理

データを別表から転記する１

データベースから条件に合うデータの検索方法。

■VLOOKUP(検索値,範囲,列番号,検索の型): 検索値に指定した値を範囲に指定した表の左端で探し、該当する行の列番号で指定した列からデータを取出す。検索の型は完全一致の場合FALSE、近似値はTRUEとなる。
■HLOOKUP(検索値,範囲,行番号,検索の型): 検索値に指定した値を範囲に指定した表の上端で探し、該当する列の行番号で指定した行からデータを取出す。検索の型は完全一致の場合FALSE、近似値はTRUEとなる。
■INDIRECT(参照文字列): 文字列として入力した範囲名を計算式で参照できる形に変換する。

　元データは4502､5401､7203､8306､9501の時系列データです。あらかじめそれぞれのシートに前日比、騰落率等の必要なデータは入力しておきます。シートの記入例（図1-6

　別々のシートになっている時系列データから目的の値を取り出します。銘柄名、コードは直接入力するかWebクエリで取得します。
ここではD6のセルにVLOOKUP関数を使い特定の日付の株価を表示してます。検索の型は完全一致なのでFALSEとします。これでC3のセルに日付を入力するとD6セルにその日の株価が表示されます。

　しかしこのままでは下のセルにコピーしても銘柄ごとの株価は得られません。そこでINDIRECT関数を使い検索範囲を文字列から参照できるようにします。これによりコードに応じた値が表示されるようになります。検索範囲は【'4502'!$A$4:$I$94】です。この中で【4502】以外は固定なのでこのまま文字列として使い、銘柄コードをB列から参照するようにします。文字列にする【'】と【'!$A$4:$I$94】の前後に【"】をつけ【&】を使い参照セルと文字列を結合します。

　INDIRECT関数を使うとD6セルの数式は、【=VLOOKUP($C$3,INDIRECT("'"&$B6&"'!$A$4:$I$94"),6,FALSE)】となるのでこれを下のセルにもコピーすれば完了です。

　INDIRECTの利用方法はいろいろとあります。図1-9では任意の銘柄との2銘柄間の相関係数を求めています。
I3セルに銘柄コード、I5セルに算出期間をいれると2銘柄の相関係数が求めれるようにしています。ただしこの場合、基準日は任意の日ではなく時系列データ各シートのH4が基準となっています。
I6セルの数式は、【=CORREL(INDIRECT("'"&$I$3&"'!$H$4:$H$"&3+$I$5),INDIRECT("'"&$B6&"'!$H$4:$H$"&3+$I$5))】となります。
相関係数についてはポートフォリオ理論相関係数で詳しく説明します。

Excelに戻る

データを別表から転記する２

VLOOKUP関数を使った検索方法の改良とその他の検索方法。

■VLOOKUP(検索値,範囲,列番号,検索の型): 検索値に指定した値を範囲に指定した表の左端で探し、該当する行の列番号で指定した列からデータを取り出す。検索の型は完全一致の場合FALSE、近似値はTRUEとなる。
■COLUMN(範囲): 指定したセルの列番号を調べる。引数を省略すると、式を入力したセルの列番号を求める。
■ROW(範囲): 指定したセルの行番号を調べる。引数を省略すると、式を入力したセルの行番号を求める。
■INDEX(範囲,行番号,列番号): 範囲の中から、指定した位置にあるデータを取出します。
■MATCH(検査値,検査範囲,照合の型): 検査値を検査範囲内で探し何番目に位置するかを数値で示します。照合の型は検査値と完全一致とする場合0とします。1とすると検査値以下で最も近い値。-1とすると検査値以上で最も近い値となります。】
■DGET(データベースの範囲,フィールド,検索条件範囲): データベースの中から検索条件にあったフィールドの値を取出します】

　作成した一覧表から入力したコードに応じた値を検索してみます。一覧表のデータが多くなった場合など応用次第ではいろいろな使い道があります。検索の方法は、いくつかありますが図1-10はVLOOKUP関数を使ったものです。ただし、2つほど工夫しています。
　1.参照範囲B6:I10に【一覧】と名前をつけています。参照範囲に名前を定義することで参照の整理やINDIRECT関数が使いやすくなります。
　2.列番号に1行目のセルを参照しています。1行目のセルにはCOLUMN関数を使いVLOOKUP関数で使用する列番号が表示されるようにしています。これにより参照範囲に後から列を挿入しても求める列がずれることがなくなります。また、行番号を表示する場合はROW関数を使用します。（図1-11
データを整理していくと列や行を挿入することは度々あるので覚えておくと便利です。

　次にINDEX関数を使ったものです。VLOOKUP関数と違うところは検査範囲の中から行番号と列番号で検査値を調べるので検索値を先頭にしておく必要がないところです。 INDEX関数は表のデータを縦横に調べることができるのでVLOOKUP関数より使える条件は広がります。(図1-12
さらに行番号と列番号を調べるにはMATCH関数を使用します。MATCH関数は指定した項目の行や列数を調べることができるので前述のCOLUMN関数やROW関数よりさらに使い勝手がよくなります。

　最後はDGET関数を使ったものです。検索範囲はラベルを含めたデータ全体となります。検索するフィールドは検索範囲のラベルを指定し、検索条件はラベルとその検索値となります。 INDEX関数とほぼ同じことができますがラベルに数値を使うことができません。このためG14の20日移動平均線の値はエラーになっています。(図1-13

Excelに戻る　　次のページへ

このページのトップへ