Excel VBAでデータ整形 (表レイアウト変更) を自動化しました。 表レイアウト変更に役立つコードもまとめ、解説してあります。
目次
はじめに
集めた元データの表レイアウトが使いにくい場合、集計や分析がしやすい表レイアウトへ変更する必要がありますよね。 今回はエクセル用のプログラミングコードVBAを用いることによってデータ整形(主に表レイアウト変更)を行いました。 なぜVBAを使うのかという疑問に思う方もいると思いますが、実際のデータは10万行越えになるため手動ではデータ整形が不可能でした。ゆえにVBAを用いることにしました。 ここではサンプルデータを用いて説明させていただきます。 ＜この記事がおすすめの方＞ ・Excelでデータ整形を自動で効率的に行いたい方 ・Excelでデータ整形に役立つVBAコードを知りたい方 ・Excelで列や行を入れ替える必要があるデータ整形を行いたい方 ・集めた元データを使いやすい表にまとめたい方そもそも集計や分析がしやすいレイアウトとは
そもそも集計や分析がしやすい表レイアウトとは、以下の写真のように ①見出しが１行 ②１列１行データ ③１列同種類データ の上記３つが守られているデータのことを指します。 データ分析においてはデータを使いやすい表形式に直すことは重要です。 つまりこの記事では、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_データ整形後
データ整形後は以下の写真のようになります。 データ整形を行うことによって集計や分析がしやすい表になりました。 集計や分析がしやすい表の３原則が守られていますね。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'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:=TrueVBAでデータ整形_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)).PasteSpecialVBAでデータ整形_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
