毎月の結果をexcelで集計するときに、1か月の日数が異なる場合に集計の元になるセルの範囲を可変で取りたい場合、どのように作業したらいいでしょうか。
30日ある月は31日の分の行を削除しますか？
翌月31日になったら、また31日の行を追加して作業しますか？
2月は28日までなので、29日以降の行を削除して作業しますか？
3月になったら再び3行追加しますか？
うるう年は29日まででですが、同様に削除して作業しますか？
と、その月の日数によって、参照したい範囲が変わります。それに合わせて行を削除していると、毎月同じことを作業しないといけないですよね。
もし削除し忘れたら、余計な計算をしたり、翌月に追加し忘れたら計算がおかしくなったりします。
なるべくなら自動で参照範囲を変更してくれたら。
毎月の作業量も減らせます。
実は私自身、毎月の日数によって参照範囲を可変にしたかったのですが、これを分かりやすくしてくれているものがなくて、苦労しました。
ようやく自在に参照範囲を変更することができたので、忘備録も兼ねてまとめます。
Excelで実現したいのは、月の日数によって計算する範囲の参照を可変にすること
冒頭でも簡単に書きましたが、定型業務を行っているときに作業内容は同じだけど、日数が違う場合に計算する範囲を変更するのが、今回の目的です。
計算と書いていますが、実際にやろうとしている作業は、参照範囲をグラフ化し、毎月の日数によって表示している範囲を変える方法です。
もし、何の加工もしていない場合の結果の状態が以下の画像です。
こちらの画像は横軸が日数になっています。
2月分のデータとしてグラフを作っているのですが、横軸は31日までです。
できれば、こういうのは避けたいですよね。
もちろんそれでもいいよという人は、そのままで。
でも、2月28日までしかないのに、グラフの横軸は31日まで。しかもグラフ終端がなんとなく切れっぱなしで、見映えがよくないなとなると気になります。
データの元になっている部分は以下の画像です。
こちらも枠は31日までありますが、参照しているデータは2月なので、28日分まで。残りの参照は関数の関係で０で表記されている状態です。
これを例えば参照範囲を毎回変えれば、日付も変動しますが、参照範囲の変更を毎月やるのは大変です。
また、例として１つだけ画面を出していますが、実はこのグラフは同じシートが何十枚もあります。
もちろん、Excelシートを複数選択して一度に変更すればいいですが、これに限らず、ファイルが複数あるので、作業量はファイル分必要になります。
この作業、考えたらとても気が遠くなりますし、また1つ選択し忘れただけでも、同じものが出来上がらないことになりますので、なるべくであれば毎月作業したくはありません。
OFFSET関数は参照範囲を可変にする便利な関数
入力されているデータの数によって、参照範囲を可変にしたいという場合には、OFFSET関数を使います。
これを使うことにより、30日、31日がある月、2月でも28日まで、うるう年には29日とすべての範囲をカバーすることができます。
書式 ：OFFSET(基準,行数,列数,[高さ],[幅])
機能 ：基準セルを指定した数だけ移動したセル範囲を返します
OFFSET関数を利用して、グラフデータの元になるセルの参照範囲を可変にするには
実はOFFSET関数をどうやって使うのかは、セル内に単独に入れても、ほとんど意味がありません。
使い方としては、何かと組み合わせるという方法になります。
グラフの参照範囲としてOFFSET関数を用いる場合には、グラフ対象のセルや、別のセルには入力しません。
グラフとして参照したい範囲に名前をつけ、その中でOFFSET関数を使用します。
ここで使うのが「名前の管理」です。
名前の管理は、セル、セル範囲、数式、または定数値に「名前」を付けることができます。
「名前の管理」画面は、「数式」タブから、名前の管理をクリックして表示します。
この「名前の管理」画面上で、グラフの元になる範囲に名前を付け、その範囲の中で可変参照させるという方法を取ります。
「名前の管理」画面から、新たに作る場合には新規作成をクリックします。
「新しい名前」に参照範囲につけたい個別の名前を任意で入れます。（ここではgraph_rangeとしました）
「範囲」には式を適用したいシートを指定します。（ブックとすると式がブック内全てに適用されます）
今回は、参照したい範囲はシート単位で合わせるので、シートを指定します。（適用したいシート名を選びます）
「参照範囲」に任意でつけた名前をセルの範囲としてどこまで適用するかを指定します。ここにOFFSET関数を入れることで、参照範囲と名前を紐づけることになります。
実際に入力している関数の式は以下のものです。
=OFFSET(‘1’!$BD$7,0,0,COUNTA(‘1’!$BD$7:$BD$37)-COUNTBLANK(‘1’!$BD$7:$BD$37),1)
この式だけ見ると、何をしているのかわかりづらいですよね。
- やっていることとしては、
シート１のセルBD7を基準にし（グラフの元になるデータの先頭） - 行数０（基準から移動しない）
- 列数０（基準から移動しない）
- 範囲の行数を関数を用いて計算する（ここではCOUNTAで空白ではないセルの個数を数えて、空白のセル個数を数えた結果のセル数を求める）
- 範囲の列数を確定する（ここでは基準地点からずれずに1列の幅を取る）
というやり方で、基準点からまっすぐ下までを参照範囲として、空白の入っていないセルの分だけ参照範囲を可変にするという方法を取っています。
参照しているセルに入っている値で可変にするので、参照値によっては空白ではなく０が入っている場合もあるかもしれません。
ここは条件が異なるので、０の個数を数えるといった方法で参照範囲が可変になるように調整してください。
今回の場合は空白セルを抜く方法を取りました。
グラフ表記は名前管理だけでは足りないので、グラフの設定にも適用する
実は、グラフの元データにOFFSET関数を適用しても、実際に表記されるグラフと連動していくためには、さらにグラフそのものにこの可変参照を適用していきます。
適用するには、グラフを右クリックしてデータの選択を選びます。
この中にある「編集」ボタンをクリックします。
「系列の編集」画面が出てくるので、ここで「系列値」の欄にOFFSET関数で指定した範囲の名前を入力します。
今回の場合は、シート名!定義名になります。
左側は設定前、右が設定後です。
ここまで設定することで、グラフの値を可変にし、表示も可変化することができます。
実際に2月分の集計値のグラフを可変参照にすると、28日までのデータ参照となり、日付もそれに合わせての表記になります。
まとめ
見映えにこだわらなければ、どの月であっても31日まで横軸を出してしまってもいいのですが、表示しない月にもあるのは見た目がよくないという場合に使えます。
設定がやや複雑になるので、ほぼ自分のための忘備録ですが、困っている方がいたら、参考にしてください。
Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術
コメント