エクセルで 複数のデータを検索して複数表示させたいのですが・・・。

質問

エクセルで在庫の場所がチェックできる下記のような物を作りたいと考えています。

在庫リスト　シート1
　　　　　　Ａ　　　　Ｂ　　　Ｃ　　
1行目　　商品名　　　色　　　場所
2行目　　あいう　　　赤　　　Ａ1
3行目　　かきく　　　黄　　　Ａ2
4行目　　さしす　　　青　　　Ａ3
5行目　　あいう　　　黄　　　Ａ4
6行目　　かきく　　　青　　　Ａ5
・
・
・
・

在庫リスト　シート2
　　　　　　Ａ　　　　Ｂ　　　Ｃ　　
1行目　　商品名　　　色　　　場所
2行目　　あいう　　　青　　　Ｂ1
3行目　　かきく　　　緑　　　Ｂ2
4行目　　さしす　　　桃　　　Ｂ3
5行目　　あいう　　　茶　　　Ｂ4
6行目　　かきく　　　桃　　　Ｂ5
・
・
・
・

上のような表を作成し、別のシート（シート3）で　検索名　あいう　と打ち込むと　

　赤　　Ａ１
　黄　　Ａ４
　青　　Ｂ１
　茶　　Ｂ４

という具合に同じ商品を検索し、色と場所を全て表示させるようなものをエクセルで作成できたらと思っています。可能でしょうか？また、可能であればどのような関数を使って作成したらよいのかを教えていただきたいです。vlookupが使えるかと思ったのですが、それだと一番上のデータ（この例でいけば　赤　Ａ１）のみの表示になってしまい、使えませんでした。

あと、欲を言えば、　商品名を全て打ち込みをしなくても、一部のみ（この例でいけば　「あいう」　ではなくて　「あい」のみ）を打ち込むだけでその字を含む商品名を検索し、表示してくれるような表記の仕方があるともっとありがたいです。在庫リストは在庫の場所ごとに複数のシートに分かれています。

よろしくお願いします。

回答No.6

id-sakura

Sub 商品検索()
Dim Shouhin As String　<----------------------------------Inputboxに入れる値を決める
Dim zaikoSheet As Worksheet<-----------------------------------それぞれのWorksheetをzaikosheetと決めた　　　　　
Dim kashiraMoji As String<-------------------------------------在庫リストの頭文字の値を決める
Dim i As Integer<-------------------------------------------1,2,3,と変化する値
Shouhin = Application.InputBox(prompt:="商品名を入力して下さい。", Title:="在庫検索")　<------------putboxに入れた値をとりだす
For Each zaikoSheet In Worksheets<-----------------------------すべてのシートに以下処理をする
If zaikoSheet.Name = "検索シート" Then　　
Exit For　<----------------------------------------------------もしシート名が"検索シート"になったら処理を止める
Else　 <------------------------------------------------それ以外は
i = 1 <--------------------------------------------------まず変化する値を1と決める
Do Until zaikoSheet.Cells(i + 2, "b").Value = ""<-------------シートのB列3行目以降のセルが空になるまで以下の処理を繰り返す
kashiraMoji = Left(zaikoSheet.Cells(i + 2, "b").Value, 1)<----KashiraMojiと言うのはB列3行目以降の左から1番目の文字だよと決めた
If kashiraMoji = Shouhin Then<-------------------------------頭文字と商品が同じだったらその時は
Worksheets("検索シート").Activate<----------------------------最後のシートをアクティブにして
Range("B2").End(xlDown).Offset(1).Value = zaikoSheet.Cells(i + 2, "b").Value　<---Ｂ列の表の一番最終行にヒットしたセルの値を入れる
Range("c2").End(xlDown).Offset(1).Value = zaikoSheet.Cells(i + 2, "c").Value<---C列の表の一番最終行にヒットしたセルの値を入れる
Range("d2").End(xlDown).Offset(1).Value = zaikoSheet.Cells(i + 2, "d").Value<---D列の表の一番最終行にヒットしたセルの値を入れる
Else <--------------------------------------------------------それ以外(頭文字と商品が違う場合)は何もしない
End If <------------------------------------------------------値がなくなったら終わりにして下さい
i = i + 1　<--------------------------------------------------- 1を２にして
Loop　　<-----------------------------------------------------繰り返す(Do untillの行へ戻る)
End If <-------------------------------------------------------シート名が検索シートなら終わる
Next　<--------------------------------------------------------シート名が検索シートでなければ次ぎのシートに進む
End Sub

