【Excel】OFFSET関数とグラフを連動させて動的グラフを作成する方法

Excelでグラフを作成する際、元データの範囲を固定していませんか?

データが増減するたびにグラフの元データ範囲を修正するのは手間がかかります。

OFFSET関数とグラフを組み合わせれば、元データの範囲を自動で更新する動的グラフを作成できます。

この記事では、OFFSET関数を使って動的グラフを作成する具体的な手順を解説します。

データ範囲の自動更新で、グラフ管理の手間を大幅に削減しましょう。

OFFSET関数とCOUNTA関数で動的な範囲を定義する仕組み

OFFSET関数は、指定したセルを基準に、指定した行数・列数だけ移動した位置にあるセルの範囲を返します。

COUNTA関数は、指定した範囲内の空白でないセルの数を数えます。

この2つを組み合わせることで、データが入力されている範囲を自動で検出し、グラフの元データ範囲を動的に設定できます。

例えば、A1セルを基準に、データが入力されている行数と列数をCOUNTA関数で取得し、OFFSET関数でその範囲を指定します。

これにより、データが増えてもグラフの範囲を自動で拡張できるようになります。

OFFSET関数とグラフを連動させる手順

ここでは、具体的なデータ例を用いて、OFFSET関数とグラフを連動させる手順を解説します。

例として、A1セルにタイトル、A2セルからデータが始まる棒グラフを作成します。

1. OFFSET関数でデータ範囲を定義する

1. 名前の定義ダイアログを開く
   Excelメニューの「数式」タブをクリックします。次に、「名前マネージャー」をクリックし、「名前の定義」を選択します。
2. 名前の定義
   「新しい名前」ダイアログボックスが表示されます。「名前」欄に、グラフで参照する範囲の名前を入力します。ここでは例として「売上データ」と入力します。
3. 参照範囲の設定
   「参照範囲」欄に、以下のOFFSET関数を入力します。

   =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

   この数式は、Sheet1のA1セルを基準に、1行下から、A列に入力されているセルの数だけ行数を取得し、1行目に入力されているセルの数だけ列数を取得した範囲を指定します。

4. OKをクリック
   「OK」ボタンをクリックして、名前の定義を完了します。

2. グラフを作成し、元データ範囲を名前で指定する

1. グラフの元データを作成
   Excelシート上で、グラフにしたいデータ範囲を選択します。この時点では、一時的な範囲で構いません。例として、A1セルからB5セルまでを選択します。
2. グラフの挿入
   「挿入」タブをクリックし、「グラフ」グループから作成したいグラフ(例:縦棒グラフ)を選択して挿入します。
3. グラフの元データ範囲を変更
   作成されたグラフをクリックし、グラフツールが表示されたら「デザイン」タブを選択します。「データ」グループの「グラフのデータの選択」をクリックします。
4. データソースの編集
   「グラフデータのソースの選択」ダイアログボックスが表示されます。「グラフエリア」の「系列名」または「軸ラベル」など、グラフの元データとなる項目を選択し、「編集」ボタンをクリックします。
5. 名前を参照する
   「系列名」または「軸ラベル」の編集ダイアログボックスが表示されます。「系列値」または「軸ラベル」の範囲指定欄を削除し、先ほど定義した名前「売上データ」を直接入力します。例:「=Sheet1!売上データ」のように入力します。
6. OKをクリック
   各ダイアログボックスで「OK」をクリックして、グラフの元データ範囲の設定を完了します。

3. 動的な動作を確認する

1. データの追加
   元のデータ範囲(例:A列、1行目)に、新しいデータを追加します。
2. グラフの更新
   グラフの範囲が自動的に更新され、追加したデータがグラフに反映されていることを確認します。

OFFSET関数で動的グラフを作成する際の注意点

OFFSET関数とグラフを連携させる際には、いくつか注意すべき点があります。

データが空白になる場合の対処法

COUNTA関数は空白セルをカウントしないため、データの中間に空白があると、そこまでしか範囲を認識しません。

データ範囲の途中に空白セルができないように注意してください。

もし空白セルが入る可能性がある場合は、COUNTA関数の代わりにROW関数やCOLUMN関数と組み合わせるなど、別の方法を検討する必要があります。

OFFSET関数の参照元を間違える

OFFSET関数の基準セルや、COUNTA関数で参照する範囲を間違えると、意図しない範囲がグラフに反映されます。

数式を入力する際は、シート名やセル参照が正しいか、慎重に確認してください。

「名前の定義」で入力した数式は、Excelの数式バーで直接編集できます。

グラフの系列が複数ある場合

複数の系列(例:複数商品の売上推移)をグラフに表示する場合、それぞれの系列に対してOFFSET関数で定義した名前を作成し、グラフの各系列に設定する必要があります。

例えば、売上データがA列、単価がB列にある場合、「売上データ」と「単価データ」のように別々の名前を定義し、それぞれをグラフの系列に紐付けます。

Excel 2019以前のバージョンでの互換性

OFFSET関数自体はExcelの初期バージョンから存在する関数です。

しかし、名前の定義機能やグラフのデータソース設定方法は、Excelのバージョンによって若干操作が異なる場合があります。

Excel 2019以前のバージョンでも同様の手順で設定可能ですが、メニューの場所などが異なる場合があります。

OFFSET関数とVLOOKUP関数を組み合わせた応用

OFFSET関数は、他の関数と組み合わせることで、さらに高度なデータ管理が可能になります。

VLOOKUP関数との連携

例えば、VLOOKUP関数で特定の条件に一致するデータを検索し、その検索結果をOFFSET関数で動的に取得する、といった応用が考えられます。

これにより、ユーザーが入力した条件に応じて、グラフに表示されるデータが自動で切り替わるような、インタラクティブなダッシュボードを作成できます。

Power Queryとの比較

データソースが複数あったり、データの整形が必要な場合は、Power Queryの利用も有効です。

Power Queryを使えば、外部データソースからの取り込みや、複雑なデータ変換を自動化できます。

動的グラフの作成という点ではOFFSET関数が手軽ですが、データの前処理が必要な場合はPower Queryが強力な選択肢となります。

機能	OFFSET関数+グラフ	Power Query+グラフ
主な用途	シート内のデータ範囲を動的にグラフへ反映	外部データソースからのデータ取得・整形、シート内データの自動更新
設定の容易さ	比較的容易。数式と名前の定義で設定完了	学習コストがかかるが、複雑なデータ処理も可能
データ更新	元データ変更で自動更新	クエリの更新操作で自動更新
データソース	Excelシート内データ	Excelシート、データベース、Webなど多様
複雑なデータ変換	苦手	得意

OFFSET関数とグラフを組み合わせることで、データ範囲の増減に自動で対応する動的グラフを作成できます。

この記事で解説した手順で、グラフの元データ範囲を「名前の定義」とOFFSET関数で指定する方法を習得しました。

今後は、データが増減するレポート作成や、頻繁に更新が必要なグラフ作成に、この動的グラフ作成テクニックを活用してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】