Excel関数「OFFSET」指定した参照から指定した行数、列数の範囲への参照を返す

 

 

どうも！アメビンです。

今日も娘がExcelについて悩んでいる様子です。

 

Excel関数「OFFSET」って何？

 

指定した参照から指定した行数、列数の範囲への参照を返す場合は

「OFFSET関数」を使用します。

式の書き方は

「=OFFSET(参照,行数,列数, [高さ], [幅])」

となります。

 

早速ですが、使い方を紹介させて頂きます！

※「MATCH関数」は別の回で解説します。

 

 

1限目「OFFSET」使い方：検索値の列とは

おさらいですが、以前解説した→

の解説では「番歌」を指定する事で

「DB（データベース）」から「詠み人」「歌」「歌（読み）」

を抽出して表示させるという方法でした。

 

こちらに「詠み人」を選択して

同じ「DB（データベース）」から「番歌」「歌」「歌（読み）」

を抽出して表示させるという機能を追加したいのですが

「VLOOKUP関数」だけでは同じ「DB（データベース）」から

「番歌」の値を返すことは出来ません。

図で説明すると「VLOOKUP関数」は検索値を

検索範囲（青枠）の中の１列目（赤枠）から検索して、指定した列の値を返すという動きです。

言い換えると、検索列となる１列目（赤枠）が検索範囲（青枠）の一番左端となってしまう為

その列より左側にある情報は範囲外となり、使用する事が出来なくなってしまいます。

 

ちなみに、返す列番号を「0」や「–」が使えないのか？と思うかもしれませんが

実際にやってみると「#VALUE!」という結果が返ってくるので

この方法には対応していないという事がわかります。

 

これを解決する方法としては

「詠み人」を先頭にした「DB（データベース）」を新たに作成するか

「DB（データベース）」の右に「番歌」の列を

参照コピーしておく方法も考えられますが

情報が複雑になってしまいます。

 

そこで「OFFSET関数」と「MATCH関数」を組合わせて使い

範囲外の情報を返す事が可能となります。

 

2限目「OFFSET関数」使い方：式の書き方

「OFFSET関数」の動作について、まずは式の書き方

「=OFFSET(参照,行数,列数, [高さ], [幅])」を分解して説明します。

「=OFFSET(」の後の「参照,」で指定したセルが基準位置となります。

「行数,」は基準位置から縦方向にセルをいくつ移動させた位置を参照するか

「列数,」は基準位置から横方向にセルをいくつ移動させた位置を参照するか

「高さ,」はその参照位置から横方向にセルいくつ分の範囲か

「幅,」はその参照位置から縦方向にセルいくつ分の範囲か、となります。

図の例ではセルを参照して、そこに入力された数値を使う方法ですが

移動量が固定値であれば、直接数値を入力する方法でＯＫです。

又、「高さ,」「幅,」は必要がなければ省略も可能です。

 

いくつか数値を変更してみます。

行数を「2」にした場合、基準位置「C3」セルから

縦方向（下）にセル2つ分の移動をした位置を参照し

「C5」が表示されました。

 

続けて、列数を「2」にした場合

基準位置「C3」セルから、縦方向（下）にセル2つ分の移動し

さらに横方向（右）にセル2つ分の移動を参照し「E5」が表示されました。

 

そして、数値は「–」を使う事も可能です。

行数、列数をそれぞれ「-2」にした場合

基準位置「C3」セルから、縦方向（上）にセル2つ分の移動し

さらに横方向（左）にセル2つ分の移動を参照し「A1」が表示されました。

 

ここから「高さ,」を「5」とした場合

参照した「A1」を基に、縦方向にセル5つ分の範囲を参照し

「A1」～「A5」と表示されました。

 

最後に「幅,」を「5」とした場合

参照した「A1」を基に、縦方向にセル5つ分の範囲を参照し

さらに横方向にセル5つ分の範囲を参照し、「A1」～「E5」が表示されました。

 

Excel関数「OFFSET」まとめ

「OFFSET関数」の特徴は

・参照したセルの位置から離れた位置にあるセルを参照する事が出来る。

・範囲の指定をする事も出来る。

・値に「–」符号を付けて逆方向の指定が出来る。

・他の関数と組合わせて使える。

以上となります。

 

これで悩みが解決し、一つ成長しました。

最後までご観覧頂きありがとうございました！