在庫管理表をエクセルで作る方法と、出荷が増えたときの限界

在庫管理表をエクセルで作るなら、入出庫を1行ずつ書き足す「記録」、品番ごとに記録を足し上げる「現在庫」、現在庫が発注点を下回った品目に印を付ける「発注点」の3枚に分けるのがいちばん長持ちします。入力するシートと式のあるシートを分けておけば、担当者が増えても式のセルを上書きされず、行を足しても集計の範囲から漏れません。品目が数百、1日の入出庫が数百行までなら、この形で十分に回ります。

そのうえで、出荷が増えてくると、エクセルの在庫管理表は2つの点で追いつかなくなります。1つは記録の入力が実際の動きより遅れること、もう1つは過去の平均から出した発注点が、増えていく出荷に対して遅れて鳴ることです。この記事では、記事のおまけのひな形と同じ形で、3枚それぞれの列と式、式を壊さない守り方、そして限界が来たときにどこから手を付けるかを、倉庫の数字の例で書いていきます。

在庫管理表は、記録・現在庫・発注点の3枚に分けると式が壊れにくくなります

入力する人が触るのは「入出庫の記録」だけにして、「現在庫」と「発注点の印」は式で自動に出すようにすると、だれが何を入れても集計が崩れません。よくある在庫表は、品目を縦に、日付を横に並べて、マス目に入荷と出荷の数を書き込む形です。見た目はわかりやすいのですが、月が替わるたびに列を足し、合計の式を横へ延ばす作業が発生し、そこで範囲の指定が1列ずれると、その月の数字がすべて狂います。記録を縦に積む形なら、月が替わっても行が下へ伸びるだけで、式は1回書いたまま触らずに済みます。

シート	入れるもの	式で出すもの	主に見る人
入出庫の記録	日付・品番・区分（入庫・出庫・返品・調整）・数量・担当	品名、在庫の増減（区分から向きを付ける）	入荷と出荷の担当者
現在庫	品番・品名・入数・棚番・期首の在庫	入庫と出庫の合計、現在庫、最後に動いた日	在庫の担当者、荷主への報告
発注点の印	リードタイム・安全の日数（見込みの出庫は任意）	直近の1日出庫、発注点、残りの日数、「発注」「欠品」の印	発注を決める人、センター長

表1のように分けると、現場の担当者は記録のシートに書き足すだけで、品目の登録や発注点の日数を変えるのは決まった1〜2人に限られます。在庫の数が合わないときに原因をたどる手順は、倉庫で数が合わない理由を工程ごとに書いた記事にまとめていますが、記録が1行ずつ残っていれば、その調べ物もどの取引で数が動いたかを品番で絞り込むだけになります。

品目ごとに1枚の「単票」は、品目が数十を超えると集計が苦しくなります

品目ごとにシートを1枚ずつ作り、その品目の入出庫だけを書いていく「単票」の形もよく見かけます。品目が20〜30のうちは、1枚を開けばその品目の動きが全部見えるので便利です。ただ、品目が増えるとシートの数が増え、全品目の現在庫を1枚の一覧にするためにシートをまたぐ式を品目の数だけ書くことになります。新しい品目を足すたびにシートをコピーし、一覧の式を1行足す作業が要るので、どこかで足し忘れが起きます。倉庫で使うなら、最初から記録を1枚にまとめる形で作るほうが、あとで作り直さずに済みます。

入出庫の記録は、1回の動きを1行で下へ書き足すだけのシートにします

記録のシートは、日付・品番・区分・数量・担当の5つを入れるだけにし、数量はいつもプラスで入れて、入庫か出庫かの向きは区分を見て式が付けるようにします。出庫をマイナスで入れる決まりにすると、急いでいる日にマイナスを付け忘れた1行が、その品目の在庫を出庫の2倍ずらします。区分を一覧から選ばせ、向きを式に任せておけば、入れる人が符号を気にする必要がなくなり、表2がその列の並びです。

列	入れ方	入力規則	ねらい
日付	手で入れる（Ctrl＋;で今日の日付）	日付の形だけ受け付ける	「11/8」のような文字で入って集計から漏れるのを防ぐ
品番	一覧から選ぶ	「現在庫」の品番の列をリストにする	全角と半角の混ざった品番や打ち間違いを入れさせない
品名	式で自動表示	（入力しない）	選んだ品番が正しいかを、名前で目で確かめられる
区分	入庫・出庫・返品・調整から選ぶ	4つの言葉のリスト	集計の条件と同じ言葉だけを入れさせる
数量	プラスで入れる（調整だけ増減で入れる）	—	符号の付け忘れで在庫がずれるのを防ぐ
在庫の増減	式で自動（出庫はマイナスにする）	（入力しない）	現在庫の集計はこの列だけを足し上げる

