• ベストアンサー

VBAで2つのデータを横に結合できますか?

Sheet1には以下のデータが入力されています。 NO TIMES SCORE 1  1  20 1  2  30 1  3  25 2  1  50 2  2  40 2  3  45 3  1  70 3  2  75 4  3  3 いっぽうsheet2には以下のデータが入力されています。 NO  NAME  SEX  AGE 1  Aさん  男  31 2  Bさん  女  27 3  Cさん  女  33 4  Dさん  男  26 この2つのデータをNOをキーとして横に結合したいのですが VBAでこのような結合操作はできるものでしょうか? NO NAME SEX AGE TIMES SCORE 1 Aさん 男 31 1 20 1 Aさん 男 31 2 30 1 Aさん 男 31 3 25 2 Bさん 女 27 1 50 2 Bさん 女 27 2 40 2 Bさん 女 27 3 45 3 Cさん 女 33 1 70 3 Cさん 女 33 2 75 4 Dさん 男 26 3 3 if文を使ってNOが1ならNAMEがAさん、SEXが男・・・という 条件文をかけばできないこともありませんが、 実際のデータではNOが450もありますので 非効率と考えています。 もしご存知でしたら教えていただけませんか。 よろしくお願いいたします。

質問者が選んだベストアンサー

  • ベストアンサー
  • keirika
  • ベストアンサー率42% (279/658)
回答No.3

シート1の横にシート2の情報を貼り付ける方法です。 質問に提示された列の並びと異なりますが、結合と言う意味では 要件を満たしていると思います。 シート2は見出しを含め451行あると仮定します。 Sub Sample() Dim SH1 As Range Dim SH2 As Range Dim i As Long Set SH1 = Sheets("Sheet1").Range("a1").CurrentRegion Set SH2 = Sheets("Sheet2").Range("a1:d451") For i = 2 To SH1.Rows.Count SH1.Cells(i, 4) = Application.VLookup(SH1.Cells(i, 1), SH2, 2, 0) SH1.Cells(i, 5) = Application.VLookup(SH1.Cells(i, 1), SH2, 3, 0) SH1.Cells(i, 6) = Application.VLookup(SH1.Cells(i, 1), SH2, 4, 0) Next i Set SH1 = Nothing Set SH2 = Nothing End Sub 以上です。

kenkichi55
質問者

お礼

ありがとうございます。 コンパクトで分かりやすいですね。 SETというものを知らなかったので、大変勉強になりました。 ありがとうございます。 自分でもコードを1からなぞって勉強させていただきます。 ところでコード中にa1:d451ってありますが、この451を SH1.Rows.COUNTに置き換えることってできるのでしょうか?

その他の回答 (4)

  • keirika
  • ベストアンサー率42% (279/658)
回答No.5

#3です。 >ところでコード中にa1:d451ってありますが、この451を >SH1.Rows.COUNTに置き換えることってできるのでしょうか? シート1とシート2では行数が異なると思いますので、出来ません。 汎用性と言う意味ではRange("a1:d451")の代わりにRange("a1").CurrentRegion とした方が良いかもしれません。

  • mitarashi
  • ベストアンサー率59% (574/965)
回答No.4

VBAを持ち出さずとも、VLOOKUPで十分ではないかと思いましたが、VBAで式を、動的な対象範囲に対して生成するのはどうやるのかなと、気になったのでやってみました。#3と考え方は似ているかもしれません。ご参考まで...とは言い難いですが。 'Sheet1,Sheet2のデータを照合して、Sheet3にまとめる Sub test() Dim destRange As Range Dim i As Long Dim master As Range Dim fieldNames As Range Sheets("Sheet1").Cells.Copy Sheets("Sheet3").Range("a1") Set master = Sheets("Sheet2").Range("a1").CurrentRegion Set fieldNames = master.Rows(1) Set master = master.Offset(1, 0).Resize(master.Rows.Count - 1, master.Columns.Count) Set destRange = Sheets("Sheet3").Range("a1").CurrentRegion Set destRange = destRange.Offset(1, 0).Resize(destRange.Rows.Count - 1, destRange.Columns.Count) For i = 2 To 4 destRange.Columns(i).EntireColumn.Insert Shift:=xlToRight destRange.Columns(i).FormulaR1C1 = "=VLOOKUP(RC1,Sheet2!" & master.Address(True, True, xlR1C1) & "," & Format(i, "0") & ",false)" Next i fieldNames.Copy Sheets("Sheet3").Range("a1") End Sub 関数入力を自動記録すると、R1C1形式で記述される事を知りました。

