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

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

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

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から[汎用データ出力]→[年税額・精算データ]で出力できます)


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

昨日は、個別コンサルティング(提供する側)→ランレッスン(受ける側)→セミナー(受ける側)という1日。
間のランレッスンが日比谷公園だったので、個別コンサルティング後はランの格好で移動しました。

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

とあるセミナー参加
ラン マンツーマンレッスン 新しい方

【昨日の娘日記】

最近はお風呂上がるときに泣き叫びます。
もっと遊んでいたいのか。
シャンプーのときも泣き叫びますが。




■スポンサードリンク


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

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