機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > 機能と数式

グラフの参照範囲を自動的に変更する



下図のような表があります。



この表からグラフを作りました。



この手の表は、新しいデータが追記されるものです。で、10月のデータが追記されたら、グラフも更新しなければなりません。これを、自動的に更新されるようにしようというのが、今回のテーマです。もちろん、マクロを使わずに。






グラフの系列(ここではAとB)は、SERIES関数で定義されています。系列を選択すると、数式バーに表示されます。



SERIES関数の書式は次の通りです。



引数 必須/省略 指定内容
 name  省略可  凡例に表示される名前
 category_labels  省略可  項目軸に表示されるラベル
 values  必須  プロットされる値
 order  必須  系列のプロット順

今回の系列「A」は、次のように指定されています。



分かりにくいですから、シート名(Sheet1)と、絶対参照を示す「$」記号を消して考えましょう。



新しい10月のデータが追記されても、引数name(凡例に表示されるラベル)は変わりません。更新するのは、引数category_labels(項目軸に表示されるラベル)と、引数values(プロットされる値)です。このアドレスを、入力されているデータの個数に応じて、自動的に変化させます。

OFFSET関数を名前定義する


セル範囲を動的にとらえるには、OFFSET関数がポイントです。OFFSET関数については、下記のページで詳しく解説していますのでご覧ください。


まず、2番目の引数category_labelsからいきましょう。現在ここには「A2:A10」が指定されています。この「A2:A10」は、次のように考えられます。



「データの個数-1」とマイナス1しているのは、1行目にタイトル「月」が入力されているからです。これをOFFSET関数で表すと、次のようになります。



「OFFSET(A1, 1, 0」は「OFFSET(A2, 0, 0」でも同じです。どっちでもいいですが、ここでは「OFFSET(A1, 1, 0」でいきます。A列に入力されているデータの個数は、COUNTA関数で取得できます。引数に指定する範囲はA列全体です。したがって、



となります。

この数式を、名前として定義します。セルの参照先が変化しないように、アドレスは絶対参照にします。Excel 2007以降は[数式]タブ[定義された名前]グループの[名前の定義]ボタンをクリックします。表示される[新しい名前]ダイアログボックスの[名前]ボックスに「項目名」と入力します。[範囲]は、どちらでもいいんですが、ここではアクティブシート(たとえばSheet1)を選択します。[コメント]欄は、書きたい人は何でも好きなことを書いてください。[参照範囲]に、さっきの数式を次のように入力します。

=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)




Excel 2003までは、[挿入]-[名前]-[定義]をクリックして、表示される[名前の定義]ダイアログボックスに、次のように指定します。



[名前]ボックスで指定する名前は「Sheet1!項目名」のように、先頭に"シート名!"をつけてください

名前の定義ができたら、SERIES関数の引数を、この名前に書き換えます。どっちでもいいんですが、とりあえず系列「A」を選択してください。数式バーに表示されている数式の「$A$2:$A$10」を、上で定義した名前「項目名」に書き換えます。






正しく編集してEnterキーを押すと、系列「B」も数式が変わっているはずです。



これで、項目名の書き換えは終わりです。続いて、系列「A」と系列「B」の値が自動的に変化するようにします。といっても、考え方は一緒です。

系列「A」が参照しているセル範囲は「B2:B10」で、系列「B」が参照しているセル範囲は「C2:C10」です。これを、先と同じように、OFFSET関数とCOUNTA関数で表して、それぞれの式に名前を定義します。ここでは「系列A」「系列B」という名前にしましょうか。

【すでに定義した名前】
項目名:=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)

【これから定義する名前】
系列A:=OFFSET($B$1,1,0,COUNTA($B:$B)-1,1)
系列B:=OFFSET($C$1,1,0,COUNTA($C:$C)-1,1)





系列「A」の数式を次のように変更します。





同じように、系列「B」を選択して、数式を次のように変更します。






お疲れ様でした。完成です。表に新しいデータを入力すると、グラフも自動的に変化します。









このエントリーをはてなブックマークに追加