表の統合

質問

エクセルで表を作るときの簡単な方法を探しています。

今、下のようなデータ１とデータ２があります。（ほんとうはたくさんあるのですが、省略させていただきます）
そのデータを統合してtotalというデータを作る場合、どのような方法があるでしょうか。

Ｄ１ 　　Ｄ２
名前 出身 頻度 名前 出身 頻度
山田 東京 3 山田 東京 2
伊藤 埼玉 1 山田 埼玉 1
梅田 大分 2 伊藤 埼玉 2

total
名前 出身　 D1 D2
山田 東京 3 2
伊藤 埼玉 1 2
梅田 大分 2 0
山田 埼玉 0 1

回答No.3

wisemac21 · ベストアンサー

┌─┬──┬──┬─┬──┬──┬─┐
│ │ A │ B │ C│ D │ E │ F│
├─┼──┼──┼─┼──┼──┼─┤
│1 │名前│出身│D1│名前│出身│D2│
├─┼──┼──┼─┼──┼──┼─┤
│2 │山田│東京│3 │山田│東京│2 │
├─┼──┼──┼─┼──┼──┼─┤
│3 │伊藤│埼玉│1 │山田│埼玉│1 │
├─┼──┼──┼─┼──┼──┼─┤
│4 │梅田│大分│2 │伊藤│埼玉│2 │
└─┴──┴──┴─┴──┴──┴─┘
表を上記のようにします。データの統合を行うため作業列を設け
次のような表にします。
C2==A2&" "&B2、G2=E2&" "&F2として名前と出身を間に半角スペースを入れて結合する
┌─┬──┬──┬─────┬─┬──┬──┬─────┬─┐
│ │ A │ B │ C │ D│ E │ F │ G │ H│
├─┼──┼──┼─────┼─┼──┼──┼─────┼─┤
│1 │名前│出身│ │D1│名前│出身│ │D2│
├─┼──┼──┼─────┼─┼──┼──┼─────┼─┤
│2 │山田│東京│山田 東京 │3 │山田│東京│山田 東京 │2 │
├─┼──┼──┼─────┼─┼──┼──┼─────┼─┤
│3 │伊藤│埼玉│伊藤 埼玉 │1 │山田│埼玉│山田 埼玉 │1 │
├─┼──┼──┼─────┼─┼──┼──┼─────┼─┤
│4 │梅田│大分│梅田 大分 │2 │伊藤│埼玉│伊藤 埼玉 │2 │
└─┴──┴──┴─────┴─┴──┴──┴─────┴─┘
次にTOTALの表を作成するセル範囲の基準位置としてセルJ1を選択し、
[データ]→[統合]を選択します。
「統合の設定」ダイアログボックスで
集計の方法を「合計」
統合元範囲として、セル範囲C1:D4を入力し、追加ボタンをクリック
統合元範囲として、セル範囲G1:H4を入力し、追加ボタンをクリック
統合の基準の「上端行」と「左端列」の両方にチェックを入れて、
OKをクリックと次のような表が作成されます。
┌─┬─────┬─┬─┐
│ │ J │ K│ L│
├─┼─────┼─┼─┤
│1 │ │D1│D2│
├─┼─────┼─┼─┤
│2 │山田 東京 │3 │2 │
├─┼─────┼─┼─┤
│3 │山田 埼玉 │ │1 │
├─┼─────┼─┼─┤
│4 │伊藤 埼玉 │1 │2 │
├─┼─────┼─┼─┤
│5 │梅田 大分 │2 │ │
└─┴─────┴─┴─┘
ここでJ列の名前と出身を分割するために、K列を選択して、1つ列を挿入します。
┌─┬─────┬─┬─┬─┐
│ │ J │ K│ L│ M│
├─┼─────┼─┼─┼─┤
│1 │ │ │D1│D2│
├─┼─────┼─┼─┼─┤
│2 │山田 東京 │ │3 │2 │
├─┼─────┼─┼─┼─┤
│3 │山田 埼玉 │ │ │1 │
├─┼─────┼─┼─┼─┤
│4 │伊藤 埼玉 │ │1 │2 │
├─┼─────┼─┼─┼─┤
│5 │梅田 大分 │ │2 │ │
└─┴─────┴─┴─┴─┘
次にJ列を選択して、[区切り位置]で「区切り文字」にスペースを選択して、
「完了」をクリックすると次のようになります。
┌─┬──┬──┬─┬─┐
│ │ J │ K │ L│ M│
├─┼──┼──┼─┼─┤
│1 │ │ │D1│D2│
├─┼──┼──┼─┼─┤
│2 │山田│東京│3 │2 │
├─┼──┼──┼─┼─┤
│3 │山田│埼玉│ │1 │
├─┼──┼──┼─┼─┤
│4 │伊藤│埼玉│1 │2 │
├─┼──┼──┼─┼─┤
│5 │梅田│大分│2 │ │
└─┴──┴──┴─┴─┘
データの空欄を0にするために、L列とM列を選択して、[編集]→[ジャンプ]
「選択オプション」ダイアログボックスで「空白セル」をONにして、OKをクリック
そのままの状態で、0を入力し、CTRL+ENTERで確定する。

