エクセルで参照セルをずらすINDIRECT関数とOFFSET関数の使い方を解説！

エクセル初心者

エクセルで参照セルをずらす関数があるって聞いたんだけど、参照のセルをずらすってどういうこと？関数を使うメリットとかも知りたいな。

エクセルでは、参照を使ってセルに文字や数字を表示させることがあるよね。関数を使うと間接的に別の場所を参照させたり、ずらす方向と数を指定して参照先を変更したりすることができるんだ。

Dr.オフィス

引数に参照範囲を入力する必要がある関数と組み合わせて使うと、元を変更せずに参照している範囲をずらすことができる2つの関数を紹介します。

エクセルで参照先をずらす2つの関数

文字列を使って間接的に参照先を変えてセルの位置をずらす「INDIRECT関数」
行と列のずらす方向と数を指定する「OFFSET関数」

マイクロソフトオフィススペシャリストマスターの資格を持つ私が、元を変えずに参照先をずらすことができる便利な関数を紹介するね。

Dr.オフィス

今回紹介する関数は少し扱い方が難しいのですが、使い方を覚えてしまうと業務に役立つ便利な関数なのでぜひ挑戦してみてください。

≫エクセルの参照とは？基本の使い方から覚えておくと便利な機能まで徹底解説！では、参照の基本の使い方から上級者向けの機能まで解説していますので、こちらの記事も参考にしてみてください。

※本記事は『OS：Windows11』画像は『Excelのバージョン：Microsoft365』を使用しています。

エクセルで参照セルをずらす関数の種類

セル位置を1つずつずらしたときは相対参照にしたり、計算式をコピーしても参照しているセルの位置をずらしたくないときは絶対参照したりなど、あまり意識していない人でも参照のセルをずらすという操作をおこなっています。

相対参照や複合参照は参照セルをずらすことで、正しい計算式がコピーされるようにしているよね。

Dr.オフィス

今回紹介する「INDIRECT関数」と「OFFSET関数」は、VLOOKUP関数やSUM関数のように引数に参照範囲が必要な関数と組み合わせると、元のデータを変更せずに参照セルをずらすことができる便利な関数です。

エクセル初心者

言葉だけだとイメージしにくいから具体的にわかりやすく教えて。

はじめに、「INDERECT関数」と「OFFSET関数」とはどんな関数なのか、この2つの関数を使うと何ができるのかについて解説していくよ。

Dr.オフィス

INDERECT関数

INDIRECT関数は、セルに入力されている文字列を介して、間接的に参照するセルを指定する関数です。

INDIRECT関数の構文：=INDIRECT(参照文字列,[参照形式])

引数の「参照文字列」は必須ですが、「参照形式」は省略することが可能です。

INDIRECT関数は少し使い方が複雑なので、ここでは「参照形式」を省略した形で解説していきます。

エクセル初心者

なんだか難しそう。

実際のデータを使って操作しながら、INDIRECT関数使い方をイメージしてみよう。

Dr.オフィス

INDIRECT関数は、引数のセル番地をダブルクォーテーションで囲むのか、何もつけずにセル番地を挿入するかによって、表示される値が変わります。

これは、A1に「=INDIRECT(“D1”)」と入力した画像です。

INDIRECT関数のセル番地にダブルクォーテーションをつけて入力すると、D1に入力されている文字がA1に表示されます。

Enterを押して確認してみましょう。

「A1」に「D1」に入力されている文字が表示されました。

今度は、ダブルクォーテーションをつけずにセル番地を指定してみましょう。

ダブルクォーテーションをつけずにINDIRECT関数を使うときはちょっと複雑だから注意してね。

Dr.オフィス

「A3」のセルに「=INDIRECT(C4)」が入力されています。

「A1」には、先ほどINDIRECT関数を使って表示させた「D1」と同じ文字列が表示されています。

C4のセルには、「A1」と入力されていますね。

この状態でEnterを押して、「A3」のセルの値を確認してみましょう。

「A3」のセルに「A1」に入力されている文字が表示されました。

エクセル初心者

C4を参照したのに、どうしてA1に入力されている文字が表示されたの？

ここはちょっとわかりにくいところだから、もう少しくわしく解説するね。

Dr.オフィス

「A3」のセルには、「C4」を参照するINDIRECT関数が入力されているので、はじめに「C4」を参照します。

INDIRECT関数には、ダブルクォーテーションをつけずに引数を入力すると指定したセルにセル番地が入力されていた場合、そのセルを参照するという特徴があります。

「C4」を見ると「A1」と入力されていますよね。

つまり、「A3」に入力したINDIRECT関数は「直接C4を参照するのではなく、A1を参照してね」という意味になるということです。

