Excelで在庫管理を行うときは、入庫数と出庫数を入力するだけで残数が変わる表を作ると、確認作業を大きく減らせます。
特に在庫数が少なくなった商品を早めに把握する仕組みを用意しておくと、欠品や過剰発注を防ぎやすくなります。
この記事では、1行目を見出し行としたサンプルデータを使い、残数を計算する基本関数、発注数の自動計算、判定表示、運用時の注意点を順番に解説します。
在庫管理の基本式は、期首在庫＋入庫数－出庫数＝現在庫です。
発注数まで管理する場合は、発注点－現在庫を基準に必要量を計算します。
セル参照を正しく使えば、日々の数値入力だけで残数と発注の目安を自動更新できるようになります。
エクセルで残数を自動計算する基本関数
それではまず、在庫数から入庫数と出庫数を反映して残数を求める基本式について解説していきます。
| 商品コード | 商品名 | 期首在庫 | 入庫数 | 出庫数 | 現在庫 |
|---|---|---|---|---|---|
| A001 | ボールペン | 120 | 30 | 45 | 105 |
| A002 | ノート | 80 | 0 | 25 | 55 |
| A003 | 付箋 | 50 | 40 | 20 | 70 |
残数計算用の列構成
在庫管理表では、商品コード、商品名、期首在庫、入庫数、出庫数、現在庫を横並びにすると、数字の流れを確認しやすくなります。
上記の例では、C列が期首在庫、D列が入庫数、E列が出庫数、F列が現在庫です。
残数を手入力すると転記漏れや計算ミスが起きやすいため、F列は数式専用の列として扱うのが基本です。
商品ごとに1行を使い、2行目からデータを登録すると、数式のコピーや並べ替えもしやすくなります。
在庫表を作る際は、数値を入力する列と数式を入れる列を分けることが重要です。
現在庫の列を直接編集しない運用にすると、計算結果の信頼性を保ちやすくなります。
期首在庫には前月末の実棚数量を入れ、入庫数と出庫数には当月の増減を入力する設計がわかりやすい方法です。
現在庫を求める計算式
現在庫を表示するF2セルには、次の数式を入力します。
=C2+D2-E2
この式は、期首在庫に入庫数を足し、出庫数を引くという意味です。
たとえばC2が120、D2が30、E2が45なら、F2には105と表示されます。
入庫数がない場合は0を入力しておけば計算でき、出庫数がない場合も同様です。
数式を入力した後は、F2セル右下の小さな四角を下へドラッグするか、ダブルクリックしてオートフィルで各行へ数式をコピーしましょう。
コピー後は、F3では=C3+D3-E3、F4では=C4+D4-E4のように、行番号が自動で変わります。
空白セルとエラーを防ぐ数式
まだ商品情報を入力していない行まで数式を入れると、0が大量に表示されて見づらくなることがあります。
そのような場合は、商品コードが空欄なら結果も空欄にするIF関数を組み合わせます。
=IF(A2=””,””,C2+D2-E2)
A2に商品コードがあるときだけ残数を計算し、A2が空欄なら何も表示しない式です。
数式の見た目を整える工夫は、在庫表を複数人で確認するときにも役立ちます。
なお、在庫がマイナスになる場合は、入力ミス、出庫の先行登録、実棚との差異などを疑う必要があります。
【操作のポイント】現在庫の数式は最初のデータ行だけで完成させ、確認後にオートフィルで最終行までコピーすると安全です。
在庫数と発注数を自動計算するIF関数
続いては、現在庫が一定数を下回ったときに、必要な発注数を自動計算する方法を確認していきます。
| 商品名 | 現在庫 | 発注点 | 目標在庫 | 発注数 | 発注判定 |
|---|---|---|---|---|---|
| ボールペン | 105 | 40 | 150 | 0 | 発注不要 |
| ノート | 55 | 60 | 120 | 65 | 発注必要 |
| 付箋 | 70 | 30 | 100 | 0 | 発注不要 |
発注点と目標在庫の設定
発注数を自動化するには、現在庫とは別に発注点と目標在庫を設定します。
発注点は、在庫がこの数値以下になったら発注を検討する基準です。
目標在庫は、入荷後に確保したい数量を表します。
たとえば、毎週20個売れる商品で、発注から納品まで2週間かかるなら、余裕を含めた発注点を60個程度に設定する考え方があります。
発注点は単なる最低在庫ではなく、納品までの販売量を考慮した安全在庫として決めると実務に合いやすくなります。
発注点は欠品を防ぐための警戒ラインです。
目標在庫は発注後に戻したい在庫水準です。
必要発注数を表示する数式
現在庫がF列、発注点がG列、目標在庫がH列、発注数がI列の場合、I2には次の数式を入力します。
=IF(F2<=G2,H2-F2,0)
現在庫F2が発注点G2以下なら、目標在庫H2から現在庫F2を引いた数を表示します。
現在庫が発注点より多いときは、発注不要として0を返します。
ノートの現在庫が55、発注点が60、目標在庫が120の場合、発注数は65です。
この式なら、日々の出庫数を入力して現在庫が変わるたびに、必要な発注数も連動して更新されます。
発注単位が10個単位などの場合は、CEILING関数を組み合わせて切り上げる方法もあります。
=IF(F2<=G2,CEILING(H2-F2,10),0)
この式では必要数が65個でも、10個単位で70個と表示されます。
発注の必要性を文字で判定する式
数字だけでは発注対象を見落としやすいため、発注判定の列を作ると便利です。
J2セルには次の数式を入力します。
=IF(I2>0,”発注必要”,”発注不要”)
発注数が1以上なら発注必要、0なら発注不要と表示されます。
画面を一覧で見るだけで、対応すべき商品がわかる構成です。
発注数と発注判定を別列にすることで、数量確認と担当者への連絡をスムーズに進められます。
【操作のポイント】発注点と目標在庫は商品ごとに異なるため、同じ数値を一律で入れず、販売量や納期に応じて設定します。
在庫不足を見える化する条件付き書式
続いては、発注が必要な商品を色で目立たせる条件付き書式について確認していきます。
| 商品名 | 現在庫 | 発注点 | 発注数 | 状態 |
|---|---|---|---|---|
| ボールペン | 105 | 40 | 0 | 通常 |
| ノート | 55 | 60 | 65 | 要発注 |
| 付箋 | 18 | 30 | 82 | 要発注 |
現在庫が発注点以下のセル選択
条件付き書式を設定する前に、現在庫を表示しているF2からF100など、対象範囲を選択します。
商品数が増える予定なら、少し余裕を持った行数まで選択しておくと後から設定し直す手間を減らせます。
次に、ホームタブの条件付き書式から新しいルールを選びます。
数式を使用して、書式設定するセルを決定を選択すると、別列の発注点と比較するルールを作成できます。
【操作のポイント】現在庫列だけを赤くするのか、商品名から発注判定まで行全体を色付けするのかを、先に決めてから範囲を選択します。
比較数式による警告表示
現在庫がF列、発注点がG列の場合、条件付き書式の数式欄には次の式を入力します。
=$F2<=$G2
列記号の前に付けた$は、横方向に書式を適用しても比較する列を固定する役割です。
行番号には$を付けないことで、2行目、3行目、4行目と各商品の在庫状況を個別に判定できます。
書式では、薄い赤の塗りつぶしと濃い赤の文字色を選ぶと、警告が見やすくなります。
在庫が発注点以下になると、セルまたは行が自動で目立つため、確認漏れを防ぎやすくなります。
条件付き書式の設定画面イメージ
以下は、現在庫と発注点を比較する条件付き書式を設定する画面のイメージです。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 商品名 | 現在庫 | 発注点 | ||||
| 2 | ノート | 55 | 60 |
このように、F列の現在庫とG列の発注点を同じ行で比較します。
色による警告は数式だけでは気付きにくい不足在庫を補う機能です。
【操作のポイント】条件付き書式は、在庫数が変化しても自動判定されるため、毎日目視で比較する必要がありません。
入出庫履歴から在庫数を集計するSUMIFS関数
続いては、入出庫の履歴を別シートに記録し、商品ごとの在庫数を集計するSUMIFS関数を確認していきます。
| 日付 | 商品コード | 区分 | 数量 |
|---|---|---|---|
| 4月1日 | A001 | 入庫 | 100 |
| 4月3日 | A001 | 出庫 | 25 |
| 4月5日 | A001 | 出庫 | 10 |
入出庫履歴シートの作成
在庫の動きが多い場合は、商品マスターの表に日々の入庫数と出庫数を上書きするより、履歴を1行ずつ追加する方式が適しています。
履歴シートには、日付、商品コード、区分、数量を記録します。
区分には入庫または出庫を統一して入力し、表記ゆれを防ぎましょう。
履歴を残す運用にすると、いつ何個動いたかを後から確認できます。
商品別入庫数の集計式
在庫管理シートのA2に商品コードがあり、履歴シートのB列が商品コード、C列が区分、D列が数量の場合、入庫合計は次の式で求められます。
=SUMIFS(履歴!$D:$D,履歴!$B:$B,$A2,履歴!$C:$C,”入庫”)
SUMIFS関数は、複数の条件に一致する数値だけを合計する関数です。
この例では、商品コードがA2と一致し、区分が入庫である履歴の数量を合計します。
商品コードを基準に集計するため、同じ商品が何度入庫されても正しく合算できます。
商品別出庫数と現在庫の集計
出庫合計も、条件の文字を出庫に変えるだけで計算できます。
=SUMIFS(履歴!$D:$D,履歴!$B:$B,$A2,履歴!$C:$C,”出庫”)
現在庫は、期首在庫に入庫合計を足し、出庫合計を引く形です。
履歴を追加するたびに合計値と残数が更新されるため、月ごとの在庫集計を手計算する負担を減らせます。
【操作のポイント】SUMIFS関数で列全体を参照する場合は、履歴の件数が非常に多いブックでは処理が重くなることがあるため、必要に応じて範囲を絞ります。
在庫管理表を運用しやすくする入力ルール
続いては、計算式を壊さずに在庫管理表を継続利用するための入力ルールを確認していきます。
| 確認項目 | 入力の例 | 管理上の目的 |
|---|---|---|
| 商品コード | A001 | 商品を一意に識別 |
| 入出庫日 | 2026年4月1日 | 履歴の追跡 |
| 数量 | 25 | 計算の統一 |
商品コードによるデータ統一
商品名だけで管理すると、同じ商品でも表記が少し違うだけで別商品として集計される可能性があります。
そこで、商品ごとに重複しない商品コードを設定し、入出庫履歴にも同じコードを入力します。
商品コードは在庫管理の検索と集計の軸になります。
商品名の変更や表記の揺れがあっても、コードが同じならSUMIFS関数で正しく集計できます。
入力規則による誤入力防止
区分列には、データの入力規則を設定して、入庫と出庫だけを選べるようにすると便利です。
データタブからデータの入力規則を開き、入力値の種類でリストを選択します。
元の値に入庫,出庫と入力すると、セルに選択用の矢印が表示されます。
誤って入荷、出荷、入庫済みなど異なる文字を入力することを防げるため、集計式の条件漏れを抑えられます。
実棚卸との照合
Excel上の現在庫は、入力された入出庫データに基づく理論在庫です。
破損、返品、入力漏れ、数量間違いがあると、実際の棚にある数量と一致しないことがあります。
定期的に棚卸を行い、実棚数量と理論在庫を比較することが大切です。
差異が出たときは数式を直す前に履歴と現物を確認しましょう。
【操作のポイント】在庫表は担当者だけが編集するのではなく、入力方法と棚卸日を共有して運用ルールを固定します。
まとめ エクセルの在庫管理で残数を計算する関数と発注数の自動計算
Excelの在庫管理では、期首在庫に入庫数を加え、出庫数を引く数式を使うことで現在庫を自動計算できます。
=C2+D2-E2のような基本式を現在庫列に設定し、オートフィルでコピーすれば、商品ごとの残数をすばやく把握できます。
発注点と目標在庫を用意し、IF関数で発注数を求めれば、在庫不足に気付いてから慌てて発注する状況を減らせます。
さらに、条件付き書式で不足在庫を色付けし、SUMIFS関数で入出庫履歴を集計すると、商品数や取引回数が増えても管理しやすくなります。
重要なのは、数式だけに頼るのではなく、商品コードの統一、入力規則、実棚卸による照合も組み合わせることです。
自社の発注単位、納品リードタイム、販売量に合わせて発注点を調整し、実用的な在庫管理表へ育てていきましょう。
