上の画像のように社員名をプルダウンで表示させたいときに、全社員がすべて出てきてしまうと表示される量も多く、だれがどの部署なのかもわからないので非常に不便です。
この場合まず部署を選んで、その部署の人だけが社員名のプルダウンで表示されるとシンプルで使いやすくなります。
そこで、今回は大分類→小分類と二つのプルダウンを連動させる方法をご紹介いたします。
基本のプルダウンを作成する方法はこちらを参照してください。プルダウン(ドロップダウン)を作る Excel(エクセル)
二つのプルダウンを連動させる方法は主に二つあり、参照元のリストが縦に1列に並んでいるパターンと大分類を横軸に表にするパターンがあります。
後者はこちらマクロ無しで２つの入力規則を連動させる(複数行に反映させる場合) Excel(エクセル)でご紹介していますので、今回は前者の方法をご紹介いたします。
大分類(部署)のリストを作成する
まず部署のリストを作成します。
これは特に説明不要だと思います。縦一列に部署を並べます。
小分類(社員名)のリストを作成する
次に社員リストを作成します。
この時に社員名の隣にその社員の部署を並べます。この時部署名が完全一致するように注意してください。空白があったり半角全角が違っていたりするとうまく参照することができなくなってしまいます。
次に部署の隣にこのような関数を記述します。
=E4&COUNTIFS(E$4:E4,E4)この時「$」マークを入れる場所に注意してください。
そしてこの関数を一番下までコピーします。
=E17&COUNTIFS(E$4:E17,E17)下までコピーしたときに「$」マークを入れる場所が正しければこのような関数になります。
この関数がやっているのはこの社員と同じ部署が自分合わせて上に何人いるかを数えています。これにより部署の番号を上から順に連番にすることができます。
入力フォームを作る
今回「フォーム」というシート名にしてそこにフォームを作ることにします。
部署名は「B3」セルに作ることとしました。
小分類のプルダウン参照リストを作成する
今回「マスタ」シートの「I4」セルに
=フォーム!$B$3&ROW()-3と記載します。
この関数の意味は「フォーム」シートの「B3」セルを絶対参照で部署名を参照し、その後ろに連番を表示させるためにROW()で行数、4行目なので3を引いて連番が1になるようにしています。
この関数を下までコピーします。
ここで一番下といってももともと何行かデータがあるわけではないので、何行まで作るかは任意で決めることになります。この行数は一つの部署の人数よりも多い行数になるようにしてください。人数よりも少ないとその部署を選択したときに最後の人が選択できなくなってしまいます。
関数を下までコピーした後、「フォーム」シートで
このように部署を選択すると
「マスタ」シートではこのように表示されます。
次にJ列にindex・match関数を使って
=INDEX(D:D,MATCH(I4,F:F,0))このように記載し、一番下までコピーします。
index・match関数の詳しい使い方はこちらで解説しています。Excel(エクセル)INDEX関数とMATCH関数の組み合わせた使い方
するとJ列にこのように表示されました。
F列に存在しない「第一営業4」以降のデータにはエラーが表示されてしまいました。
そのためJ列の関数をそれぞれIFERROR関数で挟んでこのように記載します。
=IFERROR(INDEX(D:D,MATCH(I4,F:F,0)),"")
これでF列に存在しない行には空欄が表示され、エラーは消えました。
社員名のプルダウンを設定する
最後に「フォーム」シートの社員名の入力欄に入力規則を設定します。
「データ」タブの「データの入力規則」をクリックします。
入力値の種類を「リスト」に変更し、このボタンから元の値の範囲を指定します。
「マスタ」シートのこの部分、この例でいえばJ4からJ列の最後の行までを指定し「Enter」キーを押します。
するとこの画面に戻ってきますので「OK」ボタンをクリックします。
これで「B3」セルを切り替えることによって、「B7」セルの選択肢が連動する設定が完成しました。
この方法のメリット
- 連動させたいデータの一覧が縦に一列に並んでいる場合に便利
別で紹介する参照するリストが表にまとめられている場合はそちらマクロ無しで２つの入力規則を連動させる(複数行に反映させる場合) Excel(エクセル)の表の方が便利なのですが、データが一列に並んでいる場合表に並べなおす必要があるので、その場合はこちらの方法が便利です。
- マスタのメンテナンスが簡単
今回の例で社員が部署移動した場合、その社員の部署項目をプルダウンで変更するだけでメンテナンスが完了します。
例えば竹田さんが総務部から開発部に移動になった場合、社員リストの部署欄を修正するだけで
社員名に表示される選択肢を変えることができます。
もし表になっていると両方の表を修正しなければならないため作業工数が増えてしまい、ミスが発生しやすくなってしまいます。
この方法のデメリット
- 複数行の連動に使えない
最大のデメリットがこれですが、今回の例では作業シートで連動させる対象のセルが１行しかありませんが、これが各行ごとにそれぞれ連動させようとするとマクロを使わない限り実現不可能です。そのような使い方をしたい場合は表になっている場合を使うしかありません。
- 作り方が複雑
こちらもなかなか大きなデメリットなのですが、ある程度関数の知識が無いと作るのが難しいのと、たくさんの関数を組み合わせて作るので少々複雑になってしまいます。
コメント