スプレッドシートでプルダウン項目を選択した際、他のプルダウン項目の選択肢が自動で変わるようすることで、不要な情報が表示されず、見た目がスッキリして使いやすくなります。今回はそんなプルダウン項目を連動させる方法を、画像付きで丁寧に解説していきたいと思います。
まずはプルダウン項目をつくる
連動させる必要がないシンプルなプルダウンの作り方についてはこちらの記事で紹介しています。
最初に完成形のイメージを見ていきましょう 今回は『都道府県』と『店舗リスト』のプルダウン項目が連動されるようにしました。
１つ目のプルダウンから『都道府県』を選択すると、２つ目のプルダウンの『店舗リスト』には選択された県内のものだけが表示されるようになります。
プルダウンを連動させるためのポイントと流れ
プルダウンの連動設定には少し手間がかかるので、作業を行うまえに、ポイントとおおまかな流れをイメージとして掴んでおきましょう。
- 『操作閲覧用』（表側）と『参照用データリスト』（裏側）の2つのシートを使用
- 『参照用データリスト』内には内容の変わらないデータベースと、『操作閲覧用』シートで表示させるための可変データベースの2つを作成
- 『iferror』と『VLOOKUP』という2つの関数を使用
- 二つのプルダウンが表示される枠を作成する
- 『参照用データリスト』を作成する
- 都道府県のプルダウンリスト項目が『参照用データリスト』から読み込まれるようにする
- 選択された都道府県により可変データリストが作成されるようにする
- 店舗リストの選択項目に可変データベースが読み込まれるようにする
別シートの値を参照する方法については下記の記事で解説しています。
プルダウンを連動させるための手順
表側のシートを作成
操作閲覧用のシートに、『都道府県』と『店舗名』というセルを作成します。 こちらのシートは、わかりやすいように『表』という名前にしました。
参照用のデータベースシートを作ろう
新たにシートを追加してください。 今回はシンプルに『データ』というシート名で作成しました。
データベースとなるリストを作成
まずはもとになるデータベースを作成します。
わかりやすいように色や文字を装飾していますが、今回の挙動には関係ありません。
上段にある『参照用データリスト』がプルダウン内で表示される要素となります。 Aの列に『都道府県』（１つ目のプルダウン）の選択肢、それぞれの行に『店舗』（２つ目のプルダウン）の選択肢が並ぶように作成してください。
将来的にデータが増える場合はここに追加することになりますので、少し余裕をもたせた設計にしておくといいかもしれません。
このデータベースの下に『表示用データリスト』というスペースを確保します。 のちほど、ここに関数を入れることで、表側で選択した『都道府県名』を元に、『店舗名』で選択できる項目を抽出し、制御するようなイメージを持っていてください。
１つ目のプルダウンの入力規則を設定
表のシートの『都道府県』の下にプルダウンを設定します。 セルを選択した状態で右クリックし、一番下までスクロールすると『データの入力規則』の項目があるので選択します。
入力規則の設定ウィンドウが出てきますので、範囲を指定します。 ここではデータというシートの県名が入っているセルを選択する必要があるため、下記のようになります。
‘データ’!A2:A5
さらに、選択肢以外の入力をしてほしくないため、『入力を拒否』にクリックして保存します。
都道府県のプルダウンが反映されました。
右下を掴んでそのまま下にドラッグすればプルダウンがコピーされますので、必要な分だけおこなってください。
プルダウンの選択肢用に可変するデータリストを作成する
表側の都道府県のプルダウンが選択できるようになったので、次は裏側のシート「データ」の編集です。 可変で表示される表示用データリストの都道府県名のセルを選択し、表側で選択された県名が表示されるようにします。
=’表’!B3
表側で選択した都道府県が表示用リストの県名に表示されるようになりました。 それでは、それらの県名ごとに店舗名リストが表示されるようにしましょう
VLOOKUP関数を使用して固定用のリストから、県名に応じた店舗リストを表示させます。 隣のセルに移動し、下記のように入力してください。
=iferror(VLOOKUP(A9,A2:I5,2,0),””)
いきなり関数が出てきて戸惑ったかもしれませんが、一つずつ解説します。
iferror関数
=iferror(VLOOKUP(A9,A2:I5,2,0),“”)
iferror関数は、エラーの場合に指定した値を返す関数です。 今回は特に文字が必要ないのでダブルクォーテーションの中身を空にしてありますが、任意の文字列をいれ、左のセルを空にしてみれば、ダブルクォーテーション内に入力した文字列が表示されます。
VLOOKUP関数
VLOOKUP関数は、垂直（縦）方向(Vertical)に、条件に一致した検索を行い、結果を返してくれる関数です。 指定している中身は(検索する値、検索する範囲、返す値を含む範囲内の列番号、近似値または完全一致）となっています。
=iferror(VLOOKUP(A9,A2:I5,2,0),””)
つまり、A9のセル（表側で選択した都道府県名）の検索の値を、検索する範囲を参照元データベースのA2からI5内から探し、見つけた文字列から２つ目（隣のセル）の文字列を、完全一致した場合に返すという関数になります。
画像の場合、表側で埼玉県を選択したため、自動的に埼玉県が検索語となり、検索範囲と一致したため、２列めの「大宮店」の文字列が返されました。隣のセルにも関数を入力していく必要がありますが、3つ目の引数の部分のみ数を増やしていくことになります。
VLOOKUP(A9,A2:I5,3,0)
すべてを入力したあと、表側の都道府県を神奈川にしてみました。 神奈川県の店舗名がきちんと表示されています。
きちんと表示されるのを確認したら下方向にドラッグしてコピーします。 この時、範囲である『A2:I5』は固定したいので絶対参照にします。
=iferror(VLOOKUP(A9,$A$2:$I$5,2,0),””)
絶対参照については下記記事で詳しく解説しています。
見た目は空白のままで変わらないように見えるかもしれませんが、表側の都道府県のプルダウンからいずれかの県を選択すると表示されます。 ※もしプルダウン項目を選択してもうまく表示されない場合はコピー元のセルの内容が間違ってないか確認してください。
さぁ、あと一歩です。県名によって抽出、表示された項目を表側の店舗名プルダウンに反映させれば終わりです。
データ用のシートは、『店舗』の追加など大きな変更を加えたい時以外はさわる必要がありません。 このようなシートを他の人と共有して使う場合、意図せず変更を加えてしまうこともありえますので、実際に運用する場合はシートを保護することをおすすめします。
シートの保護についてはこちらの記事で解説していますので実際に運用する際はあわせて参考にしてみてください。
２つ目のプルダウンに連動した結果が反映されるようにする
店舗名の下部のセルを選択して、右コピーからデータの入力規則を選択します。
うまくプルダウンの連動をさせることができました。
まとめ
いかがだったでしょうか。 少し手間はかかるものの、一度やり方を覚えてしまえばそんなに難しくはないと思います。 今回解説した2つの関数のうち、VLOOKUP関数については使う頻度も高いのでしっかり覚えておくと役に立つでしょう。
今回はスプレッドシートの関数も紹介しましたが、関数を使いこなせるようになると、やれることが大幅に増え、作業効率もアップします。
関数については下記の記事でまとめていますので良かったら合わせてご覧ください。
スプレッドシートの使い方については下記の記事でまとめていますので併せてお読みください。
