エクセルの便利な関数：OFFSET関数

2016.03.18 カテゴリ：EXCEL

エクセルの便利な関数：OFFSET関数

こんにちは、篠原です。

今回の便利な関数、4回目は「OFFSET関数」です。

便利な関数

これから解説予定の関数や設定は以下のとおりです。

OFFSET関数

　OFFSET関数は、「単一セル」または「セル範囲（左上隅）」を基準として、指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。また、返されるセル参照の行数と列数を指定することができます。
　この機能も便利だと思いませんか。ちょっとピンときませんかね？
　それでは、OFFSETT関数の使い方を見ていきましょう。

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

　基準：セルアドレス、セル範囲（左上隅か基準となる）を指定します。
　行数：基準から上下方向に移動する大きさを行単位で指定します。
　列数：基準から左右方向に移動する大きさを行単位で指定します。
　高さ：移動した基準からの行方向の範囲の大きさを指定します。省略は「0」で正の数値を指定します。
　幅　：移動した基準からの列方向の範囲の大きさを指定します。省略は「0」で正の数値を指定します。

OFFSET関数の使い方

1-1　OFFSET関数使い方　その１（戻り値が単一セル）

計算式
　=OFFSET(B61:E70,5,2,1,1)

　関数の意味は、
　参照範囲（B61:E70）の左上隅（B61）を基準として、
　行方向に５行
　列方向に２列
　参照範囲を移動後
　1行、1列
　の参照「D66」を返します。

　つまり計算式としては「=D66」として同じ意味になり、結果は「1」と表示されます。

OFFSET関数を他の関数と組み合わせて使う

