給与データから明細へのコピペを自動化するマクロ

給与データ、売上データなどを、それぞれの明細にするケースで効率化するには、マクロが必要です。
そのマクロをご紹介します。

スクリーンショット 2014 11 20 11 27 17

「コピペすればすむ」には注意

仕事の中で、「コピペすればすむ」というものがあるはずです。
コピペ、つまりコピーして貼り付け(ペースト)は一見便利ですが、人が手作業でやらないければいけません。

コピペを繰り返すたびに時間を使いますし、量が増えるにつれて、時間や手間がかかり、ミスの可能性も高くなります。

「コピペすればすむ」には注意しなければいけないのです。

マクロを使うべきところ

「コピペすればすむ」仕事は、マクロが得意とするものの1つです。

繰り返しやる作業にマクロは最適です。

「この作業を、○回繰り返して。よろしく!」とマクロを動かせば自動的にやってくれます。

・繰り返しやること
・1枚のシートから複数のシートへデータをコピペする(又は入れていく)
・複数のシートのデータを1枚のシートへまとめる
といったことがマクロの得意とするところで、使いどころです。


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

■スポンサードリンク


これ以外は、マクロを使わずともできる(使わない方が簡単)なものといえます。
(マクロを使わざるを得ないケースには、シート、ファイルなどのデータの作る方はまずい場合がほとんどです)

データから明細を作るマクロ

データから明細を作る、つまり、1枚のシートにあるデータから複数のシートの明細を作る場合もマクロが有効です。

こういった勤怠データ・給与データから、
スクリーンショット 2014 11 20 11 35 16

人ごとに明細を作っていきます。
(サンプルデータの氏名は架空のものです。好き嫌いは関係ありません)
スクリーンショット 2014 11 20 11 33 50

コピペすればすむ話ですが、人の数だけコピーしなければいけません。
そこでマクロを使います。

①データの整理

・・・とその前に、本来はこういったデータがあればいいのですが、そうそううまくいかないことが多いです。
スクリーンショット 2014 11 20 11 35 16

こういったデータが手元に届き、それを加工することが多いでしょう。
システムから出てくる形式がこうだったり、お客様からいただくデータがこうだったり、営業から出てくるデータこうだったりします。
1つの解決策は、加工しやすいデータ形式で入れてもらうことですが、そうもいかないことがあります。
スクリーンショット 2014 11 20 11 35 00

そこで、こちら側で自動的にデータを整理します。
元のデータ「data1」から数式で連動して、「data2」を作りましょう。
その際に計算する部分は数式を入れておきます。

「data2」の2行目に入っているのは、次のような数式です。
時間の計算、単価をかける計算をやっています。
スクリーンショット 2014 11 20 11 41 50

この数式を「data1」のデータ数だけコピーすれば、シート「data2」は完成です。
ここまでのマクロは次のように書きます。

■スポンサードリンク

Last_rowでデータ数を数えて、その分だけ「data2」の2行目をコピーしています。
間違いがないように、
Worksheets(“data2”).Rows(“3:10000”).Delete
でデータを消しておきます。

10000というのは意味がなく、これくらい消しておけば全部消えるという考えです。
もちろん、ここで「data2」のデータ数を数えても問題ありません。

'■データソート
    Dim Last_row
    Last_row = Worksheets("data1").Range("a" & Rows.Count).End(xlUp).Row
    Worksheets("data1").Range("a1", "az" & Last_row).Sort _
    key1:=Worksheets("data1").Range("b1"), order1:=xlAscending, Header:=xlYes


'■データコピー
    Worksheets("data2").Rows("3:10000").Delete
    Worksheets("data2").Range("a2", "az2").Copy Worksheets("data2").Range("a3", "az" & Last_row)

なお、この「data2」のフォーマットは、明細のフォーマットに合わせます。

[日付]から[備考]までの並びは同じです。
スクリーンショット 2014 11 20 11 33 50
[氏名]は各行では使わずに、明細で一度だけ使うので、一番右に残してあります。
スクリーンショット 2014 11 20 11 35 16

②マスターをコピー

ここまで整理して明細を作ります。
まずは明細のマスター(ひな形)をコピーします。

コピーして新しいブック(ファイル)を作り、そこにデータを貼り付けていくのです。
合計、税金や社会保険料などは数式を入れて、このシートで計算できるようにしておきます。
スクリーンショット 2014 11 20 11 55 01

こうしておけば、新しいブックを「2014年11月分」として保存しておけます。
(保存しなくても、データがあるのでいつでも出せるといえば出せます)

毎回、新しいブックにコピーするわけではなく、シート「data2」の2行目(1行目はタイトル)のときのみ新しいブックにコピーし、それ以降は、シートを一番右に追加していきます。

           If i = 2 Then
                To_W_s.Copy
            Else
                To_W_s.Copy after:=Worksheets(Worksheets.Count)
            End If

③各人の1行目のデータ処理
ただし、各人の1行目のときのみ、シートをコピーします。
そうしないと、同じ人のシートが何枚もできてしまうからです。

各人で1行目のときは、
・シートをコピー
・データをコピペ
・シート名を変更
・名前を挿入
といった処理をします。

