【Excel】OFFSET関数とは？指定した数だけシフトした位置にあるセル範囲を参照する関数

エクセルと言えば、オフィスワークのみならず、自宅での家計簿やスケジュール表などの作成など、さまざまなシーンで活躍する作業ツールです。

しかし、エクセルの関数には現在480種以上あるとも言われており、ひとつずつ覚えていくことは困難と言えます。

今回紹介するエクセルの関数は【OFFSET関数】です。

OFFSET関数の使い方や応用方法について、画像付きで解説していますので、ぜひ一緒に実践してマスターしてください。

OFFSET関数とは？

OFFSETという言葉の意味をご存じですか？これは英語で【相殺する】といった意味があります。

しかし、エクセルにおいては全く別の意味を持っています。

OFFSET関数とは、

行数
列数
高さ
幅

の4項目を指定して、答えを表示させたいセルに数値や文字列を返す関数です。

OFFSET関数の書式の意味

OFFSET関数のは以下の書式になります。

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

それぞれの引数の意味を説明します。

参照

最初の引数である第1引数は【参照】です。

参照したいテーブルデータをマウスで範囲指定、または手動でセル数値を入力します。

オートフィルを用いて複数のテーブルデータに使用する場合は【F4】キーを押して【絶対参照】にすることで、ずれることなく参照することができます。

行数

第1引数には【行数】が入ります。

この項目を入力しなければ関数の意味をなさないため、入力必須項目となります。

第1引数で参照したセルの左上から上、もしくは下へ移動させたい距離を数値を用いて指定します。

入力する数値は以下の通りです。

【0】	基準と同じ行の場合に用いる
【1】	基準よりも1つ下の行の場合に用いる
【2】	基準よりも2つ下の行の場合に用いる(3行目、4行目以降も同じイメージで数値を入力します)

また、【-1】とマイナス記号を付けることで、基準よりも上の行をしているすることができます。

列数

第2引数には【列数】が入ります。

列数も入力しなければ関数の効果を発揮しませんので、入力必須項目となります。

列数も行数と同じ要領で入力します。

【0】	基準と同じ列の場合に用いる
【1】	基準よりも1つ右の列の場合に用いる
【2】	基準よりも2つ右の列の場合に用いる(3列目、4列目以降も同じイメージで数値を入力します)

高さ

第3引数には【高さ】を入力します。

高さの引数は任意で入力します。

高さは結果を返したいセル範囲の行数を数値で設定します。

幅

第5引数は【幅】を入力します。

この引数も入力任意となります。

結果を参照したいセル範囲の列数を数値で指定します。

OFFSET関数の基本的な使い方

OFFSET関数の使い方について説明します。

このようなテーブルデータがあります。

あらかじめ商品コードのなかに【カーネーション】の商品コードが入力されています。

その横の商品名のセル内に、【カーネーション】をOFFSET関数を用いて返します。

C2セル内に、以下の関数を記入します。

=OFFSET($B$4:$E$6,1,2,1,1)

それぞれの引数を解説します。

$B$4:$E$6,	カスミソウの商品コードであるB4から、ランの価格までのE6までを範囲指定し、【絶対参照】させるため【F4】キーで閉じています。
1,	商品名の行を指定します。カーネーションは基準より一行下にあるため【1】を入力します。
2,	商品名の列数を指定します。カーネーションは基準より2列右にあるため【2】を入力します。
1,	結果を参照するセルの範囲を正の値である【1】で指定します。
1	結果を参照するセルの範囲を正の値である【1】で指定します。

以上を入力したら【Enter】を押します。

【カーネーション】がOFFSET関数によって返りました。

関数の挿入を用いてOFFSET関数を使う方法

関数を作成する場合、実は2通りの作成方法があることをご存じですか？

手入力で関数を作成する方法とは別に、【関数の挿入】というツールを使って作成する方法があるのです。

手入力で関数を作成する場合、関数で使用する引数の意味や個数をしっかりと把握しておかなければなりませんが、関数の挿入を使えば、引数部分にそれぞれ必要項目を入力するだけで関数を作成することが可能になります。

また、関数の挿入を使うことで、引数ごとに使用する演算子と呼ばれる【,】や【/】などの記号や、文字列を入力する際、必ず入力しなければならない【””】(ダブルクォーテーション)も、自動で入力してくれるため、エラーを防ぎながら関数を作成できます。

さらに関数にはそれぞれ【INDIRECT】や【OFFSET】など、英単語を入力しなければなりませんが、関数の挿入では頭文字さえ覚えていれば、入力した頭文字を含む関数を検索し、自動表示してくれる機能もあるため、エクセル初心者にかかわらず、上級者でもスペルに自信がない場合には重宝するツールなのです。

