エクセル在庫管理の数式エラー解決とData Validationによるデータロック

Excel在庫管理ファイルの数式エラーは、関数そのものより入力されたデータに原因があることがほとんどです。SKUコードの打ち間違いや余分な空白があるとSUMIFSは0を返し、検索関数は#N/Aを表示し、数値が文字列として書式設定されていると#VALUE!が出ます。Data ValidationでSKU列をロックし、Protect Sheetで数式セルを保護すれば、こうしたエラーの大半を元から防げます。

この記事では、データをロックする方法、各エラーコードを正しく読む方法、そして実際に運用し始めてから必要になるコツ（シートの保護、バーコードスキャナーの接続、複数倉庫の管理、新しい会計年度へのデータ繰り越し）を詳しく解説します。まだファイルを作っていない場合は、先に4つのシートでExcel在庫管理ファイルを作る方法をご覧ください。

1. ファイル構成のおさらい

4シート構成どおりにファイルを作っていれば、以降の数式で参照する列は下の表と一致します。列名を変えている場合は、数式のセル番地もそれに合わせて直してください。

データは一方向に流れます。CatalogシートがInboundシートとOutboundシートにドロップダウンリストを渡し、その2つのシートがSUMIFSを通じてSummaryシートへ数値を送ります。保管場所が2か所以上ある場合は、Storage Location列を埋める前に倉庫を効率よく整理する原則に沿って場所コードの付け方を統一しておきましょう。

2. Data Validationでデータをロックする

SKUの打ち間違いは、集計表に深刻なずれが生じる最大の原因です。空白が1つ多い、あるいは取引記録でその場で新しいコードを作っただけで、SUMIFSはその行を何の警告もなく飛ばしてしまいます。Data Validationを使えば、誤字や独自に作ったコードが入り込む余地をなくし、入力のたびにマスタへ登録済みのコードを選ばせることができます。SKUコードの設計とマスタの標準化については商品マスタを作るステップで解説しています。

SKUドロップダウンリストの設定

1. InboundとOutboundシートのSKU Code列全体を選択します。
2. ツールバーのDataタブ ＞ Data Validationを選びます。
3. AllowでListを選びます。
4. Sourceに参照用の数式を入力します：=Catalog!$A$2:$A$100（マスタをすでにExcelテーブルにしている場合は=INDIRECT("Table_Catalog[SKU Code]")）。
5. Error Alertタブに切り替えてStopスタイルを選び、メッセージを入力します：「マスタに存在しないSKUです。もう一度ご確認ください！」。OKを押して完了です。

マスタ側で重複コードを防ぐ

取引シートのドロップダウンをロックするだけでは、問題の半分しか解決しません。マスタ自体に同じSKUの行が2つあると、その先の検索数式がすべて、原因の分からないまま狂ってしまうからです。マスタシートでSKU Code列を選び、Dataタブ ＞ Data Validation ＞ AllowでCustomを選び、数式=COUNTIF($A:$A, A1)<=1を入力してください。すでに入力済みのコードを再び入れた瞬間にExcelが止めてくれます。

3. 自動化の数式とエラーへの対処

入出庫ファイルを自動で動かす中心的な関数は3つです。SUMIFS（条件に合う数値の合計）、XLOOKUP（SKUから商品名を取得）、そしてIFと条件付き書式の組み合わせ（在庫不足の警告）です。この3つ以上に大切なのが、数式が想定外の結果を返したときにエラーコードを正しく読むことです。多くの人がまさにこの場面であきらめ、手書きの記録に戻ってしまうからです。

基本となる3つの数式

