EXCEL2003で、Vlookupの検索で複数ヒットした場合全て抽出したいんですが、可能ですか?

<sheet1>
  A列   B列
1 A1000 1234
2 A2000 2345
3 A3000 3456
4 A1000 7777
5 A4000 4567
6 A1000 8888

<sheet2>
B1に関数を入れて、
  A列   B列
1 A1000  1234・7777・8888

のように表示させたいんです。


過去質問を確認すると、imogasi方式や、オートフィルタを使用するとありましたが、
imogasi方式はいまいち理解ができず(っというか、有効な手段を見つけることができませんでした)
オートフィルタは使用上、今回は有効な手段ではありません。
また、Vlookupには拘りません。他の関数や、複数の関数を組み合わせても大丈夫です。
関数を使ってできるようであればご教示お願いします。
(複雑な式になっても構いません)

よろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (4件)

VLOOKUP関数でできないこともないでしょうがかなり複雑になりますね。


次のように作業列を作って対応します。
シート1のC1セルに次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",A1&"/"&COUNTIF(A$1:A1,A1))

次にシート2のB1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(Sheet1!C:C,A1&"/"&1)=0,"",INDEX(Sheet1!B:B,MATCH(A1&"/"&1,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&2)=0,"","・"&INDEX(Sheet1!B:B,MATCH(A1&"/"&2,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&3)=0,"","・"&INDEX(Sheet1!B:B,MATCH(A1&"/"&3,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&4)=0,"","・"&INDEX(Sheet1!B:B,MATCH(A1&"/"&4,Sheet1!C:C,0)))&IF(COUNTIF(Sheet1!C:C,A1&"/"&5)=0,"","・"&INDEX(Sheet1!B,B,MATCH(A1&"/"&5,Sheet1!C:C,0)))

この式では複数のデータが5個まであるケースに対応しています。
    • good
    • 8
この回答へのお礼

勉強になりました!
ありがとうございました!!!

お礼日時:2010/06/16 19:57

該当データの出現個数が一定数であれば関数の組み合わせや作業セルを多用することで対処可能ですが、計算式はとて無複雑なものになります。



出現個数が未知数の場合は想定範囲外の個数データーがあった場合は正しい結果にはなりません。

VBAを利用した抽出方法が適していますが、マクロは利用可能な環境でしょうか?
    • good
    • 1
この回答へのお礼

回答遅くなり申し訳ございません。
確かにVBAであれば適切なのでしょうが、訳あってマクロ使用ができない状態でした。

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

お礼日時:2010/06/16 19:56

VLOOKUP関数だけで対応するなら以下のようなVLOOKUP数式で2つ目以降のデータを表示することができます(C1セルに検索値で。



2つ目のデータ
=VLOOKUP(C1,OFFSET($A$1,MATCH(C1,$A:$A,0),0,100,2),2,0)

3つ目のデータ
=VLOOKUP(C1,OFFSET(A3,MATCH(C1,A:A,0)+MATCH(C1,OFFSET(A1,MATCH(C1,A:A,0),0,100,1),0),0,100,2),2,0)

この数式をIF関数を使ってつなげれば1つのセルに検索結果を表示することが可能です。

配列数式を使うなら以下のような関数になります(A列のデータ範囲に「データ」と名前が付けてある場合)

=SUBSTITUTE(TRIM(VLOOKUP(C1,A:B,2,0)&" "&INDEX(B:B,SMALL(INDEX((データ<>C1)*1000+ROW(データ),),2))&" "&INDEX(B:B,SMALL(INDEX((データ<>C1)*1000+ROW(データ),),3))&" "&INDEX(B:B,SMALL(INDEX((データ<>C1)*1000+ROW(データ),),4)))," ","・")

上記の数式は配列を多用していますので、入力セルが多くなると動きが重くなるので、上のVLOOKUP式などでエラー処理をしない数式などと適宜使い分けてください。
    • good
    • 2
この回答へのお礼

こちらも勉強になりました!
ありがとうございました!!

お礼日時:2010/06/16 19:59

こんばんは!


VLOOKUP関数で複数のデータは抽出できないと思います。
VLOOKUP関数とMATCH関数での検索は出来ますが、一番上のデータしかヒットしないはずです。

そしてヒットした複数データを一つのセルに表示させたいということですが
これも関数では無理だと思います。

ただし、一つの案として↓の画像のように列方向にヒットしたものを表示させることは可能です。

Sheet2のB2セルに
=IF($A2="","",IF(COUNTIF(Sheet1!$A$2:$A$1000,$A2)<COLUMN(A1),"",INDEX(Sheet1!$B$2:$B$1000,SMALL(IF(Sheet1!$A$2:$A$1000=$A2,ROW($A$1:$A$999)),COLUMN(A1)))))

(Sheet1の1000行目まで対応できる数式にしています。)
これは配列数式になってしまいますので、
この画面からSheet2のB2セルに貼り付け後、F2キーを押す、又はB2セルでダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので
Shift+Ctrlキーを押しながらEnterキーで確定してみてください。
数式の前後に{ }マークが入り配列数式になります。

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

どうしもて一つのセルに納めたいのであれば、
これを CONCATENATE関数か & でまとめていくくらいしか思い浮かびません。

以上、参考になればよいのですが
的外れなら読み流してくださいね。m(__)m
「EXCEL2003で、Vlookupの検」の回答画像1
    • good
    • 6
この回答へのお礼

図解説明までして頂き、大変感謝します!
このような回答方法であれば、分かりやすくて助かります。
内容としては、求めていたものとは違い残念ですが、
とても勉強になりました。ありがとうございました。

お礼日時:2010/06/16 20:01

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QA列とB列の文字列を検索 結果をA列セル色で変化

めぐみと申します。
エクセルのマクロのことで質問させて頂きます。
少し複雑ですみません。

A列とB列に文字列があります。
A列に並んでいる文字を1つずつ検索してB列にその文字列があればA列のその文字があるセルを赤色にするというマクロはできますでしょうか?

お時間のある方がいらっしゃりましたら教えて頂ければ幸いです。
めぐみ

Aベストアンサー

別にマクロなど使わなくても条件付き書式でできますが?

手順:
A列を列選択する
A1セルがアクティブセルになっている事を確認する
Excel2003までは書式メニューから条件付き書式を開始する
セルの値が → 数式が に変えて
右の空欄に
=MATCH(A1,B:B,0)
と記入し,書式ボタンで赤く塗る。

Excel2007以降を使っているならホームタブで条件付き書式▼から新しいルールを開始
数式を使用して…を選んで
下の空欄に
=MATCH(A1,B:B,0)
と記入し,書式ボタンで赤く塗る。




そこを敢えてどうしてもマクロが使いたいんだというご相談なら。
ALT+F11を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
 dim r as long
 for r = 1 to range("A65536").end(xlup).row
  if cells(r, "A") <> "" then
  if application.countif(range("B:B"), cells(r, "A")) > 0 then
   cells(r, "A").interior.colorindex = 3
  else
   cells(r, "A").interior.colorindex = xlnone
  end if
  end if
 next r
end sub

別にマクロなど使わなくても条件付き書式でできますが?

手順:
A列を列選択する
A1セルがアクティブセルになっている事を確認する
Excel2003までは書式メニューから条件付き書式を開始する
セルの値が → 数式が に変えて
右の空欄に
=MATCH(A1,B:B,0)
と記入し,書式ボタンで赤く塗る。

Excel2007以降を使っているならホームタブで条件付き書式▼から新しいルールを開始
数式を使用して…を選んで
下の空欄に
=MATCH(A1,B:B,0)
と記入し,書式ボタンで赤く塗る。




そこを敢えてどうしてもマクロが使いた...続きを読む

Qエクセルで A列に商品名 B列に商品番号 C列に管理者 が入っています

エクセルで A列に商品名 B列に商品番号 C列に管理者 が入っています。

A B C
あ 001 佐藤
い 002 鈴木
う 003 田中
え 004 伊藤
お 005 三木

以下600行くらい続いています。
商品名は重複していません。

D列を空列にしE列に商品名をいくつか並べたときに、
F列とG列に商品番号と管理者を表示させたいのですが、
どの関数を使えばいいのかよくわかりません。

環境はエクセル2003です。

Aベストアンサー

こんにちは。

 下記サイトをご参照ください。
  http://allabout.co.jp/gm/gc/3143/
  VLOOKUP関数で一覧表から検索できます。

では。

QエクセルのA列で同じデータがあった場合、C列にB列のデータを合体して表示

エクセルのA列で同じデータがあった場合、C列にB列のデータを合体して表示、…というと説明不足ですが、要は以下のような感じです。
A    B
あああ a
あああ b
いいい c
ううう d
えええ e
いいい f

このときC列には

A    C
あああ ab
あああ ab
いいい cf
ううう d
えええ e
いいい cf

となるようにしたいです。
ご教授ください。

Aベストアンサー

No.2です!
たびたびお邪魔します。

重複の数が多い場合にも対応できる方法をお望みみたいなので、
一気にやろうとすると途方もなく長い数式しか思いつきませんので
↓の画像のように作業用の表を使う方法はどうでしょうか?

とりあえず、データが1000行目まで対応できる方法です。
作業列E2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"")
という数式を入れ、オートフィルでずぃ~~~!っと下へコピーします。
(これは作業用の表の列方向にA列の項目を重複なしに表示するためです。)
そして、F2セルに
=IF(COUNT($E$2:$E$1000)<COLUMN(A1),"",INDEX($A$2:$A$1000,SMALL($E$2:$E$1000,COLUMN(A1))))
という数式を入れ列方向(右方向)のオートフィルでコピーします。

F3セルに
=IF(OR(F$2="",COUNTIF($A$2:$A$1000,F$2)<ROW(A1)),"",INDEX($B$2:$B$1000,SMALL(IF($A$2:$A$1000=F$2,ROW($A$1:$A$999)),ROW(A1))))
(この数式だけが配列数式になりますので、前回同様に
Shift+Ctrl+Enterキーで確定してください。)

これを列方向と行方向にオートフィルでコピーします。

そして、F16セルに(画像ではF16セルにしていますが、何行目でも構いません)
=CONCATENATE(F3,F4,F5,F6,F7,F8,F9,F10,F11,F12)
という数式を入れ、列方向にコピーします。
とりあえず、重複が10あっても大丈夫です。

最後にC2セルに
=IF(A2="","",A2&INDEX($F$16:$K$16,,MATCH(A2,$F$2:$K$2,0)))
という数式を入れ、オートフィルで下へコピーしています。

以上、長々と書いた上に、結構手間がかかる方法ですが
参考になれば幸いです。
他に良い方法があれば読み流してくださいね。m(__)m

No.2です!
たびたびお邪魔します。

重複の数が多い場合にも対応できる方法をお望みみたいなので、
一気にやろうとすると途方もなく長い数式しか思いつきませんので
↓の画像のように作業用の表を使う方法はどうでしょうか?

とりあえず、データが1000行目まで対応できる方法です。
作業列E2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"")
という数式を入れ、オートフィルでずぃ~~~!っと下へコピーします。
(これは作業用の表の列方向にA列の項目を重複なしに表示するためです。)
そして、F2セ...続きを読む

QエクセルでA列をB列の数だけ縦に並べたデータ

添付画像を見ていただいて、A列の名前をB列の数だけ縦に並べたD列のようなデータを作りたいのですが、関数などでできるでしょうか。

詳しく質問を書きます
A2が佐藤でB2が2なので佐藤を縦に2つ並べて、その下にA3が鈴木でB3が1なので鈴木を縦に1つ並べていくというものです。名前の数は100名前後になると思います、B列の数は1~4です。

D列のようなデータを作るために毎回コピー&ペーストをしています。
もし自動でできるようになれば仕事の能率が格段にアップすると思い質問をさせていただきました。
回答よろしくお願いします。

Aベストアンサー

D1やD2セルでもよいのですが、先の式よりも次の式を入力して下方にオートフィルドラッグコピーすればよいですね。

=IF(ROW(A1)>MAX(C:C),"",INDEX(A:A,IF(ROW(A1)<=B$2,2,IF(COUNTIF(C:C,ROW(A1)),MATCH(ROW(A1),C:C,0),MATCH(ROW(A1),C:C,1)+1))))

QA列の文字列によってB列への入力を変える

こんにちは。マクロ初心者で、質問なのですが、
エクセルで、A列に入力されている文字列によって、
B列文字を自動で入力するマクロを作成したいのですが、
どのように記述すればよいでしょうか?

例:
A列 |B列 |
 あ | 01 |A列「あ」の場合、B列の同じ行に「01」
 い | 02 |A列「い」の場合、B列の同じ行に「02」

といった感じです。
初歩的な質問で申し訳ございませんがご教授願います。

Aベストアンサー

意味がわかりませんが,このマクロではダメだと言うことですね。

#参考
A列にデータ記入済みのシートに,回答の通りにマクロを取り付ける
A列のデータをコピーし,その場で貼り付ける
と,A列のデータを再入力したことになりマクロが実行される
以後はA列を編集する都度,直ちにB列に反映する




>マクロの実行をおこなってからマクロが動作する方がありがたかったです

やることは一緒なので,あとはあなたが実際にヤリタイコトに応じて適切に応用してください。

手順:
前回回答のマクロを全て漏れなく消去する

改めてALT+F11でVBE画面を出す
挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
 dim h as range
 on error resume next

 for each h in application.intersect(activesheet.usedrange, range("A:A"))
 select case h.value
 case "あ"
  cells(h.row, "B") = "'01"
 case "い"
  cells(h.row, "B") = "'02"
 case else
  cells(h.row, "B").clearcontents
 end select
 next
end sub

ファイルメニューから終了してエクセルに戻る
マクロを実行する。

意味がわかりませんが,このマクロではダメだと言うことですね。

#参考
A列にデータ記入済みのシートに,回答の通りにマクロを取り付ける
A列のデータをコピーし,その場で貼り付ける
と,A列のデータを再入力したことになりマクロが実行される
以後はA列を編集する都度,直ちにB列に反映する




>マクロの実行をおこなってからマクロが動作する方がありがたかったです

やることは一緒なので,あとはあなたが実際にヤリタイコトに応じて適切に応用してください。

手順:
前回回答のマクロを全て漏れな...続きを読む


このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング

おすすめ情報