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

2023-02-24 2024-02-02

えくせるサイトウ

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関数を使ってグラフの参照範囲を自動的に変更してみましょう。

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

[数式]→[名前の定義]の順に押す。

「名前」を「日付」
参照範囲を、=OFFSET(S1!$A$2,0,0,COUNTA(S1!$A:$A)-1,1)に書き換える

「名前」を「売上」
参照範囲を、=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
に書き換える

青棒グラフを選択
青棒の数式を、=SERIES(‘S1′!$B$1,’S1′!日付,’S1’!売上,1)
に書き換える

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

解説

全体ロジックの解説

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

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

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

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

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

参考記事

【OFFSET関数】本当の理解を目指して

関数式の解説

=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関数に関して、簡単に紹介します。

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