2022/12/4 T. Hirano
学会の研究会の幹事などをすると会計伝票（見積書・納品書・請求書・領収書など）の発行作業が大変なことがあります。特に、10人程度ならはフォーマットに名前などをコピペ（コピー＆ペースト）してもあまり時間がかかりませんが、20人を超えるようになるとコピペしてPDFファイル印刷するだけでも多くの時間がかかります。このページでは、Excelの数式とマクロを用いて、Googleフォームなどで収集した申込者のデータからボタン１つで会計伝票を生成する方法について説明します。
次のzipファイルをダウンロードして展開するとExcelのマクロ有効ファイル"accounting slip.xlsm"が入っています。
Download: accounting slip.zip (50KB)
使い方は下記を参考にしてください。
方法は簡単で、ExcelシートにGoogleフォームなどで収集した登録者情報をコピー＆ペーストするだけで使うことができます。
「データ」シートの出力No.(C1セル)にあるデータを「見積書」、「納品書」、「請求書」、「領収書」に自動転記して伝票を生成します。各伝票は各シートに完成しています。そして、各伝票の右にあるPDF出力ボタンを押すとこのXLSMファイルがあるフォルダに各伝票（シート名+"_"+伝票No."+".pdf"）のPDFファイルが生成されます。
「見積書」、「納品書」、「請求書」、「領収書」のデザインはそれぞれのシートで直接編集してください。
「データ」シートの氏名、所属、伝票No.は伝票に転記される必要項目です。
「データ」シートのデータはGoogleフォームなどで収集したデータをxlsxファイルにエクスポートし、コピー＆ペーストすることで簡単に生成することができます。
※たまに、宛名や品名がイレギュラーな人がいますが、その場合はファイルを上書きしないように、一度コピーして「見積書」シートの宛名・品名などを手書きで書き換えて出力するのをおすすめします。
「データ」シート
「見積書」シート
生成された「見積書」の例: 見積書_KEN2022-02.pdf
「納品書」シート
生成された「納品書」の例: 納品書_KEN2022-02.pdf
「請求書」シート
生成された「請求書」の例: 請求書_KEN2022-02.pdf
「領収書」シート
生成された「領収書」の例: 領収書_KEN2022-02.pdf
方法は簡単で、ExcelシートにGoogleフォームなどで収集した登録者情報をコピー＆ペーストするだけで使うことができる。
このシステムはExcelのマクロで記述されています。
「データ」シートの出力No.(C1セル)にあるデータを「見積書」、「納品書」、「請求書」、「領収書」のシートに自動転記されるようになっています。自動転記の方法はOFFSET関数とC1セルの値を使って参照しています。
「見積書」、「納品書」、「請求書」、「領収書」のシートにはPDF出力ボタンがあります。ボタンをクリックするとExcelマクロが呼び出されるようになっています。
Excelマクロを使うには、初回のみ開発機能を有効にする必要があります。リボン（ツールバー）に「開発」を表示させるには、「ファイル」→「オプション」→「リボンのユーザー設定」からメインタブの「開発」にチェックを入れる必要があります。
Exelマクロの記録はリボンの「開発」→「マクロの記録」を押して開始し、記録したい操作を行い、「記録終了」を押すとマクロにコードが記録されます。記録されたコードは「開発」→「マクロ」から再編集することも可能です。（参考: https://www.microsoft.com/ja-jp/biz/smb/column-excel-macro.aspx ）
あとは、「開発」→「挿入」→「コントロール」で挿入したボタンをクリックしたらExcelマクロを呼び出すように設定しているだけです。
PDFファイル出力のマクロは次のようになります。
| Sub PDF出力_ファイル名伝票番号入() ' ' 見積書発行 Macro ' ' (シート名_"伝票No.".pdf)のPDFファイルを出力する。 ' 下のコードの"B4"はB列、が伝票番号の列を、4は最初のデータの前の行を意味する。 Dim fname As String Dim sid As String Dim r As Integer r = Worksheets("データ").Range("C1").Value sid = Worksheets("データ").Range("B4").Offset(r, 0).Value fname = ThisWorkbook.Path & "\" & ActiveSheet.Name & "_" & sid & ".pdf" ChDir ThisWorkbook.Path ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, fileName:=fname, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False End Sub |
以上、業務効率化にお役立てください。