VLOOKUPの結果をすべて表示したいです。

質問

A1:B100にデータが入っていて、所々A3:B3のように空白セルがあるため、C列に関数を入れA列の空白セルと重複データを省いて表示しています。
C列を検索セルにして、表のようにD、E、F・・にB列のデータを順にすべて表示したいのですが、D列はVLOOKUPで一番目のデータだけ表示できてもE列以降のデータの返し方がわかりません。つたない説明で申し訳ありません。ご指導の程、宜しくお願い致します。

回答No.7

MackyNo1 · ベストアンサー

>質問の添付画像で、A列となっていますが実際にはK列で、B列はL列、間に非表示にしている列を挟んでC列はP列となっています。

この条件ならP1セル（どのセルに入力しても同じ数式です）に以下の式を入力して下方向にオートフィルしてください（すでに重複のないデータが表示されているなら必要ないので念のため他の空白列に入力して同じデータになるか確認してみてください）。

=INDEX(K:K,SMALL(INDEX(((MATCH($K$1:$K$100&"",$K$1:$K$100&"",)<>ROW($K$1:$K$100))+($K$1:$K$100=""))*1000+ROW($K$1:$K$100),),ROW(K1)))&""

そのP列の結果に対応するL列のデータを横方向に引っ張ってきたいなら以下の式を入力して、右方向および下方向にオートフィルしてください（最後のCOLUMN関数の引数だけはどのセル位置でもA1にしてください）。

=INDEX($L:$L,SMALL(INDEX(($K$1:$K$100<>$P1)*1000+ROW($K$1:$K$100),),COLUMN(A1)))&""

お礼

細かくご指導いただきまして、ありがとうございました。
お陰様で完璧なデータが仕上がりました。これからも勉強していきますので、また何かありましたら、ご指導の程宜しくお願い致します。

補足

出来ました！ありがとうございます。計算式は理解できていないのですけど・・少しずつ勉強していきます。
ただ、P列でひとつだけ「0」が表示されます。オプションの「ゼロ値」はチェックを外している状態です。何が原因なのでしょうか・・

回答No.6

tom04

No.2です！
補足に
＞実際のデータですと1行目がないため・・・

とありますので再びお邪魔します。

余計なお世話をしてしまったようですね！
質問通りの配置で1行目のタイトル行なない場合のD1セルに入れる数式を
=IF(COUNTIF($A:$A,$C1)<COLUMN(A1),"",INDEX($B$1:$B$1000,SMALL(IF($A$1:$A$1000=$C1,ROW($A$1:$A$1000)),COLUMN(A1))))
（前回同様、配列数式になってしまいますので、Shift＋Ctrlキーを押しながらEnterキーで確定してください。）

として列方向・行方向にオートフィルでコピーではどうでしょうか？

何度も失礼しました。m(__)m

お礼

ご指導いただきましてありがとうございました。
なかなか理解できずにいますが、お陰様で完成致しました。
これからもご指導の程、宜しくお願い致します。

補足

お世話おかけして申し訳ありません。自分のスキルに合わないことをしようとしていて、なかなかご指導通りにできませんでした。何度も教えていただいてありがたく思います。

回答No.5

MackyNo1

>>所々A3:B3のように空白セルがあるため、C列に関数を入れA列の空白セルと重複データを省いて表示しています。

>これもネットで検索して見つけた計算式で私が作ったものではなく、理解もできていません・・
宜しくお願い致します。
=IF(ROW()>COUNTIF($N$1:$N$110,"<>"),"",INDEX($N$1:$N$110,SMALL(IF($N$1:$N$110="","",ROW($N$1:$N$110)),ROW())))

上記の数式は少なくとも重複のあるデータを処理する数式になっていないように思われます。

実際に、重複のないデータが詰めて表示できているなら、他の補助列の数式が関与していると思われますが、数式を多くのセルに入力すると（特にCOUNT系の関数や検索関数）、多くのメモリーを消費するので、エクセルがハングアップするなど問題が発生する可能性も考えられます。