• 期末在庫 — SummaryシートのG2セル：=D2+E2-F2（期首在庫 ＋ 入庫合計 − 出庫合計）。
• 入庫合計 — E2セル：=SUMIFS(Inbound!$F:$F, Inbound!$C:$C, A2)で、InboundシートのC列のSKUがA2セルと一致する行について、F列の数量をすべて合計します。
• 出庫合計 — F2セル：=SUMIFS(Outbound!$F:$F, Outbound!$C:$C, A2)。同じ考え方でOutboundシートから取得します。
• 商品名の検索 — InboundシートのD2セル：=XLOOKUP(C2, Catalog!$A:$A, Catalog!$B:$B, "名称未設定", 0)。XLOOKUPがないExcel 2019/2016では=IFERROR(VLOOKUP(C2, Catalog!$A:$E, 2, FALSE), "名称未設定")を使います。
• 状態の警告 — I2セル：=IF(G2<=0, "在庫なし", IF(G2<=H2, "発注が必要", "安全"))。そのあと条件付き書式 ＞ セルの強調表示ルール ＞ 指定の文字列を含む、で状態ごとに色を付けます。

この3つ以外の関数も必要であれば、エクセル在庫管理の記事に一覧表があります。

よく出る3つのエラーコードを読む

• #N/Aエラー：検索関数（XLOOKUP、VLOOKUP）がマスタで該当するSKUを見つけられない状態です。数式をIFERROR(数式, "代替値")で囲むか、そのコードがマスタに登録されているかを確認してください。
• #VALUE!エラー：計算式が空白や文字の入ったセルを足し引きしようとしています。入出庫シートの数量セルがすべて文字列（Text）ではなく数値（Number）書式になっているか確認してください。
• #REF!エラー：行・列・シートが削除され、数式が参照先を失った状態です。構造となる列は直接削除せず、誤って消した場合はすぐにCtrl + Zで元に戻してください。
• 入庫があったのにSUMIFSが0を返すとき：最も多い原因は、2つのシートでデータの書式が揃っていないか、どちらかのSKUの末尾に空白が付いていることです。照合する前に=TRIM()で文字列を整えましょう。

4. ずれの管理とファイルの保護

スプレッドシートのリスクについて広く引用される調査によると、中規模以上のスプレッドシートの多くには人為的なミスが含まれています。必要な防御線は2つです。数値を定期的に突き合わせること、そして担当者が誤って数式を壊さないようファイルをロックすることです。

在庫数値の3者照合

• 帳簿在庫（Book Inventory）：入出庫の証憑をもとにExcelファイル上で計算された数量です。
• 実地在庫（Physical Inventory）：倉庫の棚で直接数えた数量です。
• 引当可能在庫（Available Inventory）：実地在庫から、すでに注文が入って梱包待ちの数量を差し引いた値です。

高額な商品群には週次または月次で循環棚卸し（Cycle Counting）を行い、年末の一斉棚卸しを待たずに、差異が出た時点ですぐ見つけられるようにしましょう。

数式を守るためにシートをロックする

自動計算の数式が入ったセルに担当者が誤って数字を上書きすることが、計算列全体が壊れるよくある原因です。ロックの手順：

1. シート全体を選択してCtrl + 1を押し、ProtectionタブでLockedのチェックを外します。
2. 数式の入った列だけを選び（集計シートのD・E・F・G・I列）、Ctrl + 1を押してProtectionタブでLockedにチェックを入れます。
3. Reviewタブ ＞ Protect Sheetを選び、パスワードを設定して、ロックされていないセルにだけ入力できるようにします。

さらにOneDriveやSharePointを組み合わせれば、リアルタイムの共同編集とバージョン履歴の自動保存が使え、問題が起きたときの復旧が早くなります。複数人が社内ネットワーク経由でファイルを保存し、互いに上書きしてしまう状況で特に役立ちます。

5. 運用の応用テクニック

以下の作業は、数か月ほど実際に運用し、利用者が増えて取引量が伸びてきたころに必要になります。