kenkichi55
質問者

お礼

ありがとうございます。 今まではvlookupでやっていたのですが、ここひと月前から VBAを勉強中でして、実際に今までやってきた業務を VBAでできるかチャレンジしていたのでした。 いやいやみなさん、スラスラとお書きになられるようで すばらしいです。 いただいたコードを勉強します!

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.2

ANo.1です。 >, n As Long 変数nは使っていないので削除して下さい。

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.1

Sheet2にはNoの重複がないものとして、結果をSheet3に書き出します。 Sub test()  Dim Dic As Object  Dim i As Long, j As Long  Dim m As Long, n As Long  Dim v, w, x  Set Dic = CreateObject("Scripting.Dictionary")  With Worksheets("Sheet2")       v = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp).Resize(, 4)).Value  End With  ReDim x(1 To 6, 1 To 1): m = 1  For i = 1 To UBound(v, 1)      Dic.Add v(i, 1), Array(v(i, 2), v(i, 3), v(i, 4))  Next  With Worksheets("Sheet1")       w = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp).Resize(, 3)).Value       For j = 1 To UBound(w, 1)           If Dic.exists(w(j, 1)) Then              x(1, m) = w(j, 1): x(2, m) = Dic(w(j, 1))(0)              x(3, m) = Dic(w(j, 1))(1): x(4, m) = Dic(w(j, 1))(2)              x(5, m) = w(j, 2): x(6, m) = w(j, 3)              m = m + 1              ReDim Preserve x(1 To 6, 1 To m)           End If       Next  End With  With Worksheets("Sheet3")       .Range("A1:F1").Value = Array("NO", "NAME", "SEX", "AGE", "TIMES", "SCORE")       .Range("A2").Resize(m - 1, 6).Value = Application.Transpose(x)  End With  Set Dic = Nothing  Erase v, w, x End Sub ご参考になれば。

kenkichi55
質問者

お礼

ありがとうございます。 非常にエレガントなコードですね。 コードを見せていただきましたが、知らない構文がいろいろと あるので、いい勉強材料をいただいたと感謝しております。 やはり本よりも人が書いたコードのほうが勉強になりますね。 私もあなたのようにスラスラとコードが書けるように がんばっていきたいです。 今後ともよろしくお願いいたします。

