Excel2003 重複しないデータを別のシート

質問

お世話になります。Excel初心者です。皆様のお知恵をお貸しいただければ幸いです。
Excel2003で下記の件で、非常に困っております。

sheet1に重複しないデータがあります。

A B C　←セル横
1 顧客No 商品No パーツNo
2 12345 23-1111 23
3 13456 21-1234 55
4 23456 22-5555 66

sheet2に上記のどの情報を入れても、3つのデータを表示させたいです。

A ←セル横
1 顧客No
2 商品No　→ここに入力(21-1234)
3 パーツNo

上記の縦の列は空白です。sheet1の2の商品No21-1234と入力すると
sheet1の顧客No13456とパーツNo55が抽出されます。
同様にsheet2のパーツNo空白欄にsheet1のパーツNoの55と入力
すると、sheet2の顧客Noと商品Noが表示されます。

こういう事は、Excel2003で可能なのでしょうか。
お手数お掛けしますが、ご教授よろしくお願いいたします。

回答No.6

eden3616 · ベストアンサー

>添付して頂きました画像のB2縦列で、一つでもsheet1がヒットすれば、
>値を表示させるようにしたいです。
>sheet2の縦列Bは、始めは空欄ですが、例えばパーツNoを入力すると
>顧客Noと商品Noが表示します。
>3つ表示された状態で、今度は顧客Noを入力すると残り2つの商品Noと
>パーツNoが表示できますか？

入力するセル（B列）と、結果を表示するセル（B列）が同じ場合はVBAでの処理になります。
ご提示の「Sheet1」がベースのデータで、「Sheet2」に表示させるとします。

■コードの登録
(1)「Sheet2」のシートタブ(エクセル下部に並んでいるシート名)を
　　右クリックで「コードの表示」を選択
(2)VBEが開きますので、下記のVBAコードを貼付
(3)右上の「×」またはAlt+F11でVBEを閉じる

■使い方
「Sheet2」のB1～B3に値を入力してください。
入力したセル以外のセルへ結果が表示されます。
入力した項目に入力した値が見つからなかった場合は、「不明」と表示されます。

■コードの修正（以下の場合はVBAコードを修正してください）
(1)「Sheet2」以外に表示する場合
　→　上記「■コードの登録」(1)で対象のシートを右クリックしてコードを追加してください。
(2)データのベースが「Sheet1」以外の場合
　→　コード内の末尾に「★」を付けている行の「Sheet1」を変更してください。
(3)見つからなかった場合に表示する文字を変更する場合
　→　コード内の末尾に「☆」を付けている行の「不明」を変更してください。
　　　（何も表示しない（空欄）場合は　Cells(i, "B") = "" '☆　）
　→　元のセルのまま、変更しない場合は「☆」のコードを行ごと削除してください。

■VBAコード

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Variant, mySt As Worksheet, tar As Range, i As Integer
If Target.Count > 10 Then Exit Sub
Set mySt = Worksheets("Sheet1") '★
For Each myRng In Target
　　With mySt
　　　　If Not Application.Intersect(myRng, Range("B1:B3")) Is Nothing Then
　　　　　　Set tar = .Columns(myRng.Row).Find(myRng.Value, , xlValues, _
　　　　　　　　xlWhole, xlByRows, xlPrevious, True, True, False)
　　　　　　i = myRng.Row
　　　　　　Application.EnableEvents = False
　　　　　　Do
　　　　　　　　i = i + 1: If i = 4 Then i = 1
　　　　　　　　If i = myRng.Row Then Exit Do
　　　　　　　　If tar Is Nothing Then
　　　　　　　　　　Cells(i, "B") = "不明" '☆
　　　　　　　　Else
　　　　　　　　　　Cells(i, "B") = .Cells(tar.Row, i).Value
　　　　　　　　End If
　　　　　　Loop
　　　　　　Application.EnableEvents = True
　　　　End If
　　End With
Next
End Sub

お礼

eden3616様

お世話になります。
この度は、VBAのコードをご教授頂きありがとうございました。
私の思う用に動作いたしまました。

しかし、いざ会社で本番のExcel(ベースは変わりません)で試したところ
動作してくれません。Set mySt = Worksheets("Sheet1") '★は、任意の名前(DATAというシート名)に
変更して、いろいろ試しましたが結局今日は出来ないので諦めました。

