エクセル、日付別に複数検索値からデーターを表示（VLOOKUP)させるには？

質問

資材部門に働いておりますが、月または週毎に独自で納品チェック表作成にVLOOKUP関数等を用いております。下記のような構成でデーターベースがあるのですが、日々、変更変動が激しく、該当日の車種のタイヤとその番号を表示させたいのですが、今までは日付を無視できたのですが、日付毎に分けなくてはならなくなり、上手くいかなくなりました。

　　　　　Ａ　　　　Ｂ　　　　　Ｃ　　　　Ｄ　　　
　１　日付　　　車種　　　部品　　　番号
　２　４／３　　ムーヴ　　タイヤ　　ＢＳ１
　３　４／３　　ムーヴ　　タイヤ　　ＴＹ２
　４　４／３　　タント　　タイヤ　　ＤＬ１
　５　４／３　　タント　　タイヤ　　ＢＳ２
　６　４／３　　エッセ　　タイヤ　　ＹＨ１
　７　４／３　　エッセ　　タイヤ　　ＹＨ２
　８　４／４　　タント　　タイヤ　　ＢＳ３
　９　４／４　　ムーヴ　　タイヤ　　ＤＬ３
１０　４／５　　ミ　ラ　　タイヤ　　ＢＳ２
１１　４／５　　ミ　ラ　　タイヤ　　ＢＳ１
１２　４／５　　エッセ　　タイヤ　　ＤＬ１
１３　４／５　　タント　　タイヤ　　ＴＹ２
　↓　　↓　　　　↓　　　　↓　　　　↓
　従来は、この表からタイヤの番号を導くため、
　=VLOOKUP(B2&C2,B2:C10000,3,0)の式を基本にIFなどをアレンジを加えて何とか、導けていましたが、日付が加わったのでどうすれば良いでしょうか?
　VLOOKUP関数は同じ検索値だと最初の行だけが抽出されると言うのは知っています。
　上記の表の構成で○月○日の○○車種のタイヤの番号を完全一致で表示させるにはどのような式を立てればよいでしょうか?私は時刻日付関数は苦手で、表示形式なども絡み、どうも理解が出来ておりません。
　参考書をヒントに考えているのですが、IF,COUNTIF,INDEX,ROW,CORMINなどが複合されて長い式になり、頭で整理が付きません。
　どなたか、教えていただけないでしょうか?

回答No.6

nicotinism · ベストアンサー

VBA（マクロ）での回答は余計かな？
Sheet１に検索するデータがあるとして、こんな風だとします。
セルの区切りを , カンマで表しています。
日付,車種,部品,番号
4月3日,ムーヴ,タイヤ,ＢＳ１
2007/4/3,ムーヴ,タイヤ,ＴＹ２
2007/4/3,タント,タイヤ,ＤＬ１
2007/4/3,タント,タイヤ,ＢＳ２
2007/4/3,エッセ,タイヤ,ＹＨ１
2007/4/3,エッセ,タイヤ,ＹＨ２
2007/4/4,タント,タイヤ,ＢＳ３
4月4日,ムーヴ,タイヤ,ＤＬ３
4月5日,ミラ,タイヤ,ＢＳ２
4月5日,ミラ,タイヤ,ＢＳ１
4月5日,エッセ,タイヤ,ＤＬ１
4月5日,タント,タイヤ,ＴＹ２

でSheet2が
　　A,B,C,D
1　日付,車種,部品,番号
として
２行目に検索したいデータを入れるとします。こうなります。
　　A,B,C,D
1　日付,車種,部品,番号
2　4月3日,タント,タイヤ

名前をつけて一旦保存して下さい。
Alt + F11 を押して VBE のウィンドウに切り替えます
メニューの挿入から標準モジュールを選びます
新しいウィンドウが開きますので下記をコピペ

Sub ListUp()
　　Dim cnXL As Object
　　Dim rsXL As Object
　　Dim strSql As String
　　Dim wS1 As Worksheet, wS2 As Worksheet
　　Const adOpenForwardOnly = 0

　　Set wS1 = Worksheets("sheet1")　'検索データのあるシート
　　Set wS2 = Worksheets("sheet2")　'検索作業をするシート

　　If WorksheetFunction.CountA(wS2.Range("A2:C2")) < 3 Then
　　　　MsgBox "検索内容に未記入があります"
　　　　Exit Sub
　　End If

　　'以前の検索結果をクリア
　　Range(wS2.Range("A3:D3"), wS2.Range("A3:D3").End(xlDown)).ClearContents

　　Set cnXL = CreateObject("ADODB.Connection") '←↓ADOで接続するおまじない

　　With cnXL
　　　　.Provider = "MSDASQL"
　　　　.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
　　　　"DBQ=" & ThisWorkbook.FullName & "; ReadOnly=True;"
　　　　.Open
　　End With

　　Set rsXL = CreateObject("ADODB.Recordset")
　　strSql = "select 日付,車種,部品,番号 from [" & wS1.Name & "$]" _
　　　　　　　　& " where 日付 = #" & wS2.Cells(2, 1) & "#" _
　　　　　　　　& " and 車種 like '%" & wS2.Cells(2, 2) & "%'" _
　　　　　　　　& " and 部品 like '%" & wS2.Cells(2, 3) & "%'"
　　'↑車種と部品は部分一致検索なので完全一致なら like を = に、% は削除
　　rsXL.Open strSql, cnXL, adOpenForwardOnly

