Excel給与データ→JDL年末調整取込データ変換マクロ

2017年12月5日

URLをコピーしました！

JDL年末調整を使う場合、Excelデータから取り込むと効率的です。
ただ、JDLの取込形式が特殊なので、マクロを使っています。

EX IT 1
※スクリーンショット＋パワポで作成

1 Excelデータをいかに作るかが勝負
2 JDL年末調整のデータ形式
3 Excel給料データ→JDL年末調整取り込みデータ変換マクロ

Excelデータをいかに作るかが勝負

年末調整ソフトと給与計算ソフトが同じメーカーのものなら、データを連動させることができます。
ただ、同じメーカーのものを使っているとは限らず、Excelで給与計算をしていることも多いでしょう。

そんな場合は、Excel給与データを年末調整ソフトへ取り込めば効率的です。
同じメーカーのものを使ったほうが効率的なのですが、給与計算ソフトは高いものもあり、かといって使いやすいわけでもありません。
Excelでやったほうが楽な部分も多いです。

そのExcel給与データをいかにつくるかが勝負になります。
私の場合は、Excelで給与計算のしくみをつくりお客様に提供し、自分でも使っていますが、通常は、
・給与計算ソフトからExcelにする
・Excelに入力する
といった方法になるはずです。

一般的なこういったデータを前提にすすめていきます。
（氏名は、ランダムにつくりました）
WS000001

年末調整の最低限必要な給与データは、氏名、支給年月日、金額、社会保険の金額、所得税の金額（差し引いた金額）です。

JDL年末調整のデータ形式

データを取り込む場合は、取り込み先のソフトの形式をまず確認します。
ExcelのA列に何を入れて、B列に何を入れて・・・という形式です。

JDL（私の場合は組曲Major）の取り込み形式は、こうなっています。
（実際には生年月日、住所等のデータが入っています）
WS000002

給与データはどこに入っているかというと、このように横に並べるイケてない形式です。
（住宅ローン控除が手計算なのも嫌いです）
もちろん、ここに入力すれば取り込めるのですが、手間もかかります。
WS000003

一般的な形式のデータを取り込めるように変換しましょう。

Excel給料データ→JDL年末調整取り込みデータ変換マクロ

次の手順で取り込みます。

1　社員データの入力

まずは、JDLで社員データ（社員番号、氏名等）を入力します。
取り込み時に追加することもできますが、先に入力しておいたほうが楽です。
この段階では番号と氏名があれば問題ありません。

前年の繰越データがJDLにあれば、新入社員を追加すればすみます。

2　Excel給与データの準備

Excel給与データを準備します。
ここで大事なのは、
・Excel給与データとJDL年末調整ソフトと同じ社員番号であること
・Excel給与データが、社員番号、支給年月日、給料・賞与の順に並んでいること
・Excel給与データは、A列氏名、B列支給年月日、C列給与の金額、D列社会保険、E列所得税の並びであること（F列以降は自由です）
・年の途中で退職した社員のデータもあること
です。

並び順は、
Aさん　1月給料
Bさん　1月給料
Cさん　1月給料
・
・
・
Aさん　2月給料
Bさん　2月給料
Cさん　2月給料

そして、賞与がある場合は、
Aさん　12月給料
Bさん　12月給料
Cさん　12月給料
のあとに
Aさん　7月賞与
Bさん　7月賞与
Cさん　7月賞与
・
・
・
と並びます。

これは、JDLのデータがそういう並びだからです。
さらには、支給年月日、社会保険、所得税の順なので、これと合わせてExcelデータを準備します。
WS000003

退職者がいた場合にデータを削除すると行がずれますので、退職者分もデータを削らないようにしましょう。
たとえば、

Aさん　1月給料
Bさん　1月給料
Cさん　1月給料
・
・
・
Aさん　2月給料
Cさん　2月給料
というようにしないという意味です。

新入社員の場合は、1の社員データ入力をやっておけばマクロで自動的に取り込みます。

Excelがどんな並びでも、マッチングさせて年末調整データに貼り付ける方法もありますが、フォーマットに合わせてExcelデータを準備したほうがシンプルでわかりやすいです。
（非課税分、扶養控除人数は省略しています）

JDL取り込みデータの準備

JDL取り込みデータは、JDL年末調整の［8.ファイル管理］→［21.汎用データ出力］で準備できます。
EX IT 2

出力するのは［総合データ］です。
EX IT No 01

データには次のような特徴があります。
・手動でcsvに保存するなら、「CSV（カンマ区切り）」で保存する必要があります。

・データの最後の＜総合データ終了＞が必須です
EX IT 7

・取り込みは社員番号（社員C）で判断しています。氏名が一致するかは見ていません。
JDL年末調整ソフトにない社員番号を取り込むと追加します。

・データは上書きしますので、たとえば、JDLで生年月日、扶養親族の情報を入れていても、給料のみのデータ（生年月日、扶養親族等なし）を取り込むと上書きして消えてしまいます。
JDLで入力→データ出力→Excel給料データを貼り付ける→取り込むという流れでやりましょう。
もちろん、Excelで生年月日、住所等を編集できます。

マクロのしくみ

Excelデータで、1月分を選択してコピーし、JDL取り込みデータに貼り付ける。
これを12回＋α（賞与分）繰り返せば、それでも済みますが、手間もかかりますし、毎年のことなので効率化しましょう。
（税理士で複数社だとそれだけ手間もかかります）
WS000004

プログラムはこんな感じです。

Sub 年末調整()

    
    '■選択範囲　First_Dataの初期値は2
    Dim First_Data
    Dim Last_Data
    
    First_Data = 2
    
    '■給与データ
    Dim w_Data
    Set w_Data = Worksheets("data")
    
    '■年末調整データ
    Dim w
    Set w = Worksheets("年末調整")
    
    '■年末調整データ貼り付け先　初期値は0
    Dim n
    n = 0
    
    '■いったんデータをクリア
    w.Range("fd6", "iu1000").Clear
    
    
    Dim i
    '2行目からデータ数だけ繰り返す
    For i = 2 To w_Data.Range("a" & w_Data.Rows.Count).End(xlUp).Row
        
        '支給年月日が同じものを選択して、コピー貼り付け
        If w_Data.Range("b" & i).Value < > w_Data.Range("b" & i + 1) Then
            Last_Data = i
            w_Data.Range("b" & First_Data, "e" & Last_Data).Copy w.Range("fd6").Offset(0, n)
            First_Data = Last_Data + 1
            n = n + 6
        End If

    Next
    
    
    '■年末調整データを新しいブックへコピー
    w.Copy
    
    
    '■import.csvという名称で、ファイルを保存
    Application.DisplayAlerts = False
    
      ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "¥import.csv", FileFormat:=xlCSV, local:=True
    
      ActiveWorkbook.Close
    
    Application.DisplayAlerts = True
    

End Sub

First_DataからLast_Dataまでを選択して貼り付けるしくみです。
First_Dataの初期値は2。
どこまで選択するかは、

If w_Data.Range("b" & i).Value < > w_Data.Range("b" & i + 1) Then

で、判定しています。
1つ下のセルと支払年月日が違えば・・という判定です。
この場合だと、セルB28（2017/1/25）とセルB29（2017/2/25）は違うので、「28」が選択する範囲の行ということになります。
EX IT 4

セルB2（First_Data）から、セルF28（Last_Data=28）をコピーして、

            w_Data.Range("b" & First_Data, "f" & Last_Data).Copy w.Range("fd6").Offset(0, n)

年末調整データのセルFD6に貼り付けます。
最初の1月分はFD6に貼り付けることになるからです。
このFD6は、そのあと、横にずらしていくので、Offsetを使っています。
Offset（0、6）なら、FD6から行は0、列を6ずらした位置に貼り付けられるというしくみです。

            w_Data.Range("b" & First_Data, "f" & Last_Data).Copy w.Range("fd6").Offset(0, n)

offset(0.n）と書いてあり、初期値はn＝0と指定しているので、

最初に貼り付けるのは、Range(“fd6”).Offset(0, 0)、つまりセルFD6になります。
EX IT 6

1月を貼り付けたら、次は2月。
今度のFirst_Dataは、前回の28に1を足した29です。
これを
First_Data = Last_Data + 1
で表現しています。
EX IT 5

貼り付け先は6増えるので、
n = n + 6
と表現し、FJ6に貼り付けるというわけです。
EX IT 6

データはこのマクロファイルの保存場所と同じところに「import.csv」という名前でできます。
これをJDLで取り込みましょう。

EX IT 3

サンプルはこちらですが、取り込み後に必ず確認をお願いします。
これでやって年末調整間違えたーとならないように。。

EX-ITサンプル　Excel→JDL年末調整.xlsm

流れとしては、次のとおりです。

・JDLで社員データ整備
・JDLで住所、扶養、控除データの入力、チェック
・JDLからExcelにデータを出力
・11月の給料までのExcelデータを入れて、マクロでデータをつくり、取り込んでチェック
・12月の給料が決まったらExcelデータを準備（私はここにもマクロを使っています）
・JDLからExcelにデータを出力
・12月の給料（または賞与）までのExcelデータを入れて、マクロでデータをつくり、取り込んでチェック
・年末調整額を給与計算へ反映（JDLから［汎用データ出力］→［年税額・精算データ］で出力できます）