在庫管理をエクセルで作る｜受払簿を正にする設計と製造業で要る3列

エクセルの在庫管理が壊れる理由

うまくいかないエクセルには、ほぼ共通の形がある。在庫数のセルを直接書き換えていることである。

10 個入荷したら在庫を 30 から 40 に書き換え、5 個出したら 35 に書き換える。一見わかりやすいが、この形では次のことができない。

• いつ誰が何を動かしたか分からない
• 棚卸で差が出ても、どこでずれたか追えない
• 入力を間違えたとき、元に戻せない
• 2 人が同時に触ると、後から保存した方で上書きされる

直すには、考え方を変える。在庫数は入力するものではなく、計算で出すものにする。

受払簿を正にする

シートを 3 枚に分ける。この構成が基本形になる。

受払簿の列

1 行が 1 件の入出庫である。列は次のとおり。

入庫と出庫を別の列（入庫数・出庫数）に分ける方法もあるが、1 列にして符号で持つ方が集計しやすい。入庫は正、出庫は負にする。こうすると在庫数は合計を取るだけで出る。

在庫一覧の数式

在庫数は SUMIF で出す。品番をキーに、受払簿の数量を合計する。

この形にすると、在庫一覧はどのセルも手入力にならない。壊れるとしたら受払簿の入力ミスだけになり、原因の場所が 1 つに絞られる。

製造業で最初から入れておく 3 列

単位

同じ材料でも、購買は kg、現場は本、在庫は箱で数えていることがある。品目マスタに単位を持たせ、受払簿では単位を入力させない。入力させると必ず混ざる。換算が必要なら、マスタに換算係数の列を足して数式で処理する。

保管場所（ロケーション）

同じ品番が複数の場所にあるなら、受払簿にも場所を記録する。差が出たときに「どこを数え漏らしたか」を追える。棚卸のカウントシートも場所順に出せるようになる。

発注残

発注済みでまだ入荷していない数量。これが見えないと二重発注が起きる。発注簿を 4 枚目のシートとして持ち、入荷したら受払簿に入庫行を足したうえで発注簿の入荷済フラグを立てる。

エクセルで作るときの具体的な注意

テーブルにする

受払簿と発注簿は、範囲をテーブル（Ctrl＋T）にしておく。行を足したときに数式の参照範囲が自動で広がる。普通の範囲のまま A2:A1000 のように書くと、1000 行を超えた時点で静かに集計から漏れる。

入力規則で品番を選ばせる

受払簿の品番列は、品目マスタからの選択にする。手打ちすると、全角と半角、ハイフンの有無で別品目になる。同じ品目が 2 つの品番で登録されるのが、差異のよくある原因である。

既存の行を直さない

入力を間違えたら、その行を消すのではなく取消の行を足す（同じ内容で符号を逆にする）。消してしまうと、いつ何を直したか残らない。

日付を文字列にしない

「8/27」と入力すると日付になるが、「2026.8.27」だと文字列になる。月別に集計するときに効いてくる。入力規則で日付型に限定する。

ファイルを複数の場所に置かない

各自のパソコンにコピーを持つ運用が始まると、どれが最新か分からなくなる。共有フォルダかクラウド上の 1 か所に置き、そこだけを触る。

作り始めるときの手順

今ある在庫表から移行するときの順番を書いておく。ここを飛ばすと、初日から数が合わない状態で始まる。

1. 品目マスタを整理する

現在の在庫表から品番を抜き出し、重複を先に潰す。全角と半角、ハイフンの有無、大文字と小文字。同じ品目が別行になっているものを 1 つにまとめる。ここで残った重複は、あとから必ず差異になって現れる。

あわせて単位を決める。1 品目 1 単位にする。「本でも箱でも入力できる」形にすると、換算のたびにずれる。

2. 実地棚卸をして期首残高を作る

現物を数える。その数を受払簿の 1 行目として、日付・品番・数量・区分「期首」で入れる。今の在庫表の数字をそのまま持ってこない。合っていない可能性があるものを起点にすると、移行後の差が移行前のものか運用のものか区別できなくなる。

3. 未入荷の発注を発注簿に入れる

発注済みで届いていないものを洗い出し、発注簿に入れる。ここが抜けると、移行直後に二重発注が起きる。

4. 1 か月は並行して動かす

旧来のやり方と受払簿を両方つける。月末に突き合わせ、差が出た原因を確認する。差が出るのは正常で、それが運用の穴を教えてくれる。差がなくなってから旧来のやり方を止める。

よく起きる不具合と直し方

在庫数がマイナスになる

出庫の記録が入庫より先に入っている。入荷の記録が漏れているか、日付が逆になっている。受払簿を日付順に並べ、その品番の行だけを見れば場所が分かる。

ある品番だけ集計に出てこない

品番の表記ゆれである。受払簿の品番と品目マスタの品番が文字として一致していない。末尾の空白が入っていることが多い（貼り付けたときに混入する）。

合計が途中までしか合わない

数式の参照範囲が固定されている。テーブルにするか、列全体を参照する形に直す。

棚卸のたびに同じ品目で差が出る

これはエクセルの問題ではない。持ち出しが記録されていない、置き場が複数ある、不良品が良品と混ざっている——現場側に原因がある。表を直しても消えない。

棚卸との突き合わせ

受払簿があると、棚卸は「数えた数と数式の結果を比べる」だけになる。

棚卸シートを追加する

棚卸日・品番・保管場所・実地数量・数えた人の列を持つシートを作る。在庫一覧の横に実地数量を引いてきて、差異の列を作る。

帳簿を実地に合わせるとき

