データ分析
2023/03/16
上野 桃香

【Excel】VBAでデータ整形 (表レイアウト変更) を自動化

Excel VBAでデータ整形 (表レイアウト変更) を自動化しました。
表レイアウト変更に役立つコードもまとめ、解説してあります。

はじめに

集めた元データの表レイアウトが使いにくい場合、集計や分析がしやすい表レイアウトへ変更する必要がありますよね。

今回はエクセル用のプログラミングコードVBAを用いることによってデータ整形(主に表レイアウト変更)を行いました。

なぜVBAを使うのかという疑問に思う方もいると思いますが、実際のデータは10万行越えになるため手動ではデータ整形が不可能でした。ゆえにVBAを用いることにしました。

ここではサンプルデータを用いて説明させていただきます。

<この記事がおすすめの方>

・Excelでデータ整形を自動で効率的に行いたい方

・Excelでデータ整形に役立つVBAコードを知りたい方

・Excelで列や行を入れ替える必要があるデータ整形を行いたい方

・集めた元データを使いやすい表にまとめたい方

そもそも集計や分析がしやすいレイアウトとは

そもそも集計や分析がしやすい表レイアウトとは、以下の写真のように

①見出しが1行

②1列1行データ

③1列同種類データ

の上記3つが守られているデータのことを指します。

データ分析においてはデータを使いやすい表形式に直すことは重要です。

つまりこの記事では、ExcelのVBAを使用して、元データの表レイアウトを集計や分析しやすい表レイアウトに変更する方法を紹介していくということになります。

VBAとは

VBAとは、Visual Basic for Applicationsの略で、Microsoft Office製品に搭載されたプログラミング言語です。VBAを使用することで、ExcelやWordなどのOfficeアプリケーションの様々な機能を拡張することができます。

また、この後にVBAのデータ整形に便利な構文についても解説しています。VBAを始めたばかりの方や、プログラミング初心者の方でも理解しやすいように、丁寧に説明しています。Excelでの作業が多い方は、ぜひVBAを学んで作業効率をアップさせてみてください。

VBAでデータ整形_1_元データ

具体的なデータを出さないとイメージが湧きにくいと思うので、全体の流れについて説明します。

目標は以下の写真のような元データの表レイアウトを分析・集計しやすい表レイアウトに変形することです。

この元データのままだといざ集計や分析を行う際に使いにくいデータになっています。

SQLやPythonでデータ整形を行うことも考えましたが、行の入れ替えやコピー、ペーストを自由に操作できるVBAが今回適任となりました。

VBAでデータ整形_2_データ整形後

データ整形後は以下の写真のようになります。

データ整形を行うことによって集計や分析がしやすい表になりました。

集計や分析がしやすい表の3原則が守られていますね。

VBAでデータ整形_3_実行イメージ

VBAで書いたコードを実行すると動画にあるように自動でデータ整形してくれます。

動画を再生すると分かる通り、とても便利ですね。

元データが10万行以上のサイズの時にはこのコードを実行することで楽にデータ整形できます。

ExcelにVBAでプログラムを書くという機能があって助かりました。

VBAでデータ整形_4_VBA便利コードの紹介

データ整形に便利なVBAコードについて解説します。

①シートを変数に格納


Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
'コピーしたいシート名を指定
Set Sht1 = Sheets("Sheet1")
'貼り付けたいシート名を指定
Set Sht2 = Sheets("Sheet2")


これは後ほどとても役立ちます。

②セル範囲を指定してコピー

'Sheet1のセル(1,1)からセル(1,3)の範囲をコピー
Sht1.Range(Sht1.Cells(1, 1), Sht1.Cells(1,3)).Copy


Sht1に格納されているワークシート名のコピーしたい範囲をRangeで指定しています。

Sht1.Range(”A1”).CopyというCells()ではなくA1などでも指定可能ですが

今回は数字の変数を後にコピーしたい範囲にもいれるためCellsで範囲指定しておくと便利です。

③コピーした範囲をペースト(普通)

'Sheet2のセル(1,1)からセル(1,3)にコピーしたものをペースト
Sht2.Range(Sht2.Cells(1, 1), Sht2.Cells(1, 3)).PasteSpecial


こちらも同様にペーストしたい範囲を指定しています。

④コピーした範囲をペースト(行と列を入れ替え)

'Sheet2のセル(1,1)からセル(1,3)にコピーしたものを行と列を入れ替えてペースト
Sht2.Range(Sht2.Cells(1, 1), Sht2.Cells(1, 3)).PasteSpecial Transpose:=True


これがとても便利です。

後ろにTranspose:=Trueを追加することで行と列を入れ替えてペーストできます。

ほかにも色々なペーストの種類があるので是非試してみてください。

