表形式データの展開

質問

表形式に入力されている数値データを、1行に数値データが１つだけ持つフラットな形に変換（展開？）したいのですが、どうやるのが効率的でしょうか。（実際のイメージは下記のとおりです。）

■変換前
　a b c d
A 1 0 0 2
B 2 0 3 1
C 5 0 2 5

■変換後
a,A,1
a,B,2
a,C,5
b,A,0
b,B,0
b,C,0
c,A,0
c,B,3
c,C,2
d,A,2
d,B,1
d,C,5

元のファイルはExcelです。複数あるファイルのデータを、全て変換して合体させるので、簡単な方法が良いです。使うソフトはExcelでもAccessでも構いません。（データ量が多くなる予定なので、合体させたデータは最終的にAccessに取り込む予定です。）

どなたか知恵を貸してください。

回答No.2

maruru01

こんにちは。maruru01です。

元のデータがSheet1のA1:E4にあるとします。
Sheet2のA1に、

=OFFSET(Sheet1!$A$1,,INT((ROW()-1)/COUNTA(Sheet1!A:A))+1)

と入力します。
Sheet2のB1に、

=OFFSET(Sheet1!$A$1,MOD(ROW()-1,COUNTA(Sheet1!A:A))+1,)

と入力します。
Sheet2のC1に、

=INDEX(OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!A:A),COUNTA(Sheet1!$1:$1)),MATCH(B1,Sheet1!A:A,0)-1,MATCH(A1,Sheet1!$1:$1,0)-1)

と入力します。
そうしたら、このA1:C1を、適当な行までコピーします。
（元のデータ数、質問の例なら3×4=12行分で十分です。）
C列の下の方の行に「#N/A」エラーが表示されたら、エラー以降の数式の入った行を全て削除しておきます。
これで変換出来ました。
後は、Sheet2をAccessからインポートすればOKです。
（もちろん、Sheet2をいったんテキストファイルとして保存して、それをAccessからインポートしてもOK。）

お礼

うまくできました。ただ実際のデータはもっと多いので、こちらを参考にして検討してみます。ありがとうございました。

回答No.1

noname#240783

どの程度まで「効率的」を求めていらっしゃるか
今ひとつ？ですが、
質問中の例だけみると、以下のVBAコードで基本的な
変換はできますね。
あとは、変換後の複数シートのデータをひとつにする辺りも
VBAでやるか、手作業でやっつけるか、というところでしょうか。

'==========
Sub Foo()
Dim i As Integer
Dim j As Integer
Dim g As Integer
Dim r As Integer

i = 2
j = 2
g = i
r = 1

While Cells(1, j).Value <> ""
While Cells(i, j).Value <> ""
Worksheets("Sheet2").Cells(g - 1, r).Value = Cells(1, j).Value
Worksheets("Sheet2").Cells(g - 1, r + 1).Value = Cells(i, 1).Value
Worksheets("Sheet2").Cells(g - 1, r + 2).Value = Cells(i, j).Value
i = i + 1
g = g + 1
Wend
j = j + 1
i = 2
Wend

End Sub

お礼

ありがとうございました。VBAの知識に乏しいので、できるかどうか不安ですが、参考にしてやってみようと思います。