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

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

 

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

それがC列の状態です。

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

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

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

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

 

G列の隣のH列で、A1セル(今回は“小林”)を含むものはA1セルの文字列を表示させ、そうでないセルは空欄にします。

 

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

すると小林1、小林2、小林3、小林4、中小林1、中小林2の右側に“小林”が表示されるようになります。

 

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

まずI列の1行目は普通に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行目なので、1+2で3行目から探すことになります。

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

 

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

 

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

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

 

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

 

 

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

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

 

 

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

 

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

関連記事はこちら
お知らせ求人案内(令和2年6月)
お知らせ企業理念
お知らせ大雨で浸かったクルマ、水が引いても使用しないでください
お知らせ【東工大生向け】アルバイト先休業で学費困窮支援