こんにちは、「仕事を楽に回そう」デスクワークラボの吉井良平です。
ヤフオクに出品されている方から、「在庫管理ファイルを作って欲しい」という依頼があって作成したのですが、エクセルで在庫管理ファイルを作るのは、簡単なようで結構難しいのです。
基本的なやり方を知らずに作ると、必ずと言って良いほど在庫が合わなくなるので、今回は在庫管理ファイルを作る方法について書いてみます。
エクセルのマクロ機能を使うと、市販ソフトに負けないぐらいのものが出来ますが、マクロを使わない場合の作り方も書いています。
関数も結構複雑なので、テンプレートをダウンロードできるようにしています。在庫管理ファイル作成のご参考になれば幸いです。
エクセルで在庫管理する際の注意点
在庫とは？
そもそも在庫とは何かというと、式で表すとこうなりますよね。
在庫 = 入庫 - 出庫
モノが入ってきて、出ていく間までの状態が在庫です。
別の言葉を使うと、次のようにも表現できます。
在庫 = 仕入れ - 販売
商品が入ってきて、売れるまでの間が在庫です。売れるだけじゃなくて、廃棄ロスがある場合もあります。
工場の場合は、材料を仕入れてから生産に使うまでが在庫（材料在庫）、生産が完了してから出荷するまでが在庫（製品在庫）です。
在庫というのは入ってきたものと出ていくものの差数なので、「在庫数を管理する」ためには、入庫数と出庫数を管理する必要があります。
在庫数だけを管理することはできないのです。
上書きしてはダメ！
在庫だけを管理しようとして、次のような在庫管理表を作られている方もいます。
・商品が売れたら残数をコピーして、在庫数に上書き
・商品が入庫したら、在庫数に入庫数を足して上書き
これはこれでシンプルなやり方なのですが、上書きをしていると履歴が残らないので、「あれ、これ上書きしたかな？？」ということが、絶対と言って良いほど起こります。
間違えていたとしても、履歴が残ってないので調べようがなく、また一から在庫を数えなおす・・・という、とんでもない手間になります。
「何が何個入ってきたか」「何が何個出て行ったのか」という履歴が残るやり方で、在庫管理表は作っていかないといけないのです。
関数を使った在庫管理表の例
在庫管理をエクセルで手軽に行うためには、マクロ機能を使った方が良いのですが、「マクロは苦手」という方のために、関数を使ったやり方を2つ紹介します。
使ってる関数も少し複雑なので、サンプルファイルをダウンロードして確認してみてくださいね。
SUMIF関数を使った例
単純に、入庫した品番と数量、出庫した品番と数量をどんどん入力していって、在庫管理表でSUMIF関数を使って集計した例です。
概要
まず、入庫シートに、入庫した品番と数量をどんどん入力していきます。
同様に、出庫した品番と数量も、出庫シートにどんどん入力していきます。
明細が増えていっても、後から集計範囲を変更しなくて良いように、COUNTA関数でデータの個数を集計しておきます。
在庫管理表では、入庫シートから総入庫数、出庫シートから総出庫数をSUMIF関数で集計して、その差数から在庫数を求めています。
関数が長くなりすぎて、嫌になる方もいるかもしれませんが＾＾入庫や在庫のデータが増えていっても大丈夫なように、SUMIF関数の中にOFFSET関数というのを入れています。
OFFSET関数は、指定したセルから X行、Y列 離れたセルを求める関数です。
計算式は、OFFSET(開始セル,X,Y) で、SUMIF関数の範囲の最終セルを「COUNTA関数で求めたデータの個数分離れた位置」という指定にします。
こうしておけば、データが増えたとしてもSUMIF関数の集計範囲を変更しなくて良いので楽です。
良いところと悪いところ
この方法の良いところは、運用がいたってシンプルという点です。入庫した数量、出庫した数量をどんどん入力すれば良いだけなので、誰にでも運用ができます。
また、在庫が合わなくなったとしても、「いつの入力分にミスや漏れがあるか」というのが後から検証できます。
悪いところとしては、入庫した数量、出庫した数量を品番ごとに入力していく必要があるので、入力に時間がかかる点です。
VLOOKUP関数を使った例
先ほどの例は、正確ですが手間がかかるということで、少し入力を楽にしたバージョンも作りました。
リスト形式の表に、日付と個数を入力していくタイプです。
概要
入庫シートは表形式になっていて、入庫した日付と数量を入力していきます。
同様に、出庫シートも表になっていて、出庫した日付と数量を入力していきます。
日付が増えていっても、集計範囲を変更しなくて良いように、COUNTA関数でデータの個数を集計しておきます。
C列には、COUNTAで数えた列数分をSUM関数で集計しておきます。
在庫管理表の方では、入庫シート、出庫シートで集計された値をVLOOKUP関数で引っ張って来て、差数を求めています。
良いところと悪いところ
この方法の良いところは、先ほどの管理表よりも品番を入力しなくて良いだけ、入力が楽になっています。
悪いところとしては、品番と数量の入力位置が離れているので、入力ミスが起こりやすい点です。（違う品番の行に入力してしまう可能性がある）
こんなことも出来ます
明細を入力するのは面倒と思うかもしれませんが、明細を入力することで出来ることもあります。
出庫明細をつけ始めた日から今日までの経過日数を算出しておいて、出庫数を経過日数で割ると、1日あたりの平均出荷個数が出てきます。
在庫数を平均出荷個数で割ると、「今の在庫が何日分か」が求められるため、「在庫が1週間分を切ったら追加発注」などのルールを作ることができます。
発注済み未入荷のシートも作れば、立派な在庫管理表の出来上がりです。
マクロを使った在庫管理表の作成例(無料テンプレートダウンロードできます)
関数だけで作ると、入力に時間がかかったり、入力にミスが出る可能性があります。
かんたんなマクロを使うと、これらの欠点を解消することができます。
サンプルファイルは、下記のフォームからダウンロードできます。ある程度は、これで実用に耐えるはずです。
（取得した個人情報については、個人情報保護法に則って、適切に対応します。）
※Gmailの方は、迷惑メールに判定されることもありますので、届かない場合は迷惑メールフォルダもご確認ください。
概要
入庫シートに、入庫数を入力して登録ボタンを押します。
入庫数の入力ミスが無いよう、品番と数量の入力欄は隣り合わせにしています。
在庫登録ボタンを押すと、当日の日付で入庫明細が追記されます。
また、在庫管理表の在庫数に、入庫数が追加されます。
（新規品番の場合は、在庫管理表に品番を追加しておいてください。このダウンロードファイルでは、在庫管理表に品番が自動追加される仕様にはなっていません）
出庫する場合は、在庫管理表に数量を入力して、登録ボタンを押します。
「在庫が何個あって、そのうち何個使ったか」ということが目に見えて分かるので、在庫が無くなった際のアクションもしやすいです。
出庫登録ボタンを押すと、現在個数が出庫済みの数量に置き換わります。
また、当日の日付で出庫明細に追加されていきます。
マクロを使うことで、シンプルで使いやすい在庫表が出来上がりました。
エクセル在庫管理システムのカスタマイズ例(動画あり)
在庫管理を確実に行うためには、出庫伝票を作って「伝票とモノをセットにする」などのルールを作った方が良いので、やはりマクロを使った方が良いでしょう。
（マクロを使った在庫管理の作成例。納品書、請求書と連動させています。）
またその他にも業種別の作成事例がありますので、ご興味のある方は下記よりご確認ください。
なお、今回紹介したテンプレートは、あくまでサンプルとして作ったものです。
「もっと自分にあったものを作りたい、でも作り方が分からない」という方は、下記のボタンからお問合せくださいね。
アプリを使う場合
販売管理ソフトを導入すれば良いのですが、販売管理ソフトはそれなりの値段がします。
（2019年10月追記：ZAICOというアプリは、安価でかなり良いです。エクセルで作るのは結構大変なので、アプリを使うことも検討した方が良いと思います。）
（2023年12月追記：ノーコードのアプリを使うのもお勧めです。弊社では、「ピジョンクラウド」というアプリを勧めています。ご興味がありましたら、「ピジョンクラウドを使って生産管理システムを安価に作った事例」記事をご参照ください）
なので、エクセルで作ろうという要望があるのは当然で、実際にエクセルの方が小回りが利いて良い業務もあります。
まとめ
在庫管理をエクセルで行うためには、ちょっとしたテクニックが必要です。
マクロに関しては、作り方を説明すると長くなるので作り方は割愛しましたが、そんなに難しいテクニックは使っていません。
やろうと思えば、出庫伝票を作ったり、月別に請求書を作ったりと発展させることができます。
では、在庫管理を確実に行って、業務効率を向上させましょう！
こちらの記事をご覧になった方は、SKUの作成に関する記事も参考にしてみて下さい＾＾
