取次の送品予定表を、使える資料に編集して保存するVBA

souyosakueyecatcha

書店の業務のなかで、取次の雑誌送品予定表を自店で加工し、使い易く編集することで、日々の業務効率を上げることが出来ます。取り置き・配達・引き上げ作業に活用できます。

どうもじゅんぱ店長(@junpa33)です。

取次から提供される資料の中で、雑誌と書籍の発売予定表(送品予定表)があります。

ここで言うデータは、コミック「何月何日に発売予定です。」とかの発売データのことではなく、

明日あさっての着荷となる店着雑誌・書籍の送品予定のデータ資料のことです。

現状多くのものはデフォルトのままでは、ほぼ単なるデータの羅列で、あまり利用者サイドの資料とは言えません。(取次にもよるとは思いますが・・・)

この記事は、このデータ資料を自店にとってより利用価値のあるデータ表に加工し、業務の効率化のために利用しましょうということです。

それでは早速、その「送品予定表」をエクセルVBAで作成していきたいと思います。

雑誌の「送品予定表」を仕上げていきます。

送品予定表の3つのシート機能の設定

souyosaku012kai

作業的には3つのシートを使って、この様な内容で作業を行っていきます。

  1. 新規のエクセルブックを立ち上げて、3枚のシートのシート名を「Paste」シート、「設定操作」シート「送品予定表」シートと名前を変更をします。
  2. 「設定操作」シートは、組み上げたVBAを起動させる”操作ボタン”を設置する場所、送品予定表を適切に作成するための初期設定を入力する入力表を設置する場所とします。
  3. 「送品予定表」シートは、仕上がった一覧表を表示するシートになります。
  4. 「Paste」シートは、取次から発信されてくる生のデータ(CSV)を貼り付けるシートとします。

「設定操作」シートが、実際の操作のコントロールセンター的部分となります。

実務的な送品予定表の作成

souyosaku013kai

4つの項目を順に行っていきます。

①3つのシートの名称変更

エクセルを起動して白紙のエクセルBOOKを開いてください。

「Sheet1」を「Paste」

「Sheet2」を「設定操作」

「Sheet3」を「送品予定表」

名称変更してください。

変更方法は何度か行っていますので、お分かりかと思いますが、

念のため、変更方法はこちらで確認できます。↓

「まずは、Sheet(シート)名の変更」

こんな感じになります。

souyosaku001aa

そしてこのBOOKに名前を付けます。

一度、「名前を付けて保存」で保存してください。

「送品予定表作成.xlsm」と名付けしてください。

「Paste」シートは、白紙のままでOKです。

②「設定操作」シートのデザイン

送品データを加工するためには、最初にユーザーからの情報を入手する必要があります。まず最初に、「設定操作」シートのユーザーの情報入力のセル位置を確定させておきます。

  •  送品予定表の作表をするVBAのコントロールボタンを「設定操作」シートに設置します。
  •  送品予定表を適切に作成するための利用者側の初期設定値を入力する入力表を設置します。

表示データ選択リスト

「Paste」シートに貼り付けたデータの必要部分を取り出す設定をします。

「設定操作」シート上にまず、必要なデータ列を指定する入力欄を作ります。

表示する項目の設定とそのデータ列の列名を指定します。

VBAでデータ値を収集しますので、入力表の表示位置に注意してください。

データ入力セルを”J4からK13”の番地に必ずセットしてください。

(J列のデータ項目順は固定です。)

表の体裁については、お好みでアレンジしてください。

例えばこの様になります。

souyosaku005a

見出し行、消費税率確認欄

Pasteシートに貼り付ける送品データは先頭行が「項目か?データか?」を、確認する必要があります。(項目行は利用しないので作表時にこれを表示しないようにします。)

”NOなら「0」” そして ”YESなら「1」”を入力します。)

消費税を計算しますので、税率を入力する欄を作ります。

この場合もこのシートから設定値を取り出しますので、入力セル位置を間違わないようにしてください。ここは作表時のデータ加工の肝の部分になります。

ここでの完成形はこのようになります。

souyosaku007a

上の入力表内の数値は”例えばの値”です。実際の使用では適宜変更してください。

マクロを実行するコマンドボタンの設置

最後に操作ボタンを「設定操作」シートに設置します。

ボタンとプロシージャーの連結関係
  • 送品表作成ボタン・・・・⇒「送品表作成」プロシージャー
  • 送品表バックアップ・・・⇒「送品表BUP」プロシージャー
  • 送品表クリアー・・・・・⇒「送品表クリアー」プロシージャー
  • Pasteシートクリアー⇒「Pasteシートクリアー」プロシージャー

