【OFFSET関数】グラフ範囲を自動的に変更する方法 DL可

ExcelのOFFSET関数を使えば、グラフの参照範囲を自動的に変更することができます。

これにより、新しいデータを追加したときやデータを削除したときにも、
グラフの範囲を手動で調整する必要がなくなります。

過去に、OFFSET関数に関連する記事を複数アップしています。

【OFFSET関数】本当の理解を目指して』では、OFFSET関数の原理原則を、

【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -1-』では、「1.SUM関数を組み合わせた動的な範囲指定に向けた仕込み」、

【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -2-』では、
2. SUM関数を組み合わせた動的な範囲指定を実施

に焦点を当てました。

今回は、
「【参照範囲】セル範囲を可変しながら、セル値の取得やグラフを作成することができる」のグラフ作成部分に着目します。

セル値の取得に関しては、『【OFFSET関数】本当の理解を目指して』でご覧ください。

主に、次のようなシーンで役立ちます。

  • 【条件付き書式】セル範囲を可変しながら、条件付き書式を適用できる
  • 【参照範囲】セル範囲を可変しながら、セル値の取得やグラフを作成することができる
  • 【計算】セル範囲を可変しながら、合計値や平均値を計算できる
投稿を編集 “【OFFSET関数】本当の理解を目指して” ‹ データ分析ドットコム — WordPress

本記事でのゴールは、次のような「グラフの参照範囲を自動的に変更する方法」を目指します。

なお、OFFSET関数に関連する記事は次の通りです。

 

例題

サンプルデータ

以下のようなサンプルデータを考えます。

A列には日付、B列には売上が入っています。
2022年3月から2022年10月まで売上がグラフとして作成されています。

しかし、
このグラフは現在のデータに基づいているため、
新しいデータやデータを削除した場合には、グラフの範囲を手動で調整する必要があります。

グラフの参照範囲を自動的に変更してみましょう。

解法

OFFSET関数を使って、グラフの参照範囲を自動的に変更することができます。
以下の手順に従って、OFFSET関数を使ってグラフの参照範囲を自動的に変更してみましょう。

なお、画像が小さくて見えづらい場合は、画像クリックすれば拡大して表示されます。

  1. [数式]→[名前の定義]の順に押す。
  1. 「名前」を「日付」
  2. 参照範囲を、=OFFSET(S1!$A$2,0,0,COUNTA(S1!$A:$A)-1,1)に書き換える
  1. 「名前」を「売上」
  2. 参照範囲を、=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
    に書き換える
  1. 青棒グラフを選択
  2. 青棒の数式を、=SERIES(‘S1′!$B$1,’S1′!日付,’S1’!売上,1)
    に書き換える

上記の手順で、出来上がりました。
A列とB列に、データを追加&削除すると、グラフの参照範囲が自動的に可変しています。

解説

全体ロジックの解説

OFFSET関数は、指定された参照範囲から指定された行数と列数だけ移動して、新しい範囲を返します。
例えば、「=OFFSET(A1,1,0,3,1)」という式を使うと、A2からA4までの範囲が返されます。

OFFSET関数を使って、参照範囲を動的に変更する方法は、COUNTA関数を組み合わせることで実現されます。

COUNTA関数は、指定された範囲にある空でないセルの数を数えます。
空でないセルの数 は、 データが入力された範囲のセル数 です。つまり、データ数でもあります。
そのため、データ追加&削除が発生しても、グラフを可変に変更できるといわけです。

具体的には、次の3ステップにより、
参照範囲を自動的に変更することができるようになります。

  1. COUNTA関数を使用して、データ数を調べる
  2. 調べたデータ数をOFFSET関数の引数に渡し、データ数分だけ、行数と列数だけ移動
  3. OFFSET関数を、グラフ参照範囲として設定
    (グラフの系列を指定するSERIES関数の「category_labels」「values」に渡す)

参考記事

関数式の解説

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


Sheet1のA列「日付」のセル数分の行数を返します。
上記関数式の「日付」を[名前の定義]で作成します。

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


Sheet1のB列「売上」のセル数分の行数を返します。
上記関数式の「売上」を[名前の定義]で作成します。

以上2つの関数式により、空でないセルの数 が分かり、 データの数 を知ることができます。

=SERIES('S1'!$B$1,'S1'!日付,'S1'!売上,1 ) 



青棒グラフの数式を、上記関数式に書き換えます。
第2引数では「’S1′!日付」、第3引数では「’S1′!売上」を設定しています。

「’S1′!日付」「’S1′!売上」は、[名前の定義]で作成したものです。

グラフ数式の話になるため、ここては、SERIES関数に関して、簡単に紹介します。

引数名称
範囲データ系列を表すために使用するセル範囲を指定
行または列データ系列の方向を指定
データ系列が水平方向に並んでいる場合は「行」、垂直方向に並んでいる場合は「列」と指定
名前データ系列の名前を指定
グラフの凡例(レジェンド)に表示される
プロットされる値の数データ系列に含まれるデータポイントの数を指定

COUNTA関数以外の代替案

動的なグラフの作成方法に関して、COUNTA関数を紹介しましたが、代替手段が他にもあります。
簡単なまとめとなります。
なお、1つ目が一番簡単ですが、他の作業者がテーブル解除する恐れがあります。

  1. グラフのデータ範囲をテーブルに変更することで、グラフの参照範囲を可変する
  2. COUNTA関数の代わり、MATCH関数を使うことも可能

最後に

Excelでグラフ範囲を自動的に変更する方法について、解説してきました。
自動的にグラフの範囲が変更できれば、様々なメリットを享受できると思います。

この他にもたくさんの関数が存在します。表計算ソフトには様々な関数が用意されており、
それらをうまく活用することで、日々の業務や生活に役立てることができます。
今後もExcelやGoogleスプレッドシートなどを使って、便利な機能や使い方を学んでいきましょう。

解答付きサンプルデータ

参考記事

上記記事の読みやすさをさらに向上させるために、Excel関数に関する原理原則についてまとめた記事を紹介します。

数式の原則|Excel関数を学ぶ前に

Excelほど、馴染みがあり、広く浸透しているツールもないでしょう。 さらに、Excel関数はExcel作業を便利するパッケージであり、習得することで、大きな進歩につながりま…

数式の原則|Excel関数の種類は大まかに知っておこう

冷静に、スムーズに仕事ができるため、Excel関数の種類を大まかに知っておくことが大切です。 Excel 2019では、Excel関数が全486関数もありますが、Excel機能を上手に活用…

数式の原則|Excel関数にはそれぞれ構文がある

Excel関数には構文があります。それが、引数と戻り値です。構文に沿えばいいだけです。言わば、数学の公式のようなものです。理論を知らなくても、公式に当てはめれば答え…

数式の原則|Excel関数の引数とは

Excelの引数は、料理を完成させるための材料のようなものです。しかも、材料に種類があります。その種類を理解すると、Excel関数を上手く使いこなせるようになります。

数式の原則|Excelの検索ダイアログから、関数を探す

Excelの検索ダイアログに使えば、目的の関数を発見しやすくなります。WEB検索する前に、Excel関数の検索ダイアログを使ってみましょう。