販売王のデータ出力で3点セット帳票をエクセルで簡単に作る

3tendenhaneyecatch

官公庁で求められる3点セット伝票は、市販の売上管理ソフトでもうまく作れません。

ただし販売王のデータ出力を上手く使えばエクセルでチョチョイと簡単に作成できるんです。

VBAコードを紹介します。

 

今回は第6回目の「販売帳票(見積書、納品書、請求書)を作成するエクセルソフト」についての記事になります。

こんにちは、じゅんぱ店長(@junpa33)です。

 

毎日利用している売上管理ソフトに入力した、日々販売データとは関係なく、

3点セット帳票を作るためには、同じ販売データを、エクセルにもまた打ち込まなければならない。

これは誰がどう考えても、時間の利用効率が悪すぎると言わざるを得ないでしょう。

では、

既に売上管理ソフトに入力した日々データを出力して、この3点セット販売帳票を作成することはできないでしょうか。

悩める君

何とかできませんか?店長

 

記事内容に進む前に、前回の記事を確認されるにはこちら↓になります。

実用に耐えるユーザーフォーム作成のコツは具体的実例を教科書に

 

見積書・納品書・請求書作成に関する記事はこちらです。

「見積書納品書請求書作成」の記事一覧を開く

伝票の作成でデータのやり取りをするソリマチ販売王に関する記事はこちらです。

「販売王」の記事一覧を開く

 

売上管理ソフトでは作れない帳票がある

 

商品販売に関する業務の効率を上げる目的で、売上販売ソフトを利用するのは、皆さん行っている方法だと思います。

これは、ソフトの入力手順に従ってデータを打ち込めば、自動的に帳票化してくれるという便利なソフトです。

当店では長年「販売王」を利用しています。

この記事をお読みの多くの方も、売上管理ソフトをお使いになっておられるでしょう。とにかく便利ですよね。

 

けれども、痒いところに手が届かないこともあって、

「中には作れない帳票もある。」と、ご存じの方も多く居られるのではないでしょうか。

それはいわゆる「3点セット」と言われる帳票です。主に官公庁向けに提出する見積書・納品書・請求書のことです。

 

「そうそう、それは無理でしょ。ソフトでは!」と思われた人は実際の体験者で、

すでにあきらめて、自前でひたすら帳票作成されている方達かもしれません。

実際、普通の方法ではソフトで作るのはほぼ無理で、もうこれは自前で自炊で作るしかありません。

 

官公庁や公共機関に納品をする際には、提出伝票として、決済をしてもらうために、必要なのがこの「3点セット」です。

しかも、この見積書・納品書・請求書は、単に同一書式というだけでなく、

「税込金額で表示」とか「税別で最終合計で消費税加算」とか「セット品は1項目でとか、内容全て表示とか」

提出部署によっても、いろいろローカルルールさえもあります。

 

結論?

だから普通に、自前の手書きや、エクセルの手作りで、作るのは仕方ない。

と、

みなさん思っていませんか?

 

3点セット帳票作成に販売王のデータを使いたい

 

3点セットを作成するのに、何が大変かというと、

  • 期間や年度契約の場合、月締めで作成するので、1か月内に納品した商品全てを一覧化して帳票作成する。計算ミスをしないため何度か検算することは必須。
  • 受注が1案件単位の場合、帳票も1案件単位。分割納品が発生すれば、その時の分割納品のための(分割)納品書の添付が必要。
  • 各伝票に書く品名は(当たり前ですが)完全一致なので、特に手書きの場合は、転記ミスに最大級の注意が必要。
  • とにかく帳票作成に時間がかかる。作業手間がスゴイ。

 

実際は、売上管理ソフトに販売データを打ち込んで、計算集計し、分割納品書はこのソフトの納品書をプリントアウトして利用し、

締め時の3点セットは、お手製の伝票作成ということではないでしょうか。

でも、帳票作成作業はこんなにも大変なので、やはり出来るなら、何とかしたいと、

解決したいと、皆さんは思っているのではないでしょうか。

 

ズバリ!この記事の表題通り、解決策はあります。

当店では、これから話す方法を使っていて、この苦悩からずいぶん解放されました。

利用ツールとしては、エクセルと売上管理ソフトの「販売王」です。ご準備ください。

 

 

MEMO
この記事の上位のテーマ「販売帳票(見積書、納品書、請求書)を作成するエクセルソフト」は、

まさに「3点セット」をサクッと作成するためのもので、そのサクッとを更にアップする方法がこの記事の内容になります。

 

出力したデータを選択してエクセルへ自動導入するには

 

簡単に言えば、問題の解決法は、売上管理ソフトの販売王に日々入力をしている販売データをソフトから取り出し、

エクセルにデータとして入力できればいいということになります。

その様に出来れば、データの再度の打ち直しはしなくてよくなるということです。

 

先回までの記事で、「販売帳票(見積書、納品書、請求書)を作成するエクセルソフト」の作り方を解説してきましたが、

そこでのエクセルVBAコードに、これから説明していくコードを付け加えていただければOKということです。

 

販売王の売り上げデータを出力する方法

 

最初に販売王のデータ出力を行います。

CSV形式で出力されますが、保存先フォルダを決めておきましょう。

今回はこのように配置します。保存先を「販売ソフト出力」フォルダとします。

「見納請3点伝票」フォルダ → → 「販売ソフト出力」フォルダ
→ 見納請3点伝票作成.xlsm

販売王の売上伝票のデータ出力の方法については、こちら↓が参考になります。

無料で時短!販売王のデータ入力・出力機能で伝票作成をカスタマイズ