2-1　OFFSET関数使い方　その２(戻り値がセル範囲の参照で、関数の組み合わせで使う、SUM関数と配列数式）

計算式
　=OFFSET(B81:E90,2,2,2,2)

　{=OFFSET(B81:E90,2,2,2,2)}

　=SUM(OFFSET(B81:E90,2,2,2,2))

　関数の意味は、
　参照範囲（B81:E90）の左上隅（B81）を基準として、
　行方向に２行
　列方向に２列
　参照範囲を移動後
　２行、２列
　の参照範囲「D83:E84」を返します。

　つまり計算式としては「=D83:E84」として同じ意味になり、結果は「#VALUE!」でエラーとなります。

　前例との違いは、前例では戻り値は、単一のセルの値を表示することが出来ましたが、この例では、戻り値がセル範囲の参照(複数のセル）となっていることで、正しい数式ではなくなり、エラーとなってしまっています。

　そこで、同じ関数式を配列式(複数範囲を選択し式入力後、「Ctrl」＋SHIFT」＋「ENTER」を押す）にすると、エラーがなくなり、参照されているセル範囲のデータがきちんと表示されるようになります。

　3個目の数式のSUM関数での使用では、戻り値はセルの参照範囲「D83:E84」となるので、このSUM関数の中のOFFSET関数をセルの参照範囲で書き換えてみると

　=SUM(D83:E84)

　となります

　結果は参照範囲のセルの値「9,7,3,4」を加算し「23」となります。

2-2　OFFSET関数使い方　その３(関数を組み合わせて使う）

計算式
　=INDEX(OFFSET(C38:F39,0,0,COUNTA(B38:B43),4),B46,)
　　C46:F46を選択した状態で下記式を入力し「CTRL」＋「SHIFT」＋「ENTER」を押す。自動的に｛、｝が入力されます(配列式）。

　かなり複雑な関数式になっています。詳しく解説しますね。

　まずは、このINDEX関数の期待するところは、
　表3の中から、
　指定した行数番目の行のデータを
　１行の配列として表４に取り出す
　ただし、表３は行が追加されることがあるので、
　行が増えてもこの数式を変更することなく利用できること

　こんな感じでしょうか。
　こんなことが出来てしまいますので、この関数が活用できれば嬉しいですよね。

　INDEX関数を配列式で入力することで、ここでは、行単位でデータを取得します。

　このINDEX関数の意味は、
　INDEX(参照範囲,取り出したい範囲内の行位置,列は省略（全ての列を取り出すため)）
　です。
　では、この結果を返すために各関数の役目を順を追って見ましょう。

　１．まず、COUNTA関数で、この表３テーブルのデータ件数を数えます。空白でないセルの数はここでは「2」となります。
　２．次に、OFFSET関数で、この表３テーブルのセルの参照範囲を取得します。
　　OFFSET(C38:F39,0,0,COUNTA(B38:B43),4)の意味は
　　2-1.C38:F39の左上隅を基準として
　　2-2.参照範囲の移動は行列とも「0」。つまり参照範囲は移動しない。
　　2-3.移動後の範囲の行数（データ件数は、COUNTA関数の戻り値）は、今回「2」。
　　2-4.移動後の列数は「4」。
　３．最後に、INDEX関数（配列式にする）で、この表３テーブルから指定された１行を取得する。

　以上から、結果としてセルの参照範囲は「C38:F39」ということになります。
　もし、この表３にデータが追加され40行目に情報が入力された場合には、2-3のCOUNTA関数の戻り値は「3」となり、このOFFSET関数の戻り値は「C38:F40」となり、自動的に参照範囲が広がる結果となります。

　このOFFSET関数の中で、参照する範囲が追加されることがあるため、行方向の行数を自動で変更できるように、
　COUNTA関数で行方向の空白でないセルの数を数えるようにしています。
　この例では行方向に伸びることが出来る量は少ないですが、テーブルの作り方次第で充分な量を設定できます。

2-3　OFFSET関数使い方　その４(関数を組み合わせて使う：VLOOKUP関数）

計算式
　=VLOOKUP(2,OFFSET(B5,C48,D48,E48,F48),2,FALSE)

　では最後にもう一つ。

　VLOOKUP関数との組み合わせです。

　要するに、セル範囲を使用する関数であれば応用できるということを分かってもらえればいいですね。

　VLOOKUP関数は、指定したキーを指定したセル範囲から検索し、その範囲内から指定した列位置の値を取り出すのが機能ですから、OFFSET関数の部分の意味が分かれば、VLOOKUP関数は、前に解説していますので、そちらを参考してください。

　この例では、表４の情報をもとに、VLOOKUP関数の検索範囲であるセル参照範囲を決めています。
　VLOOKUP関数の中のOFFSET関数の意味は
　　1.基準となるセル位置「B5」を基準として
　　2.行方向の参照位置をセル：C48（値：20）の値分移動します。
　　3.列方向の参照位置をセル：D48（値： 1）の値分移動します。
　　4.参照範囲の行数はセル：E48（値：10）の値により10行となります。
　　5.参照範囲の列数はセル：F48（値： 4）の値により4列となります。

　このことから、参照範囲となるのは「C25:F34」となります。

　これをもとに、VLOOKUP関数で、
　　1.検索するキー「2」を表２のC列から検索し「セル：C26」と一致します。
　　2.一致したキーと同じ行の左から２列目「セル：D26」の値が結果「8」として返されます。

　OFFSET関数を使うことで、セル範囲を引数にする関数はかなり柔軟な使い方が出来るようになりますね。
　この機能をしっかり理解して、ワンランク上の関数式を組んで、仕事に役立ててもらえるといいなと思います。

以上でOFFSET関数の使い方は終わりです。

?
Tag: エクセル EXCEL OFFSET関数参照範囲の移動関数の組み合わせ
- 次の記事　→
- ←　前の記事
コメント

タイトル：

お名前：

認証コード6160

コメント：

コメントは管理者の承認後に表示されます。

エクセルの便利な関数：OFFSET関数

便利な関数

OFFSET関数

OFFSET関数の使い方

OFFSET関数を他の関数と組み合わせて使う

コメント