毎月の実績を入力していく表って、まだ数字が入っていない未来の月の分まで棒グラフに反映されちゃうの、地味にストレスじゃないですか？
この空白部分は表示せずに、
・4月だけ入力→棒グラフは1本だけ
・5月まで入力→棒グラフは自動で2本に
・6月まで入力→3本に…
というように、入力した分だけグラフが自動で伸びていくようにしたい！って思ったことないですか？
これ、実は関数だけで解決できました。VBAもいりません。
さらに、「どの科目のグラフを見るか」もセルで切り替えられるようにしておくと、1つのグラフの型をいろんな科目で使い回せるようになります。
今回はその作り方を、そのまま応用できる形でまとめました。
＊もくじ＊
全体のしくみ
やることは以下の3ステップです。
- 元の表の「どの行（項目）をグラフにするか」をセルで指定する
- その行の中で「実際に数字が入っているのは何セルまでか」を数える
- その範囲だけをグラフのSERIES関数に渡す
これを「名前の定義」を使って数式化しておくことで、グラフ側は特別な設定をしなくても自動で追従してくれます。
前提となる表の形
今回は以下のような表を想定しています。
- A列の4行目から項目名が縦に並んでいる
- 横に月が並んでいる
- どの項目も入力されている月数は揃っているという前提
- 縦棒グラフ
これで数字を入れた月の分だけ棒が増える仕組みをつくります
STEP1：見たい科目をセルで指定する
まず、グラフの元データがある表とは別に、「どの科目のグラフを表示するか」を入力するセルを1つ用意します（例：S3セル）。
ここは直接入力でもいいですし、入力ミス防止のためにデータの入力規則でリスト選択にしてもOKです。
STEP2：選んだ科目が表の何行目にあるかを調べる
S3セルの隣（例：T3セル）に、以下の数式を入れます。
=MATCH(S3,$A$4:$A$250,0)
これは「S3に入力された科目名が、A4〜A250の中で何番目にあるか」を数字で返す数式です。A列の項目の数によって$A$250の部分は変更してください。
例えばA5セルに入力されている科目をS3で選んだ場合、A4セルを1番目として数えるので「2」が返ってきます。
このT3の数字を、次のOFFSET関数で「表の何行目を見にいくか」の基準として使います。
STEP3：「名前の定義」で可変の範囲を作る
ここが今回のキモです。Excelの「名前の定義」という機能を使って、2つの独自の数式（名前）を登録します。
登録場所：数式タブ→名前の定義（Ctrl+F3からでもOK）
なお、名前の定義には「範囲（スコープ）」の設定があります。同じブック内の別シートでも同じ名前を別の用途で使い回したい場合などは、スコープを「ブック全体」ではなく「このシートのみ」に設定しておくのがおすすめです（今回はシート単位で範囲を固定しています）。
名前①：選んだ科目の「数値範囲」を返す数式
名前の例：範囲あ
参照範囲：以下の数式を入れます
=OFFSET($A$3,$T$3,1,1,COUNT($B$4:$M$4))
これは何をしているかというと、
・$A$3を基準にして
・T3の数だけ下に移動する（＝選んだ科目の行に移動）
・右に1列ずらす（＝A列の科目名を飛ばしてB列の数値からスタート）
・高さは1行
・横の幅は「COUNT($B$4:$M$4)」＝4行目（例：売上高の行）で数値が入っているセルの数
つまり「選んだ科目の行のうち、B列から、実際に数字が入っている分だけ」を範囲として返しています。ここが自動で伸び縮みするポイントです。
名前②：選んだ科目の「名前（ラベル）」を返す数式
名前の例：列位置あ
参照範囲：以下の数式を入れます
=OFFSET($A$3,$T$3,0,1,1)
こちらは列をずらさずにそのままA列を参照するので、選んだ科目の「科目名セル」そのものを1つ返します。グラフの系列名（凡例）として使うためのものです。
STEP4：グラフのSERIES関数を書き換える
とりあえずA3:B4の範囲を選択して、作りたいグラフを挿入してみましょう。ここでは棒グラフとします。
出来上がったグラフの棒をクリックすると、数式バーに以下のような数式が表示されます。
=SERIES(Sheet4!$A$4,Sheet4!$B$3,Sheet4!$B$4,1)
これがグラフの元になっている数式（SERIES関数）です。この状態から、それぞれの引数をSTEP3で作った名前に置き換えていきます。
=SERIES(シート名!列位置あ,シート名!$B$3:$M$3,シート名!範囲あ,1)
それぞれの引数の意味は以下の通りです。
・1つ目（シート名!列位置あ）：グラフの系列名（凡例に出る名前）
・2つ目（シート名!$B$3:$M$3）：横軸のラベル（04、05、06…といった月の見出し。ここは固定でOK）
・3つ目（シート名!範囲あ）：実際にグラフに描画する数値の範囲（ここがSTEP3で作った可変範囲）
・4つ目（1）：系列の番号
この3つ目の引数が「範囲あ」という可変の名前に置き換わっていることで、入力されている月数だけ棒グラフが自動で表示される、という仕組みが完成します。
つまずきやすいポイント
- SERIES関数はセルに直接入力できません。グラフを選択→数式バーに表示されたSERIES関数を、直接書き換える形で編集します。
- 「名前の定義」はスコープの設定次第でブック全体にも各シート単位にもなります。同じ名前（範囲あ、列位置あなど）を他のシートでも別の意味で使い回す場合は、スコープを「このシートのみ」にしておくと競合を防げます。
- OFFSET関数は「揮発性関数」と呼ばれるタイプで、シートの再計算のたびに毎回計算し直されます。表が非常に大きい・シートが多いブックだと動作が重くなることがあります。
まとめ
- 「入力した月数だけグラフに反映したい」は、OFFSET＋COUNTの組み合わせで解決できる
- 「見たい科目を切り替えたい」は、MATCH関数で行位置を取得し、OFFSETの基準にすることで解決できる
- 「名前の定義」に数式を登録しておくと、グラフのSERIES関数から呼び出すだけで済むので管理がラク
- VBAを使わずに関数だけで完結するので、共有ブックなど「マクロを有効にできない環境」でも使えるのが地味に嬉しいポイント
ポチップ
