Excel(エクセル)で商品や棚卸資産などの在庫を管理したいと思う方は多いと思います。
エクセルで在庫管理する場合、在庫切れを防ぐために、発注するタイミングも把握できれば便利ですよね？
例えば、「在庫20個」「毎日平均５個使用」「仕入に２日かかる」という条件で、発注が必要かどうかを判定する、といったツールです。
難しく思うかもしれませんが、有料・無料のテンプレートやアプリを使わなくても、在庫管理表はだれでも簡単に作成することができます。
今回の記事では、『自動で発注時点が把握できる在庫管理表の作成方法』を紹介します。
エクセルの在庫管理表の完成イメージ
今回紹介するエクセルの在庫管理表の完成イメージは以下となります。
右から２列目の「発注の要・不要」列の値が「１」であれば「発注が必要」、「0」であれば「発注は不要」、「注意」であれば「発注する場合は注意」というフラグが自動で表示されるように、計算式を挿入します。
また、発注忘れが発生しないよう、発注フラグ(1，0，注意)に応じて、セルに色が自動で付くようにフォーマットを作成します。
この在庫管理ツールの各列の入力内容は以下のとおりです。
在庫管理表の各列
品目コード・品目名 ⇒ 棚卸資産番号や在庫の名称
単価 ⇒ 在庫の単価を入力 ※入力は任意
在庫数(個） ⇒ 発注時点の在庫数
調達期間(日) ⇒ 発注してから何日後に仕入れることができるか
１日当たり使用数(個） ⇒ １日あたり何個の在庫を消費するか
注文ロット(個） ⇒ １発注で何個仕入れることができるか
調達前の在庫数(個） ⇒ 購入直前にいくつの在庫数になっているか
発注点(個） ⇒ 在庫の下限値。在庫を最低いくつ持っていたいか
発注の要・不要 ⇒ 発注必要は「１」、発注不要は「0」、注意は「注意」
備考 ⇒ 注意事項やメモ
エクセルの在庫管理表を作成する方法
それでは、上記で紹介した在庫管理表を作成する手順(1～8)を紹介します。
長く感じるかもしれませんが、実際は簡単な作業が多いです。IF(S)関数や条件付き書式の設定だけで誰でも簡単に作成できます。
在庫管理/手順１．フォーマットを作成
まずは、以下のように在庫管理表のフォーマットを作成します。
見出しの左からの並び順は以下のとおりです。
列項目(左から)
品目コード、品目名、単価、在庫数(個）、調達期間(日)、１日当たり使用数(個）、注文ロット(個）、調達前の在庫数(個）、発注点(個）、発注の要・不要、備考
在庫管理/手順２．在庫数、調達期間などの値を入力
「品目コード」、「品目名」、「単価」、「在庫数(個）」、「調達期間(日)」、「１日当たり使用数(個）」、「注文ロット(個）」列に値を入力します。
棚卸資産を管理しているデータからVLOOKUP関数などを使用して入力するとスムーズに転記できます。
「調達期間(日)」は発注から納入までにかかる期間のことで、「注文ロット(個）」は１発注あたりの納入数です。
在庫管理/手順３．「調達前の在庫数」を計算
続いて、「調達前の在庫数」列の値を計算します。
「調達前の在庫数」は、「もしこの時点で発注しなかった場合、調達期間後はいくつの在庫数になっているか」の予想数です。
つまり、発注を１日飛ばしても問題ないかの判断となる数値を算出します。
そのため、「(在庫数)‐(１日当たりの使用数)×(調達期間)」となる以下の数式を挿入します。
=E3-F3*G3
在庫管理/手順４．発注点(発注最低ライン)を入力
続いて、「発注点」列の値を入力します。
「発注点」は、「在庫数の下限値」のことで、「この在庫数は絶対に下回ってほしくない数量」のことです。
この数値を大きくしすぎると発注タイミングが早くなり、小さくしすぎると在庫切れを起こしやすくなります。
過去の在庫推移などを参考に適正な発注点を検討してください。
在庫管理/手順５．発注を自動で判定
「発注の要・不要」列に数式をIFS関数を挿入します。
IFS関数は、IF関数を複数の条件で利用できるようになった新関数です。
IFS関数の結果が「１」であれば「発注が必要」、「0」であれば「発注は不要」、「注意」であれば「発注する場合は注意」となるよう、構文を作ります。
=IFS(L3<>"","注意",I3<=J3,1,"TRUE",0)
IFS関数の構文と３つの条件の意味は、以下のとおりです。
IFS関数の構文
L3<>"","注意" ⇒ 第１条件「L3セル(備考欄)が空白以外であれば、”注意”を表示」
I3<=J3,1 ⇒ 第2条件「J3セル(発注点)の値がI3セル(調達前の在庫数)以上であれば、”１”を表示」
"TRUE",0 ⇒ 第3条件「それ以外であれば、”0”を表示」
もし、「注意」という文字を表示させたくない場合は、以下のIF関数を挿入してください。
=IF(I3<=J3,1,0)
IFS関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
在庫管理/手順６．備考欄を入力
次に、「備考欄」列に発注に関するメモなどを入力します。
L列に文字を入力すると、手順５で挿入したIFS関数によって、K列には「注意」という文字が自動で表示されます。
もし、発注以外の情報を入力し記録したい場合は、右隣りの列に２行目の「備考欄」列を作成してください。
在庫管理/手順７．発注が必要な品目に自動で色付け
「発注の要・不要」列の文字が「1」(発注必要)であれば、管理表の行全体に自動で色が付く設定を行います。
手順１．「条件付き書式」の「新しいルール」を選択
まずは、予定表のセル範囲を左上から右下に向かってドラッグで選択します。必ず左上から選択してください。
選択した状態で、「ホーム」タブ⇒「条件付き書式」から、「新しいルール」をクリックします。
手順２．「ルールの種類」を選択
「ルールの種類を選択してください」のメニューの中から、「数式を使用して、書式設定するセルを決定」をクリックします。
手順３．数式を入力
「次の数式を満たす場合に値を書式設定」の下の数式入力枠に、K列の値が”1”かを判定するために、以下の数式を挿入します。必ず先頭に絶対参照の＄マークを入力してください。
=$K3=1
数式を入力したら、色を設定するため「書式」をクリックしてください。
手順４．自動で表示したい色を選択
「セルの書式設定」画面の「塗りつぶし」タブから、塗りつぶしの色を選択します。設定し終えたら、「OK」を押し、「新しいルール設定」画面も「OK」で閉じます。
手順５．色の自動化の完了
下の図のように、K列が”１”の行が自動で色付けされれば、設定は無事完了しています。
在庫管理/手順８．”注意”に自動で色付け
最後に、「発注の要・不要」列の文字が「注意」であれば、管理表の行全体に手順7とは異なる色を付ける設定を行います。
手順7で紹介した『条件付き書式』の設定を再度実行します。(２回目)
「新しいルール」画面で以下の数式を入力し、好みの色を指定してください。
=$K3=”注意”
下の図のように、K列が”注意”の行が自動で色付けされれば、設定は無事完了しています。
以上で、エクセルの在庫管理表の作成は完了です。
