Excel業務カイゼンブログ

OFFSET関数の便利な使い方

VLOOKUP関数やINDEX関数のようなExcelの行列関数の仲間に、OFFSET関数という関数があります。

OFFSET関数は非常に特殊な関数で、普通の関数は、ある一つの値を答えとしますが、OFFSET関数は、範囲を答えとします。

範囲を答えとしますので、OFFSET関数の答えをセルに求めても正しく何かを求める形にはなりません。

実際に操作するファイルはこちらからダウンロードできます。

OFFSET関数で範囲を指定する

OFFSET関数で範囲を指定するというのは、実際にはどんなことなのでしょうか？

ほとんどのケースではOFFSET関数は他の関数と組み合わせて使用します。

他の関数とは、「範囲」をもとに値を求める関数です。

範囲を指定する関数はとても多く、単純なものであればSUM関数も範囲を指定しますし、行列関数のVLOOKUP関数や、高度なものであればデータベース関数のDSUM関数なども範囲を指定します。

これらの関数は全てOFFSET関数と組み合わせて応用した数式を組み立てることができます。

OFFSET関数の書式

OFFSET関数は次のような書式で指定します。

=OFFSET(基準となるセル,基準からいくつ縦にずれるか, 基準からいくつ横にずれるか,範囲の行数,範囲の列数)

黄色のセル範囲は、基準となるセルはセルB2として、基準より縦に１つ、横に２つ、範囲の行数は５行、列数は３列ということになります。

これを関数に当てはめると、

=OFFSET(B2,1,2,5,3)

のようになります。

［基準となるセル］は、どこでも任意のセルを指定して構いません。もし悩んだら、指定したい範囲の左上のセルを指定して、［基準からいくつ縦にずれるか］と、[基準からいくつ横にずれるか]をともに0と指定するとよいでしょう。

OFFSET関数のみをセルに入力すると「#VALUE」エラーとなります。Excelは範囲をセルに答えとして表示することができないからです。このエラーの値を使って計算してもエラーになります。

もし、VLOOKUP関数でこの範囲をOFFSET関数で指定する場合は、

=VLOOKUP(H2,OFFSET($B$2,1,2,5,3),2,0)

のようになります。数式をコピーする場合は、基準となるセルB2は相対的に移動するといけませんので絶対参照にします。

完全な絶対参照

OFFSET関数は完全な絶対参照を実現することができます。

OFFSET関数の動作を説明する前にセルを削除したり挿入したりする操作をした時の絶対参照の動作についておさらいしましょう。

このようなINDEX関数内の絶対参照があります。ここでは、わかりやすいように[数式]タブ内の[数式の表示]機能で数式を表示しています。

元の範囲、B2からB6の範囲を切り取ってB3から始まる範囲に貼り付けると、範囲がB3からB7に変更になります。

絶対参照といえども、編集することで範囲が変わってしまうのです。

元の状態から４行目に１行挿入した状態です。範囲がB2からB7までの範囲に変更になり、1行範囲が増えました。

逆に削除を行うと、範囲が削除した分、減ります。

元の状態からセルB6のセルをDeleteキーでクリアした状態では、範囲は元のままでした。

このように編集の方法によって、絶対参照の範囲が変わってしまうことがあります。

効率化のための自動化されたExcelシートは、利用者がどのような編集を行うかわかりません。

計算式がおかしくなったという話はExcelのシートを扱っていれば誰もが経験することですが、この、参照範囲が編集の方法によって変わるということが一因であると考えられます。

そこでOFFSET関数を使えば、どんなに編集しても必ず初期の想定した範囲を指定することができます。

このようにOFFSET関数でセルB1を基準として、そこから縦に1行、横に0列ずれたB2のセルから5行、1列を範囲とする設定を行います。

例えB2からB6の範囲で挿入や削除、切り取り、貼り付けし、セル範囲が変更になってしまうケースでも、セルB1基準、縦に1、横に0、5行1列という設定は変わりませんので、常にB2からB6までを範囲とすることができます。

ダウンロードしたファイルのステップ１シートで、２行目から５行目に行を挿入や削除をしてみて、計算式が変わらないことを確認しましょう。

COUNTA関数との組み合わせ

VLOOKUP関数やINDEX関数と組み合わせるだけでは、完全な絶対参照以外の便利さはあまりないかもしれません。

OFFSET関数の中にCOUNTA関数を組み込むことで、範囲の大きさを可変する範囲にできます。

次の例ではB列の一覧表に入力されている分だけをINDEX関数の範囲とすることができます。

B列全体の入力セルを数えて、そこから項目名「名前」のセルの分、１を差し引いた範囲を行数として指定しています。

このように、範囲の大きさを入力セルに合わせて変更できるというのが、OFFSET関数の最も有効な使い方かもしれません。

ダウンロードしたファイルのステップ2シートで、セルE3に次の計算式を入力後、セルE2の値を5に変更してセルE3の結果が名川に代わることを確認しましょう。

=INDEX(OFFSET(B2,1,0,COUNTA(B:B)-1,1),E2)

さらにE2を6に設定すると、一覧表にデータがないので、#REFのエラーが出ることを確認し、セルB8に斎藤と入力すると一覧表範囲が自動で広がるので、エラーが斎藤に変わることを確認しましょう。

入力規則のリストで指定する範囲にOFFSET関数を使う

OFFSET関数で求めた範囲は、計算式として答えをセルに出すことはできませんが、Excelの様々な機能の参照範囲としてOFFSET関数を指定することはできます。

この使い方の代表例は入力規則機能のリストです。

ダウンロードしたファイルのステップ3シートのセルE2で実際に操作して確認してみましょう。

［データ］タブの［データの入力規則］で、［入力値の指定］で「リスト」を指定し、元の値に

=OFFSET(B2,1,0,COUNTA(B:B)-1,1)

と指定することで、B列に入力されたデータの数だけを選択肢にすることができます。

あくまで項目数＝選択肢の数になります。範囲にデータが足りなかったり、余計な空白が含まれていることがありません。

セルB8に斎藤と入力すると一覧表範囲が自動で広がり、斎藤も選択肢に入ることを確認しましょう。

このようなOFFSET関数を使った範囲指定はすべてのExcelの範囲を指定する機能でできるわけではありません。

グラフの元データとしてOFFSET関数で指定しても、データを入力するたびに項目が増えるということはありません。

ピボットテーブルの元データ範囲の場合はOFFSET関数は有効で、データを入力するたびに更新すれば都度データ範囲が広がります。

INDEX関数の代わりにOFFSET関数を使う

実は、例外ですが、OFFSET関数単体でも答えを出すことができます。

=OFFSET(A1,0,0)

この関数のように、はじめの３つの引数だけ設定し、４つめの引数の高さと、５つめの引数の幅に何も設定しなければ、基準のセルから行数と列数移動したところのセルの値を返す、INDEX関数のような動きをします。ただし、INDEX関数では縦の位置と横の位置を１から始めるように指定しますが、OFFSET関数では0から始めます。

つまり、この関数の答えはセルA1の値を求めます。

しかし、OFFSET関数はもともと範囲を指定することが目的の関数で、INDEX関数はそのセルの位置にある値を求める関数という、目的が別のものですので、範囲を指定する目的以外では極力OFFSET関数は使わないほうが混乱しないです。