複数行のデータを列に整形したいと思っています。
画像のように縦に並んでいる「整形前」データを、店名から店名までの間にあるデータをひとつのまとまりとして扱い、「整形後」のようにしたいと思います。(実際のデータ数は3万行ほどあり、手作業では行えません。。)
データを区切る、「店名」は各データに必ず存在するのですが、その他のカラムは存在する場合と存在しない場合があり、行数が一定ではありません。
利用できる関数や方法などがございましたら、教えていただけますでしょうか。
どうぞ、よろしくお願いします。
No.1ベストアンサー
- 回答日時:
こんにちは!
元データの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
- 回答日時:
今仮に、「整形前」データの表の中の「カラム」と入力されているセルが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
- 回答日時:
例示のレイアウトで、D1セルから右に店名からの項目が記載されていて、項目数が最大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さんをベストアンサーとさせていただきました。
お時間を割いていただいて数式を作っていただき、本当にありがとうございました。とても助かりました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルのデータ整形について 3 2022/11/12 00:27
- Excel(エクセル) Power Query でのデータの一括修正について 2 2022/05/10 02:00
- Visual Basic(VBA) 【VBA】複数行あるカンマ区切りのデータを全て縦に一列に並べたい 5 2022/04/13 17:03
- Excel(エクセル) VBAで重複データを合算したい(時間) 1 2022/12/08 23:06
- Excel(エクセル) 名前と日付が一致する箇所にフラグを立てる関数が知りたいです 4 2022/08/11 02:24
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- Access(アクセス) Accessのクエリの結果を、既存のエクセルに追加したい 2 2022/07/31 22:44
- その他(Microsoft Office) Excel2019と365、2021 2 2023/07/08 06:22
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- HTML・CSS WEBサイトの構築。表示データとWEBデザインを分離する考え方を専門用語・業界用語では何と言うか? 8 2022/09/27 09:16
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報