エクセルのOFFSET関数の使い方｜基準から動かして値を取る・INDEXとの使い分け

OFFSETは「ここから○行・○列ずらしたセル（範囲）を指す」関数です。表が増えても合計の範囲を自動で追いたいときなどに出てきます。便利ですが式が読みにくいので、使いどころと代わりの手段（INDEX・テーブル）を並べて紹介します。

■ 書き方

=OFFSET(基準, 行数, 列数, 高さ, 幅)

基準は出発点のセルです。行数・列数は、そこから下（右）へ何マスずらすかで、マイナスにすると上（左）へ動きます。高さ・幅は省略できます。入れると、ずらした先を左上として、その大きさの範囲を指します。

■ 例1：基準から3つ下の値を取る

=OFFSET(A1,3,0)

A1から3つ下、つまりA4の値が返ります。列も動かすなら次の形です。

=OFFSET(A1,3,2)

A1から3つ下・2つ右、つまりC4の値が返ります。

■ 例2：増える表の合計（高さをCOUNTAで数える）

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

B2から、B列の入力数に合わせた高さの範囲を合計します。行が増えると範囲も伸びます。見出し1行ぶんを引くため「-1」を付けています。途中に空白があると個数がずれるので、空白のない列で使います。

■ 例3：選んだ月の列を合計する（プルダウンと組み合わせ）

=SUM(OFFSET(D2,0,MATCH(H1,D1:F1,0)-1,3,1))

H1で選んだ月の列をMATCHで探し、その列の3行を合計する形です。列の探し方と範囲の高さは、自分の表に合わせて直してください。

■ 気をつけること

・OFFSETは、シートのどこかを触るたびに再計算される関数です。データが多いブックでは動作が重くなることがあります。

・参照先を目で追いにくく、ずれたときに気づきにくいです。

・基準より上や左へずらしすぎてシートの外に出ると、#REF! になります。

■ 代わりの手段1：INDEX

=SUM(B2:INDEX(B:B,COUNTA(B:B)))

終わりの位置だけを動かす書き方です。再計算の負担が小さく、範囲も見やすくなります。例2と同じ答えになります。

■ 代わりの手段2：テーブル

表を選んで Ctrl+T でテーブルにすると、次の式で行が増えても自動で追います。

=SUM(テーブル名[金額])

初心者にはこれがいちばん楽です。

■ 向かない場合

何千行もある表や複数ファイルの連動で動作が重いときは、INDEXやテーブルへの切り替えが向きます。式を直す範囲が大きいときは、手作業より専門の調整が早いです。

■ 注意

・操作名・関数名は Windows・Microsoft 365 のExcelの表記です。Macや古い版では少し違うことがあります。

・式は例です。自分の表のセル位置に置き換えて使ってください。

表が増えるたびに合計や参照がずれて困っている方は、Excel調整サービスでご相談を承ります。