EXCELで検索で一致したキーの値を文字列連結する方法を教えてください。

以下のように2つのシートを使い、Sheet2のB列が実際には数式となり、Sheet2!B1とSheet1!A列が一致した場合に該当行のSheet1!B値を文字列連結して代入したいです。
VLOOKUPを普通に使うと、最初の値だけが代入されてしまったもので・・
よろしくお願いします。

Sheet1

   A      B
┌─────┬─────┐
│ID │趣味 │
├─────┼─────┤
│1 │スキー │
├─────┼─────┤
│2 │サーフィン│
├─────┼─────┤
│1 │スノボ │
├─────┼─────┤
│3 │乗馬 │
├─────┼─────┤


Sheet2
   A      B
┌─────┬────────┐
│ID │趣味 │
├─────┼────────┤
│1 │スキースノボ │
├─────┼────────┤
│2 │サーフィン │
├─────┼────────┤
│3 │乗馬 │
├─────┼────────┤

回答の条件
  • 1人3回まで
  • 登録:2009/05/06 02:45:22
  • 終了:2009/05/07 09:58:16

回答(3件)

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692009/05/06 08:07:38

ポイント27pt

VBAを使えば簡単にできそうですね。

VBAとはなんぞやという場合はこちらを参考にして標準モジュールにコピペして実行してみてください。

http://www.officepro.jp/excelvba/ini/index1.html

http://www.officepro.jp/excelvba/ini/index2.html

http://www.officepro.jp/excelvba/ini/index3.html


表ではIDは連番のようですけど、連番じゃない場合も考慮しています。

Sub Macro()
    Dim lastRow As Long
    Dim lastRow2 As Long
    Dim i As Long
    Dim j As Long
    Dim obj As Object
    
    With Worksheets("Sheet2")
        .Cells.Clear
        .Range("A1").Value = "ID"
        .Range("B1").Value = "趣味"
        
        lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        
        For i = 2 To lastRow
            Set obj = .Range("A:A").Find(what:=Worksheets("Sheet1").Cells(i, 1).Value, _
                LookAt:=xlWhole, MatchCase:=True, MatchByte:=True)
            If obj Is Nothing Then
                lastRow2 = .Cells(Rows.Count, 1).End(xlUp).Row
                .Cells(lastRow2 + 1, 1).Value = Worksheets("Sheet1").Cells(i, 1).Value
                .Cells(lastRow2 + 1, 2).Value = Worksheets("Sheet1").Cells(i, 2).Value
            Else
                obj.Offset(0, 1).Value = obj.Offset(0, 1).Value & _
                    Worksheets("Sheet1").Cells(i, 2).Value
            End If
            Set obj = Nothing
        Next i
    End With
End Sub

CONCATENATEが配列数式に使えたら関数1行でできるのだけどね。

id:kbkb

ありがとうございます。挑戦してみたのですが、Sheet!2のA列の並び順がSheet!1のA列の順番により書き換えられてしまうようです。

例示が足りなくてすみませんが、Sheet!2のA列にあって、Sheet!1のA列にない値も存在しますし、Sheet!1のA列はランダムにIDが振られています。

Sheet1

ID,原稿

11,あああ

12,いいい

11,ううう

21,えええ

22,おおお

21,かかか

13,ききき

22,くくく

14,けけけ

23,こここ

Sheet2

11

12

13

14

15

21

22

23

24

25

2009/05/06 13:55:41
id:jccrh1 No.2

jccrh1回答回数111ベストアンサー獲得回数192009/05/06 06:54:53

ポイント27pt

EXCELの関数では無理だと思います。

 

VBAでユーザ関数を作成するしかないと思います。

具体的には

 Sheet2!B2に =文字連結(A2,Sheet1!$A$2:$B$5,2)

VBAは

Function 文字連結(検索値, 範囲 As Range, 列番号 As Integer)
 文字連結 = ""
 For I = 1 To 範囲.Rows.Count
  If 検索値 = 範囲(I, 1) Then 文字連結 = 文字連結 & 範囲(I, 列番号).Value
 Next I
End Function

「文字連結」関数は「VLOOKUP」関数のように 検索値, 範囲, 列番号を指定します。

id:SALINGER No.3

SALINGER回答回数3454ベストアンサー獲得回数9692009/05/06 15:16:24

ポイント26pt

最初からシート2のA列にはIDが入っているのですね。

その場合は、もう少し簡単なコードになります。

Sub Macro()
    Dim lastRow As Long
    Dim lastRow2 As Long
    Dim i As Long
    Dim j As Long
    Dim str As String
    
    With Worksheets("Sheet2")
        lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        lastRow2 = .Cells(Rows.Count, 1).End(xlUp).Row
        For j = 2 To lastRow2
            str = ""
            For i = 2 To lastRow
                If .Cells(j, 1).Value = Worksheets("Sheet1").Cells(i, 1).Value Then
                    str = str & Worksheets("Sheet1").Cells(i, 2).Value
                End If
            Next i
            .Cells(j, 2).Value = str
        Next j
    End With
End Sub
id:kbkb

ありがとうございます。

甘えついでにもう少し教えていただきたいのですが、Sheet1!A列およびSheet2!B列は、数式によって値が表示されているため、このまま実行すると空白になってしまいます。

数式の結果表示されている値を対象に処理するようにVBA部分を変えたいのですが、教えていただけますでしょうか。

2009/05/06 16:16:11
  • id:nobnob3
    ごめんなさい。質問の意味がいまいち分かりません。
    最終的に欲しいアウトプットの例を明示してくれると助かります。
  • id:kbkb
    nobnob3さん

    ほしいアウトプットはSheet!2のB列です。
    IDをキーにSheet!1のB列から引っ張ってきた値で重複する場合は後方にドンドン文字列連結したいのです。
    仮にSheet!1で同一IDで趣味がスキー、スノボ、車、旅行とあった場合はSheet!2の当IDのB列にはスキースノボ車旅行 と表示したいのです。
  • id:SALINGER
    >Sheet2!B列は、数式によって値が表示されている
    Sheet2!B列はマクロの結果が入るので、書き間違いだと思いますが、
    Sheet1の値が数式の結果でも問題は無いはずです。
  • id:kbkb
    >Sheet2!B列はマクロの結果が入るので、書き間違いだと思いますが、
    >Sheet1の値が数式の結果でも問題は無いはずです。
    書き誤りました。
    Sheet1!A列およびSheet1!B列の値が数式の結果になっています。
    それぞれ実際の数値を貼り付けてテストしたところ、
    Sheet1!A列が数式⇒マクロ実行時にSheet2!B列に値が入らない
    Sheet1!B列が数式⇒問題なく値が入る

    となります。
    Sheet1!A列が数式の場合には判定に失敗しているようなのですが・・・
  • id:SALINGER
    表示形式を設定している場合、実際の値と見かけの値とが違う場合があります。
    例えば日付などは、内部ではシリアル値という数値になっています。
    それで、実際の見かけの値を使って処理するには、真ん中辺りにある
    >>
    If .Cells(j, 1).Value = Worksheets("Sheet1").Cells(i, 1).Value Then
      str = str & Worksheets("Sheet1").Cells(i, 2).Value
    End If
    <<

    >>
    If .Cells(j, 1).Text = Worksheets("Sheet1").Cells(i, 1).Text Then
      str = str & Worksheets("Sheet1").Cells(i, 2).Text
    End If
    <<
    と変更してみてください。
  • id:kbkb
    該当部分を.Value -> .Textに変換したら動きました!いろいろとありがとうございました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません