Googleスプレッドシートで他のセルの内容に応じてプルダウンリストの内容を変更する - プルダウンリストの条件分岐あるいは階層化

2016-07-03

Googleスプレッドシート 備忘録

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

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

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

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

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


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


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


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


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

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

まず、F列から右の列を作業用セルとして使用することとし、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列全体にコピーします。


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


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


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