エクセル 検索結果の抽出方法

質問

Ａ　　Ｂ　　　Ｃ　　　　Ｄ　　　　Ｅ　　Ｆ　　　Ｇ　　　Ｈ　　　Ｉ
１　カナ　漢字　住所　備考　（空白）　カナ　漢字　住所　備考
２　カナ　漢字　住所　備考　（空白）　カナ　漢字　住所　備考

というような住所録があるのですが、
別シートで検索結果を表示できるようにしたいのです。

検索方法は「カナ」であったり「漢字」であったりと様々
検索結果全てをＡ～Ｄ、Ｆ～Ｉというようにまとめて表示

このようなことができるような方法（関数？）はありませんでしょうか？

よろしくお願いします。

回答No.3

MackyNo1 · ベストアンサー

よく質問内容を見たら該当データが複数あるのですね。

この場合は、さらに複雑な数式になりますが、以下の式を入力して右方向および下方向にオートフィルしてください。

=IF(SUMPRODUCT(($A$1:$D$100=$K$1)*1)>=ROW(A1),INDEX(A:A,SMALL(INDEX(($A$1:$D$100<>$K$1)*100+ROW($A$1:$D$100),),ROW(A1))),INDEX(F:F,SMALL(INDEX(($F$1:$I$100<>$K$1)*100+ROW($F$1:$I$100),),ROW(A1)-SUMPRODUCT(($A$1:$D$100=$K$1)*1)))&"")

回答No.2

MackyNo1

検索値がK1セルにあり、各データが100行目まで入力されているなら、以下のような数式を入力し右方向にオートフィルしてください。

=INDEX(A:A,MIN(INDEX(($A$1:$D$100<>$K$1)*100+ROW($A$1:$D$100),)))&""&INDEX(F:F,MIN(INDEX(($F$1:$I$100<>$K$1)*100+ROW($F$1:$I$100),)))&""

なお上記の数式は、同じシートに表示する数式を提示しましたので適宜参照範囲を変更してください。

回答No.1

KURUMITO

シート1の2行目から下方にデータが入力されているとします。
作業用としてＪ2セルには次の式を入力します。

=IF(COUNTIF(A:A,Sheet2!B1)>0,1&"/"&MATCH(Sheet2!B1,A:A,0),IF(COUNTIF(B:B,Sheet2!B2)>0,1&"/"&MATCH(Sheet2!B2,B:B,0),IF(COUNTIF(F:F,Sheet2!B1)>0,2&"/"&MATCH(Sheet2!B1,F:F,0),IF(COUNTIF(G:G,Sheet2!B2)>0,2&"/"&MATCH(Sheet2!B2,G:G,0),""))))

シート2には検索用のセルと結果を表示するためのセルを設定します。
Ａ1セルには[カナ検索]、Ａ2セルには「漢字検索」と入力しＢ1セルにはカナで検索するときの文字列を入力します。Ｂ2セルには漢字で検索するときの文字列を入力します。
その上でＡ4セルには次の式を入力し、右横方向にＩ4セルまでオートフィルドラッグします。

=IF(Sheet1!$J$2="","",IF(AND(LEFT(Sheet1!$J$2,1)*1=1,COLUMN(A1)<5),INDEX(Sheet1!$A:$D,MID(Sheet1!$J$2,FIND("/",Sheet1!$J$2)+1,10),COLUMN(A1)),IF(AND(LEFT(Sheet1!$J$2,1)*1=2,COLUMN(A1)>5),INDEX(Sheet1!$F:$I,MID(Sheet1!$J$2,FIND("/",Sheet1!$J$2)+1,10),COLUMN(A1)-5),"")))