Excelマクロで請求書作成→PDF保存。複数データ対応完全版。

Excelマクロで請求書をつくっています。
実際に私がつかっているのは、複数のデータがあっても転記できるバージョンです。
そのバージョンを解説します。

EX-IT_23

Excelマクロで請求書をつくる流れ

Excelファイルの構成

Excelマクロで請求書をつくるには、3枚のシートを使います。
まず、請求書データ(data)
EX-IT_24

請求書のひな型(master)

EX-IT_27

顧客データ(住所、担当者名等)

EX-IT_25

このうち、請求書データには、VLOOKUP関数を使って、シート「顧客データ」から名称を連動させています。

EX-IT_37

また、会計データも作成しているので、取り込みにも使えます。


■スポンサードリンク

EX-IT_38

マクロで転記する部分

マクロでやるのは、請求書データから、請求書のひな型への転記です。
人間がやるならコピーして貼り付けてコピーして貼り付けてを繰り返さなければいけません。

転記しているのは、
・請求書ナンバー
・顧客コード
・日付
・支払期限

・請求データ(項目、内容、金額)

です。

EX-IT_30

すべてのデータをマクロで転記することもできますが、Excel側でやったほうが、つくりやすくメンテナンスしやすい部分もあるため、マクロとExcelを組み合わせています。

日付は、ひな型に書式を設定しておき(Ctrl+1)、「2018年5月1日」と表示されるようにしておきましょう。

EX-IT_31

EX-IT_32

Excelで計算する部分

小計、消費税の計算、合計は、Excelで計算しています。

EX-IT_29

ExcelのVLOOKUP関数で連動する部分

顧客コードは、請求書の印刷範囲外に転記し、そのコードをVLOOKUPで読み取っています。

EX-IT_28

こういった数式です。

EX-IT_33

コードが1なら、「株式会社タイムコンサルティング」のデータを連動して表示します。

EX-IT_34

こういうしくみをつくっておき、マクロ部分をつくっていく流れです。

請求書作成マクロ(データが1行)

まず、請求書データが1行の場合を考えてみます。

EX-IT_35

マクロはこういったものです。

Sub invoice1()

 

'選択した部分で繰り返す
Dim i As Long
For i = Selection(1).Row To Selection(Selection.count).Row

'選択したデータのうち最初の行なら新規ブックへひな形をコピー
 If i = Selection(1).Row Then
  ThisWorkbook.Worksheets("master").Copy
 Else
'そうでなかったら同じブックでシートをコピー
  ActiveSheet.Copy after:=Worksheets(Worksheets.count)
 End If


'データの転記
 Range("e1").Value = ThisWorkbook.Worksheets("data").Range("a" & i).Value
 Range("f1").Value = ThisWorkbook.Worksheets("data").Range("b" & i).Value 'コード
 Range("e5").Value = ThisWorkbook.Worksheets("data").Range("d" & i).Value '発行日
 Range("e8").Value = ThisWorkbook.Worksheets("data").Range("e" & i).Value '支払期限
 Range("b20").Value = ThisWorkbook.Worksheets("data").Range("f" & i).Value '項目
 Range("c20").Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value '内容
 Range("e20").Value = ThisWorkbook.Worksheets("data").Range("h" & i).Value '金額
 ActiveSheet.name = ThisWorkbook.Worksheets("data").Range("a" & i).Value & ThisWorkbook.Worksheets("data").Range("c" & i).Value  'シート名


Next

'■請求書PDFの作成

Dim w

'それぞれのシートをPDFとして保存
For Each w In Worksheets
 w.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & w.name & "様 請求書" & ".pdf"
Next

'作成したExcelを閉じる

ActiveWorkbook.Close savechanges:=False


End Sub

順を追ってみていきます。

forは、繰り返しを指示する構文です。
forからNextまでを繰り返します。

Dim i
For i = 1 to 10

Next
と通常書きます。
iというのは箱のようなもので、1to10なら、1から10までをiに入れて繰り返すという意味です。
(変数といいます)

今回の場合、少し工夫しています。

EX-IT_41

請求書をつくるときは、〇月という指定もできますし、シートにあるすべてのデータという指定もできますが、「選択したデータ」で請求書をつくるというしくみにしておいたほうが、便利です。

