【VBA ほぼ変更不要】売上明細からPDF請求書を自動作成

公開日: 売上明細・売上集計表

エクセルで売上明細や請求明細を管理しているとき、自動で請求書を作れると非常に楽ですよね?

そこで、今回は、請求明細から請求書を全自動で作る方法を紹介します。

VBAソースの修正は最小限で済む作りなので、VBA初心者に特におすすめです。

※下記を再生しても音は出ませんので、音が出せない環境でもご安心ください。

請求書を作ろうとすると、どうしても関数だけでは実現できないので、必要に応じてVBAを使います。

請求書を作る全体的な流れ

この記事では、以下の流れで請求書を作っていきます。

Step1:Noに連動して、請求書を自動作成

請求No、取引先Noなどの「No」を固定したときに、その「No」に該当する請求書を自動で作れる仕組みを作る

Step2:Noを変化させPDF出力

「No」を次々と変更し、変更するたびにPDF出力 を繰り返す仕組みを作る

以下、順番に解説していきます。

seikyuusyo_1_1

Step1:Noに連動して、請求書を自動作成

最初に、Noを1つ固定し、そのNoに対する請求書を自動作成していきます。
請求書レイアウトの変更に柔軟に対応できるように、関数を使って自動作成をする仕組みを作ります

イメージ的には、次の図のような感じです。
No欄に「1」と入力すると、そのNoに対応する取引先名と請求明細が表示されるようにします。

seikyuusyo_1_2

取引先名はvlookup関数を使って入力

取引先名は、vlookup関数を使うと簡単に入力できます。

seikyuusyo_1_3

具体的には、次のような数式を入力します。

=VLOOKUP(B1,請求明細!$A:$G,2,FALSE)&" 御中"
seikyuusyo_1_4

請求明細はvlookup関数では転記できない

次に請求明細です。

請求明細は、(取引先名と同じように)vlookup関数を使って転記をすることはできません。
なぜなら「1つ」のNoに対して「複数」の請求明細があるからです。

seikyuusyo_1_5

vlookup関数を使うと、最初の1行しか転記することができないので、今回のような転記には使えないのです。

ユーザー定義関数arrayvlookup関数を使って転記

条件に合う複数行の転記を行う方法には様々な方法があります。
今回はarrayvlookup関数というユーザ定義関数を使います。

この関数を使うと、vlookup関数と同じような感覚で、複数行の転記をすることができます。

seikyuusyo_1_6

通常の関数とは異なり、次のように特殊な手順で入力することに注意してください。

  • B21~B30セルを選択
  • 「=arrayvlookup(B1,請求明細!$A:$G,3,FALSE)&""」と入力
  • Ctrl+Shift+Enterを押す

特に、単にB21セルに数式を入力し、それをB30セルまでコピペをするだけでは正しい計算結果は得られないことに注意してください。

入力済みの関数の修正・削除をする方法、その他の注意点は、複数行の値を返すvlookup関数をVBAのユーザー定義関数で作るをご覧ください。

同じように、内容だけでなく、納品日、数量、単価もユーザー定義関数であるarrayvlookup関数を使って入力します。

seikyuusyo_1_7

これで、B1セルの「No」を変えることで、そのNoに対応する請求書を表示できるようになりました。

※下記を再生しても音は出ませんので、音が出せない環境でもご安心ください。

Step2:Noを変化させPDF出力

Step1では、「No」を入力すれば、そのNoに対応する請求書を作れるようになりました。

この状態でも、先ほどの動画のように

Noを手で打ち替え
 ↓
PDF出力
 ↓
Noを手で打ち替え
 ↓
PDF出力
 ↓
・・・

と繰り返していけば、請求書を次々とPDF化することができます。

でも、せっかくですから、この作業もVBAを使って自動化してしまいましょう。

自動化すると、冒頭の動画のようにマクロを実行するだけで、請求書のPDFを自動作成することができるようになります。

VBAのソースコード

今回の、請求書PDFの自動作成マクロのソースコードは次のとおりです。

下記ソースコードを、標準モジュールに貼り付けてください。

Sub SeikyuusyoSakusei()
  Dim iSheet As Worksheet
  Set iSheet = ThisWorkbook.Worksheets("請求明細")
  
  Dim iNameCol As Long
  iNameCol = 2

  Dim oCell As Range
  Set oCell = ThisWorkbook.Worksheets("請求書ひな形").Range("B1")
  oCell.Value = ""
  
  Dim tmplSheet As Worksheet
  Set tmplSheet = ThisWorkbook.Worksheets("請求書ひな形")
  
  Dim iY As Long
  iY = 2
  
  Dim oFilename As String
      
  While iSheet.Cells(iY, 1).Value <> ""
    If oCell.Value <> iSheet.Cells(iY, 1).Value Then
      oCell.Value = iSheet.Cells(iY, 1).Value
      oFilename = ThisWorkbook.Path & "\" & oCell.Value & _
                  "_" & iSheet.Cells(iY, iNameCol).Value & _
                  "_" & Format(Date, "yyyymmdd") & "_請求書.pdf"
      tmplSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=oFilename
    End If
    iY = iY + 1
  Wend
End Sub


