【Excel・エクセル】OFFSET関数の使い方！範囲指定する関数？
参照するセル範囲を可変にできないかな？
それなら、OFFSET関数が便利かも。
- OFFSET関数は2通りの使い方がある
- 値を抽出：=OFFSET(参照,行数,列数)
- 範囲を指定：=OFFSET(参照,行数,列数,高さ,幅)
- ほかの関数と組み合わせて使うことが多い
【Excel・エクセル】値の取得と範囲指定、2つの意味を持つOFFSET関数
OFFSET関数はとっても特殊な関数で、「指定した位置の値を取得する」「セルの範囲指定をする」2つの用途で使えます。
僕の難しそうセンサーが反応してます！
乗り越えよ。
それが一体、何に使えるのか？
まずはオフセット関数の基本的な使い方から、見ていきましょう！
ちなみに読み方は、「オフセット関数」です。
【Excel・エクセル】OFFSET関数の使い方
OFFSET関数の練習ファイルを用意したので、良ければ使ってくださいね。
※実際に操作できます。
いちいちスクロールするのは面倒なので、別タブか別ウィンドウで開くの推奨です！
OFFSET関数で、セルの値を取得する
=OFFSET(参照,行数,列数)
| OFFSET | 次の位置にある値を抽出して |
| 参照 | このセルから |
| 行数 | 縦方向にこのセル分（+なら下、-なら上） |
| 列数 | 横方向にこのセル分（+なら右、-なら左） |
行数と列数はちょっとややこしいのですが、
- 行数：1だと真下のセル、-1だと真上のセル
- 列数：1だと右隣のセル、-1だと左隣のセル
- 0だと参照から動かない
という感じで指定します。
例えばこの表のセルB4を参照にして、行数を2，列数を0にした場合。
B4から2セル分下にある、B6の値が抽出されます。
参照は同じで、今度は行数を-3、列数を0にした場合。
B4から3つ上、2つ右にあるセルD1の値が抽出されます。
INDEX関数に似てる？
確かに、INDEX関数と同じようにMATCH関数と組み合わせて使えるね。
https://sakusaku-office.com/excel/post-10827/
OFFSET関数で、セルの範囲を指定する
=OFFSET(参照,行数,列数,高さ,幅)
| OFFSET | この位置から、次の範囲を指定して |
| 参照 | このセルから |
| 行数 | 縦方向にこのセル分（+なら下、-なら上） |
| 列数 | 横方向にこのセル分（+なら右、-なら左）の位置から |
| 高さ | このセル分下 |
| 幅 | このセル分右までの範囲 |
この使い方は直接使うのではなく、関数などで範囲指定する際に使います。
例えばSUM関数では、合計する範囲をOFFSET関数で指定できます。
=SUM(OFFSET(A1,1,2,5,1)では、A1から1つ下、2つ右のセルから5行1列分の範囲の合計ができるんです。
【Excel・エクセル】OFFSET関数とVLOOKUP関数の組み合わせ
何となくOFFSET関数の使い方は分かったけど、いつ使えばいいの？
確かにこの関数単体ではあまり活用はできません。
OFFSET関数は、他の関数と組み合わせてこそ、真の力を発揮します！
ためしに、VLOOKUP関数と組み合わせて使ってみよう。
=VLOOKUP(検索値,範囲,列番号,検索方法)
| 検索値 | この値を基準にして |
| 範囲 | この範囲の中の |
| 列番号 | 左から何番目にある値を抽出して |
| 検索方法 | 基本は0（FALSE）でOK |
VLOOKUP関数で、型番から分類を確認できるようにしました。
が、毎月新製品を出しているので、毎月VLOOKUP関数の範囲を変えないといけません。
表に追加したら、自動で範囲を更新できないかな？
VLOOKUP関数の範囲にOFFSET関数を使って、範囲を可変にしたいと思います。
練習ファイルをお使いの方は、「応用」シートを開いてください。
ステップ1
OFFSETでセル範囲を指定します。
VLOOKUP関数の範囲を、OFFSET関数に置き換えましょう。
A1:C6を消して「OFFSET(」と入力します。
ステップ2
参照にするセルは、VLOOKUP関数の範囲で一番左上に位置するセルにします。
今回はセルA1にして、特に動かす必要もないので、行数列数ともに0にしておきましょう。
つまり前半は「A1,0,0,」となります。
ステップ3
次に高さですが、月が増えると行数が増え、高さが増していきます。
つまり高さは、表の行数分あればいいってことですよね。
そこで今回は、COUNTA関数で行数を出していくよ。
COUNTA関数は、何かしらが書いてあるセルの数を数える関数です。
図の場合「=COUNTA(A1:A5)」で、答えは4になります。
というわけで、OFFSET関数の高さの部分に「COUNTA(」まで入力してください。
ステップ4
行数は表の列範囲のセルの数とイコールになるので、どこか列を指定すればOKです。
A列をクリックして、指定します。
COUNTA関数の出番はここまでなので、カッコを閉じましょう。
ステップ5
次の引数、幅に移るので「,」を打ちます。幅は、表の列数です。
今回の表ではデータが追加されても列数は変わらないので、数字で「4」と指定しておきます。
これでOFFSET関数も終わりなので、カッコを閉じてください。
完成！
これで、範囲の高さが表の行数で変わるVLOOKUP関数の完成です！
新しく行を追加しても、ちゃんと読み取ってくれていますね！
VLOOKUP関数の範囲を列選択すればいいのでは？
ま、MATCH関数と組み合わせる方法もあるんよ。
OFFSET関数、使い方によってはかなり便利な関数ですね！
それでは、お疲れさまでした！
- OFFSET関数は2通りの使い方がある
- 値を抽出：=OFFSET(参照,行数,列数)
- 範囲を指定：=OFFSET(参照,行数,列数,高さ,幅)
- ほかの関数と組み合わせて使うことが多い