sheet2のB1からB3へ表示させる方法を教えて頂きましたが、本番のExcelでは、D3,D4,D5に表示を
させたいのですが、ここを変更すれば良いのでは？色々試してみましたがダメでした。

そこで大変申し訳ございませんが、どこの部分のコードを変更するとD3,D4,D5に表示させられるのでしょうか？
あと、　i = i + 1: If i = 4 Then i = 1このコードは、どのような内容ですか？

質問ばかりで申し訳ございませんが、何卒よろしくお願いいたします。

回答No.8

eden3616

>実は今日、ご指摘頂きました事は会社で上記のVBEでコードを変更してました。
>ですが、何故か一つの値を入力をsheet2のD3,D4,D5いづれかを入れると入力された
>文字は当然残りますが、他の2項目が”不明”と表示されます。

大変申し訳ありませんでした。
こちらで確認したところ検索対象の行番号指定部分をD3～D5に対応し漏れていました。
以下のコードでは末尾「◎」の部分を修正（「-2」を追加）しております。
具体的な変更内容は以下の通りになります。

①変更内容で変更値の行番号を取得する箇所
Set tar = .Columns(myRng.Row - 2).Find(myRng.Value, , xlValues, _
　xlWhole, xlByRows, xlPrevious, True, True, False) '◎

②取得した行の他２項目を取得する箇所
Cells(i, "D") = .Cells(tar.Row, i - 2).Value '◎

myRng.Rowには変更（入力）した行番号（3～4）が入りますので、
検索対象列がA～C（1～3列番号）の場合は行番号から「- 2」した値を
列番号として使用する必要があります。
このため、上記変更箇所の列番号指定部分を其々「- 2」を追加いたしました。

代理投稿になりますので画像添付はできませんが、
こちらでは動作することを確認しましたのでコードの差替え願います。
もし、こちらのコードで動作しない場合は検索方法を変えるか、
検索結果をデバッグする必要がありますので再質問を上げて頂けますでしょうか。

■VBAコード

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Variant, mySt As Worksheet, tar As Range, i As Integer
If Target.Count > 10 Then Exit Sub
Set mySt = Worksheets("Sheet1") '★
For Each myRng In Target
　　With mySt
　　　　If Not Application.Intersect(myRng, Range("D3:D5")) Is Nothing Then
　　　　　　Set tar = .Columns(myRng.Row - 2).Find(myRng.Value, , xlValues, _
　　　　　　　　　　xlWhole, xlByRows, xlPrevious, True, True, False) '◎
　　　　　　i = myRng.Row
　　　　　　Application.EnableEvents = False
　　　　　　Do
　　　　　　　　i = i + 1: If i = 6 Then i = 3
　　　　　　　　If i = myRng.Row Then Exit Do
　　　　　　　　If tar Is Nothing Then
　　　　　　　　　　Cells(i, "D") = "不明" '☆
　　　　　　　　Else
　　　　　　　　　　Cells(i, "D") = .Cells(tar.Row, i - 2).Value '◎
　　　　　　　　End If
　　　　　　Loop
　　　　　　Application.EnableEvents = True
　　　　End If
　　End With
Next
End Sub

お礼

eden3616様

お世話になります。完璧に動作を確認しました。
あとは、会社での動作確認をしたいと思います。

また、同じ件名で質問をさせて頂いております。
私は、素人ですので、お手すきの時ににでも
今回のコードの下にでも、何をしているコードなのかも
頂けると幸いです。お忙しいところお手数おかけいますが
何卒、よろしくお願いいたします。
その内容で、また勉強していきたいと思っております。

回答No.7

eden3616

ベストアンサーで回答が締め切られていため、
この回答はサポートにより追加して頂いております。

>しかし、いざ会社で本番のExcel(ベースは変わりません)で試したところ
>動作してくれません。Set mySt = Worksheets("Sheet1") '★は、任意の名前(DATAというシート名)に
>変更して、いろいろ試しましたが結局今日は出来ないので諦めました。

「DATA」が対象のシートであれば、以下のようにしてください。
(シート名は半角スペース、大文字小文字、全角半角など間違えないように)
Set mySt = Worksheets("DATA") '★

