Googleスプレッドシート プルダウンリスト活用術 2（連動プルダウン 基本）
前回の続きで Googleスプレッドシートのプルダウンを掘り下げていきます。前回はプルダウンの基本やExcelとの違い、ちょっとした小ネタを書きましたが、今回はみんなが知りたい 連動プルダウンについてです。
以降は プルダウンの表示は 全て旧表示（矢印）を使います。
チップ表示でやりたい、もしくは チップ表示を 旧表示に切り替える方法がわからないという方は、前回の noteを参照ください。
シリーズ前回の記事
プルダウンはネタが多いのでマガジンにシリーズをまとめています。
チップ型 プルダウンは GASでコントロールできるのか？
本題の連動プルダウンの前に、今のプルダウンの標準仕様である チップ表示と GASについて 少し書いておきます。
たまーに
チップ表示がうざいので GASでプルダウンの初期表示設定を 矢印にできないか？
チップの色設定を1個ずつやるのが面倒なので、GASで一気にできないか？
こんなことを聞かれますが、どっちも出来ません！（2023年7月現在）
残念ながら GASは万能ではありません。
新しく実装された機能や 仕様変更があっても、GASからは1年以上触れないってケースも多いのです。※セル内画像もかなりGAS側は放置されてた
GASは プルダウンのチップ表示には対応していない
プルダウンリスト自体は GASの DataValidationBuilder クラスから作成できます。
データの入力規制 は全て DataValidationBuilder で作成します。
その中の requireValueInList( ), requireValueInRange( ) がプルダウンなんですが、残念ながら プルダウンメニューを非表示にする オプションはあるものの、チップと矢印の表示切替をするようなオプションは見当たりません。
マクロの記録で 手動でチップ表示の プルダウンを作成するという操作をマクロ化しても、
マクロを実行すると チップ表示部分は反映されず、旧式（矢印表示）のプルダウンが生成されます。
GASでは プルダウンのチップ表示は 操作できない（生成できない）ってことです。
これは現状では仕方ないので、諦めて対応されるのを待ちましょう。
Googleスプレッドシート 連動プルダウンのゴール
で、本題の 連動プルダウンです。
「連動プルダウンってなに？」って人でも、実物を見れば「あーこれね！」ってなると思います。
上のgif動画のように 1つ目プルダウンで選択した項目によって、次の2つ目のプルダウンの選択肢が 動的に変化するプルダウンを連動プルダウンと呼びます。
2つだったら2段階、3つだったら3段階 の連動プルダウンといった言い方をすることが多いです。gif動画は3段階の連動プルダウンですね。
1段階目のプルダウンで「野菜」を選ぶと2段階目は、複数の野菜の選択肢になります。さらに野菜の中から「ニンジン」を選ぶと、3段階目では ニンジンを使った料理が選択肢として表示されます。
もし、1段階目で「フルーツ」を選択した時は、2段階目は 複数の各種フルーツが選択肢となり、さらに「リンゴ」を選ぶと 3段階目のプルダウンは リンゴ料理が選択肢になります。
連動プルダウンがよく使われる例としては 地域 ＞県 ＞ 市（区）といった絞り込みがあります。
【1段階目 地域】
北海道、東北、関東、中部、近畿、中国、四国、九州沖縄
↓
関東を選択
↓
【2段階目 都道府県】
東京、神奈川、埼玉、千葉、茨城 ……
↓
東京を選択
↓
【3段階目 市・区】
千代田区、中央区、港区、新宿区、文京区 .…
他には 顧客マスタがあって 選択肢が多い時に 先頭行の読みから絞り込むといったケース。
例えば 1段階目に 「あ、か、さ、た・・」と用意し 「あ」を選択したら、2段階目のリストが 「赤城、麻生、安西」に絞り込まれる、といった使い方もよく見かけます。
Googleスプレッドシートのプルダウンは 直接 選びたい選択肢に含まれるキーワードを入力すれば、 「含む検索」でリストを絞り込む機能があります。
データや実現したいことによっては、連動プルダウンでなくこれで十分ってケースも多いでしょう。
でも、マウス操作ポチポチでユーザーがミス少なく気軽に利用できるのもああって、根強い人気があるのが 今回の連動プルダウンという機能です。
ネットには 古いプルダウン情報が多いので注意
よく使われる 需要が高い機能なんで、Excelでの連動プルダウンはもちろんのこと、Googleスプレッドシートの連動プルうダウンも、解説しているサイトは多数存在しています。
ですが、前回書いた通り Googelスプレッドシートのプルダウンは昨年仕様が変わったばかりで、やはり古い方式の連動プルダウンを紹介しているサイトが多いんです。
こんな感じの ダイアログ上で設定する画像が掲載されているサイトは、旧式の手法の紹介サイトです。
式の組み方や一部の設定はそのまま使えることもありますが、混乱のもとなので サイドバーの設定画面で解説している、新仕様に対応した サイトを参考にしましょう。
さらに言わせていただくと、多くのサイトが紹介している連動プルダウンは、2段階までだったり、連動プルダウン箇所が 1行のみだったり、複数行にわたる連動プルダウン用に、各行ごとに式を入れて、○段階目ごとにシートを用意する面倒な方式だったり、汎用的とは 言えない 連動プルダウンが多い印象。
mirの noteでは、1つのマスタ表、1つの式で 多段階 連動プルダウンに対応できる汎用的な作り方をゴールとしてみたいと思います！！
（残念ながら今回はそこまでたどり着きません！）
Excelは 数式を使って連動プルダウンが作れる
前回のプルダウン機能の Googleスプレッドシートと Excel比較でも触れましたが、Excelは プルダウンの リストに数式を使えるのが強みです。
つまり一つ目のプルダウンで選択した値を参照して、２つ目のプルダウンの選択肢を式で可変にすることが出来るのです。
先に Excel側の 連動プルダウンの基本も理解しておきましょう。
スピル非対応のExcelでも使える 基本の連動プルダウン式としては、
クロス表を用意して INDEX + MATCH で リストを絞り込み
名前の定義で リスト指定する範囲に名前を付けて INDIRECT で呼び出す
この2つがあげられます。
それぞれ簡単に見ていきましょう。
Excelの INDEX + MATCHを使った連動プルダウン
例えば 連動プルダウンを利用するセルを
項目１ A2:A10
項目２ B2:B10 （A列の選択に連動させたい）
マスタデータ（選択肢）を
項目１のマスタ E2:E4 （縦並び）
項目２のマスタ F2:I4 （横並び）
とした 連動プルダウンを作る時、
まずＡ列の項目１のプルダウンリストは
=$E$2:$E$4
このように指定します。自動で $が付きますが、条件付き書式と同じくプルダウンの設定範囲に対して、絶対参照を使うべきか相対参照を使うべきか を意識しながら設定できるとよいです。
そして 項目2のB列の方を
=INDEX($F$2:$I$4,MATCH(A2,$E$2:$E$4,FALSE),)
このように指定することで 連動プルダウンが出来ます。
検索キーとなる A2は 相対参照にしたいので A2だけ $を外しています。
もちろん スピル非対応のExcelで 上の式を 普通に セルに入れた場合はエラーを返します。
しかし セルには出力せず データの入力規制の内部処理として利用するなら、INDEXで行単位でまるっと取得した結果を利用できるってことですね。
Excelの 名前の定義と INDIRECT を使った 連動プルダウン
もう1つは 名前の定義を使って INDIRECTで呼び出す方法を使った 連動プルダウンです。
まず先に「名前の定義」という機能で、項目１で選択した値に 連動してリストにしたい範囲に名前をつけます。ここで範囲に設定する名前を、それぞれ項目1の選択肢の値とします。
つまり、項目１で「野菜」を選んだ時に項目２にリストとして表示させたいF2:I2 を 野菜 という名前に、同じく F3:I3を フルーツ、F4:I4を 肉類 と名前を定義するわけです。
これで準備はOK
項目2の範囲 B2:B10 を選択し データの入力規制 ＞ リスト で範囲（元の値）を
=INDIRECT(A2)
こうするだけです。簡単ですね。
このとき 項目１がなにも選択されていな状態だと「エラーと判断されます」という情報ポップがでますが、問題ないので「はい」で進みましょう。これで完成です。
範囲にそれぞれ名前をつける手間がかかりますが、式はシンプルなのでこちらの方が初心者向けかもしれません。
このように Excelの場合は、 プルダウンのリスト（元の値）に 数式を使うことで連動プルダウンが簡単に実現できます。
Googleスプレッドシートは 出力した範囲を使って 連動プルダウンを作る
一方、Googleスプレッドシートでは 2023年7月現在、プルダウンのリスト範囲に 数式を指定する（関数を利用する）ことが出来ません。
Excelと同じことをやろうとするとエラーになるのです。
これが モヒカンヘアで むき出しのバイクに乗ってるような Excelユーザーに「ヒャッハー。スプシのプルダウン使えねーな。雑魚が！」と言われる要因です。
こんなExcelユーザーに限ってテーブルもパワクも使えないことが多いんで、「お前は既に死んでいる」なんですが、同じことが出来ないのは認めざるを得ませんし、悔やんでも仕方ないです。
Googleスプレッドシートで連動プルダウンを実現する為には、数式で絞り込んだ結果を 一度セルに書き出して、そのセル範囲を 参照するという方法を使うことになります。
Googleスプレッドシートの INDEX + XMATCHで 連動プルダウン
まずは 先ほどのExcelで使った連動プルダウンのサンプルと同じようなものを作ってみましょう。
Googleスプレッドシートでも 名前付き範囲 + INDIRECT関数で出力する方法もありますが、項目が増えるたびに範囲に名前を付けるのが大変です。
ここは もう1つの方法、INDEX + MATCH方式 がよいでしょう。
もちろん 項目1の値を使って リストを絞り込めれば良いので、XLOOKUPだろうが FILTERだろうが、QUERYだろうが、どの関数を使っても構いません。
Excel の場合は リスト範囲の設定内で使える 式には制限がありますが、Googleスプレドシートの場合はセルに書き出せればなんでもいいわけです。
とりあえずは Excelの事例で使ったINDEX + MATCH方式 がわかりやすいかなと。
また、全ユーザーが 最新関数を同じように使えるのが Googleスプレッドシートの魅力の一つですから、せっかくなんで MATCH関数の上位互換、XMATCH関数を使っときましょう。（ここで XMATCHを使うメリットは、単に 第3引数のFALSEが不要ってくらいですが）
=INDEX($F$2:$I$4, XMATCH(A2,$E$2:$E$4),)
あとは 項目2のプルダウン 範囲を K2:N2とすればいいですね。
とりあえず INDEXとXMATCHで 2行目（1ヶ所）だけの 2段階連動プルダウンは出来ました！
2段階 連動プルダウン を複数行で使えるようにする為の基本
A2セル→B2セルの連動プルダウンは出来ましたが、これだと1ヶ所だけで 3行目以降は連動プルダウンになっていません。
基本の考え方としては、Googleスプレッドシートは 1つの連動プルダウンに1つ リスト範囲を用意する必要があります。
つまり
こんな感じで 項目１（A列）選択 で絞り込んだ 結果を 対応する行に出力させた 範囲を 項目2のリスト範囲とする必要があるってことです。
ここで 前回少し触れた、プルダウンのリスト範囲を相対参照する必要が出てきます。
マウスで範囲を選択しただけだと、自動で $が付いて絶対参照化されます。もちろん 一度保存してから $を手動削除してもいいんですが、一発で相対参照にしたい場合は
'シート13'!K2:N10
↓
='シート13'!K2:N10
このように先頭に手動で = を付ける 方法があります。
これで設定保存すことで、イコールの後ろの範囲は 自動で絶対参照化されなくなります。
Q1. INDEX + XMATCHをスピらせたい
=INDEX($F$2:$I$4, XMATCH(A2,$E$2:$E$4),)
次に 項目2のリストを行毎に生成する式の方を考えましょう。
もちろん、上の式を下にフィルコピーしてもいいんですが、1つの式で一発処理できた方がいいですよね？
では、ここでミニお題です。
この式を A2:A10 を検索キーとして 10行目まで結果をスピらせる式にするにはどうすればよいでしょうか？
合わせて、項目1が選択されていない時はエラーではなく空白を返すようにしておきましょう。
新関数を理解していれば簡単な問題です。まずは自力でチャレンジしてみましょう。
↓↓↓
回答は以下
↓↓↓
A1. INDEX + XMATCHをスピらせる式
それでは正解です。
=MAP(A2:A10,LAMBDA(v,IFERROR(INDEX(F2:I4, XMATCH(v,E2:E4),))))
残念ながら INDEX関数は ARRAYFORMULAでは スピらない関数なので、このように LAMBDAヘルパー関数のMAPを使うと良いでしょう。
行単位の処理なので MAPでなく BYROWでもOKです。
エラーの際の空白処理は IFERROR,もしくは IFNA関数を使いましょう。Googleスプレッドシートの場合は 引数をまるっと省略してカッコでくくるだけで エラー時は空白を返すことが出来ます。
Googleスプレッドシートの LAMBDAヘルパー関数は、A2:A10のような縦1列のデータそれぞれに対して 横方向にスピらせた結果を返す、配列のネストに対応しています。
ここが 配列ネストに対応していない ExcelのLAMBDAヘルパー関数に比べ 圧倒的に優秀な点です。
もちろん INDEX +XMATCH ではなく、ARRAYFORMULAで縦横スピルが出来る VLOOKUPに切り替えるという方法もあります。
=ARRAYFORMULA(IFERROR(
VLOOKUP(A2:A10,E2:I4,
SEQUENCE(1,COLUMNS(E2:I4)-1,2),false)))
SEQUENCE(1,COLUMNS(E2:I4)-1,2)
の部分は 普通に
{2,3,4,5}
と書いた方が短くて簡潔ですが、指定したリスト範囲に合わせて可変になる式にしておきました。
本当はここで 新関数のXLOOKUPを使いたいところですが、残念ながらXLOOKUPは縦横スピルできないという弱点があります。
ARRAYFORMULAと組み合わせた時は、VLOOKUPの方が出番が多いのです。
Googleスプレッドシート 2段階 連動プルダウンを試してみよう
それでは完成した 2段階 連動プルダウンの動きを見てみましょう。
K2:N のリスト範囲の値 が 項目１と連動して変わるので当然ですが、項目２のプルダウンが連動していますね。
今回はわかりやすく 同じシートに項目2 用の 範囲を用意しましたが、通常は別シートに範囲を作成し シートを非表示とすることが多いです。
その際は、式を 別シート参照にアレンジすればOK。条件付き書式と違って データの入力規制は 他のシートをそのまま参照できます。
Googleスプレッドシートの 基本の 2段階連動プルダウンができました。
作業セルを使うんでスマートさが足りないですが、割と簡単に作れるなって感じじゃないでしょうか？
以前はリストが相対参照しなかった
でも、これ今は 凄く簡単なんですが Googleスプレッドシートは何年か前（確か2020年くらいまで）は、プルダウンのリスト範囲の相対参照が出来ませんでした。
プルダウンが参照する範囲はセルをコピーペーストしても変わりません。よって連動するプルダウンを複数組作成する場合、２組目以降のプルダウンについては参照範囲を1つ１つ手動で設定する必要があります。
いきなり答える備忘録さんでも、2019年11月掲載の連動プルダウンの記事では、このように書かれています。
さすがに 数十、数百と 連動プルダウンを作るのに 手動で1つ1つ範囲指定はありえないですよね。
じゃあもう GASでやるしかないのか！？となっていた当時、こんな 裏技 を発見された方がいたんです。これは mirも驚きました。
✖ 出来ない
Excelで 数式を使ったプルダウン作成 → Googleドライブアップロード → Googleスプレッドシート変換
✖ 出来ない
Googleスプレッドシートでリスト範囲を 相対参照するプルダウンを作成
○ なぜか出来る
Excelで リスト範囲を 相対参照するプルダウンを作成→ Googleドライブアップロード → Googleスプレッドシート変換 → なぜかリストが相対参照になる！
Excel側で相対参照のプルダウンを作ってから スプレッドシートに変換することで回避できたんですね～。もちろん Excelが使える環境である必要がありますが、こんな技があったとは。
Webの Excelオンライン も 数式を プルダウンのリストに指定できないけど、ローカルで作成した 数式を入れたプルダウンは 動くんですよね。
Web版は 裏では対応した設計になってるのに、直接は操作できないってのが意外とあるのかもしれません。
最近回答したネタですが、 Googleドキュメントの フォント10.5指定なんかも 変な仕様だな～と思います。
この辺りの裏技って、今どきの「○○ハック」みたいな 時短テクニックというよりも、 昔のファミコン時代の バグ技（ウルテク）に近いものがある気がしますｗ
Googleスプレッドシート 汎用性を高めた 多段階 連動プルダウン
ここまでの内容は、他のサイトで紹介している 連動プルダウンと大きな差はありません。サイトによって 使う関数は若干違ったり 古い操作画面だったりしますが、
数式を使って絞り込んだ結果を プルダウンリストに使う
プルダウンの分だけ リスト範囲を用意する
この2つが基本となります。
多段階になるほど 作業（リスト用）セルとシートが増える
しかし、この2の 「プルダウンの分だけ リスト範囲を用意する 」が結構面倒だったりします。
上で書いた リスト範囲が相対参照しない時代よりはマシですが、それでも 何十、何百とプルダウンがあったら、その分の範囲（行）を用意する必要があります。
さらに、連動が2段階でなく、3段階、4段階の連動となった際は、クロス表のマスタの ままだと複数用意することになります。
項目数が読めず横方向にどれくらい出力されるか不明な場合は、リスト範囲用にシートを段階ごとに用意する必要が出てきます。
つまり、上のやり方を拡張して 3段階 の連動プルダウンを実現するには
こんな手順になるわけです。
慣れれば 3段階くらいならそこまで手間に感じず出来ますし、理解が早い人なら これだけであとは自分で応用して （あまりないですが）4段階、5段階プルダウンも作れると思います。
でもこれを初心者ですって人に説明するのは、すごーく面倒なんですよね。
もっと簡単に作れて、簡単に人に教えられる汎用性の高いプルダウンを作りたい！
ってのが、今回の メインテーマ（ 薬師丸ひろ子ではない ）です！
次回、いよいよ 連動プルダウンの応用編へ
期待値が上がりすぎるとガッカリするので、先に言っておきます。
mir の提唱する 汎用的なプルダウンは、上の リスト毎に 行を用意する方法と同じことが出来るわけではありません。
ここでポイントとなるのは 「完璧を目指さない」ということです。
特に自社内で利用する連動プルダウンなら、ある程度割り切って部分的には運用でカバー（ユーザーにルールを周知・徹底）とすることで、連動プルダウンの作り方もぐっと簡略化できます。
一方で 簡略化といっても 汎用的な連動プルダウンは 複雑な数式を使うことになるので、意味を理解したいって人にはグッとハードルがあがるかもしれません。
逆に コピペでそのまま使えて 手順（工数）少なく作れるなら ラッキーって人には 良いと思います。
詳細は次週、プルダウンシリーズ3回目で！
いいなと思ったら応援しよう！
