Excel 集計済みのデータをローデータ化したい

質問

既出でしたら、申し訳ございません。
簡単なようで、アイデアがなく質問させていただきます。

図の左側のように、既に「日ごと」「品物別」に 集計が
された状態の表があったとします。
（ピボットではなく、手作業で集計されたものとします）

図の右側のように、集計する前のローデータ に変換をしたいのですが、
何かいい方法はないでしょうか？

目的は、ローデータをもとに、ピボットで集計しなおしたり、
タブローなどの集計ツールに、生データを格納しておくなどです。

Excel マスターの皆様、どうかお助けください。
よろしくお願いいたします。

回答No.4

imogasi

＃１です。
VBAでの回答もやってみるらしいから、
ーーー
ＶＢＡの回答は出ていますが、別個人で書き方が違うと、下記のようになります。コードの印象がちがいますよね。
Sub test02()
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
lr = sh1.Range("A100000").End(xlUp).Row: MsgBox lr
lc = sh1.Range("XFD1").End(xlToLeft).Column: MsgBox lc
k = 2
'---
For i = 2 To lr
For j = 2 To lc
If sh1.Cells(i, j) <> "" Then
sh2.Cells(k, "A") = sh1.Cells(i, 1)
sh2.Cells(k, "B") = sh1.Cells(1, j)
sh2.Cells(k, "c") = 1
k = k + 1
If sh1.Cells(i, j) > 1 Then
For l = 2 To sh1.Cells(i, j)
sh2.Cells(k, "A") = sh1.Cells(i, 1)
sh2.Cells(k, "B") = sh1.Cells(1, j)
sh2.Cells(k, "c") = 1
k = k + 1
Next l
End If
End If
Next j
Next i
End Sub
例データ　Ｓｈｅｅｔ１
ー リンゴ メロン いちご
2017/4/1 1 1
2017/4/2 3 1 1
2017/4/3 1
2017/4/4 1 1 1
2017/4/5 1 1
結果　Ｓｈｅｅｔ２
2017/4/1 リンゴ 1
2017/4/1 メロン 1
2017/4/2 リンゴ 1
2017/4/2 リンゴ 1
2017/4/2 リンゴ 1
2017/4/2 メロン 1
2017/4/2 いちご 1
2017/4/3 メロン 1
2017/4/4 リンゴ 1
2017/4/4 メロン 1
2017/4/4 いちご 1
2017/4/5 メロン 1
2017/4/5 いちご 1
件数1が要らない場合は
sh2.Cells(k, "c") = 1　の行（コード）を省いてください。

回答No.3

bunjii

貼付画像のような結果（K1:L28）で良ければ作業用テーブル（F1:I81）を使って関数のみでできます。
但し、Excel 2007以降のバージョンによるものです。
F1=IF(ISTEXT(H1),ROW(),"")
F1をF2:F81へコピーする。
H1="日付"
I1="売れたもの"
H2=IF(INT((ROW()-MAX($F$1:$F1)-1)/3)<COUNT(A:A),OFFSET($A$1,INT((ROW()-MAX($F$1:$F1)-1)/3)+1,0),"")
I2=IF(IFERROR(VLOOKUP(H2,A:D,MOD((ROW()-MAX($F$1:$F1)-1),3)+2,FALSE)>0,),OFFSET($A$1,0,MOD((ROW()-MAX($F$1:$F1)-1),3)+1),"")
H2:I2を下へ81行目までコピーする。
K2=INDEX(H:H,MOD(SMALL(IFERROR((I$2:I$101<>"")*H$2:H$101+(I$2:I$101="")*"3000/1/1","3000/1/1"*1)*1000+ROW(I$2:I$101),ROW()-1),1000))
この数式はCtrl+Shft+Enterキーで確定する。
L2=INDEX(I:I,MOD(SMALL(IFERROR((I$2:I$101<>"")*H$2:H$101+(I$2:I$101="")*"3000/1/1","3000/1/1"*1)*1000+ROW(I$2:I$101),ROW()-1),1000))
この数式もK2と同様のCtrl+Shft+Enterキーで確定する。
K2:L2を下へ28行目までコピーする。
当方はExcel 2013で検証しました。

回答No.2

tsubu-yuki

一先ず、用語の整理。
　ローデータ ＝　加工されていない生データ
ですから、「ローデータに変換」って変な日本語ですね。

さて、本題。
ワークシート関数でも何とかなるのかもしれませんが、
件数が多くなればなるほど式も複雑になりますし、
なんせエクセルの動きが渋くなる可能性も高いです。
私は関数は不得手なので提示できませんが(笑)。

なので私もＶＢＡを推しますね。
例示のデータ件数くらいなら１０数行で書ききれると思いますし。
例えば、ごく単純な考え方で、極力（無駄があっても）簡単な処理で
Sub Sample1()
Dim TagRow As Long, LastRow As Long, LastCol As Long
Dim Target As Range

　　Range("F:G").Delete
　　LastRow = Cells(Rows.Count, 1).End(xlUp).Row
　　LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
　　Range("F1:G1") = Array("日付", "売れたもの")

　　TagRow = 2
　　For Each Target In Range(Cells(2, 2), Cells(LastRow, LastCol))
　　　　If Target.Value > 0 Then
　　　　　　i = 0
　　　　　　Do
　　　　　　　　Cells(TagRow, 6) = Cells(Target.Row, 1)
　　　　　　　　Cells(TagRow, 7) = Cells(1, Target.Column)
　　　　　　　　TagRow = TagRow + 1: i = i + 1
　　　　　　Loop Until i = Target
　　　　End If
　　Next
　　Columns(6).NumberFormatLocal = "yyyy/m/d"
End Sub
Ａ～Ｄ列に集計表があると仮定し、Ｆ～Ｇ列にテーブルを返します。
行・列、表の位置、その他の条件についてのご提示が無いので、
この中では全く考慮していません。
よって、「どこをどういじれば実状に合うか」等の
追加質問には基本的には応じませんので悪しからず。

とりあえず・・・「逆だよなー」と、いつも思います(笑)。
右が素材で左が集計結果ですね。
データベースの考え方としては素材を集計して結果を出すのであり、
得られるべき結果から素材を引き出す・・
つまり、与えられた「生データ（現状ではこれがローデータですね）」から
データテーブルを再構築して、今後の「ローデータ」として使いたい、
という作業ですので、通常とは真逆の作業なわけです。
なので、ＶＢＡで強引に・・という事ですね。

ただし、他人が書いたマクロを鵜呑みにするのは危険です。
世の中には（私のように）悪意あるマクロを提示する人間も
若干名ではありますが存在しますので、お気を付けくださいませ。

お礼

ご丁寧にありがとうございます。
VBA が分かるスタッフがいたので、参考にしてトライしてみます！

回答No.1

imogasi

VBAでやれば、ロジックは、簡単。ＶＢＡはできる？
ＶＢＡを勉強したら。
ーー
できないなら、数が少なければ、手作業でやればよい。
ただし同一日・同一品目が１セルに複数件数のままでよいなら、手作業でやれる。その先は手作業ではむつかしかろう。
でも関数マニアの回答者が回答するかもしれないから、気長に回答を待っていたら。
ーー
エクセルでは、関数では、表の組換えは、苦手な分野だと思う。特に行やセルを増やすほうに持って行くのは極く難しい（式が長く複雑）ように思う。

お礼

ありがとうございます！
VBA はできないんです。。。
もう少し待ってみます！