在庫管理表をスプレッドシートで作る｜入出庫の記録から在庫数を自動計算

在庫数を直接書き換える表がずれる理由

よくある在庫表は、商品ごとに「在庫数」の列があり、入荷や出荷のたびにその数字を書き換える形です。この形だと、書き換えを忘れた、2 人が同時に直した、という理由で実際の在庫と数がずれても、どこで間違えたかをたどれません。

この記事では、入庫と出庫の記録を 1 行ずつ積み上げ、在庫数は式で計算する形の在庫管理表を作ります。記録が残るので、数が合わないときに履歴から原因を探せます。

シートの構成

1 つのスプレッドシートに 2 つのシートを作ります。

使い始めるときの在庫は、「初期在庫」として入庫の行で入れておきます。

入力の間違いを防ぐプルダウン

入出庫のシートの B 列 (商品コード) と C 列 (区分) は、プルダウンから選ぶ形にします。

• B 列: B2 から B1000 を選んで右クリック > [プルダウン]。[条件] を「リストを範囲で指定」にし、=商品マスタ!$A$2:$A$500 を指定します。
• C 列: C2 から C1000 を選んで同じく [プルダウン] を開き、項目に「入庫」「出庫」を入れます。

公式ヘルプによると、初期設定ではリストにない値は入力できません。存在しない商品コードや「入荷」などの別の書き方が入らないので、集計から漏れる行がなくなります。作り方の詳細は プルダウンの作り方 で説明しています。

在庫数を計算する式

商品マスタの D2・E2・F2 に次の式を入れ、商品の行の数だけ下へコピーします。

D2 (入庫計): =SUMIFS(入出庫!$D$2:$D$1000,入出庫!$B$2:$B$1000,$A2,入出庫!$C$2:$C$1000,"入庫")
E2 (出庫計): =SUMIFS(入出庫!$D$2:$D$1000,入出庫!$B$2:$B$1000,$A2,入出庫!$C$2:$C$1000,"出庫")
F2 (在庫数): =D2-E2

SUMIFS 関数は、すべての条件に合う行だけを合計する関数です。ここでは「商品コードがこの行の商品と同じ」かつ「区分が入庫 (または出庫)」の行の数量を合計しています。合計する範囲と条件の範囲は、どれも 2 行目から 1000 行目までにそろえておきます。

発注点を下回った商品に色を付ける

在庫数が発注点以下になった商品の行に色を付け、発注の漏れを防ぎます。

1. 範囲を選ぶ

   商品マスタの A2 から F500 を選びます。

2. 条件付き書式を開く

   上部のメニューの [表示形式] > [条件付き書式] を押します。

3. カスタム数式を入れる

   [セルの書式設定の条件] で [カスタム数式] を選び、次の式を入れます。

   =AND($A2<>"",$F2<=$C2)
   

4. 色を選んで完了

   背景色に薄い赤などを選び、[完了] を押します。

$F2 と $C2 のように列だけを $ で固定すると、行のどのセルでも在庫数と発注点で判定されるため、行全体に色が付きます。この書き方は、条件付き書式の公式ヘルプで紹介されている、行全体を強調する例と同じ形です。$A2<>"" は、まだ商品が入っていない空の行に色が付かないようにするための条件です。

運用するときのコツ

• 記録を消さない: 入力を間違えたときは行を消さず、逆の区分で同じ数量の行を足して打ち消すと、履歴がきれいに残ります。
• 棚卸で数を合わせる: 実際に数えた数と在庫数が違ったら、差の分を入庫か出庫で「棚卸調整」として記録します。
• 編集できる人を絞る: 商品マスタの式を消されないよう、入力する人には入出庫のシートだけを触ってもらい、共有の権限も見直します (スプレッドシートの共有方法)。

自分で作るのが難しいときは

商品が数十点で、記録する人も数人までなら、このテンプレートで十分に回せます。ただ、拠点ごとに在庫を分けたい、バーコードで入出庫を記録したい、発注点を下回ったらチャットに通知したい、となると、関数だけでは手が回らなくなります。

GAS を組み合わせると、毎朝在庫を確かめて発注が必要な商品を通知する、といった仕組みを作れます。シートでの管理に限界を感じている場合は、専用のシステムへの置き換えも含めて、シートナビの相談フォームからお気軽にご相談ください。

よくある質問