エクセル ドロップダウンリストに検索機能を追加したい

リストに約１００件のデータがあり、ドロップダウンで選択しているのですが、
データを見つけるだけ一苦労しています。
検索機能を追加し、近しい文字を入力すると、候補が絞られ、ドロップダウンリストに入っているデータを少なくしたいです。
何か方法はありますか？

質問者からの補足コメント

• 

  ドロップダウンリストで選択できるところまでは出来上がっています。

    補足日時：2021/06/12 22:55

• 

  申し訳ありません。
  シート２のA列に選択するデータがあり、
  シート１にA列２にドロップダウンでシート２のA列にある１００件のデータを選択させます。
  
  ドロップダウンを作るため、入力規制で＝（シート２!$A$１:$A$100")を選択していますが、
  その入力規制の入力欄に(＝INDIRECT（シート２!$A$１:$A$100"))を入れて
  シート１で試して見るとエラーで出て、どうしたら良いのか悩んでいます。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/06/13 07:04

• 

  ありがとうございます。
  入力制限欄に入力すると、図のエラーで出てきます。
  理由がわからなく、困っております。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2021/06/13 15:00

• 

  ありがとうございます。
  セルに文字を入力して、下矢印お押すと候補が絞られているイメージをつくりたいのです。

  
  No.2の回答に寄せられた補足コメントです。 補足日時：2021/06/13 15:13

• 

  ありがとうございます。
  つまり、手入力設定や近しい文字検索については、
  別のリストを作成し、そこから選び出すそうなリストが必要という理解で良いでしょうか。

  No.5の回答に寄せられた補足コメントです。 補足日時：2021/06/14 04:38

• 

  ありがとうございます。
  ＞A1セルではその範囲名をリスト表示するように指定します。
  ＞B1セルで範囲名を割り当てられた範囲のデータをリストとして表示するようにしています
  この部分の設定方法は、どうされていますか。

  No.4の回答に寄せられた補足コメントです。 補足日時：2021/06/14 04:54

No.7 回答者： fujillin 回答日時： 2021/06/14 12:09

No2です

＞セルに文字を入力して、下矢印お押すと候補が絞られている
＞イメージをつくりたいのです。
文章の意味が不明です。

そういう回答をしたつもりですけれど？？？？
わからないなら、ひとまず、回答通りのものを作成してみれば？
コピペで同じものが作成できるはずです。

No.6 回答者： 銀鱗 回答日時： 2021/06/14 08:54

No.5の補足への返答です。

その通りです。
そのため呼び出す範囲に対して「範囲名」を付けるのです。

その「範囲名」は分かりやすい名称なら何でもいい。
　「Aから始まる」
でも、
　「Aから」
でも、
　「Header_A」
でも、
制限されている単語以外なら何でもいい。
（設定してエラーが返るなら、その単語は制限されて使えないという事）
ただし、統一しないと読みにくくなるから注意しよう。

・・・

「検索」じゃないんだ。
「目次」「インデックス」を作るという意味です。

No.5 回答者： 銀鱗 回答日時： 2021/06/13 17:37

範囲名をリストから選んでも良い。

手入力しても良い。
これは「入力規則」の「リスト」の制限です。

範囲名以外の値を入力したら怒られるのは当然。
自分が示した例では「Z_」とか「A10]なんて範囲名は作っていないので、
　「そんな値はリストにねーぞ。よく見ろ」
と怒られる。
当たり前のことですよね。

No.4 回答者： 銀鱗 回答日時： 2021/06/13 17:30

ええと、どのようなことをやっているのか理解していらっしゃいませんね。

自分の回答で例を挙げたものは、
　事前準備として、
　　Aで始まるデータに対して「A」という範囲名を、
　　Bで始まるデータに対して「B」という範囲名を、
　　…
　　Fで始まるデータに対して「F」という範囲名を、
　つける。
　A1セルではその範囲名をリスト表示するように指定します。
　B1セルで範囲名を割り当てられた範囲のデータをリストとして表示するようにしています。

範囲名に対するセルの範囲をあらかじめ手動で設定してください。

・・・

あと、スクリーンショットをトリミングするなら、見やすいサイズにしてください。
何が書いてあるのか分からないような図を貼られても困る。

No.3 回答者： 銀鱗 回答日時： 2021/06/13 09:02

(＝INDIRECT（シート２!$A$１:$A$100"))

……ダブルクオーテーションが一つ足りない、括弧が多い、ような気がするのは、自分だけでしょうか。

あと、INDIRECT関数に与えるパラメータ内は絶対参照にしなくてもいい。
その方が見やすいと思う。（見やすい＝間違えにくい）

　＝INDIRECT("Sheet2!A1:A100")
でOKてこと。

No.2 回答者： fujillin 回答日時： 2021/06/13 08:52

こんにちは

＞近しい文字を入力すると、候補が絞られ、ドロップダウンリストに
＞入っているデータを少なくしたいです。
どのような絞り込みをするのか不明ですが、普通に考えれば、「先頭文字から順に絞り込む」か、あるいは、いわゆる「あいまい検索」で絞り込むかでしょうか？

とりあえず、「あいまい検索」（「*検索値*」 での絞込み）での例を以下に挙げておきます。
要領のみなので、1シートに集約してありますが、別シートの場合等は、セル参照を調整すれば良いでしょう。

添付図では、仮に、
ドロップダウンがA1セルにあるものとして、もとのリストはD列にあるとしています。
入力規則に直接使用するリスト（＝絞り込まれたリスト）を作成する必要があるので、これにはF列を利用しています。

A1セルに検索値となる文字（図では「田」の文字）を入力した状態で、ドロップダウンを表示した状態が添付図です。
「*田*」で検索しているので、「田」を含むものが元のリストから絞り込みリストに4件抽出された状態です。
（二文字以上の入力でも、同様の絞り込みを行います）


添付図は、以下のような仕組みになっています。
1）絞込リストのF2セルに
=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($D$2:$D$100)/(COUNTIF(OFFSET($D$1,ROW($D$2:$D$100)-1,0),"*" & $A$1 & "*")>0),ROW(A1))),"")
の関数式を入力し、下方に必要なだけフィルコピーします。

2）A1セルの入力規則を、F列に連動させるため
「設定」タブで、種類はリストを選択し、元の値の範囲に、
「=OFFSET($F$2,,,SUMPRODUCT((F:F<>"")*1)-1)」の式を設定。
「エラーメッセージ」タブで「無効なデータが～」のチェックを外す。
（↑これを行なっておかないと、検索値の一時的入力ができません）

という設定にしてあります。

※ 違う絞込方法をとりたい場合は、抽出の式を適宜変更していただければ、実現できるものと想像します。

No.1 回答者： 銀鱗 回答日時： 2021/06/13 03:03

どういった目的でリストから選択するのかで、対処方法が変わってくるわけですが、

単に値として表示させたいだけならインデックスのようなリストを別に作って、
最終的に選択する値を絞り込ませる。

A1セルには
　A～F
B1セルには
　A1セルで選択された文字から始まる一覧
……のような感じ。

こんなんで良ければ、
　Aから始まるリストの範囲に「A」
　Bから始まるリストの範囲に「B」
　…
　Fから始まるリストの範囲に「F」
と範囲名を付ける。
A1セルの入力規則に設定した範囲名のテキスト一覧を指定。
元の値……に、
　A,B,C,D,E,F
と入力すればでOK。
B1セルの入力規則に数式で
　=INDIRECT(A1)
と設定。
こんだけです。

INDIRECT関数は「文字列」を「範囲」に置き換える関数ですので、
範囲名のテキストを「範囲」に置き換えてくれます。