今回は、グラフの横軸を自動的に追加表示する方法を書いていきます。
エクセルでグラフを作っていくときに、横軸はデータがあるとこまでの表示でいいんだよなーって思ったことはないでしょうか。
そんなときは「OFFSET関数」と「COUNTA関数」と「名前の定義」で解決できます。
OFFSET関数とは？
OFFSET関数とは、指定の範囲を返す機能を持っています。
日常的に使う関数のSUM関数やAVERAGE関数は、値を返す機能がありますが、OFFSET関数は範囲を返します。
=OFFSET ( 参照 , 行数 , 列数 , 高さ , 幅 )
参照 ：範囲指定の起点セルを決める基準点
行数 ：基準点からズラす行数 （縦方向）
列数 ：基準点からズラす列数 （横方向）
高さ ：指定したい範囲の高さ （縦方向）
幅 ：指定したい範囲の幅 （横方向）
文字だけではわかりづらいので、画像で説明します。
F3に『 =OFFSET(A1,2,1,8,3) 』と打ちました。
すると、F3にB3～D10までの範囲をOFFSET関数で返されます。
基準点をA1として、範囲の起点となるセルを行数と列数で指定。
この場合は、2行ズラし、1列ズラした。
次に、範囲の高さと幅を指定。範囲の高さを8、幅を3とした。
「起点」と「範囲終」と書かれている以外の範囲内のセルは何も情報が入ってないので、０で表示されています。
F3に返された範囲は、指定した高さと幅の分の情報をもって、その範囲分のセルに表示されます。
この範囲内にあるセルは消そうとしても消えません。例えば、G5だけ消したくても消せない。
F3を消すとすべて消えます。
つまり、このF3にB3~D10の範囲の情報が入っているということです。
また、基準点＝起点にすることもできます。
その場合、ズラす行数・列数をともに0にします。
COUNTA関数とは？
COUNTA関数とは、範囲内にある”空白でないセルの数”を返します。
=COUNTA(範囲1,範囲2,…)
こちらも画像で説明します。
G2セルに、『 =COUNTA(A2:A9,C2:E9) 』と打ちました。
A2:A9 と C2:E9 の中にある空白でないセルの数は14なので、
G2に14という値が返されました。
グラフの横軸表示を可変にする
「OFFSET関数」と「COUNTA関数」と「名前の定義」を用いてグラフの横軸を可変にします。
まずは、「名前の定義」をする際に、数式を打ち込みます。
「名前の定義」をなぜするかは後々説明します。とりあえず進めてください。
「名前の定義」…「数式」タブ⇒「名前の定義」
そして名前を定義します。
自分が分かれば何でも良いです。
今回は分かりやすいように「横軸」にしました。
参照範囲の矢印を押すと、範囲を打ち込むように指示されます。
ここで、OFFSET関数を打ち込みます。またこの時に、COUNTA関数も使用します。
=OFFSET(A2,0,0,COUNT(B2:B11),1)
シートが複数ある場合は上のように[シート!]が入ります。
これは何をしているのかですが、、
”横軸のグラフに読み取る範囲”を”縦軸の数値が入力されている個数”で決めています。
わけわかめ！って人はコピペでおっけーです！
そして、グラフのデータソースを改めます。
[右クリック]⇒[データソースの選択]⇒[横軸ラベル編集]⇒[軸ラベルの範囲]
横軸の”軸ラベルの範囲”には、先ほど行った「名前の定義」で定義した名前を使用します。
=book1.xlsx!横軸
と書きました。
“book1.xlsx”はエクセルのファイル名が入ります。
“!”を挟んで、定義した”名前(横軸)”を入れます。
ここで、OFFSET関数が使えれば「名前の定義」は要らないのですが、エクセルの仕様で使えないので、「名前の定義」でOFFSET関数で指定した範囲を”名前（横軸）”に置き換えたのです。
以上で完了です。
コメント