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

Question

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

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

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

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

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

tom04 · Accepted Answer

こんにちは！

元データの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

kagakusuki · Answer

今仮に、「整形前」データの表の中の「カラム」と入力されているセルが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の何行目のカラムの事であるのかが表示されます。

MackyNo1 · Answer

例示のレイアウトで、Ｄ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行分づつオートフィルコピーをして、最終行以外の数式範囲を選択して右クリック「コピー」、そのままもう一度右クリック「形式を選択して貼り付け」で「値」を選択して、数式を文字列に変換してから、最終行のオートフィルコピーを続けて行ってください。

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

こんにちは！

今仮に、「整形前」データの表の中の「カラム」と入力されているセルがSheet1のA3セルであるものとします。

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

　今仮に、「整形前」データの表の中の「カラム」と入力されているセルがSheet1のA3セルであるものとします。