一番上の入り口へ > excel > ちょいわざ > 入力規則がセルによって自動的に変わるようにするちょいわざ

入力規則がセルによって自動的に変わるようにするちょいわざ

--

入力規則を普通に使うと対象のリストは固定されていますよね。

でもたまに、1段階目のドロップダウンリストで東京都を選んだら2段階目で23区が選択肢になるように、1段目に福岡を選んだら2段目が福岡県の市が一欄になるように動的に変わって欲しい時もあります。

そんな時のためのわざを紹介します。
(ただし、名前の機能と、入力規則のリストの機能がよくわかっていないと理解出来ないと思います。)


まず2つの内一つめを説明します。

やり方(1段階バージョン)

入力規則のリストでは範囲を指定するので、その"指定されている範囲"の中の値を直接動的に変えればよいよね(F列↓)と考えたパターンです。


・step1:赤字の種別の内容によって、緑色のリスト(F列)が動的に変わるようにする
・step2:入力規則範囲は、単純に緑色のリスト(F列)を範囲とする


種別をベジタブルにすると、もちろん入力規則の選択対象も以下のように切り替わります。


この方法の欠点は、見てわかるように右側のデータの入力が面倒。3列4列になるとif文が果てしない感じです。。

ちなみに、指定範囲を変えるやり方は

offset関数を使えば数値で範囲を変えられるので、 そのことを利用して次の2段階バージョンのやり方に近いイメージが使えます。

入力規則の中で offset関数を使い、引数にあるセルを指定します。 そのセルの実態の数値を 例えばcountifなり、ifなりで変えれば、 最終的に、入力できる範囲を動的に変更させられます。

場所を表す関数 ,ちょっとかっこ良く柔軟にすっきりと参照先シートを文字で指定するちょいわざ はこちら。

やり方(2段階バージョン)

2段階方式では、1段階目で選んだ内容によって、2段階目の内容が切り替わるようになっています。

木構造のように 2段階リストをイメージしてください。

├─[任天堂[ファミコン,スーファミ, ...] ]
├─[SONY[PS,PSP, ...] ]
└─[SEGA[MD,SS, ...] ]


1段階目は、会社名を選択します。 2段階目は、選択した会社の商品を選択します。
(他の例としては、1段階目は、都道府県を、2段階目は市区町村を指定するイメージです)

事前準備として図の右側では、

会社名のリストを濃い緑で右側の表に書いています。 その会社名の製品リストが縦に並んでいます。

会社名をまず選択させ、その選択内容に応じてリスト範囲を切り替えるということをします。

そのため、1段階目の会社リストと 2段階目の内容となる部分をあわせて(F:H)に書き、 範囲指定で各列に名前を定義しておきます。
(それぞれ「任天堂」、「SONY」、「SEGA」という名前を定義した列が右の表にある)

1段階目の入力内容を2段階目にリンクさせるために、そのリストを示すラベル名と範囲内の名前を同じにしておきます(濃い緑部分のF2:H2)。つまり、会社の製品の範囲を会社名の名前でセットします。
具体的に言うと、これで F3:F9の範囲は任天堂という名称で「名前」定義を設定し、F2に任天堂と記載。同様に、 SEGAという名称の範囲はH3:H5の"MD〜DC"が対象となるように「名前」を設定した状態。
正確に言うと、2行目はなくてもよいですが、会社名選択(1段階目)のリスト選択の名称として使います。
ラベルの定義で使用した Excelの名前についてはこちら


そして図の左側に戻って

考え方

やりたいことは、会社名を選んだらその会社名に応じた製品群リストから選ぶということ。
前文の後半は普通の入力規則でできますが、その範囲指定を直接(F3:F9)ではなく間にクッションを置いて間接的にする必要があります。

例えばF3:F9と直接指定するのではなく、入力規則は左のセルを見てねと指定します。その左のセルは、右側で選択させたい対象範囲を記載します。
なので左のC列で F3:F9を選ぶと、 D列の入力規則の内容が F3:F9になります。C列でG3:G9を選ぶとD列の選択対象がG列になります。
(今回の例では、さらに F3:F9とはせずに、わかりやすいように その範囲に会社名を付けて、選択させています。先ほどF2:H2列のリストやその製品リストを会社名にしたので、見た目も、Excelの指定も同じように設定できます。)

人間での認識 → F2:H2(まず会社名の選択リストに使う)
   ↑↓ これらをリンク
Excelの認識 → 製品名の対象範囲の名前(入力規則で使われる)



実際の設定

1段階目(C列)の入力規則ではそのラベル名だけ(F2:H2)を指定できるようにしておき(入力規則か自由欄かはお好みで)、
2段階目(D列)の入力規則では1段階目(C列)の内容を入力規則のリスト範囲(INDIRECT関数を利用)としています。
INDIRECT関数を利用して相対参照し、(名前を使っているため)実体となる右側の名前を指定した範囲が選択対象となります。





下の例のように、C4が"任天堂"となっているのでAさんの二段目の選択肢(D4)は、F3:F9の範囲が対象になります。
C4に入力された値から、D4上では入力規則のリスト(ダイアログ内=INDIRECT(C4))の指定を見て任天堂という名称の範囲であるF3:F9を対象としています。



これで二重の構造化リストに対応できました。

説明の修飾子が長いなぁ、、

参考

入力規則についてはこちら


類似リンク

入力規則の対象リストを他のシートでまとめて、綺麗に表示させるちょいわざ

入力規則をセルによって自動的に変わるようにするちょいわざ




その他のちょいわざはこちら