私はマクロの初心者ですが質問のようなマクロだと簡単な記述で出来そうだったので作ってみました。もしかしたら何かの参考になるかと思い勉強も兼ねて作ってみました。　<------------------------以下は補足です。
上記のマクロだとシートが何枚あっても商品がいくつあっても頭文字を入れれば目的の物だけを表示します
"検索シート"は最後のシートにしてください(もちろん他の名前でも良いです)
とりあえず"検索シート"のＢ列の最終行を探してヒットした項目を書き出すようにしました

　　　　　Ｂ列　　 Ｃ列　　　Ｄ列
2行目　　 商品 色 場所 <------見だし行
3行目　　 ＊＊＊ ＊＊＊ ＊＊＊<--------------------- ここ表の2行めに何か入れていないと最終行を探し出して書き出してくれない

回答No.5

mitarashi

#3です。部分一致なら、ヒットした値も表示しないといけませんね。
後半部分を下記のように変更して下さい。
For Each myCell In myArea.Cells
destrange.Value = myCell.Value
destrange.Offset(0, 1).Value = myCell.Offset(0, 1).Value
destrange.Offset(0, 2).Value = myCell.Offset(0, 2).Value
Set destrange = destrange.Offset(1, 0)
Next myCell

回答No.4

imogasi

（１）関数では条件により抜き出しは複雑になるということが有る上に
（２）２シートに検索対象が分かれている
（３）シート１の該当数が商品によって不定
（４）２シートが現実か？どうか質問では断言されていない。もっと多いのかも知れない。
そういうことで関数では難しい。
アクセスやエクセルVBAを勉強してください。
ーー
自称imogasi方式で
例データ
Sheet1
商品名 色 場所
あいう 赤 Ａ1 1
かきく 黄 Ａ2
さしす 青 Ａ3
あいう 黄 Ａ4 2
かきく 青 Ａ5
D2は　=IF(A2=Sheet3!$A$1,MAX($D$1:D1)+1,"")
以下複写
Sheet2
商品名 色 場所 2
あいう 青 Ｂ1 3
かきく 緑 Ｂ2
さしす 桃 Ｂ3
あいう 茶 Ｂ4 4
かきく 桃 Ｂ5
D1は　=MAX(Sheet1!$D$2:$D$100)
D2は　=IF(A2=Sheet3!$A$1,MAX($D$1:D1)+1,"")
式を下方向に複写
Sheet3
A1に　あいう　（指定条件入力）
A2に
=IF(ROW()-1<=MAX(Sheet1!$D:$D),INDEX(Sheet1!$A$1:$D$100,MATCH(ROW()-1,Sheet1!$D$1:$D$100,0),COLUMN()),INDEX(Sheet2!$A$1:$D$100,MATCH(ROW()-1,Sheet2!$D$1:$D$100,0),COLUMN()))
C2まで式複写。
A2:C2を下方向に式複写。
結果
あいう 赤 Ａ1
あいう 黄 Ａ4
あいう 青 Ｂ1
あいう 茶 Ｂ4
ーー
式が長くて苦しい
Sheet3のA２以下の「あいう」は出さないように出来る。
以下の＃N/Aを出さない式は略。Googlede[imogasi方式」で照会し、どれかの私の回答を見てください。

お礼

貴重なお時間を使ってご回答いただき、誠にありがとうございます。エクセル素人の為、順番にやらせていただいているので、お礼に時間がかかってしまい、申し訳ございません。教えていただいた情報は無駄にせず、参考にさせていただきます。また色々教えてください。よろしくお願い申し上げます。

回答No.3

mitarashi

最近検索に凝っていて、色々なバリエーションを試しています。関数ではありませんが、ご参考までに呈示します。検索部と、出力部を分けているのは、単なる趣味です。
検索を行うブックに、検索結果表示用のシート：search を設け、標準モジュールに下記のコードを入れます。このシート以外の全てのシートを検索対象とします。
セル配置は下記の通り。
........A......................B..................
3.......キーワード........................
4.......あい.....................................
5.......検索結果..........................
6.......黄....................Ａ4...............
7.......赤....................Ａ1...............
8.......茶....................Ｂ4...............
9.......青....................Ｂ1...............
Sub test()
Dim matchWord As Variant
Dim sh As Worksheet
Dim findResult As New Collection
Dim hitRange As Range, destrange As Range, findItem As Range
Dim myCell As Range, myArea As Range

matchWord = Sheets("search").Range("a4").Value
Set destrange = Sheets("search").Range("a6")
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "search" Then
Set hitRange = myFind(sh.UsedRange, matchWord, xlPart)
If Not hitRange Is Nothing Then
findResult.Add Item:=hitRange
End If
End If
Next sh
For Each findItem In findResult
For Each myArea In findItem.Areas
For Each myCell In myArea.Cells
destrange.Value = myCell.Offset(0, 1).Value
destrange.Offset(0, 1).Value = myCell.Offset(0, 2).Value
Set destrange = destrange.Offset(1, 0)
Next myCell
Next myArea
Next findItem
End Sub
'検索でヒットしたセル群を戻す関数
Private Function myFind(target As Range, findValue As Variant, findMode As Variant) As Range
Dim c As Range
Dim firstAddress As String

With target
Set c = .Find(findValue, LookIn:=xlValues, LookAt:=findMode, MatchCase:=False, MatchByte:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If myFind Is Nothing Then
Set myFind = c
Else
Set myFind = Union(c, myFind)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Function

お礼

貴重なお時間を使ってご回答いただき、誠にありがとうございます。エクセル素人の為、順番にやらせていただいているので、お礼に時間がかかってしまい、申し訳ございません。教えていただいた情報は無駄にせず、参考にさせていただきます。また色々教えてください。よろしくお願い申し上げます。

回答No.2

hallo-2007

元データが2枚あるのが厄介です。
作業列入れますが
シート３
　A　　　B　　　C
1あいう
2　１
3　２
・・・
シート３のA1に検索文字（あいう）入れます。

A2以下に　１，２，３・・と番号を入れておきます。
シート１のA列に1列挿入　=COUNTIF(B2:B$2,シート3!A$1)　下へコピィ
シート２も同様　=COUNTIF(B2:B$2,シート3!A$1)　下へコピィ
シート３のB2セルに　=IF(A3<=MAX(シート1!A:A),VLOOKUP(A3,シート!A:D,3,FALSE),VLOOKUP(A3-MAX(シート1!A:A),シート2!A:D,3,FALSE))
C2セルに　=IF(A3<=MAX(シート1!A:A),VLOOKUP(A3,シート!A:D,4,FALSE),VLOOKUP(A3-MAX(シート1!A:A),シート2!A:D,4,FALSE))
B２、C2セルを下へコピィしてみてください。

お礼

貴重なお時間を使ってご回答いただき、誠にありがとうございます。エクセル素人の為、順番にやらせていただいているので、お礼に時間がかかってしまい、申し訳ございません。教えていただいた情報は無駄にせず、参考にさせていただきます。また色々教えてください。よろしくお願い申し上げます。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

マクロの記録で試してみました

Sheet1においてシート1、シート2を連結します
1. シート1でA1セルを選択し、
1.5 マクロの記録の[相対参照]にする
2. [Ctrl]+[Shift]+[↓]
3. [Shift]+[→]
4. [Shift]+[→]
5. コピー
5.5 マクロの記録の[相対参照]を解除する
6. Sheet1のA1セルを選択して貼り付け
7. シート2でA1セルを選択し、
7.5 マクロの記録の[相対参照]にする
8. [Ctrl]+[Shift]+[↓]
9. [Shift]+[→]
10. [Shift]+[→]
11. コピー
12. Sheet1を選択する
13. [Ctrl]+[↓]
14. [↓]
15. 貼り付け
15.5(マクロの記録の[相対参照]を解除する)
16. [Esc]
以上をマクロの記録で記録します

また、このSheet1で名前も定義します
[Ctrl]+[F3]名前の定義
名前 桜
参照範囲 =$A$1:INDEX($C:$C,COUNTA($A:$A))

抽出するシートにおいて
A B C
1 商品名
2 か*
3
4 商品名 色 場所
まで書いておきます(A2セルは抽出条件)

1. 抽出するシートを選択する
2. データ - フィルタ フィルタオプションの設定
3-1. 抽出先 指定した範囲
3-2. リスト範囲 桜
3-3. 検索条件範囲 $A$1:$A$2
3-4. 抽出範囲 $A$4:$C$4
3-5. [OK]
以上をマクロの記録で記録します
[Alt]+[F11]VBEで上記で記録されたモジュールを連結します
ボタンなどにマクロを登録します

お礼

エクセル素人の為、実行に時間がかかり、お礼が遅れまして申し訳ございません。貴重なお時間を使ってご回答いただき、誠にありがとうございました。細かく説明書きをしていただき、大変わかりやすかったです。早速教えてもらった通りに試してみました。

最後の

＞[Alt]+[F11]VBEで上記で記録されたモジュールを連結します

の部分で、マクロ１とマクロ２の連結の仕方がどうしてもわかりませんでした。ネットで検索してみてもイマイチ答えが見つからず・・・＞＜

具体的な方法、又は、やり方が書いてあるようなURLがありましたら教えていただけると大変ありがたいです。

よろしくお願い申し上げます。