ですから、関数を作成する場合は、手入力だけでなく、関数の挿入と呼ばれるツールでも作成できることを覚えておき、シーンに合わせて使い分けられるようになることで、エラーを防ぎながら上手に関数を活用できるようになりますよ。

それでは実際に関数の挿入を使ってOFFSET関数を作成してみましょう。

OFFSET関数を作成したいデータのこの部分をクリックしましょう。

この【fx】と書かれた部分が【関数の挿入】になります。

クリックすると関数の挿入ダイアログボックスが開きます。

【関数の検索】の下にある文書を削除し、【OF】と入力し【検索開始】をクリックしましょう。

すると【OF】を含めた関数が一覧表示されるので【OFFSET】を選択し【OK】を押しましょう。

関数の引数ダイアログボックスが開いたら、

$B$4:$E$6,	カスミソウの商品コードであるB4から、ランの価格までのE6までを範囲指定し、【絶対参照】させるため【F4】キーで閉じています。
1,	商品名の行を指定します。カーネーションは基準より一行下にあるため【1】を入力します。
2,	商品名の列数を指定します。カーネーションは基準より2列右にあるため【2】を入力します。
1,	結果を参照するセルの範囲を正の値である【1】で指定します。
1	結果を参照するセルの範囲を正の値である【1】で指定します。

の通りに作成していきましょう。

【参照】には商品コード【1001-003】を含めたセルを範囲指定します。

手入力、またはマウスを使って範囲を指定しても問題ありません。

範囲指定したいセルを入力したら【F4】キーを押して【$】を付けます。

この作業をしておくことで、範囲指定したセルがズレてしまっても、【絶対参照】してくれるため、エラーになることを防いでくれます。

次に【行数】です。

カーネーションは基準より一行下にあるため【1】を入力します。

つづいて【列数】です。

カーネーションは基準より2列右にあるため【2】を入力します。

つづいて【高さ】です。

結果を参照するセルの範囲を正の値である【1】を指定します。

最後の引数は【幅】です。

こちらも、結果を参照するセルの範囲を正の値である【1】を指定します。

このように作成できたら【OK】を押しましょう。

関数の挿入を使って、手入力と同じようにOFFSET関数を作成することができました。

関数の挿入では、それぞれ引数の意味を説明してくれるため、非常にわかりやすく作成することができますから、ぜひこの機会に覚えておきましょう。

別シートからデータを抜き出す方法

↑商品リストシート

↑発注書シート

こちらにそれぞれ商品リストシートと、発注書シートの画像があります。

1枚目の商品リストシートには【ヒマワリ】が入力されていますが、下の発注書シートには【ヒマワリ】の文字が抜けています。

2枚目の発注書シート内のE5セルに、OFFSET関数を用いて【ヒマワリ】を参照させます。

その際の関数の書式は以下の通りです。

=OFFSET(商品リスト!A1,5,2,)

それぞれの引数には以下の意味があります。

商品リスト!	商品リストがあるエクセルシートのタイトル部分をクリックすることで【!】まで自動入力されます。
A1,	参照したいテーブルのあるシートの任意の基準セルをクリックします。
5,	A1の行を0から数えて【5行目】にあるので【5】を入力します。
2,	A1の列を0から数えて【2行目】にあるので【2】を入力します。

関数を入力したら【Enter】を押します。

別シートから【ヒマワリ】を返すことができました。

OFFSET関数と似ている関数【INDEX関数】との違い

OFFSET関数と同じように、行と列を指定したセル内に数値などを返しますが、OFFSET関数では、参照セルの範囲をさらに大きく指定可能です。

また、それぞれの関数では、数え方にも違いがあります。

OFFSET関数は【0】から、INDEX関数は【1】から数えます。

OFFSET関数の応用

OFFSET関数を使う場合、複数の関数を組み合わせることで、さまざまな用途に合わせて使いこなすことができます。

ここではOFFSET関数と主に組み合わせて使う

SUM関数
MATCH関数
CONTA関数

の応用方法について解説します。

OFFSET関数とSUM関数

OFFSET関数とSUM関数を組み合わせることで、既存のテーブル内の数値を合わせた合計値を指定したセル内に返すことができます。

B5からE5までの数値を足した合計値を、OFFSET関数とSUM関数を組み合わせて求めます。

この場合の書式は以下の通りです。

=SUM(OFFSET(A1,5,1,1,4))

