タグ: 労務

組合健保など、エクセルに値を転記しなければならないときに使えるVBAコードです。

組合健保は算定基礎届で独自のエクセル様式用いているとこが多いです。
給与計算システムから算定のCSVを吐き出し、それを整えたあとに使えるVBAコードです。
ブック名や列アルファベットを適時入れ替えると使えるかなと思います。

Sub sample()

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'i = 届1枚に記載できる人数
i = 5

'n = 全体のループ回数（届枚数と同じ）
n = (LastRow \ i) + 1

    For j = 1 To n
        For m = 1 To i 'm = 届出1枚の人数分だけ繰り返す
            k = m + (i * (j - 1))  'k = 何人目（何行目）の情報か
            
            atama = (m - 1) * 21 + 86 '転記先のセル行
            shigatsu = (m - 1) * 21 + 92 '転記先のセル行その2
            gogatsu = (m - 1) * 21 + 97 '転記先のセル行その3
            rokugatsu = (m - 1) * 21 + 102 '転記先のセル行その4
                
            '整理番号
            Workbooks("転記先ブック名").Worksheets(j).Range("D" & atama).Value = Workbooks("転記元ブック名").Worksheets(1).Range("A" & 1 + k).Text
            '氏名
            Workbooks("転記先ブック名").Worksheets(j).Range("M" & atama).Value = Workbooks("転記元ブック名").Worksheets(1).Range("B" & 1 + k).Text
            '元号
            If Workbooks("転記元ブック名").Worksheets(1).Range("C" & 1 + k).Text = "7" Then
                Workbooks("転記先ブック名").Worksheets(j).Range("AE" & atama).Value = "平成"
            Else: Workbooks("転記先ブック名").Worksheets(j).Range("AE" & atama).Value = "昭和"
            End If
            
            '和暦年月日
            Workbooks("転記先ブック名").Worksheets(j).Range("AI" & atama).Value = Workbooks("転記元ブック名").Worksheets(1).Range("D" & 1 + k).Text
            Workbooks("転記先ブック名").Worksheets(j).Range("AP" & atama).Value = Workbooks("転記元ブック名").Worksheets(1).Range("E" & 1 + k).Text
            Workbooks("転記先ブック名").Worksheets(j).Range("BA" & atama).Value = Workbooks("転記元ブック名").Worksheets(1).Range("F" & 1 + k).Text
            '従前健保
            Workbooks("転記先ブック名").Worksheets(j).Range("BC" & atama).Value = Workbooks("転記元ブック名").Worksheets(1).Range("G" & 1 + k).Text
            '従前厚年
            Workbooks("転記先ブック名").Worksheets(j).Range("BK" & atama).Value = Workbooks("転記元ブック名").Worksheets(1).Range("H" & 1 + k).Text
            
            '4月の日数
            Workbooks("転記先ブック名").Worksheets(j).Range("H" & shigatsu).Value = Workbooks("転記元ブック名").Worksheets(1).Range("I" & 1 + k).Text
            '4月の金額
            Workbooks("転記先ブック名").Worksheets(j).Range("M" & shigatsu).Value = Workbooks("転記元ブック名").Worksheets(1).Range("L" & 1 + k).Text
            '総計
            Workbooks("転記先ブック名").Worksheets(j).Range("BC" & shigatsu).Value = Workbooks("転記元ブック名").Worksheets(1).Range("O" & 1 + k).Text
            
            '5月の日数
            Workbooks("転記先ブック名").Worksheets(j).Range("H" & gogatsu).Value = Workbooks("転記元ブック名").Worksheets(1).Range("J" & 1 + k).Text
            '5月の金額
            Workbooks("転記先ブック名").Worksheets(j).Range("M" & gogatsu).Value = Workbooks("転記元ブック名").Worksheets(1).Range("M" & 1 + k).Text
            '平均
            Workbooks("転記先ブック名").Worksheets(j).Range("BC" & gogatsu).Value = Workbooks("転記元ブック名").Worksheets(1).Range("P" & 1 + k).Text
            
            '6月の日数
            Workbooks("転記先ブック名").Worksheets(j).Range("H" & rokugatsu).Value = Workbooks("転記元ブック名").Worksheets(1).Range("K" & 1 + k).Text
            '6月の金額
            Workbooks("転記先ブック名").Worksheets(j).Range("M" & rokugatsu).Value = Workbooks("転記元ブック名").Worksheets(1).Range("N" & 1 + k).Text
            
        Next m '次の人の転記
    Next j '次のページへ

End Sub

予め、転記先のブックについて必要な届出枚数分シートをコピーしておく必要があります。転記先のシートは左に寄せておいてください。シートコピーのVBAは下記の通り。

Sub sh_copy()
 
 For i = 1 To 7 '必要な枚数の数字に変えてください
    Worksheets(1).Copy Before:=Worksheets(1)
 Next i
 
End Sub

あとはXでもちょいちょい紹介している和暦年月日を分解する関数
例えば昭和60年6月10日生まれの方だと600610だったり
平成8年12月11日生まれの方だと81211とでたりするのを和暦年月日に分ける関数です。

• 和暦年

=LEFT(分解元セル,IF(LEN(分解元セル)=6,2,1))

• 月

=MID(RIGHT(分解元セル,4),1,2)

• 日

=RIGHT(分解元セル,2)

猛暑が続きますが……すっきりとした気持ちで7月10日を超えたいですね

給与や賞与の明細を作成するエクセルツールを公開しました！

エクセルダウンロードはこちらから

使い方は次の手順となります。

1. 基本データシートに事業所名などの情報を入力します
2. 明細データシートに各項目名や内容を入力します
3. 基本データシートに戻り、「明細を作成する」ボタンを推してください
4. 印刷用シートに明細が印字されるので印刷したりPDFにしたりしてお使いください

• 勤怠項目は10個、支給控除項目はそれぞれ11個設定できます
• 基本データ、明細データに空欄があっても明細データの1行目に氏名が入力されていれば作成されます
• マクロを使用しているのでセキュリティ警告が出る場合があります、その場合よろしければ下記図のとおりご対応ください

【注意事項】

• 不特定多数への再配布はおやめください
• 本ツールの利用に際し一切の責任を負いかねます

給与と賞与を簡易的に計算するエクセルツールを公開しました！確認用などにお使いください。

エクセルダウンロードはこちらから

使い方は次の手順となります。

給与計算

1. 灰色部分に基本情報を入力します
2. 灰色部分に給与計算に必要な情報を入力します
3. 社会保険料額・所得税額が表示されます
4. 住民税額や他控除項目があれば入力してください
5. 差引支給額が表示されます
6. 差引支給額がエラーと表示された場合は入力を見直してみてください

賞与計算

1. 灰色部分に基本情報を入力します
2. 灰色部分に賞与計算に必要な情報を入力します
3. 社会保険料額・所得税額が表示されます
4. 他控除項目があれば入力してください
5. 差引支給額が表示されます
6. 差引支給額がエラーと表示された場合は入力を見直してみてください

【改訂履歴2023/6/11】

• 給与計算で税区分乙欄で課税対象額88,000円以上だったときに住民税が二重で徴収されるバグを修正しました
• 簡易賞与計算シートを追加しました、前月の給与支払いがなかった場合にも対応しています

【改訂履歴2024/4/21】

• 協会けんぽ保険料率・介護保険料率を令和6年度の数字にしました（雇用保険料・所得税は変わりありません）
• 給与計算シートの標準報酬月額選択の際に金額幅が表示されるようにしました

【注意事項】

• 税区分乙で課税対象額74万円以上の方については給与計算ができません
• 賞与計算において、前月の社保控除後の金額の10倍を超える賞与の所得税額については計算ができません
• 不特定多数への再配布はおやめください
• 本ツールの利用に際し一切の責任を負いかねます