在庫管理アドバイザーの岡本です。
この記事では、私が考案して、実際に在庫管理の現場で使っていたシンプルで使いやすいエクセル在庫管理表（3欄式在庫管理表）の使い方を解説します。
無料テンプレートをダウンロードできるようにしています。ダウンロード後使い方を学んでいただいたらすぐに使えるいただけるようになっています。
そして、この記事の最後にご自身でエクセルで在庫管理表を作る場合のポイントを簡単にお伝えします。
目次
【無料】在庫管理表のダウンロード
最初に、今回解説する3欄式在庫管理表をご入手ください。
下記のフォームにご登録いただくと、登録していただいたメールにダウンロード用URLが届きます。（URLが届くまで少しお時間がかかる場合があります）
ダウンロードが終わりましたら、3欄式在庫管理表を開いていただいて次にお進みください。
3欄式在庫管理表は使いやすさが特徴
3欄式在庫管理表は、考案する当初から誰にでもわかりやすくて使いやすいシンプルなものにすることを目指して開発しました。
次のような特徴があります。
- エクセルで使える
- 1000点を超える品目でも管理可能：管理品番の点数が数千点あったので、たくさんの品番を一度で管理できるようにしています。
- 誰でも使える：この管理表を使ってパートさんに管理してもらうことを想定していたので、簡単で見やすいものになっています。
- 欠品回避が可能：この在庫管理表を見れば、どこで在庫が切れそうになるかがすぐにわかるようになっています。
- メンテナンスが楽：品番点数はずっと同じままではなく、必ず増減するので品番を追加したり、減らしたりすることができます。
実際に、毎日３～4人のパートさんがこの管理表を使って、欠品しそうな部品を見つけたり、先々の発注に役立てていました。
あなたの会社でも、欠品しそうな商品を見つけたり、発注が必要な商品を見つけることが可能になります。
在庫管理表の構成と見方
3欄式在庫管理表は、在庫推移表、品目マスタ、グラフの3つのシートでできています。
- 在庫推移表：メインのシートです。このシートを見て、各品を管理します
- 品目マスタ：管理したい品目（商品や部品）を登録します。
- グラフ：在庫と入出庫の動きをグラフ化して視覚的にわかりやすくします。
見やすくする工夫はウィンドウ枠の固定と条件付き書式
在庫推移表は、見やすくするためにウィンドウ枠の固定と条件付きを設定しています。
毎日使うものなので、ちょっとした工夫をするだけで、とても使いやすくなります。
ウィンドウ枠の固定（特定のセルをずっと見られるようにする）
ウィンドウ枠の固定とは、ずっと表示しておきたい場所を画面に残り続けるようにする機能です。
列固定：品目から繰り越し在庫までを固定（ずっと見られるようにする）
スクロールしても、ずっと品目情報が見えるので、わざわざ左にスクロールして確認する必要はないです。
行固定：日付を固定（ずっと見られるようにする）
管理する商品点数が多くなると、下にスクロールする機会が多くなります。
行固定をして、日付がある行を見えるように固定しておけば、わざわざ上にスクロールして戻って、日付確認をしなくても良いです。
条件付き書式でセルの見落としを防ぐ
条件付き書式とは、セルの値が特定の条件になった時に、自動でセルを塗りつぶしたりフォントを変えたりする機能です。
この管理表では、安全在庫と日付に条件付き書式を設定しています。
この在庫管理表では、日付と在庫数にに条件付き書式を設定しています。
- 日付：土曜日で青色、日曜で赤色に塗りつぶし
- 在庫数：在庫数が安全在庫以下になったら、オレンジ色に塗りつぶし
機能をたくさん使う必要はなく、必要な機能だけを効果的に使うと良いです。
在庫管理表の使い方
３欄式在庫管理表の使い方をご説明します。
簡単な手順としては、
- 品目マスタを準備する（シート：品目マスタ）
- 日付を入力する（シート：在庫推移表）
- 管理したい商品を在庫推移表に登録する（シート：在庫推移表）
- 実績を入力する
管理したい商品を決める（品目マスタを準備する）
管理したい商品（部品）をシート「品目マスタ」に登録してください。
必ず登録していただきたいのは、次の3点です。
- 品目コード
- 品目名称
- 前月繰り越し在庫
前月繰り越し在庫は、初期在庫になります。
安全在庫はできれば登録してください。
登録しておけば、「条件付き書式」が働き、欠品しそうな品がわかります。登録無しの場合は安全在庫＝０になるので、在庫数が0個になった時に条件付き書式が働いてオレンジ色になります。
ロット、発注LT、発注T（発注できるタイミング）は、発注をするときのガイドです。在庫管理表を使って、発注まで行いたい場合は、この3つは登録しておきましょう。
（なお、これら3つの項目を設定しなくても、在庫推移表はきちんと機能します）
日付を入力する
次に、セルC1に初日を入力してください。
すると、在庫推移表の日付が自動で入力されます。
なお、初期設定では在庫管理表は1か月間の在庫推移を管理できるようになっています。
管理したい商品の品目コードを在庫推移表に登録する
試しにa01（りんご）を在庫推移表に登録します。
ひとつ入力しただけで、必要な項目に全て反映されます。なお、在庫回転日数は、入出庫が入って初めて計算されるのでエラーでも問題ないです。
もし、登録されていない品目コードを入力すると、「#N/A」と表示され、必要な情報が自動で反映されませんので間違えると良くわかります。
実績（入庫・出庫）を入力する
ここまでできたら、あとは運用するだけです。
入庫出庫を入力していきます。すると同時に在庫数が連動して変わります。
安全在庫を設定していれば、在庫数が安全在庫数以下になった時、セルの色がオレンジ色になります。
（設定していない場合は、在庫数が0になった時にセルの色がオレンジ色になります。）
なお、「調整」は、不良品が出たときや、商品が戻入（戻ってくる）時に使ってください。入出庫と一緒にしない理由は、通常の入出庫と明確に区別するためです。
区別しておかないと、正しい分析値（後で分析値については解説します）がわからなくなるので発注数を決めるときに正しい判断ができなくなります。
翌月への繰り越し方法
在庫管理表は1か月間の在庫推移が管理できるようになっているので、月末になったら翌月分を作成します。
- 月末になった時点でファイルをコピーします。
- 月末在庫（赤枠で囲った部分）を新しいフォーマットのシート「品目マスタ」の前月繰越在庫に入力します。
- 在庫推移表の入出庫データを全て削除する。
これで翌月分の準備は完了です。
管理したい商品を増やす（新品番の登録、廃番）
在庫管理表で絶対に考えておかないといけないのは、品番が増減することです。
在庫管理表の運用に苦労しているご相談者様が使っている在庫管理表を見ると、品番が増減することを考えていないことが多いです。
この在庫管理表では、品番の追加や削除がしやすいのが特徴です。
品番を追加したい場合の手順は、
- 品目マスタに管理したい商品の情報を追加（シート「品目マスタ」）
- 在庫推移表にフォーマットを追加（シート「在庫推移表」）
- 品目コードを入力（シート「在庫推移表」）
管理していた品番が終売や廃番などで管理不要になった場合は、対象の行を削除するだけでOKです。
このように3欄式在庫管理表は、メンテナンスがとても簡単なので毎日品番が増えたとしても大丈夫です。
在庫分析値の見方
適正在庫のためには、在庫分析は必須です。
3欄式在庫管理表には重要な分析値が自動計算されます。
分析値は以下の通りです。
- 在庫回転日数（在庫数と出庫数で計算）
- 合計値（入庫、出庫、調整のそれぞれで計算）
- 最大値（入庫、出庫、調整、在庫数）
- 最小値（入庫、出庫、調整、在庫数）
- 平均値（入庫、出庫、調整、在庫数）
- 中央値（入庫、出庫、調整、在庫数）
- 実施回数（入庫、出庫、調整）
特に説明が必要な分析値について解説します。
在庫回転日数（在庫回転率）
在庫回転日数は、在庫の流動性（滞留度）を表す指標です。
在庫回転日数が悪いと、会社は在庫の山になり、キャッシュフローが悪化します。
在庫管理の中でも最も大切な指標のひとつです。自社の適正な在庫回転日数を知っておき、その回転日数になるように在庫コントロールします。
平均値
平均値は一番よく使用する分析値ではないでしょうか？しかし、平均値は簡単に信用してはいけません。
例えば、次のような場合です。
100人のクラスがあって、0点の人が50人、100点の人が50人。
平均値を計算すると50点になります。さて、この平均値は意味があるでしょうか？
恐らくないですね。実は平均値は使って良い場合が決まっているため、安易に使ってはいけない分析値です。
平均値を使ってはいけない場合について詳しく知りたい方はこちら
使って良い場合は、
- 平均値の値が、だいたい最大値と最小値の間であること
- 平均値の値が、中央値とほぼ同じであること
- 平均値の計算に使うデータ数が十分に多いこと（20以上が良い）
- データが最大値や最小値に極端に偏っていないこと
今回、3欄式在庫管理表にある分析値である程度判断が可能です。
中央値
中央値とは、データを小さい順に並べて真ん中にくるデータのことです。
例えば、「１、２、３、４、５」というデータがあった場合、中央値は３になります。
中央値と平均値の使い分けですが、特にデータに偏り（最小値や最大値付近にデータが偏っている）
場合は、平均値よりも中央値の方がよくあてはまります。
平均値と中央値は必ずセットで計算することをお勧めします。
実施回数
実施回数とは、入庫や出庫が起こった回数です。
例えば、1か月で3回出庫した場合は、出庫の実施回数は３回になります。
入庫数や出庫数ではありません。
実施回数を取る意味を取るのは、合計値だけではわからないことがあるからです。
例えば、商品Aと商品B、1か月間で300個売れたとします。そして発注LTが7日だったとしましょう。
さて、商品Aと商品Bの1回の発注数は何個くらいが良いでしょうか？
300÷30=10個／日 発注LTが7日なので、70個
で良いでしょうか？これは間違いです。商品Aが1回で100個売れたとしたらどうでしょうか？
発注数70個とするとおかしくなるでしょう。
実施回数がわかることで、仕入れの頻度や1回あたりの発注数など発注戦略を考えることができます。
実施回数は発注方法を検討するときによく使う指標です。
コンサルティン先では、この指標とほかの指標を組み合わせて
- 発注の頻度（月次、週次、いつでも）
- 在庫の持ち方
の仕組みづくりを行っており、私は必須指標としてとらえています。（あるコンサルティン先では、５つの発注方法を定義しました）
具体的な活用方法を知りたい場合は、個別相談（無料）をご活用ください。
この指標を使っている在庫管理表は今まで見たことはないですが、実務を考えれば当然必要な情報なのでぜひ取ってもらいたいです。
フィルタを活用する
この在庫管理表が1000部品でも発注できるようになっているのは、エクセルのフィルタ機能を使っているからです。
例えば、各商品で発注しなければならないものを見つけたい時に、「在庫」だけに絞り込めば、各商品の在庫データだけが一覧で確認できます。
今回の例の場合では、商品点数が少ないのでフィルタをかける必要は感じませんが、50点を超えてくるとフィルタをかけた方が見やすく、見落としも無くなります。
地味ですが、画面スクロールする時間の短縮にもなります。
セル結合したらコピーできなくなります。
3欄式在庫管理表を初めて見たとき、品目コードと品目名称がカッコ悪い、セルを結合して表示を1つにした方がすっきりとして見やすいのでは？？と思いませんでしたか。
フィルタをかけた後の「一覧」を実務は、そのまま別の用途に使いたいということが良くあります。
しかし、セル結合すると、フィルタをかけて作成した一覧をコピーできません。
実際の実務のシーンでは、絞り込んだ一覧の部分だけを他の用途に使用するということがよくあるので、絞り込んだデータをそのまま使えるようにあえてセル結合はしていません。
グラフを追加する
３欄式在庫管理表では、「グラフ」シートをご用意しています。
在庫管理に使うグラフは2軸グラフがお勧めです。2軸グラフとは、縦棒が左と右にそれぞれ１本ずつあるグラフのことです。
- 縦棒グラフ（縦軸は左側）：入庫数、出庫数、調整数 を表す
- 折れ線グラフ（縦軸は右側）：在庫数 を表す
グラフにすると、在庫の動きが一目瞭然です。
これまで、多くの方のご相談に乗ってきましたが、在庫管理表は作っていても、グラフを作っているという方はごくわずかでした。
グラフを作れば、表だけではわかりづらいデータの傾向が一目でわかるのでとてもおすすめです。
縦軸を2軸にしている理由
縦軸の最大値が、在庫数とそれ以外の値で違うことが多いからです。
在庫数が極端に多かったり、その逆で仕入れロットが多い場合、縦軸を1つにしてしまうと、小さな値が見えなくなります。
在庫管理表の便利な使い方（応用編）
ここまで、基本的な使い方をご説明しました。
ここから先は、応用編として便利な使い方をご紹介します。
担当者別・仕入先別に業務を振り分ける
在庫管理では、担当者ごとに仕入先担当を持っていることが多いです。
そこで、在庫管理表に、担当者別に絞り込む機能を追加します。
- 在庫推移表に列「担当者」を追加
- 担当者を入力（ここもセル結合をしない）
- フィルタで絞り込む
これで、自分の担当する部品だけに絞り込むことができました。
これでさらに、「在庫」だけに絞り込むと、自分の担当する商品の在庫だけに絞り込めます。
在庫推移表は、列を追加しても機能には影響が出ないように作っています。
（もし、エラーが出た場合は一旦元に戻してからやり直してください。）
また、品目マスタに担当者を入力しておいて自動参照させる方法もおすすめです。
メンテナンスのことを考えるとこちらの方法の方をお勧めします。（VLOOKUP関数を使う必要があります）
販売（使用）計画や発注残を入れて、先回り発注で余計な在庫を減らす
在庫管理表には、実績を入れてきましたが、未来の値（販売計画や先々の発注残）を入れておくこともおすすめです。
未来の値を入れておけば、いつ頃欠品するのかがわかるため、先々の予定を見ながら発注ができます。
在庫が危なくなった時点で発注する方法は、安全在庫をたくさん持っていないといけません。（理由＝発注したものが入ってくるまでの在庫が必要）
安全在庫をたくさん持ちすぎる必要がなくなります。
エクセル在庫管理表をお勧めする規模は従業員4人まで
今回ご紹介した３欄式在庫管理表に関わらず、エクセルだけで在庫管理をする会社規模としては、4人くらいまでをお勧めしています。
エクセルは無料で使えて、機能も豊富で便利ですが、あくまでも「表計算ソフト」です。
特に次のような理由です。
- 大量のデータをためるのに向かない（２万行くらいまでにとどめた方が良い。）
- リアルタイム管理が難しい
- 属人化が起きやすい
データを使うことには長けていますが、それ以外の用途はあまりお勧めしません。
在庫は会社が日常的に使う大部分のお金を占めています。
売上やキャッシュフローにも直結しますので、今後ずっと在庫管理が必要な商売を続けるのであれば、在庫管理システムの導入をお勧めします。
（在庫管理システムは必要最小限のシンプルなもので良いです。）
エクセル在庫管理表と在庫管理システムの効果的な連携方法
私が最もお勧めするシンプルかつ効果的な在庫管理の仕組みは、データの入力と蓄積を在庫管理システムで、発注量の決定や月次の集計などをエクセル在庫管理表
で行う方法です。下記のようなシステム構成になります。
システムの良さである
- 大量のデータ蓄積可能
- 共同で同時作業が可能
- 属人化防止（業務を標準化しやすい）
とエクセルの良さである
- 柔軟な機能
- データ活用に強い
を組み合わせて在庫管理体制を整えます。
ハンディターミナルやQRコードの活用は、現場の状況に応じて考えればよいでしょう。
低予算で使いやすく、応用の効きやすいシステム構築が可能です。
在庫管理システムと3欄式在庫管理表を連携する
3欄式在庫管理表を在庫管理システムと連携することも可能です。
エクセル在庫管理表に必要な情報は、次の3つです。
- 商品の情報（品番、品名、その他情報（発注ロットや安全在庫等）
- 入出庫の情報
- 現在庫
この情報を簡単に連携できるようにしたのが、高機能版3欄式在庫管理表です。
システムに入力したデータを使って、在庫推移表を自動で作成します。
また、エクセル版の在庫推移表では難しかった
- 保管場所別の在庫推移表（同じ商品が別々の場所にある場合、その場所ごとでの推移データ。※全ての場所の在庫を合算した推移データも作成）
- グラフの自動生成機能
もできるようになっています。
最新の在庫（現在庫情報）と入出庫履歴が取れるシステムであれば、どんなものにでも対応可能です。
在庫管理システムの導入、入れ替えの相談をする
在庫管理110番では、実務経験に基づいたシンプルかつ使いやすいシステム導入のコンサルティングやご支援を行っています。
無料相談も承っています。
御社の現状を踏まえ、
- どのようなシステムが良いのか？
- システム構築の前に何から取り組んだ方が間違いが無いのか？
を無料でアドバイスします。
ただし、無料個別相談の受付数は1か月の上限があります。お問い合わせの上ご予約をお願いします。
あなたの会社に現状に合わせたアドバイス！
在庫推移自動作成ツール
今回ご紹介した在庫管理表をさらに発展させて、現在庫データと入出庫履歴データだけで、在庫管理表を自動で作成する
「在庫推移自動作成ツール」を作成しました。手間を省いて、在庫管理表を作りたい・・・という方はぜひこちらもご確認してみてください。