スプレッドシートにてプルダウンメニューの選択項目によって分岐させ別のプルダウンメニューを連動させる方法を教えて下さい。

Question

実現したいこと

1.プルダウンメニューで地方を選択
2.地方に応じて都道府県が分岐してプルダウンメニューが表示される

##参考にしたサイト
参考リンク

##問題点
上記サイトで1.2まではクリアできるのですが
都道府県の各セルでデータの入力規則を設定する必要があり、行数が多いので対応しきれません。

##参考画像

このように"近畿"と選んだ際に

こちらのシートから近畿に対応するものだけを表示するという形にしたいです。

Comments

[macaron_xxx]

GASを使えばできるんですが、ちょっとコード書く手が現状ないです。
しばらくしたら回答しますね。

[Naoki.Y]

ありがとうございます！
お待ちしています。

Answer 1

定数できっているところは適当にどうぞ。

function getPref() {
  const PREF_SHEET = 'シート2';
  const PREF_RANGE = 'D2:M12';
  
  var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(PREF_SHEET).getRange(PREF_RANGE).getValues();
  var pref = {};
  data.forEach(function(row) {
    pref[row.shift()] = row.filter(function(e) {
      return e !== '';
    });;
  });
  return pref;
}

function checkTarget(e) {
  const TARGET_SHEET = 'シート1';
  const TARGET_COLUMN = 9;
  
  if(e.range.getSheet().getName() !== TARGET_SHEET) return false;
  if(e.range.getColumn() !== TARGET_COLUMN) return false;
  return true;
}

function onEdit(e) {
  if (!checkTarget(e)) return;
  var pref = getPref();
  if(e.value === undefined) {
    e.range.offset(0, 1).clear().clearDataValidations();
  } else {
    var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(pref[e.value])
    .setAllowInvalid(false)
    .build();
    e.range.offset(0, 1).clear().setDataValidation(rule);
  }
}

Comments

[Naoki.Y]

すいません。

function getPref() {
const PREF_SHEET = 'シート2';
const PREF_RANGE = 'D2:M12';

こちらに参照先を指定して

function checkTarget(e) {
const TARGET_SHEET = 'シート1';
const TARGET_COLUMN = 9;

こちらに出力先を指定すればいいんでしょうか？

[macaron_xxx]

そうですね。
getPrefは2枚目の画像で言うH2:Q12ですかね。
checkTargetのTARGET_COLUMNは1枚目の画像の地域の列番号ですね。
地域の入力規則はコピーでいけますよね?!

[Naoki.Y]

スクリプトを試してみましたが下記のエラーが出ます。
かなりいいところまでキている感じです。ありがとうございます。

TypeError: undefined からプロパティ「range」を読み取れません。（行 19、ファイル「コード」）

[macaron_xxx]

スクリプトの実行してませんよね？
実際にスプレッドシートを編集して試してくださいね。

[Naoki.Y]

あ、実行はしないんですね。

できました！

誠にありがとうございます！

Answer 2

大本のデータやそのあとどうなりたいのかがいまいちイメージができなくて申し訳ありませんが、
おそらくやろうとしていることはQuery関数を使えば対応できるんじゃないでしょうか？

【QUERY関数】where句と比較演算子を使って単一条件に一致した行を抽出する

これを使って、

Query文に　"where 対象としている行 = プルダウンで入力するセル"
で条件を絞っていけば出せそうなきはします。

Query文の中にセルを選ぶ方法は

A CONTAINS '"&B7&"'

等と入力すればいけます。

とりあえずこういう方法もあるということで回答させていただきます。

---

一応追記しますが、
このクエリ文の結果を、プルダウン対象にする（質問にかいてあるURLのような連動シートの様に使う）ことで、
何千件とデータがあったとしても、リストを絞る事もできます。

追記

データ連携用のシートに、
=index(H2:Q,match("最初のプルダウンするセル",H2:H,0))

これで、1行にリストアップできますので、
それを二回目のプルダウンリストに　連携シート!:1:1
で取れます。

もし縦横を入れ替えたくて、A:A のような表記にしたい場合は

=TRANSPOSE(1:1)で縦横が入れ替えられたとおもいます。

追記

よくよく見てみたら、データ入力規則を対象セルにいちいち設定するのが大変だから、
何か一気にやる方法はあるのかって話だったのですね・・・ｗ

申し訳ありませんでした。私では解決できないですね。

一応参考サイトにあるVlookupの場合は横列もいちいち設定しないといけないところ、
Indexを使えば1セル設定すれば横は勝手に表示されたりはしますが、
プルダウンのセル指定は自動ではできないのでダメでしょうね。。

一応ほかの記事もみてみましたが、言ってること質問者様の参考サイトと同じですね
スプレッドシートでプルダウン連動！

カスタム関数ではリスト化できませんですし、
自分のほうで最初にいっていたQuery関数を用いても結果は変わらないです。

諦めて範囲を打ち込んでいくか、私の知らない事を知っている回答者様をお待ちください。

Comments

[Naoki.Y]

回答ありがとうございます。

参考URLを拝見しましたがQuery関数の場合だと

地域で"近畿”と指定して
都道府県名に"近畿 大阪","近畿 京都","近畿 滋賀"
のようになっていたら呼び出せるという理屈はわかるのですが

"近畿"と”大阪”,"京都","滋賀"を紐付ける方法がわからないです・・・

実現したい状況が伝わりづらかったということで元の投稿に画像を入れて追記してみましたのでアドバイスいただけると幸いです。