【Excel】OFFSET関数で参照範囲を可変させる

OFFSET関数は基準のセルからシフトした位置を返す関数です。単一セル、複数範囲どちらでも参照できます。参照した範囲の行/列数を変更することもできます。

サンプルで図説しているのでイメージが湧かない人はそこまで読み進めて下さい。

OFFSET関数の使い方

=OFFSET(参照, 行数, 列数, [高さ], [幅])

…基準からシフトさせたい行数、列数を指定します。シフト後の高さ（行数）、幅（列数）も変更できます。

OFFSET関数のサンプル

基本的な例をサンプルで説明します。

単純にOFFSET関数の挙動を表す例です。実用的な例は次項で解説します。

Sample

単一セルをシフトする

=OFFSET(A2,2,1)

基準のセルA2から2行1列シフトします。B4セルを参照し、2,000を返します。

Sample

SUM関数の参照範囲を変更する

=SUM(OFFSET(B2,0,0,3))

SUM関数の引数にOFFSET関数を使用しています。基準セルB2の高さを3に変更しています。B2:B4となるため=SUM(B2:B4)となり5,000を返します。

引数の幅と高さは「変更後の値」を指定します。「変更量」ではないので注意して下さい。

Sample

シフトして参照範囲を変更する

=SUM(OFFSET(B2:B4,0,1,6))

基準セルB2:B4を1列右にシフトし、高さを6に変更しています。C2:C7となるため4を返します。

OFFSET関数の主な使い道は他の関数の参照範囲を変更することです。次の項でCOUNTA関数とMATCH関数を例に説明します。

COUNTA関数と組み合わせてプルダウンリストの参照範囲を可変させる

データの入力規則でプルダウンリストを作成する際、リストの元の値に空データがあるとリストにも空白が出力されます。

OFFSET関数とCOUNTA関数を組み合わせることで、空白なしのリストを作成することができます。

Sample

OFFSET関数とCOUNTA関数でプルダウンリストの空白を無くす

データの入力規則の設定タブで元の値欄に以下の式を入力して下さい。

=OFFSET(A2,,,COUNTA(A:A)-1)

COUNTA(A:A)は氏名も含めてカウントするので4を返します。COUNTA(A:A)-1は氏名をカウントしないための-1です。結果、=OFFSET(A2,,,3)となるので元の値はA2:A4（A2から3行分の高さ＝A2,A3,A4）を返します。

A列にデータを追加してみます。

=OFFSET(A2,,,COUNTA(A:A)-1)

A列にデータが追加されています。COUNTA(A:A)は5、5-1で4、OFFSET（A2,,,4)となりA2:A5を返します。

この数式は名前の定義でも同じことができます。

SUM関数、MATCH関数と組み合わせて集計範囲を可変させる

Sample

MATCH関数で日付を検索、OFFSET関数で参照した範囲をSUM関数で集計

4名の売り上げを集計するサンプルです。12/1からB7セルで指定した日付までの売り上げを集計できます。

=SUM(OFFSET(B2:B5,,,,MATCH(B7,B1:E1,0)))

1. MATCH(B7,B1:E1,0)で日付12/3を検索しています。範囲内で3番目に見つかるので3を返します。
2. OFFSET(B2:B5,,,,3)となるので列数が1から3になりB2:D5を返します。
3. =SUM(B2:D5)となり21,000を返します。

作業用の行を1行追加して=COLUMN()を各列に入力すれば、列番号で検索することもできます。色々な場面で応用できます。また、COUNTIF関数などにも応用できます。

OFFSET関数は他の関数の参照範囲を可変させるのに便利

ここでは2例しか挙げませんでしたが、その他にも組み合わせられる関数はたくさんあります。覚えれば集計表作成のスキルが格段に上がるので、勉強して使いこなせるようになってください。