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には拘りません。他の関数や、複数の関数を組み合わせても大丈夫です。
関数を使ってできるようであればご教示お願いします。
(複雑な式になっても構いません)
よろしくお願いします。
No.3ベストアンサー
- 回答日時:
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個まであるケースに対応しています。
No.4
- 回答日時:
該当データの出現個数が一定数であれば関数の組み合わせや作業セルを多用することで対処可能ですが、計算式はとて無複雑なものになります。
出現個数が未知数の場合は想定範囲外の個数データーがあった場合は正しい結果にはなりません。
VBAを利用した抽出方法が適していますが、マクロは利用可能な環境でしょうか?
回答遅くなり申し訳ございません。
確かにVBAであれば適切なのでしょうが、訳あってマクロ使用ができない状態でした。
ありがとうございました。
No.2
- 回答日時:
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式などでエラー処理をしない数式などと適宜使い分けてください。
No.1
- 回答日時:
こんばんは!
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
図解説明までして頂き、大変感謝します!
このような回答方法であれば、分かりやすくて助かります。
内容としては、求めていたものとは違い残念ですが、
とても勉強になりました。ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Microsoft365で提供しているアプリ
-
会社PCのメールが更新されない
-
outlookのメールが固まってしま...
-
teams設定教えて下さい。 ①ビデ...
-
Windows 11で、IME言語バー(IM...
-
Microsoft Officeを2台目のPCに...
-
英数字のみ全角から半角に変換
-
Outlook で宛先が複数の場合の人数
-
【Excel VBA】PDFを作成して,...
-
Microsoft Formsの「個人情報や...
-
マクロ1があります。 A1のセル...
-
Officeを開くたびの「再起動メ...
-
Office 2021 Professional Plus...
-
会社のTeamsのことで相談です。...
-
【Excel】セル内の文字が正しい...
-
Excel テーブル内の空白行の削除
-
WindowsのPCで単純な勤怠管理を...
-
エクセルでレーダーチャートの...
-
【マクロ】違うブックのCallス...
-
Excel 日付を比較したら、同じ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
英数字のみ全角から半角に変換
-
outlookのメールが固まってしま...
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
大学のレポート A4で1枚レポー...
-
Officeを開くたびの「再起動メ...
-
Microsoft Formsの「個人情報や...
-
one drive使えるpcを買う
-
マクロ自動コピペ 貼り付ける場...
-
PDFのハイパーリンクを自動的に変更し...
-
あらかじめ用意したテンプレー...
-
別シートの年間行事表をカレン...
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
-
office365って抵抗感ないですか?
-
ステータスバーの合計に表示さ...
-
【スプレドシート】IF関数の複...
-
Teamsで課題を提出します。 画...
-
Outlook で宛先が複数の場合の人数
-
Microsoft Officeを2台目のPCに...
おすすめ情報