在庫管理アドバイザーの岡本です。
在庫管理の実務経験のある、在庫管理の専門家が、在庫管理表をエクセルで作る際のポイントと手順、そして在庫管理の方法を分かりやすく解説します。
形だけの在庫管理表はたくさんありますが、なぜそれが良いのかという理由まで知っておけば、自社仕様に改造できます。
また、今回例として取り上げるどんな業界でも使えるオリジナル在庫管理表を３種類無料で配布します。
ぜひ、自社の在庫管理にご利用ください。
在庫管理110番の無料在庫管理テンプレートの特徴は以下の通りです。
- 業種を問わない：製造業や卸売業、小売業に限らず飲食店や薬局、アパレルなどでも使えます。
- 複数商品が管理可能：１シートで複数の商品を簡単に管理できます。
- 発注管理ができる：欠品しそうな商品が一目でわかります。
- 見やすい：必要な機能をコンパクトにまとめてシンプルで分かりやすい。
- 誰でも使える：知識のないパートやアルバイトでも簡単に使えます。
一見とてもシンプルですが、私のノウハウすべてを注ぎ込み重要なポイントをしっかりと押さえました。
簡単な在庫管理ならエクセルで十分対応できます。今回は難しい関数を使わずに誰でも作れる在庫管理表の作り方と活用方法を説明します。
実務ですぐに使える
在庫管理表の他にも在庫管理に役立つフォーマット配布中
基本的な考え方を知ったうえで、小規模な企業（１～３人）であれば運用さえ工夫すれば大がかりな在庫管理システムやバーコード管理をしなくても大丈夫です。４人以上の企業や、今後もずっと在庫管理を行っていく予定であれば、早いうちに在庫管理システムを導入しても良いでしょう。詳しくはこちらをご覧ください。
☑「エクセル在庫管理表」は、どれくらいの規模までが限界なのかをチェックする
☑在庫管理の専門家が作ったクラウド型在庫管理システム「成長する在庫管理」
在庫管理表を導入して、入出庫管理や欠品の検出、発注管理を簡単にしましょう。
今回ご紹介する在庫管理表は、簡単なエクセル関数だけで構築してます。
在庫管理表を作ったことのない初心者でも簡単に作れます。また、難しい関数やマクロも一切使っておらずシンプルなので、自社仕様にカスタマイズしていただくこともできます。
もくじ
在庫管理表はシンプルでなければいけない
まず、最初に在庫管理アドバイザーの支援で在庫管理表を改善したクライアントの声をご紹介します。
以前の在庫管理表を改善された今回のものに変更して何が変わりましたか？
- 書類やデータを複数見なくてもよくなった
- 作業時間が改善された
- 管理しやすくなった
- 慣れた作業者だけでなく、営業担当、事務員が見ても分かるようになった
改善されたことで、具体的に何が変わりましたか？
これまでは、詳しい作業者に聞かないと、在庫数がつかめず、また在庫数間違いも多かったが、今回の在庫管理表の改善で、
- 作業者が時間を時間を書けていた部分を、慣れていない事務員でも対応できるようになり作業時間短縮になった。
- 間違いがあったとしても、間違い自体の把握も即時できるようになり、都度修正が可能になった。
囲今回の在庫管理表の改善によって、得られた効果を金額または時間換算するとどれくらいになりましたか？
- 毎日行う在庫管理作業が３０分→１５分に短縮
- 月末に行う在庫管理作業が１２０分→４０～６０分に短縮
他に欲しい機能はありますか？
- １つの商品を入力したら、その商品にセットまたは付属している複数の部材の在庫数が自動で変動する
- 入力・編集の履歴が見れる。（今は、入力結果しか見えない。履歴が見えれば操作ミスが見える化できるため）
- エクセルがもっと早く動いてほしい（関数やデータが多くて重たい。特にパソコンが苦手な人は戸惑ってしまい、ミスを誘発する
この会社では、元々在庫管理をしている作業者がエクセルを使って独自で作った在庫管理表で管理していました。
しかし、１つのファイルに10以上のシートがあり、関数が複雑に絡み合っていました。
さらに１つのシートにも情報が満載で、つくった人にしか見方が分からない状態。（在庫数すら、わからない）
さらに最悪なことに、扱う商品が増えた時に、商品を追加しなければいけないのですが、その処理ができるのは、つくった本人のみ。しかも２時間以上かかります。
在庫管理担当者も忙しいので、商品の追加が後回しになってしまい、ますます分からなくなってしまっていました。その結果、ミスも増え、時間が足りなくなる悪循環が起こっていました。
作った本人すら、つくった在庫管理表をメンテナンスできない状態で、他の人は怖くて触れなくなっていました。
今回、今回在庫管理110番が推奨する在庫管理表に切り替えたところ見る見るうちに業務が改善しました。
- 在庫確認で現場やほかの書類を何度も確認しなくて済むようになった
- 他の部署の担当者と在庫情報を共有できるようになった
- 作業時間の短縮ができるようになった
と在庫管理表を見直すだけで色々な効果が現れました。
それでは業務を劇的に改善できる在庫管理表の作り方を解説します。
誰でも使える分かりやすいエクセル在庫管理表の作り方
属人化（ベテランや特定の担当者しか使えない）ではなく、誰でも使えるエクセル在庫管理表の作り方をご説明します。
この会社に提供した在庫管理表は在庫管理１１０番が推奨する「３欄式在庫管理表」です。（在庫管理１１０番が命名したオリジナルの在庫管理表です！）
完成形はこんな感じです↓
この在庫管理表の特徴は、
- 何が、いくつあるかがすぐにわかる！
- 在庫が少なくなってきたら分かる！
- 一元管理が楽にできる
- 管理したいものを追加・削除しやすい
- なんといっても無料
私が現場でメインで使っていた在庫管理表です。
これ１つで1000点以上の部品を管理していました。
この３欄式在庫管理表は「入出庫管理」だけにとどまらず、欠品の検出機能付きです。
安全在庫を設定すれば、安全在庫を切った在庫が赤く表示され補充が必要なことが一目でわかる仕組みです。
１０００種類以上の商品でも簡単に管理ができますし、しかもパートさんのように商品知識がない人でも使いこなすことができます。
（実際に、現場でこの在庫管理表をメインで使っていたのは、３名のパートさんでした）
エクセルで自作すれば無料なので、お金もかかりません。
在庫管理110番では今回、作り方を解説する在庫管理表の完成版をダウンロードできるようにしていますので、ぜひ使ってみてください。
解説を見ても作り方がよくわからない場合は、完成版を見ながらだとわかりやすいのでお勧めです。
先ほどダウンロードしたエクセル在庫管理表をご覧になりながら作り方を確認してください。
在庫管理フォーマット（骨格）を作成する
alt=”在庫管理表フォーマット_誰でも使える分かりやすいエクセル在庫管理表の作り方” width=”1582″ height=”291″ />
まず、上記のようなフォーマットを作成してください。
品目（商品や部品）情報として、
- 品名
- ロット（発注ロット：発注する際の単位数量のこと）
- 安全在庫
入庫・出庫・在庫を記入する欄
繰り越し在庫を入力する欄
在庫数の計算は、「前日在庫＋当日入庫数－当日出庫数」が基本です。
（仮に１か月間の在庫を計算する場合は、月初の在庫数は前月末の在庫数になります。）
最後に、在庫管理表の期間を決めます。
なお、在庫管理期間は今回の例では１か月分で締めることを想定していますが、期間は自由です。（１週間とか１年でも構いません）
在庫数の計算式を追加する
この在庫管理表では、関数を一切使いません。関数が苦手だな・・・と思っている方でも簡単に安心して作れます。
下記のような計算式を加えます。
在庫数の計算は、「前日在庫＋当日入庫数－当日出庫数」なので、
参考までにセルの番号を当てはめると、前日在庫(D7)＋当日入庫数(E4)－当日出庫数(E5)
この在庫管理表を見て、「仕損」というものがあることに気づいたのではないでしょうか？
これは、予期せぬ出庫を入力する欄です。予期せぬ出庫とは、
- 破損
- 歩留まり
- 不良品の除外
などです。
通常の出庫数と分けている理由は、需要と仕損は違うからです。もし、これを一緒にしてしまうと本当にどれだけ使ったかが分からなくなります。
これを加えると在庫数の計算式は、
「前日在庫＋当日入庫数－当日出庫数－仕損数」なので、
セルの番号を当てはめると、前日在庫(D7)＋当日入庫数(E4)－当日出庫数(E5)ー仕損数(E6)
になります。
計算式を一つ入力後、数字を入れて間違っていないかどうかを確認してみましょう。
その後、コピーして計算式を在庫管理表の期間全てに適用します。
これで、骨格は完成です。
品番が増えた時の在庫管理表のメンテナンス方法はとても簡単
もし管理したい商品が増えた時は、今回作ったフォーマットをコピーして下に貼り付けるだけです。
行ごとコピーして、一番下に貼り付けます。
貼り付けた後に、品目番号、品名、繰り越し在庫などを修正します。
この方法であれば、管理したい商品を簡単に増やせます。（２時間もかかりませんし、一度覚えればパートさんでも誰でもできるくらい簡単です）
【無料】在庫管理個別相談受付中
欠品を防ぐ発注点（安全在庫）を一目でわかるようにする
在庫管理表は、
- フォーマットを作る
- フォーマットに式を加える
だけで良いですが、もう１点欲しい機能があります。
それは、欠品を防ぐ機能です。管理する在庫が数点（10点以下）であれば、しっかりと全部見ることができるでしょう。
しかし、増えてくると、必ず見落としが発生します。逆に見落とさないように！！と思って頑張ってみようとすると、時間がかかります。
例えば、在庫数が１０個以下になったら、在庫数セルが赤色になるように設定します。（発注点・安全在庫を１０個に設定）
これを知っていれば、時間をかけずに見落としが無くなり、欠品を防げます。
これを実現するエクセルの機能は、「条件付き書式」です。条件付き書式を使えば、こんな感じで色が自動でつくので一目でわかります。
条件付き書式の設定
条件付き書式とは、ある条件を満たした時に、セルの色を変えたり、フォントを太くしたりする機能です。
「見た目」が自動で変わるので、しっかり＆じっくりと見なくても見落としが無くなるので、ぜひ覚えておいていただきたい機能です。
まず、繰り越し在庫以外の在庫のセルを全て選択します。
次に条件付き書式を設定します。下記の図の番号順にクリックしていってください。
在庫数が安全在庫以下の数量になったら、セルが赤く塗りつぶしされるようにします。
この時、Bの前に必ず「＄」を付けるのを忘れないでください。
もし、F４を押してもうまくいかない場合は、shiftキーを押しながら数字の４を押すと「＄」が入力できます。
これで、在庫が設定した安全在庫数量以下になったときに、色が自動で変わるフォーマットが出来上がりました。
管理する商品や部品が増えた時は、「品番が増えた時の在庫管理表のメンテナンス方法はとても簡単」で説明した方法でコピーしてください。
条件付き書式も自動でコピーされます。
【無料】在庫管理個別相談受付中
シンプルで使いやすい在庫管理表無料テンプレートを5種類ご紹介
今回、ご紹介した３欄式在庫管理表の他にも、４種類の無料在庫管理表テンプレートをご用意しました。
今回は５種類の在庫管理表をご紹介します。
- ６欄式在庫管理表
- ３欄式在庫管理表
- 累積式在庫管理表
- 複数の保管場所がある在庫管理表
- 手書き用在庫管理表
在庫管理の基本を押さえていますので、業種を問わずご利用いただけます。用途に合わせてご利用ください。在庫管理は厳密に、細かくすればするほど間違えにくく、状況がよくわかります。しかし、在庫管理を細かくすれば、それだけ在庫管理をしている現場や事務所の負担が大きくなり、時間がとられてしまいます。ABC分析などを行い、在庫の管理の重要度に応じて、管理方法と在庫管理表を選択すると良いです。
［１］６欄式在庫管理表
６欄式在庫管理表は、今回の中で最も詳細に在庫管理ができる在庫管理表です。
次の項目を一元管理できます。
- 注文残
- 入庫
- 出庫
- 引当
- 引当残
テンプレートは以下のような形式になります。
１つの品目を注文から実際の引当まで細かく管理することができます。
しかし、管理する品目点数が多いと作業量も増え、事務処理に負担がかかります。重点的に管理をしたい部品や商品に限って使うのが良いでしょう。
［２ー１］３欄式在庫管理表（入出庫管理）
３欄式在庫管理表は、最もスタンダードな在庫管理表です。在庫管理の３つの基本項目を管理します。
- 入庫数
- 出庫数
- 在庫数
テンプレートは以下のような形式になります。
３欄式在庫管理表は、日付ごとに入庫数・出庫数・在庫数だけを行・列で管理するきわめてシンプルな在庫管理表です。
私が実務においてメインで使っていた在庫管理表です。これ１つで1000点以上の部品を管理していました。
現品管理に向いていて、表示においては一覧性も高いので、使いやすいエクセル管理表です。
また、今回提供しましたこの３欄式在庫管理表は「入出庫管理」だけにとどまらず、欠品の検出機能付きです。
安全在庫を設定すれば、安全在庫を切った在庫が赤く表示され補充が必要なことが一目でわかる仕組みです。
下記の［３］［４］のように「入出庫管理＆歩留まり」、「発注管理」にも応用できますので大変便利な在庫管理表です。
商品点数の多い薬局や飲食店の食材管理にも応用できます。
［２－２］３欄式在庫管理表（入出庫管理＆歩留まり）
３欄式在庫管理表に仕損数を加えたものです。次の項目を一元管理できます。
- 入庫数
- 出庫数
- 在庫数
- 仕損数
テンプレートは以下のような形式になります。
出庫数と在庫数の間に仕損数という項目があります。
この仕損数にはイレギュラーな出庫を記録します。例えば、
不良や歩留まり等の意図せずに起こる出庫数を入力します。
通常の出庫数と分けることでイレギュラーな出庫がどれくらい起こっているかということが分かります。
イレギュラーな出庫数が管理できるので、改善して減らす活動にも活用できます。
また、液体などは、蒸発して日常的に在庫が減るということも考えられます。それを「仕損数」に記録して都度修正して、在庫数を適正に保つことができます。
［２－３］３欄式在庫管理表（発注残管理）
３欄式在庫管理表に入出庫の実績だけではなく、先々の入庫数・出庫数を入れることで簡易的な生産・仕入計画ができます。
実績だけであれば、過去から現在までの在庫数の推移しかわかりませんが、将来の予定数を入れることで、いつ頃在庫が切るかということまで管理できます。これを利用すれば、過剰な発注・発注漏れによる欠品を防ぐことができます。
［３］累積式在庫管理表（金額管理）
累積式在庫管理表は、これまでの累積と在庫金額が記録できる在庫管理表です。
- 仕入単価
- 入庫数
- 累積入庫数
- 出庫数
- 累積出庫数
- 在庫数
- 在庫金額
テンプレートは以下のような形式になります。
また、あえて金額を見せることで、在庫の大切さを理解してもらうことができます。「在庫＝お金」ですが、どんなに高価な在庫でも、社員にとって自分のものではないので管理がおおざっぱになることがあります。そこで、仕入単価を公開して、在庫金額を書きます。これだけの金額の在庫があるのか・・・と思ってもらえれば、大切に扱ってくれるようになります。
［４］複数の保管場所がある在庫管理表
在庫の保管場所が複数ある場合の在庫管理用書式です。次の項目を一元管理できます。
- 各倉庫別の入庫数
- 各倉庫別の出庫数
- 在庫数（各倉庫別の在庫数および総在庫数）
テンプレートは以下のような形式になります。
全体の在庫が100個あっても、保管場所が複数あればどこに、何が、いくつあるのかという情報がなければ意味がありません。そこで、保管場所別に在庫管理をしつつ、全体の在庫を見れて検索することができる在庫管理表を作りました。
同一品が複数の場所にあることを管理できるメリットは、探し回らなくて済むことです。
例えば次のような例です。
在庫が１００個あると言っても、保管してある倉庫がバラバラだと探し回らければいけません。
１つの品番が複数の場所に保管される可能性があるのであれば、場所ごとに管理することをお勧めします。エクセルの在庫管理システムであれば、表計算・集計機能を使って拠点ごとの管理が可能です。
複数場所の管理が必要な場合、倉庫・工場ごとにエクセルで管理するよりも、できれば在庫管理システムを導入したほうが良いでしょう。作業のミスや負担の軽減、効率化を考えるなら、在庫管理システムによって手間を省くことができます。
[ 5 ]手書き用在庫管理票
実務ですぐに使える
在庫管理表フォーマットをまとめて入手する
※エクセル在庫管理表の実践的なノウハウが全て詰まった【教材：在庫管理の教科書】について知りたい方はこちら
在庫管理表を自作する際の注意点
今回は弊社の作成した在庫管理表をご紹介しました。
今回の管理表をベースに自社で在庫管理表を作ってみよう！と思ったときに注意する点をまとめました。
絶対に作ってはいけない在庫管理表
私は、在庫管理について多くの会社様から個別相談をいただく中で、いろいろな業種・業態の企業の在庫管理表を見る機会がありますが、「もう、在庫管理の仕方が複雑すぎて、目の回るような在庫管理表が多い」と、毎回驚きます。
問題になっている在庫管理表の６つの共通点です。（どれか１つでも当てはまると要注意！）
- 作った本人しか分からない
- 作った本人ですら分からなくなる
- 担当者不在の場合、業務が止まる
- 継ぎ足し、継ぎ足しで作っていて複雑化している
- シートが大量にあり複雑化している
- 複数の機能を１つにまとめている
- 入力間違えをしても、どこにミスがあったのか気づけない
しかも、データのあちこちで「こちらを参照」という注釈が多く、間違いが起こっても一体どこが間違っているのか非常に分かりにくく、せっかくのデータが役に立たなかったり、データの間違い探しに膨大な時間がかかっています。そもそも、在庫管理の基本を理解していないため、複雑で使えない在庫管理表になってしまっているのです。
絶対に心がけていただきたいのは、「見やすさ」、「わかりやすさ」です。在庫管理表に多くの要素は必要ありません。
在庫管理表を作る時に押さえることはたった２つだけ
逆に、在庫管理表で押さえるべきことは意外と少なくて良いのです。
在庫管理表で一番大切なのは、
- 何が（品目）
- いくつ（数量）
という情報です。そして、上記に加えて「いつ（何月何日）」に入庫または出庫したのかという情報を記録（入力）していくことです。これだけで十分です。
そもそも「在庫とは何か？」ということを考えてみましょう。
在庫は必ず次の３つのプロセス（流れ）の中間に位置しています。そのプロセスとは、入庫されて、保管されて、いずれ出庫される【入庫→保管→出庫】たったこれだけです。このうち、保管に当たるところにあるのが在庫です。
つまり、在庫とは、在庫＝入庫－出庫の差となります。
つまり、在庫管理表を作成する最大のポイントは「入庫と出庫を確実に押さえる」ことです。
入庫・出庫のどちらかが欠けた途端に、その中間に位置する在庫は正しさを失います。
入庫・出庫・在庫が分かる見やすくシンプルな在庫管理表がベストです。
【無料】在庫管理個別相談受付中
在庫の保管場所
次に考えておきたいのが、在庫の保管場所です。
保管場所を複数持っており、そしてそれぞれの保管場所の距離がある程度離れているのであれば、同じ商品であってもそれぞれの場所の在庫数を管理します。
在庫はモノですから必ずどこかに「置く」という作業が必要です。そして、
同じものを複数の場所で管理するということはよくあることです。例えば、
- 工場の場合：倉庫と作業場に同じものが置いてある
- 小売の場合：陳列棚とバックヤードに同じものが置いてある
つまり、１品目を総量として管理をするのか、各場所で管理をするのかで管理方法が異なります。置き場が多い場合は、場所ごとに管理をしておかないと、「あるはずなのに無い！」ということがよく起こります。置いてある場所ごとの数量を把握するためには、管理項目に「どこに（場所：ロケーション）」を加えます。
「どこに」を加えると管理項目が１つ増え、厳密な在庫管理が求められます。
どこに入庫したのか、どこから出庫したのか？そして入出庫だけではなく、どこからどこへ在庫の保管場所を移動したのか？という移動管理も発生します。全てを記録し、管理しなくてはいけません。在庫管理レベルに不安がある会社は、まずは「どこに」はわきにおいて置き、総量として管理をすることをお勧めします。総量の管理ができない会社に、場所までを含めた厳密な管理は不可能です。
エクセルで在庫管理表を作る際に必要な３つのスキル
会社で役立つ在庫管理表をエクセルで作るためには、エクセルマニアになる必要はありません。
もし、そうであればエクセルが詳しい人が、すごく良いシステムを作っているはずです。
エクセルに詳しいことよりも次の３つのスキルの方が重要です。
- 在庫管理の基本を理解している
在庫管理の基本を理解していないと、基本が抜けた管理表を作ってしまったり、あれもこれもと盛り込んだ複雑な管理表を作ってしまいます。 - エクセルの基本的な機能や関数を使いこなせる
エクセルの機能や関数を知らない人は、とても複雑で難しい在庫管理表を作りがちです。しかも、その在庫管理表は、式の組み方に漏れやミスがあり、在庫管理表としてキチンと機能しないことが多々あります。 - データ処理の基本的なテクニックを持っている
在庫管理表の日常的な業務は記録です。つまり、ルーチン作業がメインになります。一度作ってしまえば、処理に時間を掛けるのはもったいないです。
在庫管理アドバイザーが在庫管理に必要なエクセルスキルをまとめました。
在庫を正しく扱う基本基本知識を学ぶ
仮に、在庫管理の基本知識に不安があるという方はぜひこちらの記事もご一読ください。
☑在庫管理システムを導入する前に知っておきたい在庫管理の基本
在庫を正しく扱うために必要知識で、現品管理の最も基礎的なことを解説しています。エクセルで在庫管理表を自作するという具体的な方法を知る前に、在庫管理のアウトラインを学んでおくことは非常に重要です。ー度といわず、二度三度読み返し、完璧にマスターしていきましょう。
エクセル(無料テンプレート)の在庫管理表は、見やすくて、誰でも簡単に使える在庫管理機能が最大のメリットです。しかし、それだけではありません。発注管理表、データ分析なども実現できます。
なお、エクセルによる在庫管理ノウハウをこちらで紹介しています。あわせてお役立てください。
【無料】在庫管理個別相談受付中
従業員が４人以上なら在庫管理システムがおすすめ
手軽、無料、簡単の３拍子揃ったエクセル在庫管理表ですが、本格的な在庫管理には不向きです。
エクセル在庫管理表が苦手なのは次の４点です。
- データの複雑な連動（例：１つの商品を入力したら、その商品にセットまたは付属している複数の部材の在庫数が自動で変動する）
- 重たくなる（関数やデータが多くて重たい。特にパソコンが苦手な人は戸惑ってしまい、ミスを誘発する）
- 入力・編集の履歴が分からない。（今は、入力結果しか見えない。履歴が見えれば操作ミスが見える化できるため）
エクセルは何でもできますが、やはり表計算ソフトです。
データベースのようにデータを貯めたり、プログラムをサクサク動かすには不向きです。
会社で在庫管理を行う場合は、やはりきちんとしたシステムを使用することをお勧めします。例えば、
- 従業員が４人以上いる（データのリアルタイム共有が必要）
- 複数の販売方法をしている（複数のECサイトでの販売、店舗販売＋卸売り販売等、在庫データの連携が必要な場合）
ただし、機能がたくさんある高価なシステムである必要はないです。
1000万円以上かけて導入したシステムにも関わらず、搭載された機能の20％も使っていない・・・
ということはよくあることです。
自社のやりたいこと、実は気づいていない今やるべきこと、習熟度に合ったシステムを選びましょう。
自社に合った機能を持つシステムを選び、お手伝いします
＼役立つエクセル在庫管理表／
【無料テンプレート】