人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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 │乗馬 │
├─────┼────────┤

●質問者: kbkb
●カテゴリ:コンピュータ
✍キーワード:Excel けが キー サーフィン スキー
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● SALINGER
●27ポイント

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行でできるのだけどね。

◎質問者からの返答

ありがとうございます。挑戦してみたのですが、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


2 ● jccrh1
●27ポイント

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」関数のように 検索値, 範囲, 列番号を指定します。


3 ● SALINGER
●26ポイント

最初からシート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
◎質問者からの返答

ありがとうございます。

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

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