請求書をつくるのは原則として月に1回なのですが、そうでない場合もありえます。
つくりたい請求書を選択して、マクロを実行したほうが使いやすくなるわけです。

For i = Selection(1).Row To Selection(Selection.count).Row
なので、

「Selection(1).Row」(選択した1行目)から、「 Selection(Selection.count).Row」(選択した最終行)まで繰り返します。

Selection(Selection.count).RowのSelection.countで何行選択したか、数えてくれるのです。
この場合は、3行なので、Selection.countは3。2行目から3つ、つまり4行までのデータを指定していることになります。

EX-IT_49

次にやるのは、ひな型のコピーです。
もし、Selection(1).Row(選択した1行目)のときは、新規ブックへひな形をコピーしています。

ThisWorkbook.Worksheets(“master”).Copy

は、このブック(マクロが入っているブック)のシート「master」を新規ブックにコピーするという意味です。
通常はコピー先を指定するのですが、省略すると、新規ブックへコピーできます。

EX-IT_42

EX-IT_47

選択した最初の行じゃなかったら、先ほどコピーしたブックで、ひな形をコピーします。

IFは、
If 〇〇 Then

△△
Else
■■
End If

で、もし〇〇だったら、△△、そうでなかったら■■という指示です。

EX-IT_43

このように新しいブックにシートが増えていくわけです。
このブックは最終的には保存せずに閉じます。
PDFで保存しておけば十分なので、Excelのこのブックは必要ありません。
EX-IT_48

ひな形をコピーしたら、データをそれぞれ転記します。

たとえば、
Range(“e1”).Value = ThisWorkbook.Worksheets(“data”).Range(“a” & i).Value
では、2行目のデータ(i=2)なら、ひな形をコピーしたシートのセルE1に、このブックのシート「data」のセルA2の値(79)を入れるというしくみです。

なお、こういった部分は、もっと短い文にすることもできるのですが、かえって混乱することもあるので(特にはじめたばかりの方は)、あえて長めにするようにしています。

転記が終わったら、PDFで保存しましょう。

2行目から4行目を指定した場合、このようなファイルになっていますので、それぞれのシートをPDFに保存していきます。

すべてのシート、それぞれで処理をするなら、
Dim w
For Each w in Worksheets
〇〇
Next

を使います。
wはなんでも大丈夫です。

PDFにし、ファイル名を指定して保存し、このブックを保存しているフォルダに保存されます)最後に、請求書のExcelファイル(新しいブック)は閉じます。

このように、請求書PDFが完成しました。
データが何万行あっても同じ手間です。
人間が作業することはありません。

EX-IT_64

私は最初に勤めた1997年から、こういうしくみを使っていました。
もちろん、職場にはこっそり。
こんなに早く楽に終わっているとばれたら、また仕事が降ってきますし。
ただ、その分の時間は、さらなる効率化の勉強をしていたので、職場には還元してきたつもりです。

請求書作成マクロ(データが複数行)

データが1行ずつならさきほどのマクロで大丈夫なのですが、このように、同じ取引先で複数項目がある場合は、対応できません。
そこで、プログラムを少し変えていきます。

EX-IT_36

Sub invoice()

'データが複数行ある場合の貼り付け位置
Dim n
n = 21

'選択した部分で繰り返す
Dim i As Long
For i = Selection(1).Row To Selection(Selection.count).Row

'もし、上のデータと請求書番号が違う場合
 If ThisWorkbook.Worksheets("data").Range("a" & i).Value <> ThisWorkbook.Worksheets("data").Range("a" & i - 1).Value Then

'選択したデータのうち最初の行なら新規ブックへひな形をコピー
  If i = Selection(1).Row Then
   ThisWorkbook.Worksheets("master").Copy
  Else
'そうでなかったら同じブックでシートをコピー
   ActiveSheet.Copy after:=Worksheets(Worksheets.count)
   Range("b20", "e37").ClearContents

'貼り付け位置リセット
   n = 21

  End If


