Googleスプレッドシートプルダウンリスト活用術 3（連動プルダウン応用）

mir

2023年7月30日 12:09

Googleスプレッドシートのプルダウンの3回目です。前回は連動プルダウンの基本を紹介しました。今回は連動プルダウン応用編です。

汎用性が高くて、3段階、4段階と何段階でも対応可能な連動プルダウンは作成できるでしょうか？

シリーズ前回の記事

Googleスプレッドシート連動プルダウンのゴール

こんな感じの多段階の連動プルダウンを作成するんですが、

何段階でも対応できる汎用性の高い1つの式で対応できる
プルダウン毎のリスト行を作らず1つの表で対応できる
プルダウンの設定も列毎ではなく1つの設定で対応できる

汎用性を高める為に、これを目指します。

1つのマスタ表、1つの式、1つのプルダウン設定で作る連動プルダウンとなる予定です。

ただ、前回の最後にお伝えした通り

幾つかの制限（運用でカバーすべき点）がある
2段階の連動プルダウンだったら前回紹介した手順で十分

これらを前提としたものとなります。その点ご了承ください。

汎用的連動プルダウンへの道(前編）

それでは汎用的な連動プルダウンをステップを踏みながら作成していきましょう！

理解をすっとばしてコピペで使いたい！

って方は、この前編、後編はスキップして 「汎用的連動プルダウンへの道（まとめ）」を見てください。

個人的にはこの「汎用的連動プルダウンへの道」の制作過程は、今まで noteで紹介した Googleスプレッドシートの関数記述やテクニックをかなり使っているので、好きな人には面白いかと思います。

それでは、スプレッドシートプルダウン機能のディープな世界、深い海へと潜っていきましょう。

道1. マスタ表はリスト形式に

まず選択肢となるマスタの表を見直しましょう。

前回までプルダウンの選択肢マスタとして使っていたの、画像右のいわゆるクロス表と呼ばれるものです。

ただクロス形式だと 3段階なら 2つ、4段階なら3つと 連動数に合わせて表も増えていく上に、絞り込む式もそれぞれの表に対応するものを作成する必要があります。

これだと汎用的とは言えません！

だからクロス表ではなく、マスタは左のようなリスト表（リスト形式）とします。

これで選択肢を1つの表に集約することができました。

クロス表、リスト表について知りたい方は過去noteを参照ください。

道2. プルダウンを使う時は途中でやめないをルール化

では、このリスト表からどのようにプルダウンの選択肢を抽出するのか？

これを実現する為には、運用ルールで

「プルダウンを使う時は途中でやめない」

をユーザーに徹底することが重要になります。どういうことか？

上のようにプルダウンを項目1だけ、もしくは項目2まで選んだけど

途中でやめた行（4行目や6行目）を放置しない

つまり連動プルダウンを操作したら、

きっちり最後（上だと項目3）まで選択する！

をルールとして順守しましょうってことです。

この選択途中の状態を許容してしまうと、プルダウンのリスト範囲を行毎に用意する以外には対応方法がなくなります。

上の画像のように「途中でやめた」プルダウンを選択途中で放置している行がなければ、唯一 プルダウン選択途中の行が、今ちょうど使っているプルダウンであると言えます。

つまり、今操作している行用にだけ連動プルダウンのリストを数式で生成してあげればいいってことになります。

例えば項目が3つなら A:Cの範囲で行毎に見たときに値が入ったセルが 1つ、または2つの行の項目1、または項目1と項目2の値を使ってマスタのリスト表を絞り込めばよいわけです。

上の画像だと８行目が「今操作している行」になるので、マスタを 1列目が A8セルで選択された「野菜」と一致するという条件で絞り込んだ 2列目をリストとして項目2の B8 セルのプルダウン選択肢に表示という処理になります。

ここさえルール化できれば、各行毎にリストを出力することなく連動プルダウンが作成できそうですね！

もし、選択肢によって項目3まであるものと、ないものがある場合は、選択肢がない部分は "-" や "なし" として、表の中に空白がないマスタ とした上で、必ず最後まで選択してもらうようにしましょう。

もちろん「途中で選択をやめない」と合わせて、

「プルダウンはちゃんとに左（項目1）から順に選択していく」

もルールとしておくことも重要です。

項目1、2をすっとばして、いきなり項目3を選ばれると困るのでｗ

道3. FILTER関数でプルダウン選択中の値（絞り込み条件）を取得

では、今操作中の行をどうやって取得するか？

ここでは mirの noteでは QUERY関数よりも活躍の回が多い FILTER関数が有効です。

それではここで、一発目のミニお題です。

Q1 上の画像右のようにプルダウンというシートの A2:C20に設定されたプルダウンの中で選択中の行（行毎にみて値が入ったセルが 1以上 2以下である行）の値を FILTER関数で取得するには、どのような式を作ればよいか？

FILTER関数の応用ですね。自信がある人はまずは自力で考えてみましょう。

↓↓↓
回答は以下
↓↓↓

A2. 回答 FILTER内で BYROWを使う

=FILTER('プルダウン'!A:C,BYROW('プルダウン'!A2:C,LAMBDA(r,ISBETWEEN(counta(r),1,2))))

範囲は'プルダウン'!A2:C としてもよい

FILTER関数が条件に使えるのは基本は1列（1行）ですが、BYROW関数で行毎に COUNTAで値の入ったセルの個数を取得し、それが1以上2以下である（全て空白ではなく 3つとも埋まってるわけでもない）という条件を ISBETWEEN関数で TRUE,FALSE で出力した結果（1列）を条件としています。

このFILTER内で BYROWを使う方法は、FILTER関数超応用例3で使ったテクニックですし、

FILTERの条件で ISBETWEENを使うテクは FILTER関数超応用例2 で紹介しましたね。

LAMBDAヘルパー関数のBYROWについては、登場時の noteも参考に。

この式で、今プルダウン選択中の行の値を取得できるようになりました～。

ただし、

ISBETWEEN(COUNTA(r),1,2)

の 2は今回は 3段階プルダウンで3列なので 1つ少ない2としてますが、プログラミングでいうところの マジックナンバー （後から見たときに他の人が意図がわからない数字）になっています。

ここを汎用的にするために LET関数と組み合わせて少し式を書き換えておきましょう。

=LET(x,'プルダウン'!A:C,FILTER(x,BYROW(x,LAMBDA(r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)))))

