エクセルVBAを使って伝票作成の効率をアップさせる方法【サンプルVBA付】
豆父ちゃん
こんな要望にお答えします。
結論からいうと、エクセルファイルに転記VBAを導入する！です。
転記VBAやページの設定など、効率よく伝票を管理することができるエクセルファイルの作り方を紹介します。作成した転記VBAを使った伝票作成エクセルがどのようなものか下記の動画で紹介しています。
この記事の最後では、伝票作成VBAの比較として伝票作成専用ソフト【Misoca】の紹介をしています。エクセルを使った伝票作成と専用ソフトを使った伝票作成で迷っている方は合わせてチェックしてみてください。
目次
ページ設定
VBA作成の前にページの設定をやっておくことが重要です。
様式のページ設定は今後変更が発生しないよう作り込みましょう。
一覧表シート
伝票シート
設定シート
ページ設定のポイント
- セルの結合を確認
- 余白を確認
- 様式に間違いがないか確認
伝票の様式ができたら印刷プレビューで問題がないか確認してください。
もしVBAを作ったあとに様式の間違いがあれば、VBAを1から作り直さなければならないことがあります。様式のページ設定は慎重に行いましょう。
転記VBAの作り方
会社名と作成日で伝票を作成する転記VBA
サンプルVBA
| 1 | Sub 伝票転記() |
| 2 | "’Sheetの設定"’ |
| 3 | Dim Ash As Worksheet |
| 4 | Dim Bsh As Worksheet |
| 5 | Set Ash = ThisWorkbook.Worksheets(“一覧表") |
| 6 | Set Bsh = ThisWorkbook.Worksheets(“伝票") |
| 7 | "’ユーザーフォームで入力した値を変数i,jで取得"’ |
| 8 | Dim i As Date |
| 9 | i = Bsh.Cells(6, 36) |
| 10 | j = Bsh.Cells(4, 36) |
| 11 | "’伝票の値をリセットする"’ |
| 12 | gyoa = Ash.Cells(Rows.Count, 1).End(xlUp).Row |
| 13 | gyob = Bsh.Cells(Rows.Count, 2).End(xlUp).Row |
| 14 | Bsh.Range(Cells(6, 1), Cells(6, 10)).ClearContents |
| 15 | Bsh.Range(Cells(10, 2), Cells(gyob + 1, 10)).ClearContents |
| 16 | Bsh.Range(Cells(10, 11), Cells(gyob + 1, 15)).ClearContents |
| 17 | Bsh.Range(Cells(10, 16), Cells(gyob + 1, 19)).ClearContents |
| 18 | Bsh.Range(Cells(10, 20), Cells(gyob + 1, 23)).ClearContents |
| 19 | Bsh.Range(Cells(10, 24), Cells(gyob + 1, 27)).ClearContents |
| 20 | Bsh.Range(Cells(10, 28), Cells(gyob + 1, 33)).ClearContents |
| 21 | Bsh.Range(Cells(26, 22), Cells(26, 24)).ClearContents |
| 22 | Bsh.Range(Cells(26, 14), Cells(26, 19)).ClearContents |
| 23 | Bsh.Range(Cells(26, 28), Cells(26, 33)).ClearContents |
| 24 | "’一覧表から伝票へ値を転記する"’ |
| 25 | For k = 3 To gyoa |
| 26 | gyob = Bsh.Cells(Rows.Count, 2).End(xlUp).Row |
| 27 | If Ash.Cells(k, 1) = i And Ash.Cells(k, 2) = j Then |
| 28 | Bsh.Cells(6, 1) = j |
| 29 | Bsh.Cells(gyob + 1, 2) = Ash.Cells(k, 3) |
| 30 | Bsh.Cells(gyob + 1, 11) = Ash.Cells(k, 4) |
| 31 | Bsh.Cells(gyob + 1, 16) = Ash.Cells(k, 5) |
| 32 | Bsh.Cells(gyob + 1, 20) = Ash.Cells(k, 6) |
| 33 | Bsh.Cells(gyob + 1, 24) = Ash.Cells(k, 7) |
| 34 | Bsh.Cells(gyob + 1, 28) = Ash.Cells(k, 8) |
| 35 | Else |
| 36 | End If |
| 37 | Next |
| 38 | Bsh.Cells(26, 22) = Ash.Cells(3, 9) |
| 39 | Bsh.Cells(26, 14) = Application.WorksheetFunction.Sum(Range(Cells(10, 24), Cells(gyob, 24))) |
| 40 | Bsh.Cells(26, 28) = Bsh.Cells(26, 14) * (Bsh.Cells(26, 22) / 100 + 1) |
| 41 | End Sub |
実行結果
会社名だけで伝票を作成する転記VBA
サンプルVBA
| 1～8 | 会社名と作成日で伝票を作成する転記VBAと同じ |
| 9 | 削除 |
| 10～26 | 会社名と作成日で伝票を作成する転記VBAと同じ |
| 27 | If Ash.Cells(k, 2) = j Then |
| 28～41 | 会社名と作成日で伝票を作成する転記VBAと同じ |
会社名と作成日で伝票を作成する転記VBAとほぼ同じですが、9行目と27行目を修正するだけで会社名だけで伝票を作成するVBAにすることができます。
9行目と27行目は関数IFに関係している箇所です。関数IFで条件を変化させることで業務に合った伝票を作成するVBAにすることが出来ます。
本的なVBAの組み合わせで転記VBAは成り立っています。転記VBAで使われているものの詳しい使い方を紹介している記事のリンクを上記一覧につけています。合わせてチェックしてみてください。
私の職場では会社名だけで伝票を作成すると膨大な量のデータになってしまうので、会社名と作成日を使って伝票を作成しています。関数IFで条件を追加すればさらに出力条件を細かくすることができます。
転記VBAの実行方法
ユーザーフォームによる実行
私の職場ではユーザーフォームによる実行が最も使われています。伝票シート内の情報はは全て一覧表シートから転記されるので編集する必要がありません。そのためユーザーフォームでテキストボックスを設ければこの部分だけ編集すればいい！と一目でわかるので今回のような転記させるVBAにユーザーフォームによる実行はとても適しています。
サンプルVBA
| 1～8 | 会社名と作成日で伝票を作成する転記VBAと同じ |
| 9 | i = UserForm1.TextBox1.Value |
| 10 | j = UserForm1.ListBox1.Value |
| 11～26 | 会社名と作成日で伝票を作成する転記VBAと同じ |
| 27 | If Ash.Cells(k, 1) = i And Ash.Cells(k, 2) = j Then |
| 28～41 | 会社名と作成日で伝票を作成する転記VBAと同じ |
ユーザーフォームからテキストボックスとリストボックスの値を取得し、取得した値を変数として使用しています。
リストボックスでは設定シートから会社名を取得し表示する！というVBAにしています。
リストボックスを一覧表から取得するサンプルVBA
| 1 | Private Sub UserForm_Initialize() |
| 2 | Dim Ash As Worksheet |
| 3 | Dim Bsh As Worksheet |
| 4 | Dim Csh As Worksheet |
| 5 | Set Ash = Sheets(“一覧表") |
| 6 | Set Bsh = Sheets(“伝票") |
| 7 | Set Csh = Sheets(“設定") |
| 8 | Dim re As Long |
| 9 | Dim Li As String |
| 10 | gyo = Csh.Cells(Rows.Count, 1).End(xlUp).Row |
| 11 | For i = 2 To gyo |
| 12 | Li = Csh.Cells(i, 1).Value |
| 13 | ListBox1.AddItem Li |
| 14 | Next |
| 15 | End Sub |
実行結果
セルのクリックによる実行
ユーザーフォームが邪魔くさい！という場合におすすめがセルのクリックによるVBAの実行です。
サンプルVBA
| 1 | Private Sub WorkSheet_SelectionChange(ByVal Target As Range) |
| 2 | Dim Ash As Worksheet |
| 3 | Dim Bsh As Worksheet |
| 4 | Dim Csh As Worksheet |
| 5 | Set Ash = ThisWorkbook.Worksheets(“一覧表") |
| 6 | Set Bsh = ThisWorkbook.Worksheets(“伝票") |
| 7 | Set Csh = ThisWorkbook.Worksheets(“設定") |
| 8 | "’ドロップダウンリストのリセット"’ |
| 9 | ActiveSheet.Range(“AJ4").Validation.Delete |
| 10 | ActiveSheet.Range(“AJ11").Validation.Delete |
| 11 | On Error Resume Next |
| 12 | gyoc = Csh.Cells(Rows.Count, 1).End(xlUp).Row |
| 13 | For i = 2 To gyoc |
| 14 | If (Target.Column = 36) And (Target.Row = 4) Then |
| 15 | risto = WorksheetFunction.Transpose(Csh.Range(Csh.Cells(2, 1), Csh.Cells(gyoc, 1))) |
| 16 | With Target.Validation |
| 17 | .Add Type:=xlValidateList, Formula1:=Join(risto, “,") |
| 18 | .ShowError = False |
| 19 | End With |
| 20 | ElseIf (Target.Column = 36) And (Target.Row = 11) Then |
| 21 | risto = WorksheetFunction.Transpose(Csh.Range(Csh.Cells(2, 1), Csh.Cells(gyoc, 1))) |
| 22 | With Target.Validation |
| 23 | .Add Type:=xlValidateList, Formula1:=Join(risto, “,") |
| 24 | .ShowError = False |
| 25 | End With |
| 26 | End If |
| 27 | Next |
| 28 | If Err <> 0 Then |
| 29 | Err.Clear |
| 30 | End If |
| 31 | If (Target.Column = 36) And (Target.Row = 7) Then |
| 32 | Call 伝票転記 |
| 33 | ElseIf (Target.Column = 36) And (Target.Row = 12) Then |
| 34 | Call 伝票転記2 |
| 35 | End If |
| 36 | End Sub |
実行結果
Targetを使ってVBAの実行を行います。
サンプルVBAでTargetを使用している箇所
- 14～19行目で｢36列目、4行目のセルをクリックしたときプルダウンリストを表示する｣
- 20～25行目で｢36列目、11行目のセルをクリックしたときプルダウンリストを表示する｣
- 31～32行目で｢36列目、7行目のセルをクリックしたとき転記VBAを実行する｣
- 33～34行目で｢36列目、12行目のセルをクリックしたとき転記VBAを実行する｣
Targetはとても便利な機能でプルダウンリストを表示するVBAによく使われます。
会社名の入力にはプルダウンリストから選択する！という方法が便利です。今回の転記VBAでは関数IFを使って会社名で検索をする！という方法です。そのため会社名を誤入力してしまったらVBAが動作しません。
プルダウンリストから会社名を選択する方法では誤入力によるVBAが動作しない！という心配がありません。
実行結果
設定シートの会社名からプルダウンリスト作成しています。リストの内容が変化するとプルダウンリストの内容も自動的に変化するので、大勢の人が扱うエクセルファイルにおすすめです。
大勢の人が扱うエクセルファイルでは、誰かが設定シートの会社名に新しく契約した会社名をいつの間にか追加してプルダウンリストが全て表示されていない！という事象がときどき発生していました。
下記の記事ではプルダウンリストの内容を一覧表から取得する方法について詳しく紹介しています。
このプルダウンリストの内容が変化するVBAを導入すれば、プルダウンリストに関するトラブルが激減します。
伝票作成の専用ソフトの導入
伝票作成は失敗の許されない作業です。日付や氏名などの編集忘れが発生してしまっては会社の信用問題に繋がってしまいます。特に請求書や領収書の金額を間違ってしまうと、相手先との信頼関係が壊れてしまうなんてことにも。。
転記ミスや入力ミスを防ぐためにエクセルVBAを使って自動化しても不安！と感じる方が多いのではないでしょうか。そんな方には、伝票作成の専用ソフトの導入がおすすめです。
専用ソフトの導入にはお金がかかるため、上司や会社に了承を得なければなりません。私の会社では、専用ソフトで伝票作成をしている職場もあれば、エクセルを使って自分で伝票作成している職場が混在していました。職場によって仕事量が変わるので専用ソフトの導入が認められる職場と認められない職場があっても仕方のないことです。
両方の環境で仕事をしていた私が、とても使いやすい！と感じた伝票作成ソフトを紹介します。
伝票作成ソフト【Misoca】
【MISOCA】とは、契約書や請求書などの書類作成を代行してくれるソフト＋作成依頼した公式書類の扱い方や効力などの説明もしてくれるサービスです。ただ書類を作って終わりではなく、他にいろんなサービスがあるのが魅力的です。
- 作成した書類の郵送、メール送信
- 請求書→領収書などに簡単に変換
- 安心のセキュリティ
- データのバックアップ
【Misoca】を利用してみて、入力項目がパッとみてわかるので記入漏れのミスが発生しにくい！請求書から領収書、見積書から請求書の変換がとても簡単！という印象でした。
エクセルで伝票を作成すると記入漏れや転記ミスが発生していたのですが、【Misoca】が導入されている職場ではそんなヒューマンエラーはほとんど発生していませんでした。伝票作成の業務を簡単に処理して、他の業務に専念できる！というメリットも生まれていました。
14種類ものデザインから請求書や領収書を作成できるので、いろんな環境にも対応できるかと思います。【Misoca】には、気軽に始められる無料プランからサポートが充実したプランが、3つ用意されています。気になるという方はまずは無料プランから始めてみましょう。
会計ソフト【マネーフォワードのクラウド請求書】
【マネーフォワードのクラウド請求書】は、見積書 ・納品書・請求書・領収書を簡単に作成することができます。
伝票作成画面では、左側で編集作業を行いながら右側でプレビューを確認できる仕様になっています。編集しながら出来上がりを確認できるのでとても使いやすく、編集不足によるミスプリントが発生しにくくなります。さらに、一度利用した品目の金額を登録することができる機能や毎月の伝票作成を予約する機能を使えば作業の効率を格段にアップさせることができます。
- 作成した書類の郵送、メール送信
- 請求書→領収書などに簡単に変換
- 安心のセキュリティ
- データのバックアップ
【マネーフォワードのクラウド請求書】と【マネーフォワードのクラウド確定申告】
と連携させると、作成した伝票の仕訳処理などを帳簿に自動で反映することができます。伝票作成の他にも帳簿作成や確定申告などの会計業務も行わなければならない！という方におすすめです。
2つの伝票作成専用ソフトについて紹介してきましたが、エクセルよりも専用ソフトがいい！というわけではありません。エクセルを使って伝票書類を作成するメリットもあります。
- 様式の変更に柔軟に対応できる
- 無料で作成できる
- すぐに行動できる
あなたの職場環境に合った方法を採用しましょう。
私の職場では、スピードを求められる書類はエクセルVBAで処理し、重要で正確さが求められる書類は【Misoca】を利用しています。
下記の記事で、伝票作成専用ソフト【Misoca】についてもっと詳しく紹介しています。気になる方は是非チェックしてみてください。
