雑誌のバックナンバー検索の作業を超簡単にする方法があります。
それはVBAを使って送品予定表から作ることが出来ます。
過去のデータを探し出すのは面倒なものです。恐らく一度使えば手放せなくなります。
こんにちは、じゅんぱ店長(@junpa33)です。
今回は、先回作成した送品予定表のバックアップを利用して
雑誌のバックナンバー検索ができるようにします。
送品予定表についての記事はこちらです。
送品予定表と他ファイルとのデータ連携をする。情報の一元化で業務効率を上げる
今回の雑誌バックナンバー検索シリーズの結論はこちらです。(作成方法に興味のない方はここで離脱いただけます。)これからこのダウンロード頂いた「雑誌バックナンバー検索.xlsm」について説明していきます。
エクセルVBAを使ってご自分で作れますので、もちろん使用料は無料になります。
これからの記事を読み進めて頂くと、改変可能なものも作ることができます。
ご自分でカスタマイズ可能なものが作れます。
<このDLしたエクセルファイルはVBAコードを保護していますので改変はできません。>
最終形のダウンロードはこちらから。
“雑誌バックナンバー検索ツール” をダウンロード BackNoKensaku.zip – 2188 回のダウンロード – 42 KB
このソフトはご自分で業務でお使いいただくのはフリーですが、
転載や転売については許可しておりませんので、ご使用にならないよう固くお断りいたします。
バックナンバー検索表作成に関連した記事はこちらになります。
コンテンツ
雑誌のバックナンバー検索を簡単にしたい!
ある雑誌タイトルについて、過去の入庫実績を確認したい時があるかと思います。
仕事の中で、入荷数を検討したい時や販売数を確認したい時、また、売れ残り返品数の調整(減らす)を検討したい時にどのようにされていますか?
そこで、
先回作成した「送品予定表」からバックアップ出力される「雑誌送品(年月日).xlsx」を利用して、知りたい情報を抜き出していきます。
所謂、「蓄積しているデータベースを利用しましょう。」ということです。
いろんな伝票を繰らなくてもここで調べてパッと分かればちょっと嬉しいですね。
悩める君
そうなんだよね。
売れた数にはみんな興味あるけど、
その時に入ってきた数を調べるとなると
結構面倒な作業が多いよね。
これを使えば、いつ入庫数変更したとか
いつから客注数増えたとかよくわかる様になりますよ。
じゅんぱ店長
バックナンバー検索エクセルソフトを作成する
今回は、以前に作成したVBAソフトの送品予定表の出力データを、
必要部分だけまとめて扱えるようにする部分のプログラムを作成します。
それでは、エクセルBOOKを起動してVBAを組み立てていきます。
VBA設計の方針
まずは、どのように組み立てていくかを考えます。
BOOK名(ファイル名)を「雑誌バックナンバー検索.xlsm」とします。
1日ごとに作られている雑誌送品ファイルを「雑誌バックナンバー検索.xlsm」のシートに、1日ごとにそれぞれ転記していきます。
転記完了したところで、その各シートを纏めてデータがひとつながりの一つのシートを作ります。
検索項目を作りVBAを組み上げます。
ユーザーフォームを使って、コントロールパネルに操作ボタンを作ります。
利用するファイル数も多くなりますので、データファイルの収納フォルダ管理も重要です。
「雑誌バックナンバー検索.xlsm」のシート名設定
新しいBOOKを開いて「雑誌バックナンバー検索.xlsm」としてください。
「Sheet1」を「検索結果」
「Sheet2」を「データまとめ」
「Sheet3」を「メモ」 としてください。
「検索結果」シートは、名前の通り検索結果を表示するシートとなります。
「データまとめ」は毎日の送品予定表を指定の期間分取りまとめて一覧表の形式にします。
「メモ」は白紙ページです。検索したデータを加工したり計算したりするスペースです。
毎日の「送品予定表」をデータベース化
1日ごとに作られている雑誌送品ファイルを「雑誌バックナンバー検索.xlsm」のシートに転記するためのVBAコードを組み立てます。
いつものようにまず、Module1を挿入します。
挿入方法の確認はこちら↓
関連記事
ここでのVBAは、各日付の名称で保存されているBOOKの「Sheet1」をコピーして「雑誌バックナンバー検索.xlsm」の「メモ」シート以降に「各送品日の日付」名でシートを自動追加していきます。
このシート追加はエクセルのスペックでは理論上無制限となっています。
ただ、それぞれお使いのPCのメモリ容量次第のようですので、1年分か、半年分か、3か月か
1か月かはそれぞれ試してみてご判断ください。
関連記事
VBA 回数不定のループ処理はDo LoopとFor Each
コードとしてはこのような感じです。
コード①
Sub 送品予定表転記()
Dim mb As Object
Dim wb As Object
Dim mbp As Variant
Dim fname As Variant
Dim sname As Variant
Application.ScreenUpdating = False
Set mb = ThisWorkbook
mbp = ThisWorkbook.Path
fname = Dir(mbp & "\*.xls")
Do Until fname = Empty
If fname <> mb.Name Then
Worksheets.Add after:=mb.Worksheets(mb.Worksheets.Count)
Set wb = Workbooks.Open(mbp & "\" & fname)
wb.ActiveSheet.Select
sname = ActiveSheet.Range("B2")
Selection.Copy _
Destination:=mb.Worksheets(mb.Worksheets.Count).Range("A1")
mb.Worksheets(mb.Worksheets.Count).Name = sname
wb.Close (False)
n = n + 1
Range("A1").Select
End If
fname = Dir
Loop
Application.ScreenUpdating = True
Worksheets("検索結果").Select
Range("A1").Select
MsgBox n & "日分の送品予定表を取り込みました。"
End Sub
送品予定表をひとまとめにする
送品予定表を一つづつのシートにしましたが、今度はそれを一つのシートとしてまとめます。
これはバックナンバー検索のためのデータベースとするためのものです。
8/13追加修正しました。(緑字の部分)
- コピペモードをOFFにする。
- まとめたデータの罫線を引きなおす。
「For Each~Next」の使い方についてはこちらの記事を参考にしてください。
VBA 回数不定のループ処理はDo LoopとFor Each
入力セルの最終行を取得する方法はこちらを参考にしてください。
With~End Withの使い方については、この記事を参考にしてください。
コードとしてはこのような感じです。
コード②
Sub データをまとめる() Dim Ws As Worksheet, Wsa As Worksheet Dim sR As Long, saR As Long Set Wsa = Worksheets("データまとめ") For Each Ws In Worksheets If Ws.Name <> "データまとめ" Or Ws.Name <> "検索結果" _ Or Ws.Name <> "メモ" Then sR = Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row saR = Wsa.Cells(Wsa.Rows.Count, 1).End(xlUp).Row + 1 If Wsa.Range("A2") = "" Then saR = 1 Ws.Rows("1:" & sR).Copy Destination:=Wsa.Rows(saR) Else Ws.Rows("2:" & sR).Copy Destination:=Wsa.Rows(saR) End If End If NextApplication.CutCopyMode = False Worksheets("データまとめ").SelectWith Range("A1").CurrentRegion .Borders.LineStyle = xlLineStyleNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlHairline End With シート体裁 End Sub
今回は、シート丸ごとコピペではありませんので、シートの体裁の部分で、セル幅などがデフォルト値に戻ってしまう部分があります。
そこで、設定したセル幅を復活させるために、それ用のサブルーチンを設定しています。
「シート体裁」という名称にしています。
上のコードの下に以下のコードを加えてください。
コード③
Sub シート体裁()
Range("A:L").Font.Size = 9
Range("A:A").ColumnWidth = 4
Range("B:B").ColumnWidth = 8
Range("C:C").ColumnWidth = 10
Range("D:D").ColumnWidth = 8
Range("E:F").ColumnWidth = 4
Range("G:G").ColumnWidth = 3
Range("H:H").ColumnWidth = 11
Range("I:I").ColumnWidth = 21
Range("J:J").ColumnWidth = 7
Range("K:K").ColumnWidth = 6
Range("L:L").ColumnWidth = 6
End Sub
取り込みデータのクリア
(8/13追加項目)
(1)転記した送品予定表の各シートを削除するコードを作ります。
この様になります。
コード④
Sub 送品予定表削除()
Dim Wsb As Worksheet
Application.DisplayAlerts = False
For Each Wsb In Worksheets
If Wsb.Name <> "データまとめ" And Wsb.Name <> "検索結果" _
And Wsb.Name <> "メモ" Then
Wsb.Delete
End If
Next
Application.DisplayAlerts = True
End Sub
(2)「データまとめ」シートをクリアします。
こちらのほうは、セルに入っているデータをクリアして、書式設定をデフォルトにします。
コード⑤
Sub データまとめクリア()
Worksheets("データまとめ").Cells.Clear
With Worksheets("データまとめ")
.Cells.UseStandardHeight = True
.Cells.UseStandardWidth = True
End With
End Sub
雑誌のバックナンバー検索ツール作成のまとめ
雑誌バックナンバー検索ツールの作成について、今回も長大な記事になってしまいますので、一部、二部、三部に分けることにします。
今回は第一部として、検索のためのデータベースを作るところまで説明いたしました。
次回第二部は、検索キーワード設定と検索結果の表示領域の部分を説明したいと思います。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です
エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。


電子書籍版「大村式【動画&テキスト】Excelマクロ&VBA最高のはじめ方」をamazonで見てみる
(著者)大村あつし(出版社)技術評論社
(税込価格)1,628円(本体1,480円+税)
学習書の新しい形です。
YouTubeと完全リンクした参考書です。入門と基礎を重点的に22本の動画で解説をしています。
ちょっとした空き時間を利用してでもスマホがあれば学習ができます。
動画は優しい語り口調で、視聴者にある意味安心感を与えてくれます。動画は5分から20分間ぐらいで22本の構成です。
文章解説と動画解説の関係性は、動画解説が主で、文章解説がサポートいう使い方もできます。
エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておきたい内容を纏めています。
今回の記事はここまでです。 最後までご覧いただき有難うございました。