スプレッドシートのプルダウンを連動させる方法

プルダウンを連動させる方法

スプレッドシートでプルダウン項目を選択した際、他のプルダウン項目の選択肢が自動で変わるようすることで、不要な情報が表示されず、見た目がスッキリして使いやすくなります。今回はそんなプルダウン項目を連動させる方法を、画像付きで丁寧に解説していきたいと思います。

まずはプルダウン項目をつくる

連動させる必要がないシンプルなプルダウンの作り方についてはこちらの記事で紹介しています。

最初に完成形のイメージを見ていきましょう 今回は『都道府県』と『店舗リスト』のプルダウン項目が連動されるようにしました。

完成イメージ

1つ目のプルダウンから『都道府県』を選択すると、2つ目のプルダウンの『店舗リスト』には選択された県内のものだけが表示されるようになります。

プルダウンを連動させるためのポイントと流れ

プルダウンの連動設定には少し手間がかかるので、作業を行うまえに、ポイントとおおまかな流れをイメージとして掴んでおきましょう。

今回のポイント
  • 『操作閲覧用』(表側)と『参照用データリスト』(裏側)の2つのシートを使用
  • 『参照用データリスト』内には内容の変わらないデータベースと、『操作閲覧用』シートで表示させるための可変データベースの2つを作成
  • 『iferror』と『VLOOKUP』という2つの関数を使用
おおまかな流れ
  1. 二つのプルダウンが表示される枠を作成する
  2. 『参照用データリスト』を作成する
  3. 都道府県のプルダウンリスト項目が『参照用データリスト』から読み込まれるようにする
  4. 選択された都道府県により可変データリストが作成されるようにする
  5. 店舗リストの選択項目に可変データベースが読み込まれるようにする

別シートの値を参照する方法については下記の記事で解説しています。

プルダウンを連動させるための手順

表側のシートを作成

操作閲覧用のシートに、『都道府県』と『店舗名』というセルを作成します。 こちらのシートは、わかりやすいように『表』という名前にしました。

参照用のデータベースシートを作ろう

新たにシートを追加してください。 今回はシンプルに『データ』というシート名で作成しました。

新規シート作成

データベースとなるリストを作成

まずはもとになるデータベースを作成します。

プルダウン表示用のデーターベース

わかりやすいように色や文字を装飾していますが、今回の挙動には関係ありません。

上段にある『参照用データリスト』がプルダウン内で表示される要素となります。 Aの列に『都道府県』(1つ目のプルダウン)の選択肢、それぞれの行に『店舗』(2つ目のプルダウン)の選択肢が並ぶように作成してください。

将来的にデータが増える場合はここに追加することになりますので、少し余裕をもたせた設計にしておくといいかもしれません。

このデータベースの下に『表示用データリスト』というスペースを確保します。 のちほど、ここに関数を入れることで、表側で選択した『都道府県名』を元に、『店舗名』で選択できる項目を抽出し、制御するようなイメージを持っていてください。

1つ目のプルダウンの入力規則を設定

表のシートの『都道府県』の下にプルダウンを設定します。 セルを選択した状態で右クリックし、一番下までスクロールすると『データの入力規則』の項目があるので選択します。

データの入力規則を設定する

入力規則の設定ウィンドウが出てきますので、範囲を指定します。 ここではデータというシートの県名が入っているセルを選択する必要があるため、下記のようになります。

‘データ’!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),””)

VLOOKUP関数

つまり、A9のセル(表側で選択した都道府県名)の検索の値を、検索する範囲を参照元データベースのA2からI5内から探し、見つけた文字列から2つ目(隣のセル)の文字列を、完全一致した場合に返すという関数になります。

画像の場合、表側で埼玉県を選択したため、自動的に埼玉県が検索語となり、検索範囲と一致したため、2列めの「大宮店」の文字列が返されました。隣のセルにも関数を入力していく必要がありますが、3つ目の引数の部分のみ数を増やしていくことになります。

VLOOKUP(A9,A2:I5,3,0)

すべてを入力したあと、表側の都道府県を神奈川にしてみました。 神奈川県の店舗名がきちんと表示されています。

きちんと表示されるのを確認したら下方向にドラッグしてコピーします。 この時、範囲である『A2:I5』は固定したいので絶対参照にします。

=iferror(VLOOKUP(A9,$A$2:$I$5,2,0),””)

絶対参照については下記記事で詳しく解説しています。

下方向にドラッグ

見た目は空白のままで変わらないように見えるかもしれませんが、表側の都道府県のプルダウンからいずれかの県を選択すると表示されます。 ※もしプルダウン項目を選択してもうまく表示されない場合はコピー元のセルの内容が間違ってないか確認してください。

さぁ、あと一歩です。県名によって抽出、表示された項目を表側の店舗名プルダウンに反映させれば終わりです。

データ用のシートは、『店舗』の追加など大きな変更を加えたい時以外はさわる必要がありません。 このようなシートを他の人と共有して使う場合、意図せず変更を加えてしまうこともありえますので、実際に運用する場合はシートを保護することをおすすめします。

シートの保護についてはこちらの記事で解説していますので実際に運用する際はあわせて参考にしてみてください。

2つ目のプルダウンに連動した結果が反映されるようにする

店舗名の下部のセルを選択して、右コピーからデータの入力規則を選択します。

データの入力規則を選択 データの入力規則を設定 プルダウンの連動が成功

うまくプルダウンの連動をさせることができました。

まとめ

いかがだったでしょうか。 少し手間はかかるものの、一度やり方を覚えてしまえばそんなに難しくはないと思います。 今回解説した2つの関数のうち、VLOOKUP関数については使う頻度も高いのでしっかり覚えておくと役に立つでしょう。

今回は関数も紹介しましたが、関数を使いこなせるようになると、やれることが大幅に増え、作業効率もアップします。 関数については下記の記事でまとめていますので良かったあら合わせてご覧ください。