官公庁で求められる3点セット伝票は、市販の売上管理ソフトでもうまく作れません。
ただし販売王のデータ出力を上手く使えばエクセルでチョチョイと簡単に作成できるんです。
VBAコードを紹介します。
今回は第6回目の「販売帳票（見積書、納品書、請求書）を作成するエクセルソフト」についての記事になります。
こんにちは、じゅんぱ店長（@junpa33）です。
毎日利用している売上管理ソフトに入力した、日々販売データとは関係なく、
3点セット帳票を作るためには、同じ販売データを、エクセルにもまた打ち込まなければならない。
これは誰がどう考えても、時間の利用効率が悪すぎると言わざるを得ないでしょう。
では、
既に売上管理ソフトに入力した日々データを出力して、この3点セット販売帳票を作成することはできないでしょうか。
悩める君
記事内容に進む前に、前回の記事を確認されるにはこちら↓になります。
実用に耐えるユーザーフォーム作成のコツは具体的実例を教科書に
見積書・納品書・請求書作成に関する記事はこちらです。
伝票の作成でデータのやり取りをするソリマチ販売王に関する記事はこちらです。
コンテンツ
売上管理ソフトでは作れない帳票がある
商品販売に関する業務の効率を上げる目的で、売上販売ソフトを利用するのは、皆さん行っている方法だと思います。
これは、ソフトの入力手順に従ってデータを打ち込めば、自動的に帳票化してくれるという便利なソフトです。
当店では長年「販売王」を利用しています。
この記事をお読みの多くの方も、売上管理ソフトをお使いになっておられるでしょう。とにかく便利ですよね。
けれども、痒いところに手が届かないこともあって、
「中には作れない帳票もある。」と、ご存じの方も多く居られるのではないでしょうか。
それはいわゆる「3点セット」と言われる帳票です。主に官公庁向けに提出する見積書・納品書・請求書のことです。
「そうそう、それは無理でしょ。ソフトでは！」と思われた人は実際の体験者で、
すでにあきらめて、自前でひたすら帳票作成されている方達かもしれません。
実際、普通の方法ではソフトで作るのはほぼ無理で、もうこれは自前で自炊で作るしかありません。
官公庁や公共機関に納品をする際には、提出伝票として、決済をしてもらうために、必要なのがこの「3点セット」です。
しかも、この見積書・納品書・請求書は、単に同一書式というだけでなく、
「税込金額で表示」とか「税別で最終合計で消費税加算」とか「セット品は1項目でとか、内容全て表示とか」
提出部署によっても、いろいろローカルルールさえもあります。
だから普通に、自前の手書きや、エクセルの手作りで、作るのは仕方ない。
と、
みなさん思っていませんか？
3点セット帳票作成に販売王のデータを使いたい
3点セットを作成するのに、何が大変かというと、
- 期間や年度契約の場合、月締めで作成するので、1か月内に納品した商品全てを一覧化して帳票作成する。計算ミスをしないため何度か検算することは必須。
- 受注が1案件単位の場合、帳票も１案件単位。分割納品が発生すれば、その時の分割納品のための（分割）納品書の添付が必要。
- 各伝票に書く品名は（当たり前ですが）完全一致なので、特に手書きの場合は、転記ミスに最大級の注意が必要。
- とにかく帳票作成に時間がかかる。作業手間がスゴイ。
実際は、売上管理ソフトに販売データを打ち込んで、計算集計し、分割納品書はこのソフトの納品書をプリントアウトして利用し、
締め時の3点セットは、お手製の伝票作成ということではないでしょうか。
でも、帳票作成作業はこんなにも大変なので、やはり出来るなら、何とかしたいと、
解決したいと、皆さんは思っているのではないでしょうか。
ズバリ！この記事の表題通り、解決策はあります。
当店では、これから話す方法を使っていて、この苦悩からずいぶん解放されました。
利用ツールとしては、エクセルと売上管理ソフトの「販売王」です。ご準備ください。
まさに「3点セット」をサクッと作成するためのもので、そのサクッとを更にアップする方法がこの記事の内容になります。
出力したデータを選択してエクセルへ自動導入するには
簡単に言えば、問題の解決法は、売上管理ソフトの販売王に日々入力をしている販売データをソフトから取り出し、
エクセルにデータとして入力できればいいということになります。
その様に出来れば、データの再度の打ち直しはしなくてよくなるということです。
先回までの記事で、「販売帳票（見積書、納品書、請求書）を作成するエクセルソフト」の作り方を解説してきましたが、
そこでのエクセルVBAコードに、これから説明していくコードを付け加えていただければＯＫということです。
販売王の売り上げデータを出力する方法
最初に販売王のデータ出力を行います。
CSV形式で出力されますが、保存先フォルダを決めておきましょう。
今回はこのように配置します。保存先を「販売ソフト出力」フォルダとします。
| 「見納請3点伝票」フォルダ → | → 「販売ソフト出力」フォルダ |
| → 見納請3点伝票作成.xlsm |
販売王の売上伝票のデータ出力の方法については、こちら↓が参考になります。
無料で時短！販売王のデータ入力・出力機能で伝票作成をカスタマイズ
出力した「生の売上データ」は、期間中の全顧客対象で、その１商品1顧客当たりのデータ列が55列（販売王19）になります。
月当たりで絞めれば何百行（何千行）にもなります。つまりこのままでは非常に扱い辛いデータになっています。
保存した出力データファイルを選択するVBAコード
先ず、保存フォルダから、使用する売上データファイルを選んで開くためのVBAコードです。
見納請3点伝票作成.xlsm を開いて新たにモジュールを挿入します。
Module6に記述してください。
コードの全体はこのようになります。
Sub データを開く()
Dim FD As FileDialog
Set FD = Application.FileDialog(msoFileDialogOpen)
With FD
.ButtonName = “開く”
With .Filters
.Clear
.Add “テキストファイル”, “*.txt; *.csv”, 1
.Add “Excelブック”, “*.xls; *.xlsx; *.xlsm”, 2
End With
‘表示するフォルダを指定します
.InitialFileName = ThisWorkbook.Path & “\販売ソフト出力\”
MsgBox “利用できるのは、” & vbCrLf & “csv形式またはエクセル形式のファイルになります。”
If .Show = True Then
If Right(.SelectedItems(1), 3) = “txt” Then
MsgBox “txt形式では利用できません。”
Exit Sub
Else
.Execute
End If
Else
‘[キャンセル]ボタンがクリックされた
MsgBox “キャンセルが押されました”
Canc = 1
Exit Sub
End If
End With
End Sub
このプロシージャーが起動すると、
先ず、利用可能ファイルのメッセージが表示されます。
次に、「販売ソフト出力」フォルダ内のファイル選択ダイアログが表示されます。
.txt ファイルを選んだ場合は、このメッセージが表示されます。再度最初からやり直してください。
キャンセルボタンを押した場合は、メッセージが表示されて、このプロシージャーは終了します。
この時、キャンセルされたという情報を入れ子の親のプロシージャーに送るために、
Cancという変数を設定して「１」という数字を代入します。
.csvファイルを選択すると、エクセル形式でファイルが開かれます。
エクセルソフトの「作業シート」に売上データを転送するVBAコード
この開いた売上データのCSVファイルから、必要なデータを選択し、
「販売帳票（見積書、納品書、請求書）を作成するエクセルソフト」の「作業シート」に転記します。
VBAコード全体は、このようになります。
Dim Canc As Long
Sub データ転記()
Dim MNSBook As Workbook
Dim SaSheet As Worksheet
Set MNSBook = Workbooks(“見納請3点伝票作成.xlsm”)
Set SaSheet = Workbooks(“見納請3点伝票作成.xlsm”).Worksheets(“作業シート”)
Dim CosNo As Long
Dim csvRow As Long
Dim Res(1 To 6) As Variant
Dim SaRow As Long
Dim CsvBook As Workbook
Dim CsvSheet As Worksheet
‘操作パネルのデータの表示形式を数値化して判断する
CosNo = Val(StrConv((操作パネル.顧客番号.Value), vbNarrow))
If CosNo = 0 Then
MsgBox “顧客番号を入力してください。” & vbCrLf & “全半角数字のみです。” & vbCrLf & _
”文字と数字の複合番号は使えません。”
Exit Sub
End If
‘入れ子のプロシージャーを呼び出す
Module6.データを開く
If Canc = 1 Then
MsgBox “データファイル選択エラーです。” & vbCrLf & “最初からやり直してください。”
Exit Sub
End If
‘アクティブブック、シートが売上データになっている
Set CsvBook = ActiveWorkbook
Set CsvSheet = ActiveSheet
‘以前の残りデータを消去する
SaSheet.Activate
Range(Range(“A2”), Range(“A2”).SpecialCells(xlCellTypeLastCell)).ClearContents
CsvBook.Activate
CsvSheet.Select
csvRow = Cells(Rows.Count, 1).End(xlUp).Row
‘データを転記する
k = 0
For x = 2 To csvRow
If Range(“K” & x).Value = CosNo Then
Res(1) = k + 1
Res(2) = Range(“AK” & x).Value
Res(3) = Range(“AU” & x).Value
Res(4) = “冊”
Res(5) = Range(“AW” & x).Value
Res(6) = Res(3) * Res(5)
For j = 1 To 6
SaSheet.Cells(k + 2, j) = Res(j)
Next j
k = k + 1
End If
Next x
CsvBook.Close False
‘合計計算をする
SaSheet.Select
SaRow = Cells(Rows.Count, 1).End(xlUp).Row
GS = WorksheetFunction.Sum(Range(Cells(2, 6), Cells(SaRow, 6)))
Cells(SaRow + 1, 6) = GS
End Sub
操作パネルのデータの表示形式を数値化して判断する
ユーザーフォーム「操作パネル」の顧客番号欄に入力された値を判断する。
入力された値をStrConvで全角を半角に変換します。
Valで文字列を数値に変換します。
０以外の半角か全角の数値が入力されていないと、メッセージが表示され終了します。
入れ子のプロシージャーを呼び出す
先ほどの「データを開く」プロシージャーを呼び出します。
一つ注意点があって、
「販売ソフト出力」フォルダ内のファイル選択ダイアログで「キャンセルボタン」が押されたとき、
このプロシージャーは、データファイルを開くことなく終了します。
そして、
入れ子の親プロシージャー「データ転記」に戻りますが、ここで、「開いたであろうファイル」を待ち受けていますので、
「ファイルが無い」ことでエラーストップになってしまいます。これを回避するために、
「Canc」という変数を「データを開く」から引き継いで、「=1」の時に親の「データ転記」プロシージャーが実行中止されるようにしています。
その時にメッセージで”データファイル選択エラーです。最初からやり直してください。”と表示されるようにしています。
アクティブブック、シートが売上データになっている
エクセル形式で開いた売上データがアクティブになっています。
この時、そのアクティブになったBOOKとシートに名前を付けます。
CsvBookとCsvSheetと名付けた後は、このBOOKからフォーカスが外れても、
このBOOK名やシート名で呼び出すことが出来るようになります。
以前の残りデータを消去する
先回に使った売上データが「作業シート」に残っていますので、まずそれを消去します。
データを転記する
For～Nextを使って、売上データ.csvからデータを拾ってきて、作業シートへ順番に転記していきます。
販売王から出力した販売データは、指定期間内の全顧客の全売上データになりますので、
先ず、必要な特定の顧客データだけ拾ってくる必要があります。（変数x）
該当のデータ55項目の中から必要なデータ6項目をピックアップします（変数ｊ）
それを順番に「作業シート」に移していくということです。
For～Nextの使い方については、この記事も参考にしてください。
エクセルVBA！For～Nextの入れ子ループを最速に理解する
一次元配列変数（動的配列）の使い方について詳しくはこちらです。
合計計算をする
転記したデータ、その商品の合計の最後の合計欄にトータル合計を計算し表示します。
プロシージャーをボタンで操作する
完成したプロシージャーを、ユーザーフォーム「操作パネル」に配置しているボタンとリンクさせます。
記事をご覧いただいている読者の皆さんの中には、「販売王」を利用していない方もおられます。
「操作パネル」に配置している「データ連携」というボタンですが、
「販売王」を利用するかしないかで、この機能のONとOFFが出来るようにします。
チェックボックスの「ソリマチ販売王連携」をチェックしたかしてないかで判断します。
VBAコードを記述するのは、この記事↓で紹介している部分になります。
実用に耐えるユーザーフォーム作成のコツは具体的実例を教科書に
ユーザーフォームモジュールで、次のコードを付け加えてください。
Private Sub データ連携する_Click()
If CheckBox1 = False Then Exit Sub
Module6.データ転記
End Sub
この2行のVBAコードでボタンとの接続完了です。カンタンです。
出力データで3点セット帳票作成のまとめ
これで、帳票を作りたい顧客に必要な売上データが、「作業シート」に移りました。
後は、「販売帳票（見積書、納品書、請求書）を作成するエクセルソフト」の通常操作で、
「3点セット」帳票が完成です。
この様に、「販売王」を使えばパソコン操作でもデータ入力作業をすることなく、
マウスクリックだけで「3点セット」を作成することが出来ます。
恐らく業務改善の時短効果は、「時間の単位」から「５分刻みの単位」になると思います。
じゅんぱ店長
営業ねこ氏
これで、オプション設定も含めて「販売帳票（見積書、納品書、請求書）を作成するエクセルソフト」は完成いたしました。
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAを独学で習得するためのポイントは？良書との出会いは重要
エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ５選と無料講座の上手な使い方。
次回は、全体を通してこのソフトの取扱説明を行いたいと思います。
次の記事に進むのはこちら↓からです。
販売の3点伝票を作成するエクセルソフトの操作方法を説明します
エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておくといい内容を纏めてみました。
今回の記事はここまでです。 最後までご覧いただき有難うございました。
