エクセルのリストを、入力された文字を含むデータで抽出されたものにリアルタイムで反映させる方法

たとえば、会社名が100社くらいあるとして、対象セルに「三和」と打てば「三和」を含む3社だけがリスト表示される（三和運輸株式会社、三和梱包株式会社、三和トランスポート株式会社）、そんな感じの機能をマクロを使わずエクセル関数だけで実現します。

 

Ｇ列のようなデータをリスト対象にするとすべてのデータが表示されてしまいます。

それがＣ列の状態です。

これだとデータが増えてきたときに探すのがものすごく大変になってしまいますよね。

リストが、自動的に自分が入力した文字を含むものに絞られたものになったらいいなと思ったことありませんか？

それを関数の組み合わせで実現する方法を解説します。

【サンプルエクセルファイルダウンロード】

 

Ｇ列の隣のＨ列で、Ａ１セル（今回は“小林”）を含むものはＡ１セルの文字列を表示させ、そうでないセルは空欄にします。

 

SEARCH関数で、Ｇ列がＡ１セル“小林”を含んでいればＡ１“小林”を表示、含んでいなければIFERRORで空欄表示にします。

すると小林１、小林２、小林３、小林４、中小林１、中小林２の右側に“小林”が表示されるようになります。

 

Ｉ列はH列が“小林”の行番号を、上から詰めて、列挙します。

まずI列の１行目は普通にA1セル“小林”が初めて出てくる行をH列から探します。

2行目からは以下の式で、「次に“小林”が出てくる行数」を見つけます。

IFERROR(I1+MATCH(A$1,OFFSET($H$1,I1,0,$K$1),0),””)

 

OFFSET関数の解説をします。

第1引数＝$H$1　MATCH関数の対象範囲をH1セルからスタートする、という意味です。

第2引数＝I1。自セルの1つ上のセルの数字です、H1から下何行目を検索対象範囲にするかということです。1つ目の“小林”は2行目に見つかったわけなので、次は3行目から探したいです。H1は1行目なので、１＋２で３行目から探すことになります。

第3引数＝$K$1はG列のデータ個数です。K1の計算式は=COUNTA(G:G)です。

 

OFFSET関数で前に見つかったデータ次第で検索対象範囲を変動させ、その範囲で見つかった行数を、一つ上のセル内数字に加算すると、欲しい値が得られます。

 

あとはI列の数値に相当するG列の値を、J列に上から詰めて、列挙させます。

上から詰めないとプルダウンで空白が表示されてしまうからそれを除去するために上から詰めるのです。

 

A1列は「データの入力規則」からリスト選択するわけですが、リスト以外のデータを入力したいので「エラーメッセージ」タブの「無効なデータが入力されたらエラーメッセージを表示する」のチェックを外します。

 

 

あとは普通に「設定」タブの設定です。

入力値の種類を「リスト」にして「元の値」をJ列に設定します。

 

 

これでＡ１セルのプルダウンを押すと以下のように“小林”を含むデータだけが隙間なく表示されるようになります。

 

【サンプルエクセルファイルダウンロード】