Excel。入力規則のリストはINDIRECT関数を使うと切り替えることが出来る
入力規則+INDIRECT関数＋名前の定義
先日ご質問があった中から、
今回は、入力規則のリスト+INDIRECT関数＋名前の定義のテクニックをご紹介ようと思います。
どんな質問だったのかというと、ある帳票があって、入力ミスを防ぐ意味もあって、
入力規則のリストからアイテムを選択できるようにしたいんだけど、
項目名によって選択できるアイテムがかわるので、入力規則のリストがつかえないので、
困っているので何かいい方法ってありませんか？とのことでした。
なんでも、手入力で頑張っているというものでした。
確かに、現場では様々なケースがありますので当然今回のようなこともあるでしょう。
ということで、こんな方法はどうかなぁ～ということでやってみます。
下記の表があります。
B4に路線名。つまり中央線か山手線をいれると、
C4には、中央線なら中央線の山手線なら山手線の駅名を、
C4に入力することが出来るようにしたいというのが、今回の目的です。
では、B4に入力規則のリストを設定していきます。
B4をクリックして、データタブのデータの入力規則をクリックします。
データの入力規則ダイアログボックスが表示されますね。
設定タブの条件の設定にある、
入力値の種類を リストに設定します。
元の値は、中央線・山手線と入力されている範囲、
=$E$3:$F$3
を入力してOKボタンをクリックしましょう。
B4に▼が表示されましたので、
クリックすると、
中央線と山手線が選択できるようになっていますね。これが入力規則のリストですね。
そして本題はここから、
中央線と入力したら、C4の入力規則のリストには、中央線の駅名が、山手線にしたら、
山手線の駅名がリストにあるようにしたいというわけです。
そこで、登場するのが、
INDIRECT関数です。
このINDIRECT関数は名前などをダイレクトに使用することが出来る関数です。
では、準備からいきましょう。
E4:E10に中央線という名前をF4:F9に山手線という名前を定義します。
これらの範囲に名前を設定してその前を使って切り替えるようにしていきます。
E4:E10を範囲選択して、名前ボックスをクリックして、中央線と入力します。
続いて、F4:F9を範囲選択して、同じように名前ボックスに山手線と入力して、
それぞれの範囲に名前を設定します。範囲にニックネームをつける感じですね。
この名前のポイントは、B4で設定したリストにあるものにします。
山手線内回りとかにしてはいけないわけです。
それでは、C4をクリックして、データタブの入力規則のダイアログボックスを表示しましょう。
入力値の種類は、リストに合わせるところは先程と同じです。
元の値には、 =INDIRECT(B4) と入力します。
INDIRECT関数でB4の文字を参照します。
すなわち、B4に入っている文字そのものが元の値に入力されるという意味になります。
つまり、元の値には、 中央線 と入力したのと同じことになります。
そして、この中央線というのは…そう、範囲選択した時に設定した名前ですね。
中央線と名前を設定した範囲ですので、E4:E10ということになります。
あとはOKボタンをクリックしましょう。
C4の▼をクリックしてみると、
中央線の駅名がリスト内にありますよね。では、B4を山手線にしてみましょう。
すると今度は、山手線の駅名がリスト内にありますよね。
このようにINDIRECT関数を使うと入力規則のリストを切り替えることができますので、
参考にしてみてください。
VLOOKUP関数との組み合わせもバッチリですよ。
Excel。VLOOKUP関数。その9 範囲の表が複数ある場合ってどうするの？
http://infoyandssblog.blogspot.jp/2014/08/excelvlookup9.html