その結果、「A1」に入力されている文字が「A3」に表示されたというわけです。

エクセル初心者

なんでこんなにわかりにくい参照を使う必要があるの？直接参照したいセルを指定したほうがわかりやすいと思うんだけど。

間接的にセルを参照させることで、データを変更せずに参照するセルの位置をずらすことができるんだ。だから、INDIRECT関数は他の関数と組み合わせて使うことが多いんだよ。

Dr.オフィス

ここでは、INDIRECT関数のしくみをわかりやすくするために、参照先のセルに直接セル番地を入力しています。ですが、あらかじめ参照先のセルやセル範囲に名前の定義をしておけば、セルに定義した名前と同じ文字を入力すると別のセルやセル範囲に参照先を変えることができます。
名前の定義を使って参照先を変える方法は、≫INDIRECT関数の使い方で紹介します。

INDIRECT関数については、≫INDIRECT関数でも詳しく解説していますのでこちらも参考にしてみてください。

OFFSET関数

OFFSET関数は、行や列の方向と数を指定して、参照するセル位置をずらす関数です。

OFFSET関数の構文：=OFFSET(基準,行数,列数)

引数に高さと幅を加えると、セル範囲を指定して移動させることもできますが、OFFSET関数のしくみをわかりやすくするために、今回は上記の構文をもとに解説していきます。

引数だけを見てもOFFSET関数でどのように参照するセルをずらすことができるのかわかりにくいから、こちらもデータを使って実際に操作してみよう！

Dr.オフィス

OFFSET関数の基本的な使い方を解説します。

OFFSET関数は、基準となるセルを指定して、行または列のずらす数と方向を指定します。

「A7」には、A1がそのまま参照されるように「=A1」が入力されていますが、「B7」は、A1のセルが1行下にずれるように「=OFFSET(A1,1,0)」が入力されており、「C7」には右に1列ずれるように「=OFFSET(A1,0,1)を入力しています。

この状態でEnterを押して、関数の値がどのように表示されるのか確認してみましょう。

「B7」、「C7」も「A7」と同じように「A1」基準としていましたが、行、列の位置をそれぞれ1つずつずらすことができました。

エクセル初心者

1つ下や右にずらす方法はわかったけど、上や左にずらしたり、2つ以上離れている場所にずらすことはできないの？

OFFSET関数の引数を変えれば、上や左にずらしたり、2つ以上離れた場所を参照させたりすることができるよ。

Dr.オフィス

OFFSET関数の引数は、「基準となるセル」、「行」、「列」ですが、指定する数字に正負の記号をつけることで左右上下どちらにもずらすことができます。

行の引数	列の引数
正の値と数を指定すると、下方向に指定した数だけ参照する位置がずれる	正の値と数を指定すると、右方向に指定した数だけ参照する位置がずれる
負の値と数を指定すると、上方向に指定した数だけ参照する位置がずれる	負の値を指定すると、左方向に指定した数だけ参照する位置がずれる

行と列の参照方向

OFFSET関数を使うと入力されているデータの内容を変更せずに参照するセルをずらすことができますが、OFFSET関数を単体で使用するケースはそれほど多くありません。

≫OFFSET関数の使い方で、OFFSET関数の活用の幅が広がる「他の関数と組み合わせて便利に使う方法」を紹介していますので参考にしてみてください。

INDIRECT関数の使い方

INDIRECT関数は間接的にセルを参照するため慣れるまで少しわかりにくいですが、他の関数と組み合わせて使うと関数の内容を変更せずに参照先を簡単に変えることが可能になります。

今回は、VLOOKUP関数とINDIRECT関数を組み合わせた便利な使い方を紹介します。

INDIRECT関数とVLOOKUP関数を組み合わせておくと参照先の表を変えることができるので、1つのVLOOKUP関数だけで「食料品」、「飲料品」、「日用品」の表にあるすべての価格を表示させることが可能です。

INDIRECT関数で参照先を変えるために、あらかじめ3つの表のそれぞれの範囲に「食料品」、「飲料品」、「日用品」と名前をつけておきましょう。

セル範囲に名前を付ける方法は、≫名前ボックスを使って「名前の定義」を登録するを参考にしてみてください。

エクセル初心者

VLOOKUP関数って参照したい表から指定した列のデータを抽出するんだよね？表を3つも使うのに、挿入するVLOOKUP関数は1つでいいってどういうこと？

VLOOKUP関数だけでデータを抽出するよりも手順はちょっと複雑だけど、INDIRECT関数と組み合わせると複数の表を参照させることができるんだ。

Dr.オフィス