イーロンが推してる X（えーっくす）

LET関数でプルダウン!A:C を x と置くことで、範囲指定を1ヶ所に集約して式がすっきりしました。

LET関数は Excelから輸入された 最新関数の一つです。

また、マジックナンバーとなっていた 2を入れていた箇所も

COLUMNS(x)-1

としたことでプルダウン範囲を変更すれば、4段階、5段階とした場合も列数に応じて 列数-1で 3,4 を返す汎用的なものになりました。

動作を確認しておきましょう。

これで柔軟に 選択中のプルダウンの値が取得できる式が用意できました。

道4. プルダウン選択中でない場合はそのままマスタを表示

この道3で作成した FILTER式の結果を使って、マスタのリスト表をさらに絞り込みすれば、連動プルダウン用のリストになりそうですね！

とりあえず先に LETをさらに使って

マスタのリスト表を y ,
先ほどのFILTERの結果を z

と置いておきましょう。

もちろん、絞り込みに使うのは FILTER関数です。FILTER関数の結果を使ってさらにFILTER関数で絞り込むってことで。

=LET(
x,'プルダウン'!A:C,
y,A2:C, z,FILTER(x,BYROW(x,LAMBDA(r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)))), FILTER(y, 【 zを使った条件式】))

こんな感じで、秘技ダブルFILTER！ って式を作ればよさそうです。

でも、そもそもプルダウン選択中ではない場合は、先ほどのFILTER式の結果（z）は、#N/A となります。

項目1を選択する段階では zは #N/A なので、zを条件にしてマスタを FILTER関数で絞り込むことは出来ません。

先に zが NAエラーならマスタのリスト表(y)をそのまま返すという分岐を入れておきましょう。

ここで使う式は IF関数 + ISNA関数 が良さそうです！

=LET(
x,'プルダウン'!A:C,
y,A2:C, z,FILTER(x,BYROW(x,LAMBDA(r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)))),
IF( ISNA( z ) , y , FILTER( y , 【 zを使った条件式】) ))

こんな式にしてみました。

