エクセルで検索ワードにヒットした行のみ抽出する

エクセルで一覧表から検索したキーワードにマッチする行のみ抽出したいんだけど、と相談を受けました。検索範囲の列は複数あるそうです。

完全一致でよければ、以前紹介した方法を少し修正すれば、そのまま使えます。

エクセルで特定の項目だけを抽出して別シートに表示する
「日々記録している表の中から、特定の項目だけを抽出した表を別シートに出力したい。入力すれば即反映されるのが望ましい」といった要望を受けました...

たとえば、Sheet1に、元となる下のような表があるとします。

excel-search01

 

そして、Sheet2のE1セルに入力したキーワードにマッチする行を抽出します。A2セルへ下記を入力し、右方向と下方向へオートフィルします(途中で改行していますが1行です)。

=IF(ROW(A1)>COUNTIF(Sheet1!$A$2:$B$20,$E$1),””,
INDEX(Sheet1!A$1:A$20,SMALL(INDEX((Sheet1!$A$2:$B$20<>$E$1)
*1000+ROW(Sheet1!$A$2:$B$20),),ROW(A1))))

excel-search02

以前紹介した書式から、検索範囲をSheet1のA-B列に広げ、E1の文字を検索するようにしただけです。これだけで、名前や色で検索し、一致する行が表示されます。

excel-search03

ただ完全に一致していないと表示されません。たとえば「みかん」で検索すると、「広島みかん」や「温州みかん」は検索結果に表示されません。

excel-search04

また、各列の名称が明確に分かれていれば問題ありませんが、同じ名称が含まれる場合は正常に表示されなくなります。ちょっと分かりやすい例で、「名前」が「みかん」、「色」も「みかん」の場合、「みかん」で検索すると2行に分けて表示されます。

excel-search05

スポンサーリンク

検索ワードで部分一致した行を抽出

キーワードの一部でも一致すれば結果に表示したり、重複も避けるなら、判定用の列を1つ設けるのが分かりやすいと思います。Sheet1の元の表の右側、D2セルに次の式を入れ、下方向にオートフィルします。

=IF(COUNTIF(A2:B2,”*”&Sheet2!$E$1&”*”)>0,”○”,””)

excel-search06

検索結果のSheet2は、先程の判定用の列だけをチェックし、○の数を数えるようにします。A2セルに下記の式を入れ、右方向と下方向にオートフィルします(途中で改行していますが1行です)。

=IF(ROW(A1)>COUNTIF(Sheet1!$D$2:$D$20,”○”),””,
INDEX(Sheet1!A$1:A$20,SMALL(INDEX((Sheet1!$D$2:$D$20<>”○”)
*1000+ROW(Sheet1!$D$2:$D$20),),ROW(A1))))

excel-search07

これで、「みかん」と入れれば、「広島みかん」や「温州みかん」も表示されますし、「黄」と入れれば「黄」や「黄緑」も含んで表示されます。また複数の列でヒットした場合でも、1行で表示されます。

excel-search08

実際には、判定用の列はSheet2に作るなど、リストとは別シートにしたほうが、あとあと使いやすいかも。分けておけば、判定行を気にせずリストを追加していけるからです。一応、今回はこの方法で目的を達する事が出来たそうです。

スポンサーリンク

シェアする

フォローする