検索結果を複数表示したい

質問

いつもお世話になっております。
よろしくお願いいたします。

顧客リストをユーザーの名前で検索し、条件に合うセルを表示したいと考えていますが、
同性同名や同一人物で複数回の実績があるものについて、lookupやindex、matchでは対応できず苦慮しております。
（2件以上のデータが存在するものも1件しか出せない）
条件に適合するユーザーの情報を全て表示出来ればよいのですが、
関数のみで行う方法はありますでしょうか？

何卒よろしくお願いいたします。

回答No.4

tom04

こんばんは！
横からお邪魔します。

＞ユーザーの名前で検索し、条件に合うセルを表示したいと考えていますが・・・
すなわち条件と一致する複数セルを表示したい！という解釈です。
やり方だけです。
Sheet1のデータをSheet2に表示するようにしてみました。

判りやすいのは↓の画像のように作業用の列を設ける方法だと思います。
Sheet1の作業列G2セルに
=IF(Sheet2!A$1="","",IF(ISNUMBER(FIND(Sheet2!A$1,A2)),ROW(),""))
という数式を入れフィルハンドルでずぃ～～～！っと下へコピーしておきます。
これでSheet2のA1セルの文字が含まれる行に行番号が表示されます。

そしてSheet2のA4セルに
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!$G:$G,ROW(A1))),"")
という数式を入れ、列・行方向にフィルハンドルでコピー！
これで画像のような感じになります。

※　日付セルがある場合はセルの表示形式で「日付」にしてください。

※　配列数式などで作業列なしで一気に！
極力短い数式でやってみました。m(_ _)m

回答No.3

bunjii

>INDEXで範囲の指定をしてるのは分かるのですが、肝心の内容がちんぷんかんぷんで・・・
INDEX(($B$1:$B$1000=F$1)*ROW(F$1:F$1000)+($B$1:$B$1000<>F$1)*10000,0)は検索範囲を加工するために使っています。
F$1セルと一致するB列のセルは行番号を、不一致のB列のセルは10000を返すようにしてあります。
配列値のままSMALL関数へ渡すためINDEX関数の第2引数である行番号を0にし、列番号は不要なので省略しています。
INDEX(($B$1:$B$1000=F$1)*ROW(F$1:F$1000)+($B$1:$B$1000<>F$1)*10000,)のように第2引数を指定しない方法もありますが引数の区切りの「,」を忘れるとエラーになるので「0」を指定しました。
SMALL関数では行番号の若い順に行番号を返すため数式のセルの行番号とリンクしています。
外側のINDEX関数ではC列からSMALL関数で返ってきた行番号を基に目的のデータを抽出します。
一番外のIF関数は説明するまでも無くB列の中でF$1セルと一致する個数まで繰り返し、超えたときは空欄にするための条件分岐です。

回答No.2

bunjii

>条件に適合するユーザーの情報を全て表示出来ればよいのですが、関数のみで行う方法はありますでしょうか？
幾つかの関数を組み合わせれば抽出可能です。
貼付画像はExcel 2013で検証した結果ですが他のバージョンでも再現できると思います。
F2セルへ次の数式を入力します。
=IF(COUNTIF($B:$B,F$1)>=ROW(F1),INDEX($C:$C,SMALL(INDEX(($B$1:$B$1000=F$1)*ROW(F$1:F$1000)+($B$1:$B$1000<>F$1)*10000,0),ROW(F1)),1),"")
F2セルを右と下へ必要数コピーしました。

補足

ありがとうございます。
同じ内容のデータを作ってみたところ、ご提案のやり方で進められそうなのですが
下記の処理内容が理解できません。。

INDEX(($B$1:$B$1000=F$1)*ROW(F$1:F$1000)+($B$1:$B$1000<>F$1)*10000,0)

INDEXで範囲の指定をしてるのは分かるのですが、肝心の内容がちんぷんかんぷんで・・・

恐れ入りますが、ご教示頂けますでしょうか。
何卒よろしくお願いいたします。

回答No.1

aokii

まずはピボットテーブルでユーザー名のリストを作り、そのリストのユーザー名の行をVLOOKUP関数で見つけ出し、見つけられなかった残りの行のみのデータを作り、そのデータを使ってのVLOOKUP関数で見つけ出し、見つけられなかった残りの行のみのデータを作すという操作を、見つけられなかった残りの行が無くなるまで、その操作を繰り返してみてください。

お礼

早速のご回答ありがとうございます。
今回はピボットテーブルを使わないやり方で処理を作ろうと思います。
ありがとうございました。