マクロはこう書きます。

    Dim From_W_s As Worksheet
    Set From_W_s = Worksheets("data2")
    
    Dim To_W_s As Worksheet
    Set To_W_s = Worksheets("master")
    
    Dim W_b As Workbook
    Set W_b = ThisWorkbook
    
    
    Dim i
    For i = 2 To Last_row
'○1行目の処理
        Dim Name
        If Name <> From_W_s.Range("l" & i).Value Then
            
'シートのコピー
            If i = 2 Then
                To_W_s.Copy
            Else
                To_W_s.Copy after:=Worksheets(Worksheets.Count)
            End If
'カウントを7に
            Dim n
            n = 7
            
            Name = From_W_s.Range("l" & i).Value
            
            Dim W_s As Worksheet
            Set W_s = ActiveSheet
'データコピー
            From_W_s.Range("a" & i, "k" & i).Copy
            W_s.Range("a6").PasteSpecial Paste:=xlPasteValues
'シート名
            W_s.Name = From_W_s.Range("l" & i).Value
'名前の挿入
            W_s.Range("a3").Value = From_W_s.Range("l" & i).Value
        
        Else

「カウントを7に」というのは、各人で1行目の時は、masterの6行目に貼り付け、それ以降は7行目以降に貼り付けるので、新しい人の処理をするときにいったん7にするためです。
スクリーンショット 2014 11 20 11 55 01

        Dim Name
        If Name <> From_W_s.Range("l" & i).Value Then

あたりの処理が複雑なので解説します。

変数Nameは、最初は0です。
0と、2行目にある1人目の「赤羽恵子」を比較すると一致しないので、「赤羽恵子」さんの1つめのデータということがわかります。

スクリーンショット 2014 11 20 12 03 28

3行目も「赤羽恵子」さんなので、次の処理をやります。

④各人の2行目以降のデータ処理

2行目以降はシンプルにデータのコピペをやります。
シート名や氏名はもう入っていますし、シートのコピーの必要もありません。

ただし、先ほど7に設定したカウントを7から1増やす必要があります。
7行目の次は8行目に貼り付けるからです。
それが、
n=n+1
の部分となります。

        
'○2行目以降の処理
            From_W_s.Range("a" & i, "k" & i).Copy
            W_s.Range("a" & n).PasteSpecial Paste:=xlPasteValues
            n = n + 1

処理を続けていき、「尾崎隆一」さんが出てきたら、「赤羽恵子」さんとは一致しないので、③の処理に移ります。

これらを繰り返しやってくれますので、どんなにデータが増えても手間は一緒です。

マクロ全体はこうなります。

Sub meisai()

'■データソート
    Dim Last_row
    Last_row = Worksheets("data1").Range("a" & Rows.Count).End(xlUp).Row
    
    Worksheets("data1").Range("a1", "az" & Last_row).Sort _
    key1:=Worksheets("data1").Range("b1"), order1:=xlAscending, Header:=xlYes


'■データコピー

    
    
    Worksheets("data2").Rows("3:10000").Delete
    Worksheets("data2").Range("a2", "az2").Copy Worksheets("data2").Range("a3", "az" & Last_row)
  
'■明細作成
    
    Dim From_W_s As Worksheet
    Set From_W_s = Worksheets("data2")
    
    Dim To_W_s As Worksheet
    Set To_W_s = Worksheets("master")
    
    Dim W_b As Workbook
    Set W_b = ThisWorkbook
    
    
    Dim i
    For i = 2 To Last_row
'○1行目の処理
        Dim Name
        If Name <> From_W_s.Range("l" & i).Value Then
            
'シートのコピー
            If i = 2 Then
                To_W_s.Copy
            Else
                To_W_s.Copy after:=Worksheets(Worksheets.Count)
            End If
'カウントを7に
            Dim n
            n = 7
            
            Name = From_W_s.Range("l" & i).Value
            
            Dim W_s As Worksheet
            Set W_s = ActiveSheet
'データコピー
            From_W_s.Range("a" & i, "k" & i).Copy
            W_s.Range("a6").PasteSpecial Paste:=xlPasteValues
'シート名
            W_s.Name = From_W_s.Range("l" & i).Value
'名前の挿入
            W_s.Range("a3").Value = From_W_s.Range("l" & i).Value
        
        Else
        
'○2行目以降の処理
            From_W_s.Range("a" & i, "k" & i).Copy
            W_s.Range("a" & n).PasteSpecial Paste:=xlPasteValues
            n = n + 1
        End If
            
        
    Next
End Sub

まとめ

繰り返しやるデータ処理上大事なのは次の3点です。

1 データを整理する、イレギュラーを入れない
2 人の手を極力入れない、入力しない仕組みを作る
3 できあがったデータを編集しない(これをやるとデータと明細が合わなくなります)

サンプルをおいておきますので、研究してみてください。
明細の形によっては、工夫が必要ですが、理屈は同じです。
EX-ITサンプル データから明細作成マクロ.xlsm





■スポンサードリンク
【編集後記】

仕事のやり方を考えさせられる出来事が、最近多く、いろいろ路線変更案を練っています。
反省を踏まえて、やらないことリストも見直します。

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

モスカフェ ゆずドリンク
Macアプリ HAGOROMO




■スポンサードリンク


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

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