【Excel VBA】請求書の自動作成

Excel VBAは、使っている人にとっては便利な一方、プログラミングに触れたことがない人にとっては、利便性がわかりにくいと思います。

そこで今回は、利便性を体感してもらうことを目標に、販売記録から請求書を自動作成するVBAの例をご紹介します。これ1つのVBAを学習するだけでも、実務にさまざま応用が利くようになっています。

そもそもVBAを使ったことがない、という方は以下の記事を参照ください。

販売記録から請求書を作成
コードの解説

販売記録から請求書を作成

まずは今回紹介するVBAがどんな動きをするかを紹介します。以下のように、自社のシステムで保持している販売記録を業務で活用する機会は多いと思います。今回はこのデータから、請求書を自動で作成します。

途中1度だけ、請求書の宛先を入力するボックスを用意しました。

それ以外には作業必要なく、結果的に以下のような請求書シートが自動で生成されます。

上部のヘッダーには発行日・宛先・差出人の情報があります。

続いて中段に請求書の明細情報を記載しています。明細部分は契約番号単位で集計し、小計として金額を合計しています。また明細部分の最後には総計の金額を表示しています。

明細の下にはフッターとして、振込先の銀行口座情報を記載しました。

これはあくまで一例で、VBAをこんな風に使えるんですよ、と示すために作ったものです。次項で説明するコードを理解できれば、誰でもこういった業務自動化が可能、というのが最大のポイントです。

コードの解説

では次に上の動作を達成したコードを具体的に示し、解説していきます。長くなりますので、全体のコードは最後に掲載します。

まずは以下のコードで、元データのシートをコピーして、名前を”請求書”と変更します。その後、1-7行目に行を挿入します。

ActiveSheet.Copy before:=Sheets(1)

Sheets(1).Select

ActiveSheet.Name = “請求書”

Range(Rows(1), Rows(7)).Insert

シートのコピー、行の挿入については以下の記事を参照ください。

次に、後続の処理で使う変数をあらかじめ宣言し、最終行を取得、For文を用いた繰り返し処理によって、最終行から9行目にむかって以下の処理をします。カウンタ変数iが20であるとして説明します。

A20セルの値が1行下のA21セルの値と同じではない、つまり異なる場合は以下の処理を実行
- A21に行を挿入
- C21にA20の値(契約番号)を入力
- D21に”小計”と入力
- E21列に”=SUMIF(A9:A20,C21,E9:E20)”を入力
- C21-E21をオレンジに塗りつぶす
処理終了

Dim lastrow As Integer, lastrow2 As Integer, k As Integer, i As Integer, label As Variant

lastrow = Cells(Rows.Count, 1).End(xlUp).row

For i = lastrow To 9 Step -1

If Cells(i, 1).Value <> Cells(i +1, 1).Value Then

Rows(i + 1).Insert

Cells(i + 1, 3).Value = Cells(i, 1).Value

Cells(i + 1, 4).Value = “小計”

Cells(i + 1, 5).Formula = “=SUMIF(A9:A” & i & “,C” & i + 1 & “,E9:E” & i & “)”

Range(Cells(i + 1, 3), Cells(i + 1, 5)).Interior.ColorIndex = 40

End If

Next i

最終行の取得、If文による条件分岐、For文による繰り返し処理、背景色の変更については以下を参照ください。

次に、総計を挿入します。まず、上の処理で行が追加されて、変数lastrowを取得した後に最終行がずれているので、再度D列で最終行を取得します(lastrow2)。最終行が35とした場合、以下の処理をします。

D36セルに”総計”と入力
E36セルに”=SUMIF(D11:D35,”小計”&”*”,E11:E35)”と入力
E36セルのフォントを赤色にする
D36:E36の範囲について、太字と背景黄色を設定する

lastrow2 = Cells(Rows.Count, 5).End(xlUp).row

Cells(lastrow2 + 1, 4).Value = “総計”

Cells(lastrow2 + 1, 5).Formula = “=SUMIF(D9:D” & lastrow2 & “,””小計””,E9:E” & lastrow2 & “)”

Cells(lastrow2 + 1, 5).Font.color = vbRed

With Range(Cells(lastrow2 + 1, 4), Cells(lastrow2 + 1, 5))

.Font.Bold = True

.Interior.ColorIndex = 6

End With

フォントの色の変更、With構文については以下を参照ください。

また、VBAではありませんが、ExcelのSUMIF関数の使い方については以下を参照ください。