これ、LETで A2:C を y と置いているので、そのまま IF(ISNA(z),y と書けるんですが、そのまま A2:C を使った場合、 IF(ISNA(z),A2:C　では結果はスピりません。

そのまま A2:Cを使う場合は IF(ISNA(z),{A2:C} と書く必要があります。

Excelだと勝手にスピるんで気にならない点ですが、GoogleスプレッドシートのLET関数の特徴の一つです。ざっくり言えば、LETで変数化したものは書き出し時にスピるってことです。

余談：IFNA と ISNA の使い分け

もうちょっとだけ脱線しますが、ISNA関数に似たような名前の関数で IFNA関数もあります。この2つは使いどころが違います。

ISNA関数
　ISNA(値) と記述する。 1つの引数
　値が「#N/A」のエラー値であるかを判定 TRUE,FALSEを返す
　#N/Aである → TRUE
　#N/Aでない → FALSE

IFNA関数
　IFNA(値, NAエラーの場合の値) と記述する。 2つの引数
　第1引数の値が「#N/A」のエラー値であるかを判定
　#N/Aである → 第2引数で指定した NAエラーの場合の値を返す
　#N/Aでない → 第1引数の値をそのまま返す　

イフナとイスナは関数ちゃんだったら双子の幼女キャラなイメージ

今回の場合は zが #N/A を返した時は、yをそれ以外（zが NAエラーではない）の時は、別の FILTER式の結果を返すという使い方なので、IFNAではなく IF関数と ISNAを組み合わせています。

もちろん IFNAを使って最後のFILTE式自体を評価する

=LET(
x,'プルダウン'!A:C,
y,A2:C, z,FILTER(x,BYROW(x,LAMBDA(r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)))),
IFNA( FILTER( y , 【 zを使った条件式】) , y ))

このような記述してもよいですし、こちらの方が式としてはスッキリするんですが、処理の重い FILTER式を実行してから判定するよりも

このように zが NAエラーの段階で振り分けた方が無駄に FILTER式を実行することなく 処理として軽いかなと思うわけです。

ちなみに IFERROR とISERROR関数の方がピンとくる方の方が多いかもしれません。使いわけは IFNAとISNA の時と同じです。

INFA,ISNAが #N/Aエラー だけを評価するのに対して IFERROR,ISERRORは全エラーを評価対象とします。

IFNA、ISNAとIFERROR,ISERRORについては、おなじみ 「いきなり答える備忘録」さんも書かれており参考になります。

道5. FILTERの結果を使ってFILTERでマスタを絞り込む

というわけで、あとは最後の FILTER内の 評価式（画像のグレー線で隠した部分）を考えるだけです。

ここをミニお題第2弾としましょう！

Q3. 以下の式を完成させるにはどうすればよいか？

=LET(
x,'プルダウン'!A:C,
y,A2:C, z,FILTER(x,BYROW(x,LAMBDA(r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)))),
IF( ISNA( z ) , y , FILTER( y , 【 zを使った条件式】) ))

マスタデータのサンプルはこちら

項目1	項目2	項目3
肉類	豚肉	ポークソテー
肉類	豚肉	トンカツ
肉類	豚肉	しょうが焼き
肉類	鶏肉	チキンソテー
肉類	鶏肉	唐揚げ
肉類	鶏肉	チキンカツ
肉類	牛肉	すき焼き
肉類	牛肉	しゃぶしゃぶ
肉類	牛肉	ステーキ
野菜	キャベツ	千切り
野菜	キャベツ	ロールキャベツ
野菜	キャベツ	野菜炒め
野菜	ニンジン	グラッセ
野菜	ニンジン	シリシリ
野菜	ニンジン	ナムル
野菜	タマネギ	スープ
野菜	タマネギ	マリネ
野菜	ジャガイモ	肉じゃが
野菜	ジャガイモ	じゃがバター
野菜	ジャガイモ	ポテトフライ
果物	リンゴ	アップルパイ
果物	リンゴ	リンゴジャム
果物	バナナ	クレープ
果物	バナナ	スムージー
果物	ミカン	ジュース
果物	ミカン	ゼリー
果物	イチゴ	イチゴジャム
果物	イチゴ	大福
果物	イチゴ	ショートケーキ

考えてみましょう！

↓↓↓
回答は以下
↓↓↓

A3. 連動プルダウン用 FILTER式

これも色々書き方があるので、あくまでも一例です。

=LET( x,'プルダウン'!A:C, y,A2:C, z,FILTER(x,BYROW(x,LAMBDA(r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)))), IF(ISNA(z),y,
FILTER(y,(index(y,,1)=index(z,,1))*IF(index(z,,2)="",true,index(Z,,2)=index(y,,2)))))

FILTER式の条件部分

(index(y,,1)=index(z,,1))*IF(index(z,,2)="",true,index(z,,2)=index(y,,2))

なんかごちゃごちゃしてますね。

これは乗算の演算子 * を使った AND条件です。詳しくは FILTER関数超応用の第1回を参照。

LETで定義した z （選択中のプルダウン行）、y（マスタリストの範囲）から indexを使って列単位のデータを取得します。

1つ目の条件　index(y,,1)=index(z,,1)　・・・項目1が一致する

これはいいですね。マスタの項目1 が選択中のプルダウンシートの行項目1で選択された値（画像の例だと果物）と一致するという条件です。

