最近は仕事でもプライベートでもExcelでなくGoogleスプレッドシートを使用する機会が増えているという方も多いのではないでしょうか。

今回はGoogleスプレッドシートで、セルにプルダウンリストを設定する際に、他のセルの値に連動してリストの内容を変更する方法の備忘録です。

具体例を挙げると、Ａ列には五畿七道（畿内・東海道・東山道・北陸道・山陰道・山陽道・南海道・西海道）をプルダウンリストとして設定し、Ｂ列には旧国名（山城・尾張・武藏・・・など）をプルダウンリストとして設定するものとします。普通にそれぞれの列にプルダウンリストを設定するとなると、Ｂ列のリストは六十余州すべての中から選択しなければならず、プルダウンリストにするメリットが減ってしまいます。これを、Ａ列の入力内容に連動してＢ列のリスト内容が限定される、つまり、Ａ列のセルに「畿内」と入力されたら隣のＢ列のプルダウンリストは「山城」「大和」「河内」「和泉」「摂津」の五ヶ国だけが表示されるようにすれば入力は非常に楽になるわけです。

これはつまり、プルダウンリストの条件分岐（Ａ列の値を条件としてＢ列のリスト内容が変更される）であり、プルダウンリストの階層化（Ａ列が親リストでＢ列が子リスト）だと言ってもいいかもしれません。

まず前準備として、Ｂ列のプルダウンリストのカテゴリ分類表を作成します。今回の例では、五畿七道それぞれに属する旧国名の一覧表です。
メインのシートとは別に「参照」というシートを作成し、そこに下の図のように一覧表を作成します。


次にメインのシートのＡ列（五畿七道を入力する列）にプルダウンリストを設定します。セルA2を選択し、右クリックのメニューから「データの検証」をクリックします。


するとプルダウンリストの内容を設定するダイアログが開きます。ここで「条件：」の項目で「リストを範囲で指定」を選択し、その右隣りのボックスを選択した状態で、「参照」シートのA2からA9まで（「畿内」から「西海道」まで）を選択します。「無効なデータの場合」の項目は「入力を拒否」を選択しておくと、リストに含まれない値は入力できなくなり誤入力を防げます。設定ができたら、「保存」ボタンをクリックします。


これで「メイン」シートのA2セルにプルダウンリストが設定され、親リスト（カテゴリリスト）である五畿七道のいずれかを選択して入力できるようになりました。


あとはオートフィル機能を使って、A2セルの設定をＡ列全体にコピーすれば、Ａ列の設定は完了です。

次に、Ｂ列にプルダウンリストを設定するわけですが、Ｂ列はＡ列の入力内容に応じてプルダウンリストの内容を変更しなければなりません。そこで、Ａ列に入力されたカテゴリに属するアイテムだけをリスト表示し、その表示されたリストをＢ列のプルダウンリストとして範囲指定するという方法をとります。

まず、Ｆ列から右の列を作業用セルとして使用することとし、F2セルに以下の数式を設定します。絶対参照と相対参照を間違えないように注意が必要です。
=OFFSET(‘参照’!$A$1,MATCH($A2,’参照’!$A$2:$A$9,0),COLUMN(F2)-5)
そして、オートフィル機能を使って、F2セルの数式をG2～T2までのセルにコピーします。T列まで作業用セルが必要なのは、最も国数の多い東海道に15ヶ国が属しているためです。

これで作業用セルが（1行分だけですが）完成しました。ためしに、A2に「東海道」と入力すれば、F2に「伊賀」、G2に「伊勢」、H2に「志摩」、・・・T2に「常陸」、という具合に表示されます。A2を「畿内」に変更すれば、作業用セルのほうもF2には「山城」、G2に「大和」、・・・J2に「摂津」という風に自動的に変更されるはずです。問題なく設定できているのが確認できたら、オートフィル機能を使ってG2～T2の内容をG列～T列全体にコピーします。


あとは、Ｂ列（国を入力する列）にプルダウンリストを設定するだけです。A2にプルダウンリストを設定したときと同じように、B2を選択して右クリックメニューで「データの検証」を選択し、開いたダイアログの「条件：」の項目で、リストの範囲として「’メイン’!$F2:$T2」を指定します（絶対参照の$が必須です）。


これでB2にプルダウンリストが設定されました。A2が東海道であれば、B2のプルダウンリストは「伊賀、伊勢、志摩、尾張・・・」となりますし、A2を畿内に変更すれば、B2のプルダウンリストは「山城、大和、河内、和泉、摂津」となります。


B2にプルダウンが設定できたので、またオートフィル機能を使ってB2セルの設定をB列全体にコピーします。これでA列の入力内容に連動してB列のプルダウンリストが自動的に変更される設定が完了しました。