【GAS不要】スプレッドシートで「連動するプルダウン」を作成するMAP/LAMBDA関数

スプレッドシートのプルダウンリスト、便利でよく使っています。
でも、請求書の作成や住所入力、商品の入力などでは、「カテゴリを選んだら、そのカテゴリに合った項目だけがリストに表示されるようにしたい」と思うこともしばしば……。

対象がセル1個だけならfilterやquery関数の使用で内容を絞り込んで表示する可変リストが作成できますが、複数のセルを簡単に可変リストにするのは難しい。
1個ずつに関数を設定する、なんてこともGASを使うことも避けたいし。

と思っていたのですが、最近、1列分のセルにまとめて可変リストの設定をしたい場合も、1つのセルに関数を入力するだけで実装できたので覚書がわりにシェアします。

普段使わないだろうちょっとマニアック？な関数である「MAP」と「LAMBDA」関数を使うので、難しいかもしれないですが基本的にはコピペでできるはずなので、よければチャレンジしてみてくださいね！

前置き：可変リストとはそもそも何？

ここでは、プルダウンリストの内容が別のセルの内容に合わせて自動的に変わるリストを指して可変リストと呼んでいます。
動的リストや絞り込みリストなどでもイメージに近いかもしれません。

例えば、A2セルに「部署名」を選択したら、A3セルのプルダウンリストには「その部署の社員名だけ」が表示されます。
商品の「大カテゴリ」を選択したら、「関連する小カテゴリの商品名だけ」が表示されます。
「取扱サービス名」を選択したら、次のリストには「そのサービスの担当社員だけ」が表示されます。

こういった、通常はプルダウンリストを設定したらリストの内容は変わらないと思いますが、プルダウンリストの参照先として設定した範囲のセルを関数によって内容を切り替えることで、別のセルにしたがって可変するリストを作成していきます。

実装：例として部署と氏名の連動リストの作成と設定

この記事では、「部署名」を選んだら、次のプルダウンに「その部署の氏名だけ」が連動して表示される設定を例として、具体的なシート作成手順を解説します。

何か用途のイメージがあった方が良いと思うので、ここでは備品利用申請の一覧を作ってることにしましょう。
「どこ」の「誰」が「何」を借りたかのリストで、「何」を借りたかは自由記述にします。

なるべくわかりやすく記述するので、良ければ手を動かしながら試してみてください。

完成イメージとしてはこんな感じ。

1. マスターデータの作成

連動するプルダウンリストを作るには、まず、元となるデータベース（一覧表）が必要です。
可変リストとして絞り込みをする基準のシートですね。

「DB」という名前のシートを用意し、以下のようにデータを入力してください。
コピペでもOKです。

設定後はこんな感じ。

2.プルダウンリストの「選択肢」を生成する

次に、ユーザーが選択した「部署」に応じて、対応する「氏名」を自動で抽出する関数を記述します。

新しいシート「（例）備品利用申請一覧」を作成し、A2セル以下に「部署名」を入力するシンプルなプルダウンリストを設定します。
手順は「挿入＞プルダウンリスト」を選んで、「範囲に適用」に「’（例）備品利用申請一覧’!A2:A1000」、条件を「プルダウン（範囲内）」、範囲設定には「=DB!$A$2:$A」と入力します。

（例）備品利用申請一覧の部署名にテストでいくつか入力しておいて、今の設定はこんな感じ。

その後、DBシートの空いているセル（例：D2セル）に、次の関数をコピペしてください。

DB!D2セルに可変リストの肝となる関数を記述する

=MAP('（例）備品利用申請一覧'!A2:A, LAMBDA(category, IF(category="",, TRANSPOSE(IFERROR(FILTER(DB!B:B, DB!A:A=category))) ) ))

この関数により、（例）備品利用申請一覧シートのA列に入力されている部署名に対応する氏名が、DBシートのD列から横方向に展開されます。
これが、動的に変化するプルダウンリストの選択肢の参照先になります。

使用している関数の解説

D2セルに設定したこの関数は、主に3つの部分から構成されています。

① MAPとLAMBDA：選択肢を「作成・展開」する仕組み

 
• MAP('（例）備品利用申請一覧'!A2:A, LAMBDA(...)):
   
  • MAPは、指定した範囲（（例）備品利用申請一覧'!A2:A）のセル一つ一つに対して、LAMBDAで定義した処理を繰り返し実行してくれます。

   

  • これにより、A列にあるすべての部署名に対応する名前のリストが一度に生成され、D2セル以下に展開されます。

② FILTER：対応する「項目を抽出」する仕組み

 
• FILTER(DB!B:B, DB!A:A=category):
   
  • DBシートのB列（名前）から、DBシートのA列（部署名）が、現在のcategory（MAPで処理中の部署名）と完全に一致する行だけを抽出します。

   

  • 例：「開発部」を選んだら、「山田太郎」「佐藤花子」だけを抽出する、という処理です。

③ TRANSPOSE：リストの方向を「縦から横」に変える

 
• TRANSPOSE(...):
   
  • FILTER関数で抽出されたリストは縦並びになっていますが、プルダウンリストのソースとして使いやすいように、TRANSPOSEで横並びに変換しています。

ここまで設定できていればこんな感じになっています。

3. プルダウンリストに設定する

最後に、実際にユーザーが使うシートに、これまでに設定した動的な選択肢を適用します。

 
1. 「（例）備品利用申請一覧」シートのB列（氏名を入力させたいセル）を選択します。

 

2. メニューの「挿入」から「プルダウン」を選択し、詳細設定を開きます。

 

3. 「条件」を「プルダウン（範囲内）」に変更します。

 

4. 入力欄に、先ほど関数を記述したセルを指定し（ここでは=DB!D2）、入力内容を「=DB!$D2:2」に修正します。

プルダウンの参照元

=DB!$D2:2

通常、プルダウンの参照元を指定するときは、「DB!F2:H2」のようにリストの具体的な端のセルまで指定する必要があります。
しかし、この方法では、部署の選択によってリストの長さが変わるため、どのセルまで指定すべきかわかりません。

そこで、行をまとめて対象範囲とすることで回避して、また、＄マークを起点であるD列にだけつけて、入力セルが移動するのに合わせて参照先の行が変わるようにしています。

例えばB2セルの対象範囲はDB!$D2:2、B3セルの対象範囲は自動的にDB!$D3:3となります。

これで設定完了です！今の設定画面はこんな感じ。

「（例）備品利用申請一覧」シートのA2セルに部署名を入力（またはプルダウンで選択）すると、B2セルのプルダウンリストの中身が、その部署名に対応する氏名だけに変化するはずです。

まとめ：連動プルダウン設定のステップ

今回ご紹介した可変リストの設定は、以下の3ステップで完了します。

 
1. マスターデータの準備： 「カテゴリ（ここでは部署名）」と「項目（ここでは名前）」の2列のデータ（例: DBシート）を作成する。

 

2. 選択肢の絞り込み用に関数を設置：： DBシートの空いているセルに=MAP(...)関数をコピー＆ペーストする。この関数が動的なリストの元を自動で生成します。

 

3. プルダウンに設定： リストを使いたいセルにプルダウンを設定し、参照元に「関数を置いたセルをもとにして行全体を範囲」として指定する。

この関数は、少し難しく見えますが、コピペで使える強力なツールです。ぜひ色々なリスト作成に応用してみてくださいね。