【Googleスプレッドシート】動的なプルダウンリストの作成方法【Query関数版】

悩んでいる人

Googleスプレッドシートで動的なプルダウンリストを作りたいが、やり方が分からない。

例とともに作り方を教えて欲しい。

こんなお悩みを解決します。

Googleスプレッドシートでプルダウンリストを使う際、リストを動的に作成したくなる時があります。

実は、動的なリストは「Query関数」と「データの入力規則」を組み合わせることで、簡単に実現できます。

この記事では、天気予報APIの都道府県・市町村とCityIDのテーブルを題材に、Query関数を利用した動的なプルダウンリストを作成する方法について解説したいと思います。

この記事を読み終えると、Vlookup関数、Index関数、Match関数の代替手段となるQuery関数について理解が深まるとともに、動的なプルダウンリストの作成方法を学ぶことができますよ。

事前準備

今回は、livedoor天気で利用されていた「全国の地点定義表」を題材に、動的なプルダウンリストの作成方法について解説します。

https://weather.tsukumijima.net/primary_area.xml

上記のサイトのデータはそのままでは使いづらいため、CSV形式に変換したものを以下に記載しておきます。

都道府県,市町村,CityID
道北,稚内,011000
道北,旭川,012010
道北,留萌,012020
道東,網走,013010
道東,北見,013020
道東,紋別,013030
道東,根室,014010
道東,釧路,014020
道東,帯広,014030
道南,室蘭,015010
道南,浦河,015020
道央,札幌,016010
道央,岩見沢,016020
道央,倶知安,016030
道南,函館,017010
道南,江差,017020
青森県,青森,020010
青森県,むつ,020020
青森県,八戸,020030
岩手県,盛岡,030010
岩手県,宮古,030020
岩手県,大船渡,030030
宮城県,仙台,040010
宮城県,白石,040020
秋田県,秋田,050010
秋田県,横手,050020
山形県,山形,060010
山形県,米沢,060020
山形県,酒田,060030
山形県,新庄,060040
福島県,福島,070010
福島県,小名浜,070020
福島県,若松,070030
茨城県,水戸,080010
茨城県,土浦,080020
栃木県,宇都宮,090010
栃木県,大田原,090020
群馬県,前橋,100010
群馬県,みなかみ,100020
埼玉県,さいたま,110010
埼玉県,熊谷,110020
埼玉県,秩父,110030
千葉県,千葉,120010
千葉県,銚子,120020
千葉県,館山,120030
東京都,東京,130010
東京都,大島,130020
東京都,八丈島,130030
東京都,父島,130040
神奈川県,横浜,140010
神奈川県,小田原,140020
新潟県,新潟,150010
新潟県,長岡,150020
新潟県,高田,150030
新潟県,相川,150040
富山県,富山,160010
富山県,伏木,160020
石川県,金沢,170010
石川県,輪島,170020
福井県,福井,180010
福井県,敦賀,180020
山梨県,甲府,190010
山梨県,河口湖,190020
長野県,長野,200010
長野県,松本,200020
長野県,飯田,200030
岐阜県,岐阜,210010
岐阜県,高山,210020
静岡県,静岡,220010
静岡県,網代,220020
静岡県,三島,220030
静岡県,浜松,220040
愛知県,名古屋,230010
愛知県,豊橋,230020
三重県,津,240010
三重県,尾鷲,240020
滋賀県,大津,250010
滋賀県,彦根,250020
京都府,京都,260010
京都府,舞鶴,260020
大阪府,大阪,270000
兵庫県,神戸,280010
兵庫県,豊岡,280020
奈良県,奈良,290010
奈良県,風屋,290020
和歌山県,和歌山,300010
和歌山県,潮岬,300020
鳥取県,鳥取,310010
鳥取県,米子,310020
島根県,松江,320010
島根県,浜田,320020
島根県,西郷,320030
岡山県,岡山,330010
岡山県,津山,330020
広島県,広島,340010
広島県,庄原,340020
山口県,下関,350010
山口県,山口,350020
山口県,柳井,350030
山口県,萩,350040
徳島県,徳島,360010
徳島県,日和佐,360020
香川県,高松,370000
愛媛県,松山,380010
愛媛県,新居浜,380020
愛媛県,宇和島,380030
高知県,高知,390010
高知県,室戸岬,390020
高知県,清水,390030
福岡県,福岡,400010
福岡県,八幡,400020
福岡県,飯塚,400030
福岡県,久留米,400040
佐賀県,佐賀,410010
佐賀県,伊万里,410020
長崎県,長崎,420010
長崎県,佐世保,420020
長崎県,厳原,420030
長崎県,福江,420040
熊本県,熊本,430010
熊本県,阿蘇乙姫,430020
熊本県,牛深,430030
熊本県,人吉,430040
大分県,大分,440010
大分県,中津,440020
大分県,日田,440030
大分県,佐伯,440040
宮崎県,宮崎,450010
宮崎県,延岡,450020
宮崎県,都城,450030
宮崎県,高千穂,450040
鹿児島県,鹿児島,460010
鹿児島県,鹿屋,460020
鹿児島県,種子島,460030
鹿児島県,名瀬,460040
沖縄県,那覇,471010
沖縄県,名護,471020
沖縄県,久米島,471030
沖縄県,南大東,472000
沖縄県,宮古島,473000
沖縄県,石垣島,474010
沖縄県,与那国島,474020

データ定義

Googleスプレッドシートを起動し、「データ定義」というシートを作成します。

上記のCSV形式のデータをコピーした後、先ほど作成した「データ定義」シートのA1に貼り付けてください。

