イケてないExcelファイルをきれいにするマクロ

昨日、ブログネタ用にネットからExcelファイルを落としたら、かなりイケてない形式でした。
こういった場合にもマクロは便利です。
Macro

使いにくいExcelファイル

今回落としたのは、こういう形式のファイルです。
スクリーンショット 2013 09 28 8 16 06

中小企業白書から落としました。

時系列(1955年〜2009年)のデータなのに、途中で折り返しています。
これだと計算をしたり、グラフにしたりする際に不便です。
まあ、役所関係のファイルはこういうのが多いですね^_^;
(私が元いたところ(統計局)もやはり加工しにくいデータ形式が多かったですし、今もそうです。)

縦に長く、同じ種類のものは同じ列に入っている形式だと使いやすくなります。

スクリーンショット 2013 09 28 8 21 57

マクロで整理

こういった場合にもマクロをささっと書いて整理できます。
今回のマクロはこんな感じです。
繰り返しこのマクロを使うか、一時的に使うかで書き方が多少変わってきますが、今回は一時的に使うことを考えて書きました。
ーーーここからーーー

Sub clean()

Dim i
For i = 8 To 28 Step 5
Dim Max_col
Max_col = Cells(3, Columns.Count).End(xlToLeft).ColumnRange(“b” & i, “k” & i + 3).Copy Range(“a3”).Offset(0, Max_col)

Next


■スポンサードリンク
------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

■スポンサードリンク


Range(“a3”).CurrentRegion.Copy
Sheets.Add
Range(“a1”).PasteSpecial Transpose:=True

Dim Max_row
Max_row = Cells(Rows.Count, 1).End(xlUp).Row

Dim n
For n = 2 To Max_row

If Range(“a” & n).Value >= 55 Then

Range(“a” & n).Value = “19” & Range(“a” & n).Value
Range(“a” & n).NumberFormatLocal = “####”

Else

Range(“a” & n).Value = “20” & Range(“a” & n).Value
Range(“a” & n).NumberFormatLocal = “####”
End If

Next

End Sub
ーーーここまでーーー

① まず、データをコピーすることを考えます。
折り返されている部分を上のデータにくっつけていけばいいわけです。
本来は、移動(切り取り)でやるべきでしょうが、元のデータを崩さないようにコピーを使います。

セルB8からK11の範囲をコピーして、セルL3に貼り付け、同様にすべてのデータを貼り付けていけば、データがきれいになります。
スクリーンショット 2013 09 28 8 33 45

マクロを書くと、
Range(“b8″,”k11”).Copy Range(“l3”)
となります。

■スポンサードリンク

②全体を見てみると、このコピーを5回繰り返せばいいことがわかります。
手でやってもいいのですが、めんどくさいですし、何よりも悔しいです^_^;

繰り返し部分をマクロで書いていきます。
繰り返しを考えるときは、データに何か法則がないかを探してみましょう。
この場合、8行目から、5行ごとにデータがあります。
スクリーンショット 2013 09 28 8 33 40

「8行目から28行目まで、5行おきに繰り返す」という指示をマクロで書けばいいのです。
マクロで書くと

Dim i
For i = 8 To 28 Step 5

Next
となります。
Stepというのが、「5つおきに」という意味です。

③①と②を組み合わせて書き直すとこうなります。
貼り付ける位置は、一番右ですのでMax_colで数えて、自動的に把握できるようにします。
Max_col = Cells(3, Columns.Count).End(xlToLeft).Column
は、一番右の列という意味です。

Dim i
For i = 8 To 28 Step 5

Dim Max_col
Max_col = Cells(3, Columns.Count).End(xlToLeft).ColumnRange(“b” & i, “k” & i + 3).Copy Range(“a3”).Offset(0, Max_col)

Next

④ここまでで全体を見ると、横に長いデータができあがります。
スクリーンショット 2013 09 28 8 30 38

このままでも使えるのですが、縦に長いデータに変換してみましょう。
範囲をコピーして、追加したシートへ行と列を入れ替えて貼り付けます。
Excelでやる機能と同じです。

Range(“a3”).CurrentRegion.Copy
Sheets.Add
Range(“a1”).PasteSpecial Transpose:=True

スクリーンショット 2013 09 28 8 31 14

⑤ここで終わりでもいいのですが、年が下2桁しか表示されていないのがちょっと気持ち悪いです。
これも手書きの名残ですね。。。
マクロで、年の表示を修正します。

2行目からデータがある行(Max_row)まで、処理を繰り返します。
繰り返す処理は、「もし、セルの値が55以上なら、「19」をくっつける。そうでなかったら「20」をくっつける」というものです。
今回のデータは1955〜2009年のデータで、55〜09と表示されています。
ここで法則を見てみると、55〜99までは、19をつけて19○○に、00〜09までは、20をつけて20○○にすればいいわけです。

Dim Max_row
Max_row = Cells(Rows.Count, 1).End(xlUp).Row

Dim n
For n = 2 To Max_row

If Range(“a” & n).Value >= 55 Then

Range(“a” & n).Value = “19” & Range(“a” & n).Value
Range(“a” & n).NumberFormatLocal = “####”

Else

Range(“a” & n).Value = “20” & Range(“a” & n).Value
Range(“a” & n).NumberFormatLocal = “####”
End If

Next

最終的にこのようなデータになり、
スクリーンショット 2013 09 28 8 31 31

グラフを作るのも楽になります。
Macro

マクロを使うようになると、データをきれいにする

一時的ですので、マクロを使わずに、手でやってもいいのですが、同じような事例で毎月、毎日繰り返すこともあるでしょう。
マクロを書けるようになっておくと便利です。

以前、勤めていたときの担当先で、社労士からくる給与データが今回のように折り返されている例があり、同じようにマクロできれいにしていました。

「イケてないデータをきれいにする」のはマクロの本来の使い方ではありません。
最初からデータの形式を整えておけばいいからです。

マクロを使うようになると、自分でExcelのデータを作るときにきれいに整えるようになります。
データがきれいだと無理にマクロを書かなくてもよくなるのです。
効率化の相乗効果ともいえるでしょう。

だからこそ、「Excelを使えるようになってからマクロを覚える」のではなく、「マクロを覚えてみて、そのフィードバックをExcelに活かす」ことをおすすめしています。

次回のマクロセミナーは10/24です。もしよかったらご利用ください。

詳細・お申し込み





■スポンサードリンク
【編集後記】
東京マラソンの当選発表が先日ありました。
結果は落選。
2012年に一度当たったのでしばらく当たらないかもですね。
友人数人は当たっているので応援に行く予定です。




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』
###