(Gスプレッドシート)プルダウンを連動させる

 Googleスプレッドシートで、値を段階的に絞り込んで入力する際に便利なプルダウンの連動の設定についてです。

  • 元データに加え、プルダウンから参照するためのリストを生成することでプルダウンの連動を実現できます。

手順

機能の確認

f:id:accs2014:20191117161701p:plain:right:w550

 まず設定の大枠と、プルダウンの連動の実例について確認します。
 画像の左上の部分が元になるデータです。「分類」という列と「商品」という列が記録されています。
 一方、右下の部分がプルダウンの連動により入力を行っている部分です。
 画像はセルE15のプルダウンで「肉」という値を選択したのちにF15セルをクリックしたところですが、左上のデータにより「肉」に分類されている「豚バラ」「鳥モモ」「牛スネ」の3つの値だけが表示されています。これがプルダウン連動の効果で、選択肢が多数ある場合に段階的に絞り込んで入力を進めることができます。

f:id:accs2014:20191117161706p:plain:right:w550

 ここで、データがどういう流れになっているのかを確認します。
 ①右下にあるE15セル(1つ目のプルダウン)は、「分類」の値(B3:B9)を参照しています。
 ②右上にあるリストは(F3:F9)、元データ全体(B3:C9)と1つ目のプルダウンの値(E15)を参照することで生成されます。
 ③F15セルは、リスト(F3:F9)を参照しています。

元データの入力

f:id:accs2014:20191117161614p:plain:right:w550

 では設定に移ります。まずは元データの入力です。
 B3:C9に「分類」と「商品」の組み合わせを入力していきます。
 このデータは、必ずしも「分類」に基づきソートされている必要はありません。プルダウンに表示される際には重複が除かれるため、同じ値が2回以上表示されることがないからです。

1つ目のプルダウンの設定

f:id:accs2014:20191117161618p:plain:right:w550

 次にE15セルを選択した状態にします。

f:id:accs2014:20191117161621p:plain:right:w550

 そして上部のメニューから「データ」→「データの入力規則」と進みます。

f:id:accs2014:20191117161626p:plain:right:w550

 ダイアログが現れます。
 まず「セルにプルダウンリストを表示」にチェックが入っていること、「条件」のところに2つある欄のうち左側が「リストを範囲で指定」になっていることを確認してください(デフォルト)。
 確認したら「条件」の右側の欄を選択します。

f:id:accs2014:20191117161629p:plain:right:w550

 そしてB3:B9の範囲をドラッグし、ダイアログの「OK」をクリックします。

f:id:accs2014:20191117161633p:plain:right:w550

 最初のダイアログに戻りますので、そのまま「保存」をクリックします。

f:id:accs2014:20191117161637p:plain:right:w550

 これでE15セルがプルダウンになりました。
 クリックして2つの値から選択できる状態になっています(上記のように値の重複は除かれています)。

2つ目のプルダウンが参照するリストの設定

f:id:accs2014:20191117161641p:plain:right:w550

 F3セルに次のように入力します。

=IFERROR(FILTER(C3:C9,B3:B9=E15),"")

 これにより「商品」(C3:C9)に記録されている値のうち、E15セルで選択された「分類」に属するものだけが抽出されます。
 なお、E15セルが空白であるときなどはこのリストがエラーになるためIFERROR関数で空白に変換しています。

f:id:accs2014:20191117161645p:plain:right:w550

 設定後、試しにE15セルで「野菜」を選択したところです。「野菜」に分類されている4つの「商品」の値が表示されました。

2つ目のプルダウンの設定

f:id:accs2014:20191117161650p:plain:right:w550

 F15セルを選択します。

f:id:accs2014:20191117161654p:plain:right:w550

 あとは1つ目のプルダウンと同じ要領で参照用のリスト(F3:F9)を参照します。

f:id:accs2014:20191117161657p:plain:right:w550

 最後にここで「保存」をクリックすれば完成です。

f:id:accs2014:20191117161701p:plain:right:w550

 改めて操作の様子です(最初の画面の再掲)。
 1つ目のプルダウン(F15)で選択した「分類」に対応する「商品」だけが2つ目のプルダウンに表示されます。

元データを横持ちにする場合

 元データを横持ちにする場合もほとんど同じように設定できます(プルダウンの参照先として「H3:K3」のように複数列を指定しても問題ありません)。
 右上の参照用リストについても次の画像のようにすれば大丈夫です。

f:id:accs2014:20191117173643p:plain:w750

備考

f:id:accs2014:20191117161711p:plain:right:w550

 注意点ですが「商品」を選択してから「分類」の値を変えたときに、「商品」の値がクリアされませんのでそのままでは矛盾を生じます(画像参照)。これはプルダウン作成時に「無効なデータの場合」の「入力を拒否」を選択しても阻止できません。
 画像のように警告マークは出ますが、気になる場合はスクリプトで値のクリアを行うか、条件付き書式でさらに目立たせるなどの対策が必要です。

 その他について参考まで列記します。

  • 同じシートの狭い範囲ですべての設定を行っていますが、データを増やすときや設定変更時に問題を生じやすいので、実際はそれぞれの部分を別シートに置くなど配置を工夫したほうがよいでしょう。
  • 連動するプルダウンを複数組作成する場合、元データは1つでいいですが参照用リストは複数設ける必要があります。このときFILTER関数で絶対参照を活用し参照ズレを生じないよう注意してください。
  • プルダウンが参照する範囲はセルをコピーペーストしても変わりません。よって連動するプルダウンを複数組作成する場合、2つ目のプルダウンについては参照範囲を1つ1つ手動で設定する必要があります。