大項目と連動して小項目の選択肢を絞り込める
Googleスプレッドシートの表において、一連のセルで入力する内容が決まっている場合、「プルダウンリスト」（ドロップダウンリスト）から項目を選択できると便利です。
プルダウンリストはWebサイトの入力フォームなどでもよく見かけるもので、書式の統一や誤入力の防止にも効果があります。単一のプルダウンリストの作成方法は、本記事末尾の関連記事を参照してください。
しかし、スクロールが必要になるほど選択肢が多すぎるリストは、使い勝手がよくありません。以下のように、社員名がズラリと並んでいるようなリストが典型例でしょう。
項目を探すためにスクロールしなければならないようなリストは、入力効率がむしろ低くなってしまいます。
このような場合は、選択した部署名に連動して、その部署に所属する社員名が絞り込まれるリストになっていたほうが使いやすいでしょう。つまり、大項目と連動して小項目の選択肢が絞り込まれるプルダウンリストを作成する、ということです。
連動するプルダウンリストの作成方法はいくつかありますが、本記事ではセル参照とXLOOKUP関数を利用したテクニックを、以下の4つのステップに分けて紹介します。
大項目のリストを作成する
大項目のプルダウンリストの作成は簡単です。ここでは［担当者］シートのB列に大項目のリスト、C列に小項目のリストを追加していきます。［所属部署］シートには、部署名と所属する社員名の一覧が入力されています。
［担当者］シートのB列に大項目のリスト、C列に大項目と連動する小項目のリストを作成します。
［所属部署］シートには部署と所属する人の一覧が入力されています。
1大項目のリストを作成する
大項目のリストを作成するセル範囲を選択します。
［データ］メニューから［データの入力規則］を選択します。
2参照するセル範囲を選択する
［データの入力規則］ダイアログボックスが表示されました。［リストを範囲で指定］が選択されていることを確認して［データ範囲を選択］をクリックします。
［データ範囲の選択］ダイアログボックスに切り替わります。参照するシート（ここでは［所属部署］シート）に切り替えて、セル範囲をドラッグして［OK］をクリックします。
参照するセル範囲が選択され、［データの入力規則］ダイアログボックスに切り替わります。［セルにプルダウンリストを表示］にチェックが付いていることを確認して、［保存］をクリックします。なお、［無効なデータの場合］で［入力を拒否］を選択すると、リスト以外のデータを入力を禁止できます。
3リストの内容を確認する
セルに［▼］が表示され、プルダウンリストを選択できる状態になりました。
セルの［▼］をクリックすると項目が表示されます。
大項目の内容を参照する
大項目と連動する小項目のプルダウンリストは、少し工夫が必要です。大項目で選択された内容に応じて小項目のリストを切り替えるのですから、固定の選択肢を参照するのはNGです。
B列の［部署］に応じて、C列の［担当者］を切り替えるにはどうすればいいでしょうか？
小項目のリストが参照する表が必要になるので、［担当者］シートの大項目の内容と連動するセルを［所属部署］シートに用意しましょう。
といっても、任意のセルに「='担当者'!B2」と入力するだけです。「'担当者'!」は［担当者］シートを意味します。直接入力しても構いませんが、「=」と入力後に［担当者］シートに切り替えてセルB2をクリックするのが簡単です。
4大項目の内容を参照する
［所属部署］シートの任意のセルに「=」と入力します。
［担当者］シートに切り替えて、セルB2をクリックします。「='担当者'!B2」と入力されたことを確認して［Enter］キーを押します。
［所属部署］シートに切り替わります。［担当者］シートのセルB2の内容が表示されます。数式バーを確認すると「='担当者'!B2」と入力されていることが分かります。
5数式をコピーする
「='担当者'!B2」の数式をコピーしておきます。［担当者］シートのB列の内容が表示されます。
XLOOKUP関数で選択肢の表を参照する
［担当者］シートの大項目の内容を参照できたので、この値を検索キーとして、XLOOKUP（エックスルックアップ）関数で選択肢の表を参照します。
XLOOKUP関数はVLOOKUP関数を強化した関数で、表の中から検索キーに対応する値を取り出すことが可能です。VLOOKUP関数を利用しても構いませんが、値を一気に取り出せるXLOOKUP関数のほうが便利です。
本記事では詳しい解説は割愛しますが、［検索キー］［検索範囲］［結果の範囲］の3つの引数を指定すれば動作します。XLOOKUP関数の構文と引数は以下の通りです。
=XLOOKUP（検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード）
- ［検索キー］......どのデータで
- ［検索範囲］......どこを検索して
- ［結果の範囲］......どの範囲の値を取り出すか
- ［見つからない場合の値］......検索キーが見つからない場合、何を表示するか（省略可）
- ［一致モード］......完全一致か、近似値も検索するか（初期値：完全一致、省略可）
- ［検索モード］......どのような順序で検索するか（初期値：先頭から末尾、省略可）
今回の目的において、入力する数式は以下の通りです。
=XLOOKUP(A6,$A$1:$A$3,$B$1:$K$3,"")
セルA6の値を［検索キー］として、［検索範囲］（セルA1～A3）を検索し、対応する値を［結果の範囲］（セルB1～K3）から取り出します。入力する数式は縦方向にコピーするため、［検索範囲］と［結果の範囲］のセル範囲は絶対参照にしています。
4つめの引数［見つからない場合の値］は、大項目にリスト以外データが入力された場合に空白（""）を表示するように指定しています。省略しても構いません。
6XLOOKUP関数を入力する
セルB7に「=XLOOKUP(A6,$A$1:$A$3,$B$1:$K$3,"")」と入力します。
7対応する値が表示された
セルB7～K7に［検索キー］（セルA6の値）に対応する値が表示されました。
8数式をコピーする
セルB7の数式をコピーしておきます。
これで［担当者］シートの大項目のリストに応じて、［所属部署］シートの表が切り替わるようになりました。
連動する小項目のリストを作成する
小項目として参照する選択肢の表を用意できたので、大項目と連動する小項目のリストを作成します。ここでは意図しない動作を避けるため、1つのセルにデータの入力規則を設定してからコピーします。
9［データ範囲の選択］ダイアログボックスを表示する
小項目のリストを作成するセルを1つだけ選択します（ここではセルC2）。［データ］メニューから［データの入力規則］を選択し、［データの入力規則］ダイアログボックスを表示します。さらに［データ範囲の選択］をクリックします。
10参照するセル範囲を選択する
［データ範囲の選択］ダイアログボックスに切り替わります。参照するシート（ここでは［所属部署］シート）に切り替えて、セル範囲をドラッグして［OK］をクリックします。
参照するセル範囲が選択され、［データの入力規則］ダイアログボックスに切り替わります。［セルにプルダウンリストを表示］にチェックが付いていることを確認して、［保存］をクリックします。
11［データの入力規則］を修正する
ここがポイントです。先ほどデータの入力規則を設定したセルC2を選択して［データの入力規則］ダイアログボックスを表示してください。参照するセル範囲に「='所属部署'!$B$6:$K$6」のように設定されています。このままセルC2をコピーしても、ほかのセルでは正しく動作しません。
絶対参照を意味する「$」をすべて削除します。この例での修正結果は「='所属部署'!B6:K6」となります。最後に［保存］をクリックします。
12セルをコピーする
セルC2をコピーします。
13コピーしたセルの［データの入力規則］を確認する
コピーした任意のセルを選択して［データの入力規則］ダイアログボックスを表示します。「='所属部署'!B9:K9」のように、相対参照になっていることを確認します。［キャンセル］をクリックしてダイアログボックスを閉じておきます。
14連動するリストを確認する
小項目のプルダウンリストが大項目に連動するようになりました。
連動するプルダウンリストでは、大項目のリストの内容に応じて切り替わる、小項目のリストが参照する選択肢の表を準備する作業がキモです。コツさえ覚えてしまえば、大項目・中項目・小項目といった3階層の連動にも応用可能です。機会があれば、ぜひチャレンジしてみてください。
関連記事
Googleスプレッドシートでプルダウンリストを作成する方法。選択式のメニューでミスを防げる！
選択式のリストである「プルダウンリスト」（ドロップダウンリスト）はExcelでもよく使われますが、これをGoogleスプレッドシートで作成してみましょう。入力を簡単にできるだけでなく、書式の統一や誤入力の防止にも役立ちます。