STEP
INDIRECT関数とVLOOKUP関数を入力
VLOOKUP関数とINDIRECT関数を組み合わせる
1. A2に「食料品」と入力
2. B2に「パン」と入力する
3. C2に「=VLOOKUP(B2,INDIRECT(A2),2,FALSE)」と入力
VLOOKUP関数の範囲の部分に「INDIRECT(A2)」を指定しておくことで、「食料品」と入力したときに名前の定義で「食料品」と同じ名前をつけた「食料品の表」に参照先に変えることができるよ。

Dr.オフィス
STEP
関数の値を確認する

INDIRECT関数で参照先が変わった

VLOOKUP関数の参照範囲にINDIRECT(A2)を挿入していることで、A2に入力されている『食料品』という文字列が、『【食料品】と名前がついている表の範囲を参照してね』という意味に変換されます。

VLOOKUP関数で2列目を入力されるように指定しているので、「パン」と入力すると食料品の表の「パン」の2列目の金額が表示されるようになります。

違う表のデータを入力して、参照先が変更されるか確認してみましょう。

C3に入力した関数の数式は変えずに、A2に「飲料品」、B2に「水」と入力して、Enterで数式を確定してみてください。

A2のセルの文字が「飲料品」に変わったので、B2に「水」と入力すると金額のセルに「飲料品の表」にある「100」と表示することができました。

エクセル初心者

INDIRECT関数ってややこしくてわかりにくいな～と思ったんだけど、他の関数と組み合わせるとこんな使い方ができるんだね。

VLOOKUP関数とINDIRECT関数を組み合わせて使う方法は、≫【関数の組み合わせ】VLOOKUP関数とINDIRECT関数で参照先を変える技！で詳しく解説していますので参考にしてみてください。

OFFSET関数の使い方

≫OFFSET関数では、OFFSET関数の基本的な使い方を紹介しましたが、OFFSET関数の引数に高さと幅を加えて他の関数と組み合わせるとさらに便利に使えるようになります。

例えば、こちらの表のように毎月の売上額が追加されていくデータがあったとします。

SUM関数の範囲に、OFFSET関数、COUNTA関数を組み合わせて入力すると、「F2」のセルに現時点の売上合計額を表示することができます。

OFFSET関数に「COUNTA関数」、「SUM関数」を組み合わせて、最新の月までの合計金額を計算してみましょう。

STEP
SUM関数を入力

SUM関数を入力

F2を選択して、「=SUM(」と入力します。
STEP
OFFSET関数を挿入してSUM関数の範囲を入力していく

OFFSET関数を入力する

SUM関数の範囲を入力する位置に、「OFFSET(A1,1,1」を入力します。

「OFFSET(A1」の部分は、基準になるセルの指定です。

このデータでは、見出しが入力されている「A1」を基準としています。

「OFFSET(A1,1,1」まで入力すると、SUM関数の範囲の最初の位置が「B2」を指定しているのと同じ意味合いになります。

A1の次に入力されている「1」は、「A1を下方向に1行ずらしてください」という意味なので、参照するセルの位置が「A2」に移動します。2つ目の「1」は、「A2に移動した参照先をそこからさらに右に1列ずらしてください」という意味になるので、「B2」を参照していることになります。
STEP
OFFSET関数の高さの引数の位置にCOUNTA関数を挿入する

COUNTA関数でA列全体の範囲を指定する

前の数式のあとに「,」を入れて区切り、OFFSET関数の高さの引数を入力する位置に「,COUNTA(A:A)-1」と入力します。

COUNTA関数は数字や文字などが入力されているセルを数える関数ですが、OFFSET関数の高さの引数としてCOUNTA関数を挿入すると、SUM関数の範囲の終わりの位置を指定することができます。

SUM関数、COUNT関数、COUNTA関数などは、範囲の引数に(A:A)のように入力するとA列全体を指定することができるよ。データが追加されることで範囲が変わっていく場合は、A列全体を指定しておこう。

Dr.オフィス

A1には見出しが入力されているため、最後に「-1」を入力して見出しのセルが含まれないようにしておいてください。
STEP
SUM関数とOFFSET関数を閉じる

SUM関数とOFFSET関数を閉じる

数式の最後に「))」を入力して、SUM関数とOFFSET関数の数式を閉じましょう。

D6に「=SUM(OFFSET(A1,1,1,COUNTA(A:A)-1))」と入力されていればOKです。

今回は、行数は変更されていくけど、列数は変わらないため、OFFSET関数の幅の引数は省略して計算式を入力しているよ！

Dr.オフィス
STEP
ENTERキーで数式を確定

Enterで数式を確定

Enterで数式を確定し、これと同じ数字が表示されたら完了です。

実際のデータの場合は、空いているセルにSUM関数を入力して確認してみてね。

Dr.オフィス