スプレッドシートで3段階連動のプルダウンリストを複数行つくる方法

今回はGoogleのスプレッドシートで、3段階（以上に）連動するプルダウンリストを、複数行作成する方法を紹介します。

ExcelではINDIRECT関数を用いて連動するプルダウンリストを複数行つくることができるのですが、スプレッドシートだと対応していません。

自分の場合は家計簿などに使うのですが、同じような項目を何度も入力したり、それによってデータ集計するためにプルダウンリストは便利です。

そこでスプレッドシートでもINDIRECT関数を使わずに3段階に連動するプルダウンリストを、複数行作成する方法を考案したので紹介します。

連動する3段階のプルダウンリストを作る方法

スプレッドシートの全体像

今回作成するスプレッドシートの完成形がこちらになります。

このシートはある日の料理につかった食材をリストの中から選ぶという架空の設定のシートです。

しっかりと3段階目までリストが表示できているのが分かります。

このブックでは、5枚のシートを使って3段階のプルダウンリストを作っています。

シートの名前は適当にしていますので変えても大丈夫ですが、今回はこのシート名に沿って説明していきます。

連動するプルダウンリストの仕組み

まずはブック全体の構造を説明しておきます。

「入力」と名付けたシートはプルダウンリストを使ってデータを入力していくシートです。

今回は日付、料理名とそれに対応する3段階の項目のプルダウンリストをつくっています。

このプルダウンリストはそれぞれ上位のプルダウンリストで選んだ項目に基づいてリストで表示される内容が変わります。

例えば大項目で「肉」を選択したら中項目では肉の種類がリストに出ますし、

同じく大項目で「魚」を選択したら中項目は魚の種類を表示します。

小項目のリストも同様で同じ「肉」という大項目を選んでも、中項目で選んだものによって小項目のリストの表示内容は変化します。

「設定リスト」と名付けたシートは、プルダウンリストに表示したい項目をあらかじめ整理したものです。

「入力」シートと一緒のシートに作っても構いませんが、後々項目を追加したり整理したりすることを考えて別のシートに作成しています。

そして今回の肝となるのがこの「中項目」「小項目」シートです。

形としては「入力」シートと同じようなものですが、このシートを使うことによってスプレッドシートでも3段階で連動するプルダウンリストを作ることができます。

仕組みとしては、

1. 「入力」シートで大項目を選択
2. 選ばれた大項目に連動する中項目一覧を、「中項目」シートで表示
3. 「入力」シートで中項目を選ぶ際のプルダウンリストに連動する中項目一覧を反映させる
4. さらに選ばれた中項目に連動する小項目一覧を、「小項目」シートで表示
5. 「入力」シートで小項目を選ぶ際のプルダウンリストに連動する小項目一覧を反映させる

というものです。

各シートの作成方法

では各シートの作り方を説明していきます。

「設定リスト」シートのつくり方

最初に各プルダウンリストに設定したい項目のリストを「設定リスト」シートに作っていきます。

今回はそれぞれ「大項目」「中項目」「小項目」と段階ごとに割り振って表を作成しています。

ここでのポイントとして2段階目以降のリストは、上位のリストと連動するように作成することです。

具体的にいうと中項目や小項目のリストは、それぞれ対応する上位項目を縦方向に入力します。

その右側のセルには下位の項目を連続して入力していきます。

後ほど触れますが今回のリストではVlookup関数を使うため、各項目に応じた選択肢をリストに表示できるようにこのような並びで入力します。

「入力」シートのつくり方

次にプルダウンリストを実際に使うシートを、「入力」シートに作成します。

今回は日付と自由入力できる料理名を加えて、その右に3段階の項目を並べられるように表を作成します。

また、このシートでは「通し番号」を各行にふっておきます。

これがあることによって今後このシートに入力した項目を整理するときに役立ちます。

それ以外に特に縛りはないので、備考などを適宜加えてもいいでしょう。

「中項目」「小項目」シートの役割

今回の根幹であるプルダウンリストのリスト項目を、「中項目」「小項目」シートに作成していきます。

その前になぜこのシートを作成するのかについて説明しておきます。

プルダウンリストを作成するには、スプレッドシート上の「データ」→「データの入力規則」からリスト項目を選択してリストを選択することができます。

今回のシートでは選んだ大項目と中項目・小項目させたいですが、スプレッドシートだと一筋縄では行きません。

エクセルの場合は連動するプルダウンリストはINDIRECT関数を使うと簡単にできるのですが、スプレッドシートはこれに対応していません。

そこで別のシートに連動する項目のリストを表示させて、それをリストとして無理やり設定するというのが今回のやり方です。

では具体的なシートの作成方法を説明します。

「中項目」「小項目」シートのつくり方

まず「入力」シートで作成した通し番号と同じ行に、「中項目」シートで通し番号・大項目・中項目という列をそれぞれ作成します。

3行目に通し番号1となる表を作ったので、同じように3行めに通し番号1になるように通し番号・大項目・中項目の列を割り振ります。

必ずしも同じ行・通し番号に作る必要性は無いのですが、後々わかりやすくするためにこうしています。

通し番号には「入力」シートと同じように番号を振ってください。

大項目の行には「入力」シート上で選択した大項目が反映されるように関数を入力します。

