Excelマクロで給与一覧表から給与明細へ転記

2019年1月10日

URLをコピーしました！

給与計算をExcelでやる場合、明細を転記する部分でExcelマクロを使えます。

1 Excelで給与明細をつくる流れ
2 Excelで1人分の給与明細をつくるマクロ
3 Excelで複数人の給与明細をつくるマクロ

Excelで給与明細をつくる流れ

Excelで給与計算するのは、それなりに難易度が難しいものです。
5段階でExcel難易度を判定するなら（使うもの、私が使っているもののみ）、難易度5は、総勘定元帳、給与計算は難易度4と考えています。
Excelでおすすめしているのは、難易度3までです。
（本に書いているのもその基準です）

給与計算ソフトを入れたほうが早い場合もあります。

それでも、Excelでやりたいという場合（給与計算ソフトは意外と高いですし）、Excelマクロを使う方法があります。

Excelマクロでつくるなら、給与一覧表をつくってそれを明細に転記する方法がおすすめです。
それぞれの給与明細に入力していく方法もありますが、ミスの可能性もあり、手間もかかります。

本来は給与データ形式にして、それを集計、明細にするほうがいいのですが、現場のつかいやすさも考えて、今はこの形式でやっていることが多いです。
お客様、自分で使っています。

給与一覧表

一覧表は、計算式を入れて計算できるようにしましょう。
ex-it_17

今回のサンプルファイルでは合計や差引しか入れていません。
実際は、VLOOKP関数で所得税を計算したり、雇用保険を計算したりしています。
健康保険、年金等は、変更回数も少なく、計算でやるとそれなりに大変なので（標準報酬をデータとして準備する必要）、入力のほうが楽でしょう。
（私は計算しています）
時間外手当は、時間数を入れると計算してできるようにしておくと楽です。
時給の方も同様です。

給与明細のひな形

一覧表のExcelファイルにテンプレート（ひな形）として、給与明細を準備しましょう。
どんな形式でもかまいません。
このテンプレートを変更すると、マクロも変更しなければいけなくなるので、一度決めたら変更しなくていいようにしておきましょう。

この場合、一覧表の金額をそのまま転記できるようにしておくと、マクロを書くのも楽ですし、メンテナンスも楽です。

こういった明細テンプレートだと、そのまま転記できず、マクロの手間も増えます。
使う方の意見や使い勝手とプログラミングのしやすさをどのくらいすり合わせるかは大事です。
これをやらないと、どちらかが不幸になります。

Excelで1人分の給与明細をつくるマクロ

つくるのは、給与一覧表から明細へ転記するマクロです。

まずは1人分でつくっていきます。

給与一覧表をセット

給与一覧表から明細へ転記する際、その給与一覧表は月ごとです。
シート名としては、1月、2月、3月…となります。
（年ごとにシートを分ける場合）

シート名はその都度（毎月）変わるので、「そのシート」と指定し、セットしておくと毎月が便利です。
「そのシート」というのは、ActiveSheet。
マクロを実行するシート＝そのシート＝その月のシートとなります。

※緑色の文字は、コメントで自由に書け、プログラムの実行に影響ありません。

W_sという箱をつくり、そこに「そのシート」を入れていくという手順です。
この箱には、1月、2月、3月・・・とマクロを実行するときに選択していたシートが入ります。
つまり、シートを選択してマクロを実行すれば、

これ以降は、W_sが「そのシート」になります。

（W_sは任意です）

テンプレートのコピー

次に、テンプレートのシート「meisai」をコピーします。

こう書くと新しいブックにコピーしてくれます。
給与明細書は、このあと、プリントアウトなりPDFにするなりして（どちらもマクロに組み込むことはできます）、Excelファイルとしては保存しないのがおすすめです。
無駄にファイルが増えるので。
給与一覧表が残っていれば、十分です。

転記

データを転記していきます。

〇＝△で、「〇に△を転記する」という意味ですので、それぞれのシートの対応を見ながら、書いていきましょう。
1つ書いてコピーすると楽です。

Activesheet.Range(“a3”).Value = W_s.Range(“a1”).Value ‘

と書きます。

そのシートのセルA3にW_s(この場合は、シート「1月」のセルA1を転記する
という意味です。

支給項目の転記は、

と書き、複数の連続した範囲を転記しています。

控除項目、

差引支給、

勤怠を同じように書いていきましょう。

転記部分はこうなります。

最後に、シート名を氏名にしておきましょう。

ここまでできたら、実行してみて、正しく転記されているかを確認し、できていれば、他のデータの部分をつくっていきます。

Excelで複数人の給与明細をつくるマクロ

繰り返しは、
Dim Col
for Col = 〇 To △
□

と書きます。
Colが〇から△まで、□を繰り返すというもの。
Colは列の意味の箱で、任意です。

サンプルでは、5人、列で言えば、3列目から7列目までデータがあります。
〇から△までの
〇は3
△は7
です。

こう表現します。

□の部分は、これまで書いたものを入れていきます。
ただし、一覧表をセットする部分は、繰り返さないので、こう書きましょう。

□の部分は切り取って（Ctrl＋X）、インデント（選択してTab）をつけておくと見やすくなります。

Colが3、4、5、6、7と変化していく状態をプログラムに判定していきます。

の部分のa1はそのままです。

のセルC2は、D2、E2、F2・・・と変わっていくはずです。
実は、横方向にデータが増えていくのは好ましくなりませんが、使い勝手を考えてこの事例では、この形式にしています。

横、列方向にデータが増えていくなら、RangeではなくCellsを使う方法もありますが、普段のわかりやすさからRangeをおすすめしているところです（列に増えるならCellsという切り替えもできますが）。
Rangeの場合ならOffsetを組み合わせます。

Offsetは、Range（”c2”).Offset(0,1).Valueで、行に0、列に1増やした場所を指定できるものです。

