スタハEXCELセミナー　給与明細をつくってみよう！！

今回はEXCEL講座の第3弾です。

今回は1枚目のワークシート関数、2回目のVBAの内容をミックスして、帳票の連続印刷に挑戦してみます。
若干、応用的な要素もあるので、もしセミナー内で理解が難しい場合は、このページを見返して復習をしてくださいね！！

ではやってみよう！！

まずは練習ファイルのダウンロード

以下を活用します。
適宜ダウンロードしてください。

drive.google.com

ファイルの説明

以下の給与明細にデータを差し込んで印刷することを目指します。

シート名 : Print

f:id:hanabusa-snow:20210703221748p:plain

データは以下の通りです。

シート名 : Data

f:id:hanabusa-snow:20210703221919p:plain

印刷レイアウトとデータは異なるシートにあるので、これらを合成していくイメージになります。

作業

では、まずデータを差し込みます。
今回は、DGET関数を使用します。
DGET関数は第1回のセミナーで紹介しました。
www.pon-x.jp

DGET(データベース, データを差し込みたい列の列名, 検索する列の列名とデータの範囲)

慣れるまで難しいですが、手を動かして覚えましょう。

まずは支給年月を差し込みます。

データはここですね。
f:id:hanabusa-snow:20210703222408p:plain

ここに差し込みます。
f:id:hanabusa-snow:20210703222236p:plain

以下のようにDGET関数を記述しましょう。

=DGET(Data!$A:$O,F2,$J$4:$J$5)

f:id:hanabusa-snow:20210703222241p:plain

これでNo1の日付が表示されました。
DGET関数は列名でデータを参照できるので、VLOOKUP関数に比べて調整しやすいですね！！

書くのがめんどくさい関数の複製はコピペする

このように、所属と氏名も入れたいのですが、めんどくさいのでコピペします。
ただ、そのままコピペすると書式が崩れてしまうので、数式だけコピーします。

以下の通り、日付のセルをコピーして2か所にペーストしてください。
※　支給年月でコピーして、ペーストしたいセルで右クリックして数式貼り付けを行う。

f:id:hanabusa-snow:20210704110521p:plain

※　支給年月でコピーして、ペーストしたいセルで右クリックして数式貼り付けを行う。
f:id:hanabusa-snow:20210704110630p:plain

セルの結合の都合でコピペできないところは、数式自体をコピーして編集する

明細部分は、セルが結合されている都合上、上記のように単純コピペができません。
なので、数式をあらためて書いていく必要があります。
以下を使ってもらっても結構です。

=DGET(Data!$A:$O,B8,$J$4:$J$5)

こんな感じに、データを参照することができました。
f:id:hanabusa-snow:20210703222252p:plain

以下は、セル右下の緑の四角を下に引っ張ってコピーしましょう。
緑の四角はこれ↓
f:id:hanabusa-snow:20210703223730p:plain

こんなかんじにコピーできます。
f:id:hanabusa-snow:20210703222256p:plain

控除額は支給額からコピーしよう。

数式コピーをすればOK！！
※　支給年月でコピーして、ペーストしたいセルで右クリックして数式貼り付けを行う。
f:id:hanabusa-snow:20210704110735p:plain

そして、またまた緑の四角を引っ張ってコピーしましょう。
f:id:hanabusa-snow:20210703222256p:plain

支給額と控除額の合計差引支給額を計算しよう

これは単純な演算ですね。

まずは支給額の合計

=SUM(D8:D21)

こんなかんじで計算できました。
f:id:hanabusa-snow:20210703222308p:plain

控除額合計は↑を数式コピーしましょう。

f:id:hanabusa-snow:20210703222312p:plain

合計差引支給額は、支給額から控除額を引く

こんなかんじでけいさんできました。
f:id:hanabusa-snow:20210703222317p:plain

備考のデータ

DGET関数を若干編集して以下の通りにしましょう。

=Data!$A:$O,B24,$J$4:$J$5)

こんなかんじに表示されました。
f:id:hanabusa-snow:20210703222320p:plain

Noを変えてみよう。

Noを変えると、連動してDGET関数で入力したセルの値が変わってくれます。
f:id:hanabusa-snow:20210703222324p:plain

しかし、備考がエラーになっていることに気づきます。データを見てみると、No2の備考は空文字でした。↓
f:id:hanabusa-snow:20210703224522p:plain

DGET関数は参照するデータが空文字だとエラーになってしまいます。
なので、備考欄を以下のように書き換えてあげましょう。

=IF(ISERROR(DGET(Data!$A:$O,B24,$J$4:$J$5)),"",DGET(Data!$A:$O,B24,$J$4:$J$5))

難しいようですが、おちついてみるとなんてことありません。
IF関数は条件分岐で、ISERROR関数はエラーかどうかを判定しています。
なので、数式を読み解いて文章にすると以下のようになります。

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
IF ・・・もし、
ISERROR・・・「備考」のデータが空文字なら（つまりエラーなら）、

正しければ　　・・・空文字を表示する、
正しくなければ・・・備考のデータを表示する
↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
ということです。

こんなかんじに、エラーが出ませんでした。
f:id:hanabusa-snow:20210703222327p:plain

印刷する

では、次にいよいよこれを印刷します。
印刷は、VBAで制御します。

ボタンを作ってプログラムを埋め込みましょう。
ボタンの作り方を忘れてしまった方は、第2回のセミナー資料を見ましょう。

www.pon-x.jp

印刷するプログラムはたったの1行！！

とっても簡単です。
以下の通りに書きましょう。

Sub 印刷()

    Sheets("Print").PrintOut

End Sub

f:id:hanabusa-snow:20210703222335p:plain

ボタンを押して印刷できるかを確かめてみましょう。

連続印刷をしてみよう

まずは、一括印刷のボタンを作ってあげます。

f:id:hanabusa-snow:20210703222339p:plain

印刷はできましたが、これでは全員印刷するのが手間ですね。
なので、以下の手順でプログラムをかいてあげます。

1からDataシートのNoの最大値まで繰り返す。
繰り返しの最中に、繰り返し回数をPrintシートのNoに挿入する
印刷する

上記の3STEPでOKです。

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

Sub 一括印刷()

    For i = 1 To Application.Max(Sheets("Data").Range("A:A"))
    
        Sheets("Print").Range("J5") = i

        Call 印刷

    Next

End Sub

※ Application.Max・・・ Applicationはワークシート関数を使うということになるので、 Application.Maxはワークシート関数のMax関数の意味になります。
※ [Call 印刷]は、最初に作った印刷プログラムを呼び出しています。つまり再利用です！！

以上、給与明細を作ろうでした。

本日はお疲れ様でした

模範回答はこちらです。
drive.google.com

事業紹介

Pythonプログラミングの初心者向け講座やってます。

プログラミング学びたい方で、少人数でじっくり学びたい方！！
ぜひご覧ください！！

pumpkin-labo.com

データ加工・整理、分析の請負をやってます。

学校向けにサービス展開してますが、企業さま向けにもサービス可能です。
御社の事業のなかで、データ整理や活用にお困りの場合はぜひご相談ください。データベースの運用代行やシステム開発もやってます。