'データの転記
  Range("e1").Value = ThisWorkbook.Worksheets("data").Range("a" & i).Value
  Range("f1").Value = ThisWorkbook.Worksheets("data").Range("b" & i).Value 'コード
  Range("e5").Value = ThisWorkbook.Worksheets("data").Range("d" & i).Value '発行日
  Range("e8").Value = ThisWorkbook.Worksheets("data").Range("e" & i).Value '支払期限
  Range("b20").Value = ThisWorkbook.Worksheets("data").Range("f" & i).Value '項目
  Range("c20").Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value '内容
  Range("e20").Value = ThisWorkbook.Worksheets("data").Range("h" & i).Value '金額
  ActiveSheet.name = ThisWorkbook.Worksheets("data").Range("a" & i).Value & ThisWorkbook.Worksheets("data").Range("c" & i).Value  'シート名


'上のデータと請求書番号が同じ場合
 Else
  Range("b" & n).Value = ThisWorkbook.Worksheets("data").Range("f" & i).Value '項目
  Range("c" & n).Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value '内容
  Range("e" & n).Value = ThisWorkbook.Worksheets("data").Range("h" & i).Value '金額
  n = n + 1
 End If



Next

'■請求書PDFの作成

Dim w

'それぞれのシートをPDFとして保存
For Each w In Worksheets
 w.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & w.name & "様 請求書" & ".pdf"
Next

'作成したExcelを閉じる

ActiveWorkbook.Close savechanges:=False


End Sub


複数項目がある場合は、このように同じシートに転記してほしいものです。

EX-IT_59

そこで、IFを追加しました。

EX-IT_63

最初に、「A列の1つ上のセルと違うときは」という条件にしています。
If ThisWorkbook.Worksheets(“data”).Range(“a” & i).Value <> ThisWorkbook.Worksheets(“data”).Range(“a” & i – 1).Value Then

で、

ThisWorkbook.Worksheets(“data”).Range(“a” & i).Value


ThisWorkbook.Worksheets(“data”).Range(“a” & i – 1).Value
を比較しているわけです。

iが5のとき、
IThisWorkbook.Worksheets(“data”).Range(“a” & i).Value は、セルA5、

ThisWorkbook.Worksheets(“data”).Range(“a” & i – 1).Value は、セルA4のことになり、A4とA5が一緒かどうかを判定します。

EX-IT_61

違っていれば、最初のブログラムのように、20行目に転記し、

EX-IT_60

そうでなければ、21行目以降に転記していきます。
ここで、21と固定してしまうと、3つ以上の項目に対応できません。
そのため、nという変数をつかっています。

EX-IT_59

プログラムの最初のほうで、nという変数を21と設定します。
(設定しないと、0になります)
複数の項目がある場合、21行目に貼り付けるからです。

項目、内容、金額を転記した後、

n=n+1
で、21に1加えて22にします。
さらに項目がある場合は、22行目、23行目‥‥とずらして転記できるのです。

ただ、次の取引先になったときは、いったん、データをクリアし、貼り付け位置も21にリセットします。
F8キーで1行ずつ実行してみていただければ。

項目が2つならこうなりますし、

EX-IT_56

3つならこうなります。

EX-IT_57

マクロが入っていないテンプレートExcelファイルはこちらです。

EX-ITサンプル 請求書マクロテンプレート.xlsx


【編集後記】

昨日は、午前中に『十人十色の「ひとり税理士」という生き方(井ノ上版)』セミナーを開催。
参加者の方とランチし、午後は、値付けセミナーを開催し、懇親会。
終日話す、楽しい日でした。

『十人十色の「ひとり税理士」という生き方(井ノ上版)』セミナー音声は、メルマガ「税理士進化論」にて行っているキャンペーン特典です。
(キャンペーン終了後は販売予定)

値付けセミナーは、販売開始しました。
音声 値付け 8つのポイント – 株式会社タイムコンサルティング

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

『十人十色の「ひとり税理士」という生き方』 セミナー開催
値付けセミナー開催
ボナンザ 肉バル

【昨日の娘日記】
昨日、出かけようとするとギャン泣き。
よくよく考えると毎日一緒に出かけているので、「あれ?」と思われたのでしょう。
6月は出張が多いので、心苦しいです。


■スポンサードリンク
■井ノ上陽一のプロフィール
■スポンサードリンク



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