複数のシートのデータを1枚にまとめるマクロ実況中継。指示出しは自分の非をまず確認すべき

複数のシートのあるデータを1つのシートにまとめたいときは、マクロを使うと便利です。
どのように書くか、その過程を書いてみます。
※今回の記事はある程度マクロがわかっている方向けに書きましたが、指示の出し方という観点で読んでいただいてもおもしろいかと思います。

スクリーンショット 2014 10 10 10 28 49

複数のシートのデータを1つのシートに

複数のシートにあるデータを1つのシートにまとめたい場合、通常はコピー・貼り付けを繰り返す必要があります。
たとえば、こういったファイルが事例に考えてみましょう。
1月から9月、3月賞与のシートがあり、赤枠の部分(給与の仕訳)を1枚のシートに集める事例です。
(最初から1枚のシートで給与データを入力していれば、その必要はありません)
スクリーンショット 2014 10 10 9 19 10

1枚のシートに集めると、
・合計を出したり、分析したりすることが簡単
・加工して会計ソフトに取り込むこともできる
といったメリットがあります。

いざやろうとすると、シート「1月」の該当データをコピーして、
スクリーンショット 2014 10 10 9 22 34

シート「all」に貼り付け(値のみ貼り付け)、
スクリーンショット 2014 10 10 9 23 11

次に、シート「2月」のデータをコピーして
スクリーンショット 2014 10 10 9 25 00

シート「all」に貼り付けます。
(このときには、1月のデータの下に貼り付けるようになります)
スクリーンショット 2014 10 10 9 26 35

今回は、シートの枚数が10枚なので、10回繰り返せばいいのですが、毎回これをやるのもめんどくさく、複数の会社を処理する場合は、大変です。
そこでマクロを使います。


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

■スポンサードリンク


データの形式をそろえることが必須

マクロを書く前に前提として必要なのは、データの形式をそろえることです。

シート「1月」では、セルB48からセルG55までに該当データがあります。
スクリーンショット 2014 10 10 9 22 34

これがシート「2月」で、セルB49からセルG56までにあってはいけないのです。
すべてのシートで、セルB48からセルG55にデータがなければいけません。
気まぐれでデータの位置を変えていると、マクロでは対応できないのです。

【データをそろえる】のは、非常に重要なことで、マクロを書くようになると、より意識するようになります。

データをまとめるマクロを書く過程

では、マクロをゼロから書いてみる過程を解説します。

①マクロの下準備

ExcelでAlt+F11を押し、マクロを書くVBEを開きます。

Alt、I、Mと押し、標準モジュール(マクロを書く場所)を作ります。
ここまでの下準備はこちらの記事を参考にしてください。
Excelでマクロを書く下準備ーExcelを持っていれば無料で使えますー | EX-IT

マクロ名は、任意に決めて大丈夫です。ここではsheetmergeとつけました。

スクリーンショット 2014 10 10 9 34 45

②シート「1月」をコピーして貼り付けてみる

最初に何をやるか考えます。
各月のシートのセルB49からG56までをコピーして、シート「all」に貼り付けるわけです。
マクロ書くときは少しずつテストしながら作る方がうまくいきます。
まずは、シート「1月」をコピーして貼り付けてみましょう。

■スポンサードリンク

マクロはこう書きます。
スクリーンショット 2014 10 10 9 40 21

これを実行させると、エラーになってしまいます。
コピーして集めようとしている部分に数式が入っているからです。
スクリーンショット 2014 10 10 9 41 04

値のみ選択して貼り付けをしなければいけません。
マクロを修正して実行すると、
スクリーンショット 2014 10 10 9 42 45

無事貼り付けることができました。
1つのシートでうまく動くのを確認してから全体を考えていきます。
スクリーンショット 2014 10 10 9 44 01

③繰り返しの指示をする

次は、1月〜9月、3月賞与で同じことをやればいいのです。
先ほどのマクロをこうやってコピーして2月、3月・・・と作っていくことが考えられます。
ただ、これでは美しくありませんし、めんどくさいです。
スクリーンショット 2014 10 10 9 45 32

マクロには、「すべてのシートに対して実行してくれ」という構文があります。
こういった構文です。

    Dim w
    For Each w In Worksheets

    Next

「For Each」と「Next」の間に命令を書きます。
さきほどの、コピー貼り付けの構文を入れてみましょう。
スクリーンショット 2014 10 10 9 50 23

これを動かすと、こうなります。
スクリーンショット 2014 10 10 9 44 01

ん・・?「すべてのシートでやってくれ」っていったのに、何も変わっていません。
Excelが悪いのでしょうか??
Excelが悪いと思う前に、自分の指示が正しかったかをまず考えましょう。
(人間に対してもすべからくそう考えるべきです(^_^;))

