エクセルで在庫管理をする方法 【テンプレート付き】

こんにちは、「仕事を楽に回そう」デスクワークラボの吉井良平です。

 

ヤフオクに出品されている方から、「在庫管理ファイルを作って欲しい」という依頼があって作成したのですが、エクセルで在庫管理ファイルを作るのは、簡単なようで結構難しいのです。

基本的なやり方を知らずに作ると、必ずと言って良いほど在庫が合わなくなるので、今回は在庫管理ファイルを作る方法について書いてみます。

 

エクセルのマクロ機能を使うと、市販ソフトに負けないぐらいのものが出来ますが、マクロを使わない場合の作り方も書いています。

関数も結構複雑なので、テンプレートをダウンロードできるようにしています。在庫管理ファイル作成のご参考になれば幸いです。 

エクセルで在庫管理する際の注意点

 

在庫管理をきっちりするためには、販売管理ソフトを導入すれば良いのですが、販売管理ソフトはそれなりの値段がします。

なので、エクセルで作ろうという要望があるのは当然で、実際にエクセルの方が小回りが利いて良い業務もあります。

でもエクセルで在庫管理をするのは、なかなか難しいのです。

 

在庫とは?

 

そもそも在庫とは何かというと、式で表すとこうなりますよね。

在庫 = 入庫 - 出庫

モノが入ってきて、出ていく間までの状態が在庫です。

別の言葉を使うと、次のようにも表現できます。

在庫 = 仕入れ - 販売

商品が入ってきて、売れるまでの間が在庫です。売れるだけじゃなくて、廃棄ロスがある場合もあります。

工場の場合は、材料を仕入れてから、生産に使うまでが在庫ですね。

在庫というのは入ってきたものと出ていくものの差数なので、「在庫数を管理する」ためには、入庫数と出庫数を管理する必要があります。

在庫数だけを管理することはできないのです。

上書きしてはダメ!

 

在庫だけを管理しようとして、次のような在庫管理表を作られている方もいます。

0301

・商品が売れたら残数をコピーして、在庫数に上書き

・商品が入庫したら、在庫数に入庫数を足して上書き

 

これはこれでシンプルなやり方なのですが、上書きをしていると履歴が残らないので、「あれ、これ上書きしたかな??」ということが、絶対と言って良いほど起こります。

間違えていたとしても、履歴が残ってないので調べようがなく、また一から在庫を数えなおす・・・という、とんでもない手間になります。

「何が何個入ってきたか」「何が何個出て行ったのか」という履歴が残るやり方で、在庫管理表は作っていかないといけないのです。

 

関数を使った在庫管理表の例

 

在庫管理をエクセルで手軽に行うためには、マクロ機能を使った方が良いのですが、「マクロは苦手」という方のために、関数を使ったやり方を2つ紹介します。

使ってる関数も少し複雑なので、サンプルファイルをダウンロードして確認してみてくださいね。

 

SUMIF関数を使った例

 

単純に、入庫した品番と数量、出庫した品番と数量をどんどん入力していって、在庫管理表でSUMIF関数を使って集計した例です。

ダウンロード

概要

まず、入庫シートに、入庫した品番と数量をどんどん入力していきます。

同様に、出庫した品番と数量も、出庫シートにどんどん入力していきます。

明細が増えていっても、後から集計範囲を変更しなくて良いように、COUNTA関数でデータの個数を集計しておきます。

在庫管理1

入庫シートの例

 在庫管理表では、入庫シートから総入庫数、出庫シートから総出庫数をSUMIF関数で集計して、その差数から在庫数を求めています。

在庫管理2

在庫管理表

関数が長くなりすぎて、嫌になる方もいるかもしれませんが^^入庫や在庫のデータが増えていっても大丈夫なように、SUMIF関数の中にOFFSET関数というのを入れています。

OFFSET関数は、指定したセルから X行、Y列 離れたセルを求める関数です。

計算式は、OFFSET(開始セル,X,Y) で、SUMIF関数の範囲の最終セルを「COUNTA関数で求めたデータの個数分離れた位置」という指定にします。

こうしておけば、データが増えたとしてもSUMIF関数の集計範囲を変更しなくて良いので楽です。

 

良いところと悪いところ

 

この方法の良いところは、運用がいたってシンプルという点です。入庫した数量、出庫した数量をどんどん入力すれば良いだけなので、誰にでも運用ができます。

また、在庫が合わなくなったとしても、「いつの入力分にミスや漏れがあるか」というのが後から検証できます。

悪いところとしては、入庫した数量、出庫した数量を品番ごとに入力していく必要があるので、入力に時間がかかる点です。

 

VLOOKUP関数を使った例

 

先ほどの例は、正確ですが手間がかかるということで、少し入力を楽にしたバージョンも作りました。

リスト形式の表に、日付と個数を入力していくタイプです。

ダウンロード

概要

 

入庫シートは表形式になっていて、入庫した日付と数量を入力していきます。

同様に、出庫シートも表になっていて、出庫した日付と数量を入力していきます。

日付が増えていっても、集計範囲を変更しなくて良いように、COUNTA関数でデータの個数を集計しておきます。

C列には、COUNTAで数えた列数分をSUM関数で集計しておきます。

在庫管理表3

出庫シートの例

 

在庫管理表の方では、入庫シート、出庫シートで集計された値をVLOOKUP関数で引っ張って来て、差数を求めています。

在庫管理表4

在庫管理表

 

良いところと悪いところ

この方法の良いところは、先ほどの管理表よりも品番を入力しなくて良いだけ、入力が楽になっています。

悪いところとしては、品番と数量の入力位置が離れているので、入力ミスが起こりやすい点です。(違う品番の行に入力してしまう可能性がある)

 

こんなことも出来ます

 

明細を入力するのは面倒と思うかもしれませんが、明細を入力することで出来ることもあります。

出庫明細をつけ始めた日から今日までの経過日数を算出しておいて、出庫数を経過日数で割ると、1日あたりの平均出荷個数が出てきます。

在庫数を平均出荷個数で割ると、「今の在庫が何日分か」が求められるため、「在庫が1週間分を切ったら追加発注」などのルールを作ることができます。

発注済み未入荷のシートも作れば、立派な在庫管理表の出来上がりです。

在庫管理表5

 

マクロを使った在庫管理表の作成例

 

関数だけで作ると、入力に時間がかかったり、入力にミスが出る可能性があります。

かんたんなマクロを使うと、これらの欠点を解消することができます。

サンプルファイルは、下記のフォームからダウンロードできます。ある程度は、これで実用に耐えるはずです。

(フォームをダウンロードすると、デスクワークラボのメルマガ会員に登録されます。メルマガの解除はワンクリックでできますので、すぐに解除していただいても構いません。)

お名前 (必須)

メールアドレス (必須)

 

概要

 

入庫シートに、入庫数を入力して登録ボタンを押します。

入庫数の入力ミスが無いよう、品番と数量の入力欄は隣り合わせにしています。

在庫管理表6

入庫数を入力して登録ボタンを押す

 

在庫登録ボタンを押すと、当日の日付で入庫明細が追記されます。

また、在庫管理表の在庫数に、入庫数が追加されます。

(新規品番の場合は、在庫管理表に品番を追加しておいてください。このダウンロードファイルでは、在庫管理表に品番が自動追加される仕様にはなっていません)

 

出庫する場合は、在庫管理表に数量を入力して、登録ボタンを押します。

「在庫が何個あって、そのうち何個使ったか」ということが目に見えて分かるので、在庫が無くなった際のアクションもしやすいです。

在庫管理表7

数量を入力して登録ボタンをおすだけ

出庫登録ボタンを押すと、現在個数が出庫済みの数量に置き換わります。

また、当日の日付で出庫明細に追加されていきます。

 

マクロを使うことで、シンプルで使いやすい在庫表が出来上がりました。

 

まとめ

 

在庫管理をエクセルで行うためには、ちょっとしたテクニックが必要です。

マクロに関しては、作り方を説明すると長くなるので作り方は割愛しましたが、そんなに難しいテクニックは使っていません。

マクロを使ってみたい方は、「ノンプログラマーのためのエクセルマクロ入門」記事をご覧くださいね。

やろうと思えば、出庫伝票を作ったり、月別に請求書を作ったりと発展させることができます。

在庫管理を確実に行うためには、出庫伝票を作って「伝票とモノをセットにする」などのルールを作った方が良いので、やはりマクロを使った方が良いでしょう。

(マクロを使った在庫管理の作成例。納品書、請求書と連動させています。)

 

なお、今回紹介したテンプレートは、あくまでサンプルとして作ったものです。

「もっと自分にあったものを作りたい、でも作り方が分からない」という方は、下記のボタンからお問合せくださいね。

なお、私の方でカスタマイズも行います。カスタマイズの目安の金額を入れておきますので、ご検討ください。

項目 価格 内容
商品別在庫回転率 ¥6,000 一定期間に在庫が何回転したかを算出
在庫日数 商品別に、現在何日分の在庫があるのかを算出
在庫金額算出 在庫がいくらあるのかを算出
先入れ先出し管理 先に入庫した分からの消し込み
発注書 在庫が少ない商品を選んで発注書を発行する
発注残の消し込み 発注済商品が入庫したら発注残を消し込む
納品書作成 出庫した商品の納品書を自動作成する
顧客別納品履歴 顧客別に出庫数を管理する
取引先別仕入履歴 取引先別に仕入数を管理する
バーコード読み取り バーコードと商品マスタを照合して品番を取得する
お問い合わせ

では、在庫管理を確実に行って、業務効率を向上させましょう!

 

追伸)こちらの記事をご覧になった方は、SKUの作成に関する記事も参考にしてみて下さい^^

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

ABOUTこの記事をかいた人

昭和48年広島県生まれ。現在も広島に住んでいます。 趣味は音楽鑑賞(邦楽、洋楽問わず、クラシック、童謡も)、マンガです。エクセルを効率的に使う方法を紹介して、あなたのお役に立ちたいと思っています。