エクセル クロス集計からのデータ抽出、集計方法

下記の様なエクセルでクロス集計された結果についてデータを抽出、集計する方法を教えてください。

- a b c d e
A 1 0 0 5 0
B 1 0 0 0 0
C 0 3 0 0 0
D 0 0 0 1 1

実際のデータでは、0は空欄になっています。

上記のエクセルデータを下記の様に集計したいです。

データを含む項目
A a, d
B a
C b
D d, e

列、行ともに100以上データがあり、効率的にデータを集計する方法を教えて頂きたいです。
宜しくお願い致します。

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

• 2の式を入れましたが上手く行きませんでした。
  #VALUE!となってしまいます。

  No.4の回答に寄せられた補足コメントです。 補足日時：2017/10/16 19:28

• クロス集計のエクセルデータと今回質問した集計データの2種類ありまして、今回質問した集計データ形式にまとめたいため、質問しました。

  No.2の回答に寄せられた補足コメントです。 補足日時：2017/10/16 22:27

No.5 ベストアンサー 回答者： mike_g 回答日時： 2017/10/16 20:39

[No.4補足]へのコメント、

》 2の式を入れましたが上手く行きませんでした。
》 #VALUE!となってしまいます。
失礼しました。
ステップ2に書いた式の直後に次の一文を書き漏らしていました。
⇒【お断り】上式は必ず配列数式として入力のこと
ご存知かも知れませんが、入力法が、下記の通り、通常とは異なります。

１．該当式をコピーして指定のセルに入力(でも、まだ Enterキーを叩かない！)
￣￣(#VALUE!となっていたら、そのセルを選択して、F2キーをツンして直ぐに)
２．Ctrl+Shift+Enter を「エイヤッ！」と叩き付け
３．[数式バー]を眺めて、下記のように、入力した式が ｛ ｝で囲まれていることを確認

{=IF(COLUMN(A1)>$H2,"",INDEX($A$1:$F$1,SMALL(IF($B2:$F2>0,COLUMN($B2:$F2),""),COLUMN(A1))))}

【お断り】決して、上以外の方法で ｛ ｝で囲んではなりませぬ。

この回答へのお礼

詳しい説明有難う御座います。問題なく出来ました。
本当に有難う御座いました。

お礼日時：2017/10/17 13:05

No.4 回答者： mike_g 回答日時： 2017/10/16 01:36

１．式 =COUNTA(B2:F2) を入力したセル H2 を下方にオートフィル

２．次式を入力したセル I2 を右方および下方にオートフィル
￣￣ =IF(COLUMN(A1)>$H2,"",INDEX($A$1:$F$1,SMALL(IF($B2:$F2>0,COLUMN($B2:$F2),""),COLUMN(A1))))
３．列Ｌは空白のままに放置して、次式を入力したセル M2 を右方および下方にオートフィル
￣￣ =IF(COLUMN(A1)>MAX($H$2:$H$5),"",L2&" "&I2)
４．次式を入力したセル R2 を下方にオートフィル
￣￣ =SUBSTITUTE(TRIM(N2)," ",",")

No.3 回答者： d-q-t-p 回答日時： 2017/10/15 22:01

ただ該当するデータを１セルにまとめるだけなら TEXTJOIN関数でできます。

https://detail.chiebukuro.yahoo.co.jp/qa/questio …

ただ Excelのバージョンが 2016で Office365サブスクライバーであること
が大前提となります。

この回答へのお礼

回答ありがとうございます。
エクセルが2016バージョンでなかったので、確認できませんでした。
新しいバージョンのエクセルが欲しいものです。

お礼日時：2017/10/16 22:29

No.2 回答者： d-q-t-p 回答日時： 2017/10/15 21:54

結局 何を集計するんですか？

データを抽出して組み替えているだけに見えるんですが。

No.1 回答者： tom04 回答日時： 2017/10/15 21:45

こんばんは！

抽出するのは複数列でよいのであれば、関数で出来ると思いますが、
質問文では1セルにカンマで区切って表示をお望みのようなので、VBAでの一例です。

タイトルに
＞データ抽出、集計方法

とありますので、↓の画像のように
元データはSheet1にあり、Sheet2に抽出、集計をするようにしました。
尚、Sheet2のA列は入力済みだとします。

標準モジュールにしてください。

Sub Sample1()
Dim i As Long, j As Long, wS As Worksheet, c As Range
Dim lastRow As Long, lastCol As Long, myStr As String

Set wS = Worksheets("Sheet1")
With Worksheets("Sheet2")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Range(.Cells(2, "B"), .Cells(lastRow, "C")).ClearContents
For i = 2 To wS.Cells(Rows.Count, "A").End(xlUp).Row
Set c = .Range("A:A").Find(what:=wS.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
For j = 2 To wS.Cells(1, Columns.Count).End(xlToLeft).Column
If wS.Cells(i, j) <> "" Then
myStr = myStr & wS.Cells(1, j) & ","
With .Cells(c.Row, "C")
.Value = .Value + wS.Cells(i, j)
End With
End If
Next j
.Cells(c.Row, "B") = Left(myStr, Len(myStr) - 1)
myStr = ""
Next i
.Columns.AutoFit
End With
End Sub

※　Sheet1のA列のデータに重複はない！という前提です。
※　関数でないので、Sheet1のデータ変更があるたびにマクロを実行する必要があります。

これで画像のような感じになります。m(_ _)m

この回答へのお礼

有難うございました。問題無く出来ました。本当に有難うございました。

お礼日時：2017/10/16 22:13