関連するQ&A

  • 外部結合について

    外部結合というのは、表と表を結合するという事で FROM句に書くべきことと思うのですが、 なぜWHERE句(カラム毎に指定)で指定するのでしょうか? 例えば、 テーブルA No|Kind|Name| ------------- 1|1 |A | 1|2 |B | 2|1 |C | 2|2 |D | テーブルB No|Kind|Data| ------------- 1|1 |10| 外部結合で SELECT A.No, A.Kind, A.Name, B.Data FROM A,B WHERE A.No = B.No(+) AND A.Kind = B.Kind(+) とすると No|Kind|Name|Data| ------------------ 1|1 |A |10| 1|2 |B |NULL| 2|1 |C |NULL| 2|2 |D |NULL| となります。 A.NoがB.NoになくてもOKで、Kindは 完全に一致しないとだめという意味で SELECT A.No, A.Kind, A.Name, B.Data FROM A,B WHERE A.No = B.No(+) AND A.Kind = B.Kind とした場合、 No|Kind|Name|Data| ------------------ 1|1 |A |10| 2|1 |C |NULL| となるのであれば納得いくのですが、 結果は↓なるようです。 No|Kind|Name|Data| -------------------------- 1|1 |A |10| (+)を一つも付けてない場合と 同じ動作になるようです。 テーブルAとテーブルBを結合するとき、 「外部結合する場合はWHERE句で指定する選択の条件全てに(+)を付ける」 「外部結合をしない場合はWHERE句で指定する選択の条件全てに(+)を付けない」 であれば、カラム毎に外部結合演算子を指定する意味が無いと思うのですが。 どなたか、カラム毎に外部結合演算子を指定する意味をご教授下さい。 宜しくお願いします。

  • 結合セルに結合していないセルの値を貼り付けたいです。

    結合セルに結合していないセルの値を貼り付けたいです。 シート1の1行目の[A1-B1-C1-D1]を結合しています。以下同じように[E1-F1-G1-H1]…のように4つづつ結合しています。 この行に、シート2のA1→B1→C1と続く結合していないセルのデータを貼り付けたいのですが、やりかたがわかりません。色々やってみたのですが、うまくゆきません。ご教授お願いいたします。

  • セルデータの結合に関して

    2007にて作成中の表にてデータ結合すべく数式を入れても機能しません。 仮に、セルA1,B1,C1を結合すべくセルD1に「=A1&B1&C1」としても結合となりません。検証のため別シートにてA1,B1,C1にそれぞれ1,2,3と入れやってみるとD1には123となります。 どこがおかしいのでしょうか?点検、確認してみるべき点をご指摘ください。

  • 表の結合について

    2つのテーブルがあります。(それぞれNo列が主キーです) 表A(名称:Tbl_A)に2レコードあります。 No, Str  ←列名 10,str_1  ←データ 20,str_2  ←データ 表B(名称:Tbl_B)に2レコードあります。 No, Name  ←列名 10,Name_1 ←データ 30,Name_3 ←データ このテーブルを結合して、以下のデータ(3レコード)を抽出するSQL文を教えてください。 No, Str, Name   ←列名 10,str_1,Name_1 ←データ 20,str_2,(null) ←データ 30,(null),Name_3 ←データ ポイントは、「どちらかの表にあるデータは全て抽出したい」ということです。 よろしくお願いします。

  • 2つのエクセルのデータを結合する方法で困っています。

    2つのエクセルのデータを結合する方法で困っています。 下図のE部分だけをファイル1からファイル2に反映させたいのですが、 行が追加されたり、削除されたりしているため、単純に貼り付けできません。 (下の例では、ファイル1>2の過程で【い】【え】が削除、【も】【か】が追加、【あ】のB【う】のCの内容が変更になっています) 何かいい方法はないでしょうか? ファイル1:       A   B  C   D  E   1あ  ●  ●  ●  ●  ■   2い  ●  ●  ●  ●  ■   3う  ●  ●  ●  ●  ■   4え  ●  ●  ●  ●  ■   5お  ●  ●  ●  ●  ■ ファイル2:       A   B  C   D  E   1あ  ●  ▲  ●  ●     2も  ●  ●  ●  ●     3う  ●  ●  ▲  ●     4お  ●  ●  ●  ●     5か  ●  ●  ●  ●   こうしたい:       A   B  C   D  E   1あ  ●  ▲  ●  ●  ■   2も  ●  ●  ●  ●     3う  ●  ●  ▲  ●  ■   4お  ●  ●  ●  ●  ■   5か  ●  ●  ●  ●   Excelのバージョンは2000です。実際は1000行×30列×10シートくらいあって、結構な頻度で結合するのですが、手でやっていて大変なのです。。

  • アクセスによるデータの結合

    マイクロソフトアクセス2003で下記のような2つのテーブルを結合してひとつのデータとすることができるでしょうか(データ量が膨大でエクセルでの作業には無理があります。)。詳しい方がおられたらご教示お願いします。(このことが説明されているサイトでも結構です。)よろしくお願いします。 (データ1)01:A       02:B       03:C (データ2)01:A       02:B       04:D       05:E →上記データの結合によりデータ3を作成 (データ3)01:A       02:B       03:C       04:D       05:E ※例は簡略化していますが、コードの後の情報は、アルファベット以外にも複数ありますので念のため。

  • EXCEL(マクロ)2つのデータ比較について

    いつも活用させていただいているものです。 今回は、以下の内容を行いたいと思います Sheet1とSheet2のA列(NO)を比較して、Sheet2にない項目(NO、名前、年齢、性別)をSheet2の4行目に追加したいと考えております。 どのようなプログラムを組めばよろしいでしょうか。 【Sheet1】 |A  |B | C | D | --------------------------- 1|NO  |名前 |年齢 |性別| 2|001 |鈴木 |11  |男 | 3|002 |佐藤 |15  |女 | 4|003 |長島 |29  |女 | 【Sheet2】  |A  |B | C | D | --------------------------- 1|NO  |名前 |年齢 |性別| 2|001 |鈴木 |11  |男 | 3|002 |佐藤 |15  |女 |               ←追加をしたい お願いします。

  • マスタシートと同じデータがあれば横にデータを貼り付けていく。

    シート1の品番を基準にし、シート2に同じ品番があればシート1の品番とデータの横にシート2のデータを貼り付けていく。次にシート3に移り繰り返し。 (シート1) | A | B | C | D 1 | 1a| 1 | 9 | 2 2 | 2a| 4 | 7 | 1  ・  ・  ・ A列に品番、B列に枚数、C列に最高数、D列に最低数が入ってます。 (シート2) | A | B | C | D 1 | 1a| 2 | 8 | 1 2 | 3a| 3 | 5 | 3 ・  ・  ・ シート1と同じ形式です。 (シート1)↓こうしたいです。 | A | B | C | D | E | F | G | 1 | 1a| 1 | 9 | 2 | 2 | 8 | 1 | 2 | 2a| 4 | 7 | 1 | A列の品番と同じであればその横(列)に枚数、最高数、最低数のデータを貼り付けていきたいのです。 シート数とデータ数はその時々により減ったり増えたりと変化します。 エクセル2000です。 わかる方、よろしくお願いします。

  • テーブルを結合

    テーブルの結合に関して質問します。 ネットなどでinner joinを使った複数のテーブル結合が参考として 書いてあったのですが(3つまで), 4つテーブルを結合する事は 出来るのでしょうか? A,B,CテーブルにはID,NAMEがあります。 DテーブルにはA,B,CテーブルのIDがあります。 参考サイトを見て3つのテーブルを結合するSQLが以下になります。 SELECT a.name, b.name, c.name FROM a INNER JOIN (b INNER JOIN c ON b.id = c.id) ON a.id = c.id よろしければアドバイスお願いします。

  • 結合について質問です。

    結合について質問です。 ■テーブル TOIAWASE A KOKYAKU B TOIAWASE_TAISHOU C MOUSIKOMI_INFOMATION D MOUSIKOMI_INFOMATION E KINMU_MASTER F ■結合 A.KOKYAKU_NO = B.KOKYAKU_NO AND ※1:1 A.TOIAWASE_NO = C.TOIAWASE_NO(+) ※1:1 C.MOUSIKOMI_TYPE = D.MOUSIKOMI_TYPE(+) ※1:1 D.OLD_MOUSIKOMI_TYPE = E.MOUSIKOMI_TYPE(+) ※1:1 A.KINMU_CD = F.KINMU_CD(+) ※1:1 ■データ整合性について Aがある場合、Bは必ずあります。 Aがある場合、Cはあるとは限りません。 Cがある場合、Dは必ずあります。 Dがある場合、Eがあるとは限りません。 Aがある場合、Fがあるとは限りません。 Aをもとに問い合わせ一覧を取得したいです。 Aがある場合、Cがあるとは限らないため、それに紐づくテーブルは全て外部結合をするしかありません。 一応、上記SQLでも取得はできるのですが、他に良い(効率の良い)SQLはないのでしょうか? 宜しくお願いします。

専門家に質問してみよう