Excelでの検索可能なドロップダウンリストの究極ガイド

Excelでドロップダウンリストを作成すると、データ入力が簡素化され、エラーも最小限に抑えられます。しかし、データセットが大きくなると、長いリストをスクロールするのは煩雑です。タイプして項目をすぐに見つけられると便利ではありませんか？「検索可能なドロップダウンリスト」はそのような利便性を提供します。このガイドでは、Excelでそのようなリストを設定するための4つの方法をご紹介します。

• Excel 365での検索可能なドロップダウンリスト
• 検索可能なドロップダウンリストの作成（Excel 2019以降向け）
• すべてのExcelバージョンで簡単に検索可能なドロップダウンリストを作成
• コンボボックスとVBAを使用して検索可能なドロップダウンリストを作成（より複雑）

ビデオ：検索可能なドロップダウンリストの作成

Excel 365での検索可能なドロップダウンリスト

Excel 365には、データ検証のドロップダウンリストに待望の機能が追加されました。それはリスト内を検索できる機能です。この検索機能により、ユーザーは効率的に項目を見つけて選択できます。通常通りドロップダウンリストを挿入した後、リストのあるセルをクリックして入力を開始するだけです。リストは入力されたテキストに即座にフィルタリングされます。

この場合、Sanという文字をセルに入力すると、ドロップダウンリストはSan FranciscoやSan Diegoなど、Sanという検索語で始まる都市のみを表示します。その後、マウスで結果を選択するか、矢印キーを使用してEnterキーを押すことができます。

検索可能なドロップダウンリストの作成（Excel 2019以降向け）

Excel 2019以降のバージョンを使用している場合、このセクションの方法でもExcelでドロップダウンリストを検索可能にすることができます。

Sheet1の範囲A2:A8のデータを使用して、Sheet2のセルA2にドロップダウンリストを作成済みであると仮定します（右側の画像）。以下の手順に従って、リストを検索可能にします。

ステップ1. 検索項目をリストする補助列を作成する

ここでは、ソースデータに一致する項目をリストする補助列が必要です。この場合、補助列をSheet1のD列に作成します。

1. D列の最初のセルD1を選択し、列ヘッダーとして「検索結果」などの名前を入力します。
2. 次の数式をセルD2に入力し、Enterキーを押します。 Enter.

   =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")

   

ステップ2: ドロップダウンリストを再構成する

1. ドロップダウンリストのセル（この場合はSheet2のセルA2）を選択し、次に「データ」＞「データの検証」＞「データの検証」を選択します。
   
2. 「 データの検証 」ダイアログボックスで、以下のように設定します。
   1. 「 設定 」タブで、「 」ボタンをクリックします。「 リンク元 」ボックスで。
      
   2. 「データの検証」ダイアログボックスはSheet1にリダイレクトされ、Step 1の数式があるセル（例：D2）を選択し、#記号を追加して「閉じる 」ボタンをクリックします。
      
   3. 「エラーメッセージ」タブに移動し、「無効なデータが入力された後にエラーメッセージを表示する」チェックボックスをオフにして、最後に「OK」ボタンをクリックして変更を保存します。
      

結果

Sheet2のセルA2のドロップダウンリストは、これで検索可能になりました。セルにテキストを入力し、ドロップダウン矢印をクリックしてリストを展開すると、リストが入力されたテキストに即座にフィルタリングされます。

すべてのExcelバージョンで簡単に検索可能なドロップダウンリストを作成

上記の方法にはさまざまな制限がありますが、ここで非常に効果的なツールを紹介します - Kutools for Excelの「ドロップダウンリストを検索可能にし、自動ポップアップ」機能です。この機能はすべてのExcelバージョンで利用でき、簡単な設定でドロップダウンリスト内で目的の項目を簡単に検索できます。

Kutools for Excelをダウンロードしてインストールした後、「Kutools」＞「ドロップダウンリスト」＞「ドロップダウンリストを検索可能にし、自動ポップアップ」を選択してこの機能を有効にします。「ドロップダウンリストを検索可能にする」ダイアログボックスで、次の操作を行います。

1. 検索可能なドロップダウンリストに設定する必要があるドロップダウンリストを含む範囲を選択します。
2. 「 OK 」をクリックして設定を完了します。
   

