Excelで在庫管理システムを自作するには？在庫表の作り方と運用の注意点

Excelで在庫表を作るなら、現在庫の数字を直接書き換えるのではなく、入庫と出庫の記録から計算する形にします。数が合わなくなったときに、どの取引を調べればよいか分かるからです。

この記事では、表の分け方、SUMIFSの計算式、返品や棚卸の記録方法を紹介します。すでに在庫表を使っている方は、後半の「5つのつまずき」から確認してみてください。

この記事で紹介する表や計算例は、考え方を説明するための例です。実際の運用に合わせて項目や記録ルールを調整してください。

1. 在庫表は3つに分けて作る

商品マスタ・入出庫履歴・現在庫を分ける

「商品についての情報」「入出庫の記録」「現在の残高」を、別々の表で管理します。現在庫だけを手で増減すると、数字がずれた理由を後から調べられなくなります。

商品マスタには、品番、品名、管理単位などを登録します。入出庫履歴には、入庫や出庫が起きるたびに一行追加します。現在庫は、その履歴から集計します。数量の根拠を履歴に戻って確認できる形にすることが、自作の出発点です。

ロットや使用期限を管理するなら、その項目も加えます。使わない項目まで増やす必要はありません。

数量の単位と、開始時点の在庫をそろえる

同じ品番で「箱」と「個」が混ざると、合計できても意味のある数になりません。管理の基本単位を決め、箱で入荷する場合は何個として記録するかを統一します。品番の先頭のゼロを残すため、文字列として扱うなど、データの形式にも注意します。

運用を始める時点では、実物と照合した数量を開始在庫として登録します。開始在庫を履歴に入れる方法と、別の残高表で持つ方法がありますが、両方で足さないことが大切です。以下の計算例では、開始在庫も「入庫」区分の履歴に登録し、理由に「開始在庫」と記す前提にします。

SUMIFSで品番・場所ごとに集計する

説明用の例として、入出庫履歴をExcelのテーブルに変換し、テーブル名を「履歴」、列名を「品番」「場所」「区分」「数量」とします。区分は「入庫」「出庫」のどちらか、数量は正の数に統一します。

現在庫表のA2に品番、B2に場所が入っている場合、次の式で集計できます。

=SUMIFS(履歴[数量],履歴[品番],A2,履歴[場所],B2,履歴[区分],"入庫")-SUMIFS(履歴[数量],履歴[品番],A2,履歴[場所],B2,履歴[区分],"出庫")

この式に合わせ、返品・破損・棚卸調整・保管場所の移動も、数量が増える記録は「入庫」、減る記録は「出庫」に分け、内容は「理由」列に残します。区分を追加する場合は集計式も見直さないと、その数量は現在庫に反映されません。

返品された品物を出荷可能な在庫へ戻せるかは、検品結果などに応じて別に判断します。

SUMIFSは、複数の条件に合う値を合計する関数です。この例では品番・場所・区分を条件にしています。実際に使う際は、表と列の名前を一致させ、行を追加したときも集計対象になるか確認してください。関数の引数はMicrosoft公式のSUMIFS解説で確認できます。

例えば、開始在庫20、入庫5、出庫8なら、現在庫は17です。別の品番や場所の記録を追加しても、この17が変わらないことを確認します。同じ出庫を二度入力すると、数式が正しくても在庫はずれるので、重複の確認も必要です。

品番から品名を表示する方法としては、XLOOKUPなどもあります。ただし、使える関数はExcelのバージョンで異なります。MicrosoftはXLOOKUPがExcel 2016・2019では利用できないと案内しています。利用者全員の環境で開いて確認し、必要に応じて別の検索方法を選びます。出典：Microsoft公式のXLOOKUP解説。

入力欄と計算欄を分けて、例外を試す

品番や区分をリストから選ぶ入力規則、計算セルの保護、在庫がマイナスになったときの強調表示などは、誤りに気づく助けになります。ただし、入力補助を設定しただけで全件が正しいと考えず、集計結果と伝票を照合する確認も残してください。

入庫・出庫の通常処理に加え、返品、破損、棚間移動、数量訂正を試します。保管場所AからBへ移す場合は、Aの出庫とBの入庫を同じ移動番号で結び付ける、といったルールが必要です。片側だけ登録すると、全体の数が増減してしまいます。

注文を受けて確保した数量である「引当」と、実際に出庫した数量も分けて考えます。棚にある数と、新しい注文に回せる数は同じとは限りません。引当まで管理するなら、確保・解除・出庫のどこで数量を変えるかを設計し、二重に差し引かないようにします。

発注点以下の品番に色を付ける

商品マスタなどに発注点を用意し、現在庫表にも表示します。説明用にE列を現在庫、F列を発注点とし、両方に数値が入っている場合、2行目からの対象範囲に条件付き書式の数式 =$E2<=$F2 を設定すると、発注点以下の行を強調できます。

適用範囲の先頭行と式の行番号をそろえ、発注点と同じ数・上回る数・下回る数で試してください。未設定の発注点を0として判断しないよう、空欄の行は対象から除きます。

場所ごとに基準が違う場合は品番と場所の組み合わせで管理します。色は確認の目印なので、誰がいつ発注を判断するかも決めます。設定方法はMicrosoft公式の条件付き書式の解説で確認できます。

2. 運用でつまずきやすい5つの点

① 入力が遅れ、帳簿の数が現場に追いつかない

商品を動かしたあと、伝票をまとめて入力していると、入力されるまでの間は帳簿と実物に差が出ます。更新担当者を増やす前に、いつの在庫を使って判断したいのかを決めます。出荷の直前に必要なのか、日末の集計でよいのかによって、必要な仕組みが変わるためです。

「入庫した時刻」と「入力した時刻」を分けて記録すると、差異が入力待ちなのか、記録漏れなのかを調べやすくなります。現場で入力できない理由が端末の不足なら、ソフトの入れ替えだけでは解決しません。通信環境や作業場所も含めて確認します。

② 複数のファイルや更新ルールが混在する

担当者ごとのコピーをメールで回す運用では、どのファイルが正しいか分からなくなります。ただし、Excelそのものが同時編集できないわけではありません。対応するExcelとOneDrive・SharePoint Onlineなどの条件を整えれば、共同編集できます。対応形式やバージョンはMicrosoft公式の共同編集の案内を確認します。

共同編集でファイルを一本化できても、「同じ出庫を別の担当者が二度登録する」「返品の処理方法が人によって違う」といった問題は残ります。正本の保存場所とあわせて、取引を特定する番号や担当範囲、訂正方法を決めます。

③ 数式や参照先の変更に気づけない

行のコピーや列の追加で数式が変わったとき、合計が表示されているだけでは正しさを判断できません。代表的な取引を使って期待する残高を手計算し、変更後の表と比べます。単純な入出庫だけでなく、返品や場所の移動も確認対象にします。

数式の保護は有効ですが、シート保護を機密情報へのアクセス制御と同じものとして扱うことはできません。Microsoftもシート保護はセキュリティ機能を目的としたものではないと説明しています。原価や取引先情報を誰に見せるかは、共有範囲を含めて別に設計します。出典：Microsoft公式のシート保護の説明。

④ 棚卸で数を直しても、理由が残らない

実物に合わせて現在庫のセルを書き換えるだけでは、次に同じ差異が出ても原因を追えません。まず、数え間違い、未入力、二重入力、単位違い、保管場所の誤りなどを確かめます。差異が確定したら、理由・確認者・調整日を記録したうえで調整する流れを決めます。

Excelには変更表示やバージョン履歴があります。「履歴が一切ない」と決めつける必要はありません。一方、セルの変更履歴だけで、出庫の理由や承認まで説明できるとは限りません。必要な記録をどれだけの期間保管するか、誰が確認するかを明確にします。機能と対象環境はMicrosoft公式の変更表示の案内を確認してください。

⑤ マクロを作った人に運用が集中する

マクロは繰り返し作業の自動化に役立ちます。ただし、入力用のボタンから集計、外部システムへの転記まで一人が作っていると、その人が不在のときに止まることがあります。処理の目的、入力と出力、実行手順、失敗したときの戻し方を記録し、別の担当者にも試してもらいます。

ブラウザで使う場合の条件も確認が必要です。Excel for the webではVBAマクロの作成・実行・編集はできません。マクロを含むブック自体は開けても、必要な処理が動くとは限らないため、端末や利用方法を変える際は実際の業務を通して確かめます。出典：Microsoft公式のVBA対応範囲。

3. 表を直しても困るときは

入出庫の履歴から現在庫を計算でき、出荷や発注までに入力が済むなら、そのExcelを使い続けられます。

一方、表を直しても次のような作業が残るなら、現場での入力や、ほかのシステムとの連携を考える段階です。

• 倉庫から伝票が届くまで、出荷できる数が分からない
• 各拠点の在庫表を、毎日手作業で突き合わせている
• 受注・在庫・会計に、同じ内容を入力している

在庫管理を切り替える判断と進め方は、次の記事で説明しています。

Excelの在庫管理はいつシステム化する？判断基準と移行の進め方

入力待ちや転記が残るときの判断基準と、移行前に決めることを解説します。

在庫以外の仕事も見直したい方は、脱Excelの全体像をご覧ください。

コラム脱Excelの進め方｜Excel管理の限界と、やめどきの判断基準

Ouverへの相談も、この段階から始められます。「棚卸のたびに数を合わせている」「入力待ちで出荷の判断が遅れる」など、今困っている作業をお聞かせください。表の修正で済むのか、現場で入力する仕組みが必要なのかを、業務の流れから考えます。