・2種類の表から参照範囲を自動で切り替えて値を取り出す
練習用サンプルデータ（ダウンロード）
※Excelは練習用（問題）と成果物（解答）の２種類あります。閲覧したい方はダウンロードして下さい。
区分に応じて選択肢が連動して切り替わるドロップダウンを作ってみたいと思います。
前回の【現場の実務14】で使ったINDIRECT関数の応用編になります。
入力規則にINDIRECT関数を組み合わせると、区分に応じて、選択肢変えることができます。
（練習で実施するドロップダウンの完成図）
※beforeからafterの状態まで仕上げる過程を説明します。
サンプルは、右側の「家電」と「洋服」の表にある商品リストを、C列にプルダウンで選択できるようにします。プルダウンは、区分で家電や洋服を入力すると、自動で選択肢も切り替わるようにすること。＆単価も一緒に右側の表から自動反映するように作ること。
練習サンプルデータは、ダウンロードファイルのシート「練習用」となります。
‘【１】2種類の表を自動で切り替えて値を取り出す方法
（１）名前の定義をつける（プルダウンで使う商品範囲に）
まず、下準備で2種類の表の「家電」と「洋服」のリストを範囲選択して「名前の定義」を登録しましょう。
では、名前の定義に名前を登録します。
‘①F５からF9を範囲選択してください。
‘②名前ボックスに「家電」と入力しENTERキーを押す
すると、F５からF９までの範囲が、「家電」と名前が定義されました。
洋服も同様にやってみましょう。
‘③I５からI９まで範囲選択し、「名前ボックス」に食べ物と入力しENTERキーを押す
‘（２）入力規則の活用（INDIRECT）
‘①セルC4を選択し、入力規則を開きます。そして、「入力値の種類」はリストを選択してください。そして「元の値」に「= INDIRECT(B4)」を入力しOKをクリック。
※INDIRECT関数は 「名前の定義」で登録したセル範囲を自動で引っ張ることができる関数
‘②すると、セルD４に、プルダウンリストが表示されました。
‘③C4をコピーして、C5からC10まで範囲選択してください。
右クリックして「形式を選択して貼付け」を選択し、入力規則にチェック入れてOK
その後、ESCキーを押してコピーモードを解除しておきましょう。
‘④すると、区分に応じて、プルダウンリストが表示されます。
‘【２】2種類の表から値（単価）を取り出す
ここで、「現場の実務編１４」で使ったやり方が、うまく実現できないことが判明します。
ここでの問題は、すでに「名前の定義」で【家電】と【洋服】の名前を使ってしまっていることです。名前の定義では、同じ名前をF５～G10とI５～G10の範囲に定義することはできません。そこでINDIRECT関数の参照文字列の指定にもちょっとした工夫が必要となります。その解決法をご紹介します。
※「現場の実務編14」で、INDIRECT関数とVLOOKUP関数を使って単価の自動反映
を紹介しています。
‘①F５～I10を選択し、名前ボックスに「家電商品」と入力しENTERキーを押す
（※家電の後ろに商品を付けていますが、 “商品”以外の文字でもかまいません。）
‘②同じように、I５～J10にも「洋服商品」と名前をつけます。
‘③単価に数式を入力していきます。まず、D４を選択します。
そして、数式「=VLOOKUP(C4,INDIRECT(B4&”商品”),2,0)」を入力します。
‘④すると、D4に単価が表示されました。
「数式」と「名前の定義」で付けたセル範囲との関係図は以下を参照ください。
（解説）
「B4＆ “商品”」は「家電商品」という文字列になります。
「INDIRECT (B４＆ “商品” )」は「INDIRECT(家電商品)」となり、「家電商品」と名前をつけた範囲「F５～G10」の、商品名がリストに表示されます。
‘⑤セルD４をコピーしてD５～D１０まで張り付けると計算式が反映できます。
‘⑥すると、以下のように計算式が貼り付けられます。
C列の商品が選択されていないセルは、「＃N/A」とエラー表示となっています。
商品を選択されると単価は計算され表示されます。
【３】エラー（＃N/A）を表示させなくする方法
IFERROR関数を使うとエラー表示させなくすることができます。
‘①まず、セルD４を選択します。数式バーの「VLOOKUP(C4,INDIRECT(B4&”商品”),2,0) 」の数式を「CTRLキー＋Xキー」で切り取ってください。
※「＝」は除いて切り取ってください。
‘②「ｆｘ「」をクリックして、「関数の検索」で、IFERRORと入力し、「検索開始」ボタンを押す。「関数名」にIFERRORが選択されたことを確認し、「OK」をクリック。
‘③IFERRORのダイアログボックスが起動されたら、「値」を選択し、さきほど切り取った数式を貼付け（CTRLキー＋Vキー）。
エラーの場合の値を選択し「“”」を入力（エラーは空白になります）。
入力完了したら、「ＯＫ」をクリック。
‘④すると、セルD４にIFERRORの計算式が反映しました。
‘⑤セルD４をコピーして、D５からD10まで貼付けましょう。
すると、他のセルにも計算式が反映されエラーが消えました。
【４】まとめ
INDIRECT関数は組みあわせて使うと、セル範囲を自動設定する用途などで活躍できる関数だとわかってきます。
プルダウンリストなどでも、３つ以上の複数の表の商品マスタからでも、自動で切り分けてリスト表示することが可能ですので、一度お試しください。