貼り付けた後、右下に貼り付け方法に関するボタンが表示されるため、「テキストを列に分割」を押下します。これにより、貼り付けたデータはカンマ区切りで列ごとに分割されます。

都道府県の重複削除

貼り付けたデータは、都道府県部分に重複が含まれるため、重複を削除した列を新たに定義します。

ここでは、E列に重複を削除したリストを定義したいと思います。E1とE2のセルに以下を入力してください。

以下のように重複が削除された都道府県のデータが定義できていれば成功です。

名前付き範囲の定義

Query関数から範囲を呼び出しやすいようにするため、以下の2つを名前付き範囲として定義します。

Googleスプレッドシートのメニューから「データ」→「名前付き範囲」の順に押下してください。

右側に表示されるメニューのうち、「範囲を追加」を押下します。

その後、上の枠に「名称」、下の枠に「範囲」を指定し、「完了」を押下してください。「天気予報テーブル」の場合の例は、以下のようになります。

「都道府県」も同様に実行すると最終的に以下のようになると思います。

また、「出力結果」シートも作成しておきます。

以上で、事前準備は完了です。

プルダウンリストの作成

今回は、以下のように都道府県の値に応じて市町村のプルダウンの内容が変化するものを作成します。

ヘッダ定義

「出力結果」シートのヘッダとして、以下を定義しておきます。

都道府県のプルダウンリストの作成

今回は2行目から4行目までの3行分のセルに対して、プルダウンリストを定義します。

まず、A2セルに=row(A1)と入力後、オートフィルを用いてA4まで数式をコピーします。これで、No.を定義できました。

次に、プルダウンリストを作成します。「データ」→「データの入力規則」の順に押下してください。

右側のメニューに表示される「ルールを追加」を押下します。

「範囲に適用」と「条件」をそれぞれ以下のように定義し、右下の「完了」を押下してください。

データの入力規則を定義後、B2セルを押下して以下のようなメニューが表示されればOKです。

動的なプルダウンリストの作成

動的なプルダウンリストを作成するための準備として、F2のセルに以下の数式を入力し、F4までオートフィルを適用してください。

=IFERROR(transpose(query(天気予報テーブル,"select B where A ='"&$B2&"'")),"")

そして、B列のプルダウンリストの内容を変更すると、F列からL列の内容が変わることを確認してください。

後は、C2からC4セルに対して、個別にデータの入力規則を適用することで、動的なプルダウンリストを作成できます。例えば、C2にデータの入力規則を適用する場合、以下のようになります。

C2からC4セルのすべてに適用した結果、以下のようになります。

以上で、動的なプルダウンリストの作成は完了です。以降では、F2セルに設定した数式について詳しく解説します。

【解説】Query関数の内容

今回登場した関数は、以下の3つです。それぞれ順に解説します。

• IFERROR
• TRANSPOSE
• QUERY

IFERROR関数

この関数はエラー発生時に決まった値を返却する関数であり、以下のような引数を指定します。

例えば、以下のような数式をセルに入力すると「Error」と表示されます。（0で割った結果は不定値となるため）

=IFERROR(1/0,"Error")

TRANSPOSE関数

この関数は行と列を入れ替える関数であり、以下のような引数を指定します。

例えば、A1～A3に以下のような値が入力されていたとします。

このとき、B1に以下の数式を入力すると、B1からD1にA1からA3の内容が反映されます。

=TRANSPOSE(A1:A3)

QUERY関数

QUERY関数は指定された範囲に対して、Google Visualization APIのクエリ言語を実行します。

雰囲気としては、Googleスプレッドシートをテーブルと見立ててSQL文が実行できる、というイメージになります。

今回やりたかったことを整理すると、動的なプルダウンリストを作成する上で、以下が得られれば良いことになりますね。

該当する都道府県の市町村の情報

例えば、「道東」を選択した場合は、「網走、北見、紋別、根室、釧路、帯広」の情報が得られれば良いことになります。

上記を得たい場合、「A列が'道東'である行のB列を選択する」というクエリを生成すればよいことになりますね。

これは、Google Visualization APIのクエリ言語で以下のように書けます。

select B where A = '道東'

これをQUERY関数で実現すると以下のようになります。

=QUERY(天気予報テーブル,"select B where A = '道東'")

後は、条件部分（上記の場合は「道東」の部分）を動的に指定できればよいため、以下のようにカスタマイズします。

=QUERY(天気予報テーブル,"select B where A = '"&[条件として指定したいセル番地]&"'")

今回はB2からB4に条件にあたる情報を指定したため、$B2を指定した以下の数式を採用しました。

QUERY(天気予報テーブル,"select B where A ='"&$B2&"'")

CityIDの取得

CityIDもQuery関数を用いて出力することができます。以下の数式をD2セルに入力し、D4までオートフィルしてください。

=IFERROR(query(天気予報テーブル,"select C where A = '"&$B2&"' and B = '"&$C2&"'"),"-")

今回の場合、都道府県（A列）と市町村（B列）が一致するC列の該当セル（今回の場合、一意に定まる）を出力すればよいため、上記のようにA列とB列に対して条件（where）を指定します。

まとめ

今回はGoogleスプレッドシートにおいて、Query関数を用いた動的なプルダウンリストの作成方法について解説しました。

Google Apps Script（GAS）でプログラミングを行わなくとも、既存の関数を用いることで、手の込んだ処理が実現できるのは非常にありがたいですね。

以下の2点が欠点となりますが、「プログラミングが不要」という点を考慮すると万人受けするやり方になっているかなぁと思います。

• 計算用のスペースが必要になる。
• 条件付き書式の設定が煩雑になる。

今回の例を参考に、他の事例に対しても適用してみてください。