資材部門に働いておりますが、月または週毎に独自で納品チェック表作成にVLOOKUP関数等を用いております。下記のような構成でデーターベースがあるのですが、日々、変更変動が激しく、該当日の車種のタイヤとその番号を表示させたいのですが、今までは日付を無視できたのですが、日付毎に分けなくてはならなくなり、上手くいかなくなりました。
A B C D
1 日付 車種 部品 番号
2 4/3 ムーヴ タイヤ BS1
3 4/3 ムーヴ タイヤ TY2
4 4/3 タント タイヤ DL1
5 4/3 タント タイヤ BS2
6 4/3 エッセ タイヤ YH1
7 4/3 エッセ タイヤ YH2
8 4/4 タント タイヤ BS3
9 4/4 ムーヴ タイヤ DL3
10 4/5 ミ ラ タイヤ BS2
11 4/5 ミ ラ タイヤ BS1
12 4/5 エッセ タイヤ DL1
13 4/5 タント タイヤ TY2
↓ ↓ ↓ ↓ ↓
従来は、この表からタイヤの番号を導くため、
=VLOOKUP(B2&C2,B2:C10000,3,0)の式を基本にIFなどをアレンジを加えて何とか、導けていましたが、日付が加わったのでどうすれば良いでしょうか?
VLOOKUP関数は同じ検索値だと最初の行だけが抽出されると言うのは知っています。
上記の表の構成で○月○日の○○車種のタイヤの番号を完全一致で表示させるにはどのような式を立てればよいでしょうか?私は時刻日付関数は苦手で、表示形式なども絡み、どうも理解が出来ておりません。
参考書をヒントに考えているのですが、IF,COUNTIF,INDEX,ROW,CORMINなどが複合されて長い式になり、頭で整理が付きません。
どなたか、教えていただけないでしょうか?
No.6ベストアンサー
- 回答日時:
VBA(マクロ)での回答は余計かな?
Sheet1に検索するデータがあるとして、こんな風だとします。
セルの区切りを , カンマで表しています。
日付,車種,部品,番号
4月3日,ムーヴ,タイヤ,BS1
2007/4/3,ムーヴ,タイヤ,TY2
2007/4/3,タント,タイヤ,DL1
2007/4/3,タント,タイヤ,BS2
2007/4/3,エッセ,タイヤ,YH1
2007/4/3,エッセ,タイヤ,YH2
2007/4/4,タント,タイヤ,BS3
4月4日,ムーヴ,タイヤ,DL3
4月5日,ミラ,タイヤ,BS2
4月5日,ミラ,タイヤ,BS1
4月5日,エッセ,タイヤ,DL1
4月5日,タント,タイヤ,TY2
でSheet2が
A,B,C,D
1 日付,車種,部品,番号
として
2行目に検索したいデータを入れるとします。こうなります。
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,タント,タイヤ,DL1 ←結果
2007/4/3,タント,タイヤ,BS2 ←結果
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
- 回答日時:
申し訳ないですが、何を導けばよいのか、もう一度説明していただけませんか?
4/3 ムーヴ タイヤ BS1
4/3 ムーヴ タイヤ TY2
[4/3 ムーヴ タイヤ]に対し、番号が「BS1」と「TY2」
の2つ在ります。vlookupをつかうなら、最初に一致したものだけで、
この場合、「TY2」は、無視されることになります。
これであるなら、ANo.3さんの案は、有効です。1日の違いは、何とかなると思います。 [4/3 ムーヴ タイヤ]には、2つの番号があるということを、知りたいのであれば、ANo.4さんの案は、有効だと思います。
多分、もうひとつキーがあると考えます。例えば、「顧客名」とか。
何が、入力で、何を出力とするのかを、明確にしていただけませんか?
No.4
- 回答日時:
実際のテーブルがどのようになっているかによりますが、ピボットテーブルを使ってみます。
あまり巨大な表の場合、できないかもしれません。
1.データ(テーブル)の内部を1箇所選んだ状態で、
データ→ピボットテーブルとピボットグラフ レポート
2.ピボットテーブル/ピボットグラフウイザード - 1/3 で 次へ
3.ピボットテーブル/ピボットグラフウイザード - 2/3 で 対象データ範囲が選択されているはずなので 次へ
4.ピボットテーブル/ピボットグラフウイザード - 3/3 で レイアウトをクリック
4-1.ピボットテーブル/ピボットグラフウイザード - レイアウト で
日付、車種、番号の順に行にドラッグ&ドロップ
部品を列にドラッグ&ドロップ
番号をデータにドラッグ&ドロップ(個数になるはず)
OK
これでピボットテーブルができます。
5.グレーの表題の『日付』と『車種』をA1セルあたりにドラッグ&ドロップします
これで、『日付』と『車種』を指定できるようになっているはずです
『部品』も指定できるようになっているはずです(部品はタイヤ以外もある?)
データが増えたら、ピボットテーブルで右クリックし、ウイザード→戻る でデータ範囲の行を変更します。
算式を使わず、内容を見ることができます。データベースは本来、そういうものでしょう。
カスタマイズすれば、自分専用のピボットテーブルが作れるでしょう。
No.3
- 回答日時:
=VLOOKUP(TEXT(A2,"y年m月")&B2&C2,範囲,2,0)
範囲は
A B C D E
検索キー 日付 車種 部品 番号
=TEXT(B2,"y年m月")&C2&D2 4/3 ムーヴ タイヤ BS1
のようにすれば可能ですが
この回答への補足
回答有難うございます。
解説いただいた方法を自分でも試行したのですが、上手くタイヤ番号を表示させられませんでした。
日付セルの表示(標準、数値,文字列)に問題があるのではないかと色々変えてはしていますが、困ったことに、質問の納品ベース表と別に現場用の実際の生産予定表とは1日のズレがあり、(4/3 ムーヴ タイヤ BS1では納品は4月3日なのですが、現場で使われるのは4月4日になるため、日付を-1にして完全一致したいのですが、日付を2007/4/3,4/3,39175のシリアルにしたりしていますが、上手く行きません。納品予定表と現場生産予定表は項目、順序、種類は全く同じなのですが、1日のズレがあるために、手を焼いています。
No.2
- 回答日時:
1)A列の左側に1列挿入
2)新A列に、日付・車種・部品を入力
=B1&C1&D1
3)新A列の内容をキーとしてVLOOKUPをかける
エクセルで実際に試してませんが、パッと思いつく方法はこんなとこでしょうか。
当方システム屋ですが、大企業のコンピュータシステムも、考え方は全く同じです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 国産車 タイヤについて。アドバイスお願いします 11 2023/01/05 15:00
- 国産車 純正ホイルに戻すか、社外ホイルか迷ってます 悩み過ぎて疲れてます笑 7 2023/01/06 11:42
- 自転車修理・メンテナンス インチアップタイヤ 標準と違いますが ロードインデックスで空気圧を決めていい? 1 2022/12/24 21:27
- 国産車 タイヤの価格帯について 9 2023/01/04 17:09
- 車検・修理・メンテナンス 空気圧センサー付きの車のタイヤ交換について 5 2022/09/03 16:42
- カスタマイズ(車) タイヤのサイズの違いについて教えてください。 初めてタイヤ交換をするのですが、タイヤの規格が2種類あ 11 2022/05/14 10:54
- 国産車 トヨタの車でタイヤの「空気圧センサー」バルブを、タイヤ組み換えの時にミスをしたのか、割れかけていて3 4 2022/11/04 06:42
- 車検・修理・メンテナンス 至急おねがいします タイヤを縁石にぶつけてしまいました 2週間前に4つタイヤを新しくしたばかりでショ 4 2022/10/31 14:51
- 国産車 80系のノアに乗っているのですが、現行のノアのホイールを付けても良いのでしょうか。 2 2023/07/03 21:04
- 自転車修理・メンテナンス 自転車屋さんにタイヤ交換を頼む時、自転車のメーカー名と車種名を言っても、デフォルトでどのタイヤがつい 8 2022/05/07 03:28
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
タイヤを回して遊ぶ子供は自閉...
-
ブリヂストンの電動自転車、TB1...
-
タイヤが雨水に浸かっていたら
-
先程タイヤ館というお店でタイ...
-
タイヤがすぐ茶色くなってしま...
-
タイヤ交換 ダンロップとヨコハ...
-
ミニバンタイプのタイヤ交換4...
-
タイヤメーカーの靴
-
車の走行中にコンコンコンと音...
-
ホイール軽量化に伴うロードノ...
-
Buddy Clubについて
-
韓国製タイヤ
-
ホワイトリボンタイヤの洗浄の方法
-
タイヤ痕について
-
前後に異なる太さのタイヤを使...
-
リムガード付きタイヤを検討中...
-
ノーマルタイヤでミシュランか...
-
タイヤサイズとスタッドレスの...
-
バン用タイヤと乗用タイヤの違...
-
新品タイヤと古いタイヤでこん...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
タイヤを回して遊ぶ子供は自閉...
-
先程タイヤ館というお店でタイ...
-
車中泊旅行にハイエース うるさ...
-
リムガード付きタイヤを検討中...
-
GR86のタイヤについて 純正のパ...
-
大型トラック タイヤの値段はい...
-
ブリジストンB250という純正の...
-
車の走行中にコンコンコンと音...
-
タイヤを一本だけ換えるのって...
-
タイヤがすぐ茶色くなってしま...
-
タイヤが雨水に浸かっていたら
-
公園とかによくある…
-
タイヤはサイズが合ってれば何...
-
タイヤを交換したら、コトコト...
-
タイヤの溝は何ミリで交換して...
-
農業トラクターに関しての質問...
-
タイヤ痕について
-
ブリヂストンの電動自転車、TB1...
-
車のタイヤについて。
-
スタッドレスを履いたら、昨年...
おすすめ情報