範囲の中から特定の文字を検索し隣のセルを表示させる

質問

はじめての質問のため、何か不備や不足などがあると言っていただけると幸いです。

エクセルはほぼ初心者です。下のようなエクセルで、(1)で条件に合うもののみ
(2)の表に反映されるようにしたいです。
＜完成＞
(1)
　　A　　　B 　　C　　(列)
1　　1　　　a　　山田
2　　2　　　a　　佐藤
3　　3　　　b　　高橋
4　　4　　　a　　伊藤
5　　5　　　b　　柳
(行)

(2)
　　　A　　　B 　　C　　(列)
70　　1　　　b　　高橋
71　　2　　　b　　柳　
72　　3　　　　　　　　
73　　4　　　　　　　　
74　　5　　　　　　　　
(行)

＜完成＞の図のように、(1)ではB列に商品(aまたはb)とその購入者が表示されています。
(2)では(1)の商品bと購入者のみを詰めるように表示させたいです。

現在、=if(COUNTIF($B$1:$B$5,"b")>=A70,"b","")　で、商品bのみをつめて表示
させることはできましたが、bと対応する購入者も隣(C列)に表示させたいです。

＜現在＞
(1)
　　A　　　B 　　C　　(列)
1　　1　　　a　　山田
2　　2　　　a　　佐藤
3　　3　　　b　　高橋
4　　4　　　a　　伊藤
5　　5　　　b　　柳
(行)

(2)
　　　A　　　B 　　C　　(列)
70　　1　　　b　　　　←ここにＢの対応する購入者も完成図のように
71　　2　　　b　　　　←表示させたいです
72　　3　　　　　　　　
73　　4　　　　　　　　
74　　5　　　　　　　　
(行)

http://okwave.jp/qa/q4732659.html?from=recommend
比較的近い内容かと思い↑リンクの質問も拝見させていただきましたが、
購入者のようにＢ＝○○　と固定されてはいないということと、
商品がaとbのみなのでbがひとつしかないとは限らないため、こちらで
質問させていただきました。
VBAなどはほとんど使ったことがないため自信はありません。

使用しているエクセルは2003です。

お力を貸していただけると幸いです。

回答No.1

tom04 · ベストアンサー

こんばんは！
A列は連番と考えて良い訳ですよね？

やり方だけ・・・

↓の画像のようにA69セルに検索データを入力するとします。

B70セルに
=IF(COUNTIF($B$1:$B$5,$A$69)<ROW(A1),"",INDEX(B$1:B$5,SMALL(IF($B$1:$B$5=$A$69,ROW($A$1:$A$5)),ROW(A1))))
これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！

この画面からコピー＆ペーストする場合はB70セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これを列方向・行方向にオートフィルでコピーすると
画像のような感じになります。

※　配列数式はデータ量が極端に多い場合、PCにかなりの負担をかけ計算速度が落ちますので
作業用の列を用いて操作する方が良いと思います。m(_ _)m

お礼

分かりやすく画像もつけていただき、ありがとうございます！

教えていただいた数式を参考に入力いたしましたが、なにかを間違ってしまっているのか、表示されず…。。。もう少し頑張ってみます！

この度はありがとうございました！

回答No.2

KURUMITO

データが多くなっても計算に負担のかからない方法は配列数式などを使うよりも作業列を使って対応する方法です。
例えば次のようにします。
お示しの表1がシート1に有るとします。
D1セルには次の式を入力して下方にドラッグコピーします。

=IF(B1="","",B1&COUNTIF(B$1:B1,B1))

こうすることでどんな商品についても1からの番号が付加されます。その番号を利用して別に表を作ればよいのです。
例えば表2をシート2に表示させるとしてシート2のA1セルには検索商品とでも文字を入力し、B1セルには商品名の例えばbを入力します。
A2セルには次の式を入力して下方にドラッグコピーします。
B2セルには次の式を入力してC2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COUNTIF(Sheet1!$D:$D,$B$1&ROW(A1))=0,"",INDEX(Sheet1!$B:$C,MATCH($B$1&ROW(A1),Sheet1!$D:$D,0),COLUMN(A1)))

これでB1セルにbを入力すればb商品のリストが、aを入力すればa商品のリストが瞬時に表示されます。

お礼

負荷をかけない方法を教えてくださりありがとうございます！

便利な関数を教えていただき、機会があったらそちらの関数も使わせていただきます。

ありがとうございました。