メインコンテンツへスキップ

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

Excel でドロップダウン リストを作成すると、データ入力が効率化され、エラーが最小限に抑えられます。 しかし、データセットが大きくなると、長いリストをスクロールするのが面倒になります。 入力するだけでアイテムをすぐに見つけられる方が簡単だと思いませんか? ”検索可能なドロップダウン リストこのガイドでは、Excel でそのようなリストを設定する XNUMX つの方法について説明します。


ビデオ


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

Excel 365 では、データ検証ドロップダウン リストに待望の機能、つまりリスト内を検索する機能が導入されました。 検索機能を使用すると、ユーザーはより効率的な方法でアイテムをすばやく見つけて選択できます。 通常どおりドロップダウン リストを挿入した後、ドロップダウン リストのあるセルをクリックして入力を開始します。 リストは、入力されたテキストに一致するように即座にフィルタリングされます。

この場合、次のように入力します サン セルとドロップダウン リストに入力すると、検索語で始まる都市が除外されます。 サン、 といった サンフランシスコ > サンディエゴ。 次に、マウスで結果を選択するか、矢印キーを使用して Enter を押します。

ノート:
  • また, 検索は各単語の最初の文字から開始されます ドロップダウンリストにある。 単語の先頭文字と一致しない文字を入力すると、一致する項目はリストに表示されません。
  • この機能は、最新バージョンの Excel 365 でのみ使用できます。
  • お使いの Excel のバージョンがこの機能をサポートしていない場合は、次の方法をお勧めします。 検索可能なドロップダウンリスト の特徴 Kutools for Excel。 Excel のバージョン制限はなく、有効にすると、関連するテキストを入力するだけで、ドロップダウン リストで目的の項目を簡単に検索できます。 詳細な手順を表示する.

検索可能なドロップダウン リストを作成する (Excel 2019 以降の場合)

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

Sheet2 の範囲 A2:A2 (左側の画像) のデータを使用して Sheet8 のセル A1 (右側の画像) にドロップダウン リストを作成したと仮定し、次の手順に従ってリストを検索可能にします。

ステップ 1. 検索項目をリストするヘルパー列を作成する

ここでは、ソース データに一致する項目をリストするヘルパー列が必要です。 この場合、ヘルパー列を次のように作成します。 D列 of Sheet1.

  1. 最初のセルを選択します D1 列 D に「」などの列ヘッダーを入力します。の検索結果" この場合。
  2. セル D2 に次の数式を入力し、 を押します。 入力します.
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
ノート:
  • この式では、 A2:A8 はソースデータ範囲です。 Sheet2!A2 はドロップダウン リストの場所です。これは、ドロップダウン リストが Sheet2 の A2 にあることを意味します。 ご自身のデータに合わせて変更してください。
  • Sheet2 の A2 のドロップダウン リストから項目が選択されていない場合、上の図に示すように、数式にはソース データのすべての項目が表示されます。 逆に、項目が選択されている場合、D2 はその項目を数式の結果として表示します。
ステップ 2: ドロップダウン リストを再構成する
  1. ドロップダウン リストのセルを選択し (この場合は Sheet2 のセル A2 を選択します)、選択に進みます。 且つ > データ検証 > データ検証.
  2. データ検証 ダイアログボックスでは、次のように設定する必要があります。
    1. 設定 タブをクリックします。 ボタンの ソース ボックス。
    2. また, データ検証 ダイアログ ボックスは Sheet1 にリダイレクトされ、ステップ 2 の数式が含まれるセル (D1 など) を選択し、 # 記号をクリックし、 閉じる
    3. に行きます エラーアラート タブをクリックし、 無効なデータが入力された後にエラーアラートを表示する チェックボックスをオンにし、最後に OK ボタンをクリックして変更を保存します。
Result

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

ノート:
  • この方法は、Excel 2019 以降のバージョンでのみ使用できます。
  • このメソッドは、一度に 3 つのドロップダウン リスト セルに対してのみ機能します。 Sheet8 のセル A2 ~ AXNUMX でドロップダウン リストを検索できるようにするには、セルごとに前述の手順を繰り返す必要があります。
  • ドロップダウン リストのセルにテキストを入力しても、ドロップダウン リストは自動的に展開されません。ドロップダウン矢印をクリックして手動で展開する必要があります。

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

上記の方法にはさまざまな制限があるため、非常に効果的なツールを次に示します。 Kutools for Excel's 検索可能なドロップダウンリスト 特徴。 この機能は Excel のすべてのバージョンで利用でき、指定された範囲内のすべてのドロップダウン リストだけでなく、現在のワークシートまたは現在のブック内のすべてのドロップダウン リストでも機能するため、目的の項目を簡単に検索できます。ドロップダウン リスト。

後の Kutools for Excelのダウンロードとインストール選択 クツール > ドロップダウンリスト > 検索可能なドロップダウンリスト この機能を有効にするには、 の中に 検索可能なドロップダウンリスト ダイアログボックスでは、次のことを行う必要があります。

  1. 必要なオプションを指定します スコープを設定 ドロップダウンリスト。
    この場合、指定した範囲のドロップダウン リストを検索可能にする必要があるため、 範囲指定 オプションを選択して範囲を選択します。
  2. OK.