Range（”c2”).Offset(0,0).Valueだと、セルC2
Range（”c2”).Offset(0,1).Valueだと、セルD2
Range（”c2”).Offset(0,2).Valueだと、セルE2
となります。

このように書き換えましょう。
コピペでやると楽です。

ただ、この場合、繰り返す範囲は、3から7だと都合が悪くなります。
0から4に書き換えましょう。

これで実行してみると、うまくいきません。

毎回、新しいブックへコピーしているからです。

最初のデータ（Colが0）のときは、新しいブックへコピー、
そうでないときは、そのブック内でシートをコピー

というように条件で処理を分けます。；

これですべてのデータで明細ができるはずです。

ただ、社員が増えたり減ったりしたらどうでしょう。
毎回、プログラムを変えなければいけません。
そうならないように、このように書き換えます。

データがある列をカウントするプログラムです。
Excelの一番右の列は、XFD列ですのでセルXFD2からCtrl＋左を押したときに選択される列、つまりデータが入っている最後の列を指定できます。
（厳密には、バージョンによってXFDが最大列ではなく、今後増える可能性もあるので別の書き方をします）

ただ、サンプルのままだと、この最大列は、7と出てしまいます。
繰り返したいのは、0から4までなので、7から3を引く必要があるので、このようにしているのです。

たとえば、1人増やすと、最大列は、8、8-3=5 なので、0から5まで繰り返します。

このようにデータの数だけ明細ができればOKです。

このほか、
・退職した場合、その人の明細をつくらない（データ削除すると年末調整時に不都合がある）
・月のシートの繰越
・前月と比較し、違うところを表示
・1人ずつPDFにする
・賞与の場合は、明細のタイトルを変える
といったマクロも組み込んで使っています。

サンプルはこちらです。
EX-ITサンプル　給与明細転記.xlsm

シートのボタンをクリックするとマクロが実行されます。

記事を読みながら書いてみることをおすすめします。

Sub kyuyo()


    '■その月の給与一覧表をセット
    Dim W_s As Worksheet
    Set W_s = ActiveSheet
    
    
    
    Dim Col
    For Col = 0 To Range("xfd2").End(xlToLeft).Column - 3
    
        If Col = 0 Then
        
            '■明細書テンプレートを新しいブックにコピー
            Worksheets("meisai").Copy
        Else
            '■明細書テンプレートをそのブック内でコピー
            ActiveSheet.Copy after:=Worksheets(Worksheets.Count)
        End If
        
    
    
        '■データ転記
        ActiveSheet.Range("a3").Value = W_s.Range("a1").Value '月
        ActiveSheet.Range("b4").Value = W_s.Range("c2").Offset(0, Col).Value  '社員番号
        ActiveSheet.Range("d4").Value = W_s.Range("c4").Offset(0, Col).Value  '部門
        ActiveSheet.Range("f4").Value = W_s.Range("c5").Offset(0, Col).Value  '氏名
        
        ActiveSheet.Range("b17", "b30").Value = W_s.Range("c6", "c19").Offset(0, Col).Value '支給
        ActiveSheet.Range("d17", "d30").Value = W_s.Range("c20", "c33").Offset(0, Col).Value '控除
        ActiveSheet.Range("f29").Value = W_s.Range("c34").Offset(0, Col).Value  '差引支給
        
        ActiveSheet.Range("b7", "b14").Value = W_s.Range("c35", "c42").Offset(0, Col).Value '勤怠
        
        '■シート名
        ActiveSheet.Name = W_s.Range("c5").Offset(0, Col).Value
    
    
    Next
    
    

End Sub

■井ノ上陽一のプロフィール
個別コンサルティング
プロフィール写真撮影
オンラインショップ
11/26 東京毎日更新！ブログ入門セミナー
11/28 東京タスク管理入門セミナー

『税理士のためのプログラミング -ChatGPTで知識ゼロから始める本-』

『すべてをがんばりすぎなくてもいい！顧問先の満足度を高める税理士業務の見極め方』
税務顧問・税務相談
■相続の相談
■出版・執筆の依頼
■週刊メルマガ『雇われない雇わない生き方への道』
■講演の依頼
■メールコンサルティング・顧問

■編集後記

昨日は、終日執筆。
日中にふとみかけたコートがよさげで買おうかと考えて、夜にも家族で行ってみましたが、やっぱりNG。
なかなか気に入ったのがなくて探しているところです。

■昨日の「1日1新」

新セミナー企画・告知
ゼンハイザーヘッドセット

■昨日の娘日記
昨日のご飯は、アクアパッツァ。
あさり大好きな娘が喜ぶかと思い。
あさりは大喜びで全部ひとりで食べるくらいでした。
スープをかけたご飯もお気に入りで。
鯛はそっちのけでしたが。