Excelでスペースを含む連動ドロップダウンリストを作成する2つの簡単な方法

連動ドロップダウンリストは、Excelユーザーの間でよく使われる機能です。しかし、単語の間にスペースが含まれる名前で連動ドロップダウンリストを作成する場合、そのままではエラーが発生してしまうことがあります。

例えば、3つの商品リストがあり、それぞれの名前が2語で構成されているため、単語の間にスペースが含まれているケースを考えてみましょう。このような場合でも、スペースを含む名前で連動ドロップダウンリストを作成できるようにする必要があります。INDEX関数、MATCH関数、INDIRECT関数を組み合わせた数式を使えば、参照先の名前にスペースがあっても問題なくドロップダウンリストを作成できます。

この記事では、スペースを含む連動ドロップダウンリストをExcelで作成する2つの簡単な方法をご紹介します。

ワークブックのダウンロード

スペースを含む連動ドロップダウンリストを作る2つの方法

Excelの「データ」タブにある「データツール」グループには、「データの入力規則」という機能が用意されています。この機能を使えば、誰でも簡単にドロップダウンリストを挿入できます。ただし、連動ドロップダウンリストを作成する際に、連動側の項目名にスペースが含まれていると、エラーが発生してしまいます。

この問題を解決するために、以下の2つの方法を使用します。

方法1:INDEX関数とMATCH関数を組み合わせて作成する

INDEX関数とMATCH関数を組み合わせた数式を使えば、連動項目名やタイトル内のスペースを気にせずドロップダウンリストを作成できます。

まず、INDEX関数の書式は以下の通りです。

=INDEX(配列, 行番号, [列番号], [領域番号])

• 配列:セル範囲または配列を指定します。
• 行番号:配列内の行位置を指定します。
• 列番号:配列内の列位置を指定します。[省略可能]
• 領域番号:使用する範囲を指定します。[省略可能]

続いて、MATCH関数の書式は以下の通りです。

=MATCH(検索値, 検索範囲, [照合の種類])

• 検索値:検索範囲の中から探したい値を指定します。
• 検索範囲:検索値と照合するための配列またはセル参照を指定します。
• 照合の種類:既定値は完全一致または次に小さい値=1、完全一致=0、完全一致または次に大きい値=-1です。[省略可能]

🔁 ドロップダウンリストの作成

ステップ1:任意の空白セル(ここではF4)にカーソルを置き、「データ」>「データの入力規則」(データツールグループ)を選択します。

ステップ2:「データの入力規則」ダイアログボックスが開きます。「設定」タブで以下のように設定します。

• 入力値の種類で「リスト」を選択します。
• B4:D4を元の値として指定します。
• 「OK」をクリックします。

➤ ワークシートに戻り、セルF4の下矢印アイコンをクリックすると、「元の値」として指定したすべてのセルがドロップダウンリストとして表示されます。

🔁 連動ドロップダウンリストの作成

ステップ3:セルF5に対しても、ステップ1〜2と同様の手順を実行します。「データの入力規則」ウィンドウの「元の値」欄に、以下の数式を入力します。

=INDEX(B5:D13,,MATCH($F$4,$B$4:$D$4,0))

先ほど説明した書式と照らし合わせると、数式内のMATCH($F$4,$B$4:$D$4,0)の部分がINDEX関数に列番号を渡しています。そしてB5:D13がINDEX関数の配列に相当し、行番号は指定していません。

MATCH関数の部分では、F4が検索値、B4:D4が検索範囲、0は完全一致を意味します。

「OK」をクリックします。

➤ 「OK」をクリックするとワークシートに戻ります。セルF5の下矢印アイコンをクリックしてみると、スペースが含まれていても問題なく連動ドロップダウンリストが表示されることが確認できます。

関連記事:Excelで数式を使ってドロップダウンリストを作成する方法(4選)

あわせて読みたい記事

