OFFSET関数でセル範囲自動調整 -DL有-

2024-04-17 2024-03-13

えくせるサイトウ

ExcelのOFFSET関数を使いこなせていますか？
「データが変動する表やグラフをもっと効率的に扱いたい」と考えている方は少なくないはずです。

新しいExcelからテーブル機能が追加されたため、
OFFSET関数を使用せずと、動的なセル範囲の指定ができるようなりました。

実は、テーブル形式でなくても、OFFSET関数で動的なセル範囲の指定が可能になり、
データの自動更新や分析が格段に楽になります。

この記事では、実際のサンプルデータを使った応用例までをわかりやすく解説します。

OFFSET関数のお作法は『【OFFSET関数】本当の理解を目指して』を確認してください。

OFFSET関数でセル範囲自動調整
まとめ
- - 解答付きサンプルデータ
————————————— 参考記事 —————————————
- 原理原則に関する記事

OFFSET関数でセル範囲自動調整

OFFSET関数の基本

ExcelのOFFSET関数は、
指定した基準セルから任意の行や列だけ離れた位置にあるセル範囲を返します。
例えば、OFFSET(A1, 3, 2) は、A1から3行下で2列右のセルを指します。

応用すると、
動的なセル範囲の作成に非常に便利で、
データの量が変動する表やグラフで特にその力を発揮します。

実践的な例で学ぶOFFSET関数

「売上データ」というシートに毎月の売上が記録されている場合、
OFFSET関数を使って、新しい月のデータが追加されるたびに自動で範囲を更新できます。

サンプルデータを用いたOFFSET関数のセル範囲自動調整

[関数の引数]ダイアログボックスの設定は下図の通りです。
ダイアログボックスの詳しい使用方法が自信のない方は、巻末の参考記事もチェックしてみてください。

[関数の引数]ダイアログボックスの「引数の入力結果」「数式の結果」を見ると、
引数の入力が正しいかが確認できます。

OKをクリックすると、以下の関数式がセルに入力されます。

=OFFSET(元データ!$A$1,0,0,COUNTA(元データ!$A:$A), COUNTA(元データ!$1:$1))

「元データ」シートのA1セルを基点として、
A列にあるデータの行数を範囲の高さ、
1行目にあるデータの列数を範囲の幅

として設定しています。

ここで、行と列の移動量を0に設定することで、
A1セルからの移動はなく、そのままの位置から範囲を定めています。

関数の結果(戻り値)は以下です。期待通りの結果を得られています。
データを増やしても、自動でデータを取得できています。

OFFSET関数のセル範囲自動調整の注意点

大きく2つがあります。

元データに空白行/空白列がある要注意

元データに空白行/空白列があると、期待通りの結果を得られません。
そのため、空白行/空白列を追加しないようにしましょう

空セルがある要注意

空セルは0として認識されるため、微調整が必要です。
(これはOFFSET関数だけではなく、他の関数でも起こる事象です）

IF関数やISBLANK関数などで微調整してください。
詳細は『ISBLANK関数の構文 -空白の有無をチェック-』『空白セルが0と判定される問題を、ISBLANK関数で回避』などに記載しています。

OFFSET関数のセル範囲自動調整の応用例

OFFSET関数のセル範囲自動調整ができれば、以下のようなことが実現できます。

入力リストの自動更新
印刷範囲の自動調整
OFFSET関数は、他の関数式のセル範囲指定（SUM関数/VLOOKUP関数などのセル範囲指定に）
※ただし、セル範囲指定の際に「名前の定義」を使用する工夫が必要です

まとめ

この記事では、ExcelのOFFSET関数の強力な機能とその応用方法について掘り下げました。

OFFSET関数は、単にセルを参照するだけでなく、
データの量が変動する場合にセル範囲を動的に調整する際に非常に有効です。
月次売上データの実例を通じて、OFFSET関数がどのように活用できるかを示しました。

OFFSET関数を使いこなして、データの自動更新やダイナミックな分析を実現しましょう。
今回の例を参考にして、あなたの日常の業務にOFFSET関数を活用してみてください。