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

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

 

 

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

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

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

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

 

 

マクロを使うべきところ

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

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

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

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

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

 

 

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

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

こういった勤怠データ・給与データから、

人ごとに明細を作っていきます。
（サンプルデータの氏名は架空のものです。好き嫌いは関係ありません）

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

①データの整理

・・・とその前に、本来はこういったデータがあればいいのですが、そうそううまくいかないことが多いです。

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

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

「data2」の2行目に入っているのは、次のような数式です。
時間の計算、単価をかける計算をやっています。

この数式を「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月分」として保存しておけます。
（保存しなくても、データがあるのでいつでも出せるといえば出せます）

毎回、新しいブックにコピーするわけではなく、シート「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にするためです。

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

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

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

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