ドロップダウンリストを作成する

ドロップダウンリストを設定すると、セルに小さな矢印が表示され、許可された選択肢のメニューを開けるようになります。ユーザーは入力する代わりに選択できます。

これは最も使いやすいデータの入力規則です。

• 選択肢があらかじめ承認されているため、入力ミスがありません。
• 列全体で表記を統一できます。
• ステータスや地域など、繰り返し入力する値をすばやく入力できます。

仕組みとしては、ドロップダウンはリスト型の入力規則にすぎません。

最も簡単なドロップダウンは、ルールに値を直接入力する方法です。Excelのデータの入力規則ダイアログで入力値の種類：リストを選択し、元の値ボックスに次のように入力します。

Yes,No,Maybe

項目はコンマで区切ります。Google Sheetsでは、プルダウンを選択して各選択肢を入力します。

• 項目数が少なく、ほとんど変更しないリストに最適です。
• 欠点は、リストを編集するたびにルールを開き直す必要があることです。

項目数が多いリストや変更されるリストでは、セル範囲を元の値として指定します。選択肢を列に入力します。たとえばF2:F6に入力し、入力規則の元の値を次のように設定します。

=$F$2:$F$6

これで、F列のセルを編集すると、ドロップダウンがすぐに更新されます。

• 元の範囲が変わらないように、絶対参照を使用します。
• 必要であれば、選択肢のセルを整理された検索用のシートに配置します。

ここで、名前付き範囲と入力規則を組み合わせます。選択肢の範囲にRegionListという名前を付け、入力規則の元の値を次のように設定します。

=RegionList

これでドロップダウンの設定がわかりやすくなり、保守も簡単になります。ルールを確認する人は、わかりにくいセル番地ではなく、意味のある名前を確認できます。

• ExcelとGoogle Sheetsで同じように機能します。
• 名前とドロップダウンの内容を同期できます。

タスク管理表を考えてみましょう。補助シートのA1:A4にステータスとして「未着手」、「進行中」、「ブロック中」、「完了」を入力し、その範囲にStatusListという名前を付けます。

ステータス列を選択し、データの入力規則を開いてリストを選択し、元の値を=StatusListに設定します。

これで、すべてのステータスセルに同じ4つの整った選択肢が表示されます。各ステータスを数えるレポートやCOUNTIF数式で、スペルミスのある入力が数え落とされることもありません。

元のリストに新しい選択肢を追加しても、=$F$2:$F$6のような固定範囲には含まれません。次の2つの方法で、ドロップダウンを拡張できるようにします。

• 元の範囲をExcelのテーブルに変換して列に名前を付けます。テーブルは自動的に拡張されます。
• または、新しいExcelで=A2#のようなスピル関数を使い、動的な名前付き範囲を定義します。

Google Sheetsでは、F2:Fのように列全体を元の範囲として指定すると、今後の追加分も取得できます。

連動するドロップダウンでは、別のセルの値に応じて選択肢が変わります。あるセルで国を選択すると、都市のドロップダウンにはその国の都市だけが表示されます。

Excelで使われる古典的な方法では、カテゴリごとにサブリストへカテゴリと同じ名前を付け、元の値にINDIRECTを使用します。

=INDIRECT(A2)

A2にFranceが入り、Franceという名前付き範囲にその国の都市が含まれていれば、ドロップダウンが連動します。高度な方法ですが、強力な機能です。

デフォルトでは、リストルールを設定してもユーザーは値を手入力できます。これを制御する方法は次のとおりです。

• Excelでは、エラーアラートを停止に設定すると、リストにない値を拒否します。
• 警告を許可すると、ユーザーはドロップダウンの選択肢を上書きできます。
• Google Sheetsでは、入力を拒否を選択すると、リストを厳密に適用できます。

正確なレポートを作成するには、リストにある値だけを入力できる厳格なオプションを選択してください。

小さな矢印は、セルが選択されているときだけ表示されます。また、Excelでセル内ドロップダウンにチェックが入っているか、Sheetsでドロップダウンのスタイルがオンになっている必要があります。

• 矢印が表示されない場合は、ルールを開き直してセル内ドロップダウンのオプションを有効にします。
• Sheetsでは、矢印チップと通常の入力規則のどちらかを選択できます。

この設定は表示方法だけを制御するため、どちらを選んでも許可される値のルール自体は変わりません。

ドロップダウンは=RegionListによって制御されるため、保守は簡単です。

• 名前付き範囲のセルで地域を追加または削除します。
• 範囲のサイズが変わった場合は、名前の管理で名前付き範囲を更新します。テーブルを使えば、この作業を避けられます。
• 後からリストを編集しても、既存のセルの値はそのまま保持されます。

1つの名前付きの元データを参照するすべてのドロップダウンに内容が反映されるため、1か所を変更するだけで全体を更新できます。

ドロップダウンは、検索数式と組み合わせるとさらに便利になります。A2のドロップダウンから地域を選択し、その売上を検索します。

=XLOOKUP(A2,RegionList,SalesList)

ドロップダウンによってA2には常に有効な地域が入るため、入力ミスが原因で検索に失敗することはありません。

• ドロップダウンが入力を制御します。
• 検索が選択内容に応じて動作します。

この組み合わせが、数式で動作する対話型レポートの中心です。

選択肢用にRegionListという名前付き範囲を作成しました。この範囲からドロップダウンを作成するには、データの入力規則のリストの元の値を何に設定すればよいでしょうか。

データをクリーンに保つドロップダウンリストの作成方法を学びました。

• 入力項目を直接指定する方法、セル範囲を指定する方法、または名前付き範囲を使う方法で、リストの入力規則を設定します。
• =RegionListでドロップダウンを作成すると、わかりやすく保守も簡単になります。
• テーブルや列全体の範囲を使って、リストを自動的に拡張します。
• 停止のエラーアラートを使って、リストにある値だけを入力できるようにします。

これで「名前付き範囲とデータの入力規則」は完了です。わかりやすい数式と、制御された信頼性の高い入力を実現できます。