複数グラフでの『参照範囲』一括変更方法

質問

複数の複合グラフにおいて、
『参照範囲をまとめて更新していく方法』を模索しています。

『OFFSET関数』という方法を見つけましたが、うまくいきませんでした。
他にはマクロ（VBA）の方法もありましたが、できればこの方法は使わないようにしたいです。

グラフの概要は以下になります。
（例：集計週＜1/27～2/2＞『りんご』の場合）
------------------------------------------------------------------------------------------------------
※添付画像参照ください。

Sheet1　⇒くだもの別の金額前年比
Sheet2　⇒くだもの別の金額　（※集計週までは当年実績、未来週は前年実績）

Sheet3　⇒くだもの別に売上グラフを作成
　【棒グラフ】
　　　=SERIES(Sheet2!$C$24,Sheet2!$S$23:$AH$23,Sheet2!$S$24:$AH$24,1)
　【折れ線グラフ】
　　　=SERIES("前年比",Sheet1!$D$3:$O$3,Sheet2!$S$4:$AD$4,2)
------------------------------------------------------------------------------------------------------

●グラフは毎週１回更新。
●グラフの参照範囲は、種類ごとに
　【集計週から１２週前～集計週より４週先まで】を固定表示。
　（＝１週間ごとに表示範囲が１行ずつ画面右へズレていく）
●参照列は全種類共通。参照行は種類により異なる。

対応の可否と併せまして、具体的な方法をご教示いただきたいです。
何卒よろしくお願いいたします。

回答No.1

masnoske · ベストアンサー

集計シート(Sheet1, Sheet2)とグラフ用シート(Sheet4, Sheet5)を分けてはどうでしょうか。

１．集計週を基準に12週前～4週先までのデータをOFFSET関数でSheet1からSheet4に抽出（同様にSheet2からSheet5にも抽出）。
２．Sheet4とSheet5を使ってグラフを作成。

適当なセルに基準週の列番号（OFFSETの基準列番号）を入力しておきます。
毎週Sheet1とSheet2にデータを追加し、基準列番号をプラス1すれば、Sheet4とSheet5は関数で更新されますから、グラフも自動的に更新されます。

お礼

masnoske さん
大変お世話になります。
早速、ご教示いただいた方法で対応してみました。

…バッチリでした！ ありがとうございます。
今一歩、わたくしの理解と応用力が足りておりませんでした。

大変助かりました。
また機会がございましたら、是非よろしくお願いいたします。

回答No.2

kkkkkm

最初の週から何週目かをどこかに記載もしくは計算で表示して
http://officetanaka.net/excel/function/tips/tips71.htm
のOffsetの使い方を参考にして
A1に週目を表示するとして
項目名
=OFFSET(Sheet1!$D$3:$O$3,0,Sheet1!$A$1)
のような感じで名前の定義で使えばいかがでしょう。
定義でSHeet1が入りますが
SERIES("前年比",Sheet1!$項目名,Sheet2!系列2,2)
にしないとエラーになるみたいです。

お礼

kkkkkm さん
大変お世話になります。
この度はご教示いただき、ありがとうございました。

ベストアンサーが１つしか選べないため、大変心苦しいのですが最初に回答をいただいた方にさせていただきました。
が、kkkkkmさんにご教示いただいた【名前の定義】も試して理解できました。
ありがとうございました。

また機会がありましたら、ぜひよろしくお願いいたします。