Excel(エクセル)関数逆引き グラフにデータが追加された場合自動追加する(実践編)。

Excelでデータをグラフ化すると、範囲指定をするため、最後の行のデータを追加するたびに範囲指定を変えなければ為らない。
そこで、データが追加されても自動で範囲指定を更新する関数を使用する。

Excel VBA小技集Eでグラフにデータが追加された場合自動追加することを記載したが、実践例を記載する。

最終的に以下のようなグラフを作成する。

日付をX軸、平均株価をY軸として、最後の行が追加される毎にグラフに自動追加するグラフを作成する。




まず、名前定義を行う。

Excelのツールバーの【挿入】→【名前】→【定義】で以下の画面を出して設定する。





名前定義
名前:日付
参照範囲:=$A$3:INDEX($A:$A,COUNTA($A:$A)+1)
       (空白行が1つあるため、COUNTA($A:$A)+1としている。)
名前:株価
参照範囲:=$B$3:INDEX($B:$B,COUNTA($B:$B))

INDEX関数+COUNTA関数で行の最後の値(B140というセルの位置)を検索する(詳細はこちらを参照)。

INDEX関数等、値が検索された場合は、セルの位置(例:B140)が返され、=セルの位置で値を表示しているようだ。
(Excel2000で確認。その他は?)

ということで、名前定義で$B$3:INDEX($B:$B,COUNTA($B:$B))とやれば、$B$3:B140と同等になり、最終行が定義できる。


次にグラフの【元データ】→【系列】で「値」や「項目軸ラベルに使用」でY軸、X軸に範囲を指定する。



項目軸ラベルに使用(X軸)に名前定義した「日付」、値(Y軸)に名前定義した「株価」を入れる。

値:
=株価データ自動収集.xls!株価

項目軸ラベルに使用
=株価データ自動収集.xls!日付


これで、A列、B列については値が更新されても自動的に行数を取得し、そこまでの範囲をグラフが指定してくれる。

名前定義した「=$A$2:INDEX($A:$A,COUNTA($A:$A))」をグラフの「値」等に入れたが、エラーが出てしまったので名前定義をしなければならない。INDIRECTを使用してもうまくいかなかった。

これで、最後の行が追加されるとX軸、Y軸が自動更新される。


PS.

名前定義方法だが、INDIRECTとCOUNTAを使用して、無理やりやる方法もうまく行った。





名前定義
名前:日付
参照範囲:=INDIRECT("まとめ!$A$3:$A$" & COUNTA(まとめ!$A:$A)+1)
       (空白行が1つあるため、COUNTA($A:$A)+1としている。)
名前:株価
参照範囲:=INDIRECT("まとめ!$B$3:$B$" & COUNTA(まとめ!$B:$B))

INDIRECTで文字列やセルの値をEXCELの数式にしている。

"まとめ!$B$3:$B$"とCOUNTA(まとめ!$B:$B)の「141」という値を「&」で連結して"まとめ!$B$3:$B$141"という文字列を作り、INDIRECTでEXCELの数式に変換している。

こっちの方が正攻法か。

しかし、グラフの「値」に上記の式を直接入力したら、また、エラーが出た。


PS.PS.

名前定義方法だが、今度はOFFSETとCOUNTAを使用する方法もうまく行った。



名前定義
名前:日付1
参照範囲:=OFFSET(まとめ!$A$3,0,0,COUNTA(まとめ!$A:$A)-1,1)
       (OFFSETで$A$3を基準にしており、A1の「日付」を無駄にカウントしているためCOUNTA($A:$A)-1としている。)
株価の方は省略。。

OFFSET関数は参照範囲を可変にできる関数。

$A$3を基準として、次の0,0で$A$3から0行目、0列目へセルを移動してそこを基準とし、COUNTA(まとめ!$A:$A)-1,1で基準からCOUNTA(まとめ!$A:$A)-1行分、1列分加えた分の参照範囲をもつことができる。

この場合、$A$3から0行、0列移動するため、基準は$A$3となり、COUNTA(まとめ!$A:$A)-1(行データの数139)と1(列データの数1)より参照範囲はA141までとなる。

しかし、これもグラフの「値」に上記の式を直接入力したら、また、エラーが出た。

名前定義はグラフ数が多くなると面倒なので、直接入力できるようにして欲しい。。

ただ、INDEXやINDIRECTでは1列限定で範囲指定しかできないのに対し、OFFSETを使用すると複数列の範囲指定ができる(最後の1を4とかにすると、参照範囲がA3:D141まで広がる)のでVLOOKUPとの相性がよさそう。

VLOOKUPで参照している範囲で、データの追加があった場合はOFFSETで自動で参照範囲の追加をしてやった方が良いだろう。

MATCH+INDEXの検索の場合は1列検索なので、INDEXをそのまま持ってこれば良いのだが。。

いろいろなやり方があるので、試してみよう!!


忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP