OFFSET関数：基準位置から指定の行数と列数離れたセルの参照を取得する
OFFSET 関数は Excel で用意されている関数の一つで、基準位置から指定した行数と列数だけ離れたセルへの参照を取得します。取得するセル参照の高さと幅を指定することもできます。ここでは Excel における OFFSET 関数の使い方について解説します。
※ Excel の対応バージョン ： 365 web 2021 2019 2016 2013 2010 2007
(Last modified: )
OFFSET関数の使い方
OFFSET 関数は基準位置から指定した行数と列数だけ離れたセルの参照を取得します。
OFFSET(参照,行数,列数,高さ,幅)
1 番目の引数に基準となるセルを指定します。 2 番目の引数に指定した行数と 3 番目の引数に指定した列数分だけ離れた位置にあるセルへの参照を返します。
下記では基準点として B2 、行数として 3 、列数として 2 を指定しています。結果として D5 セルへの参照が返されます。
関数の戻り値をセルにそのまま入力した場合は、戻り値として取得した参照先に入力されている値である 92 が表示されます。戻り値はセルへの参照なので、 SUM 関数のように引数にセル参照を指定できる関数の引数として OFFSET 関数を指定できます。
高さと幅を指定する
省略可能な 4 番目と 5 番目の引数で、取得するセル参照の高さと幅を指定することができます。例えば高さに 2 、幅に 3 を指定した場合、 OFFSET 関数の戻り値として D5:F6 のセル範囲への参照を返します。
戻り値としてセル範囲への参照を取得する場合は、配列数式を利用する必要があります。まず結果を表示するセル範囲を選択した選択した上で、数式バーに次のように入力してください。
=OFFSET(B2,3,2,2,3)
入力が終わったら Ctrl＋Shift＋Enter キーを押してください。あらかじめ選択していたセル範囲に OFFSET 関数が返すセル範囲への参照が表示されます。
※ 配列数式の使い方については「配列数式の利用方法」を参照されてください。
なお Microsoft 365 の環境であれば、戻り値を表示するセルに対して直接 =OFFSET(B2,3,2,2,3) を入力し、 Enter キーを押すだけで構いません。
基準位置にセル範囲を指定する
OFFSET 関数の 1 番目の引数には基準位置を示すセルを指定しますが、セル範囲を指定することもできます。その場合は、セル範囲の先頭のセルが基準位置となります。そしてセル範囲を指定した場合、 OFFSET 関数で高さと幅の引数を省略するとセル範囲の高さと幅が指定されたものとみなされます。
例えば次のように OFFSET 関数を入力したとします。
=OFFSET(B2:D3,3,2)
1 番目の引数にセル範囲 B2:D3 を指定しています。この場合基準位置は B2 セル となり、高さと幅がそれぞれセル範囲の高さと幅と同じに設定されるため、高さが 2 、幅が 3 と設定されたものと同じ扱いになります。なお高さや幅を引数で指定した場合は引数に指定した値が有効となります。
なお戻り値が複数のセルへの参照になる場合は、配列数式を使用する必要があります。幅と高さを指定した場合と同じように、あらかじめ戻り値を表示するセル範囲を選択した上で、数式バーで式を入力した上で、 Ctrl＋Shift＋Enter キーを押してください。
なお Microsoft 365 の環境であれば、戻り値を表示するセルに対して直接 =OFFSET(B2:D3,3,2) を入力し、 Enter キーを押すだけで構いません。
OFFSET関数のサンプル
それでは実際に OFFSET 関数を使ってみます。 Excel のシートに対象の数値を次のように入力しました。
取得したセルの参照先に入力されている値を表示する C10 セルを選択し、次のように入力しました。 1 番目の引数に基準位置となる B2 セル、 2 番目の引数に行数が入力されている C8 セル、 3 番目の引数に列数が入力されている C9 セルを指定しました。
=OFFSET(B2,C8,C9)
Enter キーを押すと、 C10 セルには次のように表示されます。基準位置からデフォルトで入力していた 1 行 1 列離れた C3 セルの参照を取得し、そのセルに入力されている値が表示されました。
それでは行数を指定する C8 セルに 3 を入力したあとで Enter キーを押すと、 C10 セルには基準位置から 3 行 1 列離れた C5 セルの参照を取得し、そのセルに入力されている値が表示されました。
OFFSET関数の利用方法(1):検索で一致した行の任意の列のデータを取得する
OFFSET 関数と MATCH 関数を組み合わせることで、指定した文字列と一致する行を取得し、その行にある任意の列のデータを取得できます。次のシートを見てください。
氏名を入力すると住所を表示するようにします。結果を表示する C11 セルに次のような数式を入力しました。
=IFERROR(OFFSET(D2,MATCH(C10,C2:C8,0)-1,0),"--")
MATCH 関数を使って C10 セルに入力された値をセル範囲 C2:C8 で検索してインデックスを取得します。そして OFFSET 関数を使って D2 セルを起点として行数に先ほど MATCH 関数を使って取得したインデックス - 1 、列数に 0 を指定して値を取得しています。 IFERROR 関数は MATCH 関数で値が検索できなかったときにエラーの代わりに "--" を表示するために使用しています。
C10 セルに検索する名前を入力すると、住所を C11 セルに表示します。
OFFSET 関数と MATCH 関数を組み合わせることで VLOOKUP 関数と似た処理を行うことができました。
※ MATCH 関数の使い方については「MATCH関数：指定の範囲を検索し範囲内での相対位置を取得する」を参照されてください。
-- --
Excel における OFFSET 関数の使い方について解説しました。
( Written by Tatsuo Ikura )
著者 / TATSUO IKURA
これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。