書式のみ貼り付けや値のみ貼り付けなど便利なものがたくさんあります。

試しに①~③を使って実行しましょう。

以下のようなSheet1があったとします。紹介した便利なVBAを使ってこれと同じものをSheet2にコピーしましょう。

VBAでデータ整形_5_VBA便利コードの紹介_例を用いて実行

準備ができたら以下のコードを実行しましょう。


Sub example()

Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
'コピーしたいシート名を指定
Set Sht1 = Sheets("Sheet1")
'貼り付けたいシート名を指定
Set Sht2 = Sheets("Sheet2")

'Sheet1のセル(1,1)からセル(1,3)の範囲をコピー
Sht1.Range(Sht1.Cells(1, 1), Sht1.Cells(1,3)).Copy

'Sheet1のセル(1,1)からセル(1,3)にコピーしたものをペースト
Sht2.Range(Sht2.Cells(1, 1), Sht2.Cells(1, 3)).PasteSpecial


そして実行するとSheet1からSheet2にきちんとコピーしてペーストできていることが分かると思います。

VBAでデータ整形_6_コード

基本的に今回のコードで使うVBA文法は①~④のみです。

これらをFor文や変数と組み合わせることで自由にコピーしてペーストができます。

使う人によって元データの形式が異なっていたりすると思いますので、適宜上記の文法とFor文と変数を組み合わせることでレイアウト変更の自動化を行いましょう。

先ほどの文法とFor文や変数で組み合わせて完成したコードがこちらになります。


Sub paste_example()

Dim Sht1 As Worksheet
Dim Sht2 As Worksheet

'参考にしたいデータがあるシート名を指定
Set Sht1 = Sheets("元データ")
'加工したデータを貼り付けるシート名を指定
Set Sht2 = Sheets("加工後データ")

Dim i As Integer
Dim roop_num As Integer
Dim date_num As Integer
Dim paste_row As Integer
Dim paste_row_next As Integer
Dim coloum_num As Integer
Dim shop_coloum_num As Integer
Dim date_start_coloum As Integer
Dim date_end_coloum As Integer
Dim row_start As Integer
Dim row_end As Integer

'ループ回数 今回はレコードが3×6行あるので6回実行する
roop_num = 6
'ここでいう客数、売上、売上数を指すカラム数
coloum_num = 3
'ここでいう都道府県、店舗名を指すカラム数
shop_coloum_num = 2
'日付データの開始列
date_start_coloum = 4
'日付データの終了列
date_end_coloum = 11
'日付のセルの個数 今回は18日から25日なので8日間
date_num = date_end_coloum - date_start_coloum + 1

For i = 0 To roop_num - 1
'データの貼り付け開始行
paste_row = 2 + i * date_num
'次のデータの貼り付け開始行
paste_row_next = 2 + (i + 1) * date_num

'都道府県と店舗名をコピー
Sht1.Range(Sht1.Cells(2+i*coloum_num,1),Sht1.Cells(2+i*coloum_num,shop_coloum_num)).Copy
'都道府県と店舗名を貼り付ける
Sht2.Range(Sht2.Cells(paste_row,2),Sht2.Cells(paste_row_next-1,2)).PasteSpecial xlPasteAll
'日付をコピー
Sht1.Range(Sht1.Cells(1,date_start_coloum),Sht1.Cells(1, date_end_coloum)).Copy
'日付を転置して貼り付ける
Sht2.Cells(paste_row, 1).PasteSpecial Transpose:=True

'客数、売上数、売上のコピー開始列を指定
row_start =2 +coloum_num * i
'客数、売上数、売上のコピー終了列を指定
row_end = row_start + coloum_num - 1

'客数、売上数、売上をコピー
Sht1.Range(Sht1.Cells(row_start,date_start_coloum),Sht1.Cells(row_end, date_end_coloum)).Copy
'客数、売上数、売上を転置して貼り付ける
Sht2.Cells(paste_row, shop_coloum_num + 2).PasteSpecial Transpose:=True
Next i

End Sub


このコードを実行すると実行イメージ動画のように動作します。

まとめ

この記事では、ExcelのVBAを使用することによって、元の表レイアウトを集計や分析に適した形式に変更する方法を紹介しました。具体的には、シートを変数に格納する方法、セル範囲を指定してコピーする方法、コピーした範囲をペーストする方法、コピーした範囲を行と列を入れ替えてペーストする方法について説明しました。また、これらの文法をFor文や変数と組み合わせて自由にコピーしてペーストができることを示しました。Excelでのレイアウト変更を効率化したい方は、ぜひVBAを学んでみてください。

<参考URL>

Excel(エクセル)で行うデータ整形の作業内容と主要機能まとめ

New call-to-action