私の回答はA列だけのデータから重複のないデータをC列に表示する数式ですが、配列数式を利用したものですので、表示データ数が多くなると動きが重くなるので、注意が必要です。
この際に、一度シート全体の構成を見直して不要な数式などがないか、あるいは関数以外の方法で対応できないかなどを検討したほうが良いような気もします。

補足

ご指導いただきまして、ありがとうございます。
C列のデータは、重複データと空白セルを除いて正常に反映されているのです。
MackyNo1様の計算式も入れてみたのですが、何かが影響していて反映されません・・
質問の添付画像で、A列となっていますが実際にはK列で、B列はL列、間に非表示にしている列を挟んでC列はP列となっています。A列もＢ列も計算式で表示しています。
このあたりに何か原因があるのでしょうか・・？

回答No.4

MackyNo1

No3の回答の訂正と補足です。

誤：例えば、2行目からのデータの場合、C1セルに以下の数式を入力します。
正：例えば、1行目からのデータの場合、C1セルに以下の数式を入力します。

もし1行目が項目名でデータ部分が2行目から入力されている場合は、以下のようにROW関数の後に行数との違いの数値（2行目場合は「２－１」）を引き算するように数式を修正する必要があります。

=INDEX(A:A,SMALL(INDEX(((MATCH($A$2:$A$100&"",$A$2:$A$100&"",)<>ROW($A$2:$A$100)-1)+($A$2:$A$100=""))*1000+ROW($A$2:$A$100),),ROW(A1)))&""

回答No.3

MackyNo1

C列の空白データを除いて重複のないデータを一覧表示するのはかなり複雑な数式を利用する必要がありますがOKなのでしょうか？

例えば、2行目からのデータの場合、C1セルに以下の数式を入力します。

=INDEX(A:A,SMALL(INDEX(((MATCH($A$1:$A$100"",$A$1:$A$100&"",)<>ROW($A$1:$A$100))+($A$1:$A$100=""))*1000+ROW($A$1:$A$100),),ROW(A1)))&""

D１セルは以下の式を入力し、右方向および下方向にオートフィルします。

=INDEX($B:$B,SMALL(INDEX(($A$1:$A$100<>$C1)*1000+ROW($A$1:$A$100),),COLUMN(A1)))&""

回答No.2

tom04

こんにちは！
C列に重複なしに表示させるまでは問題なくできている訳ですよね？

確かにVLOOKUP関数では最初にヒットしたものしか表示できませんので、
一例です。
↓の画像のようにデータは2行目からあるとします。
D2セルに
=IF(COUNTIF($A:$A,$C2)<COLUMN(A1),"",INDEX($B$1:$B$1000,SMALL(IF($A$1:$A$1000=$C2,ROW($A$1:$A$1000)),COLUMN(A1))))
これは配列数式になってしまいますので、この画面からD2セルにコピー＆ペーストしたあとに、数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

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

以上、参考になれば良いのですが・・・m(__)m

補足

ご回答いただきまして、ありがとうございます。
tom04様の添付画像のとおり、データを作成し計算式を入れますとできたのですが、私の実際のデータですと1行目がないため、失敗してしまいます。計算式を理解しようとネットで調べながら勉強しているのですが、時間がかかりそうなので取り急ぎお礼申し上げます。

回答No.1

ihyou_P

回答ではありません。
Ｃ列の関数はどう記述されているのか、教えていただけないでしょうか。

補足

ありがとうございます。
添付画像では、A列からG列を使用していますが、実際にはK列から使用し、間に非表示にしている列があって下記の数式（配列）が入っています。でも、これもネットで検索して見つけた計算式で私が作ったものではなく、理解もできていません・・
宜しくお願い致します。
=IF(ROW()>COUNTIF($N$1:$N$110,"<>"),"",INDEX($N$1:$N$110,SMALL(IF($N$1:$N$110="","",ROW($N$1:$N$110)),ROW())))