世界一分かりやすいエクセルの『OFFSET関数』の解説。=OFFSET()の使い方や他関数との組み合わせ

この記事ではエクセルのOFFSET関数の使い方や組み合わせついて、どこよりも分かりやすく解説していきます。

エクセルのOFFSET関数とは

エクセルのOFFSET関数とは、指定した移動指示に当てはまるセル範囲を参照してくれる関数です。

こちららの画像はOFFSET関数の仕組みを図式化したものです。

このように指定する条件に合わせてセル範囲を参照できる関数なので、他の関数やエクセルの様々な機能と組み合わせて使うことができます。

エクセルのOFFSET関数の書式

エクセルのOFFSET関数の書式がこちらです。

つまり、OFFSET関数は「基準となるセルから指定した行数・列数だけ移動し、指定した縦数・横数を参照する関数」になります。

上記の事例を関数にすると=OFFSET(B2,4,2,5,3)になります

今回の事例で紹介した=OFFSET(B2,4,2,5,3)では、「基準となるB2から4行下に移動、2列右に移動した後に、5×3のセル範囲を参照する」ことを意味しています。

OFFSET関数の仕組みたったこれだけのことですが、より分かりやすく説明するためにOFFSET関数を引数に分けて解説してみます。

OFFSET関数の第1引数の解説

OFFSET関数の第1引数には基準となるセルを入力します。

移動する前の最初のスタート地点を意味しますので、こちらの事例であればB2が基準セルにあたります。

OFFSET関数の第2引数の解説

OFFSET関数の第2引数では、基準セルから下向きに何行移動するかを指示します。

こちらの事例であれば下向きに4行移動しているので、第2引数には4と入力するのが正解です。

OFFSET関数の第3引数の解説

OFFSET関数の第3引数では、第2引数の指示で移動したセルから右向きに何列移動するかを指示します。

こちらの事例であれば右向きに2行移動しているので、第3引数には2と入力するのが正解です。

OFFSET関数の第4引数の解説

OFFSET関数の第4引数では、第3引数で移動したセルを起点に縦方向のセル範囲を指示します。

こちらの事例であれば縦5×横3の範囲でセルを選択しているので、第4引数には5と入力するのが正解です。

OFFSET関数の第5引数の解説

OFFSET関数の第5引数では、第3引数で移動したセルを起点に横方向のセル範囲を指示します。

こちらの事例であれば縦5×横3の範囲でセルを選択しているので、第5引数には3と入力するのが正解です。

エクセルのOFFSET関数の実践問題

ここからはOFFSET関数をどのように使うのかを、実践形式の練習問題を出しながら解説していきます。

OFFSET関数を単独で使う

OFFSET関数を単独で使うことは実務ではあまりありませんが、仕組みを理解するためにまずは単独での使い方を解説します。

=OFFSET(B2,3,2,1,1)は「左上のB2セルを基準に3行下・2列右に移動して、そのセルを起点に1×1の範囲を参照」と指示しています。

OFFSET関数とMATCH関数を組み合わせて使う

実務ではOFFSET関数を単独で使うことはなく、他の関数と組み合わせて使うことがほとんどです。

なかでもOFFSET関数とMATCH関数を組み合わせて使うことが多く、マスターすれば出来ることがかなり拡がる便利な組み合わせです。

第1引数の解説

第2引数の解説

OFFSET関数の第2引数には、基準となるセルから下向きに何行移動するかを指示します。

ここではF3に入力された文字列と合致するB列のセルを指定したいため、MATCH関数を組み合わせてMATCH($F$3,B3:B7,0)-1と入力します。

MATCH関数は「指定した条件にあう文字列や数値が、対象とする範囲の中で何行目のセルにあるかを示す関数」です。

例えばF3に”カラムーチョ”と入力した場合、カラムーチョはB3～B5の中で上から5行目のセルにあるので、=MATCH($F$3,B3:B7,0)が指す数値は5になります。

このようにMATCH関数では指定した文字列が何行目にあるかを示すことができるので、OFFSET関数の第2引数(行)にMATCH関数を組み合わせることで、指定した文字列がある行まで移動指示を出すことができという仕組みです。

ちなみにMATCH関数が先頭の行をカウントするのに対して、OFFSET関数では先頭の行はカウントしません。

そのため=OFFSET(B3,MATCH($F$3,B3:B7,0)-1,1,1,1)という感じで、MATCH関数(***)-1としています。

MATCH関数の詳しい解説については、こちらの「世界一分かりやすいエクセルの『MATCH関数』の解説」をご覧ください。

関連記事

世界一分かりやすいエクセルの『MATCH関数』の解説。=MATCH()の使い方や他関数との組み合わせ

定時でカエルExcel(エクセル)のMATCH関数について教えてください！オフィスうさぎこの記事ではMATCH関数について世界一分かりやすく解説していくね！この記事ではエクセルのMATCH関数[…]

第3引数の解説

OFFSET関数の第3引数には、第2引数で指示したセルから右側に何列移動するかを指示します。

ここでは1列隣にある価格列を参照したいので1と入力します。

第4・5引数の解説

OFFSET関数とSUM関数と組み合わせて使う

エクセルの関数の中で一番基本とも言えるSUM関数も、OFFSET関数と組み合わせることでより便利に使うことができます。

ここではOFFSET関数とSUM関数を組み合わせた使い方を解説します。

F3のセルに1～10の任意の数値を入力したときに、入力した順位までの合計値がH3に表示されるようにしてみましょう。

求めたい合計の対象範囲が指定する順位によって変動するため、SUM関数の範囲をOFFSET関数を使って指示してあげます。

OFFSET関数では「1位の点数であるD3のセルを起点にして、高さ(=F3)を指定してセル範囲を参照する」ように指示をしていきます。

例えばF3に5と入力すれば、=SUM(OFFSET(D3,0,0,5,1))となり1位から5位までの合計値を表示します。

F3に8と入力すれば、=SUM(OFFSET(D3,0,0,8,1))となり1位から8位までの合計値を表示します。

第1引数の解説

第2引数の解説

第3引数の解説

OFFSET関数の第3引数には、第2引数で指示したセルから右側に何列移動するかを指示します。

今回のケースでは基準セルから列は移動しないため、0と入力しておきましょう。

第4引数の解説

OFFSET関数の第5引数では、移動後のセルを起点に横方向の選択範囲を指定します。

今回のケースでは基準セルの列のみ選択すればいいので、1を入力しておきましょう。

OFFSET関数とMATCH関数を組み合わせた関数のより詳しい解説は、こちらの「執筆中です」をご覧ください。