2つ目の条件　IF(index(z,,2)="",true,index(z,,2)=index(y,,2)))

これは少し複雑です。

index(z,,2)=index(y,,2)

単純にこのようにしてしまうとプルダウンで項目1だけ（果物を）選択した状態の時に、プルダウンの項目2 index(Z,,2) は空白である為、

項目1が果物かつ項目2が空白という条件でマスタの絞り込みをかけてしまいます。

当然そんな行はないので #N/Aエラーが返ります。

これではプルダウンの選択肢になりません。

よって項目2が空白の時は項目1だけを条件とする式にする必要があります。

それを IFで分岐させた式が

IF(index(z,,2)="",true,index(z,,2)=index(y,,2)))

となります。

項目2が空白だった時は true を返すので、

( index(y,,1)=index(z,,1) ) * true

このような式になり、項目2が空白の時は項目1だけを条件とするを満たすことができます。

Googleスプレッドシートの FILTER関数の AND条件は、

FILTER(範囲, 条件1, [条件2, ...])

こんな感じでカンマ区切りで羅列することも出来るのですが、この書き方だと項目2が空白だった時の true を 範囲と同じ行数分用意する必要があり面倒です。

そのため、今回は 演算子を使った条件処理としています。

一旦完成となるので、同じ式ですがインデント付きも掲載しておきましょう。

=LET(
  x,'プルダウン'!A:C,
  y,A2:C, 
  z,FILTER(
      x,BYROW(
        x,LAMBDA(
          r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)
        )
      )
    ),
  IF(ISNA(z),y,
    FILTER(y,
      (index(y,,1)=index(z,,1))*IF(index(z,,2)="",true,index(Z,,2)=index(y,,2))
    )
  )
)

ただし、この式はあくまでも 3段階（3列）のプルダウンを前提とした、3段階でのみ動く式です。

4段階、5段階にも対応できる 汎用的な式 にするにはもう一工夫必要です。（もしかすると、この段階で何段階でも対応できる式を作成してしまった人もいるかもしれませんが。。）

ゴールはもう少し先になりますが、ここで一旦プルダウン範囲を設定して、作成した式で 連動プルダウンとして機能するかをテストしておきましょう。

道 6. 絶対参照と相対参照を組み合わせて1回で連動プルダウンを範囲指定

この式の結果の範囲（マスタ! E2:G）をプルダウンリストの範囲に設定します。

プルダウンを設定したいプルダウンシートの A2:C20を選択して

右クリック＞プルダウンでサイドバーを表示

プルダウン（範囲内）を選択して、範囲を

='マスタ'!E$2:E

このように指定します。

※さらに詳細オプションから表示スタイルを矢印に変更。

ここのポイントは行方向は絶対参照、列方向は相対参照としている点、そして E$2:Gではなく E$2:Eと 1列にしておく点です。

単体列のE列を選択して相対参照にすることで、

項目1（A列）のプルダウン範囲はマスタシートの E列
項目2（B列）のプルダウン範囲はマスタシートの F列
項目3（C列）のプルダウン範囲はマスタシートの G列

と自動で列方向の参照が自動でズレるので、1列ずつ設定する手間がはぶけます。これだけでも大きいですね。

道1で 表をリスト形式にしたのはこの効果もあるからです。

これで動かしてみましょう！！

汎用的連動プルダウンへの道（前編）までの動作確認

1つの表、１つの式、１つのプルダウン範囲設定で、3段階の連動プルダウンが機能してますね！

プルダウンの1回目でも紹介しましたが、Googleスプレッドシートのプルダウンは 自動で重複排除され一意の値になります。

だから項目1で肉類で絞り込んだあとFILTER関数の結果のリストに豚肉や鶏肉が複数あっても プルダウン上の項目2の選択肢は 1つずつしか表示されません。この効果も大きいです。

また、プルダウンの選択途中だとFILTER関数が返すプルダウンのリストが選択中の行に合わせた形に絞りこまれているので、

このように選択を終えたプルダウンの行に 赤い ◥ がついて「選択肢以外の値」として扱われエラー表示になります。

もちろんプルダウンの選択が完了すれば、式で返るプルダウンのリストは全件になる為、エラーは解消されます。

これに関しては「プルダウンを使う時は途中でやめない」がルール化できれば問題ないと言えます。

まずはここまではクリアです。

ちなみに、3段階で十分で自分だけが使うプルダウンなら、この段階で実践投入でも良いです。

