ExcelのINDIRECT関数でリストを階層表示にしてみた

こんにちは、ちちくろです♪

Excelの関数は通常セルに数式として書き込むのですが・・その方法は・・セル内に“=”を書き・・その後に関数を書き込む・・例”=ISBLSNK（参照先セル）等・・。

特定の関数はセル以外にも書き込むことが出来る・・一部の関数はデータの入力規則のリスト表示の【元の値】に指定することが出来ます・・。

その関数は・・INDIRECT関数・・この関数を使うことでリストを階層化できることに・・。

階層化とは・・リストに表示される内容が動的に変化するということ・・Excelのデータ入力作業が効率的になるので覚えていて損はないかと・・。

1 INDIRECT関数
- 1.1 特徴
- 1.2 注意点
2 入力規則のリストを階層化
- 2.1 リストの範囲をINDIRECT関数で指定
3 例題
4 まとめ

INDIRECT関数

INDIRECT関数とは・・INDIRECT関数は参照先のテキストをセル番地などに変換できる・・そんな関数♪

INDIRECT関数の書き方は・・”=INDIRECT(参照先セル,参照形式)と書きます。

参照形式は・・A1形式化R1C1形式を指定できます・・省略しても大丈夫・・省略するとA1形式になります♪

通常・・セルを指定する時はA1形式が一般的なので・・省略すればOK

特徴

INDIRECT関数の特徴は・・というか・・利点は・・。

セル内の数式を変更せずに数式の参照先セルを変更できるというもの・・数式のセル参照を動的に変更できるということに・・。

INDIRECT関数の参照先セルのテキストが参照先セルに・・チョットわかりにくいかも・・

例えば・・A1のセルを参照するのに・・【A1】というテキストが書かれた・・C1のセルを参照することに・・。

なんか・・頭が混乱しそう・・なのでセル範囲に名前を付けて使うことが一般的になります。

例えば・・INDIRECT関数の参照先セルに・・【部品】と書き込まれたら・・部品と名前が付けられたセル範囲を参照できます♪

注意点

INDIRECT関数は・・セル内のテキストでセル番地を参照するもの・・そのため・・。

INDIRECT関数は書き方で結果が大きく変わることに・・セル内のテキストを参照するのだから・・＝INDIRECT(“A1”)と書くと・・セル番地に変換できないことに・・。

例題図のようなワークシートで・・セルA2に【4】を入力・・セルの名前を・・・【テスト】に設定・・。

セルC2に【テスト】と入力し・・セルC4に【=INDIRECT(C2)】と数式を入力・・すると結果は【4】に・・これは【テスト】という名前が付いたセルA2の値が返されたことに・・。

次に・・セルC5に【=INDIRECT(“C2”)】と数式を入力すると・・【テスト】と表示される

これはセルC2に書き込まれたテキストがそのまま返されることに・・この場合・・セルの範囲の名前に変換されないので注意してね♪

入力規則のリストを階層化

INDIRECT関数を使うと・・数式の参照先セルを動的に変更することができる・・。

このことを利用して・・セルの入力規則のリスト表示を階層化することが・・。

このことについて・・次項から説明を・・。

リストの範囲をINDIRECT関数で指定

リスト表示は・・【データ】リボンの【入力規則】を選択して・・リストを選びます♪

その際に・・【元の値】にリストに表示させたいテキストを記入します・・テキストの代わりにセル範囲を選択することもできます♪

その時に・・INDIRECT関数を使うことが可能・・INDIRECT関数を使うことで・・リストの表示内容を動的に変更することが可能に・・。

INDIRECT関数でセル範囲を動的に変更するには・・セル範囲に名前を付けるのが良いかと・・。

以下に例題を用意したので・・それで解説したいと・・。

例題

用意した例題は・・商品の種類を選ぶと・・その種類の商品がリストに表示され・・リストから商品を選ぶと・・その単価が表示される・・そんなワークシートを作ってみようかと・・。

セル範囲に名前を付ける

リスト表示を階層化するために参照するセル範囲に名前を付ける必要が・・セル範囲に名前を付けることで・・INDIRECT関数でセル範囲を指定することが出来ます♪

セル範囲に名前を付けるには・・セル範囲を選択し・・名前ボックスに名前を書き込む・・これだけでOK

例を挙げると・・図のワークシートで・・A2～A4を選択し・・名前ボックスに【種類】と書き込む・・これでA2～A4のセルに【種類】という名前を付けることが出来ます♪

セル範囲をテーブルに

INDIRECT関数でセル範囲を指定する場合・・名前以外にのテーブルを指定することが出来ます。

テーブルを作成するには・・セル範囲を選択し・・【挿入】→【テーブル】をクリック・・すると・・テーブルの作成が表示されるので・・【先頭行をテーブルの見出しとして使用する(M)】にチェックを入れて【OK】をクリック・・。

すると・・テーブルデザインのリボンが表示されるので・・【テーブル名】にわかりやすい名前を付ければOK・・ここでは果物と名前を付けることに・・。

リストの範囲をIINDIRECT関数で指定

リストを使うには・・データの入力規則で【入力値の種類】にリストを指定する必要が・・。

【データの入力規則】は・・【データ】リボンにあるのでそれをクリック・・。

すると・・【データ入力規則】が表示されるので・・【入力値の種類】をリストに・・【元値】に【=数式】と書き込めばOK

ここでは・・【入力値の種類】でリストを選択し・・【元の値】にINDIRECT関数でセル範囲を指定することに・・。

【元の値】の数式は・・A列のセルには・・【=種類】を選択・・これはセル範囲【種類】の内容がリスト表示してね・・ということ

次に・・B列のセルには・・【=INDIRECT(CONCAT(A2,”[商品]”))】と書き込み・・。

これは・・INDIRECT関数でテーブル名［見出し］を指定することに・・ここでは・・A列で選択されたテーブルの【商品】という見出しの内容をリスト表示してね・・という意味に・・。

VLOOKUP関数で単価を取り出す

更に・・商品が選択されたら単価が表示されるようにしたい・・。

単価が表示されるのはC列にしたいので・・C列にVLOOKUP関数で数式を書き込むことに・・。

書き込んだ数式は・・【=IF(ISBLANK(B2),””,VLOOKUP(B2,INDIRECT(A2),2,0))】というもの・・。

この数式は・・前半の【=IF(ISBLANK(B2),””,】は・・B2のセルが空白なら空白にするということ・・後半の【VLOOKUP(B2,INDIRECT(A2),2,0))】は・・A3セルのテキストと一致するテーブルから・・B2セルのテキストを探し出し・・その一つ右の値を取り出すということ・・。

後は2行目のセルをコピーして・・必要な行数に張り付ければOK