今回の場合「入力」シートのD列が大項目に対応していますので、下記のような関数を入力。

これで同じ「入力」シートで同じ通し番号の行で大項目を入力すると、このシートでも同じモノが表示されるようになります。

次に中項目は、関数「iferror(vlookup($B3,’設定リスト’!$B$8:$E$9,2,false))」

を入力します。

さらにその右のセルにはVlookup関数の指数部分（上の画像であれば2）に1ずつ加えながら、同じ関数をコピーします。

中項目リストで表示させたい項目だけ、この関数を入力したセルを指数を加えながら右に作成していってください。
今回は余裕をもってC列からJ列まで作っています。

小項目も同じ構造のシートをつくります。

大項目だったところが中項目に、中項目だったところが小項目になっています。

また小項目の列に入力するVlookup関数では、小項目のリスト部分を選択しています
（上の画像ですと「$B$12:$F$17」の部分です）。

1行分これらがつくれたら、あとは行ごと下にコピーしていけばこの2つのシートは完成です。

「入力」シートにプルダウンリストを設定する

最後に実際に入力する際にプルダウンリストとして使えるように、「入力」シートで設定します。

大項目の行は「データの入力規則」から大項目にあたる項目を「設定リスト」シートからセル範囲で指定して、大項目の列にプルダウンリストとして設定します。

中項目・小項目については設定の仕方が少し異なります。

プルダウンリストの項目として設定したいのは設定リストにある項目全てではなく、各行で選択した大項目に連動した項目です。

したがってそれら項目が表示されている「中項目」「小項目」シートから項目リストを参照します。

セル参照をする際は、相対参照で設定してください。

相対参照にすることによってそれぞれの行ごとのリストが反映されるようになります。

小項目も同様です。

連動したプルダウンリストを動かしてみる

これで各項目に連動してリストの表示項目が変わる、3段階のプルダウンリストを複数行作成することができました。

実際に動かしてみます。

まず、「入力」シートで大項目をプルダウンリストから選択します。

すると「中項目」シートの同じ行・通し番号のところには選択した大項目と、それに連動した中項目のリストが並びます。

なので続けて「入力」シートで中項目を選ぶと、「中項目」シートで出た項目がプルダウンリストとして表示されます。

さらに続けて「小項目」シートを見ると、選択した中項目に連動した項目が表示されているので「入力」シートでも選ぶことができます。

これらを複数行分作ることで、連続した入力も可能となります。

デメリットと対策

エクセルと同様に3段階に連動するプルダウンリストを複数行作ろうとして考えたこのやり方ですが、自分で使用するうちに欠点を2つ発見しています。

並び替えについては別シートで対応

まず「入力」シートで並び替えを行おうとすると、エラーが発生する点です。

例えば入力する日付が前後したために日付順に並び替えたいと思った場合、並び替えると次のようにエラーが出ます。

この原因は並び替えたことでデータの入力規則で指定しているセルの参照範囲が変わってしまうためと思われます。

これが発生すると、それぞれの項目にプルダウンリストが連動しなくなってしまいます。

そこで別のシートに内容を転記して、そのシートで並び替えることでこれを解決しています。

この「整列」シートでは「入力」シートの内容をセル参照してコピーしているだけですが、日付でソートしても元データをいじっていないためエラーが出ません。

ブック内のデータが大きいと、リストのレスポンスに時間がかかる

しかし最大の問題はこれらの方法を使うと大量の計算式がシートに表示されることでスプレッドシートの容量が大きくなり、リストのレスポンスに時間がかかるということです。

具体的には連続で項目を選ぼうとしても1回ごとに計算が入るため、待ち時間が発生します。

上の動画は実際に今回紹介した方法を使って作成している自分の家計簿です。

大項目である支出を選択するとどんな内容の支出なのかを選択することができるのですが、1回1回読み込みが入って連続で選択することができません。

もっとも自分の家計簿はシートだけで約30シートあって、このプルダウンリストも1,000行くらいつくっているので重いのは当然です。

これを解決するにはスプレッドシートそれ自体の容量を抑えるか、慣れるしかありません。

自分はもう何回も使っているので慣れてしまいました。

スプレッドシートでも連動プルダウンリストはつくれる

ということで3段階に連動するプルダウンリストを複数行つくる方法を解説してきました。

リストに表示する項目を別のシートに入力しておくことで、実質的にExcelにおけるINDIRECT関数のような役割を果たしてくれます。

極端なことをいえばシートの枚数を増やしていけば更に何段階にも連動するプルダウンリスを作ることも可能です。

他のサイトでも連動するプルダウンリストをつくる方法は見つかったのですが、その方法では1行しか連動するプルダウンリストを作れなかったので、今回は複数行分つくる方法を考えました。

プルダウンリストを使う目的は、機械的に決められた入力を何度も行うためにあると思っています。

自分がつかっている家計簿のように後で集計してデータとしてまとめたいとか、見返すときに同じことが入力されていたほうが検索しやすいとかです。

にも関わらず1行だけプルダウンリストをつくってもなんの役にも立たないので、この方法で対処することにしました。

家計簿を始めとして連動するプルダウンリストを活用する機会はたくさんあると思うので、ぜひ使ってみてください。