お礼

ありがとうございます。かなりのヒントになりました。スペースで結合して、後で切り離すという斬新な方法があるとは気付きませんでした。

回答No.2

mitarashi

Accessで、標準では出来ない、完全外部結合というのを使えば出来ます。参考URLの様に、同じデータから、右外部結合、左外部結合それぞれのクエリを作って、SQLを取得し、UNIONを介してくっつければできます。
下記は、Sheet2及びSheet3のデータからインポートしたテーブルについてやってみた事例です。
SELECT Sheet2.名前, Sheet2.出身, Sheet2.頻度, Sheet3.頻度
FROM Sheet2 LEFT JOIN Sheet3 ON (Sheet2.出身 = Sheet3.出身) AND (Sheet2.名前 = Sheet3.名前)
UNION
SELECT Sheet3.名前, Sheet3.出身, Sheet2.頻度, Sheet3.頻度
FROM Sheet2 RIGHT JOIN Sheet3 ON (Sheet2.名前 = Sheet3.名前) AND (Sheet2.出身 = Sheet3.出身)
ORDER BY 1;
結果は下記の通り（０は入りませんけどね）Office LinkでExcelに書き出しました。
..1..................名前..................出身.....Sheet2.頻度.....Sheet3.頻度
..2..................伊藤..................埼玉.........................1........................2
..3..................山田..................埼玉...................................................1
..4..................山田..................東京........................3........................2
..5..................梅田..................大分........................2..........................

ついでに、興味本位でVBAでやってみた事例です。「簡単に」からは非常に外れると思いますが．．．
Const delimiterChar As String = "☆"

Sub conbine()
Dim i As Long, j As Long
Dim splitArray As Variant
Dim targetRange As Range
Dim targetRow As Range
Dim destRange As Range
Dim myDic As Object, myKey As Variant

'2番目、3番目のシートのA1から入っているデータを結合して、4番目のシートに出力
Set myDic = CreateObject("Scripting.Dictionary")
Sheets(4).Range("a1:d1") = Array("名前", "出身", "D1", "D2")
Set destRange = Sheets(4).Range("A2")
Set targetRange = Sheets(2).Range("A1").CurrentRegion
Set targetRange = targetRange.Offset(1, 0).Resize(targetRange.Rows.Count - 1, targetRange.Columns.Count)
Call classify(targetRange, myDic)
Set targetRange = Sheets(3).Range("A1").CurrentRegion
Set targetRange = targetRange.Offset(1, 0).Resize(targetRange.Rows.Count - 1, targetRange.Columns.Count)
Call classify(targetRange, myDic)
myKey = myDic.keys
For i = 1 To myDic.Count
splitArray = Split(myKey(i - 1), delimiterChar)
destRange.Value = splitArray(0)
destRange.Offset(0, 1).Value = splitArray(1)
For j = 1 To myDic(myKey(i - 1)).Count
With myDic(myKey(i - 1))(j)
Select Case .Parent.Name
Case "Sheet2"
destRange.Offset(0, 2) = .Value
Case "Sheet3"
destRange.Offset(0, 3) = .Value
End Select
End With
Next j
Set destRange = destRange.Offset(1, 0)
Next i
Set myDic = Nothing
End Sub

Private Sub classify(targetArray As Range, ByRef myDic As Object)
Dim targetRow As Range
Dim keyString As String
Static rangeCollection As Collection

