エクセル 別シートにあるデータを同一人物のデータとして自動に張り付けるには

別シートにある住所録のデータ内容を、別のシートの同一人物の列に自動で張り付けたい場合どのような数式を使えばいいでしょうか？
色々検索したのですが、上手くいくものがありませんでした。

sheet1
　A 　　　　B　　　　　C　　　　　D　　　E　　　　　F　　　　G
物件名　　貸主名　　郵便番号　　住所1　　住所2　

sheet2
A B 　 C D E
物件名　　郵便番号　　住所1　　住所2　　貸主名

◆シート1のA・BにはデータがありますがC・D・Eは空欄です。
F以降にはデータが入っています。

◆シート2のA・B・C・D・Eにはデータがあります。

＜やりたい事＞
シート1とシート2の入力順はバラバラで、シート1の郵便番号・住所1・住所2にシート2から
物件名もしくは貸主名で照合して自動で対象セルを張り付けたいのですが、VLOOKUPもしくは
マクロで一括してできるでしょうか？

よろしくお願いいたします。

質問者からの補足コメント

• ありがとうございます。
  出来れば物件名と貸主名両方が一致した方が誤りがないと思うのでそれで出来ればベストです。
  
  これは表の一部でまだ物件住所も物件の郵便番号も同じよう入力したいと思っています。
  今後表の変更がありそうなので今回教えて頂いたものを基本に作ろうと思っています。

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/05/14 14:34

No.5 ベストアンサー 回答者： kup-ue 回答日時： 2016/05/14 19:41

vba を使えばいろいろ手が考えられますが（ちなみにvbaを使う場合はセルごとに処理するのでなく、一旦配列に入れて処理しないと速度が桁違いに（下手すると2桁位は）遅くなります。

エクセルの関数でも出来そうですので（エクセルの組み込み関数は早い）ちょっと提案しましょう。

まず、シート2の貸主名のデータに重複がないとします。普通は重複があるといけないと思いますのでこの考えで問題ないと思います。

この貸主名の入力されている行が分かれば、その行の郵便番号や住所を拾ってくるだけですが、Vlookup関数は、検索するデータが範囲の先頭列に無ければいけないので今回のシート2の順番では使えません。

この場合は、match　関数で行で調べてindex関数で範囲のその行の希望の列を拾ってくれば良いです。

実際はひとつの式にまとめても良いですが、考え方を示すために別々の式で示します
sheet2のデータ範囲を仮にA2:E1000　とします。(1行目はタイトルですね）
sheet1の方は1行目がタイトルで2行目からデータが始まっているとします。
仮にH列に貸主のデータが入っている行を調べるmatch関数を入れることにします
H2のセルの式
=match(B2,sheet2!$E$2:$E$1000,0)
3番目の引数を0にしないと、sheet2の方は貸主名でソートしないといけなくなります
これでH2に貸主名の行が出てくるので（範囲が2行目からなので行数としては実際の行数より１少ない値です）idex関数で必要な値を引っ張ってくるだけです。
郵便番号の C2セルには
=index(sheet2!$A$2:$ES$1000,$H2,2)
検索の範囲は、絶対参照ですが、行の値のセルへの参照はコピーする都合で列のHだけ絶対参照にしておきます。
これを住所1、住所2にコピーすると、3番目の引数の列の値が郵便番号の２のままですから、３と４に直せばOKです。

範囲指定の方法はもっと範囲を絞っても構いませんが（3番目の引数も直す）内部では配列で処理しているので範囲が広くても実行速度はほとんど変わらないようです。

慣れないうちはmatch関数の列を別にに設けた方が分かり易いですが、これをIndex関数に組込めばmatch列は不要になります。
たとえば
=Index(sheet2!$A$2:$ES$1000,Match(B2,sheet2!$E$2:$E$1000,0),2)

範囲の指定は名前と言う機能を使いと可読性は上がりますが、範囲を広げる時は名前の管理で行うなど、ちょっと別のセンスが必要となります。

この回答へのお礼

matchで拾ってくるのですね。
VLOOKUPしか浮かばなかったので、目から鱗です。
色々な拾い方ができますね。とても参考になりました。
他の帳票でも使えそうです！

ありがとうございました。

お礼日時：2016/05/15 11:28

No.4 回答者： GooUserラック 回答日時： 2016/05/14 17:18

すいません、「元行」と「先行」が入れ違っていた所が有りました。

以下の様に修正してください。

Sub Sample()

Dim 元行 As Long
Dim 先行 As Long

Sheets("Sheet1").Select
For 先行 = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For 元行 = 1 To Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
If Cells(先行, 1).Value = Sheets("Sheet2").Cells(元行, 1).Value Then
If Cells(先行, 2).Value = Sheets("Sheet2").Cells(元行, 5).Value Then
Cells(先行, 3).Value = Sheets("Sheet2").Cells(元行, 2).Value
Cells(先行, 4).Value = Sheets("Sheet2").Cells(元行, 3).Value
Cells(先行, 5).Value = Sheets("Sheet2").Cells(元行, 4).Value
End If
End If
Next
Next

End Sub

この回答へのお礼

ありがとうございます。
早速訂正してやってみます。
エクセルって面白いですね！

お礼日時：2016/05/15 11:29

No.3 回答者： GooUserラック 回答日時： 2016/05/14 15:28

「Cells(Rows.Count, 1).End(xlUp).Row」は１列目（A列）の最後の行番号になります。

たとえば E列（５列目） の場合は「Cells(Rows.Count, 5).End(xlUp).Row」になります。
後は判ると思いますので説明は省きますが、質問いただければ説明します。

No.2 回答者： GooUserラック 回答日時： 2016/05/14 15:18

とりあえず、かんたんな「For … Next」を使ったもの

Sub Sample()

Dim 元行 As Long
Dim 先行 As Long

Sheets("Sheet1").Select
For 先行 = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For 元行 = 1 To Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
If Cells(元行, 1).Value = Sheets("Sheet2").Cells(先行, 1).Value Then
If Cells(元行, 2).Value = Sheets("Sheet2").Cells(先行, 5).Value Then
Cells(元行, 3).Value = Sheets("Sheet2").Cells(先行, 2).Value
Cells(元行, 4).Value = Sheets("Sheet2").Cells(先行, 3).Value
Cells(元行, 5).Value = Sheets("Sheet2").Cells(先行, 4).Value
End If
End If
Next
Next

End Sub

この回答へのお礼

ありがとうございます。
早速やってみますね。

お礼日時：2016/05/14 16:07

No.1 回答者： GooUserラック 回答日時： 2016/05/14 14:18

「物件名もしくは貸主名」で良いのですか？「物件名」は重複は無いと思いますが「貸主名」は重複がありそうです。

両方が一致したものとした方が良いのでは？
ちなみに、郵便番号、住所１、住所２が、貸主様の物ならば問題はありませんが！