関数を入力したら【Enter】を押しましょう。

B5からE5の数値をSUM関数で足した数値がB8の数値に返りました。

OFFSET関数とMATCH関数

OFFSET関数とMATCH関数の組み合わせでは、値を参照したい業と列の条件を可変的に設定することができます。

このようなテーブルデータがあるとします。

右のテーブル内に、OFFSET関数とMATCH関数を使ってそれぞれの商品名を入力します。

その際の書式は以下の通りです。

=OFFSET($B$3:$C$7,MATCH(E3,B3:B7,0)-1,1,1,1)

入力し終えたら【Enter】を押します。

OFFSET関数とMATCH関数を使い、1001－004の商品名である【ヒマワリ】を返すことができました。

OFFSET関数とCOUNTA関数

OFFSET関数の中にCOUNTA関数を組み込むことで、範囲の大きさを柔軟に変えられます。そもそもCOUTA関数とは、「範囲の中の空白ではないセルの個数」を求められる関数です。出席者のデータで返事が来た数をカウントしたいような場合に利用します。

OFFSET関数でVLOOKUP関数の上位互換を行う

VLOOKUUPとは、データの表を縦方向に検索し、取り出したいデータに対応する値を取り出す関数です。商品価格や在庫数の情報の検索を行う時などに使います。

ただしVLOOKUP関数は、縦方向にしかデータを検索できない上に、左側にデータがある場合は使えないという弱点があります。それをOFFSET関数を組み合わせることで補えます。

OFFSET関数とVLOOKUP関数を組み合わせて使うことで、VLOOKUP関数だけでは算出できない検索値よりも左側のセルを参照することができるようになります。

OFFSET関数におけるエラーの理由と解決方法

ここではOFFESET関数におけるエラーの理由と解決方法について解説します。

主なエラーには、

#NAME？
#SPILL!
求めたいセルとは違うセルが表示される
0と表示される
横に複数の項目が表示されてしまう

の5つがあります。

＃NAME?エラーの理由と解決方法

関数を作成するにあたり、【#NAME?】エラーになってしまう多くの理由には、関数の【スペル】を間違って入力している可能性があります。

この画像を見てわかる通り、【OFFSET】となっていなければならないのに【OFFSE】となってしまっています。

#NAME?エラーが出た場合は、関数の英単語のスペルを確認してみましょう。

＃SPILL!エラーの理由と解決方法

【#SPILL!】エラーの場合は、幅の指定がうまくできていない可能性があります。

画像でわかるように、幅の引数の部分が【11】となっており、うまく関数が作成できていないことが確認できます。

#SPILL!エラーの場合は、結果を参照するセルの範囲を正の値である【1】が入力できているかを確認してみましょう。

求めたいセルとは違うセルが表示される理由と解決方法

この画像の場合、商品名を参照したいのに対し、価格が表示されてしまっていることがわかります。

これは参照したいセルがB5の商品名からE6の価格までになっていることが理由となります。

この場合は範囲指定したいセルの確認を行うと解決します。

また、範囲指定したセル以外の理由も考えられます。

それが、2つ目の引数である行数です。

本来であれば参照したいセルは1行目にあるため【1】と入力されていなければならないのですが、打ち間違いなどによって【2】となっています。

行数の打ち間違いの可能性も考えられますから、それぞれの引数の入力ミスを疑うと良いでしょう。

0と表示される理由と解決方法

うまく関数を入力したはずなのに【0】と表示されてしまう場合は、第2引数の行数が【3】と入力されてしまっており、うまく参照できていない可能性が考えられます。

この場合も、引数の確認をし、必要に応じて数値を変えることで解決できます。

横に複数の項目が表示されてしまう理由と解決方法

関数をうまく入力できたはずなのに、表示されたものが横に広がってしまうことがあります。

これは第4引数と第5引数でうまく数値を入力できていない(省いている)可能性と、【,】が入力されていない可能性が考えられます。

この場合も、引数の個数と【,】を確認することで解決できます。

まとめ

エクセルには480種以上の関数があると言われています。

膨大な数の関数を、ひとつずつ覚えていくのは大変難しいことと言えます。

しかし、関数を使うシーンや、応用してどんな作業に活用できるかをイメージしながら覚えていけば、より簡単にインプットできるようになり、オフィスワークでのアウトプットも行えるようになります。

これからエクセルを覚えようと考えている方も、オフィスワークをよりスムーズにこなせるようになりたい方も、ぜひこの機会にOFFSET関数の使い方や応用をマスターし、エクセルの関数を着実に覚えていきましょう。