　　wS2.Cells(3, 1).CopyFromRecordset rsXL　'レコードセットをCells(3, 1)を起点にしてコピー
　　wS1.Range("A:A").NumberFormatLocal = "m""月""d""日"";@"　Sheet1 A列の書式を復元

　　rsXL.Close: Set rsXL = Nothing
　　cnXL.Close: Set cnXL = Nothing
End Sub

で、Alt + F11 を押して Excel のウィンドウに切り替えます
Alt + F8 を押すと ListUp というのが有りますので、「実行」をクリック
下記のような結果が得られると思います。
日付,車種,部品,番号
2007/4/3,タント,タイヤ　　　　　←検索値
2007/4/3,タント,タイヤ,ＤＬ１　←結果
2007/4/3,タント,タイヤ,ＢＳ２　←結果

Sheet2の
　　A,B,C,D
1　日付,車種,部品,番号
2　4月3日,タント,タイヤ　　←日付、車種、部品を変えてAlt + F8　実行で色々お試しを

※提示された例題では、ミ　ラ となっていますがミラとスペースを削除しています
おそらく表示上のためかと思いますが
品名の前後や間にスペースがあると期待した結果は得られません。ご注意を。
また、
Sheet1 の 日付が 4月3日 → 2007/4/3 と変わってしまうのを防ぐ手立ては分かりません
wS1.Range("A:A").NumberFormatLocal = "m""月""d""日"";@" で元に戻しています

回答No.5

noname#95859

申し訳ないですが、何を導けばよいのか、もう一度説明していただけませんか？

４／３　　ムーヴ　　タイヤ　　ＢＳ１
４／３　　ムーヴ　　タイヤ　　ＴＹ２

[４／３　　ムーヴ　　タイヤ]に対し、番号が「ＢＳ１」と「ＴＹ２」
の2つ在ります。vlookupをつかうなら、最初に一致したものだけで、
この場合、「ＴＹ２」は、無視されることになります。
これであるなら、ANo.3さんの案は、有効です。1日の違いは、何とかなると思います。　[４／３　　ムーヴ　　タイヤ]には、２つの番号があるということを、知りたいのであれば、ANo.4さんの案は、有効だと思います。

多分、もうひとつキーがあると考えます。例えば、「顧客名」とか。

何が、入力で、何を出力とするのかを、明確にしていただけませんか？

回答No.4

nishi6

実際のテーブルがどのようになっているかによりますが、ピボットテーブルを使ってみます。
あまり巨大な表の場合、できないかもしれません。

１．データ（テーブル）の内部を１箇所選んだ状態で、
　　データ→ピボットテーブルとピボットグラフ レポート
２．ピボットテーブル/ピボットグラフウイザード - 1/3 で 次へ
３．ピボットテーブル/ピボットグラフウイザード - 2/3 で 対象データ範囲が選択されているはずなので 次へ
４．ピボットテーブル/ピボットグラフウイザード - 3/3 で レイアウトをクリック
　４－１．ピボットテーブル/ピボットグラフウイザード - レイアウト　で
　　　　日付、車種、番号の順に行にドラッグ＆ドロップ
　　　　部品を列にドラッグ＆ドロップ
　　　　番号をデータにドラッグ＆ドロップ（個数になるはず）
　　　　ＯＫ

これでピボットテーブルができます。

５．グレーの表題の『日付』と『車種』をA1セルあたりにドラッグ＆ドロップします
　　これで、『日付』と『車種』を指定できるようになっているはずです
　　『部品』も指定できるようになっているはずです（部品はタイヤ以外もある？）

データが増えたら、ピボットテーブルで右クリックし、ウイザード→戻る　でデータ範囲の行を変更します。

算式を使わず、内容を見ることができます。データベースは本来、そういうものでしょう。
カスタマイズすれば、自分専用のピボットテーブルが作れるでしょう。

回答No.3

mshr1962

=VLOOKUP(TEXT(A2,"y年m月")&B2&C2,範囲,2,0)

範囲は
Ａ　　　　　　　　　　　　　Ｂ　　　　Ｃ　　　　Ｄ　　　Ｅ
検索キー　　　　　　　　　　　日付　　　車種　　　部品　　番号
=TEXT(B2,"y年m月")&C2&D2 ４／３　　ムーヴ　　タイヤ　　ＢＳ１
のようにすれば可能ですが

補足

回答有難うございます。
解説いただいた方法を自分でも試行したのですが、上手くタイヤ番号を表示させられませんでした。
　日付セルの表示（標準、数値,文字列）に問題があるのではないかと色々変えてはしていますが、困ったことに、質問の納品ベース表と別に現場用の実際の生産予定表とは1日のズレがあり、（４／３　　ムーヴ　　タイヤ　　ＢＳ１では納品は4月3日なのですが、現場で使われるのは4月4日になるため、日付を-1にして完全一致したいのですが、日付を2007/4/3,4/3,39175のシリアルにしたりしていますが、上手く行きません。納品予定表と現場生産予定表は項目、順序、種類は全く同じなのですが、1日のズレがあるために、手を焼いています。

回答No.2

bari_saku

１）A列の左側に1列挿入
２）新A列に、日付・車種・部品を入力
　=B1&C1&D1
３）新A列の内容をキーとしてVLOOKUPをかける

エクセルで実際に試してませんが、パッと思いつく方法はこんなとこでしょうか。
当方システム屋ですが、大企業のコンピュータシステムも、考え方は全く同じです。

回答No.1

picklse

オートフィルタを使ってはどうでしょうか。

[データ]メニュー→フィルタ→オートフィルタ