Googleスプレッドシート プルダウンリスト活用術 3（連動プルダウン 応用）
Googleスプレッドシートのプルダウン の3回目です。前回は 連動プルダウンの基本を紹介しました。今回は連動プルダウン応用編です。
汎用性が高くて、3段階、4段階と 何段階でも対応可能な 連動プルダウンは作成できるでしょうか？
シリーズ前回の記事
プルダウンはネタの宝庫なんで、マガジンにまとめています。今回とは別の方法の3連以上のプルダウン方法も記載！
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))))
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)))))
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)))))
といっても、考え方はこの式の延長線上なので、最初から汎用的な式を作成しちゃった人もいるかもしれません。
それではこれを最後のお題・・と思いましたが、これはちょっと難しいって人が多そうなんで、そのまま解説にいきましょう。
汎用プルダウン式 ↓
=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をスプレッドシートで管理してる人だと、便利そうなんですけどね。
次回は軽めの関数系単発ネタでもやろうかなと思います。
いいなと思ったら応援しよう！