Result

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

ノート:
  • この機能には、次の XNUMX つの便利なオプションが用意されています。
    • 範囲指定: このオプションを選択してセル範囲を指定すると、この指定した範囲内のドロップダウン リストのみが検索可能になります。
    • 現在のワークブック: このオプションを選択すると、現在のワークブック内のすべてのドロップダウン リストが検索可能になります。
    • 現在のワークシート: このオプションを選択すると、現在のワークシート内のドロップダウン リストのみが検索可能になります。
  • この機能はサポートします 単語内の任意の位置から検索。 つまり、単語の途中または末尾にある文字を入力した場合でも、一致する項目が検索されて表示され、より包括的でユーザーフレンドリーな検索エクスペリエンスが提供されます。
  • この機能について詳しく知りたい場合は、 このページをご覧ください.
  • この機能を適用するには、 Kutools for Excelをダウンロードしてインストールします 最初。

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

特定のドロップダウン リスト タイプを指定せずに、単に検索可能なドロップダウン リストを作成したい場合。 このセクションでは、別のアプローチとして、VBA コードを含むコンボ ボックスを使用してタスクを実行する方法について説明します。

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

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

  1. Status Developer タブがリボンに表示されない場合は、有効にすることができます Developer 次のようにタブ。
    1. Excel 2010 以降のバージョンでは、 File > オプション。 とで Excelオプション ダイアログボックスで、をクリックします。 リボンをカスタマイズする 左ペインにあります。 [リボンのカスタマイズ]リストボックスに移動し、[ Developer ボックスをクリックし、 OK ボタン。 スクリーンショットを参照してください:
    2. Excel 2007で、をクリックします Office ボタン> Excelのオプション。 の中に Excelのオプション ダイアログボックスで、をクリックします。 人気 左側のペインで、 リボンに[開発者]タブを表示する ボックスをクリックし、最後に OK
  2. を見せた後、 Developer タブをクリックします。 Developer > インセット > コンボボックス.
  3. ワークシートにコンボ ボックスを描画し、右クリックして選択します。 プロパティ 右クリックメニューから。
  4. プロパティ ダイアログボックスでは、次のことを行う必要があります。
    1. 選択 × セクションに オートワードセレクト フィールド;
    2. でセルを指定します。 リンクされたセル フィールド。 この場合、A12と入力します。
    3. 選択 2-fmMatchEntryNone セクションに マッチエントリー フィールド;
    4. ドロップダウンリストリストフィル範囲 フィールド;
    5. それを閉めて プロパティ ダイアログボックス。 スクリーンショットを参照してください:
  5. 次に、 をクリックしてデザインモードをオフにします。 Developer > デザインモード.
  6. C2 などの空白セルを選択し、以下の数式を入力して を押します。 入力します。 オートフィルハンドルをセルC9までドラッグして、同じ数式でセルを自動入力します。 スクリーンショットを参照してください:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    ノート:
    1. $ A $ 12 として指定したセルです リンクされたセル ステップ4で;
    2. 上記の手順を完了したら、コンボ ボックスに文字 C を入力すると、文字 C を含むセルを参照する数式セルに数値 1 が入力されることを確認できます。
  7. セルD2を選択し、以下の数式を入力して押します。 入力します。 次に、オートフィルハンドルをセルD9までドラッグします。
    =IF(C2=1,COUNTIF($C$2:C2,1),"")
  8. セルE2を選択し、以下の数式を入力して押します。 入力します。 次に、オートフィルハンドルをE9までドラッグして、同じ数式を適用します。
    =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
  9. 次に、名前範囲を作成する必要があります。 クリックしてください > 名前を定義する.
  10. 新しい名前 ダイアログボックス、タイプ ドロップダウンリスト セクションに お名前 ボックスに、以下の式を入力します を参照する ボックスをクリックし、 OK
    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    
  11. 次に、 をクリックしてデザインモードをオンにします。 Developer > デザインモード。 次に、コンボボックスをダブルクリックして、 アプリケーション向け Microsoft Visual Basic 窓。
  12. 以下の VBA コードをコピーしてコード エディターに貼り付けます。
    VBAコード:ドロップダウンリストを検索可能にする
    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub
  13. 他の + Q を閉じるためのキー アプリケーション向け Microsoft Visual Basic 窓。

これ以降、コンボボックスに文字が入力されると、あいまい検索が実行され、関連する値がリストに一覧表示されます。

Note:将来使用するためにVBAコードを保持するには、このブックをExcelマクロ対応ブックファイルとして保存する必要があります。

最高のオフィス生産性ツール

Kutools forExcel-群衆から目立つのに役立ちます

人気の機能: 重複を検索、強調表示、または識別する  |  空白行を削除する  |  データを失わずに列またはセルを結合する  |  数式なしのラウンド ...
スーパーVルックアップ: 複数の基準  |  複数の値  |  複数のシートにわたって  |  ファジールックアップ...
上級ドロップダウンリスト: 簡単なドロップダウンリスト  |  依存関係のドロップダウン リスト  |  複数選択のドロップダウンリスト...
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  列を比較する 同じセルと異なるセルを選択する ...
注目の機能: グリッドフォーカス  |  デザインビュー  |  ビッグフォーミュラバー  |  ワークブックとシートマネージャー | リソースライブラリ (自動テキスト)  |  日付ピッカー  |  ワークシートを組み合わせる  |  セルの暗号化/復号化  |  リストごとにメールを送信する  |  スーパーフィルター  |  特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する ...)  |  50+ チャート 種類 (ガントチャート ...)  |  40+ 実用的 (誕生日に基づいて年齢を計算する ...)  |  19 挿入 工具 (QRコードを挿入, パスから画像を挿入 ...)  |  12 変換 工具 (数字から言葉へ, 通貨の換算 ...)  |  7 マージ&スプリット 工具 (高度な結合行, Excelのセルを分割する ...)  |  ... もっと

Kutools for Excelは300以上の機能を誇り、 必要なものをワンクリックで手に入れることができます...


Officeタブ-MicrosoftOffice(Excelを含む)でタブ付きの読み取りと編集を有効にする

  • 数十の開いているドキュメントを切り替えるのにXNUMX秒!
  • マウスの手に別れを告げて、毎日何百ものマウスクリックを減らしてください。
  • 複数のドキュメントを表示および編集する際の生産性が 50% 向上します。
  • Chrome、Edge、Firefox と同様に、効率的なタブを Office (Excel を含む) にもたらします。
Comments (67)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Perfect idea for me. But I have a problem with the "ROWS" formula.
I mean point 8.
When I use your formula (in the drop-down list I have nothing entered, as you can see in point 8) in the first cell is "INDIA".
And pick up the cells with the "spilled" error. What I need to change for the formula to work properly.

E1 - India
E2 - #SPILL!
E3 -#SPILL!
E4 - #SPILL!
E5 - #SPILL!
E6 -#SPILL!
E7 - #SPILL!
E8 - India
E9 - Brazil
E10 - Italy
E11 - Japan
E12 - United State
E13 - Francy
E14 - Germany

You also see that there are more poems appearing than yours.
This comment was minimized by the moderator on the site
Hi Przamek PL,
Sory, I cannot reproduce the problem you mentioned. Can you provide us with your data for tesing? If you don't mind, upload your sample file here.
This comment was minimized by the moderator on the site
Thank you for your message.
I was able to run your example correctly.
I have a reflection now ...
How to apply your solution to the UseForm form?

I would like to select a person from the list in the form, then I would have information about the age of this person elsewhere in the form. Such a simple example. Difficult?
This comment was minimized by the moderator on the site
Hi Przemek PF,
This method does not work in UserForm. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Somehow excel will not let me fill in the ListFillRange with ANYTHING. so also not the DropDownList. I did all the steps but am not able to get a flashing cursor and when I type no drop down list appears. any solutions?
This comment was minimized by the moderator on the site
Hi Marloes, This problem can't be solved yet. Make sure the ListFillRange is on the same sheet as your list box. 
This comment was minimized by the moderator on the site
I've just purchased kutools to use this function. Is it possible to have two or more different searchable drop down lists (i.e. referncing different lists of valid entries) on the same sheet?
This comment was minimized by the moderator on the site
Hi Marc,The feature does not support two or more different searchable drop down lists on the same sheet. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
how to use this dropdown in vba form any konw please reply
This comment was minimized by the moderator on the site
Hi, I made an action list for internal use with automatic email reminders in Excel, based on macro and vba. in a cell you select which person to send the reminder to, in a next cell you select which person to CC etc. Is it a good idea to copy this dropdownlist a few 100 times to every possible entry that I supply ? And is it possible to add a rule: Per row a particular person can only be selected once?
This comment was minimized by the moderator on the site
I have around 80000 data while running excel is hang
This comment was minimized by the moderator on the site
Sir How to use this in excel userform combobox....? plz help
This comment was minimized by the moderator on the site
Hi Sourav Singha,
Can't use it in a userform combobox. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Is there a way to make it call up a hyperlink? My email is
This comment was minimized by the moderator on the site
Hi Josh,
Sorry can;t help you with that yet.
This comment was minimized by the moderator on the site
I have a problem. My list is in Armenian language, and I see ??????-s instead of the letters. how can I fix this problem? Thank you in advance
This comment was minimized by the moderator on the site
Hi Vrezh,
Sorry this kind of problem can't be solved yet. Thank you for your comment.
This comment was minimized by the moderator on the site
How can I use this? I have two problem
1st I would like use ComboBox1 for a full column, so I have D column, it should see empty.
When I click into a cell in D column example D7 or D8(etc) I should get a Combo in D7 or D8 etc cell and after select just see the result, not the combo too.

But how can I add combobox dynamically to D2, D4, D11 etc when click or before.
I need for I can search with typing too, so simple(not active-x) combo is wrong.

2nd how set padding? - my combo text when I search is not see whole because itt has padding.

3th if my source is C column, how drop empty elements from list
This comment was minimized by the moderator on the site
Hi Steve Olah,
Sorry can't help you with that. Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations