Office Hack

ExcelでOFFSET関数の使い方|指定した位置のセル範囲を参照する

  • Release
  • Update

Excelで指定した位置のセル範囲を参照したい場合、OFFSET関数で求めることができます。SUMIF関数やMATCH関数といったセル参照を引数として使う関数と組み合わせることが多いです。

OFFSET関数の基本的な使い方から他の関数との組み合わせまで解説しています。

OFFSET関数の意味とは?

offsetは英語で「相殺する」という意味です。エクセルのOFFSET関数では別の意味となっています。

OFFSET(オフセット)関数は、指定したセルまたはセル範囲から指定された行数と列数だけ移動した位置にあるセル範囲を返します。検索/行列関数の1つです。

OFFSET関数のイメージ

上図は「=OFFSET(範囲,1,2,1,1)」というOFFSET関数の数式を図と文字で表した例です。

範囲の左上から1行下がって、2列右に移動した位置にある「高さ1」「幅1」のセル範囲を結果として返します。

範囲の左上を基準として行数と列数で位置を特定し、結果として返すセル範囲を「高さ」と「幅」で指定します。

OFFSET関数の書式

まずは、OFFSET関数の書式を確認していきましょう。

OFFSET関数の書式

OFFSET関数の書式は「=OFFSET(参照,行数,列数,[高さ], [幅])」のように引数を最大5つ使用します。引数を1つずつ確認していきましょう。

※引数(ひきすう)とは、Excelの関数を使用する際に必要な情報です。関数が結果を返すための判断材料とイメージしましょう。関数名の後の括弧「()」内に入力します。

第1引数(参照)

参照

1番目の引数は「参照」です。この引数は必須です。基準となるセルまたはセル範囲を指定します。

セルまたはセル範囲以外は、#VALUE!エラーが返されます。

第2引数(行数)

行数

2番目の引数は「行数」です。この引数は必須です。第1引数「参照」で指定したセルまたはセル範囲の左上(以下基準とします)から上方向、または下方向へ移動する距離を数値で指定します。

「0」を指定すると基準と同じ行、「1」だと基準の1つ下の行、「2」だと基準の2つ下の行・・・を指定します。「-1」のように負の値を指定すると基準より上の行を指定できます。

行数

例えば下側の表からバナナを参照したい場合、C5セルを基準としたとき、基準から1つ下の行にバナナが含まれる行があるため行数は「1」となります。

第3引数(列数)

列数

3番目の引数は「列数」です。この引数は必須です。基準から左方向、または右方向へ移動する距離を数値で指定します。

「0」を指定すると基準と同じ列、「1」だと基準の1つ右の列、「2」だと基準の2つ右の列・・・を指定します。「-1」のように負の値を指定すると基準より左の列を指定できます。

列数

例えば下側の表からバナナを参照したい場合、C5セルを基準としたとき、基準から1つ右の行にバナナが含まれる列があるため列数は「1」となります。

第4引数(高さ)

高さ

3番目の引数は「高さ」です。この引数は任意です。結果として返したいセル範囲の行数を数値で指定します。高さは正の値である必要があります。

省略した場合、第1引数「参照」で指定したセルまたはセル範囲と同じ行数とみなされます。

第5引数(幅)

幅

3番目の引数は「幅」です。この引数は任意です。結果として返したいセル範囲の列数を数値で指定します。幅は正の値である必要があります。

省略した場合、第1引数「参照」で指定したセルまたはセル範囲と同じ列数とみなされます。

OFFSET関数の使い方(実践)

実際にOFFSET関数を使用する例を紹介します。引数をどのように使用するか確認してみましょう。

作業時間:8分

  1. 表を準備する

    表を準備する

    まずは上記の表を準備しましょう。C3セルに下側の表から商品コード「1001」の商品名であるりんご(C6セル)をOFFSET関数を使って参照させます。

  2. OFFSET関数を入力する

    OFFSET関数を入力する

    C3セルを選択し、『=OFFSET(』と入力します。

  3. 参照を指定する

    参照を指定する

    C3セルの「=OFFSET(」に続けて『$C$5:$E$7,』と入力します。マウスでセル範囲を選択してから【F4】キーを押して絶対参照にしても同じです。

  4. 行数を入力する

    行数を入力する

    行数を指定します。C3セルの「=OFFSET($C$5:$E$7,」に続けて『1,』と入力します。結果として返したいセル(C6セル)は指定したセル範囲の左上から1行下がったところにあるので「1」を指定しています。

  5. 列数を入力する

    列数を入力する

    列数を指定します。C3セルの「=OFFSET($C$5:$E$7,1,」に続けて『0,』と入力します。結果として返したいセル(C6セル)は指定したセル範囲の左上と同じ列にあるために「0」を指定しています。

  6. 高さを指定する

    高さを指定する

    高さを指定します。C3セルの「=OFFSET($C$5:$E$7,1,0,」に続けて『1,』と入力します。結果として返したいセル(C6セル)は行数1のセルなので「1」を指定しています。

  7. 幅を指定する

    幅を指定する

    幅を指定します。C3セルの「=OFFSET($C$5:$E$7,1,0,1,」に続けて『1)』と入力し、【Enter】キーを押します。結果として返したいセル(C6セル)は列数1のセルなので「1」を指定しています。

  8. OFFSET関数の結果

    OFFSET関数の結果

    OFFSET関数を使って指定したセル範囲から行数、列数、高さ、幅を指定することで目的のセル(C6セル)を参照することができました。

OFFSET関数とINDEX関数の違い

OFFSET関数と使い方が似ているINDEX関数との違いについて説明します。

関数の意味と引数の違い

両者とも行と列を指定した位置にあるセル範囲を返しますが、OFFSET関数はさらに結果として返したいセル範囲の大きさを指定することができます。

引数の数はOFFSET関数が最大5つ、INDEX関数が最大で4つとなっています。

関数 意味 引数
OFFSET関数 指定したセルまたはセル範囲から指定された行数と列数だけ移動した位置にあるセル範囲を返します =OFFSET(参照,行数,列数,[高さ], [幅])
INDEX関数 指定された行と列が交差する位置にあるセルまたはセルの参照を返します =INDEX(配列, 行番号, [列番号])
=INDEX(参照, 行番号, [列番号], [領域番号])

基準からの行と列の数え方の違い

OFFSET関数とINDEX関数では基準からの行と列の数え方に違いがあります。OFFSET関数は0から数えますが、INDEX関数は1から数えます。

関数 行、列の始点
OFFSET関数 基準を「0」として、1つ下の行は行数「1」を指定する
INDEX関数

基準を「1」として、1つ下の行は行番号「2」を指定する

行番号、列番号に「0」を指定すると行全体、列全体を返します

INDEX関数の詳しい使い方をチェック!

Excelの関数の中でもよく使用されるINDEX関数の引数を1つずつ分解して解説しています。

その他、OFFSET関数の便利な使い方

OFFSET関数の行数をMATCH関数で連動させる

OFFSET関数の行数にMATCH関数を入れ子(ネスト)すると、行数を連動させることができます。VLOOKUP関数のような検索が可能になります。

表の準備

上図では左側の表に「商品コード」と「商品名」があります。右側に商品コードに紐づいた商品名を検索して表示させます。

参照の指定

F3セルを選択し、『=OFFSET($B$3:$C$7,』と入力します。参照として左側の表を選択しています。

MATCH関数の入れ子

次にOFFSET関数の行数を指定します。E3セルに入力されている商品コードをMATCH関数を使って検索して行数を求めます。

F3セルの「=OFFSET($B$3:$C$7,」に続けて『MATCH(E3,B3:B7,0)-1,』と入力します。MATCH関数は1から数えますが、OFFSET関数は0から数えるため1を引いています。

列数の指定

OFFSET関数の列数、高さ、幅を指定します。F3セルの「=OFFSET($B$3:$C$7,MATCH(E3,B3:B7,0)-1,」に続けて『1,1,1)』と入力します。数式を確定させるためにEnterを押します。

関数の結果

F3セルにE3セルに入力された商品コード(1002)に紐づいた商品名「みかん」が表示されました。ではE3セルの商品コードを変更すると行数が自動的に変更させるのか確認してみましょう。

検索値を変更

E3セルに『1005』と入力したところ、MATCH関数で左側の表から商品コードの場所を検索することでF3セルが「ぶどう」に自動的に切り替わりました。

OFFSET関数でSUMIF関数の合計範囲を可変にする

SUMIF関数の合計範囲にOFFSET関数とMATCH関数を入れ子(ネスト)にすることで合計範囲を可変にすることができます。

合計したい範囲が月別などで切り替えることが多い場合に便利なので以下の記事でご紹介しています。

おすすめの商品をご紹介

MATCH関数の詳しい使い方をチェック!

OFFSET関数とよく組み合わせるMATCH関数の引数を1つずつ分解して解説しています。

OFFSET関数とMATCH関数の組み合わせは他にもプルダウンメニュー(ドロップダウンリスト)を連動するのに活躍します。

以下の記事で組み合わせ方を紹介しています。

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。

よろしければ参考にならなかった点をお聞かせください

CAPTCHA


Page Top