エクセルVBAで単なる雑誌の送品予定表から、よりデータ価値の高いものに作り替えます。他のエクセルブックのデータと連携することで、業務効率を計ることが出来ます。
こんにちはじゅんぱ店長（@junpa33）です。
この記事では、
先回作成した、雑誌の「送品予定表」を、他データと連携させて、さらに使い易く便利化していきます。
「他データ資料」の一つとしてここで紹介するのは、「外商取置雑誌リスト」です。
外商で定期配達する雑誌と店取り置きする雑誌のリストになります。
例えば、すでに作成し使っているもので、
「外商定期購読雑誌リスト」
「店頭お取り置き予約雑誌リスト」的なリストが2つあった場合でも
それぞれのリストからデータを取り込んで、適合商品があれば、
「送品予定表」に”注意マーク”やその”数量”を追加で表示することも出来ます。
今回はこのような2つのリストを、1つにまとめたものを使用しますが、VBAコードをカスタマイズして2つのリストからデータを読み込めるようすることも可能です。
また他の方法として、
その2つのリストを1つのリストに、これから説明するフォーマットで再編成していただくことで、連携出来るようにもなります。
送品予定表作成の記事編成
- 送品予定表の使い方とダウンロード
- 送品予定表作成手順
コンテンツ
送品予定表と他データ連携なら出来ること
先回作成した送品予定表は、直接的には、新刊の発売チェックや店頭残品の引き上げなどに利用することが出来ます。
今回は、その送品予定表に新たなデータを付加して、その他の業務にも利用してより活用度を上げるようにします。
例えば、
送品着荷した雑誌は、”店頭並べ用”だけでなく、”配達用”や”取り置き用”としても必要です。そのために冊数を振り分ける必要があります。
また、その合計必要数が送品数と比べてどうか、割り振り余裕がどれだけあるかなどを見て、店頭並べ数を決めておく必要もあります。
ナショナルチェーンであれば、そういった複合データを纏めるシステムもあるかと思いますが、現状、多くのお店は一つづつ点検しながら作業していかなければなりません。
このような、複数の業務の内容をまとめる「面倒な作業」でも、少しの工夫で簡単にすることができます。
この作業を改善するためには、
「外商定期購読雑誌リスト」と「店頭お取り置き予約雑誌リスト」を作成して、そのデータを同時に横並びで見れるようにすればよいのです。
このようなデータ連携をした結果として、このバラバラで行っていた作業と比較して、同時作業にして余分な所要時間もほぼ０分。大きく業務時間短縮を出来るようになります。
今まで、当日着荷の雑誌は、”店頭へ全出し”からの”取り置き分逆引き上げ”と”配達分逆引き上げ”なんていう無駄な作業は無くなります。
データを連携させる資料となるブックを作成
エクセルブックでデータ参照をするリストとして、「外商定期購読雑誌リスト」と「店頭お取り置き予約雑誌リスト」を作成しておきます。
既に作ってある場合は、取り出しデータのあるセルの位置を調整すれば利用できるかもしれません。
この記事の解説の通り進めるには、以下のリストの様にデータを編集してください。
手書きのリストで対応しているお店は、是非この機会に以下のフォーマットで、エクセルで作成してみてください。
外商取置雑誌リスト.xlsxを作成
「外商定期購読雑誌リスト」と「店頭お取り置き予約雑誌リスト」を一つのリストにまとめて作成します。
Book名を「外商取置雑誌リスト.xlsx」として、「雑誌シート」を作っています。
保存場所は「送品予定表作成.xlsm」と同じところ（フォルダ）に保存してください。
フォルダ内レイアウト
「外商定期購読雑誌リスト」と「店頭お取り置き予約雑誌リスト」についての注意点
リストをまとめるのは次の設定としてください。
- C列・・・配達雑誌コード
- D列・・・配達冊数
- E列・・・取置雑誌コード
- F列・・・取置冊数
- シート名・・・雑誌リスト
- ブック名・・・外商取置雑誌リスト.xlsx
入力項目の事項
入力項目の注意ポイントを説明します。
データをリンクさせるためのキー項目になります。
ですが今回はキーとしては利用しません。（キーは「雑誌コード」）にしています。
雑誌名そのものをマッチングさせるキーにすることも出来ます。
ただし文字列の場合は、全角半角スペースなど微妙に違うだけでも同一とは見なされないことが多くあります。
なので、その調節のためのコードも必要で、いろんな意味で効率が悪いと言えます。
いわゆる種別です。
雑誌のグループ分けのようなものですので、普段お使いの種別でよろしいかと思います。
雑誌コードを記入します。
雑誌コードは５桁で記入してください。（発売）号数コードは不要です。
０から始まるコードは、リストに入力しても０はデフォルトでは表示されないと思います。
その場合は４桁の表示でＯＫとして使っていきます。
セルに雑誌コードを入力すると”エクセルの自動認識で数値として”認識されます。
「０」で始まる雑誌コードはエクセルでは”デフォルト状態で0は無しの4桁”で表示されます。
セルの表示形式を数値から文字列に変更すれば、本来の雑誌コード表示に「０」が付きますが、ここで行いたい作業はそれではありませんので、コードの簡略化からも、この問題は無視できると判断しています。
ご存知のように、週刊誌系の雑誌（２や３からはじまる雑誌コード）については、一つの雑誌に複数の雑誌コードがあります。
その雑誌コードはすべてリストに入力する必要があります。
対応する雑誌名入力については、例えば、「少年ジャンプ」、「少年ジャンプ ダミー」を４つ などとして対応してください。
ムックやコミックについては、雑誌コード最初の５桁は共通で号数コードで本を区別しています。またその号数コードも毎回変わりますので、このジャンルについては本の特定について有効な判定ができません。
それぞれの必要冊数を記入してください。
販売管理ソフトの「販売王」と連携させるためのコードです。
この連携によって、見積、納品、請求の帳票作成の業務効率化・時短化を図っていくことが可能になります。
この連携が外商営業業務改善の核心部分の1つとなります。
ここに進むための準備として、この資料作成があります。
また、後々に。
じゅんぱ店長
配達や取り置きが終了したときに、雑誌コードをここへ移動させてください。
過去実績の備忘的項目になります。
情報一元化するVBAコードを作成
コードはこのようになります。
このコードは、２つのエクセルBOOKを対比しながらデータを書き込んでいく設計です。
コードの挿入先は、「送品予定表作成.xlsm」でModule3を新規挿入して記述します。
データ連携の全コード
データ連携の全コードです。
Module3を新設してそちらに記述します。
Option Explicit
Sub データ連携()
Dim Msg As Integer
Dim wb As Workbook
Dim Flag As Boolean
Dim r, myRG As Range
Dim Zrow, Srow As Long
Dim Hcode, Hs, Tcode, Ts As Long, Ct As Variant
Dim i As Long
Dim V As String
'データ連携するかどうかの確認メッセージ
Msg = MsgBox("データ連携を行ないますか？", vbYesNo _
+ vbInformation, "データ連携")
If Msg = 7 Then
MsgBox "終了します", vbInformation, "データ連携"
Exit Sub
End If
'外商取置雑誌リストが存在するか？
ChDir ThisWorkbook.Path
If Dir("外商取置雑誌リスト.xlsx") = "" Then
MsgBox "『外商取置雑誌リスト.xlsx』" & vbCrLf & _
"が作成されていないので、外商マーク機能は利用できません。"
Exit Sub
End If
'外商取置雑誌リストが既に開かれているか？
For Each wb In Workbooks
If wb.Name = Dir("外商取置雑誌リスト.xlsx") Then
Flag = True
Exit For
End If
Next
If Flag = False Then
Workbooks.Open Filename:="外商取置雑誌リスト.xlsx"
End If
'配達・取置商品を調べる
'データ取得範囲を設定
Workbooks("外商取置雑誌リスト.xlsx").Activate
Zrow = Worksheets("雑誌リスト").Cells(Rows.Count, 1) _
.End(xlUp).Row
Workbooks("送品予定表作成.xlsm").Activate
With Worksheets("送品予定表")
Srow = .Cells(Rows.Count, 1).End(xlUp).Row
Set myRG = .Range(.Cells(2, 4), .Cells(Srow, 4))
End With
'取得範囲から一つづつデータを取得
For i = 2 To Zrow
Workbooks("外商取置雑誌リスト.xlsx").Activate
With Worksheets("雑誌リスト")
Hcode = .Cells(i, 3)
Hs = .Cells(i, 4)
Tcode = .Cells(i, 5)
Ts = .Cells(i, 6)
End With
'配達商品データをチェックする
For Each r In myRG
If r.Value = Hcode Then
Workbooks("送品予定表作成.xlsm").Activate
With Worksheets("送品予定表")
Ct = .Cells(r.Row, 10).Value
V = Hs & "☆" & Ct
.Cells(r.Row, 10) = V
End With
Exit For
End If
Next
'取置商品データをチェックする
For Each r In myRG
If r.Value = Tcode Then
Workbooks("送品予定表作成.xlsm").Activate
With Worksheets("送品予定表")
Ct = .Cells(r.Row, 10).Value
V = Ts & "■" & Ct
.Cells(r.Row, 10) = V
End With
Exit For
End If
Next
Next i
'チェックを完了して終了処理をする
Workbooks("外商取置雑誌リスト.xlsx").Close SaveChanges:=False
Workbooks("送品予定表作成.xlsm").Activate
Worksheets("設定操作").Select
Range("A1").Select
MsgBox "データ連携完了しました。", vbInformation, _
"送品予定表作成"
End SubVBAコードの解説
データ連携プロシージャーは、前提として「送品予定表」の作成が完了していることが必要です。
なので、
先回、解説した「送品表作成」プロシージャーのコード実行のストリームの最後に、
このデータ連携」プロシージャーを呼び出すという形で起動させます。
つまり、
「送品表作成」プロシージャーの部品化プロシージャーとして、Callステートメントで起動させることとします。
Call Module3.データ連携このコードを、「送品表作成」プロシージャーの終了コード「End Sub」の直前に挿入します。
まず最初に、「送品予定表」を作成完了してさらに「データ連携」を行うかどうかを確認します。
メッセージボックスで、” 行う ” か ” 止める ” かを、指示することが出来ます。
実際に、「外商取置雑誌リスト」が存在しなければ、作業を進めることが出来ません。
もし存在しない場合は、ここでこのマクロは終了することになります。
リスト名を間違えて作成している場合は、そのリストとなるエクセルブックを検出できませんので注意してください。名前の修正が必要です。
Windows上で既に外商取置雑誌リストが開かれていれば、重複起動になるのでチェックしておきます。
VBA 回数不定のループ処理はDo LoopとFor Each
ワークブックを開くOpenメソッドの書き方 Excelマクロ
外商取置雑誌リストがあるということが分かり、ここからリスト内のデータと送品予定表のデータを比較・マッチングさせていきます。
調べる方法は、「外商取置雑誌リスト」のそれぞれのデータを、「送品予定表」のデータに当てていくという方法で行います。
外商取置雑誌リストの規模（総行数）を調べておきます。
「何処の範囲」調べるのかということで、送品予定表の調査範囲を変数代入しておきます。
「外商取置雑誌リスト」のそれぞれのデータを一つずつ変数にははめ込んでいきます。
Hcode・・・配達雑誌の雑誌コード
Hs・・・・・配達雑誌の合計冊数
Tcode・・・取り置き雑誌の雑誌コード
Ts・・・・・取り置き雑誌の合計冊数
エクセルVBA！For～Nextのループと入れ子構造をVBA最速理解
With～End Withの使い方。VBAコードを簡潔に記述する
配達雑誌の雑誌コードが、「送品予定表」の調べる対象となる範囲に存在するかどうかを調べていきます。
存在していた場合は、「送品予定表」の雑誌データの送品数のところに「必要数☆」マークを付加します。
この送品予定データ表では、配達に3冊必要に対して、着荷予定0冊ということを読み取ることが出来ます。
取り置き雑誌の雑誌コードが、「送品予定表」の調べる対象となる範囲に存在するかどうかを調べていきます。存在していた場合は、「送品予定表」の雑誌データの送品数のところに「必要数■」マークを付加します。
この送品予定データ表では、取り置きに１冊必要に対して、着荷予定0冊ということを読み取ることが出来ます。
チェック終了後の終了処理です。
「外商取置雑誌リスト.xlsx」を閉じて、送品予定表作成.xlsmの操作のスタート位置に戻ります。
送品予定表との他データの連携 まとめ
補足として、外商取置雑誌リストで、配達商品と取り置き商品の両方ともある場合は、2つのマークが付くことになります。
例えば、「5■６☆」の様に着荷数量の左に付加されることになります。
このように今回の「送品予定表」へのアドオンで、利用効率を１ステップ上げることが出来ます。
これにより着荷した雑誌の必要数が分かりますので、何冊を店頭並べできるのか、何冊まで”傷み品の数を許容できるのかを簡単に判断することもできるようになりました。
また、送品予定数（着荷予定）ですので、事前に雑誌不足数があれば認識することができ、事前の対処も可能になります。
また、外商配達やレジ担当の人が、店内担当者の雑誌振り分け業務を待たずして、自分の作業に取り掛かれるという時間メリットを出すことも出来ます。
データ連携を行なった送品予定表作成の完成品はこちらです。
今まで解説しましたエクセルＶＢＡの完成品です。
これは無料でダウンロードしていただけます。
ですが、ご利用に際しては注意点をお守りください。
[download id=”22786″]このソフトはご自分で業務でお使いいただくのはフリーですが、
転載や転売については許可しておりませんので、ご使用にならないよう固くお断りいたします。
違法ルートで入手された場合は、著作権に抵触します。
This software is free to use for your own business,
Reproduction or resale is not permitted, so please refrain from using it.
It violates copyright if it is obtained through illegal routes.
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です
今回の記事はここまでです。 最後までご覧いただき有難うございました。
