今回は、検索付きリストの具体的な作成方法を紹介します

作成するには、３つの手順を踏みます

ちなに、VBAは一切使いません！

➀検索文字を作成

リストに入力された文字から検索文字を作成する

➁候補データの作成する

検索文字をもとにして、リストデータから候補データを作成する

③候補データの表示

②で作成した候補データをリスト表示します

上の①～③をエクセルシート上で表すと、次の画像の通りとなります

上の画像の中で、最もポイントとなるのは②です！

ここで、エクセルの新機能である、1セルに出力範囲が収まらない関数を使用します！

では、ここから詳細な解説を開始します

➀検索文字を作成

下の画像の通り、C１セルに「=E2」を入力し、リスト内の文字（E2セル）を、検索文字としてC1セルに抽出します

➁候補データを作成する

ここから次の３つの関数を組み合わせて、検索文字から候補データを作成しますⅰ）SEARCH関数

ⅱ）ISNUMBER関数

ⅲ）FILTER関数

ⅰ）SEARCH関数

構文/SEARCH(検索文字列,対象,[開始位置])

まずは下のGIF画像にて、この関数を入力した時の様子を確認してください

１つのセルに入力したら、対象（リストデータ）に該当したデータを複数出力してくれます

ちなみに、上のGIFで出力される「１」の意味合いは想像がつきますでしょうか？

もし、検索文字が「悟」の場合には、下の画像のように「４」が出力されます

つまり、SEARCH関数は検索文字が含まれる位置を出力してくれるのです！

ですので、検索文字が含まれる候補データを、リストデータの中から特定できるようになりました

ただし、あくまで候補データを特定しただけで、候補データとしてはまだ使用できませんので、次のⅱ）でもう少し修正を加えます

ⅱ）ISNUMBER関数

構文/ ISNUMBER(判定対象)

ISNUMBER関数は判定対象が「数字」かどうかを判定する関数です

判定対象が数字であればTRUE、数字でなければFALSEを返します

ですから、前述ⅰ）のSEARCH関数で出力した内容（例/１や4）をISNUMBER関数の判定対象にした場合にはどうなるでしょうか？

ISNUMBER関数の判定がTRUEのものは「候補データとして使えるもの」、FALSEは「候補データとして使えないもの」として区分することができます

ⅲ）FILTER関数

構文/FILTER(a:対象,b:条件式,c:省略可/合致するもの無の場合)

➁の仕上げとしてFILTER関数を使います

FILTER関数は、aの対象リストの中からbの条件式に合致するものを抽出します

今回は、FILTER関数で下の画像の「A列/リストデータ」の中から、「C1セル/検索文字」が含まれているものを抽出します

但し、検索文字に該当するかどうかについては、直接は判定することができないので、bの条件式を2段階に分けます

ちなみに、条件式はTRUEかFALSEかどうか、でも設定可能です！

1段階目➡SEARCH関数にて、リストデータの中で検索文字を含むものは数値化

2段階目➡1段階目で数値化したものをISNUMBER関数でTRUEとFALSEに置換

では、上記の「1段階目」と「2段階目」を実際にFILTER関数で表現してみしょう！

FILTER（ _

a:A列/リストデータ _

b:ISNUMBER(SEARCH(C1セル/検索文字,A列/リストデータ)) _

)

上のbの条件式を前述の1段階目と2段階目で分けると次の通りとなります

1段階目 ➡ SEARCH(C1セル/検索文字,A列/リストデータ)

1段階目で検索文字が含まれている位置を１や４などに数値化します

2段階目 ➡ ISNUBER(1段階目)

1段階目で数値化されたものをTRUE、それ以外をFALSEで返します

では、上記のFILTER関数の実際の”動き”をGIF画像で確認してみましょう

C１セルに設定した、E2セルとの連動式（=E2）を一旦外し、「鈴木」と入力してみます

検索文字の「鈴木」を含む候補データが、A列のリストデータから抽出されています

検索文字の「鈴木」を入力する前は、全てのデータが出力されています

これは、氏名の間に空白が含まれているためです

③候補データをリストに表示する

②で候補データを抽出する仕組みができました

いよいよ、候補データから検索付きリストを作成します

➀で検索文字を入力しますが、通常のリストの作成の仕方では、エラーになってしまいます

何故なら、検索文字自体はリストに含まれていないからです

例えば、下の画像でいえば、鈴木健司～鈴木修の中に「鈴木」と一致するものがないです

ではどうするか？

リスト機能にはエラーメッセージを調整する機能があるので、そちらの機能を使います

上の画像の黄色の箇所「無効なデータが入力されたらエラーメッセージを表示する」のチェックを外します

そうすれば、リストに完全一致しないものをリストに入力してもエラーメッセージは出力されません

そして、最後にリストの範囲の仕方を工夫しましょう

リストの「元の値」として、候補データが出力される最初のセルを指定しましょう！

　そして、＃を加えれば、出力される候補データ数に合わせてリストが自動作成されます！

下のGIF画像は#の使用例です！

＜まとめ＞

　今回は、エクセルの新機能（SEARCH関数、FILTER関数）とリスト機能を組み合わせてリストに検索機能を加えることができました

　SEARCH関数により、検索文字を含む文字列から検索文字が含まれる「位置/数値」を抽出し、ISNUMBER関数でTRUEかFALSEに置き換えるのがポイントです

　検索文字が含まれているかどうかをTRUEかFALSEに置き換えることで、FILTER関数の条件式として活用することができます

　FILTER関数をはじめとする、エクセルの新関数を有効活用すれば「VBA無」でもかなりの事ができますので、今回を機会に、ぜひ研究してみてください

FILTER関数の使用例については、他にも記事を書いています！

　もし、今までリスト機能を使ったことがない方がいたら、エクセル画面上のデータタブからデータの入力規則を見つけてみてください

データの入力規則から前述のリストが作成できます

解説は以上です

長文に最後までお付き合い頂き誠にありがとうございました

参考までに今回使用したエクセルファイルを添付します

検索付きリスト

1 ファイル 11.00 KB

ダウンロード

にほんブログ村