次に、以下の8行目のようなラベル部分を作成します。

具体的には、変数labelに格納しているArray(“”)という配列から、カウント変数kを使って1つずつデータを取り出して、Cells(8,k)、つまり8行目に順番に貼り付けます。その後、色をグレーに塗っています。

‘ラベル名変更

label = Array(“契約番号”, “見積番号”, “明細番号”, “商品名”, “注文金額”, “納品日”, “支払期限”)

For k = 1 To 7

Cells(8, k) = label(k – 1)

Range(Cells(8, 1), Cells(8, 7)).Interior.ColorIndex = 15

‘8行目にタイトル追加

Cells(6, 1).Value = “請求書”

配列については以下を参照ください。

次に、シート全体に対して以下の設定をします。Cellsというのは、すべてのセルを指定しています。すべてのセルに対して以下を実行します。

フォントを”メイリオ”に統一
フォントサイズを9に統一
文字の縦位置の配置を”中央揃え”に
文字の横位置の配置を”中央揃え”に

‘シート全体の書式設定

With Cells

.Font.Name = “Meiryo UI”

.Font.Size = 9

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

End With

フォントの変更、フォントサイズの変更、文字の配置変更については以下の記事を参照ください。

続いて、左上のヘッダー部分を設定します。

具体的には、以下の通りです。

A2セルに作成日の日付を入力する。日付は”yyyy年m月d日”形式。
A3セルに”宛先：”と入力する
入力ボックスを出現させ、入力内容をB3セルに入力する。
A1:B2の範囲について、左揃えにする。

‘左上側ヘッダー追加

Cells(2, 1).Value = “発行日: ” & Format(Date, “yyyy年m月d日”)

Cells(3, 1).Value = “宛先:”

Cells(3, 2).Value = InputBox(“宛先を入力してください：”)

Range(Cells(2, 1), Cells(3, 2)).HorizontalAlignment = xlLeft

インプットボックスの使い方は以下を参照ください。

日付の設定の部分については今後記事を作成しますのでお待ちください。

次に、右上のヘッダーを設定します。

具体的には以下の処理をします。

G2セルに”差出人：ABC株式会社”
G3セルに”営業部”
G4セルに”鈴木　一郎”
G2:G4を右揃えに

‘右上ヘッダー追加

Cells(2, 7).Value = “差出人：ABC株式会社”

Cells(3, 7).Value = “営業部”

Cells(4, 7).Value = “鈴木　一郎”

Range(Cells(2, 7), Cells(4, 7)).HorizontalAlignment = xlRight

次に、左下のフッター部分を設定します。

具体的には、”総計”の部分で取得した最終行のlastrow2を使って以下の処理をします。

A列最終行+4行目に”振込先銀行口座:”
A列最終行+5行目に”大日本帝国銀行”
A列最終行+6行目に”XX支店”
A列最終行+7行目に”1234567890″

‘左下フッター追加

Cells(lastrow2 + 4, 1).Value = “振込先銀行口座:”

Cells(lastrow2 + 5, 1).Value = “大日本帝国銀行”

Cells(lastrow2 + 6, 1).Value = “XX支店”

Cells(lastrow2 + 7, 1).Value = “口座番号 : 1234567890”

次に、セルを折り返すかどうかについて設定します。

2行目から5行目までは折り返しなし、9行目から表の最終行までは折り返しありで設定しています。

‘書式設定

Range(Cells(2, 1), Cells(5, 7)).WrapText = False

Range(Cells(9, 1), Cells(lastrow2, 7)).WrapText = True

wraptextの使い方は以下を参照ください。

次にタイトルを設定します。

A6セルに”請求書”と入力後、A1-G1の範囲に以下を設定します。

選択範囲内で中央揃え
フォントサイズは14
太字

‘タイトル書式

Cells(6, 1).Value = “請求書”

With Range(Cells(6, 1), Cells(6, 7))

.Select

.HorizontalAlignment = xlCenterAcrossSelection

.Font.Size = 14

.Font.Bold = True

End With

なお、選択範囲内で中央というのがどういうことかわからない方は以下を参照ください。

次に、フッターの書式を設定します。具体的には、フッター部分のA列～G列について、折り返しをFalseにし、表示を左揃えに設定します。

‘フッター書式

With Range(Cells(lastrow2 + 4, 1), Cells(lastrow2 + 10, 7))

.WrapText = False

.HorizontalAlignment = xlLeft

End With

最後に、金額の表記を設定します。金額は以下の通り、E列の9行目以降に表示されています。

