【Excel】関数だけで在庫管理する方法｜マクロ不要で自動計算！

「在庫管理って専用システムが必要そう…」そう思っていませんか？実は Excelの関数だけ でも、実務レベルの在庫管理は作れます。

今回は、

✅ マクロなし
✅ 入力は1か所だけ
✅ 在庫が自動計算される

そんな「関数だけ在庫管理表」の作り方を解説します。

今回の在庫管理の仕組み

このファイルは4つのシートで構成しています。

シート	役割
マスタ	入荷・出荷の履歴入力
拠点１	拠点①の在庫
拠点２	拠点②の在庫
在庫合計	全体在庫

👉 入力するのは「マスタ」だけ
つまり、「在庫数を直接入力する」のではなく、 入荷・出荷の履歴から在庫を計算する仕組みです。

sheet1：「マスタ履歴」を作成する

在庫数を直接書き換えてしまうと、履歴が消えてしまいます。なので、入庫データ、出庫データを入力する「マスタ履歴」シートをまずは作成します。
※小規模オフィスの備品管理を想定してデータを作成します。

このように 増減の記録だけ残す 方法です。※担当者名も入力していますが、記録用に入力しているだけなので、無くても大丈夫です！

sheet2：拠点１のシートを作成する

拠点１のシートに必要な記述はコチラ。

大切なのは、ふたつ。

※「必要数」は在庫として必要な数を記述し、この数を下回ったら各部品の在庫数が赤字で表示されるようにしています。詳しくはコチラの記事をご覧ください ⇨ 「【Excel】ルールを作って表を見やすくしよう！条件付き書式でできることまとめ（画像付きガイド） – いのこblog」

在庫計算は「SUMIFS関数」を使用

在庫は次の考え方で求めます。

在庫 ＝ マスタ履歴「入荷」合計 − マスタ履歴「出荷」合計

使用する関数はこちら。

SUMIFS

C3セルに入力する関数は　⇩⇩

=SUMIFS(マスタ!E:E,マスタ!$B:$B,"<="&$A3,
        マスタ!$A:$A,"入荷",
        マスタ!$C:$C,"拠点１")
-
 SUMIFS(マスタ!E:E,マスタ!$B:$B,"<="&$A3,
        マスタ!$A:$A,"出荷",
        マスタ!$C:$C,"拠点１")

この数式を他の在庫表示セルにコピーすればOK！

数式の意味（超かんたん解説）

SUMIFS関数は、「複数の条件に一致するデータの合計を求める関数」できる関数です。

=SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...)

今回の場合、

これでマスタ履歴の入荷、出荷をそれぞれ計算して引き算して在庫を計算しています！

日付を変えるだけで過去在庫も確認できる

この仕組みの最大メリット。
日付を変えると…

✅ 月末在庫
✅ 過去在庫
✅ 棚卸時点

すべて自動計算されます。
在庫管理ソフトと同じ考え方です。

sheet3：拠点２のシートを作成する

拠点２のシートは、拠点１のシートをコピーして作成します。
変更箇所は２個所。

【変更１】日付

日付を拠点１と同期するために、拠点２の日付欄はこの数式を入力しています。

=IF(拠点１!A3="","",拠点１!A3)

こうすることで、

になります。

【変更２】在庫計算式を「拠点２」に変更

拠点２の在庫を計算する必要があるので、在庫計算数式の「拠点１」を「拠点２」に変更する必要があります。これの一番簡単な変更方法は置換を使用する事。

関数が入力されている範囲を選択し、Ctrl+Hで一括置換しましょう！

sheet4：在庫合計も自動化

最後は各拠点を合計するだけ。日付は拠点２と同様に、拠点１の日付と同期しているので足し算するだけ！

=拠点１の在庫 + 拠点２の在庫

これで全体在庫が完成します。

この在庫管理のメリット

✔ マクロ不要
✔ 壊れにくい
✔ 履歴が残る
✔ 過去在庫が出せる
✔ Excel初心者でも理解できる

実務でも十分使えると思うので、参考にしてもらえると嬉しいです！