表2の「調整」は、棚卸で数えた数が表と合わなかったときに使う区分です。現在庫のセルを直接書き換えたくなりますが、そうすると式が消えるうえに、いつ・いくつ直したかの記録も残りません。棚卸で3個足りなければ、調整の行に「−3」と書き、メモに「11月の棚卸」と残しておきます。こうしておくと、調整の行だけを並べれば、どの品目が何度ずれたかがあとから一目でわかります。

現在庫は、品番ごとにSUMIFSで記録を足し上げて期首の数に加えます

現在庫のシートでは、品番ごとに「期首の在庫＋入庫の合計−出庫の合計＋返品と調整」を出し、その合計はすべて記録のシートをSUMIFS関数で品番と区分の2つの条件で足し上げて求めます。SUMIFは条件が1つしか付けられないので、入庫と出庫を分けて数えたいときにはSUMIFSを使います。期首の在庫には直近の棚卸で数えた数を入れ、図1は、1行の記録が現在庫と発注点の印に届くまでの流れです。

記録：11/7　B-2010　出庫　39個（増減の列は−39）

▼

現在庫：SUMIFSで品番B-2010の入庫と出庫をそれぞれ足し上げ、期首の300個に加える

▼

現在庫は218個（入庫240・出庫322）

直近28日の出庫を稼働24日で割り、1日13.4個

▼

発注点の印：現在庫と発注点を比べ、下回っていれば「発注」と出す

図1の2段目で使う式は、ひな形では「=SUMIFS(記録の増減の列, 記録の品番の列, この行の品番, 記録の区分の列, “入庫”)」の形です。範囲は記録のシートの3,000行目までを最初から指定してあるので、記録が増えても式を延ばす必要はありません。もう1つ入れておくと便利なのが「最後に動いた日」で、品番ごとに記録のいちばん新しい日付を出しておくと、3か月以上動いていない品目を並べ替えで拾えます。

発注点は、1日の出庫に「リードタイム＋安全の日数」を掛けて出します

発注点は「1日あたりの出庫×（発注してから届くまでの日数＋安全のための日数）」で出し、現在庫がこの数を下回った品目に「発注」の印を付けます。1日あたりの出庫は、直近28日の出庫の合計を、その間に稼働した日数で割って求めます。リードタイムは仕入先ごとに違うので品目ごとに入れ、安全の日数は、出荷のぶれが大きい品目や、仕入先の納期が遅れやすい品目ほど長めにし、表3にはひな形の見本の5品目で計算した結果を並べました。

品番	現在庫	直近の1日出庫	リードタイム＋安全の日数	発注点	印
A-1001	576	28.5	5＋3＝8日	228	—
A-1002	44	8.4	5＋3＝8日	68	発注
B-2010	218	13.4	7＋4＝11日	148	—（見込み28個なら308で「発注」）
C-3005	123	11.4	4＋2＝6日	69	—
D-4100	18	3.4	10＋5＝15日	52	発注

表3で見てほしいのはB-2010で、過去28日の平均で見ると発注点は148個で、現在庫の218個はまだ余裕があるように見えます。ところが、来週から荷主の販促で1日28個ほど出ると聞いているなら、発注点は308個になり、今日の時点ですでに発注しなければ間に合いません。ひな形では、こうした品目のために「見込みの1日出庫」の列を用意し、そこに数を入れたときは過去の平均よりそちらを優先して発注点を計算します。印の横には「残りの日数」（現在庫÷1日の出庫）も出るので、あと何日で棚が空くかを数字で話せます。

式が壊れる原因は上書き・行の挿入・範囲の外への入力で、保護と入力規則で防げます

エクセルの在庫管理表が使えなくなるのは、式のセルに数を上書きされたとき、途中に行を挿入されたとき、集計の範囲より下に記録を書かれたときの3つがほとんどで、どれもシートの保護と入力規則と範囲の取り方で前もって防げます。作った人が替わると、数字がおかしいことだけが残って原因がわからなくなるので、表4に壊れ方ごとの防ぎ方をまとめました。

壊れ方	起きること	防ぎ方
式のセルへの上書き	現在庫に手で数を入れられ、以後その品目だけ記録が反映されない	入力するセルだけロックを外し、シートを保護する
途中への行の挿入	挿入した行が集計の範囲から外れたり、式がその行に入っていなかったりする	記録は下へ書き足すだけと決め、並べ替えはフィルターで見るだけにする
範囲の外への入力	3,001行目以降の記録が現在庫に足されない	範囲を余裕を持って取り、最後の行の近くに「ここまで」の注意を書く
言葉の揺れ	「出庫」と「出荷」、全角と半角の品番が混ざり、条件に合わず集計から漏れる	品番と区分は入力規則のリストから選ばせる