>sheet2のB1からB3へ表示させる方法を教えて頂きましたが、本番のExcelでは、D3,D4,D5に表示を
>させたいのですが、ここを変更すれば良いのでは？色々試してみましたがダメでした。
>そこで大変申し訳ございませんが、どこの部分のコードを変更するとD3,D4,D5に表示させられるのでしょうか？

以下の3箇所を変更してください。

①変更されたセルが対象範囲内であるか判定する箇所
If Not Application.Intersect(myRng, Range("B1:B3")) Is Nothing Then
　　　　　↓
If Not Application.Intersect(myRng, Range("D3:D5")) Is Nothing Then

②繰り返し処理(Do～Loop)において行番号を小さい行番号へリセットする処理
i = i + 1: If i = 4 Then i = 1
　　　　　↓
i = i + 1: If i = 6 Then i = 3

③実際にセルに結果を入力する処理
Cells(i, "B") = "不明" '☆
　　　　及び
Cells(i, "B") = .Cells(tar.Row, i).Value
　　　　　↓
Cells(i, "D") = "不明" '☆
　　　　及び
Cells(i, "D") = .Cells(tar.Row, i).Value

>あと、　i = i + 1: If i = 4 Then i = 1このコードは、どのような内容ですか？

コードの内容は今回のように質問者様で改変される場合はコードの処理内容・流れを把握しないと
手当たり次第に触ると意図しない動作をします。
よって、該当箇所について順に説明いたします。

上記②に少し書いていますが、「Do」～「Loop」の間を処理が繰り返し行われます。
手前のコード「i = myRng.Row」変更されたセルの行番号が変数「i」に格納されます。
「i = i + 1」は「i」に「1」を加えたものを再度「i」に格納することで、
「i」を「1」増加させます。その後「i」が「4」であれば「i」に「1」を格納し直します。
このような処理を行っています。

具体的な意味としては、B1、B2、B3セルがあり、「B2」が入力（変更）されれば
手前の処理「i = myRng.Row」で変数「i」に「2」が入ります。
繰り返し処理の中で「i」に1を加えて「3」となります。
この「3」は次に検索して入力する対象のセルの行番号を「3」として指定するために用意しました。
次の行から「Loop」まで間の処理で「B」列の「i」行のセルに対象シートの
値を入力して「Do」に戻ります（繰り返す）。
前回「i」に3が入っていたので同様に1増加して「4」になりますが、
「i」が「4」であれば「1」とするため、次の処理で使う「i」は「1」となります。
よって、B1セルに次の値が入り、Doに戻ります。

このままだと繰り返し処理が終わりませんので、終わらすための処理が次になります。
If i = myRng.Row Then Exit Do
「myRng.Row」は開始したセルB2の行番号「2」になります。
上記コードの意味は「i」が開始行番号と一致したら繰り返しを終わるという意味になります。
上記「B1」（「i」が「1」）が終わり、「Do」に戻ってから「i = i + 1」されましたので
「i」が「2」となります。ここで、この処理の条件と一致しますので繰り返し処理が終わります。

>質問ばかりで申し訳ございませんが、何卒よろしくお願いいたします。

ベストアンサー（BA）を選ばれると回答が締め切られますので返答が出来なくなります。

お礼

eden3616様

ご回答ありがとうございます。また、BAをすると回答が締め切られる事をしらず申し訳ございませんでした。

実は今日、ご指摘頂きました事は会社で上記のVBEでコードを変更してました。
ですが、何故か一つの値を入力をsheet2のD3,D4,D5いづれかを入れると入力された
文字は当然残りますが、他の2項目が”不明”と表示されます。

また、コードを変えてデバックをして失敗して元にもどしても、動作がおかしくなります。
何か、コードを変更した後、VBEで保存?的なことをしないとダメなのでしょうか。

私が変更したコードをは、下記です。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Variant, mySt As Worksheet, tar As Range, i As Integer
If Target.Count > 10 Then Exit Sub
Set mySt = Worksheets("Sheet1") '★
For Each myRng In Target
With mySt
If Not Application.Intersect(myRng, Range("D3:D5")) Is Nothing Then
Set tar = .Columns(myRng.Row).Find(myRng.Value, , xlValues, _
xlWhole, xlByRows, xlPrevious, True, True, False)
i = myRng.Row
Application.EnableEvents = False
Do
i = i + 1: If i = 6 Then i = 3
If i = myRng.Row Then Exit Do
If tar Is Nothing Then
Cells(i, "D") = "不明" '☆
Else
Cells(i, "D") = .Cells(tar.Row, i).Value
End If
Loop
Application.EnableEvents = True
End If
End With
Next
End Sub