汎用的連動プルダウンへの道(後編）

それでは、さらなる高みへ プルス・ウルトラ！ ってことで後半戦です。

先ほどのFILTER式の汎用化もそうですが、実はまだこのプルダウンには致命的な欠点があります。その部分をカバーする設定をしていきます。

道7. 汎用的連動プルダウンの弱点を理解する

「プルダウンを使う時は途中でやめない」がルール化できれば問題ない。

と言っても、やはりユーザー側の操作を運用ルールで制限するには限界があり、うっかり途中で忘れて放置しちゃったとか、そもそもルールを聞いていなかった、といった理由で選択途中で放置された行が発生してしまう可能性があります。

さらにこの連動プルダウンの欠点として、

一度選択したプルダウンの再選択時は Deleteでリセットしてから選択する必要がある。

という制限があります。

これは一度設定完了済みのプルダウンを修正しようと、再度プルダウンでを操作した際、該当の箇所は選択済みと FILTER関数側は判断してしまうからです。

だから一度選択した項目を選びなおそうとすると、項目2の場合だと

このようにイチゴ以外に選びなおしたいのに、リストのFILTER関数側はイチゴで絞り込み済みなので他の項目2の選択肢が選べません。

項目3を修正する場合はもっと厄介で

リスト範囲の FILTER関数はプルダウン選択中の行は無いと判断する為、このように項目3の全選択肢が表示されてしまいます。

つまり、Deleteで一度消さずに選択しなおしをされてしまうと、本来選択できてはいけない

野菜　＞　キャベツ　＞　トンカツ

このような選択がエラー表示なしで存在してしまう恐れがあります。

これはちょっと困りますね。

汎用的連動プルダウンには、この2つの弱点があることをまずは理解しましょう。

道8. 条件付き書式でルールを順守していない例外的な処理をチェック

・プルダウンを使う時は途中でやめない
・一度選択したプルダウンの再選択時は Deleteでリセットしてから選択

この2つの運用ルールが守られなかった時に、ユーザーや管理者が問題がある行に気づくための工夫をしておきましょう。

本当はデータの入力規制でルールを逸脱できないように規制したいところですが、残念ながら1つのセルには1つのデータの入力規制しか適用できません。

データの入力規制はプルダウンで使ってしまっているので、代わりにこれらの禁止行為を条件付き書式を使って 違反セル（行）を色付けすることで対応しましょう。

条件付き書式を使うにあたり、今回の構成では注意点があります。

Googleスプレッドシートの条件付き書式で他のシートを参照する場合は、INDIRECT関数を組み合わせる必要がある

ここを気をつけましょう。

条件付き書式で INDIRECT関数を組み合わせた他のシートの参照については、過去にも何度か登場しています。

・プルダウンを使う時は途中でやめない
・一度選択したプルダウンの再選択時は Deleteでリセットしてから選択

では、この2つの条件が守られていない行をどのように定義して条件付き書式のカスタム数式に落としこめばよいか？

実は 2つの条件のように見えますが、これは 1つの定義にまとめることができます。

マスタのリスト表と一致しない行だったら TRUEとする

これだけです。

このように マスタのリスト表にない組み合わせだったら TRUEと定義する式を作れば、野菜＞キャベツ＞トンカツのような違反した組み合わせはもちろん、部分的に選択された（値の入った）行もマスタとは一致しないとみなされるので TRUEとなり色付けできます。

一方、正しい組み合わせは当然ですが、全て空白の行（プルダウン未選択の行）もマスタのリスト表の空白行と一致するという扱いになるので、プルダウンが一つも選択されていない行が色付けされることはありません。

では、この条件付き書式のカスタム数式をどう作るかをミニお題 3問目にしてみましょう。

Q3. マスタ表と一致しない行を色付けする為の条件付き書式のカスタム数式は、どのような式を組めばよいか？

色付けする範囲　A2:C20 （行単位で色付けしたい）
マスタ表の範囲　マスタ!A2:C
色付け条件　マスタ表の行と一致しない行を色付けしたい

↓↓↓
回答は以下
↓↓↓

A4. マスタ表と一致しない行を色付けする式

=LET(a,INDIRECT("マスタ!A2:C"),b,$A2:$C2,
ROWS(UNIQUE(VSTACK(a,b)))-ROWS(UNIQUE(a)))

条件付き書式カスタム数式

これも色々なアプローチがあるんですが、今回は UNIQUE関数 を使ったカスタム式を使ってみました。

UNIQUE関数は複数行・複数列の範囲に利用した場合は、特に引数で指定がなければ、行単位でみて同じ組み合わせの行ががあったら一意にするという動作をします。

このUNIQUE処理後のデータ数（行数）は ROWS関数で取得できます。

マスタの表・・・ a
プルダウンの範囲の1つの行　・・・　b

とすると、

VSTACK(a,b) はマスタの表とプルダウンシートの 1行を縦に連結した配列となります。

これを UNIQUE処理してからROWSでデータ数を取得したものが

ROWS(UNIQUE(VSTACK(a,b)))

です。これと、マスタ表のユニークなデータ個数

ROWS(UNIQUE(a))

の差分を取得しています。

プルダウンの行(b)がマスタ表(a)に一致する（同じ組み合わせの行が存在する）場合は、VSTACK(a,b) は UNIQUE関数で重複が一つにまとめられる為、ROWS(UNIQUE(VSTACK(a,b))) と ROWS(UNIQUE(a)) の差分は 0となります。

0はFALSE扱いなので条件付き書式は適用されません。

一方プルダウンの行(b)がマスタ表(a)に一致しない（違反した）組み合わせの場合は、VSTACK(a,b) を UNIQUEにしても b は残ります。この1行分があるので、ROWS(UNIQUE(VSTACK(a,b))) と ROWS(UNIQUE(a)) の差分は 1となります。

1は TRUE扱いである為、条件付き書式が適用され色付けされます。

ちなみに VSTACKは範囲や配列を縦方向に連結する Excelから輸入された新関数なんですが、ここれをなぜ中カッコ連結にしていないか？

実は LETで a,bと置いてしまったので、 { a ; b } として連結しようとすると、A列、B列と誤認されエラーになってしまうからです。

また、UNIQUEな数を数える際に COUNTUNIQUE関数を知ってる人は、これ使えるんじゃ？と思うかもしれませんが、こちらは複数行・複数列の範囲を指定した場合、行単位ではなくセル（個々の値）をみてUNIQUE処理した個数を返す関数です。

残念ながら、今回のように行単位の重複チェックには利用できません。

色付けに関しては面倒であればやらなくても良いですし、プルダウン範囲が大きい場合、条件付き書式の処理は結構重いので動きが悪くなる可能性があります。

でも誤操作でリストに無い組み合わせが選択されるリスクを考えると、なるべく設定しておくことをお勧めします。

道9. FILTER式を何段階でも対応できる式に改良する

それでは最後に道5（Q3）で作成した下の3段階プルダウン専用のダブルFILTERの式を改良して、指定した範囲の列に合わせて何段階でも対応できる汎用的な式の作成にチャレンジしましょう！

=LET( x,'プルダウン'!A:C, y,A2:C, z,FILTER(x,BYROW(x,LAMBDA(r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)))), IF(ISNA(z),y,
FILTER(y,(index(y,,1)=index(z,,1))*IF(index(z,,2)="",true,index(z,,2)=index(y,,2)))))

