(エクセル)複数行のデータを列に整形したい

質問

複数行のデータを列に整形したいと思っています。

画像のように縦に並んでいる「整形前」データを、店名から店名までの間にあるデータをひとつのまとまりとして扱い、「整形後」のようにしたいと思います。（実際のデータ数は3万行ほどあり、手作業では行えません。。）

データを区切る、「店名」は各データに必ず存在するのですが、その他のカラムは存在する場合と存在しない場合があり、行数が一定ではありません。

利用できる関数や方法などがございましたら、教えていただけますでしょうか。

どうぞ、よろしくお願いします。

回答No.1

tom04 · ベストアンサー

こんにちは！

元データのA列には必ず「店名」は存在する訳ですね？
そういう前提で、
VBAになってしまいますが・・・

番号と電話が存在しますがどちらも「電話番号」だとします。
元データには「都道府県」となっていますが、整列させたい項目は「住所」となっていますよね？
これでは何かと面倒ですので、
↓の画像の右側がSheet2で1行目項目は
都道府県（住所）に関しては「都道府県」に
番号（電話）は「電話番号」にしています。

以上の下準備ができた上での一例です。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, cnt As Long, endRow As Long
Dim wS1 As Worksheet, wS2 As Worksheet, myArea As Range, c As Range
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Set myArea = wS2.Range("A1:D1")
endRow = wS2.UsedRange.Rows.Count
If endRow > 1 Then
Range(wS2.Cells(2, "A"), wS2.Cells(endRow, "D")).ClearContents
End If
cnt = 1
For i = 2 To wS1.Cells(Rows.Count, "A").End(xlUp).Row
If wS1.Cells(i, "A") = "店名" Then
cnt = cnt + 1
wS2.Cells(cnt, "A") = wS1.Cells(i, "B")
Else
Set c = myArea.Find(what:=wS1.Cells(i, "A"), LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
j = c.Column
wS2.Cells(cnt, j) = wS1.Cells(i, "B")
End If
End If
Next i
End Sub 'この行まで

※　30000行程度データがあるようなのでそこそこ時間を要すると思います。m（_ _）m

お礼

早速のご回答ありがとうございました！上記に示していただいたとおりの方法で、無事にデータを作成することができました。

お時間を割いていただき、本当にありがとうございました。とても助かりました。

MackyNo1さんにお教えいただいた方法でも無事にできたのですが、最初に回答していただいたtom04さんを、ベストアンサーとさせていただきます。

回答No.3

kagakusuki

今仮に、「整形前」データの表の中の「カラム」と入力されているセルがSheet1のA3セルであるものとします。
　そして、Sheet3のA列を作業列として使用して、Sheet2に「整形後」の表を表示させるものとします。

　まず、Sheet3のA4セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$B:$B,ROW())="","",IF(ISNUMBER(MATCH(SUBSTITUTE(ASC(INDEX(Sheet1!$A:$A,ROW()))," ",),{"tell.","tell","コメント","市町村","住所","地名","店名","電話","電話番号","都道府県","番号","備考"},0)),LOOKUP(SUBSTITUTE(ASC(INDEX(Sheet1!$A:$A,ROW()))," ",),{"tell","コメント","市町村","住所","地名","店名","電話","都道府県","番号","備";"電話","コメント","住所","住所","住所","店名","電話","住所","電話","コメント"}),"不明")&"◆"&COUNTIF(Sheet1!$A$3:INDEX(Sheet1!$A:$A,ROW()),"店名"))

　尚、このサイトの入力欄には半角のカナ文字を入力する事が出来ないため紛らわしくなってしまっておりますが、上記関数の中に2箇所存在している「"tell",」の直後に記されている"コメント"は、必ず半角文字で記入して下さい。（その2箇所以外の所に記されている"コメント"は、必ず全角文字で記入して下さい）

　次に、Sheet3のA4セルをコピーして、Sheet3のA5以下に貼り付けて下さい。

　次に、Sheet2の
A4セルに　　　　　　　　　　　　店名
B4セルに　　　　　　　　　　　　住所
C4セルに　　　　　　　　　　　　電話
D4セルに（全角文字で）　　　コメント
と入力して下さい。
　次に、Sheet2のA4セルに次の関数を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,A$3&"◆"&ROWS($4:4)),INDEX(Sheet1!$B:$B,MATCH(A$3&"◆"&ROWS($4:4),Sheet3!$A:$A,0)),"")

　次に、Sheet2のA4セルをコピーして、Sheet2のB4～D4の範囲に貼り付けて下さい。
　次に、Sheet2のE4セルに次の関数を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,"不明◆"&ROWS($4:4)),"不明なカラム有:"&MATCH("不明◆"&ROWS($4:4),Sheet3!$A:$A,0)&"行目","")

　次に、Sheet2のA4～D4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　これで、整形後の表がSheet2上に自動的に作成されます。
　尚、もしも、Sheet1のA列のカラム欄に、上記の関数では何のカラムであるのか判断出来ないカラムが入力されていた場合には、Sheet2のE列上に「不明なカラムあり」という表示と共に、Sheet1の何行目のカラムの事であるのかが表示されます。

お礼

お礼が遅くなってしまいまして大変失礼しました。ご丁寧にお教えいただき、ありがとうございました！

申し訳ないのですが、最初にご回答いただいた方をベストアンサーとさせていただいております。

ありがとうございました。

回答No.2

MackyNo1

例示のレイアウトで、Ｄ1セルから右に店名からの項目が記載されていて、項目数が最大5つの場合、以下のような配列数式で該当データを表示できます。

D2セルに以下の式を入力し、Ctrl+Shift+Enterで確定して下方向にオートフィルコピーしてください。

=INDEX(B:B,SMALL(IF($A$2:$A$30000=D$1,ROW($A$2:$A$30000),30000),ROW(A1)))&""

同様にE2セルに以下の式を入力し、Ctrl+Shift+Enterで確定して右方向および下方向にオートフィルコピーしてください。

=IFERROR(INDEX($B:$B,MATCH(E$1,INDEX($A:$A,MATCH($D2,$B:$B,0)+1):INDEX($A:$A,MATCH($D2,$B:$B,0)+3),0)+MATCH($D2,$B:$B,0)),"")

上記の数式は多くのセルに入力するとシートの動きが重くなりますので、実際に表示させる場合は、例えば200行分づつオートフィルコピーをして、最終行以外の数式範囲を選択して右クリック「コピー」、そのままもう一度右クリック「形式を選択して貼り付け」で「値」を選択して、数式を文字列に変換してから、最終行のオートフィルコピーを続けて行ってください。

お礼

MackyNo1さん、ご回答ありがとうございました。お教えいただいました方法で、無事にデータを生成することができました。

申し訳ないのですが、最初にご回答を頂いたtom04さんをベストアンサーとさせていただきました。

お時間を割いていただいて数式を作っていただき、本当にありがとうございました。とても助かりました。