INDIRECT関数とは
INDIRECT関数は、文字列で間接的にセルを参照して、そのセルの値を返してくれる関数になります。
INDIRECT関数は単体での使用はほぼなく、他の関数と組み合わせて使用するのがメインとなる関数です。
最初は少しややこしく感じるかもしれませんが、他の関数と一緒に組むことで、動的なセル参照も可能になり、作業の幅が格段に上がります。
INDIRECT関数は最初は少しややこしく感じるので、まず基本の動きをりっかりマスターしてから、代表的な実用例として、他の関数と一緒に組む方法を何段階かに分けて、ご紹介していきたいと思います。
他の関数と一緒に組む方法をマスターするためにも、INDIRECT関数の基本の動きを理解しておきましょう！
INDIRECT関数を使いこなせば、あなたも上級者の仲間入りです！
練習用ファイルを準備していますので、必要な方は下記よりダウンロードしてくださいね。
基本的な動き
さっそくINDIRECT関数の基本の動きを一緒に見ていきましょう。
A1セルに「営業部」と記載があります。
この「営業部」を別のセルに表示させたい場合、通常であれば「=A1」とセルに入力すれば、A1セル内にある文字列が表示されます。
実際にD2セルに入力して、最後に Enter を押してみましょう。
当然ですが、D2セルには「営業部」と表示されました。
では、これをINDIRECT関数でやるとどうなるか、実際にやってみましょう。
2通りやってみますので、まずはD3セルに = と入力しINDIRECTと入れます。途中まで入力すると候補が上がってくるので、INDIRECT関数を選んで Tab キーで使用関数を確定させます。
Tab キーで使用関数を確定させたら、INDIRECT関数の第1引数を入力します。
INDIRECT関数の第1引数は「参照文字列」となり、そのまま参照したい文字列を入力します。
今回はA1セルを参照したいので、ダブルクォーテーション “ 打ってからA1と入力し、最後にダブルクォーテーション “ を閉じます。
参照文字列なので、ダブルクォーテーションを忘れずに！
第2引数は「参照形式」となりますが、基本的に省略でOKなので、最後に Enter を押して完成です。
今回もきちんと「営業部」と表示されました。
=INDIRECT("参照したいセル番地")
=INDIRECT("A1")
先ほどはセル参照で「A1」セルを参照するよう指定しましたが、今度はINDIRECT関数を使って、文字列で参照セルを「A1」と指定しました。
参照形式とは、A1形式のセル参照かR1C1形式のセル参照かを指定します。
- A1形式
セル番地である「A1」や「B6」の様に行番号と列記号で指定。Excelでよく使われる形式
省略可能。省略した場合は自動的にA1形式になる。指定する場合は第2引数に「TRUE」と入力 - R1C1形式
行と列をそれぞれ番号で指定。VBAなどでよく使われる形式
省略不可なので、指定する場合は第2引数に「FALSE」と入力
では、もう一つの方法をやってみます。
D4セルに、先ほどと同様にINDIRECT関数を入力し、Tab キーで使用関数を確定させましょう。
INDIRECT関数の第1引数が「参照文字列」であることに変わりはないので、ダブルクォーテーション “ 打って、まずは列記号のAを入力し、ダブルクォーテーション “ を閉じます。
ダブルクォーテーション “ を閉じたら & を入力してから行番号「1」を入力します。
列記号の「A」は文字列ですが
行番号の「1」は数字なので
列記号だけに “ を付ける方法です。
これで列記号と行番号を & で繋いでA1と入力できましたので Enter を押して完成です。
今回も、きちんと「営業部」と表示されました。
=INDIRECT("参照したい列記号"&参照したい行番号)
=INDIRECT("A"&1)
結果的に、セル参照させる方法もセル番地を文字列で指定する方法も、セルの列記号だけを文字列指定し、行番号を & で繋いで参照セルを指定する方法も、全て同じ「A1に入力されている値」を返してくれます。
なんでそんな面倒なことするの？？？
他の関数と一緒に組むとは
最初に少し触れましたが、基本的にINDITRECT関数は単体で使用することはほぼありません。
単体で参照セルを指定するのであれば、普通に「=A1」とするのが一番簡単で分かりやすいですよね。
では、なぜそんな面倒なことをするのか。
先ほど、列記号と行番号を ＆ で繋いで参照セルを指定しましたが、例えば行番号の部分に他の関数を入れることで、動的なセル参照が可能になります。
実際に一緒にやってみましょう。
A～C列に日付、商品名、金額がリストになっています。
この一覧から、E～G列に最後に売れた日、最後に売れた商品、金額をそれぞれ表示させたい場合、リストがこれ以上増えないのであれば、セル参照方式で「A5～C5」セルを = で反映させれば良いですよね。
でも、まだリストは増えるし、何行増えるか分からないという場合、セル参照方式では先ほど指定した「A5」セルを指定したままなので、増減があるたびに手動でセル参照を変更しなければいけません。
こういう時に、INDITRECT関数で列記号は文字列で指定して、行番号のところに、空白ではないセルの数を数えてくれるCOUNTA関数を入れてあげれば、リストにデータが増えてもCOUNTA関数で行番号を返してくれるので増えた行番号を返してくれるようになります。
COUNTA関数の紹介記事は準備中です
実際にやってみましょう。
E2セルにINDITRECT関数を入力して、使用関数を確定させます。
Tab キーで使用関数を確定させたら、第1引数を入力します。
ここでは日付の列を反映させたいので、第1引数はA列になりますが、列記号は文字列で指定するので、必ずダブルクォーテーション “ で囲んであげましょう。
続けて行番号を繋ぐための ＆ を入力したらCOUNTA関数を入れて、Tab キーで使用関数を確定させます。
Tab キーで使用関数を確定させたらCOUNTA関数の第1引数を入力します。
COUNTA関数の第1引数は、空白ではないセルの数を数える場所を聞かれているので、ここではA列全体を選択します。
第1引数を指定したら、COUNTA関数のカッコを閉じます。
ここまでがINDIRECT関数の第1引数となるので、続けてINDIRECT関数のカッコも閉じてあげましょう。
最後に Enter を押せば完成です。
一番下にある12月8日が表示されました。
E2セルをコピーしてF2・G2セルに数式だけ貼付してあげましょう。
すると全てのセルに「12月8日」と表示されてしまいました。
なぜこうなるのか、F2セルを選択した状態でキーボードの F2 キーを押して数式を確認してみましょう。
列記号は文字列で入力したので、列記号はＡのままになっていますね。
セル参照と違い、文字列で指定した場合はコピーしても連動して動かないので、F2セル・G2セルのどちらもそれぞれ手動でB、Cと変更してあげる必要があります。
それぞれ列記号を修正してあげた結果、正しく反映されました。
では、先ほどと同じように6行目にデータを追加してみましょう。
今度はリストにデータが増えても自動で最後のデータを反映してくれました。
INDIRECT関数とCOUNTA関数を一緒に組むことで、最終行のセルを参照するように指定ができました。
このように、INDIRECT関数は他の関数と一緒に組むことで、かなり自由度の高いセル参照が可能になり、エクセル作業の幅が大きく広がります。
=INDIRECT("列記号"&COUNTA(行番号を数えたい列))
=INDIRECT("A"&COUNTA(A:A))
まとめ
INDIRECT関数の基本的な動きと、実際に他の関数と一緒に組むとどう動くのかの例としてCOUNTIA関数と一緒に組んで最終行を参照させる方法をご紹介しました。
まだまだ他の関数と組むことで便利に使用できる方法がありますが、最初は混乱しやすい関数なので、まずはここでINDIRECT関数の基本的な動きをしっかり理解してから応用に進みましょう！
動画でもご紹介していますので、動画を見ながら操作を確認したい方は下記からどうぞ！
励みになります！高評価とチャンネル登録よろしくおねがいいたします。