3段階プルダウン式

といっても、考え方はこの式の延長線上なので、最初から汎用的な式を作成しちゃった人もいるかもしれません。

それではこれを最後のお題・・と思いましたが、これはちょっと難しいって人が多そうなんで、そのまま解説にいきましょう。

汎用プルダウン式　↓

=LET( x,'プルダウン'!A:C, y,A2:C, z,FILTER(x,BYROW(x,LAMBDA(r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)))), IF(ISNA(z),y,FILTER(y,BYROW((z="")+(z=y),LAMBDA(r,PRODUCT(r))))))

汎用プルダウン式

mirが作った汎用プルダウン式がこちらになります。

最後の FILTER関数でも BYROWを使った条件記述としてますね。

FILTER(y,BYROW((z="")+(z=y),LAMBDA(r,PRODUCT(r))))

ここも、式がスッキリしましたね。少し解説しておきましょう。

(z="")+(z=y)

この部分がポイントです。なにをやっているのか？
FILTER内の記述なので配列処理がされているので、ARRAYFORMULAを付けて書き出してみましょう。

このようにプルダウン行（z）、マスタ表(y) どちらも列方向は相対参照されながら

z が空白である + プルダウンで選択したものに一致

が計算されているのが分かります。

プルダウンでは項目1で肉類が選ばれているのですが、

このように (z="")+(z=y) の結果を 行単位で見て 0が1つもない行、つまり

行単位の積が 0ではない行

が抽出対象であることがわかります。

行単位で積を計算するってことは、

行単位（BYROW）で積の計算（PRODUCT）

をすれば良いってわかりますね。

それが

FILTER(y,BYROW((z="")+(z=y),LAMBDA(r,PRODUCT(r))))

この式になります。

この FILTER に BYROWと PRDOCUTを組み合わせた式、実は FILTER関数シリーズの最終形の式でも登場しています。

複雑な条件処理での抽出をする際、非常に強力な組み合わせとなります。覚えておきましょう！

