スプレッドシートで連動するプルダウン【2段階】を作成する方法

リストから選択入力にすることで入力ミスを防ぐのに便利なプルダウンですが、階層構造にできるのを知っていますか？連動プルダウンを作成すれば表示されるリスト数を減らせるので、視認性と効率性のアップに繋がります。

２段階プルダウンとは？

プルダウンの１段階目と２段階目を連動させることです。１段階目をプルダウンリストから選択すると、その項目に従属するリストのみを２段階目に表示させます。

リスト表を階層構造で作成することで、例えば１段階目で「ドリンク」を選択すると２段階目でドリンクメニューが、１段階目で「パン」を選択すると２段階目ではパンメニューだけを表示させることができます。

プルダウンの選択肢に指定するリストを、事前に作成しておきます。

縦方向に1段階目の項目を入力し、その横方向に従属する２段階目の項目を入力してください。

まず1段階目のプルダウンを挿入する範囲を選択し、挿入タブからプルダウンをクリックします。

次に入力規則の条件から プルダウン(範囲内) を選び、データ範囲を選択を開いてください。準備しておいたリストを選択し、OKボタンをクリックします。

プルダウンのアイテムに、選択したリストの値(ドリンク・ぱん)が追加されました。

最後に完了ボタンをクリックすると、まずは1段階目のプルダウンの完成です。なお今回は色アイコン(○)から、2つの選択肢を色分けしています。

１段階目のプルダウンは問題なく作成できたでしょうか。今回ご紹介する方法では名前付き範囲とINDIRECT関数を活用して、２段階目のプルダウンを作成していきます。

まず２段階目の選択肢に指定するリストから、１行目のみ選択ししてください。

次に右クリックメニューのセルでの他の操作項目を表示から、名前付き範囲を定義 を開きます。このとき、選択した行が所属する1段階目の項目名を入力します。

1段階目で選んだ値と一致する項目名で、2段階目のリストを呼び出します。そのため定義する名前は、２段階目のリストが所属する１段階目の項目名を使用しましょう。

完了ボタンをクリックして1行目の名前が定義できたら、範囲を追加(+) から2行目以降も同じように名前を定義していきましょう。

なお数式バーの左にある名前ボックスからも名前の定義と確認が可能です。

任意の場所に INDIRECT関数を入力し、参照文字列にプルダウン1段階目の最初のセルを指定します。例：=INDIRECT(A2)

例では参照文字列(A2)がドリンクなので、名前付き範囲”ドリンク”が呼び出されました。

INDIRECT関数の構成要素：(参照文字列, 参照形式(省略可))

入力したINDIRECT関数を、オートフィルでプルダウンの行数分コピーしてください。

例のように1段階目で選択されている項目に応じて、2段階目のリストが呼び出されます。

#REF!を消したい場合は =IFERROR(INDIRECT(A2),””) のように、エラーのときは空白になるように設定しましょう。

2段階目プルダウンの１行目のセル(B2)のみ選択し、挿入タブからプルダウンを開きます。

データ範囲にはINDIRECT関数で取得した名前付き範囲を、こちらも1行目のみ指定してください。ここで一旦、完了ボタンをクリックして保存します。

編集ボタンから、再びB2セルのプルダウン設定を開きます。

範囲に自動で固定記号($)が追加されているので、$ を全て削除してください。

例のように、固定なしの状態で保存し直します。

最後にB2セルをオートフィルでコピーすれば、2段階(連動)プルダウンの完成です。

今回は分かりやすいように、プルダウンの隣にリスト表を作成しました。表は非表示にしたり別のシートに作成すれば、見た目もすっきりと仕上がります。