パソコンスキル教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

エクセルマクロを使った在庫管理表の作り方|テンプレートで簡単作成

f:id:gene320:20161229205508j:plain

・在庫管理表が、作った本人しか分からない。もはや本人も分からない
・表の入力を間違えても、ミスが気づけない
・エクセルの行や列を継ぎ足しで作っていて、複雑化している…
・担当者がいないと、業務が止まる
・エクセル関数やマクロを入れて自動化したい!

エクセルに限らず、在庫管理表を使っていて、こんなことを感じていませんか?

私はかつて、製造業で勤務したことがありますが、在庫管理表はエクセルのデータは、どこの数値を参照しているの?となったり、データにミスがあっても、どこが間違っているのかが分からない…という状況でした。

その結果、データの修正に時間ばかりかかっていました。

しかし、エクセルマクロを学び、管理表を作成することで、業務効率化し、年間100時間以上の業務時間を削減しました。その成果を評価され、企業のパソコン講師を経験するまでになりました。

この記事では、そんな私が、エクセルマクロを使った在庫管理表の作り方を解説します。コピペして使えるマクロのコードも紹介していますので、ぜひこのまま読み進めていってください。

そもそもエクセルマクロVBAとは?とギモンに感じているなら、こちらの記事がオススメです。

www.fastclassinfo.com

エクセルマクロを使った在庫管理表の作り方を徹底解説

在庫管理とは?

そもそも在庫管理とは、wikipediaによると、以下の通りです

“在庫管理"とは、(中略)常に変動する需要(出庫数量)を満足するように入庫数量を確保すること

在庫を抱えることはコスト要因になるため、在庫管理ではできるだけ在庫を少なく抑えることが目標になる。しかしながら、在庫が不足するとサービスレベルが下がる可能性があるため、在庫を持つことに伴う在庫コストと、サービスレベルをうまくバランスさせる必要がある。
参考元:wikipedia

カンタンに言うと、在庫管理とは、

・できるだけ在庫を少なく抑えること
・在庫不足にならないこと

この2つを維持することです。

在庫管理でやってはいけない2つのこと

在庫管理でやってはいけないのは、以下の2つです。

在庫管理でやってはいけないこと
[1] 欠品の状態になること
[2] 製品在庫を大量に抱えてしまうこと

[1] 欠品の状態になること

欠品状態になると、注文があっても、販売できないので、せっかくの売上を逃してしまいます。

[2] 製品在庫を大量に抱えてしまうこと

製品在庫を大量に抱えると、販売しきれずに売れ残りが生じてしまいます。売れ残ると
・倉庫のスペースがなくなり、他製品の保管ができない。
・誤出荷の原因
・売れ残り処分のために、乱売して価格下落し、売上ダウン

在庫を持つ場合、この2つの状態にならないように管理することはとても重要です。

在庫管理表に必要な3つの機能

在庫管理表に必要な機能は、以下の3つです。

[1] 欠品の状態にならないこと
[2] 製品在庫を大量に抱えないこと
[3] 入力が簡単であること

[1]と[2]は、在庫管理をする上で、ゼッタイに抑える項目であることは、既にお伝えした通りです。

この2つに加えて、[3] 入力が簡単であること は、管理表を維持していく上で、重要です。

たとえば、入力項目が多いと、「データ入力がメンドウだから、後でやろう」となってしまい、入力漏れにつながるからです。

管理表を作成する場合、データ入力者のことまで考えて、設定しないと、カタチだけの管理表になってしまいます。

無料テンプレートって使える?フリーの在庫管理表のメリット/デメリット

ウェブ上には、エクセルの在庫管理表が、数多く紹介されています。

無料テンプレートを使えば、カンタンに在庫管理が出来る!と思うかもしれません。しかし、メリットとデメリットがあります。

もちろん、あなたの要望を満たしたものがあれば、とても役立つでしょう。ただ、そんなオイシイ話はなかなかありません。

ここでは、フリーでダウンロードできる在庫管理表のメリットとデメリットを紹介します。

無料テンプレートのメリット/デメリット

No メリット デメリット
1 必要項目が記載されていて、すぐに使える 書式が使いづらいと、入力ミスにつながる
2 エクセル関数が入っていて、自動計算してくれるので、操作がラク 追加の機能をいれたいとき、エクセル関数の変更が難しい
3 要望に合致したものは、なかなか見つからない

無料で入手できる在庫管理表は、たしかに便利な点があります。スグに使えて、必要項目が入力されているのは、魅力的です。

しかし、無料の在庫管理表では、あなたの業務に、そのまま使えるとは限りません。むしろ、ほとんど場合、使えないでしょう。もしあなたが、入手した在庫管理表のポテンシャルを完全に発揮させたいなら、在庫管理表を自力でアレンジできる力が必要です。

アレンジする力があれば、エクセル関数を変更したり、マクロを入れ込んで、業務の大半を、入力ミスなしで、しかも自動化することができます。

ですので、無料テンプレートに頼りつつ、自力で編集する力を付けるのが、使いやすい管理表を作るための近道です。

管理表は、エクセル関数よりエクセルマクロVBAで作った方が便利!その3つの理由

エクセルで、在庫管理表を作るために、必要なスキルとして、すぐに思い浮かぶのが、エクセル関数とエクセルマクロVBAです。

もし、エクセルマクロVBAという言葉を初めて聞いた方は、こちらの記事で詳しく紹介していますので、合わせて読んでみてくださいね。

www.fastclassinfo.com

エクセル関数を利用している在庫管理表が多いですが、実はエクセルマクロ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|習得難易度は、エクセル関数と難易度はあまり変わらない

一般的に、エクセルマクロとエクセル関数を比べたら、エクセルマクロは習得が難しそうと思われがちです。しかし、そんなことはありません。

実は、エクセルマクロとエクセル関数と難易度はあまり変わりません。

たとえば、以下の画像のように

f:id:gene320:20170624020529p:plain

①で記載されている氏名を
②のように分離するとします

エクセル関数を使うと

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つを選択しました

A列|品目ID:事前に入力
B列|品名:事前に入力
C列|最低保管在庫:事前に入力
D列|現在の在庫数:記入不要(自動更新される)

f:id:gene320:20170624013207p:plain

新しい商品が増えたら、この品目表に追加していきます。

作り方2|シート「入出庫表」に必要項目を入れ込む

シート「入出庫表」に必要となる項目を入力します。

この記事では、以下の手順で入力します。

①|シート「入出庫表」を選択
②|入庫か出庫が発生したら、[摘要~出庫数]のセルに情報を入力
③|記入日時~状況のセルは記入不要(自動更新される)
④|新しく入庫か出庫が発生したら、新たに情報を入力
⑤|「更新」ボタンを押す(自動処理開始)

f:id:gene320:20170624013221p:plain

動画をご覧になった方は、お気づきかもしれませんが、「更新」ボタンには、マクロが起動するように設定しています。これで、ボタン一つで、処理が終わるようになっています。

もし、マクロを起動する方法や設定方法が分からない場合は、こちらの記事の中で、紹介していますので、合わせて読んでみて下さい

www.fastclassinfo.com

「更新」ボタンを押したら、情報が読み込まれて、更新された行は自動で灰色になる

f:id:gene320:20170624013235p:plain

また、「更新」ボタンを押すと、シート「品目表」も更新されます

そのとき、「D列|現在の在庫数」が「C列|最低保管在庫」を下回ると、行が自動で黄色になります。

黄色に変化することで、アラート機能の役割を果たします。

f:id:gene320:20170624013331p:plain

作り方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を学んで、メンドウな業務をシステム化しましょう。