For Each targetRow In targetArray.Rows
With targetRow
keyString = .Cells(1) & delimiterChar & .Cells(2)
If Not myDic.exists(keyString) Then
Set rangeCollection = New Collection
rangeCollection.Add .Cells(3)
myDic.Add keyString, rangeCollection
Else
myDic.Item(keyString).Add .Cells(3)
End If
End With
Next
End Sub

※rangeCollectionはstatic宣言しなくても動きます。myDicの一部として保持されるのかも。

お礼

ありがとうございます。でも、ちょっと、自分の実力では難しいかもしれません。

回答No.1

imogasi

エクセルには「統合」という特別な機能があり、いわばエクセルの術語です。
それと関係のない、一般的な意味で使っているようだ。紛らわしいのではっきりさせること。
質問では「表の組み換え」といったような意味かな。
ーーーー
実例を挙げるのはよいが、どういうことをするのか、文章でも書き添えないとわかり難い。
２塊（名前 出身 頻度）のデータを別シートにおいて１塊（名前 出身 頻度）に並べるのかな。
その際名前＋出身地で１個人と判定するのかな。そして同一人のD1、D2は加算しないで別列に保存するのかな。
D1の塊のデータ内では同一人が再度出現は無いのだろうね。
ーー
こういう表の組み換えは関数では複雑になって難しい。
VBAを使う必要があると思う。
VBAでも、相当経験したものでないと、コードを作れないように予想する。
ーー
例データ
３塊（A-I列の３ｘ３列）でやってみた。
Sheet1　A-I列
山田 東京 3 山田 東京 2 山田 東京 3
伊藤 埼玉 1 山田 埼玉 1 山田 長野 1
梅田 大分 2 伊藤 埼玉 2 伊藤 埼玉 5
木村 神奈川 5 木村 神奈川 3
鈴木 靜岡 2
ーーー
３列（１塊）に整形する。下の方に継ぎ足す。
標準モジュール
Sub test01()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
c = sh1.Range("IV1").End(xlToLeft).Column
' MsgBox c
k = 1
no = 1
For j = 1 To c Step 3
d = sh1.Cells(65536, j).End(xlUp).Row
sh1.Range(sh1.Cells(1, j), sh1.Cells(d, j + 2)).Copy sh2.Cells(k + 1, 1)
sh2.Range(sh2.Cells(k + 1, "D"), sh2.Cells(k + d, "D")) = no
k = k + d
no = no + 1
Next j
End Sub
ーー
結果
Sheet2
上記結果が出るが、それを
A,B、D列でソートして
木村 神奈川 5 2
木村 神奈川 3 3
鈴木 靜岡 2 2
伊藤 埼玉 1 1
伊藤 埼玉 2 2
伊藤 埼玉 5 3
山田 長野 1 3
山田 埼玉 1 2
山田 東京 3 1
山田 東京 2 2
山田 東京 3 3
梅田 大分 2 1
ーーー
標準モジュールに
Sub test02()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet2")
Set sh2 = Worksheets("Sheet3")
d = sh1.Range("A65536").End(xlUp).Row
MsgBox d
k = 1
m = ""
For i = 2 To d
If m = sh1.Cells(i, "A") & sh1.Cells(i, "B") Then
sh2.Cells(k, 2 + sh1.Cells(i, "D")) = sh1.Cells(i, "C")
Else
k = k + 1
sh2.Cells(k, "A") = sh1.Cells(i, "A")
sh2.Cells(k, "B") = sh1.Cells(i, "B")
sh2.Cells(k, 2 + sh1.Cells(i, "D")) = sh1.Cells(i, "C")
End If
m = sh1.Cells(i, "A") & sh1.Cells(i, "B")
Next i
End Sub
そして上記test02を実行する。
ーーー
結果
Sheet3　A-E列（DはC列、ｄ２はD列、D3はE列にセット）
木村 神奈川 5 3
鈴木 靜岡 2
伊藤 埼玉 1 2 5
山田 長野 1
山田 埼玉 1
山田 東京 3 2 3
梅田 大分 2
ーー
少数例でしかテストできてない。
氏名等の並び順が漢字コード順になり、当初の順番を崩す、という欠点があるが、とりあえずやってみました。

お礼

ありがとうございます。