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

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

OFFSET関数とMATCH関数を使った検索

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

1 2つの条件に一致する値を検索する方法
2 返す値の列が変動する場合の検索方法
3 OFFSET関数で可変範囲の合計
4 OFFSET関数
- 4.1 OFFSET関数の書式と引数
- 4.2 OFFSET関数の使用例
5 MATCH関数
- 5.1 MATCH関数の書式と引数
- 5.2 MATCH関数の使い方
6 行番号が可変する検索もできます。

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は、検査値以上の最小の値を返します。この場合、検査範囲を降順に並べ替えする必要があります。