世の中には頭の中でプログラミングして、脳内デバッグまで出来ちゃう天才がいるんですが、その手のタイプの人だと一発でこんな式も作れちゃうんでしょうね。

汎用的連動プルダウンへの道（まとめ）

それでは、動作確認ついでに手順をおさらいしつつまとめます。せっかくなので 4段階プルダウンを例に手順をまとめてみましょう。

1. マスタシートにプルダウンの選択肢をリスト形式で用意する

1つの表！

こんな感じでプルダウンの選択肢を用意しましょう。

サンプルデータ↓

項目1	項目2	項目3	項目4
肉類	豚肉	ポークソテー	オニオンソース
肉類	豚肉	ポークソテー	ガーリックソース
肉類	豚肉	トンカツ	普通
肉類	豚肉	トンカツ	味噌カツ
肉類	豚肉	トンカツ	おろし
肉類	豚肉	しょうが焼き	しょうが焼き
肉類	鶏肉	チキンソテー	オニオンソース
肉類	鶏肉	チキンソテー	ガーリックソース
肉類	鶏肉	唐揚げ	マヨ付き
肉類	鶏肉	唐揚げ	ポン酢つき
肉類	鶏肉	チキンカツ	チキンカツ
肉類	牛肉	すき焼き	すき焼き
肉類	牛肉	しゃぶしゃぶ	ポン酢
肉類	牛肉	しゃぶしゃぶ	ごまだれ
肉類	牛肉	ステーキ	オニオンソース
肉類	牛肉	ステーキ	ガーリックソース
肉類	牛肉	ステーキ	和風ソース
野菜	キャベツ	千切り	千切り
野菜	キャベツ	ロールキャベツ	クリーム味
野菜	キャベツ	ロールキャベツ	トマト味
野菜	キャベツ	野菜炒め	肉入り
野菜	キャベツ	野菜炒め	肉無し
野菜	ニンジン	グラッセ	グラッセ
野菜	ニンジン	シリシリ	シリシリ
野菜	ニンジン	ナムル	ナムル
野菜	タマネギ	スープ	スープ
野菜	タマネギ	マリネ	マリネ
野菜	ジャガイモ	肉じゃが	肉じゃが
野菜	ジャガイモ	じゃがバター	じゃがバター
野菜	ジャガイモ	ポテトフライ	ポテトフライ
果物	リンゴ	アップルパイ	アップルパイ
果物	リンゴ	リンゴジャム	リンゴジャム
果物	バナナ	クレープ	アイス入り
果物	バナナ	クレープ	アイスなし
果物	バナナ	スムージー	スムージー
果物	ミカン	ジュース	果汁100%
果物	ミカン	ジュース	微炭酸
果物	ミカン	ゼリー	果肉入り
果物	ミカン	ゼリー	果肉なし
果物	イチゴ	イチゴジャム	イチゴジャム
果物	イチゴ	大福	大福
果物	イチゴ	ショートケーキ	ショートケーキ

2. プルダウンを使うシートを用意し範囲を決めておく

マスタと 同じ構成（列数）としましょう。

3. 汎用プルダウン式をマスタ表の横にコピペ

1つの式！

=LET(
  x,'プルダウン'!A:D,
  y,A2:D,
  z,FILTER(x,BYROW(x,LAMBDA(r,ISBETWEEN(counta(r),1,COLUMNS(x)-1)))),
  IF(ISNA(z),y,FILTER(y,BYROW((z="")+(z=y),LAMBDA(r,PRODUCT(r)))))
)

↑ この汎用プルダウン式をコピーして

↓ マスタ表の1列開けて横あたりにペースト

式の中の

x,'プルダウン'!A:D20,　←　プルダウンの範囲
y,A2:D,　　←　マスタ表の範囲

この範囲だけは、使用するリアルデータの範囲に合わせて修正を。

4. プルダウンを設定

1つのプルダウン設定！

プルダウン式で出力した範囲（今回の場合はマスタ!F2:I）をプルダウンリスト（範囲）として設定します。

ここで重要になるのはリストの範囲の 一番左の列です。

画像のケースだと マスタ!F2:F になります。これを使います。

サイドバーの範囲を確認し、条件をプルダウン（範囲内）とし、

さきほど重要と書いたリスト範囲の一番左の列を使って

='マスタ'!F$2:F

このように先頭に＝をつけて、開始行だけ $で絶対参照にして設定します。

この時、プルダウンの表示がチップは嫌だという場合は詳細オプションから表示スタイルを切り替えましょう。

5. 誤操作防止用に条件付き書式を設定

4まででプルダウンは完成しているのですが、誤操作防止用に条件付き書式を設定しておきます。