• Excelでの条件付きドロップダウンリスト(作成・並べ替え・使い方)
• Excelでセルの値とドロップダウンリストを連携させる方法(5選)
• 選択内容に応じて変わるExcelドロップダウンリスト
• Excelで検索可能なドロップダウンリストを作成する(2つの方法)
• Excelで色付きのドロップダウンリストを作成する方法(2選)

方法2:名前の定義とINDIRECT関数を使用する

INDIRECT関数は、連動項目名や参照名にスペースが含まれていると、そのままではドロップダウンリストを作成できません。そこで、SUBSTITUTE関数を使って数式を少し工夫することで、INDIRECT関数でもスペースを含む名前を扱えるようになります。

INDIRECT関数の書式は以下の通りです。

=INDIRECT(参照文字列, [a1])

• 参照文字列:文字列形式の参照を指定します。
• a1:A1形式かどうかを示す論理値です。既定値はTRUE(A1形式)です。[省略可能]

🔁 名前の定義

ステップ1:列見出し(ここではB4:D4)を選択し、「数式」>「名前の定義」(定義された名前グループ)を選択します。

ステップ2:「新しい名前」ダイアログボックスが表示されるので、以下のように設定します。

• セル範囲にわかりやすい名前を付けます(例:List)。
• 「参照範囲」ボックスには、Excelが自動的にセル参照を入力してくれます。
• 「OK」をクリックします。

ステップ3:この方法のステップ1〜2を繰り返して、各リストごとに名前と範囲を割り当てます。

➤ 割り当てた名前の一覧は、「数式」>「ネームマネージャー」(定義された名前グループ)から確認できます。

🔁 ドロップダウンリストの作成

ステップ4:セルF4にカーソルを置き、「データ」>「データの入力規則」(データツールグループ)を選択します。

ステップ5:「データの入力規則」ダイアログボックスが表示されたら、以下のように設定します。

• 入力値の種類で「リスト」を選択します。
• 「元の値」欄に=Listと入力します(Listは列見出しに付けた定義名です)。
• 「OK」をクリックします。

➤ セルF4の下矢印アイコンをクリックすると、下図のように列見出しがドロップダウンリストとして表示されます。

🔁 連動ドロップダウンリストの作成

ステップ6:ステップ4〜5と同じ手順でデータの入力規則ウィンドウを開き、「元の値」欄に以下の数式を貼り付けます。

=INDIRECT(SUBSTITUTE($F$4," ","_"))

この数式では、SUBSTITUTE関数がF4の参照値に含まれるスペースをアンダースコア(_)に置き換えています。置換後の文字は、定義名で使っている文字であれば他の記号でも構いません。こうすることで、INDIRECT関数はF4の入力値を定義名と同じ形式に変換できます。そしてF4の値に応じて、対応する商品リストが表示される仕組みです。

➤ 「OK」をクリックすると、下図のような連動ドロップダウンリストが表示されます。

最初のドロップダウンリストで連動項目を変更すれば、2つ目のドロップダウンリストの内容も自動的に切り替わります。

関連記事:Excelでドロップダウンリストを作成する方法(独立型・連動型)

まとめ

この記事では、スペースを含む連動ドロップダウンリストをExcelに挿入するための数式をご紹介しました。INDEX関数とMATCH関数を組み合わせた数式なら、標準のままでも連動項目名に含まれるスペースを扱えます。一方、INDIRECT関数を使う場合は、SUBSTITUTE関数で数式を工夫する必要があります。ぜひ目的に合った方法をお試しください。ご不明な点や追加情報などがあれば、コメントでお知らせください。

関連記事

• ExcelでVLOOKUPとドロップダウンリストを組み合わせる方法
• Excelのドロップダウンリストを編集する方法(基本の4つ)
• 別シートからドロップダウンリストを作成する方法(2選)
• Excelのドロップダウンリストを削除する方法
• IF関数を使ってExcelにドロップダウンリストを作成する方法
• Excelのドロップダウンリストが動かないときの対処法(8つの原因と解決策)