度々、申し訳ございません。

回答No.5

tom04

No.3です。

＞全く同じようにExcel表とVBAを入力してみましたが、スペルが間違っているのか
分かりませんが、顧客Noや商品No,パーツNoを入力してみましたが、全く値が表示されません。

スペルが間違っているのか？というコトはご自身で手入力されたのでしょうか？
もう一度確認してみてください。

（1）Sheet1・Sheet2の配置は前回アップした画像通りになっているか？
（2）コードはSheet2のシートモジュールになっているか？
画面左下に「Sheet1」「Sheet2」・・・とSheet見出しが並んでいると思います。
その「Sheet2」の上で右クリック　→　コードの表示　→　VBE画面（白い画面）が表示され、カーソルが点滅しているところがあります。
（3）前回のコード
Private Sub・・・の行から・・・End Subまでの行すべてを
画面上でドラッグ（範囲指定）　→　右クリック　→　コピー　→　先ほどのVBE画面のカーソルが点滅しているところで右クリック　→　貼り付け
これで前回のコードが「Sheet2」のシートモジュールになりましたので、
一旦表示されているVBE画面を閉じます。
そしてSheet2のB1～B3セルに何らかのデータを入力してみてください。

Excel2003でもちゃんと動くはずです。m(_ _)m

お礼

tom04様

ご回答ありがとうございます。昨日、メモ帳でペーストして会社のアドレスに送って無事動作しました。
正に私が求めいた通りの動作でした。

先ほども、お礼をした方にもメッセージを残したのですが、いざ会社の本番のExcelで教えて頂いた
ものをコピーして編集しました。もちろんsheet2ではなく実際はDATAという名のシートですので
名前も変更しました。コードを私なりに考え調べて色々やりましたが、結局今日はできませんでした。

実際はsheet1ではなくDATAという名です。表示は、sheet2のB1,B2,B3ではなくD3,D4,D5です。
教えて頂いたコードは、どこの部分で、sheet2で表示させる動きをさせてるのでしょうか？

どうか、ご教授ください。よろしくお願いいたします。

回答No.4

eden3616

無理やり関数で・・・・

データが「Sheet1」、表示先が「Sheet2」とします。
ご提示の様式と異なってしまいますが、
Sheet2のC列を入力用の列、B列を結果表示用の列とします。

Sheet2のB1～B3セルに以下の数式を貼り付けてください。
（同じ数式ですので、まとめて貼付またはフィルハンドルで複写可能です）

=INDIRECT("Sheet1!"&LEFT(ADDRESS(1,ROW(),4),1)&MATCH(INDIRECT("C"&MIN(IF(COUNTA(C$1:C$1)=1,1,4),IF(COUNTA(C$1:C$2)=1,2,4),IF(COUNTA(C$1:C$3)=1,3,4))),INDIRECT("Sheet1!"&LEFT(ADDRESS(1,MIN(IF(COUNTA(C$1:C$1)=1,1,4),IF(COUNTA(C$1:C$2)=1,2,4),IF(COUNTA(C$1:C$3)=1,3,4)),4),1) & ":" & LEFT(ADDRESS(1,MIN(IF(COUNTA(C$1:C$1)=1,1,4),IF(COUNTA(C$1:C$2)=1,2,4),IF(COUNTA(C$1:C$3)=1,3,4)),4),1)),0))

Sheet2のC列（セルC1、C2、C3）に顧客No、商品No、パーツNoを入力するとB列に結果が表示されます。
複数C列に入力された場合は一番上のセルの値で検索します。
（C1、C3セルに入力した場合は、C1セルの顧客Noを使用して検索）

検索が見つからないまたは、C列に何も入力されていない場合はエラー「#N/A」となります。

複合的な関数式は得意ではないのでもう少しスマートな方法があるかもしれませんが。
シンプルに片づけたい場合はVBAによる処理になります。

補足

eden3616様

お忙しいところ、ご回答ありがとうございます。関数でも可能なんですね。
複雑なので、私には無理そうですが・・・