プルダウンを使う範囲を選択し 表示形式＞条件付き書式

カスタム数式で以下の式をコピペ

=LET(a,INDIRECT("マスタ!A:D"),b,$A2:$D2,ROWS(UNIQUE(VSTACK(a,b)))-ROWS(UNIQUE(a)))

太字部分の範囲を同じくリアルデータの範囲に修正

マスタ!A:D ← 式の結果ではなく手順1で作成した元になるマスタ表
$A2:$D2 ← プルダウン設定範囲の先頭行

これで完成です。

4段階連動プルダウンを試してみよう

利用する上での注意点は

プルダウンを使う時は途中でやめない（選択中の行を放置しない）
選択後に修正（変更）する時は、一度選択をDeleteしてから

この2点です。

操作のGIF動画では、これに該当する選択中の行や修正の際に Deleteせずにありえない組み合わせを選択した行が手順5で設定した 条件付き書式で色付けされているのがわかりますね。

2段階、3段階、4段階、それ以上の多段階にも対応できる 1つの表、1つの式、1つのプルダウン設定で使える汎用的な連動プルダウン の完成です！

プルダウンのアップデートはいつの日か

全3回（3週）にわたって Googleスプレッドシートのプルダウン機能について書いてみました。

最後の汎用的連動プルダウンは、たかがプルダウンに難しすぎるよ～と感じるかもしれませんが、3段階以上の連動が必要な時には役立つ知識です。

さらに、今までの noteで登場した FILTER関数の超応用テクや新関数のLET、BYROW+LAMBDA、VSTACK、絶対参照・相対参照の使いわけなど、基本から応用までフル活用する良い事例になったんじゃないでしょうか。

もちろん、Googleスプレッドシートの今後のアップデートで、Excelと同じようにプルダウン範囲に数式が使えればベストなんですが、現在のプルダウンの変更の方向性やチップ推しの流れだと、しばらく無さそうな予感が。。

チップも Youtubeをスプレッドシートで管理してる人だと、便利そうなんですけどね。

次回は軽めの関数系単発ネタでもやろうかなと思います。

この記事が気に入ったらサポートをしてみませんか？

Googleスプレッドシート プルダウンリスト活用術 3（連動プルダウン 応用）

Googleスプレッドシート 連動プルダウンのゴール

汎用的 連動プルダウンへの道(前編）

道1. マスタ表は リスト形式に

道2. プルダウンを使う時は 途中でやめないをルール化

道3. FILTER関数でプルダウン選択中の値（絞り込み条件）を取得

道4. プルダウン選択中でない場合は そのままマスタを表示

余談：IFNA と ISNA の使い分け

道5. FILTERの結果を使ってFILTERで マスタを絞り込む

道 6. 絶対参照と相対参照を組み合わせて1回で 連動プルダウンを範囲指定

汎用的 連動プルダウンへの道（前編）までの動作確認

汎用的 連動プルダウンへの道(後編）

道7. 汎用的連動プルダウンの 弱点を理解する

道8. 条件付き書式で ルールを順守していない 例外的な処理をチェック

道9. FILTER式を 何段階でも対応できる式に改良する

汎用的 連動プルダウンへの道（まとめ）

1. マスタシートに プルダウンの選択肢を リスト形式で用意する

2. プルダウンを使うシートを用意し範囲を決めておく

3. 汎用プルダウン式 を マスタ表の 横にコピペ

4. プルダウンを設定

5. 誤操作防止用に条件付き書式を設定

4段階 連動プルダウンを試してみよう

プルダウンのアップデートはいつの日か

Googleスプレッドシートプルダウンリスト活用術 3（連動プルダウン応用）

Googleスプレッドシート連動プルダウンのゴール

汎用的連動プルダウンへの道(前編）

道1. マスタ表はリスト形式に

道2. プルダウンを使う時は途中でやめないをルール化

道4. プルダウン選択中でない場合はそのままマスタを表示

道5. FILTERの結果を使ってFILTERでマスタを絞り込む

道 6. 絶対参照と相対参照を組み合わせて1回で連動プルダウンを範囲指定

汎用的連動プルダウンへの道（前編）までの動作確認

汎用的連動プルダウンへの道(後編）

道7. 汎用的連動プルダウンの弱点を理解する

道8. 条件付き書式でルールを順守していない例外的な処理をチェック

道9. FILTER式を何段階でも対応できる式に改良する

汎用的連動プルダウンへの道（まとめ）

1. マスタシートにプルダウンの選択肢をリスト形式で用意する

3. 汎用プルダウン式をマスタ表の横にコピペ

4段階連動プルダウンを試してみよう