グラフの参照範囲を自動的に変更する
下図のような表があります。
この表からグラフを作りました。
この手の表は、新しいデータが追記されるものです。で、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」を選択して、数式を次のように変更します。
お疲れ様でした。完成です。表に新しいデータを入力すると、グラフも自動的に変化します。