在庫一覧のセルを書き換えてはいけない。受払簿に「棚卸調整」の行を足す。日付・品番・数量（差異と同じ符号）・区分「棚卸調整」で 1 行。こうすれば、いつどれだけ調整したかが記録として残る。

差異の処理そのものについては棚卸減耗損と棚卸評価損の違いで扱っている。棚卸表の作り方は棚卸表の作り方にまとめた。

金額をどう出すか

期末の在庫金額は、選定して届け出た評価方法で計算する^[1][2]。エクセルでどこまでできるかは方法によって差が大きい。

届出をしていない場合は、最終仕入原価法による原価法が法定の評価方法になる^[1]。自社が何で届け出ているかを先に確認する。エクセルで作れる方法に合わせて評価方法を選ぶ、という順序にはできない。

法令の保存要件との関係

在庫表そのものに法令上の様式要件はない。ただし取引先とメールや Web でやり取りした注文書・請求書などの電子取引データは、電子のまま保存することとされている^[3]。

要件は真実性・可視性・検索の 3 つで、検索は取引年月日・取引金額・取引先の 3 項目で行えることが求められる^[3]。真実性については、訂正削除の防止に関する事務処理規程を定めて運用する方法が認められている^[4]。専用のシステムがなくても要件は満たせるということである。

詳しくは電子帳簿保存法にシステム無しで対応するで扱っている。

エクセルで限界が来る境目

受払簿の形にしても、次のどれかに当てはまると運用が重くなる。

同時に入力する人が 3 人以上いる

共有ブックにしても、行の競合と保存待ちが起きる。入力を 1 人に集約すると記録が後追いになるため、結局差が出る。

受払簿が数万行を超える

SUMIF を品目数ぶん計算するため、開くたびに待たされるようになる。値貼り付けで期首残高を作り、受払簿を期ごとに切る対処はできるが、履歴が分断される。

ロットや製造番号を追う必要が出た

材料の受入ロットと製品の製造番号を紐付けるには、1 対多の関係を持たせることになる。表計算の形では管理しきれない。

現場から入力したい

倉庫や工場でその場で記録したい場合、ファイルを開いて行を足す形は成立しない。この時点でシステムを検討する。判断の材料は在庫管理システムの選び方にまとめた。

よくある質問

関数は何を覚えればよいか

SUMIF／SUMIFS（条件つき合計）、XLOOKUP または VLOOKUP（マスタから引く）、COUNTIFS（件数）の 3 つで足りる。ピボットテーブルが使えると、月別・品目別の集計が速い。

マクロは使うべきか

作った人しか直せなくなるため、担当者が変わると止まる。使うなら、入力の補助（行の追加、日付の自動入力）に限る。計算そのものをマクロでやると、結果を検算できなくなる。

スプレッドシートでもよいか

複数人が同時に編集する点では有利である。関数の考え方は同じで、SUMIF も使える。ただし行数が増えたときの速度はエクセルより落ちやすい。

発注点の管理もできるか

できる。品目マスタに発注点と発注量の列を足し、在庫一覧で「現在庫 ＋ 発注残 ＜ 発注点」の行に色を付ける（条件付き書式）。ただし発注点そのものを決めるには、消費量とリードタイムの実績が要る。

入力を現場に任せられないときは

紙の入出庫伝票を回してもらい、事務所でまとめて入力する形になる。この場合、伝票番号を必ず受払簿に記録する。あとで差が出たときに現物の伝票まで戻れる。伝票が回ってこないことが多いなら、それ自体が差異の原因である。

過去分をさかのぼって作れるか

仕入と出庫の記録が残っていれば作れるが、手間に見合わないことが多い。実地棚卸をして期首残高を作り、そこから先を記録する方が速い。過去の差の原因は、どのみち追えない。

支給材はどう管理するか

所有区分の列を品目マスタに持たせ、自社品・預り品・預け品を分ける。有償支給の場合、発注時に品名・数量・対価・引渡しの期日・決済期日・決済方法を明示する義務がある^[5]。この記録と受払簿を突き合わせられるよう、伝票番号を共通にしておく。

まとめ

エクセルで在庫を管理するときの分かれ目は、機能ではなく設計にある。在庫数を書き換える形にした時点で、差が出ても原因を追えなくなる。

受払簿を正にし、在庫数は数式で出す。これだけで、いつ誰が何を動かしたかが残り、棚卸の差異も追えるようになる。既存の行は直さず、取消や調整も行を足して表現する。

製造業なら、単位・保管場所・発注残の 3 つを最初から列に入れておく。後から足すと、過去の行が空欄のまま残って使いものにならない。

そして、表を整えても消えない差がある。持ち出しの記録、置き場、不良品の扱い——現場側の運用に原因があるものは、システムに替えても同じように残る。エクセルで一度受払簿の形にしてみると、どこに穴があるかがはっきりする。それが分かってから、システムを検討するかどうかを決めればよい。

出典・参考資料

1. 法人税法施行令 第10条（棚卸資産の範囲）・第28条（評価の方法）・第31条（法定評価方法）（e-Gov 法令検索）
2. C1-25 棚卸資産の評価方法の届出（国税庁）
3. 電子帳簿保存法上の電子データの保存要件（国税庁）
4. 電子帳簿保存法一問一答【電子取引関係】（国税庁）
5. 中小受託取引適正化法 テキスト「下請法」から「取適法」へ（公正取引委員会・中小企業庁／令和 7 年 11 月）

WHITE PAPER

この記事の理解を深める
無料ホワイトペーパーをプレゼント

製造業の現場で使える実務資料（PDF）を無料でお届けします。"こんな資料が届きます" ↓ 下のボタンからどうぞ。

🔒 全文は無料ダウンロードで