一番上の入り口へ >
excel >
ちょいわざ >
入力規則をセルによって自動的に変わるようにするちょいわざ
入力規則をセルによって自動的に変わるようにするちょいわざ
入力規則を普通に使うと対象のリストは固定されていますよね。
でもたまに、1段階目のドロップダウンリストで東京都を選んだら、2段階目で23区が選択肢になるように
動的に変わって欲しい時もあります。
そんな時のためのわざを紹介します。
（ただし、名前の機能と、入力規則のリストの機能がよくわかっていないと理解出来ないと思います。）
まず２つの内一つめを説明します。
やり方(1段階バージョン)
入力規則のリストでは範囲を指定するので、指定されている範囲の値を動的に変えればよいよねと考えたパターンです。
・step1:赤字の種別の内容によって、緑色のリストが動的に変わるようにする
・step2:入力規則範囲は、単純に緑色のリストを範囲とする
種別をベジタブルにすると、もちろん入力規則の選択対象も以下のように切り替わります。
ちなみに、指定範囲を変えるやり方は
offset関数を使えば数値で範囲を変えられることを利用し
次の2段階バージョンのやり方に近いイメージで行えます。
入力規則の中で offset関数を使い、引数にあるセルを指定します。
そのセルの実態の数値を 例えばcountifなり、ifなりで変えれば、
最終的に、入力できる範囲を動的に変更させられます。
場所を表す関数
,参照先シートを文字で指定するちょいわざ はこちら。
やり方(2段階バージョン)
2段階方式では、1段目で選んだ内容によって、2段目の内容が切り替わるようになっています。
事前準備として図の右側では、
2段階目の内容となる部分(F:H)を範囲指定で各列に名前を定義しておきます。
(それぞれ「任天堂」、「SONY」、「SEGA」という名前を定義)
1段階目の入力内容をリンクさせるために、そのリストを示すラベル名と範囲指定の名前を同じにしておきます(濃い緑部分のF2:H2)。
これで 任天堂という名称の範囲はF3:F9 となり、 SEGAという名称の範囲はH3:H5の"MD〜DC"が対象となるようにした状態。
そして図の左側に戻って
1段階目(C列)の入力規則ではそのラベル名だけ(F2:H2)を指定できるようにしておき(設定しなくても良い)、
2段階目(D列)の入力規則では1段階目(C列)の内容を入力規則のリスト範囲(INDIRECT関数を利用)としています。
INDIRECT関数を利用して相対参照し、(名前を使っているため)実体となる右側の名前を指定した範囲が選択対象となります。
下の例のように、C4が"任天堂"となっているのでAさんの二段目の選択肢(D4)は、F3:F9の範囲が対象になります。
C4に入力された値から、D4上では入力規則のリスト(ダイアログ内=INDIRECT(C4))の指定を見て任天堂という名称の範囲であるF3:F9を対象としています。
これで二重の構造化リストに対応できました。
説明の修飾子が長いなぁ、、
参考
入力規則についてはこちら
名前についてはこちら
類似リンク
・入力規則の対象リストを他のシートでまとめて、綺麗に表示させるちょいわざ
・入力規則をセルによって自動的に変わるようにするちょいわざ
その他のちょいわざはこちら