souyosaku008a

ボタンの設置についてはこちらの記事を参考にしてください。

ボタンとプロシージャーの連結は、最後(全コード記述後)に行います。

③「送品予定表」シート作成のVBAコード

  • 送品予定表の項目表示や列幅を整える
  • 設定操作シートの項目で指定した値を基に「Paste」シート上のデータを「送品予定表」シートに移します。
  • 消費税を加味した「税込価格」を計算して表示する設定をします。計算は四捨五入の計算方法です。

などのVBAコードを組み上げます。

Moduleを挿入してください。(Module1) 挿入方法の確認はこちら

変数宣言部

コードはこのようになります。

VBA
Option Explicit

Sub 送品表作成()
    Dim a As Integer
    Dim Prow As Long
    Dim t, x As Long
    Dim Va As String
    Dim Tax As Long

見出し項目設定、セル幅設定

項目は、このように設定します。(項目名変更は後でも可能です)

必要な項目を表示する列の幅を整えます。

  • NO.
  • 送品日付
  • 陳列(雑誌ジャンル)
  • 雑誌コード
  • 号数
  • 日付
  • 出版社
  • 雑誌名
  • 配本冊数
  • 本体価格
  • 消費税
souyosaku004a
VBA
'コード①
        Worksheets("送品予定表").Activate
        With Worksheets("送品予定表")
            .Range("A:L").Font.Size = 10
            With .Range("A1:L1")
                .WrapText = True
                .Font.Bold = True
            End With
            With .Range("A1")
                .RowHeight = 21
                .Value = "No."
                .ColumnWidth = 4
            End With
            With .Range("B1")
                .Value = Worksheets("設定操作").Range("J4")
                .ColumnWidth = 8
            End With
            With .Range("C1")
                .Value = Worksheets("設定操作").Range("J5")
                .ColumnWidth = 9
            End With
            With .Range("D1")
                .Value = Worksheets("設定操作").Range("J6")
                .ColumnWidth = 8
            End With
            With Range("E1")
                .Value = Worksheets("設定操作").Range("J7")
                .ColumnWidth = 4
            End With
            With .Range("F1")
                .Value = Worksheets("設定操作").Range("J8")
                .ColumnWidth = 4
            End With
            With .Range("G1")
                .Value = Worksheets("設定操作").Range("J9")
                .ColumnWidth = 3
            End With
            With .Range("H1")
                .Value = Worksheets("設定操作").Range("J10")
                .ColumnWidth = 11
            End With
            With .Range("I1")
                .Value = Worksheets("設定操作").Range("J11")
                .ColumnWidth = 21
            End With
            With .Range("J1")
                .Value = Worksheets("設定操作").Range("J12")
                .ColumnWidth = 8
            End With
            With .Range("K1")
                .Value = Worksheets("設定操作").Range("J13")
                .ColumnWidth = 6
            End With
            With .Range("L1")
                .Value = "消費税"
                .ColumnWidth = 6
            End With
        End With

シート間のデータのコピーと貼り付け

VBA
'コード②
        With Worksheets("設定操作")
            a = .Range("I5").Value
            Tax = .Range("G9").Value
        End With
        Prow = Worksheets("Paste").Cells(Rows.Count, 1).End(xlUp).Row
        For t = 0 To 9
            Va = Worksheets("設定操作").Range("K" & 4 + t)
            Worksheets("Paste").Select
            Range(Cells(1 + a, Va), Cells(Prow, Va)).Copy _
                    Destination:=Worksheets("送品予定表").Cells(2, 2 + t)
        Next t
        For x = 2 To Prow
        With Worksheets("送品予定表")
            .Range("A" & x) = x - 1
            .Range("L" & x).Value = Application.Round( _
                            .Range("K" & x).Value * (1 + Tax / 100), 0)
        End With
        Next x
        
        Application.CutCopyMode = False

印刷設定

