ExcelのOFFSETと範囲名を使った動的グラフはデータが追加または削除されるとサイズが変更されます。 ダイナミックチャートにテーブルを使用するのとは異なり、この方法は Excel の初期バージョンと下位互換性があります。
Excel の OFFSET および COUNTA 関数を使用して、データの追加または削除に応じて自動的に調整するダイナミックレンジ名を作成します。 この例では、水平と垂直の両方のデータが含まれています。
オフセットと範囲名の方法は、テーブルで使用する 2 クリックより多くの作業が必要ですが、拙著「Microsoft Excel によるバランス スコアカードと運用ダッシュボード」が示すように、チャート データに関するすべてを制御することが可能です。 このサイトCriticalToSuccess.comでは、OFFSETの他の使い方、例えばスクロールするチャートの作成、ドロップダウンメニューを使ったチャートの制御などの例を紹介しています。
Creating a Static Chart
まず、次のように静的チャートを作成し、ダイナミックチャートを開始します。 挿入] タブの [チャート] グループで、列、棒、または折れ線グラフなどの単純なチャートを選択します。
このチャートを移動して、データ範囲の下にデータを追加するスペースを確保します。 以下は、Excel 2007、2010、2013の手順です。
1. 数式］タブの［定義された名前］グループで、［名前の定義］をクリックします。 名前の定義］ボックスが表示されます。
［名前の定義］ボックスで範囲名を作成します。 名前］テキストボックスに、「rngVDate」と入力します。 命名規則は、
rng Range Name
V B7:B16 のデータの縦方向
Date 範囲内のデータの種類
3. コメント テキスト ボックスには、この範囲名が何のためのものかをあなたや他の人に知ってもらうためにコメントを書き込みます。
4.参照先ボックスに、Dateデータの範囲を計算するOFFSET式を入力します。
=OFFSET(wrksht!$B$7,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5.この範囲を計算するための式を入力してください。 Okをクリックします。
このOFFSET式では、新しい日付が日付の古いリストの一番下にあり、日付間に空白セルがない限り、範囲B7：B16のどこでも追加の日付を入力できます。
次に、同じプロセスを繰り返し、SalesとUnitsデータの範囲名を作成します。
追加する名前と数式は、
データ範囲名の参照先
Sales rngVSales =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht!C$7:$C$16),1)
単位 rngVUnits =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)
ダイナミックレンジ名のサイズをOFFSETで計算する方法はこちらで解説されています。 また、ダイナミックレンジ名をチェックする際のヒントも紹介します。
ここをクリックして、OFFSET で絶対に犯したくない失敗を学びます。
OFFSETと範囲名を使用してチャートを動的にサイズ変更するチュートリアル例題ファイルをダウンロードするには、ここをクリックします。 この例では、水平と垂直の両方のデータが含まれています。
チャートの日付式に範囲名を挿入する
これで、チャートがデータを参照するために使用する式に、作成した範囲名を挿入することによって、チャートをダイナミックにすることができます。 心配しないでください。 これはとても簡単なことです。 実際、この方法を習得すると、神秘的な能力が身につき、どんなチャートも意のままに操ることができるようになるのです。 (混乱したチャートを修正することもできます。)
上記で、チャートで使用するデータを参照する3つのダイナミックレンジ名を作成しました。
rngVDate
rngVSales
rngVUnits
次は、チャートで使用するセル参照を、あなたが作ったダイナミックレンジ名と交換します。 グラフの端をクリックして選択します。
2. チャートツールのデザインタブのデータグループで、データの選択ツールをクリックして、データソースの選択ダイアログボックスを表示します。
データソースの選択ダイアログボックスでは、グラフが使用するデータに対して不思議な制御ができます。
3. 水平（カテゴリー）軸ラベルで、編集をクリックして軸ラベルのボックスを表示します。 このダイアログボックスでは数式が見づらいので、セル参照の右側にあるセレクタアイコンをクリックします。 これにより、数式を参照できる広い Axis Labels ボックスが表示されます。 編集する前に注意してください！
軸のラベルボックスは、水平軸の参照を示しています。
4.注意してください！
軸のラベルボックスは、水平軸の参照を示しています。 数式を編集しようとする前に、数式の内側をクリックし、編集キー（F2）を押して、数式を編集することができます。 Endキーを押して、数式の最後に移動します。 この図のように、!記号の後のセル参照を範囲名に置き換えます。
これで数式は次のようになります。
=’Dynamically Resize Charts to Data.xlsx’!rngVDate
F2 を押し、日付範囲名を含むように数式を編集すると、[軸ラベル] ボックスはこのようになります。
TIP: 数式の編集中に間違えた場合、Ctrl+Z (Undo) を押して前に戻ります。 Ok］をクリックして［データソースの選択］ダイアログボックスに戻り、［売上］と［単位］の名前を追加します。
［グラフの売上と単位の数式に範囲名を挿入］
ここで、ほぼ同じプロセスを使用して［売上］と［単位］軸数式を範囲名に置き換えます。 データソースの選択］ダイアログボックスで［売上］をクリックし、［編集］ボタンをクリックして［系列の編集］ダイアログボックスを表示します。
Salesdataセル参照をその範囲名に置き換えます。
2. Sales系列を参照する系列値式の右側の選択アイコン（Selector icon）をクリックします。
3. 数式内をクリックし、F2キーを押して編集し、Salesデータのセル参照を範囲名rngVSalesに変更します。
これでチャートはSalesデータを参照するのにダイナミック範囲名を使用するようになりました。