検索できる高度なドロップダウンリスト エクセル中級技best

検索できるドロップダウンリスト

 Excel2019

Excel2016,2013技BEST 70回

検索できるドロップダウンリストを作

今回は、検索できるドロップダウンリストを作成します。かなり高度なドロップダウンリストです。
連動するドロップダウンリストでは、物足りないというケースもあります。大量のデータから該当するデータを探し出す際に、あまりにも対象が多いと大変だということがあります。 少々手間はかかりますが、検索できるドロップダウンリストが欲しいという方は参考にしてください。

使う関数についてINDEX関数、MATCH関数

  1. 今回は、たくさんの関数を利用します。INDEX関数、
    検索できる高度なドロップダウンリスト1
  2. まず、INDEX関数は、指定された行と列が交差する位置にあるセルまたはセルの参照を返す関数です。
    検索できる高度なドロップダウンリスト2
  3. 次にMATCH関数は、指定した行または列の中で、指定した数値や文字列などを探し、発見したデータが何番目にあるのかを返す関数です。
    検索できる高度なドロップダウンリスト3

Serch関数とOffset関数

  1. SEARCH 関数は指定された文字列を他の文字列の中で検索します。
    検索できる高度なドロップダウンリスト6
  2. OFFSET関数は、指定された行数と列数だけシフトした位置にあるセルの参照を返します。(またはセル範囲への参照を返します。)
    検索できる高度なドロップダウンリスト7
  3. 以上の関数を利用して、サンプルの名簿(300件)で絞り込み検索が可能なドロップダウンリストをH2セルに作成しましょう。
    検索できる高度なドロップダウンリスト8
  4. 名簿には、フリガナを入れています。検索する時に楽だろうという考えからです。
    検索できる高度なドロップダウンリスト9
  5. フリガナから絞り込みたいカタカタが含まれるかどうかを調べます。
    検索できる高度なドロップダウンリスト10

入力規則でカタカタの設定にする

  1. H2には入力規則でカタカタのだけの設定にしておきます。
    検索できる高度なドロップダウンリスト11
  2. C,D,Eセルには、見出しとして、数式の目的を入れておきます。(実際には不要です。)
    検索できる高度なドロップダウンリスト12
  3. C 列ではあるかないかを、D 列では何個あるかを、そして良い列では対象を抽出します。
    検索できる高度なドロップダウンリスト13
  4. C2セルに数式を入力します。
    検索できる高度なドロップダウンリスト14
  5. H2セルに入力した「オ」という文字が B2セルにあるかないかを調べます。
    検索できる高度なドロップダウンリスト15
  6. H2は、絶対参照にしておきます。
    検索できる高度なドロップダウンリスト16
  7. 検索した文字が一番目にあれば戻り値は1ですが、5番目にあれば5となりますし、またない場合には Value エラーが戻り値として返されます。
    検索できる高度なドロップダウンリスト17
  8. このエラーが嫌な場合はIFERROR関数を使って非表示にすることができます。
    検索できる高度なドロップダウンリスト18
  9. =IFERROR(SEARCH($H$2,B2),0)
    検索できる高度なドロップダウンリスト19
  10. IFERRORを使うことで、 Value エラーの代わりに0と表示されるようになりました。
    検索できる高度なドロップダウンリスト20