VBA
'コード③
        Worksheets("送品予定表").Activate
        With Worksheets("送品予定表")
            .Range("A:L").Font.Size = 10
            With .Range("J:J")
                .Font.Size = 9
                .HorizontalAlignment = xlRight
            End With
            With .PageSetup
                .PrintTitleRows = "$1:$1"
                .PrintTitleColumns = ""
                .PrintArea = "A1:L" & Prow
                .CenterHeader = "雑誌新刊送品予定表"
                .RightHeader = "☆マークは配達候補、■マークは取置候補"
            End With
            With .Range(Cells(1, 1), Cells(Prow, 12))
                    With .Borders
                        .LineStyle = xlContinuous
                        .Weight = xlHairline
                    End With
            End With
            .Range("A1").Select
        End With
        Worksheets("Paste").Select
        Range("A1").Select
        Worksheets("設定操作").Select
        Range("E1").Select
        MsgBox "作成が終了しました。", vbInformation, "送品予定表作成"
End Sub

送品予定表シートのページ設定は、

印刷時の4辺余白をこのように設定しておきます。

souyosaku006

データバックアップ、シートクリアのVBAコード

次に作成した送品予定表のバックアップと終了時のクリアーについてのVBAを組みます。

モジュールを追加(Module2)して記述します。

バックアップのデータのネーミングは「雑誌送品+(送品日付)+.xlsx」となります。

バックアップデータは「雑誌送品」フォルダに保存する設定です。

VBAコードで、自動でフォルダを作成することもできますが、一回作ればOKですので、あらかじめ手作業で「雑誌送品」フォルダをこの「送品予定表作成.xlsm」と同じ場所作っておいてください。

送品予定表のバックアップ

VBA
Sub 送品表BUP()
    Dim mypath As String
    Dim FN As String
        With Worksheets("送品予定表")
            .Activate
            .Cells.Copy
            .Range("A1").Select
            FN = .Range("B2")
        End With
        ChDir ThisWorkbook.Path & "\雑誌送品"
        Workbooks.Add
        ActiveSheet.Paste
        ActiveWorkbook.SaveAs Filename:="雑誌送品" & FN & ".xlsx"
        Range("A1").Select
        ActiveWorkbook.Close
        Worksheets("送品予定表").Select
        Range("A1").Select
End Sub

シートクリアーのコード

送品予定表とPasteのシートクリアーのコードを組み立てます。

シートクリアーは、2種類のボタンを作ります。

簡単にクリアーをひとまとめにすることもできますが、あえてステップを踏んでクリアー出来るようにしています。

送品予定表

VBA
Sub 送品表クリアー()
        Worksheets("送品予定表").Select
        With Cells
            .ClearContents
            .UseStandardHeight = True
            .UseStandardWidth = True
            .ClearFormats
        End With
        Range("A1").Select
        With Worksheets("送品予定表").PageSetup
            .PrintArea = ""
            .PrintTitleRows = False
        End With
        Worksheets("設定操作").Select
        Range("E1").Select
End Sub

Paste

VBA
Sub Pasteシートクリアー()
        Worksheets("Paste").Select
        With Cells
            .ClearContents
            .UseStandardHeight = True
            .UseStandardWidth = True
            .ClearFormats
        End With
        Range("A1").Select
        Worksheets("設定操作").Select
        Range("E1").Select
End Sub

独自の送品予定表を作成のまとめ

souyosaku014kai

新刊発売予定表は、取次のダイレクトなデータでもデフォルトでも利用できるようは仕様になっているでしょう。

でもそういうデフォルト仕様から、自店に合った様に編集し利用範囲を広げることで、より効率的に業務を行なえるようになります。

その広げるためのツールのひとつが今回紹介した「送品予定表」作成です。

「外商定期購読雑誌リスト」と「店頭お取り置き予約雑誌リスト」を参照して、
その発売時に注意喚起としてチェックマークを表示するようにもできます。これについては、あとの記事で紹介させていただきます。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

大村式【動画&テキスト】Excelマクロ&VBA最高のはじめ方
初めてのVBAの勉強、構えなくても気軽に始められる。
vbastudy020a
vbastudy021a

電子書籍版「大村式【動画&テキスト】Excelマクロ&VBA最高のはじめ方」をamazonで見てみる

(著者)大村あつし
(出版社)技術評論社
(税込価格)1,628円(本体1,480円+税)

学習書の新しい形です。
YouTubeと完全リンクした参考書です。入門と基礎を重点的に22本の動画で解説をしています。
ちょっとした空き時間を利用してでもスマホがあれば学習ができます。
動画は優しい語り口調で、視聴者にある意味安心感を与えてくれます。動画は5分から20分間ぐらいで22本の構成です。
文章解説と動画解説の関係性は、動画解説が主で、文章解説がサポートいう使い方もできます。

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

エクセルVBA最速理解で必要な知識を集めよう!

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

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