- ホーム >
- 応用操作 >
- エクセル（Excel）編 >
INDIRECT関数 ～ リストメニューの選択によって参照範囲を切り替える ～
いろいろな作業をExcelでしていくうちに、こんなことができる関数があれば、と思うことがありますが、たいていそうした機能を代替できる関数が存在しているものです。
本項ではそうした関数のひとつである、
INDIRECT（インダイレクト）関数
について学習していきます。
INDIRECT関数は、簡単にいうと「文字列によって参照先のセルやシートを切り替える」関数になります。
この「文字列」というのが重要なところなのですが、下図のように「B2」セルを参照している「D2」セルがあるとします。
「D2」セルには数式の「=」ではなく、文字列で「B2」と記述しているのがミソです。これが文字列によって切り替えるという仕組みになります。そのため、数式で「=B2」とすると関数がエラーになります。
次に、INDIRECT関数を使って「D4」セルにINDIRECT関数の式「=INDIRECT（D2）」を記述します。これはINDIRECT関数で「D2」セルを参照しているという意味になります。（引数の入力については後述します）
すると、「D4」セルには「D2」セルに文字列で指定した「B2」セルの値である「ペガサス星矢」が表示されます。これは、数式ではなく文字列があらわすセルの値が表示されていることになります。
このようにINDIRECT関数は、
その文字列が示すセルやエリアの値を返す
関数と言い換えることができます。そのため、数式で「=B2」とするとエラーになりますし、なんでもない文字列では意味を成しません。あくまでどのかのセルかエリアを示す文字列を引数にする必要があります。
では、「D2」セルの値を変更してみましょう。文字列で「B3」と入力すると、ともなってINDIRECT関数の「D4」セルも「ドラゴン紫龍」に切り替わります。
今度は大文字で「Ｂ４」と入力してみましょう。すると、大文字でも参照先のセルが切り替わりました。
このように、INDIRECT関数は文字列によって参照先を切り替えることができる関数ということが理解できたと思います。では、この関数にどのような使い方があるのかというと、
ドロップダウンリストで選択した項目ごとに参照先を切り替える
という使い方が可能になるのです。（ドロップダウンリストについて詳しくは、入力規則 を参照してください）
例えば、下図のように「D2」セルをB列の文字列からなるリストメニューを設定します。（ドロップダウンリストの設定方法については、入力規則 を参照してください）
この場合、「ペガサス星矢」を選択すると「D4」セルには「ペガサス星矢」専用のリストメニューが表示されます。
また「ドラゴン紫龍」を選択すると「D4」セルには「ドラゴン紫龍」専用のリストメニューが表示されます。
このように、選択した項目に合わせて異なるリストメニューを表示させることができるのです。
こうした切り替えは、「ペガサス星矢」や「ドラゴン紫龍」といった文字列をキーにして参照先を切り替えています。参照先は下図のとおり別のシートに作成しています。
ここまで理解できたら、いろいろと使い道が広がったのがわかる思います。
例えば、メーカー別の商品リスト、コース別の料金表など、後述しますが、すべてをひとつにまとめた請求書のフォーマットを作成することも可能です。
ではまず、上記のリストメニューの切り替え方法を学習しておきましょう。
この場合の考え方は、「ペガサス星矢」の参照範囲A、「ドラゴン紫龍」の参照範囲Bという具合にそれぞれ指定し、文字列の切り替えによってその文字列が指定する参照範囲を切り替えます。
したがって、まずは「ペガサス星矢」の参照先を設定する必要があります。
参照先の設定は、名前の定義と管理 で学習したとおり、範囲指定したエリアに「名前」を付けることによって設定することができます。文字どおり「名前」によって切り替えるのです。
下図のように対象とする範囲を選択し、「数式」タブの「名前の定義」より、エリアに「ペガサス星矢」の名前を付けます。
これで「ペガサス星矢」が示す範囲は「Sheet2のA3:A5」となります。この要領ですべての範囲に名前を付けます。すると以下のとおり「名前の管理」画面にそれぞれの名前と範囲が表示されます。
以上で準備は完了です。
これで、それぞれの名前で参照範囲を切り替えることができます。「D2」セルのリストについては、エリアに設定した名前と同じ文字列が選択できさえすれば良いので、ここでは上図で別シートに作成した表の先頭行を「元の値」に指定しました。直接文字列を記述しても同じ名前であれば問題ありません。
そして、INDIRECT関数を指定する「D4」セルは、そのまま「D2」を引数に指定したままで良いのですが、この場合は、このセルもリストから選択するように設定しなければならいので、
「データの入力規則」画面のリストの設定の中にINDIRECT関数を記述する
必要があります。といっても下図のように同じ式を記述するだけです。
以上で、それぞれの名前に応じた範囲をリストによって切り替えることができるようになります。
では、これらを踏まえて、以下のような「請求書」を作成してみましょう。
この請求書は、「B5」セルの区分で下図のように年齢区分をリストメニューから選択します。
すると、区分に対応した料金設定が自動的に「B6」セルに表示されます。
こうして「区分」と「料金」が定まったら、それに対応した「項目」がリストメニューに表示されます。
上図の場合は「小学生以下」区分のリストメニューになっており、項目を選択すると、その項目に対応した「金額」が自動で入るように設定しています。
多少応用的ですが、これまでの知識で十分作成することができます。
まずは請求書の様式を作成し、それとは別シートに参照用の表を作成します。ここでは、下図のような表でそれぞれの区分の項目と金額を作成しています。
区分は「小学生以下」、「中学生以上」、「シルバー」の3つです。料金体系としてそれぞれに準ずる「子ども料金」、「一般」、「シルバー料金」としています。
このあたりの作り方は自由ですが、今回はVLOOKUP関数を組み合わせていますので、上図のような表形式で作成しておくと汎用的に使えるのでシンプルにすることが大切です。（VLOOKUP関数については、LOOKUP関数 を参照してください）
まず、トリガーとなる「区分」のリストメニューを設定します。
ここは「小学生以下」、「中学生以上」、「シルバー」の3つなので、「データの入力規則」画面に直接記述してもよいですが、ここでは別シートの表を範囲指定しました。
そして選択した「区分」に対応した「料金」を表示させます。
これも別シートの表からVLOOKUP関数で表示させています。
これで「小学生以下」を選択すると「子ども料金」、「中学生以上」を選択すると「一般」が表示されます。（VLOOKUP関数については、LOOKUP関数 を参照してください）
そもそも、VLOOKUP関数を使ってそれぞれの区分に対応する料金体系を表示させる意味について疑問に感じると思いますが、これについては後述しますので、ひとまずすすめてください。
次に、選択した区分に対応した項目メニューを表示させます。
これも、すでに学習のとおり、同様にエリアに名前を付けてINDIRECT関数で切り替えます。
まずは「小学生以下」、「中学生以上」、「シルバー」の3つのエリアを指定して名前を付けます。ここでは項目名だけなので金額まで範囲に含む必要はありません。
それぞれの名前ができたら、また同様に「データの入力規則」画面のリストの設定の中にINDIRECT関数を記述します。
これで、選択した区分に応じたリストメニューが表示されるようになります。区分を「中学生以上」に変更すると、ともなってリストの内容が切り替わります。
最後に、選択した項目に対応する「金額」を表示させます。
ここが今回の応用編です。
この部分の関数はVLOOKUP関数とINDIRECT関数を組み合わせています。IF関数とVLOOKUP関数のみで作り込むことはできますが、3パターンの「真」と「偽」を指定しなければならず、恐ろしく長い論理式となってしまいます。
まずは、VLOOKUP関数で参照する範囲を同じように名前を付けて登録します。
今度は金額を表示させるので、項目名と金額を含む範囲を指定し、名前を料金体系と同じ「こども料金」にします。
同様に、中学生以上の「一般」とシルバーの「シルバー料金」の名前も登録します。
名前が登録できたら、リストの設定と同様に、今度はVLOOKUP関数の引数にINDIRECT関数を記述します。
「検索値」は項目名のセルを、「範囲」は料金体系を表示している「B6」セルをINDIRECT関数で指定します。これで料金体系の名前と一致し、参照先を切り替えることができます。
すると、どのリストメニューをどのように変更しても、それに応じた項目名や金額が表示されるようになります。
また、セルに何も入力されていない状態で、計算式のセルに「0」や「#N/A」を表示させたくない場合は、IF関数でダブルクォーテーション2つ「""」を使用し「A●セルが空白」の場合として、「A●=""」が真の場合「""」のように記述すると空白表示になります。（詳しくは、IF関数 を参照してください）
以上でINDIRECT関数の応用的な使い方まで学習できたと思います。少々難解な部分もありますが、慣れてくると使いやすい関数なので、様々な場面で使ってみましょう。
更新履歴
- 2020年10月3日
- ページを公開。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- サイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします！