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

  • 2022年2月20日
  • 2022年2月24日
  • Excel
定時でカエル
Excel(エクセル)のOFFSET関数が難しくて全然分からないです。。。
オフィスうさぎ
原理が分かればOFFSETもシンプルな関数だよ。
この記事ではOFFSET関数について世界一分かりやすく解説していくね!

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

エクセルのOFFSET関数とは

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

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

ExcelのOFFSET関数

  • 黄色部分がスタート地点となる基準セル
  • 赤矢印の行・列の移動指示
  • 青矢印が高さ・幅の範囲指示
  • 緑色部分が最終のセル範囲

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

エクセルのOFFSET関数の書式

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

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

ExcelのOFFSET関数

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

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

ExcelのOFFSET関数

  1. B2が基準セル
  2. 基準セルから4行下に移動
  3. さらに2列右に移動
  4. 高さ5×横3の範囲を選択

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

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

OFFSET関数の第1引数の解説

OFFSET関数の基準セル

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

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

OFFSET関数の基準セル

OFFSET関数の第2引数の解説

OFFSET関数の行数

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

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

OFFSET関数の行数

OFFSET関数の第3引数の解説

OFFSET関数の列数

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

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

OFFSET関数の列数

OFFSET関数の第4引数の解説

OFFSET関数の高さ

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

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

OFFSET関数の高さ

OFFSET関数の第5引数の解説

OFFSET関数の幅

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

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

OFFSET関数の幅

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

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

OFFSET関数を単独で使う

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

1から15の表からOFFSET関数を使って14を参照してみましょう
エクセルのOFFSET関数
=OFFSET(B2,3,2,1,1)

エクセルのOFFSET関数

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

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

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

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

F3に商品名を記載したとき、OFFSET関数を使ってF4に価格が自動入力されるようにしなさい
OFFSET関数とMATCH関数を組み合わせて使う
=OFFSET(B3,MATCH($F$3,B3:B7,0)-1,1,1,1)

OFFSET関数とMATCH関数

第1引数の解説

OFFSET関数とMATCH関数を組み合わせて使う
OFFSET関数の第1引数は基準となるセルを指定します。
ここでは選択肢の先頭にあたるB3が基準セルとなります。

第2引数の解説

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

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関数とMATCH関数を組み合わせて使う
そのため=OFFSET(B3,MATCH($F$3,B3:B7,0)-1,1,1,1)という感じで、MATCH関数(***)-1としています。
MATCH関数の詳しい解説については、こちらの「世界一分かりやすいエクセルの『MATCH関数』の解説」をご覧ください。
関連記事

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

ExcelのMATCH関数の使い方

第3引数の解説

OFFSET関数とMATCH関数を組み合わせて使う
OFFSET関数の第3引数には、第2引数で指示したセルから右側に何列移動するかを指示します。
ここでは1列隣にある価格列を参照したいので1と入力します。

第4・5引数の解説

OFFSET関数とMATCH関数を組み合わせて使う
OFFSET関数の第4引数と第5印数には、移動後のセルを起点に「縦×横」で選択する範囲を指示します。
第4印数が「縦」で第5印数は「横」を指示しますが、今回は価格列の1つのセル(1×1)のみを参照するため縦横ともに1を入力するのが正解です。

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

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

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

指定した順位までの合計値をOFFSET関数を使って自動で計算する

OFFSET関数とSUM関数と組み合わせ

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

=SUM(OFFSET(D3,0,0,F3,1))

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

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

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

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

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

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

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

第1引数の解説

OFFSET関数とSUM関数と組み合わせて使う
OFFSET関数の第1引数には基準となるセルを指定します。
ここでは点数の先頭にあたるD3が基準セルとなります。

第2引数の解説

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

OFFSET関数の第2引数には、基準となるセルから下向きに何行移動するかを指示します。
今回のケースでは基準セルから行は移動しないため、0と入力しておきましょう。

第3引数の解説

OFFSET関数とSUM関数と組み合わせて使う
OFFSET関数の第3引数には、第2引数で指示したセルから右側に何列移動するかを指示します。
今回のケースでは基準セルから列は移動しないため、0と入力しておきましょう。

第4引数の解説

OFFSET関数とSUM関数と組み合わせて使う
OFFSET関数の第4引数では、移動後のセルを起点に縦方向の選択範囲を指定します。
今回は指定した順位によって縦方向の選択範囲を変更したいため、順位を指定するF3を参照しておきます。

第5引数の解説

OFFSET関数とSUM関数と組み合わせて使う
OFFSET関数の第5引数では、移動後のセルを起点に横方向の選択範囲を指定します。
今回のケースでは基準セルの列のみ選択すればいいので、1を入力しておきましょう。
OFFSET関数とMATCH関数を組み合わせた関数のより詳しい解説は、こちらの「執筆中です」をご覧ください。