Googleスプレッドシートではデータの入力規則でプルダウンリストを使うことができます。
そして、2段階のプルダウンリストとして、例えば「銀行名を指定したら連動して支店名が可変で出るようにする」といったことも可能です。
下図のY列が1つ目のプルダウン(銀行名)、Z列が2つ目のプルダウン(支店名)となっており、銀行名を指定すると、その銀行名に紐づく支店名の候補が2つ目のプルダウンで選択できるようになります。
本記事の執筆時点で既にこれを実現するための手法はいくつかあるようで、プルダウンの元となるマスタデータを以下のように横方向に並べて作ることで実現ができるとのことでした。
しかしこれ、よくよく考えてみるとですね、横方向のデータ用意するのがしんどいという問題点がありまして。どの記事もこの手法を紹介されているのですが、マスタデータの作り方には言及されているものが見当たらず...で、その横方向のマスタデータはどうやって用意するのという疑問が湧き上がりまして。
通常、マスタデータって縦方向に作られてることの方が多いと思うんですよね。RDBなどのデータベース設計してるとわかるかと思うのですがデータベースは縦方向が基本です。なので、これを何とか縦方向のマスタから作れないものかと考え、その手法を編み出しました、というのが本記事の内容です。
縦方向のマスタデータとは以下のようなイメージです。A列に1つめのプルダウンの選択肢が並んでいて、D列に2つめのプルダウンの選択肢が並んでいるといった感じです。
この方がデータベースとしては自然ですよね。CSVでも作りやすいし。
手法
では具体的にどうやるかという話なのですが、縦方向のマスタを横方向に並べ替えることで実現します。
縦方向のシートそのまま使えないかと思って調べたのですが、プルダウンには関数を入れ込むことができず、実現できそうにありませんでした。しかし、前段でお話した通り、横方向のマスタを利用するなら実現が可能なわけです。そこで、縦方向の元データを横方向に展開してプルダウンリストのデータを作ろうというのが今回のアイデアです。
今回は例として銀行名と支店名の一覧を元データとします。
記入シート
銀行名と支店名を記入するシートです。シート名は「記入シート」としましょう。
プルダウンの作り方は後ほど説明します。
後述の「銀行名」シートからY列を参照するため先に提示しています。
元データのシート
元データのシート「銀行マスタ」を用意します。
銀行マスタシートにはA列に銀行名、D列に支店名が入っています。
銀行名のシート
1つ目のプルダウンのリストは銀行名のリストです。その元となるデータを作ります。
「銀行名」というシートを作り、以下の数式をA1セルに入力します。
=UNIQUE('銀行マスタ'!A2:A)
UNIQUE関数は対象の範囲の重複する値を排除した一覧を返してくれる関数です。
銀行マスタシートのA列は値が重複していますので、それを取り除いたものが1つ目のプルダウンリスト(銀行名)の選択候補リストとなります。
支店名のシート
2つ目のプルダウンリストは支店名のリストです。
1つ目のプルダウンで選択した銀行名に紐づく支店名の一覧を作る必要があります。
作成する「支店名」シートはこんな感じです。
A列には記入シートのY列の値を引っ張ってきています。
B列から右の列が横方向に展開された支店名のデータです。
（余裕を持たせてAZZ列まで拡張しました）
A1セルの数式です。記入シートのY列のセルの値を参照しています。
='記入シート'!Y2
B2セルの数式です。TRANSPOSE関数とQUERY関数を組み合わせています。
=TRANSPOSE(QUERY('銀行マスタ'!$A2:$H,"SELECT D WHERE A = '"&A2&"'"))
まず、QUERY関数で銀行マスタの一覧を抽出します。ここでは支店名が欲しいのでSELECT句にD列を指定しています。さらに銀行名で絞り込みたいので銀行マスタのA列に対して支店名シートのA2セル(銀行名)を指定しています。こうすることで縦方向に並んだ銀行名に紐づく支店名の一覧が抽出できます。
さらに、この一覧を横方向に転換したいのでTRANSPOSE関数を使っています。
これによって、記入シートの銀行名が変わる度に、支店名シートのA列の値が変わり、A列の値に紐づく支店名が動的に変わり、横方向に展開されます。
A2とB2の数式を以降の行に貼り付けます。記入シートの行の数と同じ分だけ貼り付ける必要があります。
ここでひとつ制約がありまして、数式のあるセルが増えるほど計算に時間がかかってしまうことです。記入シートのY列のセルに対応する数だけ支店名シートのA列とB列の数式を埋め込む必要があります。数式自体はオートフィルなどで簡単に増やせますが、場合によっては1000行もの一覧になるかもしれません。1000行で試した感じでは計算に数分ほど時間がかかるようでした。
記入シートへのプルダウン作成
ここからは他の記事で紹介されている手法と同様です。
データの入力規則でY列とZ列にそれぞれ入力規則を作ります。
Y列
範囲に適用にはY列の入力範囲を指定します。
条件には「プルダウン（範囲内）」を指定します。
条件の範囲には銀行名シートのA列を指定します。（絶対指定の$を付けます）
Z列
範囲に適用にはZ列の入力範囲を指定します。
条件には「プルダウン（範囲内）」を指定します。
条件の範囲には支店名シートのB列〜AZZ列を指定します。（相対指定にしたいので$はつけません）
おわりに
今回は既存の手法に加え、元データの形式を縦方向で用意する方法についてご紹介しました。
連動するプルダウンで入力させたい場合も多くあると思います。
本記事が皆様の一助になれば幸いです。