Googleスプレッドシートで他のセルの内容に応じてプルダウンリストの内容を変更したい

Question

前提・実現したいこと

Googleスプレッドシートで他のセルの内容に応じてプルダウンリストの内容を変更するということをしたいです。

Excelではindirect 関数や名前付き範囲で設定できたのですがうまくいきません。

発生している問題・エラーメッセージ

エクセルから移行したい仕事用のファイルがいくつかありますが、これができないので障害になってます。

該当のソースコード

ソースコード

試したこと

ここに問題に対して試したことを記載してください。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

素人レベルのため説明不足ですみません汗。元々Excelで販売分析をしておりました。例えばC列にお客様の区分を入れるとします。区分が個人ユーザー、学校、一般法人から選択するとします。
D列にはその区分の中から、さらに詳細にどのようなお客様かを入力します。
例えば学校→幼稚園、小学校、中学校、高校、大学など、一般法人なら販売エリア→関西、首都圏、九州などC列の内容によってプルダウンリストの内容を変えたいのです。

Comments

[draq]

データの入力規則で「セルにプルダウンリストを表示」にチェックした場合に表示されるリストを他のセルの値に応じて変更したいということでしょうか。

[momo040228]

素人レベルのため説明不足ですみません汗。元々Excelで販売分析をしておりました。例えばC列にお客様の区分を入れるとします。区分が個人ユーザー、学校、一般法人から選択するとします。
D列にはその区分の中から、さらに詳細にどのようなお客様かを入力します。
例えば学校→幼稚園、小学校、中学校、高校、大学など、一般法人なら販売エリア→関西、首都圏、九州などC列の内容によってプルダウンリストの内容を変えたいのです。

[draq]

それなら sawa さんの回答が参考になりませんか。Filter 関数や Query 関数を使って、フィルタした結果をどこかに設定しておいて、プルダウンリストの内容をそのセル範囲に設定することで実現できそうです。

[momo040228]

ありがとうございます。一度やってみます！

[sawa]

あれ、こっちでこんなやりとりがあったのですね。draq様、補足ありがとうございました。

プルダウンリストの範囲とFilter関数については、列方向（横）も出来るので、まさに Excelのindirectを使った可変プルダウンリストと同じようなことが出来るかと思います。とりあえず解決されたようでなにより。

[momo040228]

え？横方向？？
まだこれからするのですが、なんだか混乱してきました。。。。
それって、何か参考例とか、具体的な数式書いているのとかないですよね？？汗

[sawa]

回答を追記しました。上記コメントはやや誤りで、同じようなことは出来るけど面倒です。

Filterの使い方や、スプレッドシートの連動プルダウンは、検索すればいくらでも丁寧に書かれたサイトは出てきます。あと、Yahoo知恵袋の方でも質問されたんですね。そっちにも回答ついてるので大丈夫ですかね。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12228412192?__ysp=44K544OX44Os44OD44OJ44K344O844OI

Answer 1

スプレッドシートはExcelと違ってプルダウンリストの指定に関数が使えません。固定した範囲を指定するしかないです。これはちょっと残念なところです。

ただ、スプレッドシートには元々Excelでいうところのスピル機能の関数が備わっているので、もし連動するような可変のプルダウンを作りたい場合は、プルダウンリストとして指定した範囲の中身を可変にすることで実現できます。（Filter関数などを使う）

<追記>
「解決済」にはなってるけど、実はまだ解決してないとのことで補足説明を。
まず私の回答およびコメントで、「関数を使うことで Excelのindirect等を使ったプルダウン連動と同じようなことがスプレッドシートでも出来る」と記載しましたが、これはちょっと説明不足でした。
「出来るけど 非常に面倒」です。
理由としては、連動プルダウンの入力箇所が複数ある場合は、その一つ一つに対して リストの範囲を指定してあげる必要があるからです。しかもプルダウンリストの範囲は固定なのでコピペやオートフィルが通じません。まさに一つ一つ設定する必要があります。

■参考サイト
https://aimstogeek.hatenablog.com/entry/2019/02/01/170222

これは量が多い場合は、あまりに現実的でないので、回避策として以下の2つの方法を記載しておきます。

1. 関数でFilter等に使う検索キーを可変とする
C列が第1プルダウン、D列が第2プルダウンとすると以下のような関数で取得するC列のキーワードで
FilterやVLOOKUP,、Queryをかける感じです。

=INDIRECT("C"&MIN(ARRAYFORMULA(IF((C:C<>"")*(D:D=""),ROW(C:C),))))

　これは、第1プルダウンが選択されていて、第2プルダウンが空欄で一番上の行の第1プルダウンの値をキーワードとして取得しています。これによって、1箇所固定で連動プルダウンとしています。
ただし、気にしなければいいはなしですが選択後のプルダウンではエラー表示が出ますし、上から順に選択していかないと 選択肢が連動してないというケースも出る為、運用に依存しがちだったりで、自分のみや身近な人が使うレベルにとどめておいた方がいいやり方です。

**2. on Editで Google Apps Scriptからプルダウンをセットする **
こっちの方がスマートですが、スクリプトを触ったことのない人にはハードルが上がるので、そこが悩ましいところです。

とりあえず、この質問への回答は以上といたします。

連動プルダウンに関しては、現状ではGoogleスプレッドシートは、Excelほど気軽には設定できないとお考え下さい。（私も色々苦労したのを思い出しました）