・在庫管理表が、作った本人しか分からない。もはや本人も分からない
・表の入力を間違えても、ミスが気づけない
・エクセルの行や列を継ぎ足しで作っていて、複雑化している…
・担当者がいないと、業務が止まる
・エクセル関数やマクロを入れて自動化したい！
エクセルに限らず、在庫管理表を使っていて、こんなことを感じていませんか？
私はかつて、製造業で勤務したことがありますが、在庫管理表はエクセルのデータは、どこの数値を参照しているの？となったり、データにミスがあっても、どこが間違っているのかが分からない…という状況でした。
その結果、データの修正に時間ばかりかかっていました。
しかし、エクセルマクロを学び、管理表を作成することで、業務効率化し、年間100時間以上の業務時間を削減しました。その成果を評価され、企業のパソコン講師を経験するまでになりました。
この記事では、そんな私が、エクセルマクロを使った在庫管理表の作り方を解説します。コピペして使えるマクロのコードも紹介していますので、ぜひこのまま読み進めていってください。
そもそもエクセルマクロVBAとは？とギモンに感じているなら、こちらの記事がオススメです。
- 在庫管理とは？
- 無料テンプレートって使える？フリーの在庫管理表のメリット/デメリット
- 管理表は、エクセル関数よりエクセルマクロVBAで作った方が便利！その3つの理由
- マクロでシステム化した在庫管理表の使い方・機能を動画で解説
- 簡単なのに便利！エクセルマクロVBAを使った在庫管理表の作り方
- テンプレートをダウンロードしたい方はコチラから
- まずは試してみる！使いながら、自分でアレンジしていこう
在庫管理とは？
そもそも在庫管理とは、wikipediaによると、以下の通りです
“在庫管理"とは、(中略)常に変動する需要(出庫数量)を満足するように入庫数量を確保すること
在庫を抱えることはコスト要因になるため、在庫管理ではできるだけ在庫を少なく抑えることが目標になる。しかしながら、在庫が不足するとサービスレベルが下がる可能性があるため、在庫を持つことに伴う在庫コストと、サービスレベルをうまくバランスさせる必要がある。
参考元：wikipedia
カンタンに言うと、在庫管理とは、
・在庫不足にならないこと
この2つを維持することです。
在庫管理でやってはいけない2つのこと
在庫管理でやってはいけないのは、以下の2つです。
[1] 欠品の状態になること
[2] 製品在庫を大量に抱えてしまうこと
[1] 欠品の状態になること
欠品状態になると、注文があっても、販売できないので、せっかくの売上を逃してしまいます。
[2] 製品在庫を大量に抱えてしまうこと
製品在庫を大量に抱えると、販売しきれずに売れ残りが生じてしまいます。売れ残ると
・倉庫のスペースがなくなり、他製品の保管ができない。
・誤出荷の原因
・売れ残り処分のために、乱売して価格下落し、売上ダウン
在庫を持つ場合、この2つの状態にならないように管理することはとても重要です。
在庫管理表に必要な3つの機能
在庫管理表に必要な機能は、以下の3つです。
[2] 製品在庫を大量に抱えないこと
[3] 入力が簡単であること
[1]と[2]は、在庫管理をする上で、ゼッタイに抑える項目であることは、既にお伝えした通りです。
この2つに加えて、[3] 入力が簡単であること は、管理表を維持していく上で、重要です。
たとえば、入力項目が多いと、「データ入力がメンドウだから、後でやろう」となってしまい、入力漏れにつながるからです。
管理表を作成する場合、データ入力者のことまで考えて、設定しないと、カタチだけの管理表になってしまいます。
無料テンプレートって使える？フリーの在庫管理表のメリット/デメリット
ウェブ上には、エクセルの在庫管理表が、数多く紹介されています。
無料テンプレートを使えば、カンタンに在庫管理が出来る！と思うかもしれません。しかし、メリットとデメリットがあります。
もちろん、あなたの要望を満たしたものがあれば、とても役立つでしょう。ただ、そんなオイシイ話はなかなかありません。
ここでは、フリーでダウンロードできる在庫管理表のメリットとデメリットを紹介します。
無料テンプレートのメリット/デメリット
| No | メリット | デメリット |
|---|---|---|
| 1 | 必要項目が記載されていて、すぐに使える | 書式が使いづらいと、入力ミスにつながる |
| 2 | エクセル関数が入っていて、自動計算してくれるので、操作がラク | 追加の機能をいれたいとき、エクセル関数の変更が難しい |
| 3 | ー | 要望に合致したものは、なかなか見つからない |
無料で入手できる在庫管理表は、たしかに便利な点があります。スグに使えて、必要項目が入力されているのは、魅力的です。
しかし、無料の在庫管理表では、あなたの業務に、そのまま使えるとは限りません。むしろ、ほとんど場合、使えないでしょう。もしあなたが、入手した在庫管理表のポテンシャルを完全に発揮させたいなら、在庫管理表を自力でアレンジできる力が必要です。
アレンジする力があれば、エクセル関数を変更したり、マクロを入れ込んで、業務の大半を、入力ミスなしで、しかも自動化することができます。
ですので、無料テンプレートに頼りつつ、自力で編集する力を付けるのが、使いやすい管理表を作るための近道です。
管理表は、エクセル関数よりエクセルマクロVBAで作った方が便利！その3つの理由
エクセルで、在庫管理表を作るために、必要なスキルとして、すぐに思い浮かぶのが、エクセル関数とエクセルマクロVBAです。
もし、エクセルマクロVBAという言葉を初めて聞いた方は、こちらの記事で詳しく紹介していますので、合わせて読んでみてくださいね。
エクセル関数を利用している在庫管理表が多いですが、実はエクセルマクロVBAが便利です。その理由は以下の3つです。
[1] エクセルファイルが壊れにくい
[2] エクセルマクロ見積もり書や請求書と連動できる
[3] 習得難易度は、エクセル関数と難易度はあまり変わらない
一つずつ紹介していきます。
マクロが便利な理由1｜エクセルファイルが壊れにくい
エクセル関数を使っていて、こんな式を見たことがありませんか？
=IFERROR(VLOOKUP($I$3&“-”&$C8,在庫入出記録!$C:$Q,MATCH(E$6,在庫入出記録!$C$4:$Q$4,0),0),“”)
このような式が、エクセルのセルの一つ一つに入力されています。
すると、エクセルファイルが、とても重くなります。データ数が100行程度であれば、影響は少ないですが、200行、300行とデータが増えていくと、どんどんエクセルファイルが重くなります。
エクセルファイルが重くなると、どうなるか？
エクセルファイルの処理速度が遅くなり、エラーが出て、強制終了の頻度が増え、最悪の場合、ファイルが壊れます。
せっかく蓄積したデータが壊れたら、かなりマズイ状況になります。
マクロが便利な理由2｜見積もり書や請求書と連動できる
エクセル関数で出来るのは、せいぜいエクセルファイル内の自動計算です。
しかし、エクセルマクロを使えば、さらに便利な機能を使えます。
たとえば、エクセル関数にはできないが、エクセルマクロにできることは以下のようなことがあります。
・見積もり書や請求書を自動で作成
・ワードを呼び出して、処理を行う
エクセルマクロなら、出来ることが幅広いので、在庫管理表をベースにして、さまざまな機能を付与できます。
マクロが便利な理由3｜習得難易度は、エクセル関数と難易度はあまり変わらない
一般的に、エクセルマクロとエクセル関数を比べたら、エクセルマクロは習得が難しそうと思われがちです。しかし、そんなことはありません。
実は、エクセルマクロとエクセル関数と難易度はあまり変わりません。
たとえば、以下の画像のように
①で記載されている氏名を
②のように分離するとします
エクセル関数を使うと
D2～D7に
=LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(B2),“/”,“ ”),“ ”,“ ”),FIND(“ ”,SUBSTITUTE(SUBSTITUTE(TRIM(B2),“/”,“ ”),“ ”,“ ”)))
E2～E7には、
=MID(SUBSTITUTE(SUBSTITUTE(TRIM(B2),“/”,“ ”),“ ”,“ ”),FIND(“ ”,SUBSTITUTE(SUBSTITUTE(TRIM(B2),“/”,“ ”),“ ”,“ ”))+1,100)
と入れ込む必要があります。
同じことをエクセルマクロで実行しようとすると、
Sub step3() Dim kugiri,i As Long Dim namae As String For i = 2 To 7 namae = Trim(Range("B" & i).Value) namae = Replace(namae, "/", " ") namae = Replace(namae, " ","") kugiri = InStr(myonam, " ") Range("C" & i).Value = Left(namae, kugiri - 1) Range("D" & i).Value = Mid(namae, kugiri + 1) Next End Sub
12行で書けます。
このようにエクセルマクロとエクセル関数では、記載する量や、見やすさは、ほとんど変わりません。
一般的に、エクセル関数の方がマクロよりカンタンだと思われがちですが、必ずしもそうではありません。
しかも、エクセルマクロでは、自動印刷など、エクセル関数で出来ないことができます。
同じ時間をかけて、エクセル関数を習得するなら、エクセルマクロを覚える方が、かなりトクします。
マクロでシステム化した在庫管理表の使い方・機能を動画で解説
エクセルマクロVBAを利用した「在庫管理表」の使い方について紹介していきます。
できるだけ使いやすくするために、シンプルなものを目指しました。
この記事で紹介する「マクロを使った在庫管理表」の使い方・機能について、まずは動画でご覧になってください。
クリックしてご覧になってください。なお無音ですので、音量設定は不要です。
簡単なのに便利！エクセルマクロVBAを使った在庫管理表の作り方
エクセルマクロVBAを利用した「在庫管理表」の作り方について紹介していきます。
作り方1｜シート「品目表」に必要項目を入れ込む
品目表に必要となる項目を入れ込みます。
この記事では、以下の4つを選択しました
B列｜品名：事前に入力
C列｜最低保管在庫：事前に入力
D列｜現在の在庫数：記入不要(自動更新される)
新しい商品が増えたら、この品目表に追加していきます。
作り方2｜シート「入出庫表」に必要項目を入れ込む
シート「入出庫表」に必要となる項目を入力します。
この記事では、以下の手順で入力します。
②｜入庫か出庫が発生したら、[摘要～出庫数]のセルに情報を入力
③｜記入日時～状況のセルは記入不要(自動更新される)
④｜新しく入庫か出庫が発生したら、新たに情報を入力
⑤｜「更新」ボタンを押す(自動処理開始)
動画をご覧になった方は、お気づきかもしれませんが、「更新」ボタンには、マクロが起動するように設定しています。これで、ボタン一つで、処理が終わるようになっています。
もし、マクロを起動する方法や設定方法が分からない場合は、こちらの記事の中で、紹介していますので、合わせて読んでみて下さい
「更新」ボタンを押したら、情報が読み込まれて、更新された行は自動で灰色になる
また、「更新」ボタンを押すと、シート「品目表」も更新されます
そのとき、「D列｜現在の在庫数」が「C列｜最低保管在庫」を下回ると、行が自動で黄色になります。
黄色に変化することで、アラート機能の役割を果たします。
作り方3｜マクロのコードテンプレートをVBEコピペして利用
この記事で作った在庫管理表のマクロのコードは以下の通りです。
Option Explicit Sub koushin() Dim k, i, m, cmax1, cmax2 As Long Dim ws1, ws2 As Worksheet Dim str As String Set ws1 = Worksheets("品目表") Set ws2 = Worksheets("入出庫表") cmax1 = ws1.Range("A65536").End(xlUp).Row cmax2 = ws2.Range("A65536").End(xlUp).Row For i = 6 To cmax2 '---記入漏れ対策--- If ws2.Range("A" & i).Value = "" Then MsgBox "摘要を記入してください" Exit For ElseIf ws2.Range("B" & i).Value = "" Then MsgBox "品目IDを記入してください" Exit For ElseIf ws2.Range("C" & i).Value <> "" And ws2.Range("D" & i).Value <> "" Then MsgBox "入庫数と出庫数のどちらかを削除してください" Exit For ElseIf ws2.Range("C" & i).Value = "" And ws2.Range("D" & i).Value = "" Then MsgBox "入庫数と出庫数のどちらかを記入してください" Exit For End If '---I列の更新状況をチェック。更新済ならスキップ--- If ws2.Range("I" & i).Value <> "更新済" Then str = ws2.Range("B" & i).Value '---E列｜日時の計算--- ws2.Range("E" & i).Value = Now '---F列｜品名の自動出力--- For k = 2 To cmax1 If str = ws1.Range("A" & k).Value Then ws2.Range("F" & i).Value = ws1.Range("B" & k).Value Exit For End If Next '---G列｜入庫数を入れ込む--- If ws2.Range("C" & i).Value <> "" Then ws2.Range("G" & i).Value = ws2.Range("C" & i).Value ElseIf Range("D" & i).Value <> "" Then ws2.Range("G" & i).Value = ws2.Range("D" & i).Value * (-1) End If '---H列｜現在の在庫数を計算--- If i > 6 Then For m = i - 1 To 6 Step -1 If str = ws2.Range("B" & m).Value Then ws2.Range("H" & i).Value = ws2.Range("H" & m).Value + _ ws2.Range("G" & i).Value Exit For End If Next End If If i = 6 Or m = 5 Then ws2.Range("H" & i).Value = ws2.Range("G" & i).Value End If '---I列｜更新済を入力--- Range("I" & i).Value = "更新済" Range("A" & i & ":I" & i).Interior.ColorIndex = 15 '---「品目表」のD列を更新--- ws1.Range("D" & k).Value = Range("H" & i).Value '---「現在の在庫数」が「最低保管在庫」を下回ったら黄色にする--- If ws1.Range("C" & k).Value > ws1.Range("D" & k).Value Then ws1.Range("A" & k & ":D" & k).Interior.ColorIndex = 6 End If End If Next End Sub
このコードをVBEにコピペすれば、起動します。
テンプレートをダウンロードしたい方はコチラから
今回紹介したエクセルマクロを一から作るのは大変なので、テンプレート(エクセルマクロのコード含む)は無料でダウンロードできるようにします。以下のフォームにメールアドレスを入力いただくと、返信メールからエクセルファイルをダウンロードできます。
ぜひご活用ください。
まずは試してみる！使いながら、自分でアレンジしていこう
エクセルマクロを利用した在庫管理表については、詳しく解説しました。
この記事で紹介した在庫管理表を応用すれば、出庫伝票を作ったり、月別に請求書を自動印刷することも可能です。このようなシステム化は、エクセル関数では出来ません。マクロを使うことのメリットは、非常に大きいです。
しかし、この記事で紹介している内容だけでは、あなたの業務を劇的に軽くすることはムズカシイでしょう。
なぜなら、ここで紹介しているマクロを利用して、あなたの業務に合わせてカスタムする必要があるからです。そのアレンジするスキルを身に付けないと、どれだけマクロのコードを入手しても、効果は少ないでしょう。
もし、あなたが「もっと自分の業務にあったものを作りたい！」、「編集できるようになりたい」と思うなら、こちらの無料オンライン動画がオススメです。
アレンジする力があれば、マクロのコードを編集して、業務の大半を、入力ミスなしで、しかも自動化することができます。
ぜひ、あなたもエクセルマクロVBAを学んで、メンドウな業務をシステム化しましょう。
次ページ 無職・派遣の男がたった1年で、仕事で年収100万アップし、海外プロジェクトリーダーに抜擢された「たった1つ」の方法とは？
