こんにちはじゅんぱ店長（@junpa33）です。
今回のテーマは、機能付加のカスタマイズについてです。
先回作成した、雑誌の「送品予定表」をさらに便利化していきます。
事前に作成してある
「外商定期購読雑誌リスト」
「店頭お取り置き予約雑誌リスト」
からデータを取り込んで、当該商品があれば「送品予定表」に”注意マーク”を追加で表示します。
先回の記事を読み直す＞＞＞
「取次の発売予定データを加工して使える送品予定表を独自に作成する。」
送品予定表作成の関連の記事はこちらになります。
コンテンツ
送品予定表と他ファイルとのデータ連携で出来ること
先回作成した送品予定表を、新刊の発売チェックや店頭残品の引き上げなどに利用いただけましたでしょうか。
資料や情報はペーパーレス、モニター確認で行いましょうと言われる中、プリントアウトして使えば、これだけ効率が上がると実感頂けたかも。
今回は、その送品予定表に新たなデータを付加してより活用度をアップさせることが目標です。
送品着荷した商品は”店頭並べ用”だけでなく、”配達用”や”取り置き用”にも割り振りしなければなりません。
また、その合計必要数が送品数と比べてどうか、割り振り余裕がどれだけあるか、などを判断して対策しておかねばなりません。
ナショナルチェーンであれば、そういった複合データを纏めるシステムもあるかと思いますが、現状、多くのお店は一つづつ点検しながら作業していかなければなりません。
この「面倒な作業」を少しの工夫で改善することができます。
この作業改善のために、
「外商定期購読雑誌リスト」と「店頭お取り置き予約雑誌リスト」を作成します。
そしてこの連携によって、この作業にかかっていた所要時間もほぼ０分、必要なくしてしまえます。
データ連携の資料ファイルとなる参照リストを作成
エクセルでデータ参照をするリストとして、「外商定期購読雑誌リスト」と「店頭お取り置き予約雑誌リスト」を作成しておきます。
既に作ってある場合は、取り出しデータのあるセルの位置を調整すれば利用できるかもしれません。
ファイル名を外商取置雑誌リスト.xlsxとします
当店では、「外商定期購読雑誌リスト」と「店頭お取り置き予約雑誌リスト」を一つのリストにまとめて作成しております。
Book名を「外商取置雑誌リスト.xlsx」として、「雑誌シート」を作っています。
そして保存場所は「送品予定表作成.xlsm」と同じところに保存してください。
この様な感じです。
「外商定期購読雑誌リスト」と「店頭お取り置き予約雑誌リスト」についてのご注意
既存のリストを利用する場合は、BOOK名を「外商取置雑誌リスト.xlsx」に変更してください。 今までは、まだ何も作っていなかったお店や手書きノートで作っていたお店について
- 全く何も問題ありませんので、このようなリストを作表してください。
すでにこの種のリストをエクセル以外で作表されているお店について
- このデータリンクを行いたい場合は、エクセルで再度このような作表をしてください。
すでにこの種のリストをエクセルで作表されているお店について
- エクセル間でデータリンクさせる場合は「C列からF列」が重要になります。リンクさせる共通項目を「雑誌コード」に置くからです。キー項目を「雑誌コード」にしていますので、すでにあるリストに「雑誌コード」がない場合は項目追加が必要です
- 顧客別切り口のリストの場合は修正が必要です。「雑誌コード」を含めた「雑誌名」の切り口リストに変更が必要です。
この「外商取置雑誌リスト.xlsx」は後々に記事で紹介する予定の”外商納品雑誌のシステム”でもデータリンクしていく予定です。
そのデータのやり取りのなかで、「発売されたこの雑誌を、どことどこに納品し、またどれだけ取り置くか」を調べます。そこで利用するキー項目ともまた合わせておく必要があります。
なので、もう一度言いますが、すでにエクセルでリストがあるお店については「雑誌コード」を含めた「雑誌名」の切り口でリストを組みなおしていただく必要があります。
これから以降は「参照リスト」名を「外商取置雑誌リスト.xlsx」として解説していきます。
項目の入力事項
入力項目の入力時の注意点をポイント的に少し説明していきます。
雑誌名
データリンクさせるためのキー項目になります。
ですが今回はキー項目を「雑誌コード」にしています。
全角半角スペースなど微妙に違うだけでも同一とはみなされません。
もちろんプログラム的に”曖昧さ”に対処することもできるでしょうが、そこまで記述コードを増やして複雑にしても色んな意味で効率が悪くなると思います。
（作業時間対期待する効果の面でバグが多く出たりとか）
ジャンル
いわゆるジャンルです。
雑誌のグループ分けのようなものですので、普段お使いの種別でよろしいかと思います。
配達雑誌コードと取置雑誌コード
雑誌コードを記入します。
雑誌コードは５桁で記入してください。（号数コードは不要です）
０から始まるコードは、リストに入力しても０はデフォルトでは表示されないと思います。
その場合は４桁の表示でＯＫとして使っていきます。
エクセルBOOK間でデータリンクしてデータのやり取りを組み立てていく中では、あえてここだけプロパティを”文字列”に変更することはしないで、全セル表示形式を”標準”（初期デフォルト）で進めていきます。
ご存知のように、週刊誌系の雑誌（２や３からはじまる雑誌コード）については、一つの雑誌に複数の雑誌コードがあります。
その雑誌コードはすべてリストに入力する必要があります。
対応する雑誌名入力については、例えば、「少年ジャンプ」、「少年ジャンプ ダミー」を４つ などとして対応してください。
ムックやコミックについては、雑誌コード最初の５桁は共通で号数コードで本を区別しています。またその号数コードも毎回変わりますので、このジャンルについては本の特定について有効な判定ができません。
配達冊数と取置冊数
それぞれの必要冊数を記入してください。
販売王コード
販売管理ソフトの「販売王」と連携させるためのコードです。
この連携によって、見積、納品、請求の帳票作成の業務効率化・時短化を図っていくことが可能になります。
この連携が外商営業業務改善の核心部分の1つとなります。
ここに進むための準備として、この資料作成があります。
じゅんぱ店長
配達解除、取置解除
配達や取り置きが終了したときに、雑誌コードをここへ移動させてください。
過去実績の備忘的項目になります。
情報の一元化のためのVBAコードを作成する
コードはこのようになります。
このコードは、２つのエクセルBOOKを対比しながらデータを書き込んでいく設計です。
コードの挿入先は、「送品予定表作成.xlsm」のModule1の「送品表作成()」サブルーチンの途中に挿入してください。
「外商取置雑誌リスト.xlsx」をまだ作成していなければ、このリンクコード①をスルーするVBAコードを付加します。（赤色の文字です）
ChDirの使い方についてはこちらの記事が参考になります。
If条件文についてはこの記事を参考にしてください。
「If条件文」のVBAコードの組み方。条件の絞り方を最速に理解。
入力セルの最終行を取得する方法はこちらを参考にしてください。
Match関数について詳しくはこちらになります。
エクセルVBAで使うMatch関数 活用度アップでテッパン関数に！
Dir関数についてはこちらを参考にしてください。
リンクコード①
Dim ret As Variant Dim rets As Variant Dim myRG As Object, myRGs As Object Dim Cs As Long, Ct As Long Dim p As Long, J As Variant, q As Long, K As Variant Dim last As Long ChDir ThisWorkbook.Path If Dir("外商取置雑誌リスト.xlsx") = "" Then MsgBox "『外商取置雑誌リスト.xlsx』" & vbCrLf & _ "が作成されていませんので、外商取置マーク機能使えません。" Else Workbooks.Open Filename:="外商取置雑誌リスト.xlsx" Worksheets("雑誌リスト").Select Set myRG = Workbooks("外商取置雑誌リスト.xlsx").Worksheets("雑誌リスト").Range("C:C") Set myRGs = Workbooks("外商取置雑誌リスト.xlsx").Worksheets("雑誌リスト").Range("E:E") Workbooks("送品予定表作成.xlsm").Activate Worksheets("設定操作").Select Worksheets("送品予定表").Select last = Cells(Rows.Count, 1).End(xlUp).Row For p = 1 To last J = Worksheets("送品予定表").Range("D" & p).Value ret = Application.Match(J, myRG, 0) If IsError(ret) Then Else Workbooks("外商取置雑誌リスト.xlsx").Activate Worksheets("雑誌リスト").Select Cs = Range("D" & ret).Value Workbooks("送品予定表作成.xlsm").Activate Worksheets("送品予定表").Select V = Cs & "☆:" & Range("J" & p).Value Range("J" & p).Value = V End If Next p For q = 1 To last K = Worksheets("送品予定表").Range("D" & q).Value rets = Application.Match(K, myRGs, 0) If IsError(rets) Then Else Workbooks("外商取置雑誌リスト.xlsx").Activate Worksheets("雑誌リスト").Select Ct = Range("F" & rets).Value Workbooks("送品予定表作成.xlsm").Activate Worksheets("送品予定表").Select Vs = Ct & "■:" & Range("J" & q).Value Range("J" & q).Value = Vs End If Next q Workbooks("外商取置雑誌リスト.xlsx").Close SaveChanges:=False Range("A1").Select End If
どのように表示されるかというと
「外商取置雑誌リスト」で例えばこのように登録してあれば
「送品予定表」の表示はこのようになります。
配本冊数の項目欄で、
”外商納品（配達品）”が６冊あれば「６☆」のマークがつきます。
”取り置き”が５冊あれば「５■」のマークがつきます。
両方あればこの２つのマークが横に並びます。
配本冊数の項目欄の表示文字数が増えますので、「送品予定表作成.xlsm」で組み上げたＭｏｄｕｌｅ１のＶＢＡで指定した「Ｊ列」のセル幅の設定値を変更します。
Range("J:J").ColumnWidth = 8
Range("C:C").ColumnWidth = 9
「Ｊ列」の数値を７→８に変更しましたので、全体幅を保つために「Ｃ列」を１０→９にします。
先回セットしたコードを変更してください。
送品予定表とのデータ連携で業務効率を上げるのまとめ
今回行ったことで、「送品予定表」が１ステップ グレードアップいたしました。
これで着荷した雑誌の必要数が分かりますので、何冊を店頭並べできるのか、何冊まで”傷み品”数の許容ができるのかを簡単に判断することができるようになりました。
完成品はこちらです。今まで解説しましたエクセルＶＢＡの完成品です。使用料は無料です。
＜このＤＬしたエクセルファイルはＶＢＡコードを保護していますので改変はできません。＞
最終形のダウンロードはこちら↓から。
このソフトはご自分で業務でお使いいただくのはフリーですが、
転載や転売については許可しておりませんので、ご使用にならないよう固くお断りいたします。
さらにグレードアップ第２段として、さらにバックナンバー検索もできるように計画します。
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です
定番参考書の改定新版が、動画付きになりもっと分かり易くなった
電子書籍版「改訂新版 てっとり早く確実にマスターできるＥｘｃｅｌ ＶＢＡの教科書」をamazonで見てみる
（著者）大村あつし
（出版社）技術評論社
（税込価格）2,508円（本体2,280円＋税）
30冊を超えるExcelのマクロやVBAの解説書を執筆してきた著者による考え抜かれた本書の内容と構成。
独創的な解説手法で必ずExcel VBAが理解できます！
初級からの参考書ですが、より実践的切り口での解説をしています。
QRコードから操作の流れを動画（無音です）で確認することもできるようになりました。
文章解説と動画との関係性は、主は文章での解説、サポートが動画になります。
エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておきたい内容を纏めています。
今回の記事はここまでです。 最後までご覧いただき有難うございました。