• 日付を正しく入力する：伝票を作るときはCtrl + ;のショートカットで今日の日付を固定値として入れます。記録に=TODAY()を直接使うと、ファイルを開き直すたびに日付が翌日に変わり、過去の伝票までずれてしまいます。
• 1つのファイルで複数倉庫を管理する：マスタ・入庫・出庫の各シートに「倉庫コード」列を追加し、SUMIFSで2つの条件（SKU Codeと倉庫コード）を同時に指定して拠点ごとに集計します。
• 賞味期限を管理する：入庫シートに「賞味期限」列を追加し、DATEDIFと条件付き書式を組み合わせて、残り30日を切ったロットや期限切れのロットを自動で色分けします。
• 出庫伝票を自動で印刷する：標準的な出庫伝票の体裁で「伝票印刷」シートを1枚用意し、XLOOKUPやFILTERで特定の伝票番号の品目だけを印刷範囲に呼び出します。
• 新しい会計年度へ繰り越す：年度末にファイルのコピーを新しく作り、前年度の「Closing Stock」列を新年度の「Opening Stock」列に値として貼り付け（Paste Special as Values）、その後2つの入出庫シートから古い取引行を消します。
• バーコードスキャナーを接続する：USBやBluetoothの一般的なバーコードリーダーはキーボードと同じように動くため、包装のコードを読み取ると選択中のセルにSKUが入力され、Enterまで自動で押されます。専用ソフトは要りません。Excelのセルに書き込むのではなく、スキャンで在庫をリアルタイムに引き落としたいなら、そのときが倉庫管理システム（WMS）の出番です。
• 複数人で入力できるよう共有する：ファイルをOneDriveかSharePointにアップロードしAutoSaveを有効にすれば、権限を持つメンバーがExcel for Webで同時に入力しても互いに上書きしません。
• Googleスプレッドシートで使う：SUMIFS、IF、IFERROR、Data ValidationはGoogleスプレッドシートでも同じように動作し、プラットフォームを移しても不具合は起きません。

6. 限界はソフトか、それとも場所か

ファイルが限界に達したことを示す具体的なサイン（SKU数、1日の注文数、ロットや賞味期限の管理が必要になること）と、ExcelとWMSソフトの詳しい比較表はエクセル在庫管理の記事で扱っています。ここでは別の状況、つまり問題が道具ではなく荷物を置く物理的なスペースにある場合を取り上げます。

実際には、倉庫管理のボトルネックはスプレッドシートではなく、保管スペースが足りなくなることから生じる場合が多くあります。オフィスや自宅が荷物で埋まると、Excel上では在庫があると表示されていても、担当者が商品を見つけられません。

年末の大型セールのように季節で在庫が大きく変動する場合や、3〜5年の伝統的な倉庫賃貸契約にまだ踏み切れない場合は、柔軟な緩衝スペースを見つけることが負担を減らす最も効果的な方法です。

外に出すべき在庫が何立方メートルになるか分からないときは、荷物の写真を撮ってMyStorageのAIサイズ診断で確認してみてください：

ホーチミン市のネットショップ運営者や中小企業に向けて、MyStorageはセルフストレージと温度管理付きのミニ倉庫を提供しています。個人向け収納ユニットは1m³から10m³以上まで月単位で借りられ、従来の倉庫のような長期契約に縛られず、事業の波に合わせて広げたり狭めたりできます。

規模別に見る在庫管理ツールの費用

管理ツールにかかる費用は、商品点数と管理する倉庫の数に応じて上がっていきます：

上記のソフト費用は一般的な市場の目安です。足りないのがソフトではなく物理的なスペースなら、MyStorageの個人向け倉庫料金表をご覧いただくか、ご希望のサイズの正確な料金を確認してください。

保管タイプ・サイズ・温度を選ぶと、本日のMyStorage料金をすぐに確認でき、特典付きで予約できます：

まとめ

しっかりしたExcel在庫管理ファイルは、4つのシートを正しく構成して終わりではありません。さらに3つの保護が必要です。入力の段階で誤ったコードを防ぐData Validation、あきらめずに自分で直せるようエラーコードを正しく理解すること、そして共有する人同士がデータを壊さないためのシートのロックとクラウド保存です。この3つがそろえば、事業が本当に大きくなるまでExcelファイルで十分にやっていけます。

壁になっているのが道具ではなく荷物を置く場所なら、スプレッドシートにMyStorageのセルフストレージを組み合わせるか、MyStorageへお問い合わせいただき、適したユニットサイズをご相談ください。

よくある質問