Excel関数で在庫管理する方法をお探しですね。
広告
Excel関数だけで作る在庫管理表の作り方【初心者向け】
在庫管理表をExcelで作るとき、「入庫や出庫のたびに電卓で計算するのが面倒」「出庫を入力したら在庫数が自動で減ってほしい」と思ったことはありませんか? 実は、VBAやマクロを使わなくても、Excelの関数だけで入出庫の履歴を集計して、商品ごとの現在庫を自動計算できるんです。
この記事では、初心者でも使いやすい「商品マスタ」「入出庫履歴」「在庫一覧」の3つのシート構成をベースに、ミスを減らしながら運用できる在庫管理表の作り方をわかりやすく解説します。
Excel関数だけで作る在庫管理表の基本の考え方
Excel関数だけで在庫管理表を作るなら、まず大事なのは「どこに何を入力して、どこで計算するか」をきちんと分けることです。
よくある失敗パターンは、1枚のシートに商品情報も入庫数も出庫数も現在庫も全部詰め込んで、あとから行を追加したら数式が崩れちゃった…というケースです。
そこでおすすめなのが、**「商品マスタ」「入出庫履歴」「在庫一覧」の3つのシートに分ける方法**です。
– **商品マスタ**:商品コードや商品名、最初の在庫数、発注するタイミングなどを登録する台帳
– **入出庫履歴**:日々の入庫・出庫を1件ずつ記録していくシート
– **在庫一覧**:商品マスタと入出庫履歴を参照して、現在の在庫数を自動で表示する集計シート
こうやって役割を分けておくと、日々の作業では入出庫履歴に数量を入力するだけでOK。
在庫一覧の数字を直接いじる必要がなくなります。
基本の計算式はとってもシンプルです。
**現在の在庫数 = 初期在庫数 + 入庫数の合計 – 出庫数の合計**
つまり、在庫一覧には商品ごとにこの計算をする関数を入れておいて、入出庫履歴に出庫数が追加されたら、自動で現在庫が減る仕組みにします。
紙の管理表や手入力だけのExcelだと、更新し忘れたり計算ミスが起きやすいですが、履歴をもとに集計する形にすれば、あとから「いつ、なぜ在庫が減ったのか」も確認しやすくなります。
在庫管理表に必要な項目は、最初から欲張りすぎないのがコツです。
– **商品マスタ**:商品コード、商品名、初期在庫数、発注点
– **入出庫履歴**:日付、商品コード、区分(入庫/出庫)、入庫数、出庫数、担当者、備考
– **在庫一覧**:商品コード、商品名、現在庫数、発注判定
このくらいから始めると運用しやすいです。
特に**商品コード**は重要です。
似たような商品名や色違い・サイズ違いを区別するための目印になります。
商品名だけで管理すると表記ゆれ(「ボールペン」と「ボールペン(黒)」みたいな違い)が起きやすいので、在庫計算の基準は必ず商品コードにしておきましょう。
入出庫履歴から現在庫を自動計算する関数の作り方
現在の在庫数を自動で計算する中心になるのは、**SUMIF関数**または**SUMIFS関数**です。
– **SUMIF**:1つの条件に合う数値を合計する
– **SUMIFS**:複数の条件に合う数値を合計する
在庫管理では、商品コードごとに入庫数と出庫数を集計するので、基本的にはSUMIFで十分です。
倉庫別や店舗別、期間別なども条件に入れたいときは、SUMIFSを使うと便利ですよ。
具体的な数式の例
たとえば、こんな構成だとします。
– **商品マスタ**:A列に商品コード、B列に商品名、C列に初期在庫数
– **入出庫履歴**:B列に商品コード、D列に入庫数、E列に出庫数
この場合、在庫一覧シートのA2に商品コードが入っているなら、現在庫数を求める式はこうなります。
“`
=VLOOKUP(A2,商品マスタ!A:C,3,FALSE)+SUMIF(入出庫履歴!B:B,A2,入出庫履歴!D:D)-SUMIF(入出庫履歴!B:B,A2,入出庫履歴!E:E)
“`
この式の意味を分解すると:
1. **VLOOKUP**で商品マスタから初期在庫数を取得
2. **SUMIF**で入出庫履歴から同じ商品コードの入庫数だけを合計
3. **SUMIF**で同じ商品コードの出庫数だけを合計して差し引く
これで、入出庫履歴に新しい出庫を入力した瞬間、在庫一覧の現在庫数が自動で減ります!
新しいExcelなら、VLOOKUPの代わりに**XLOOKUP**を使うこともできますが、基本的な考え方は同じです。
別のやり方:入庫も出庫も1列にまとめる
出庫数をマイナスで入力して、「数量」という1つの列にまとめる方法もあります。
入庫は+100、出庫は-50みたいに入力すれば、現在庫は「初期在庫数 + 数量合計」で計算できます。
ただ、初心者や複数人で使う場合は、**入庫数と出庫数を別の列にしたほうがわかりやすい**です。
「入庫は入庫数の列、出庫は出庫数の列」とはっきり決めておくほうが、入力ミスにも気づきやすくなります。
発注判定も自動化しよう
さらに実務で使いやすくするなら、**IF関数で発注判定を表示**させます。
たとえば在庫一覧のC列が現在庫数、D列が発注点の場合、E2にこう入力します。
“`
=IF(C2<=D2,"発注必要","")
```
これで、在庫が発注点以下になった商品だけに「発注必要」と表示されます。
条件付き書式を使ってセルを赤くすれば、見落とし防止にもなります。
ここまで設定しておけば、在庫数を自動で減らすだけでなく、次に何をすればいいかまで見える管理表になりますよ。
ミスを防ぐための入力ルールと見やすい表の作り方
関数を入れた在庫管理表は便利ですが、入力ルールがあいまいだと、正しい在庫数を保てません。
よくあるミスはこんな感じです。
– 商品名を手入力して表記がバラバラになる
– 出庫数を入庫の列に入力しちゃう
– 数式が入ったセルに上書きしちゃう
これらは、Excelの知識不足だけじゃなく、表の設計と運用ルールが整ってないことでも起こります。
関数だけで在庫を自動計算するなら、数式そのものより**「壊れにくい入力のしくみ」を作ることが大事**です。
プルダウンで商品コードを選べるようにする
まず、商品コードは手入力じゃなく、商品マスタから選べるようにします。
Excelの「データの入力規則」を使えば、商品コードをプルダウンで選択できるようになります。
商品コードを選ぶと商品名が自動表示されるようにVLOOKUPやXLOOKUPを設定しておけば、商品名の入力ミスや表記ゆれを防げます。
現在庫数は絶対に手入力しない
在庫一覧の現在庫数は、**絶対に手入力しないルール**にしましょう。
現在庫数は入出庫履歴から計算される結果なので、直接修正すると履歴と在庫数のつながりが切れちゃいます。
棚卸しで実際の在庫と差が出た場合も、現在庫セルを直接書き換えるんじゃなく、入出庫履歴に「棚卸調整」として調整行を追加するのがおすすめです。
こうしておけば、なぜ在庫数が変わったのかをあとから確認できます。
色分けで見やすくする
見やすい表にするには、**入力するセルと計算結果のセルを色分け**すると効果的です。
– 入力セル:薄い黄色
– 関数セル:薄いグレー
– 警告表示:赤系
こうすると、担当者がどこを触ればいいか迷いにくくなります。
また、行数が増えても見出しが見えるように**ウィンドウ枠を固定**したり、商品コードや日付で**フィルター**をかけられるようにしておくと、履歴確認がしやすくなります。
運用ルールを決めておく
運用を始める前に、最低限こんなルールを決めておくと安定します。
– 入庫・出庫は発生した当日中に入出庫履歴へ入力する
– 現在庫数や発注判定のセルは直接編集しない
– 棚卸しで差が出たら履歴に調整行を追加して記録する
– 商品名じゃなく商品コードを基準に管理する
こういうルールを最初に共有しておけば、担当者が変わっても管理方法がブレにくくなります。
Excel在庫管理は低コストで始められる反面、人が入力する以上、ヒューマンエラーは必ず起こります。
だからこそ、間違えない前提じゃなく、**間違えても原因を追える表にしておく**ことが大切です。
Excel在庫管理表を長く使うための注意点と限界
Excel関数だけで作る在庫管理表は、小規模な商品管理や社内備品、単一倉庫の在庫管理にはとても向いています。
専用システムを導入しなくても始められるし、商品項目や管理方法を自社に合わせて柔軟に変えられるからです。
特に、商品数が数十点から数百点くらいで、入出庫件数も日々管理できる範囲なら、SUMIFやVLOOKUPを使った在庫一覧で十分実用的な管理ができます。
Excelの限界も知っておこう
ただし、Excelには限界もあります。
– 入出庫履歴が何万行にも増えるとファイルが重くなって、保存や再計算に時間がかかる
– 複数人で同時に編集する場合、ローカルファイルでは上書きや更新漏れが起きやすい
クラウド版のExcelやOneDrive、SharePointを使えば同時編集はしやすくなりますが、それでも入力ルールがあいまいだと在庫差異は発生します。
Excelはあくまで表計算ソフトなので、在庫管理専用システムとは役割が違うことを理解しておきましょう。
バックアップとシート保護は必須
長く使うためには、**バックアップとシート保護**も欠かせません。
– 数式が入っている在庫一覧や商品マスタの重要セルは、誤って編集されないように**シート保護**を設定
– 月末や棚卸し後にはファイルをコピーして保存し、「在庫管理表_2026年05月末確定.xlsx」みたいにわかりやすい名前を付ける
こうしておけば、過去データとの比較やトラブル時の復元がしやすくなります。
最初から高機能にしすぎない
在庫管理表を改善していくとき、最初から高機能にしすぎないのもポイントです。
発注点、在庫金額、倉庫別在庫、ロット番号、賞味期限…と、入れたくなる項目はたくさんありますが、項目が増えるほど入力負担も増えます。
まずは**「商品コード別に、入出庫履歴から現在庫が自動でわかる」状態を安定させて**、そのあとに必要な項目を追加していくほうが失敗しにくいです。
システム移行を検討するタイミング
もし、複数店舗や複数倉庫の在庫をリアルタイムで連携したい、ECサイトの受注と自動連動したい、バーコードで入出庫処理をしたい…という段階になったら、Excelだけで対応するのは難しくなります。
その場合は、在庫管理システムや受発注システムへの移行を検討するタイミングです。
Excelで在庫管理の基本を整えておけば、システム移行時にも商品マスタや入出庫履歴を整理しやすくなりますよ。
まずは関数だけの在庫管理表で「正しく記録して、正しく計算する」しくみを作って、事業規模に合わせて次の管理方法へステップアップしていくのが現実的です。
まとめ
この記事を参考に、まずはシンプルな在庫管理表から始めてみてくださいね!
広告
