エクセルOFFSET関数の使い方! MATCH関数と組み合わせて可変するセル範囲に対応

エクセルOFFSET関数とMATCH関数を組み合わせて、範囲が変わっても対応できる方法を紹介しています。数式を修正する必要はありません!入力ミスや修正が防げます。VOOKUPでエラーがでる場合でも、OFFSET関数とMATCH関数を組み合わせた検索では、エラーがでません。VLOOKUP関数にはできない検索ができます。

 

 

OFFSET関数とMATCH関数を使った検索
エクセルOFFSET関数とMATCH関数を使った検索を、2つ紹介します。

2つの条件に一致する値を検索する方法

OFFSET関数とMATCH関数を使って、2つの条件が交わる値を検索します。

コースと年齢別の「入園料」を入園料金表から検索しましょう。

 

「G4」に「公園入園」「大人」の「入園料」を検索する式を入力しましょう。

G4 =OFFSET(A3,MATCH(G2,A4:A7,0),MATCH(G3,B3:D3,0))

「3,500」が返されました!

「G4」の式はOFFSET関数の「行数」と「列数」をMATCHで指定しています。

「=MATCH(G2,A4:A7,0)」で「公園入園」の位置を返しています。この位置の番号が「行数」になります。

「=MATCH(G3,B3:D3,0)」で「大人」の位置を返しています。この位置の番号が「列数」です。

「公園入園」と「大人」が交わる位置が「、公園入園」「大人」の「入園料」になります。

「G2」と「G3」に検索する文字列を入力すると、「G4」に「入園料」が表示されます!

 

 

返す値の列が変動する場合の検索方法

VLOOKUP関数では、検索範囲の一番左の列を検索して指定した列から値を返します。値を返す列は検索範囲の一番左から数えた列番号で指定し、検索範囲より左側に指定できません。OFFSET関数とMATCH関数を使うと、列番号が可変する検索が柔軟にできます。

 

セル範囲「A1:E8」に契約情報が入力されています。「契約№」「BQ20389」の「金額」と「契約者名」を検索しましょう。

最初に「金額」を検索する式を作成します。「契約者名」を検索する式はこの式をコピーしましょう。OFFSET関数の「列数」だけ修正すると「契約者名」が検索できます。

「H3」に「契約№」「BQ20389」の「金額」を返す式を入力します。「H4」に「契約者名」を返す式を入力しましょう。

H3 =OFFSET($B$1,MATCH($H$2,$B$2:$B$8,0),3)

「32,000」を返しました!

「H3」の式を「H4」に代用するので、OFFSET関数の「基準」MATCH関数の「 検査値」「 検査範囲」は絶対値にします。

「H3」の式を「H4」にコピーして、OFFSET関数の「列数」を「-1」に修正しましょう。

H4 =OFFSET($B$1,MATCH($H$2,$B$2:$B$8,0),-1)

「H4」の式「 =OFFSET($B$1,MATCH($H$2,$B$2:$B$8,0)」の部分は「H3」同じです。「契約者名」の列はOFFSET関数の「基準」から「-1」ですので「列数」が「-1」になります。

「ミズノ ミナ」を返しました!

 

 

OFFSET関数で可変範囲の合計

OFFSET関数を使うと合計の範囲を自在に変更できます。合計範囲が変動する場合、式を再度入力するのは面倒です。OFFSET関数とSUM関数を使用すると可変範囲の合計ができます。

 

セル範囲「B2:D6」に商品の月毎の売上個数が入力されています。商品と月は増えていきます。「G3」に売上個数の合計を算出する式を入力しましょう。

G3 =SUM(OFFSET(A1,1,1,5,3))

結果は、売上個数の合計「276」を返します。

 

商品と月が増えました。「G3」のOFFSET関数の式の「高さ」と「幅」を変更しましょう。

G3 =SUM(OFFSET(A1,1,1,6,4))

売上個数の合計「425」を返しました!

行が1つ列が1つ増えたので「高さ」と「幅」を1つ足します。「高さ」が「6」「幅」が「4」になります。

 

 

関数の説明

OFFSET関数

基準のセルから指定された、行数と列数だけシフトした位置にある、セルの参照を返します。

OFFSET関数の書式と引数

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

基準 基準となるセルまたは隣接するセル範囲を指定します。
行数 基準の左上隅のセルを、上方向または下方向へシフトする行数を指定します。行数が正の場合、下方向へ負の場合上方向へシフトします。
列数 基準の左上隅のセルを、左方向または右方向へシフトする列数を指定します。行数が正の場合、右方向へ負の場合左方向へシフトします。
高さ オフセット参照の行数を指定します。
オフセット参照の列数を指定します

※「高さ」と「幅」は省略できます。

OFFSET関数の使用例

F3に式を入力しましょう。

F3 =OFFSET(A1,3,2,1,1)

セル「A1」を基準として「A1」から行数「3」、列数「2」シフトしたセル参照は「C4」です。

「C4」のセルの値、「9」が返されます。

 

 

MATCH関数

指定した範囲内から検査値を探しだし、その検査値の相対的な位置を返します。

MATCH関数の書式と引数

MATCH(検査値, 検査範囲, [照合の型])

検査値 検索する値を指定します。
検査範囲 検索するセルの範囲を指定します。
照合の型 -1、0、1 の数値のいずれかを指定します。省略した場合は自動的に1が選択されます。1は、検査値以下の最大の値を返します。この場合、検査範囲を昇順で並べ替えする必要があります。0は、検査値と一致する一番最初の値を返します。行の場合は左から、列の場合は上から最初の値を返します。-1は、検査値以上の最小の値を返します。この場合、検査範囲を降順に並べ替えする必要があります。

MATCH関数の使い方

セル範囲「A2:D2」に「コード」が入力されています。「C2」には「コードC」が入力されています。「コードC」の位置を返す式を「F3」に入力します。検索範囲は「A2:D2」です。「F3」には検索する値「コードC」が入力されています。

数式 結果
F3=MATCH(F2,A2:D2,0) 「コードC」の位置「3」を返します。

 

行番号が可変する検索もできます。

エクセルOFFSET関数とMATCH関数を使うと、VLOOKUP関数ではできない、列番号が可変する検索が柔軟にできます。同様にHLOOKUP関数ではできない、行番号が可変する検索もできます。

この場合、OFFSET関数の「列数」にMATCH関数を使います。OFFSET関数とMATCH関数の組み合わせは範囲が変動する場合によく使われる関数です。

こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って複数条件で値を取得する