昨日、ブログネタ用にネットからExcelファイルを落としたら、かなりイケてない形式でした。いった場合にもマクロは便利です。マクロを使うようになると、データをきれいにする使いにくいExcelファイル今回落としたのは、こういう形式のファイルです。中小企業白書から落としました。時系列(1955年〜2009年)のデータなのに、途中で折り返しています。これだと計算をしたり、グラフにしたりする際に不便です。役所関係のファイルはこういうのが多いですね^_^;(私が元いたところ(統計局)もやはり加工しにくいデータ形式が多かったですし、今もそうです。)縦に長く、同じ種類のものは同じ列に入っている形式だと使いやすくなります。マクロで整理こういった場合にもマクロをささっと書いて整理できます。今回のマクロはこんな感じです。繰り返しこのマクロを使うか、一時的に使うかで書き方が多少変わってきますが、今回は一時的に使うことを考えて書きました。データをコピーすることを考えます。折り返されている部分を上のデータにくっつけていけばいいわけです。本来は、移動(切り取り)でやるべきでしょうが、元のデータを崩さないようにコピーを使います。セルB8からK11の範囲をコピーして、セルL3に貼り付け、同様にすべてのデータを貼り付けていけば、データがきれいになります。Range(“l3”)となります。2全体を見てみると、このコピーを5回繰り返せばいいことがわかります。手でやってもいいのですが、めんどくさいですし、何よりも悔しいです^_^;繰り返し部分をマクロで書いていきます。繰り返しを考えるときは、データに何か法則がないかを探してみましょう。この場合、8行目から、5行ごとにデータがあります。「8行目から28行目まで、5行おきに繰り返す」という指示をマクロで書けばいいのです。5Nextとなります。Stepというのが、「5つおきに」という意味です。31と2を組み合わせて書き直すとこうなります。貼り付ける位置は、一番右ですのでMax_colで数えて、自動的に把握できるようにします。Columns.Count).End(xlToLeft).Columnは、一番右の列という意味です。Max_col)Next4ここまでで全体を見ると、横に長いデータができあがります。このままでも使えるのですが、縦に長いデータに変換してみましょう。範囲をコピーして、追加したシートへ行と列を入れ替えて貼り付けます。Excelでやる機能と同じです。Transpose:=True5ここで終わりでもいいのですが、年が下2桁しか表示されていないのがちょっと気持ち悪いです。これも手書きの名残ですね。マクロで、年の表示を修正します。2行目からデータがある行(Max_row)まで、処理を繰り返します。繰り返す処理は、「もし、セルの値が55以上なら、「19」をくっつける。なかったら「20」をくっつける」というものです。今回のデータは1955〜2009年のデータで、55〜09と表示されています。ここで法則を見てみると、55〜99までは、19をつけて19○○に、00〜09までは、20をつけて20○○にすればいいわけです。IfNext最終的にこのようなデータになり、グラフを作るのも楽になります。マクロを使うようになると、データをきれいにする一時的ですので、マクロを使わずに、手でやってもいいのですが、同じような事例で毎月、毎日繰り返すこともあるでしょう。マクロを書けるようになっておくと便利です。以前、勤めていたときの担当先で、社労士からくる給与データが今回のように折り返されている例があり、同じようにマクロできれいにしていました。「イケてないデータをきれいにする」のはマクロの本来の使い方ではありません。最初からデータの形式を整えておけばいいからです。マクロを使うようになると、自分でExcelのデータを作るときにきれいに整えるようになります。データがきれいだと無理にマクロを書かなくてもよくなるのです。効率化の相乗効果ともいえるでしょう。「Excelを使えるようになってからマクロを覚える」のではなく、「マクロを覚えてみて、そのフィードバックをExcelに活かす」ことをおすすめしています。次回のマクロセミナーは10/24です。よかったらご利用ください。マラソンの当選発表が先日ありました。2012年に一度当たったのでしばらく当たらないかもですね。友人数人は当たっているので応援に行く予定です。時間とお金のバランスをとり、独立後を楽しんでいただくべく、IT・発信による効率化、時間・お金のノウハウを提供している。『税理士のためのRPA入門』『やってはいけないExcel』など39冊。日課は1日1新、ブログ。