ExcelのOFFSET(オフセット)関数は基準となるセルを指定し、基準となるセルから行数、列数で指定した値の位置にあるセルを参照します。書式は、OFFSET(基準,行数,列数,[高さ],[幅])です。「高さ」、「幅」を指定すると、参照したセルからの範囲指定もできます。
ここではExcelでOFFSET関数とMATCH関数を組み合わせた、セルの検索の手順について説明します。
目次
ExcelのOFFSET関数でセルを検索する5つの手順
Excelで作成した月別販売実績表で説明します。支店名と月を検索値にして、OFFSET関数とMATCH関数を組み合わせて検索します。MATCH関数で支店名と月の位置を取得し、OFFSET関数にその値を代入し、検索結果を表示させます。
1.表の確認
セルC3に検索する支店名、セルC4に検索する月それぞれの入力欄があります。
セルC5に関数を入力します。
2.検索値の入力
ここでは支店名に「B支店」、月に「7月」を入力します。
3.関数の入力
OFFSET関数の「基準」をB8にします。
MATCH関数の検索範囲を「支店名」はB9からB11、「月」はC4からC8にします。
セルC5に
=OFFSET(B8,MATCH(C3,B9:B11,0),MATCH(C4,C8:H8,0))
を入力します。
4.検索結果の確認
セルC5にB支店の7月の販売実績が表示されました。
5.エラーの回避
検索値の入力欄が入力されていないと「#N/A」のエラー表示が出るので、IF関数を使って回避します。
セルC5の関数を
=IF(AND(C3<>“”,C4<>“”),OFFSET(B8,MATCH(C3,B9:B11,0),MATCH(C4,C8:H8,0)),””)
に修正します。
入力した検索値がない場合は「#N/A」のエラーが表示されます。この使い方以外にも、「高さ」にCOUNTA関数でカウントした列のデータ個数をセットすれば、データが追加、削除されても、常にデータのある範囲を指定できます。ExcelのOFFSET関数は、理解すると便利な関数なので活用してください。
まとめ
ExcelのOFFSET関数でセルを検索する5つの手順
- 表の確認
- 検索値の入力
- 関数の入力
- 検索結果の確認
- エラーの回避