出力した「生の売上データ」は、期間中の全顧客対象で、その1商品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

このプロシージャーが起動すると、

先ず、利用可能ファイルのメッセージが表示されます。

3tendenhanbai001

矢印下001

次に、「販売ソフト出力」フォルダ内のファイル選択ダイアログが表示されます。

3tendenhanbai002

矢印下001

.txt ファイルを選んだ場合は、このメッセージが表示されます。再度最初からやり直してください。

3tendenhanbai003

矢印下001

キャンセルボタンを押した場合は、メッセージが表示されて、このプロシージャーは終了します。

この時、キャンセルされたという情報を入れ子の親のプロシージャーに送るために、

Cancという変数を設定して「1」という数字を代入します。

3tendenhanbai004

矢印下001

.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で文字列を数値に変換します。

0以外の半角か全角の数値が入力されていないと、メッセージが表示され終了します。

 

MEMO

Val関数の使い方についてはこちらの記事を参考にしてください。

エクセルVBA Val関数の使い方「値を文字列型から数値型へ変換する」

 

入れ子のプロシージャーを呼び出す

先ほどの「データを開く」プロシージャーを呼び出します。

 

一つ注意点があって、

「販売ソフト出力」フォルダ内のファイル選択ダイアログで「キャンセルボタン」が押されたとき、

このプロシージャーは、データファイルを開くことなく終了します。

そして、

入れ子の親プロシージャー「データ転記」に戻りますが、ここで、「開いたであろうファイル」を待ち受けていますので、

「ファイルが無い」ことでエラーストップになってしまいます。これを回避するために、

「Canc」という変数を「データを開く」から引き継いで、「=1」の時に親の「データ転記」プロシージャーが実行中止されるようにしています。

その時にメッセージで”データファイル選択エラーです。最初からやり直してください。”と表示されるようにしています。

 

アクティブブック、シートが売上データになっている

エクセル形式で開いた売上データがアクティブになっています。

この時、そのアクティブになったBOOKとシートに名前を付けます。

CsvBookとCsvSheetと名付けた後は、このBOOKからフォーカスが外れても、

このBOOK名やシート名で呼び出すことが出来るようになります。

 

以前の残りデータを消去する

先回に使った売上データが「作業シート」に残っていますので、まずそれを消去します。

 

MEMO

入力セルの最終行を取得する方法はこちらを参考にしてください。

エクセルVBA データ入力済セルの最終行番号を取得する方法

 

データを転記する

For~Nextを使って、売上データ.csvからデータを拾ってきて、作業シートへ順番に転記していきます。

販売王から出力した販売データは、指定期間内の全顧客の全売上データになりますので、

先ず、必要な特定の顧客データだけ拾ってくる必要があります。(変数x)

該当のデータ55項目の中から必要なデータ6項目をピックアップします(変数j)

それを順番に「作業シート」に移していくということです。

 

MEMO

For~Nextの使い方については、この記事も参考にしてください。

エクセルVBA!For~Nextの入れ子ループを最速に理解する

一次元配列変数(動的配列)の使い方について詳しくはこちらです。

エクセルVBA 1次元配列変数の利用法を最速に理解

 

合計計算をする

転記したデータ、その商品の合計の最後の合計欄にトータル合計を計算し表示します。

 

MEMO

Sum関数の使い方についてはこちらの記事を参考にしてください。

エクセルVBA SUM関数で合計計算!実務で使えるコード作成

 

プロシージャーをボタンで操作する

 

完成したプロシージャーを、ユーザーフォーム「操作パネル」に配置しているボタンとリンクさせます。

記事をご覧いただいている読者の皆さんの中には、「販売王」を利用していない方もおられます。

 

「操作パネル」に配置している「データ連携」というボタンですが、

「販売王」を利用するかしないかで、この機能のONとOFFが出来るようにします。

チェックボックスの「ソリマチ販売王連携」をチェックしたかしてないかで判断します。

 

VBAコードを記述するのは、この記事↓で紹介している部分になります。

実用に耐えるユーザーフォーム作成のコツは具体的実例を教科書に

ユーザーフォームモジュールへのコード記述

 

ユーザーフォームモジュールで、次のコードを付け加えてください。

Private Sub データ連携する_Click()

 

  If CheckBox1 = False Then Exit Sub

  Module6.データ転記

 

End Sub

 

この2行のVBAコードでボタンとの接続完了です。カンタンです。

 

出力データで3点セット帳票作成のまとめ

 

これで、帳票を作りたい顧客に必要な売上データが、「作業シート」に移りました。

後は、「販売帳票(見積書、納品書、請求書)を作成するエクセルソフト」の通常操作で、

「3点セット」帳票が完成です。

この様に、「販売王」を使えばパソコン操作でもデータ入力作業をすることなく、

マウスクリックだけで「3点セット」を作成することが出来ます。

恐らく業務改善の時短効果は、「時間の単位」から「5分刻みの単位」になると思います。

ウソっぽく聞こえますが、ホントです。

じゅんぱ店長

ビックリするヨ

営業ねこ氏

 

これで、オプション設定も含めて「販売帳票(見積書、納品書、請求書)を作成するエクセルソフト」は完成いたしました。

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAを独学で習得するためのポイントは?良書との出会いは重要

 

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

次回は、全体を通してこのソフトの取扱説明を行いたいと思います。

次の記事に進むのはこちら↓からです。

販売の3点伝票を作成するエクセルソフトの操作方法を説明します

 

エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておくといい内容を纏めてみました。

「VBA最速理解」の記事一覧を開く

 

今回の記事はここまでです。   最後までご覧いただき有難うございました。

 

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません