SpreadHub
Excelの在庫管理表を、「商品マスタ」と「入出庫」の2シートで作ります。商品マスタには商品名や開始時点の在庫を、入出庫には日々の入庫・出庫を1行ずつ記録します。現在庫は「開始時点の在庫＋入庫−出庫＋棚卸調整」で計算するため、残数を手で書き換える必要はありません。
マグカップ20個の在庫を例に、表の項目とSUMIFSの数式を順に設定します。棚卸で1個足りなかった場合の直し方も扱います。同じ数式を入れた練習ファイルも用意しました。ダウンロードして数字を変えながら確認できます。
小さな店舗やEC事業の商品、社内備品などを、担当者が入出庫のたびに記録する使い方に向いています。
完成すると、入出庫の記録から現在庫が変わる
下の表が完成形です。白いマグカップは期首20個＋入庫10個−出庫8個−棚卸調整1個＝現在庫21個。発注点を25個に設定しているため、「要発注」と表示されます。
記事と同じ練習用Excelを使う
「商品マスタ」「入出庫」の2シートに、2商品・4件の履歴と本文の数式を入れています。登録不要でダウンロードできます。
ダッシュボードや印刷用の棚卸表も使いたい方には、設定済みのテンプレートを用意しています。販売テンプレートの画面と機能を見る
まず、練習ファイルの「入出庫」D2を10から11へ変えてみてください。入庫数を1個増やすと、「商品マスタ」H2の現在庫も21個から22個に増えます。試したらD2を10に戻してください。
在庫管理に必要な2枚のシート
新しいExcelブックを開き、2枚のシートに「商品マスタ」「入出庫」と名前を付けてください。後で使う数式にも、このシート名を指定しています。どちらの表も1行目を見出しにし、2行目からデータを入力します。
商品マスタ：商品コードと開始在庫を決める
まず「商品マスタ」のA1〜D3に、次の内容を入力してください。「期首在庫」には、管理を始める直前に数えた実在庫を入れてください。年度の途中からでも始められます。
表は左右にスクロールして確認できます →
| A：商品コード | B：商品名 | C：期首在庫 | D：発注点 |
|---|---|---|---|
| A-001 | マグカップ（白） | 20 | 25 |
| A-002 | マグカップ（黒） | 12 | 5 |
白と黒のように別々に数える商品には、別のコードを付けます。「入出庫」にも、商品マスタと同じコードを入力してください。数量の単位は「個」にそろえます。1箱に12個入っている商品を入庫した場合も、入力する数量は12個です。
続けて、E1〜I1に「入庫計」「出庫計」「調整計」「現在庫」「状態」を入力します。この5列は次の手順で数式を入れる列です。
入出庫：在庫が動くたびに1行追加する
「入出庫」のA1〜F5に、次の例を入力してください。この例では2026年9月1日の営業開始前に在庫を数え、商品マスタの期首在庫に記録しています。入出庫シートには、その後に動いた数量を入力します。
表は左右にスクロールして確認できます →
| A：日付 | B：商品コード | C：区分 | D：数量 | E：伝票番号 | F：メモ |
|---|---|---|---|---|---|
| 2026/9/1 | A-001 | 入庫 | 10 | IN-001 | 仕入れ |
| 2026/9/2 | A-001 | 出庫 | 8 | OUT-001 | 販売 |
| 2026/9/2 | A-002 | 出庫 | 2 | OUT-002 | 販売 |
| 2026/9/3 | A-001 | 棚卸調整 | -1 | ADJ-001 | 実物21個。記録を確認後、差異1個を調整 |
入庫・出庫の数量は、どちらも正の数で入力してください。出庫8個を「-8」と入力すると、後の数式でさらに差し引くため、在庫が増えてしまいます。マイナスを使うのは、この例では在庫を減らす棚卸調整です。
期首在庫20個を、入出庫にも「入庫20個」として書くと二重計上になります。入出庫へ記録するのは、管理開始後に実際に動いた分だけです。
SUMIFSで現在庫を自動計算する
SUMIFSは、複数の条件に合う行の数値を合計する関数です。今回は「商品コードが一致する」「区分が入庫・出庫・棚卸調整のいずれか」という2条件で数量を集計します。MicrosoftのSUMIFS関数の説明
「商品マスタ」の2行目に、次の数式を入れます。下記では「入出庫」の2〜2001行目、つまり最大2,000行を集計対象にしています。
数式を入れるには、「商品マスタ」の対象セルを選び、式全体を貼り付けてEnterを押してください。「=」から最後の「)」までコピーしてください。最初に入力するE2は、E列と2行目が交わるセルです。下の式を入れると、入庫数の合計「10」が表示されます。入力した式は、セルを選ぶと数式バーで確認できます。
E2：商品ごとの入庫数を合計
=IF($A2="","",SUMIFS('入出庫'!$D$2:$D$2001,'入出庫'!$B$2:$B$2001,$A2,'入出庫'!$C$2:$C$2001,"入庫"))SUMIFSの集計対象は、「入出庫」のB列が「商品マスタ」A2の商品コードと一致し、C列が「入庫」の行です。その行のD列にある数量を合計した値が、E2の入庫計です。「商品マスタ」A2が空欄なら、E2も空欄になります。
F2・G2：出庫数と棚卸調整を合計
F2には、条件を「出庫」にした式を入力してください。
=IF($A2="","",SUMIFS('入出庫'!$D$2:$D$2001,'入出庫'!$B$2:$B$2001,$A2,'入出庫'!$C$2:$C$2001,"出庫"))G2には「棚卸調整」の式を入力してください。調整数量のプラス・マイナスは、そのまま合計します。
=IF($A2="","",SUMIFS('入出庫'!$D$2:$D$2001,'入出庫'!$B$2:$B$2001,$A2,'入出庫'!$C$2:$C$2001,"棚卸調整"))H2：期首在庫に増減を加える
=IF($A2="","",C2+E2-F2+G2)E2〜H2を選んでコピーし、E3を選んで貼り付けます。コピー後にE3を選び、商品コードの参照だけが「$A3」になったことを数式バーで確認してください。例の入力ができていれば、次の結果になります。
表は左右にスクロールして確認できます →
| 商品 | 期首 | 入庫計 | 出庫計 | 調整計 | 現在庫 |
|---|---|---|---|---|---|
| A-001 白 | 20 | 10 | 8 | -1 | 21 |
| A-002 黒 | 12 | 0 | 2 | 0 | 10 |
数式の「$」は、コピー先でも同じ集計範囲を使うための記号です。省かずにコピーしてください。
集計できるのは「入出庫」の2〜2001行目です。2,002行目以降も使う場合は、E〜G列の3本のSUMIFSを修正します。数量・商品コード・区分の参照範囲を、すべて同じ行まで延ばしてください。
この式は、入力した履歴全体の残数を出します。日付による締め処理や特定日の在庫再現は行いません。未入荷の注文や未来の出荷予定は混ぜず、実績だけを入力します。
練習ファイルでは、商品マスタの2〜3行目と、入出庫のコード確認G2〜G5に数式を入れています。行を増やしても、数式は自動では追加されません。商品を追加したらE〜I列の式、入出庫を追加したらG列の式を新しい行へコピーしてください。
発注点に達した商品を見つける
発注点は、補充を検討し始める在庫数です。この例では25個に設定します。自分の商品では、仕入れにかかる日数と、その間に必要な数量をもとに決めてください。
「商品マスタ」のI2に次の式を入れ、3行目へコピーします。
=IF($A2="","",IF(H2<0,"要確認",IF(H2=0,"欠品",IF(H2<=D2,"要発注","在庫あり"))))A-001は21個で発注点25個以下なので「要発注」、A-002は10個で発注点5個を超えるので「在庫あり」です。発注点と同数になった時点も「要発注」に含めます。負の在庫は、まず入力漏れや誤記を調べるため「要確認」と分けています。
商品マスタの見出しを含む表を選び、［データ］→［フィルター］で「状態」を絞り込んでください。「要発注」の商品だけを一覧にできます。この表では発注残を管理していないため、注文前に、発注済みでまだ届いていない数量も確認してください。
棚卸で数量が合わないときの直し方
棚卸とは、実物を数えて記録上の在庫と照合する作業です。上のA-001は、入庫と出庫だけなら20＋10−8＝22個ですが、実物は21個だったとします。
先に入出庫の記録を確認します。出荷1個の入力漏れが見つかったなら、出庫の記録を追加するのが先です。原因となる記録を直したうえで、同じ1個を棚卸調整すると二重に減ってしまいます。
記録を確認しても差異が残る場合は、「実在庫−帳簿在庫」＝21−22＝-1を棚卸調整として1回記録します。現在庫の数式や期首在庫を直接21に書き換えず、日付・確認した内容・担当者をメモに残してください。
表は左右にスクロールして確認できます →
| 確認する時点 | 帳簿在庫 | 実在庫 | 行うこと |
|---|---|---|---|
| 調整前 | 22個 | 21個 | 入出庫の入力漏れ・重複を確認 |
| 差異が残った場合 | 22個 | 21個 | 棚卸調整「-1」を1件登録 |
| 調整後 | 21個 | 21個 | 一致を確認。現在庫の数式は残す |
練習ファイルは調整後の状態です。「入出庫」D5を0にすると現在庫は22個、-1に戻すと21個になります。現在庫の数式を残したまま、棚卸調整の数量で直せます。
同じ棚卸結果をもう一度「-1」で登録すると、現在庫が20個になってしまいます。伝票番号や棚卸日を見て、登録済みの調整を重ねないようにしてください。実物が2個多い場合は「+2」、一致している場合は調整不要です。
入力ミスを減らす運用ルール
区分を選択式にする
「入出庫」のC2〜C2001を選び、［データ］→［データの入力規則］で「リスト」を選択します。元の値に入庫,出庫,棚卸調整を設定すれば、区分の表記ゆれを減らせます。Microsoftの入力規則の設定方法
セルに値を貼り付けると、入力規則で設定した選択肢以外の値が入ることがあります。在庫が合わないときは、区分名の余分な空白や、数量を文字列として入力していないかも確認してください。Microsoftの入力規則の注意点
集計されない商品コードを見つける
「A-001」と「A001」は別のコードです。入出庫に未登録のコードを書くと、その数量は商品マスタの現在庫へ反映されません。
商品マスタのコードがA2〜A301にある場合は、次の式を使えます。「入出庫」のG1に「コード確認」、G2に数式を入力してください。G2の式を入力済みの行へコピーすると、未登録・重複したコードを見つけられます。
=IF(B2="","",IF(COUNTIF('商品マスタ'!$A$2:$A$301,B2)=1,"OK","コード確認"))ここでは商品コードに「*」「?」を使わず、英数字とハイフンで統一します。数量は「8個」ではなく数値の「8」、日付は「2026/9/2」のように入力してください。
数字が合わないときは、コード・区分・数量・範囲の順で確認
| 症状 | 最初に確認すること |
|---|---|
| 1件だけ集計されない | G列の「コード確認」欄を見る。「A-001」と「A001」、余分な空白を見比べる |
| コードは正しいのに反映されない | C列が「入庫」「出庫」「棚卸調整」のいずれかになっているかを確認 |
| 出庫を入れたのに在庫が増えた | 出庫数量にマイナスを付けていないかを確認。出庫8個は「8」 |
| 数量があるのに合計へ入らない | D列が「8個」などの文字列ではなく、数値になっているかを確認 |
| 追加した行だけ計算されない | 商品側へ式をコピーしたか、入出庫が集計対象の2〜2001行目に入っているかを確認 |
| 計算結果ではなく数式が表示される | 先頭に「’」がないかを確認し、セルの表示形式を「標準」にして式を入力し直す。全セルで式が見える場合は［数式の表示］も確認 |
月が変わっても履歴を残す
この表では、期首在庫と管理開始後の履歴をセットで使います。月が変わったからと入出庫を削除すると、現在庫も変わってしまいます。保存用のコピーを取りながら同じ履歴へ追記し、ファイルを切り替えるときは切替時点の在庫を次の期首在庫へ引き継ぎます。
使い始める前に、表の在庫数が実物と合っているかを確かめてください。入庫・出庫・マイナスの棚卸調整も1件ずつ試し、計算どおりに増減するかを確認します。複数人で使う場合は、入力する担当と締切を決めておくと、同じ入出庫の二重登録を防ぎやすくなります。
練習ファイルの確認内容（2026年9月30日）
配布する.xlsxと同じ数式について、表計算の自動計算環境（artifact-tool）で15項目を確認しました。入庫変更、発注点との境界、在庫ゼロ・負数、棚卸調整、コード未登録・重複・空欄、集計範囲の端、試験後の復元を含みます。
- 入庫10→11：現在庫21→22
- 入庫14：現在庫25で「要発注」。入庫15：26で「在庫あり」
- 出庫29：現在庫0で「欠品」。出庫30：-1で「要確認」
- 棚卸調整-1→0：現在庫21→22
各条件を1つずつ変更し、最後は記事の初期値へ戻して保存しています。Excelアプリでの操作・再計算の確認は未実施です。記事の練習用画像は配布ファイルを描画した表示例で、Excelアプリのスクリーンショットではありません。
数式設定や見やすい表作りを省きたい方へ
練習ファイルでは、入出庫から現在庫を計算する表を作れます。数式を自分で調整したい方は、このファイルを使ってみてください。
棚卸表や在庫金額のグラフも使いたい方には、SpreadHubの「在庫管理 Excelテンプレート」をおすすめします。印刷用の棚卸表と、欠品・要発注の商品を確認できるダッシュボードを用意しています。
商品300件・入出庫2,000行分の入力欄と集計式も設定済みです。数式やレイアウトを一から作る手間を省けます。
自作で設定する部分と、販売テンプレートに用意したもの
| 使える状態にするための作業 | 販売テンプレートで用意しているもの |
|---|---|
| 品目を増やし、集計式の参照やコピー漏れを確認する | 商品300件・入出庫2,000行に対応する入力欄と集計式。現在庫や状態を計算 |
| 残数の表から、補充を検討する商品を探しやすくする | ダッシュボードに欠品・要発注の商品を表示 |
| 在庫金額や入出庫の動きを見る集計・グラフを作る | 在庫金額・入出庫推移をまとめたダッシュボード |
| 実物を数える帳票と、帳簿との差を計算する欄を作る | 印刷して使える棚卸表。実在庫を入力すると差異を計算 |
| 入力欄・計算欄を見分けやすくし、使い方を整理する | 入力欄と自動計算欄の色分け、操作ガイド、サンプル入力 |
購入後はZIPを展開し、元ファイルのコピーを保存してください。操作ガイドを読んだら、サンプルを自社の品目・期首在庫・発注点に置き換えます。開始在庫が実物と合っていることを確かめてから、日々の入出庫を記録し、棚卸で実物と照合します。
棚卸で出た差異は、確認後に「入出庫」へ棚卸調整として登録してください。棚卸表へ実在庫を入力しただけで、現在庫が書き換わる仕組みではありません。
PC版Excelでの利用をおすすめします。EC・POSとの自動連携、自動発注、発注残、複数倉庫別の在庫数量管理には対応していません。在庫金額は商品マスタの仕入単価を使った管理用の目安です。
買い切りで、テンプレート自体の月額料金はありません。Excel等の利用料金は別途必要です。現在の価格は下の商品カードに表示しています。内容が合えば、この記事内でカートへ追加できます。
すでに注文した商品の入荷待ちまで追いたい場合は、在庫の残数管理と注文の進捗管理を分けて考えます。受発注管理テンプレートの対応範囲も確認できます。ただし、2つのファイルが自動連携する商品ではありません。
執筆：SpreadHub。2026年10月1日更新。商品仕様は2026年9月30日に販売中のv1.2を確認。記事内の表・数値は説明用の作成例です。