表4のうちいちばん効くのはシートの保護で、エクセルのセルは最初はすべてロックされた状態なので、入力に使う黄色のセルだけ「セルの書式設定」の保護でロックを外し、そのうえで「校閲」から「シートの保護」をかけると、灰色の式のセルは選べても書き換えられなくなります。同じ守り方は、シフト表をエクセルで自動作成する記事でも、記号を入れるマス以外を守るのに使っています。パスワードを付けるかどうかは、作った人が異動したあとに直せる人がいるかで決めると、あとで困りません。

テーブル機能は、行が増えても範囲を自分で延ばしてくれます

記録を「テーブル」（Ctrl＋Tで作る範囲）にしておくと、下へ書き足した行は自動でテーブルの一部になり、テーブル名と列名で書いた式（構造化参照）は範囲を自分で延ばすので、3,000行で足りるかを心配しなくて済みます。ただ、列名で書いた式は慣れない人には読みにくいので、引き継ぐ相手がエクセルに詳しくないなら、ひな形のように固定の範囲を広めに取っておき、足りなくなったら範囲を延ばす手順を1行書いておくほうが、直せる人が増えます。

出荷が増えると、記録の遅れと発注点の遅れで表が現場に追いつかなくなります

出荷が増えたときにエクセルの在庫管理表が先に音を上げるのは行の数ではなく、入力が夕方のまとめ打ちになって日中の現在庫が古くなることと、過去の平均から出した発注点が伸びていく出荷に遅れることの2つです。エクセルの1枚のシートには1,048,576行まで入るので、1日800行の記録を年に250日入れても、上限に届くまで5年以上かかります。それより先に、出荷の担当が伝票の束を終業前にまとめて打つようになり、昼の時点の現在庫が朝の数のままになります。昼に追加の注文を受けるかどうかを、半日前の在庫で決めることになるわけです。

見る点	エクセルのままで回る	移す準備を始める
記録の入れ方	入出庫のたびに、その場で1行入れられている	伝票をためて、夕方や翌朝にまとめて打っている
同時に入れる人	1〜2人で、ほかの人は見るだけ	入荷と出荷の担当が同じ時間に同じファイルを開く
置き場所	品目ごとに棚が決まっている	空いた棚に置き、棚番まで記録したい
発注点	出荷が平らで、過去の平均で間に合う	販促や季節で出荷が跳ね、見込みの列を毎週書き直している

表5の上の3つは、在庫の記録を倉庫管理のシステムに移すかどうかの話で、ハンディ端末で読んだその場で在庫が動くしくみに替えれば解けますが、4つ目の発注点はシステムを替えても残ります。過去の平均は、出荷が増え始めてから数週間たってようやく上がるので、増える前に発注点を上げるには、品目ごとの出荷の見込みが要ります。エクセルで見込みを立てる方法は、需要予測をエクセルでやる方法の記事で関数と予測シートの使い方から書いています。品目が数百を超え、見込みを毎週手で書き直すのが追いつかなくなったら、そこが出荷予測を道具に任せる時期です。

出荷の見込みが品目ごとに立つと、使い道は発注点だけにとどまりません。同じ見込みを合計すれば、日ごとの入荷と出荷の物量になり、それを1人1時間あたりにこなせる量で割れば、持ち場ごとに何人いるかが出ます。在庫を絞って入荷の回数が増えた日に、検品の人数が足りずに残業で埋める、という食い違いも、発注と人数を同じ見込みから決めておけば起きにくくなります。物量の見込みから必要な人数を決めるやり方は、出荷物量から必要人員を決めるページにまとめています。

この記事のおまけは本文の3枚をそのまま入れたひな形で、入出庫の記録は3,000行、品目は300まで用意し、品番と区分は一覧から選ぶ形、式のセルはシートの保護で守ってあります（パスワードは付けていないので、「校閲」から外せます）。見本の5品目と約60行の記録が入っているので、基準日を2026年11月7日にすると表3と同じ結果が出ます。見本を消して、自分の倉庫の品目と期首の在庫を入れるところから使えます。

よくある質問

出典・参考：Microsoft サポート「Excel の仕様と制限」（ワークシートの行数）／Microsoft サポート「Excel テーブルで構造化参照を使用する」。本記事の在庫と出庫の数字は、おまけのひな形の見本として置いたものです。内容は2026年11月時点の公開情報に基づきます。