Excel グラフ 自動更新 方法…これをググってみたりすると
OFFSET関数を使おう！
という感じの内容が多いんじゃないかなとおもいます。
もちろん、それで十分な場合なら問題はないでしょう。
- 日々更新される、毎月更新されるデータに対してグラフを自動的に更新
具体的なやり方は割愛しますが「名前の定義」を利用し、OFFSET関数とCOUNTA関数を使ってグラフで参照する範囲を自動的に更新していくやり方です(*’ω’*)
……。
いや、それじゃないんですよ、っていうのが今回の近30日のデータを利用してのグラフ自動更新。
つまり
- 更新されたデータから30日分さかのぼって、その期間のデータを利用したグラフ
- しかもデータを更新したらグラフも自動更新したい
ということ。
正直、VBA（マクロ）でなら組めそうかなと思っています。
でも今回は関数を利用して…なんとかならないかなと(*’ω’*)
しかも、そもそもExcel野郎でもないので
関数をあまり知らない。
というワタクシが挑戦してみることに(;´･ω･)
たぶん、もっとエレガントでスタイリッシュな書き方はあると思うんですが、今回のは正直言って力技です。
利用する関数
使うのは以下の３つです
- COUNTA（空白ではないセルの数を返す）
- ADDRESS（セル番地を調べる）
- INDIRECT（セルの中身の値を返す）
これで何をするかというと
- こちらが更新するデータ（日ごととか週ごとなど）が入っている場所を調べるこれは、たとえばF列のデータ最後尾の下にデータを追加した場合
「COUNTA($f:$f)」としておくとF行にある空白でないセルの数が調べられますよね？
- 調べた場所（数）から29を引くなぜ29なのかというとたとえば調べた場所が30番目だった場合、範囲として必要なのは1～30番となります。
つまり「1」という数字を取り出したいので「30-29=1」となり、29を引くことになります。
「COUNTA($f:$f)-29」という式が導き出されました(*’ω’*)
- データの入っているADDRESSを調べるADDRESSとはセルの番地のことでよく「$A$1」とか書かれたりしてますよね？
ちなみに「$」が付く場合は絶対番地という状態です。詳しい説明は他に譲るとしてここでは絶対番地で記述していきます。
ここでADDRESS関数をしらべてみると
◆ADDRESS(行番号, 列番号, 参照の種類, 参照形式, シート名)
という書き方をすることがわかります。
行番号は先ほど式で導き出した「COUNTA($f:$f)-29」、列番号はF列なのでAから数え始めて6番目。
参照の種類ですが、ここでは1を選択します（これが絶対番地になります）。
以下は省略しても大丈夫なので「ADDRESS(COUNTA($f:$f)-29,6,1)」となります。
- 最終的にそのADDRESSの中身を取り出す中身を取り出すのがINDIRECT関数です。
ADDRESS関数ですでにセル番地が指定されているので
「INDIRECT(ADDRESS(COUNTA($f:$f)-29,6,1))」となります。
ここまで一体何をしているのかというと
◇更新されたデータに対して30日前のセルに入っている中身を取り出す
というややこしいことをしている状態。
なぜこんなことをするのかというと…次はグラフの設定なのですが
◇グラフの範囲そのものを変えずに範囲の中身を書き換える
という方法を用いようと思っています。
グラフの範囲は固定、中身を書き換える
さて。
中身を書き換えるためには30日分の参照位置をずらしてあげる必要があるわけです。
つまり上から
30日前のデータ
29日前のデータ
28日前のデータ
27日前のデータ
・
・
・
3日前のデータ
2日前のデータ
1日前のデータ
当日のデータ
といった具合。
先ほど30日前データを取り出す関数を書きましたよね？(*’ω’*)
ということは
30日前=INDIRECT(ADDRESS(COUNTA($f:$f)-29,6,1))
29日前=INDIRECT(ADDRESS(COUNTA($f:$f)-28,6,1))
28日前=INDIRECT(ADDRESS(COUNTA($f:$f)-27,6,1))
27日前=INDIRECT(ADDRESS(COUNTA($f:$f)-26,6,1))
という風に書いていくわけです。
これを元データとは別の列に30日分セットしてあげます。
実際に数字を追加してみると…
赤い枠内は左の値の下から6つを取り出している状態ですね
左の一番下空白のセルに数字を追加してみると…
赤い枠の範囲が更新されていますね(*’ω’*)
こんな感じで赤い枠の範囲をグラフにしてあげれば…最新からみて30日分のグラフを自動更新させることができるわけです！
まとめ
かなりの力技ですし、数が増えると設定する量も半端なくなるのでなんとも面倒ですが…意外に簡単にできる方法だと思います。
何かの参考になれば幸いです！
もっといい方法あると思うんだけどなぁ～(;´･ω･)