添付して頂きました画像のB2縦列で、一つでもsheet1がヒットすれば、値を表示させるようにしたいです。
sheet2の縦列Bは、始めは空欄ですが、例えばパーツNoを入力すると顧客Noと商品Noが表示します。
3つ表示された状態で、今度は顧客Noを入力すると残り2つの商品NoとパーツNoが表示できますか？

こんな場合は、やはりVBAで作った方が早いでしょうか？
この仕様でVBAをご教授頂けると幸いです。
お忙しいところ、ご回答ありがとうございました。

回答No.3

tom04

こんばんは！
VBAでの一例です。

↓の画像のように左側が元データのSheet1・右側が表示させるSheet2とします。

画面左下の「Sheet2」のSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りSheet2のB1～B3セルに好みのデータを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim c As Range, wS As Worksheet
Set wS = Worksheets("Sheet1")
If Intersect(Target, Range("B1:B3")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target
If .Value <> "" Then
Select Case .Row
Case 1
Set c = wS.Range("A:A").Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole)
Case 2
Set c = wS.Range("B:B").Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole)
Case Else
Set c = wS.Range("C:C").Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole)
End Select
If Not c Is Nothing Then
Application.EnableEvents = False
With Range("B1")
.Value = wS.Cells(c.Row, "A")
.Offset(1) = wS.Cells(c.Row, "B")
.Offset(2) = wS.Cells(c.Row, "C")
End With
Application.EnableEvents = True
Else
If MsgBox("該当データなし" & vbCrLf & "再入力しますか？", vbYesNo) = vbYes Then
Range("B1:B3").ClearContents
.Select
Else
Exit Sub
End If
End If
End If
End With
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

補足

tom04様

お忙しいところご回答ありがとうございます。
全く同じようにExcel表とVBAを入力してみましたが、スペルが間違っているのか
分かりませんが、顧客Noや商品No,パーツNoを入力してみましたが、全く値が表示されません。

メモ帳を添付させて頂きたいのですが、どうやるのかわかりません(涙)
申し訳ございません。

回答No.2

kigoshi

> sheet1の2の商品No21-1234と入力すると
> sheet1の顧客No13456とパーツNo55が抽出されます。

この「抽出されます」の意味がよく分かりません。
Sheet2のA1セルに13456が表示され、Sheet2のA3セルに55が表示したい、という意味でしょうか。

それで、Sheet2のA3セルに55を入力すると
Sheet2のA1セルに13456が表示され、Sheet2のA2セルに21-1234を表示させたい、ということですか。

もしそうならば、通常の関数ベースでは不可能だと思います。
VBAを使用しても良いのなら可能性はありますが。

補足

kigoshi様

お世話になります。この度は、ご回答ありがとうございます。
補足ですが、tom04様の画像が好ましい形でsheet2のBの縦列3つのどこに入力しても
(例えば、商品Noを入力すると、他の顧客NoとパーツNo)表示さたいと思っています。

しかも、3つ表示された状態で、(例えば、今度は顧客Noを入力すると、他の商品NoとパーツNoを上書きしてくれる)上書きをして表示される様な仕様です。

ご教授頂けると幸いです。よろしくお願いいたします。

回答No.1

ify620

＞こういう事は、Excel2003で可能なのでしょうか
初心者です！ m(*^_^*)//siturei

考えさせて頂きました。

結論は、 できないと思います。

顧客×商品×パーツで、9個のDataだけなら
IF関数を使って、直ぐにでも、どうにでも呼だされます。

しかし、実務では、顧客一覧表と商品一覧表とパーツ一覧表が必要です。
これらを組み合わせて、下記の様な集計表が作られると承知します。

日計や週計、月計表になります。

それぞれ複数の3列Dataが取引に応じて記録されますね。
そこで、A1セルにカーソルを置いて、
【データ】→【フィルタ】→【オートフィルタ】
で、各縦の列の一番上にプルダウンメニューを表示してそこで、

No21-1234とか55とかをプルダウンメニューで選択して、必要なData〔横の行〕だけを表示〔複数行になります。〕させるのが妥当かと思います。

そうで無いと、Excelの機能の意味が無いと思うのです。

もちろん、ベテランの方のご回答を待って、勉強させて頂きたいと思います。 m(_ _)m

お礼

ify620様

ご回答ありがとうございます。やはり関数ではできたとしてもかなり複雑になりそうですね。
私もVBAで作りたいと思います。ありがとうございました。