マクロをよく読んでみると、
スクリーンショット 2014 10 10 9 44 01

すべてのシートに対してやるようにいっているのですが、シート「1月」のセルB49からセルG55をコピーして貼り付けるという命令になっています。
「1月から9月まで」という命令は入っていません。
この命令を入れるなら、こうなります。

スクリーンショット 2014 10 10 9 54 49

「w」というのは、変数と呼ばれるもので、こう書くと、1回目は1月、2回目は2月・・・というようにシートごとに処理してくれるのです。
たとえば、「毎月この資料が届いたらコピーしておいて」という指示を出すようなものと考えてください。
1月に「1月の資料が届いたらコピーしておいて」
2月に「2月の資料が届いたらコピーしておいて」



と毎回指示しないはずです。

④データの貼り付け位置を指定する

では、このマクロを動かしてみましょう。
こうなります。
スクリーンショット 2014 10 10 9 58 02

あれ、指示通りやってるのかいな・・・と思うかもしれません。
試しに、ちょっとずつ指示を出して様子をみましょう。
F8キーを押すとマクロを1行ずつ実行できます。
黄色いのは、「今からこの行を実行する」という意味です。
1月のデータが貼り付けられます。

スクリーンショット 2014 10 10 10 00 32

次は2月のデータが貼り付けられるのですが、1月のデータに上書きしてしまっているのです。
スクリーンショット 2014 10 10 10 00 42

「こらー!普通、1月のデータの下に貼り付けるだろ!」と怒るかもしれませんが、指示を確認しましょう。

Worksheets("all").Range("a1").PasteSpecial Paste:=xlPasteValues

「シート「all」のセルA1に貼り付けなさい」という指示です。
Excel上では忠実にその指示が守られています。

この場合、「1月のデータを貼り付けたら、そのすぐ下のセルに2月のデータに貼り付けて。2月のデータを貼り付けたら・・」という指示を出さなければいけないのです。

データの数を数えてその一番下を指示するには、次のような構文を使います。

dim Last_data
Last_data = Worksheets("all").Range("a" & Rows.Count).End(xlUp).Row

「Last_data」は、任意に決められます(最終行という日本語でもかまいませんが、日本語だと入力モードの切り替えの必要があるため、私は英語かローマ字にしています)

これを組み入れて

スクリーンショット 2014 10 10 18 11 08

実行すると、なんだかうまくいってそうです。
スクリーンショット 2014 10 10 19 57 52

⑤コピーするシートを指定する

ところが、下の方を見ると、変なデータが混ざっています。
「すべてのシートで」と指示しているので、1月から9月、3月賞与以外のシートの該当部分もコピーしてしまっているのです。
スクリーンショット 2014 10 10 10 11 43

「給与以外のデータはコピーしなくていいよ!常識だろ!」と怒ってはいけません。
そのように指示しているからです。

たとえば、「シート名に「月」が入っている場合だけコピーして」という指示をするなら、こう書きます。

        If InStr(w.Name, "月") > 0 Then
        End If

実行・・の前に、毎回実行していくとデータがたまってしまうので、最初に、

    Worksheets("all").Cells.delete

と、データをきれいにします。
その上で構文を組み込むとこうなり、
スクリーンショット 2014 10 10 18 12 01

実行すると、こうなりました。

スクリーンショット 2014 10 10 19 57 52
ようやく完成です。


Sub sheetmerge()

    Worksheets("all").Cells.Delete
    
    Dim w
    Dim Last_data
    For Each w In Worksheets
        
        If InStr(w.Name, "月") > 0 Then
            w.Range("b48", "g55").Copy
            
            Last_data = Worksheets("all").Range("a" & Rows.Count).End(xlUp).Row
            
            Worksheets("all").Range("a" & Last_data + 1).PasteSpecial Paste:=xlPasteValues
        
        End If
        
    Next
    
    
End Sub

まとめ

マクロは一朝一夕にはできません。
いろいろと試行錯誤しながら作っていくものです。
そして、最も大事なのは、「自分の指示が正しいかどうかを常に考えること」
人への指示のいいトレーニングにもなります(^_^;)
人間は融通をきかせてくれますが、マクロ、Excelはそうではありません。
その代わり、何回でも繰り返して処理してくれますし、文句もいいませんし、サボらないし、正確です。





■スポンサードリンク
【編集後記】
ふるさと納税に関する連載をやることになりました。
(定期購読誌で書店では販売されません)
ひとまず昨日、概略をまとめ、連休明けに提出します。

[10/17までに200kmチャレンジ]
昨日 5.5km 累計 126.5km

【昨日の1日1新】
※詳細は→「1日1新」

新しいランコース
とある方の紹介で人と会う
自宅近くのスタバ2店舗利用
とある地銀のネットバンクをExcel経由で弥生会計に取込




■スポンサードリンク


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

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