この部分に対して、右揃えと、小数点なしで桁区切りを意味する”#,#”を設定します。

‘金額表記設定

Range(Cells(9, 5), Cells(lastrow2 + 1, 5)).HorizontalAlignment = xlRight

Range(Cells(9, 5), Cells(lastrow2 + 1, 5)).NumberFormatLocal = “#,#”

数値の表示形式を設定するNumberFormatLocalについては今後記事を作成しますのでお待ちください。

以上で、処理全体を説明しました。改めてコード全体を以下に掲載しますので、ご参照ください。

Sub invoice()

‘請求書シートの作成

ActiveSheet.Copy before:=Sheets(1)

Sheets(1).Select

ActiveSheet.Name = “請求書”

‘行の挿入

Range(Rows(1), Rows(7)).Insert

‘変数の宣言

Dim lastrow As Integer, lastrow2 As Integer, k As Integer, i As Integer, label As Variant

‘最終行の取得1

lastrow = Cells(Rows.Count, 1).End(xlUp).row

‘小計の追加

For i = lastrow To 9 Step -1

If Cells(i, 1).Value <> Cells(i + 1, 1).Value Then

Rows(i + 1).Insert

Cells(i + 1, 3).Value = Cells(i, 1).Value

Cells(i + 1, 4).Value = “小計”

Cells(i + 1, 5).Formula = “=SUMIF(A9:A” & i & “,C” & i + 1 & “,E9:E” & i & “)”

Range(Cells(i + 1, 3), Cells(i + 1, 5)).Interior.ColorIndex = 40

End If

Next i

‘最終行の取得2

lastrow2 = Cells(Rows.Count, 5).End(xlUp).row

‘総計追加

Cells(lastrow2 + 1, 4).Value = “総計”

Cells(lastrow2 + 1, 5).Formula = “=SUMIF(D9:D” & lastrow2 & “,””小計””,E9:E” & lastrow2 & “)”

Cells(lastrow2 + 1, 5).Font.color = vbRed

With Range(Cells(lastrow2 + 1, 4), Cells(lastrow2 + 1, 5))

.Font.Bold = True

.Interior.ColorIndex = 6

End With

‘ラベル名変更

label = Array(“契約番号”, “見積番号”, “明細番号”, “商品名”, “注文金額”, “納品日”, “支払期限”)

For k = 1 To 7

Cells(8, k) = label(k – 1)

Range(Cells(8, 1), Cells(8, 7)).Interior.ColorIndex = 15

‘シート全体の書式設定

With Cells

.Font.Name = “Meiryo UI”

.Font.Size = 9

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

End With

‘左上側ヘッダー追加

Cells(2, 1).Value = “発行日: ” & Format(Date, “yyyy年m月d日”)

Cells(3, 1).Value = “宛先:”

Cells(3, 2).Value = InputBox(“宛先を入力してください：”)

Range(Cells(2, 1), Cells(3, 2)).HorizontalAlignment = xlLeft

‘右上ヘッダー追加

Cells(2, 7).Value = “差出人：ABC株式会社”

Cells(3, 7).Value = “営業部”

Cells(4, 7).Value = “鈴木　一郎”

Range(Cells(2, 7), Cells(4, 7)).HorizontalAlignment = xlRight

‘左下フッター追加

Cells(lastrow2 + 4, 1).Value = “振込先銀行口座:”

Cells(lastrow2 + 5, 1).Value = “大日本帝国銀行”

Cells(lastrow2 + 6, 1).Value = “XX支店”

Cells(lastrow2 + 7, 1).Value = “口座番号 : 1234567890”

‘書式設定

Range(Cells(2, 1), Cells(5, 7)).WrapText = False

Range(Cells(9, 1), Cells(lastrow2, 7)).WrapText = True

‘タイトル書式

Cells(6, 1).Value = “請求書”

With Range(Cells(6, 1), Cells(6, 7))

.Select

.HorizontalAlignment = xlCenterAcrossSelection

.Font.Size = 14

.Font.Bold = True

End With

‘フッター書式

With Range(Cells(lastrow2 + 4, 1), Cells(lastrow2 + 10, 7))

.WrapText = False

.HorizontalAlignment = xlLeft

End With

‘金額表記設定

Range(Cells(9, 5), Cells(lastrow2 + 1, 5)).HorizontalAlignment = xlRight

Range(Cells(9, 5), Cells(lastrow2 + 1, 5)).NumberFormatLocal = “#,#”

End Sub