Excel(エクセル)のOFFSET関数(オフセット)は、マイナーな関数ですが、他の関数と組み合わせるとその実力を発揮する特殊な関数です。
OFFSET関数は、『基準セルから指定した分だけ移動した位置にある単一セルやセル範囲を選択』する関数です。
OFFSET関数は何が便利なのかまったく分からない方は多いでしょう。
しかし、OFFSET関数は、VLOOKUP関数やXLOOKUP関数、SUMIF関数、MATCH関数など、様々な関数と組み合わせることで、引数の設定を自動化するなどのメリットがあります。
この記事では、OFFSET関数の使い方と他の関数と組み合わせた事例をわかりやすく紹介していきます。
OFFSET関数(オフセット)とは？
OFFSET関数とは、『基準のセルから指定した分だけ移動した位置にある単一セルやセル範囲を選択』する関数です。
詳しくは下の見出しから詳しく紹介しますが、OFFSET関数のイメージ図は以下のようになります。
例えば、下のC10セルに『=OFFSET(B2,4,2,1,1)』と入力した場合は、『B2セルを基準として、4行下&2列右に基準セルを移動し、その位置から縦1セル、横1セル分のセルの値(=D6)』が返されます。
まずは、大まかに『OFFSET関数は、指定した分だけ移動して、単一セルやセル範囲を選択する関数』と覚えてください。
OFFSET関数は単体で使用するケースは少なく、VLOOKUP関数などの他の関数と組み合わせることで効果を発揮します。
ちなみにOFFSET関数は、Microsoft365(サブスク)やExcel2016以降のバージョンで利用できます。
OFFSET関数の書式と引数
OFFSET関数の書式は以下のとおりで、５つの引数を指定します。
=OFFSET(基準,行数,列数,[高さ],[幅])
OFFSET関数の引数
第１引数『基準』・・・基準となるセルやセル範囲を指定(例：B3、B:B）
第２引数『行数』・・・基準セルから何セル分、縦に移動するか指定(例：-2、2、0)
第３引数『列数』・・・基準セルから何セル分、横に移動するか指定(例：-2、2、0)
第４引数『高さ』・・・移動後の基準セルから何行分のデータを取得したいか数値で指定。１の場合は、省略可能((例：１，４)
第５引数『幅』・・・移動後の基準セルから何列分のデータを取得したいか数値で指定。１の場合は、省略可能(例：２，３)
OFFSET関数の動きのイメージは、下の表の①⇒②⇒③⇒④⇒⑤の順番です。
基準セル(①)が移動して(②③)、指定した大きさのデータ(④⑤)が返されます。
=OFFSET(B2,4,2,1,1)
【基本編】OFFSET関数の使い方(３パターン)
OFFSET関数を実際に使ってみましょう。
OFFSET関数は３パターンの使い方があるので、ここで基本的な動きを押さえてください。
OFFSET関数/使い方１：単一セルを参照して返す
まずは、一番シンプルなOFFSET関数の使い方です。
H2セルに以下のOFFSET関数を挿入します。
B2セルを基準にして、下に４セル移動、右に２セル移動し、そこから縦１横１セル分のデータであるD6セルの値が返されます。
=OFFSET(B2,4,2,1,1)
第4、5引数が「1」の場合は省略できるので、「=OFFSET(B2,4,2)」としても、同じ結果が返されます。
OFFSET関数/使い方２：セル範囲の値を返す
２つ目のパターンは、OFFSET関数でセル範囲の値を返す使い方です。
H2セルに以下のOFFSET関数を挿入します。
B2セルを基準にして、下に３セル移動、右に２セル移動し、そこから縦3横2セル分のデータであるD5:E7のセル範囲の値が隣接したセルに拡張されて返されます。
=OFFSET(B2,3,2,3,2)
OFFSET関数はH2セルに挿入しましたが、それ以外のセルにも結果が表示されます。この機能のことをスピルといいます。
OFFSET関数/使い方３：第１引数を列番号で使用
３つ目のパターンは、OFFSET関数の第１引数を列番号で指定する使い方です。
H2セルに以下のOFFSET関数を挿入します。
第１引数を「$B:$B」と指定するとB1セルが基準セルとして扱われます。このように列番号を使用すると、表に行を挿入したり、OFFSET関数をコピペしても、基準セルが移動することを防げます。
=OFFSET($B:$B,3,2,3,2)
OFFSET関数とVLOOKUP関数を組み合わせる方法
VLOOKUP関数の中にOFFSET関数を組み合わせることで、VLOOKUP関数を複数のセルで利用する際に、参照先のセルを自動で変更させることができます。
また、OFFSET関数の中には、データが入力されているセルの数をカウントするCOUNTA関数も使用します。
VLOOKUP関数とCOUNTA関数の使い方が分からない方は、こちらも合わせて参考にしてください。
＝VLOOKUP(検索値,検索範囲,列番号,[検索方法]）
＝COUNTA(値 1, [値 2], ...))
⇩⇩VLOOKUP関数とCOUNTA関数の使い方
下の表から商品番号「25」の「商品名」を検索するために、H3セルに以下のVLOOKUP関数を挿入します。
=VLOOKUP(G3,OFFSET($B$2,0,0,COUNTA(B:B)-1,4),3,FALSE)
VLOOKUP関数の第２引数「検索範囲」は、「OFFSET($B$2,0,0,COUNTA(B:B)-1,4)」と入力することで、B2セルを基準に、B列のデータ入力数分だけ下に、4列右に移動した分のセル範囲を選択しています。
VLOOKUP関数を確定すると、商品番号「25」の商品名「風邪薬」が返されます。
この数式をH4セルにコピーしても、同様に、商品番号「78」の商品名「消臭剤」が返されます。このように数式をわざわざ変更しなくて済むのがOFFSET関数を使うメリットです。
VLOOKUP関数と同様に、横方向に検索するHLOOKUP関数にもOFFSET関数は利用できるので、ぜひ試してみてください。
OFFSET関数とXLOOKUP関数を組み合わせる方法
XLOOOKUP関数は、VLOOKUP関数の進化版の後継関数です。
XLOOKUP関数の中にOFFSET関数を組み合わせることで、検索値のすべての項目のデータを一括で抽出することができます。
また、OFFSET関数の中には、データが入力されているセルの数をカウントするCOUNTA関数も使用します。
XLOOKUP関数の使い方が分からない方は、こちらも合わせて参考にしてください。
＝XLOOKUP(検索値,検索範囲,戻り範囲, [見つからない場合] ,[一致モード] ,[検索モード] ）
⇩XLOOKUP関数の使い方
下の表から商品番号「78」の「商品ｶﾃｺﾞﾘ」「商品名」「値段」を検索するために、G7セルに以下のVLOOKUP関数を挿入します。
=XLOOKUP(G3,OFFSET($B$2,0,0,COUNTA(B:B)-1,1),OFFSET($B$2,0,0,COUNTA(B:B)-1,4),FALSE)
XLOOKUP関数の第２引数「検索範囲」に「OFFSET($B$2,0,0,COUNTA(B:B)-1,1)」と入力することで、B2セルを基準に、B列の表内のセル範囲を選択しています。
さらに、第３引数「戻り範囲」に「OFFSET($B$2,0,0,COUNTA(B:B)-1,4)」と入力して、表内のB列からE列までの４列分のセル範囲を選択しています。
XLOOKUP関数を確定すると、右隣りのセルにも結果が反映されて、商品番号「78」の「商品ｶﾃｺﾞﾘ」「商品名」「値段」が返されます。(スピル機能)
OFFSET関数を使ったその他の事例
OFFSET関数は、セル範囲を選択する関数なので、検索したり抽出する関数との相性が良いです。
VLOOKUP関数やXLOOKUP関数以外にも、さまざまな関数にOFFSET関数は利用できます。
最後に、Excelでよく利用される関数とOFFSET関数を組みわせた実例を紹介すので、実践に使えるか参考にしてください。
OFFSET関数/事例１：SUM関数に使用する
SUM関数の中にOFFSET関数を挿入することで、合計する範囲を自動で変更することができます。
例えば、降順で並んでいる獲得件数表に、以下のSUM関数を挿入します。
引数「セル範囲」に「OFFSET(D3,0,0,F3,1)」と入力することで、D3セルからF3セルに入力した数値までの順位の数値を合計することができます。
=SUM(OFFSET(D3,0,0,F3,1))
F3セルの値を「3」から「5」に変更すると、OFFSET関数によりSUM関数のセル範囲が自動で変更されるので、１位から５位までの合計値が自動で表示されます。
OFFSET関数/事例２：MATCH関数に使用する
OFFSET関数の中にMATCH関数を挿入することで、表の中から指定した縦項目と横項目がクロスする位置の値を抽出することができます。
MATCH関数は、指定した検索範囲内で検索値がどの位置にあるかを数値で返す関数です。
＝MATCH(検査値,検査範囲,[照合の型])
⇩MATCHの使い方
例えば、下の料金表を使って、G10セルに以下のOFFSET関数を挿入します。
OFFSET関数の第１引数「基準値」を表の左上のB2セルにします。第2引数と第3引数にそれぞれMATCH関数を挿入して、C9セルとC10セルの位置まで基準値を移動させて、料金表から該当する料金を検索します。
=OFFSET(B2,MATCH(C9,B3:B7,0),MATCH(C10,C2:G2,0))
C9セルとC10セルの条件を変更すると、検索したい料金が自動で更新されて出力されます。
OFFSET関数/事例４：SUMIF関数に使用する
SUMIF関数を複数のセルに利用する際、参照セルを１つずつ変更していると時間がかります。
そんな時、SUMIF関数の中にOFFSET関数を挿入することで時短することができます。
例えば、下の表から、「5月」の「A製品」の合計数量を計算するために、以下のSUMIF関数を挿入します。
=SUMIF($B$3:$B$8,I2,OFFSET($B$3,0,MATCH($H$3,$C$2:$F$2,0)))
この数式のポイントは、SUMIF関数の第3引数「合計範囲」に「OFFSET($B$3,0,MATCH($H$3,$C$2:$F$2,0))」と入力することです。
OFFSET関数の基準セルをB3セルに設定して、H3セルに入力した「５月」に合う合計範囲が自動で指定されます。
数式を確定すると、「5月」の「A製品」の合計数量「55」が算出されます。さらに、数式をJ3、K3セルにコピーすると、「B製品」「C製品」の合計数量も計算することができます。
このように、SUMIF関数を複数のセルに使用する際、OFFSET関数を利用することで、１セルずつSUMIF関数の引数を変更する必要がなくなります。
OFFSET関数とINDEX関数の違い
OFFSET関数に似た関数に、INDEX関数があります。
INDEX関数は、指定した行と列が交差するセルの値を抽出する関数です。そのため、OFFSET関数とほぼ同じ機能を持っています。
=INDEX(配列, 行番号, [列番号])
=OFFSET(基準,行数,列数,[高さ],[幅])
ただ、OFFSET関数とINDEX関数の違いは２点あります。
まずは、上の書式を見てもらって分かるとおり、OFFSET関数は引数が５つあるのに対して、INDIRECT関数は３つです。この違いは、指定できるセル範囲の大きさです。
つまり、下の図のように、OFFSET関数は参照するセル範囲の大きさを自在に指定できますが、INDIRECT関数は１セルしか参照できません。
２つ目の違いは、行番号と列番号の数え方が異なります。
OFFSET関数は、左下の表のように、B2セルの位置を「0」として、B3セルを行番号「１」移動、C2セルを列番号の「１」と認識します。
一方、INDEX関数は、右下の表のように、B2セルの位置を「1」として、B3セルを行番号「2」、C2セルを列番号の「2」と認識します。
そのため、この違いを理解していないと、最終的に参照するセルの位置を誤ってしまうので注意が必要です。
INDEX関数の使い方を確認したい方は、以下の内部リンク記事を参考にしてください。