'以下、arrayvlookup関数の定義です。
Function ARRAYVLOOKUP(Key As Variant, R As Range, C As Long, _
                      Optional T As Boolean = True, _
                      Optional maxretY As Long = 20) As Variant
  '検索の型はTRUEを指定しても動作は変わらない
  Dim ret As Variant
  ReDim ret(1 To maxretY, 1 To 1) As Variant
  
  Dim targetR As Range
  Set targetR = Intersect(R, R.Worksheet.UsedRange)

  Dim Data As Variant
  Data = targetR
  
  Dim y As Long
  Dim retY As Long
  retY = 1
 
  For y = LBound(Data) To UBound(Data)
    If Data(y, 1) = Key Then
      ret(retY, 1) = Data(y, C)
      retY = retY + 1
      If retY > maxretY Then
        Exit For
      End If
    End If
  Next
    
  If retY > 1 Then
    For y = retY To maxretY
      ret(y, 1) = ""
    Next
  Else
    ret(1, 1) = CVErr(xlErrNA)
  End If
  
  ARRAYVLOOKUP = ret
End Function

下半分のグレーで表示されている部分(Function~EndFunction)はarrayvlookup関数のソースコードです。
詳細は、複数行の値を返すvlookup関数をVBAのユーザー定義関数で作るをご覧ください。

プログラムの解説

今回、新たに作成したのは上半分(Sub~End Sub)の部分のみです。
以下、その部分について解説していきます。

プログラムで使う変数の定義

Sub SeikyuusyoSakusei()
  Dim iSheet As Worksheet
  Set iSheet = ThisWorkbook.Worksheets("請求明細")
  
  Dim iNameCol As Long
  iNameCol = 2

  Dim oCell As Range
  Set oCell = ThisWorkbook.Worksheets("請求書ひな形").Range("B1")
  oCell.Value = ""
  
  Dim tmplSheet As Worksheet
  Set tmplSheet = ThisWorkbook.Worksheets("請求書ひな形")
  
  Dim iY As Long
  iY = 2

今回のプログラムで使う変数を定義しています。
それぞれの変数が指す位置を図解すると次のようになります。

seikyuusyo_1_9

変数の命名規則は適当な部分もありますが、

  • 入力関連:最初に「i」をつける
  • 出力関連:最初に「o」をつける

を意識して名前をつけています。

繰り返し処理

  While iSheet.Cells(iY, 1).Value <> ""
    (メインロジック)
    iY = iY + 1
  Wend

今回のVBAマクロのメインロジックです。

(元々変数iYには「2」が格納されているので)最初にiSheet(請求明細シート)の2行目、1列目のセルが空欄かどうかを調べます。

空欄でなければ「(メインロジック)」の部分の処理を行います。
その後、iYに1を足して、同じ処理を繰り返します。

メインロジック

  If oCell.Value <> iSheet.Cells(iY, 1).Value Then
 (PDF出力処理)
  End If

次にメインロジックです。

「iSheet.Cells(iY,1).value」で、現在処理中の行の1列目の値(=No)が得られます。
それを「oCell.Value」(=請求書ひな形シートのB1セルの値)と比較します。

そして、両者が一致しない場合のみPDF出力処理に進みます。

逆に、両者が一致するということは、すでに、その「No」は処理済みということです。ですから、何も処理せずに次に進みます。

PDF出力処理

最後にPDF出力処理です。

  oCell.Value = iSheet.Cells(iY, 1).Value

まず、「このNoまで処理したよ!」という目印にするため、「oCell」(=請求書ひな形シートのB1セル)に「iSheet.Cells(iY,1).value」(現在処理中の行のNo)を格納します。

  oFilename = ThisWorkbook.Path & "\" & oCell.Value & _
               "_" & iSheet.Cells(iY, iNameCol).Value & _
              "_請求書.pdf"

次に「oFilename」に、PDF出力する先のファイル名を格納します。

今回は、下記のデータを、つなげたものをファイル名としています。

ソース内の記述 意味
ThisWorkbook.Path このエクセルブックがあるパス
"\" ※「¥」マークです。画面上は「斜め線」に見えているかもしれませんが、お手元のPCにコピペすると「¥」マークに変わります。
oCell.Value 請求書No(あるいは取引先No)
"_"  
iSheet.Cells(iY, iNameCol).Value 取引先名
"_"  
Format(Date, “yyyymmdd”) 今日の日付を「年4桁」「月2桁」「日2桁」形式で表したもの
"_請求書.pdf"  

実際には
「C:¥My Documents¥1_株式会社アクティ_20180214_請求書.pdf」
「C:¥My Documents¥2_大中産業株式会社_20180214_請求書.pdf」
という感じのファイル名になります。

  tmplSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=oFilename

tmplSheet(「請求書ひな形」シート)の内容を使って、oFilename(指定したファイル名)という名前のPDFファイルを作成します。

補足:Noが請求書に印刷されて邪魔な場合

現段階では見やすいように「請求書ひな形」シートの上に「No」の入力欄を作っています。そのため、このままの状態で請求書を印刷すると、左上に「No」が表示されてしまいます。

もし「No」を表示させたくない場合には、「No」入力欄を別シートに移動させます。
たとえば「作業用シート」という名前のシートを新たに作って、そのシートに「No」入力欄を移動させてください。

また、VBAのソース中、下記の赤字部分を、それに合わせて変更してください。

  Set oCell = ThisWorkbook.Worksheets("請求書ひな形").Range("B1")

こうすることで、PDFファイルに「No」が表示されなくなります。

まとめ

請求書ひな形への転記作業をすべて(ユーザー定義関数を含む)関数化することで、PDF出力処理のVBAが、かなりシンプルになりました。

また、今回紹介した、VBAのソースは、請求書のレイアウトが変わったり、全然別の帳票を作る場合でも、ほとんど変更せずに使い回すことができます。

ぜひ、ご活用ください!


  • twitter
  • facebook
  • はてなブックマーク

経理事務のためのエクセル基礎講座動画マニュアル無料配布中

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!