結果

指定された範囲内のドロップダウンリストのセルをクリックすると、右側にリストボックスが表示されます。テキストを入力してリストを即座にフィルタリングし、項目を選択するか、矢印キーを使用してEnterキーを押してセルに追加します。

コンボボックスとVBAを使用して検索可能なドロップダウンリストを作成（より複雑）

特定のドロップダウンリストの種類を指定せずに、単に検索可能なドロップダウンリストを作成したい場合、このセクションでは別の方法を提供します。コンボボックスとVBAコードを使用してタスクを実現します。

下のスクリーンショットに示すように、A列に国名のリストがあり、それらを検索可能なドロップダウンリストのソースデータとして使用したいとします。以下の手順に従って実行できます。

ワークシートにデータ検証のドロップダウンリストではなく、コンボボックスを挿入する必要があります。

1. もし「 開発 」タブがリボンに表示されていない場合、「 開発 」タブを以下のように有効にできます。
   1. Excel 2010以降のバージョンでは、「ファイル」＞「オプション」をクリックします。「Excelのオプション」ダイアログボックスで、左ペインの「リボンのユーザー設定」をクリックします。「リボンのユーザー設定」リストボックスで、「開発」ボックスをチェックし、「OK」ボタンをクリックします。スクリーンショットをご覧ください：
      
   2. Excel 2007では、「Office」ボタン＞「Excelのオプション」をクリックします。 「Excelのオプション」ダイアログボックスで、左ペインの「全般」をクリックし、「リボンに開発タブを表示する」ボックスをチェックし、最後に「OK」ボタンをクリックします。
      
2. 「開発」タブが表示されたら、「開発」＞「挿入」＞「コンボボックス」をクリックします。
   
3. ワークシートにコンボボックスを描画し、右クリックして右クリックメニューから「プロパティ」を選択します。
   
4. 「 プロパティ 」ダイアログボックスで、次の操作を行う必要があります。
   1. AutoWordSelectフィールドで「False」を選択します；
   2. LinkedCellフィールドでセルを指定します。この場合、A12を入力します；
   3. MatchEntryフィールドで「2-fmMatchEntryNone」を選択します；
   4. ListFillRangeフィールドに「DropDownList」と入力します；
   5. 「プロパティ」ダイアログボックスを閉じます。スクリーンショットをご覧ください：
      
5. 「開発」＞「デザインモード」をクリックしてデザインモードをオフにします。
6. 空白のセル（例：C2）を選択し、以下の数式を入力してEnterキーを押します。 Enterその後、オートフィルハンドルをC9までドラッグして、同じ数式でセルを自動入力します。スクリーンショットをご覧ください：

   =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

   
   注意：

   1. $A$12は、ステップ4でLinkedCellとして指定したセルです；
   2. 上記の手順を終了すると、テストが可能です：コンボボックスに文字「C」を入力すると、文字「C」を含むセルを参照する数式セルが数字1で埋められていることが確認できます。
7. セルD2を選択し、以下の数式を入力してEnterキーを押します。 Enterその後、オートフィルハンドルをD9までドラッグします。

   =IF(C2=1,COUNTIF($C$2:C2,1),"")

   
8. セルE2を選択し、以下の数式を入力してEnterキーを押します。 Enterその後、オートフィルハンドルをE9までドラッグして同じ数式を適用します。

   =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")

   
9. 次に名前範囲を作成する必要があります。「数式」＞「名前の定義」をクリックします。
   
10. 「 新しい名前 」ダイアログボックスで、「 DropDownList 」を「 名前 」ボックスに入力し、以下の数式を「 参照先 」ボックスに入力して、「 OK 」ボタンをクリックします。

    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    

    
11. 次に、「開発」＞「デザインモード」をクリックしてデザインモードをオンにします。その後、コンボボックスをダブルクリックしてMicrosoft Visual Basic for Applicationsウィンドウを開きます。
12. 以下のVBAコードをコピーしてコードエディターに貼り付けます。
    
    VBAコード：ドロップダウンリストを検索可能にする

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. Alt + Qキーを押してMicrosoft Visual Basic for Applicationsウィンドウを閉じます。

これで、コンボボックスに文字を入力すると、あいまい検索を行い、関連する値をリストに表示します。

関連記事：