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と関連する良く見られている質問

QExcelですが、同一データが複数あるとき、検索して、その全部を抽出する方法

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお願いします。

<データシート>
 列A  列B    列C 列C
行1 No 会社名   所属 担当者
行2 1 A株式会社  ○事業部  坂下順人
行3 2 A株式会社  △事業部  滿山友人
行4 3 A株式会社  △事業部  目標達子
行5 4 B株式会社  設計部山下清人
行6 5 B株式会社  営業部横浜美人
行7 6 C株式会社  営業部川崎次郎
行8・・・・・・・・・・・・

<検索結果:別のシートにおいて>
行1 会社名 所属  担当者
行2 A株式会社 ○事業部 坂下順人
行3 A株式会社 △事業部 滿山友人
行4 A株式会社 △事業部 目標達子

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお...続きを読む

Aベストアンサー

#03です
>最後のROW(T1)で、T1を参照しておりますが

ROW(T1)は「1番目」を意味しています。T1セルに何もなくても関係ありません。コピーしたの行はROW(T2)になりますが、これは2番目のという意味です

#REF!エラーになりますか?
私が示した条件と何か変えていませんか?
別シートのA1に「抽出する会社名」を入力してあれば、回答したような結果になるはずですが‥ (テストしてありますので)

ただし2000行もあるならVBAの方が良いかもしれません
きっと「シートが重くなる」と思います

QexcelのVLOOKUPで検索値を2つにできますか?

excelのVLOOKUPで検索値を2つにしたいです
私の知っているVLOOKだと下記のことは対応できます
例えば、A列に会社名、B列に住所、C列に電話番号 とあった場合
「住所が検索値と同一なら電話番号を表示しなさい」という指示は出せます

そこで質問です
「会社名と住所が検索値と同一なら電話番号を表示しなさい」
というような、複数の検索値を持つ事はできないのでしょうか?

参考になるURLなどでも結構ですので、ご存知の方よろしくお願いいたします

Aベストアンサー

元の表をA列に会社名、B列に住所、C列に「=A2&B2」、D列に電話番号のようにして
=VLOOKUP(会社名&住所,$D$2:$C$100,2,FALSE)
のようにすれば可能です。

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QVLOOKUP検索結果が複数の場合の表示方法

はじめまして。

VLOOKUPで検索した結果が複数ある場合、全ての結果を表示させたいのですがご教授願います。表示方法は問いません。

宜しくお願いします。

Aベストアンサー

一例です。(相違していましたら読み飛ばして下さい)
仮に表A1:B200、検索値をC1としてD1に以下の数式を設定して下方向に必要分コピーします。
尚、配列数式の為、入力完了時にshift+ctrl+enterキーを同時押下して下さい。
=IF(ROW(A1)-1<COUNTIF($A$1:$A$200,$C$1),INDEX(B:B,SMALL(IF($A$1:$A$200=$C$1,ROW($A$1:$A$200),99999),ROW(A1))),"")

Qエクセルで重複しているデータの抽出のしかたを教えてください。

エクセルで重複しているデータを抽出したいのですが・・。なにぶん初心者なもので簡単な方法があればよいのですが・・。
A列に日付 B列に企業名このデータが300件ほどあります。

 Å列      B列
1月 1日    AAAA社
1月 4日   BBBB社
1月 8日   CCCC社
2月 1日   BBBB社
2月20日    DDDD社
3月 2日   AAAA社

と、あるとしてB列の企業名が重複しているものを抽出したいのですが・・・。この場合 AAAA社とBBBB社ですが。
これを簡単に抽出する方法はないでしょうか?
教えてください お願いいたします。

Aベストアンサー

企業名がセルB2からセルB300まで入力されていると仮定します。
セルC2に IF(COUNTIF($B$2:B2,B2)>1,"*","") を入力し、セルC300までコピーして下さい。すると重複したデータの二番目以降に "*" のマークが付きます。
(注) 数式を下のセルまでコピーするため、COUNTIF($B$2 は絶対参照にして下さい。

QExcel MATCH関数で検索範囲内に同じ値の検索値が複数ある場合

MATCH関数で、検索した値が複数ある場合に1つしか出なくて困っています。
(例)
   A B C D
1 年月日 種類 番号 備考
2 4月1日 肉類 1
3 4月2日 野菜 2
4 4月3日 肉類 1
5 4月4日 果物 2
6 4月5日 野菜 1
7 4月6日 果物 3
8 4月7日 果物 2
9 4月8日 肉類 1

この表で、D9の備考の欄に「種類:肉類、番号:1」と同じものがあった時の日付を、新しい日付で取り出したいのです。

INDEX(A2:A8,(MATCH(B9,B2:B8,0)+MATCH(C9,C2:C8,0))/2)
としたのですが、本当は4月3日を取り出したいのですが、4月1日が出てきます。
どのようにしたら良いでしょうか?
Excel2003です。
よろしくお願いします。

Aベストアンサー

MATCH関数を使用しませんが、D9の書式を日付にして、
=MAX((A2:A8)*(C2:C8=1)*(B2:B8="肉類"))
と、入力してShift+Ctrl+Enterキーで入力完了してみてください(配列式になります)。

Qvlookup関数で検索値を含む文字列を検索する方法

vlookup関数で例えば E1のセルに=vlookup(D1,A:C,3,false)とした場合、D1が佐藤であれば、A列に「佐藤」がある場合には当然、「佐藤」がある行の3列目の値が返されますが、「佐藤」はなく「佐藤A」や「A佐藤」がある場合にもこれらがある行の値を返して欲しいのですが、いい方法はありませんでしょうか。*を使ってできると思ったのですがうまくいきません。上記例で、=vlookup("*佐藤*",A:C,3,false)とすればできますが、*佐藤*の部分はD1の引用を利用したいのです。
 よろしくお願いします。

Aベストアンサー

=VLOOKUP("*"&D1&"*",A:C,3,FALSE)

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む


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

人気Q&Aランキング

おすすめ情報