会社情報
採用情報
お問い合わせ
Article

1日で完成！Excelで作る簡易在庫管理

高田晃太郎
1日で完成！Excelで作る簡易在庫管理

スプレッドシートの88%には何らかのエラーがあるとする研究は有名だが¹²、現場の意思決定でExcelが外れる気配はない。倉庫や店舗の在庫も例外ではなく、SaaSのWMS（Warehouse Management System：倉庫管理システム）を検討している最中でも、明日の入出庫を止めない最低限の可視化が求められる。そこに時間をかけられないチームにとって、Excelで組む簡易在庫管理は、過渡期を支える即効薬になり得る⁵。この記事では1日、具体的には6〜8時間を目安に稼働まで持っていく現実的な設計を、根拠や一般的な範囲を添えて示す。中長期の移行を前提に、壊れにくく、後から拡張しやすい最小構成を設計思想の中心に置く。

Excelで1日構築が現実的である理由と前提

短時間で完成させるには、目的機能を絞り込み、設計の自由度を意図的に制限する判断が重要だ。初日の完成条件を、SKU（Stock Keeping Unit：最小管理単位）マスターの確定、入出庫の記録、在庫残の自動計算、発注点によるアラート、日次集計の可視化の五つに限定すると、複雑なロット・有効期限・複数倉庫間移動は二日目以降の拡張対象になる。この割り切りにより、同日中の立ち上げと翌日からの運用が両立する。

対象環境はMicrosoft 365のExcelを前提にする。XLOOKUP（キー検索）、UNIQUE（重複排除）、LET/LAMBDA（式の部品化）が利用可能だと式が簡潔になり、処理量が増えても再計算が安定しやすい³。WindowsとMacの両方で動作するが、バーコードスキャナを使うならWindowsのほうが周辺機器の選択肢は広い傾向にある（ドライバや互換性に依存）。1日の構築でカバーできるデータ量の目安は、SKU数が3,000件以内、月間トランザクションが50,000行以内、同時編集は3人以内だ。これはSUMIFS（条件合計）中心の計算構成で、一般的なノートPCでも入力体感を快適に保ちやすいラインである⁷。実運用の体感速度はPC性能やネットワーク、関数の使い方で変動するが、この規模であれば再計算やフィルタ操作はおおむね実用範囲に収まりやすい。

時間配分は目安として、シート構成と命名規則の設計におよそ90分、マスターと入出庫ログの入力UI作成に120分、在庫計算とアラートの式実装に90分、ダッシュボード整備と検証に60分、残りの時間をバックアップ・権限・テンプレート化に充てる。実務データの取り込みが必要でも、Power Query（取り込み・変換機能）でCSVや既存台帳を取り込む構成にすれば、当日中に差分を埋めやすい⁴。

スコープ境界を明確にする

簡易在庫の初期版が担保するのは、単一拠点、単一通貨、単位変換なしの数量管理だ。棚番やロット、有効期限は項目として持っても、在庫残の集計粒度はSKUに固定する。これにより、式の条件分岐とキー設計が単純化し、計算と検証のリードタイムが縮む。倉庫横断やロット別の残量が必須な場合は、集計キーをSKU×倉庫、あるいはSKU×ロットに拡張する計画を別途用意し、初日はSKU単位で合格とする。

データモデルとシート設計：最小で壊れにくく拡張しやすく

構成は五つのシートで足りる。MasterにSKUの基本属性、Transactionsに入出庫明細、Stockに在庫残とアラート、Settingsに定数やドロップダウン源、Dashboardに集計とグラフを配置する。各シートはテーブル化して命名する。MasterはTblMaster、TransactionsはTblTxn、StockはTblStockといった規則で統一し、列名は日本語でも構わないが、一貫性を最優先する。IDは文字列として扱い、先頭のゼロ落ちを避ける。入力は原則としてTransactionsに集約し、StockやDashboardは参照専用にして直接編集を禁止する。

MasterにはSKU、品名、カテゴリ、標準原価、発注点、発注量、サプライヤを持たせる。SKUは重複を禁止し、XLOOKUPのキーにする。Transactionsは日時、SKU、方向、数量、担当、備考で構成し、方向はIN/OUT（入庫/出庫）の二値に限定する。数量は正の数で入力し、符号は方向で判定するほうが人的エラーに強い。StockはSKUを並べ、入庫数、出庫数、在庫残、発注点、アラート状態を表示する。Settingsはドロップダウンの候補やファイルパス、取込元の定義を収める。

式設計：SUMIFS中心の堅牢な在庫計算

在庫残は入庫合計と出庫合計の差で表現するのがもっとも読みやすい。TblStockの行に対して、入庫合計は次の式で得られる（SUMIFSは「条件に合う数値の合計」）。

=SUMIFS(TblTxn[数量], TblTxn[SKU], [@SKU], TblTxn[方向], "IN")

同様に出庫合計は、方向をOUTにした次の式になる。

=SUMIFS(TblTxn[数量], TblTxn[SKU], [@SKU], TblTxn[方向], "OUT")

在庫残は入庫から出庫を差し引く。

=[@[入庫合計]]-[@[出庫合計]]

発注点はマスターから引く。キーはSKUだ（XLOOKUPは「一致した行の値を返す」）。

=XLOOKUP([@SKU], TblMaster[SKU], TblMaster[発注点], "", 0)

在庫残が発注点を下回ったら警告を出す。表示と条件付き書式の両方に使えるよう、文字列でフラグを出す式にすると運用が楽だ。

=IF([@[在庫残]]<[@[発注点]], "要発注", "OK")

日付時点の在庫を求めたいときは、日時を条件に加える。LETで式中の変数を定義して読みやすさと再計算の無駄を抑えられる。

=LET(sku, A2, cutoff, B2, inQty, SUMIFS(TblTxn[数量], TblTxn[SKU], sku, TblTxn[方向], "IN", TblTxn[日時], "<="&cutoff), outQty, SUMIFS(TblTxn[数量], TblTxn[SKU], sku, TblTxn[方向], "OUT", TblTxn[日時], "<="&cutoff), inQty-outQty)

入力の堅牢化：検証、ドロップダウン、重複排除

人的エラーを減らすには、入力UIの摩擦を減らしながら自由度を狭める。TransactionsのSKUセルにはMasterのSKUを参照したドロップダウンを設定し、存在しないSKU入力をブロックする。方向はINとOUTの二択に固定し、数量は正の数値のみ受け付ける。SKUの一覧はUNIQUEで動的抽出して参照しておくと、マスター更新時の漏れを防げる。

=UNIQUE(TblMaster[SKU])

バーコードスキャンを使うなら、スキャナは多くの場合キーボードエミュレーションで動作するため、SKUセルを選んだ状態でトリガするだけで入力が完了する。改行の扱いに注意し、スキャン後に方向と数量にフォーカスが移るようにセル保護とタブ順を設計すると、入出庫のテンポが安定する。

実装プロセス：6〜8時間の現実的な進め方

開始直後の1時間半で、ファイルの雛形と命名規則を固める。シート名とテーブル名、列名の綴りを統一し、先々の拡張で迷子にならない土台を用意する。SKUの桁数や品名の文字数、カテゴリの深さなど、現物のデータを10件ほど試し入力して、カラム幅や表示形式の違和感を潰す。この段階で入力規則と条件付き書式のパターンをテンプレート化しておくと、その後の作業に迷いが出ない。

続く2時間ではMasterとTransactionsの入力UIを作り込む。MasterはCSVの既存台帳があればPower Queryで取り込み、列型の明示とトリム、全角半角の正規化まで一度に行う⁴。取り込みクエリを作っておくと、翌日以降も同じ手順で更新できる。Transactionsはテーブル最終行への追記を基本動作に設計し、既存行の編集は許さない。これは競合を減らすだけでなく、履歴の信頼性を保つ効果がある。担当者名は入力者のユーザー名を既定値にし、備考には発注IDや伝票番号を残して突合を容易にする。

在庫計算とアラートの実装には1時間半を割く。SUMIFSとXLOOKUPでStockを完成させ、在庫残の負値を検知したら背景を赤にする条件付き書式を設定する。ここまで来ると、全SKUの在庫健全度が一目で把握できる。敏感度を調整したい場合には、発注点を単純固定ではなく、過去30日出庫の平均×リードタイム日数といった算出式に置き換える。需要の季節性やリードタイムの揺れ、棚卸精度の影響を受けるため、現場の実態に合わせて期間や係数をチューニングするとよい。式の一例は次のとおりだ。

=ROUNDUP( AVERAGEIFS(TblTxn[数量], TblTxn[SKU], [@SKU], TblTxn[方向], "OUT", TblTxn[日時], ">="&TODAY()-30) * Settings!B2, 0)

最終の1時間でDashboardを仕上げる。SKUトップ20の出庫、欠品のリスクが高いアイテム、カテゴリ別の在庫金額、日次の入出庫推移をピボットテーブルとグラフで可視化する。経営層に共有するならOneDrive/SharePointに配置し、オンライン閲覧を既定にする。Excel Onlineでも主要な計算は動作するが、機能差や表示差があるため事前確認は必須だ。

Power Queryで初日からデータ取込を自動化

既存のCSVや他システムのエクスポートを取り込む場合は、Power Queryを使うと当日から繰り返し運用に乗せられる⁴。フォルダ内のCSVを結合してTransactionsに追加するだけなら、次のようなMクエリで十分だ。

let
  Source = Folder.Files("C:\\Import"),
  Filtered = Table.SelectRows(Source, each Text.Lower([Extension]) = ".csv"),
  WithContent = Table.AddColumn(Filtered, "Imported", each Csv.Document([Content], [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])),
  Expanded = Table.ExpandTableColumn(WithContent, "Imported", {"日時","SKU","方向","数量","担当","備考"}),
  Typed = Table.TransformColumnTypes(Expanded, {{"日時", type datetime}, {"SKU", type text}, {"方向", type text}, {"数量", Int64.Type}, {"担当", type text}, {"備考", type text}})
in
  Typed

取込後は関数で在庫残が自動で更新されるため、現場の手作業は最小化される。単位の不一致や日付フォーマットの揺れは取り込み側で正規化しておき、Excel側の式はロジックに専念させると保守が楽になる。

VBAで入力フォームを最短実装、エラーを握り潰さない

入力速度をさらに上げたいときは、既存シートをそのまま使うVBA（Excelのマクロ言語）の簡易フォームが即効性を発揮する。行追加のみ許可し、検証を通らない入力はメッセージで返す。例として最低限の追加マクロを示す。

Option Explicit
Sub AddTxn()
  On Error GoTo EH
  Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Transactions")
  Dim nextRow As Long: nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
  Dim sku As String: sku = Trim(ThisWorkbook.Worksheets("入出庫").Range("B2").Value)
  Dim dir As String: dir = UCase$(Trim(ThisWorkbook.Worksheets("入出庫").Range("B3").Value))
  Dim qty As Long: qty = CLng(ThisWorkbook.Worksheets("入出庫").Range("B4").Value)
  If sku = "" Or qty <= 0 Or (dir <> "IN" And dir <> "OUT") Then
    MsgBox "入力が不正です", vbExclamation: Exit Sub
  End If
  ws.Cells(nextRow, 1).Value = Now
  ws.Cells(nextRow, 2).Value = sku
  ws.Cells(nextRow, 3).Value = dir
  ws.Cells(nextRow, 4).Value = qty
  ws.Cells(nextRow, 5).Value = Environ$("Username")
  ws.Cells(nextRow, 6).Value = ThisWorkbook.Worksheets("入出庫").Range("B5").Value
  Exit Sub
EH:
  MsgBox "登録に失敗しました: " & Err.Description, vbCritical
End Sub

この程度の補助で、テーブル末尾への追記運用が徹底でき、既存行の改変を抑止できる。エラーは握り潰さずユーザーに返し、失敗した操作の痕跡がデータに混入しないようにするのがコツだ。

運用・スケール・ROI：どこまでExcelで戦うか

在庫差異の削減、欠品・過剰の是正、入出庫の時短。この三つの効果を試算しておくと、Excel継続かWMS移行かの判断がしやすい。たとえば、月間出庫が10,000件の現場で、差異調整の発生率が3%から1%に下がると、再確認や返品処理の時間がおおむね200件分減る。1件5分で見積もれば、約1,000分、すなわち17時間の削減だ。入出庫の記録にかかる時間が1件あたり20秒短縮できれば、合計で約55時間の削減になる。人件費を3,000円/時とすると、月あたり約21万円の削減効果が見込める計算になる。Excelの追加コストは小さいため、初期の投資回収が運用開始初月からプラスになるケースもある⁶。実際の効果は、需要の季節性、品目構成、リードタイムのばらつき、棚卸精度、入力ルールの徹底度合いで大きく変動する点には留意したい。

パフォーマンス面では、列指向でのテーブル参照とSUMIFS中心の設計により、10万行規模でも実用域を保ちやすい³。重くなってきたら、Transactionsを年別ファイルに分割し、在庫残に必要な期間だけをPower Queryで参照する構成に切り替える。懸念されがちな同時編集は、OneDrive/SharePoint上の共有ブックで緩和できるが、それでも入力競合はゼロにはならない。競合時は最後に保存した人が勝つ仕様を理解し、同じSKUに対する同時操作を避ける訓練が必要だ。クリティカルなカラムはWorksheet_Changeイベントで保護し、システム側で整合性を守る。

ガバナンスでは、週次のバックアップと、入出庫ログの改変禁止が重要になる。バージョン履歴を有効にし、Transactionsは追記専用、修正は取消伝票として逆仕訳で記録する。こうすると、監査時に履歴を辿る負担が減る。アクセス権は入力担当と閲覧者を分け、StockとDashboardは編集不可にしておく。棚卸の際は、カウント結果を別テーブルに収集し、差異を算出して是正伝票を自動起票する関数を用意する。差異起票の一例は次の式で実現できる。

=LET(sku,[@SKU], counted,[@[棚卸数]], book, [@[在庫残]], IF(counted=book, "", IF(counted>book, "IN", "OUT") & ":" & ABS(counted-book)))

移行の目安も決めておくと良い。たとえば、同時入力者が5名を超える、日次のトランザクションが5,000件を超える、ロットと有効期限のトレーサビリティが監査要件であるといった条件が揃えば、SQLとBI、あるいはWMSへの段階的移行を開始する。Excel版はそのままデータ入力のフロントとして当面使い続け、裏でデータレイヤを置き換えると移行ショックが小さく済む。

よくある落とし穴と回避策

もっとも多い失敗は、良かれと思ってStockやDashboardに直接入力してしまい、式を壊すケースだ。参照専用シートは保護し、保護のパスワードと解除基準をドキュメントに残す。次に、SKUの正規化を怠って桁や書式が混在し、XLOOKUPがヒットしない問題が発生しやすい。取り込み時にテキスト化し、前後の空白・全半角・大小文字をPower Queryで正規化する。さらに、単位の混在が見逃されがちだ。入数や重量換算が必要なSKUはMasterに変換係数を持たせ、Transactionsでは必ず基準単位に換算して記録する。最後に、初期に棚番やロットまで完璧に入れようとして時間切れになることも多い。本当に必要な粒度を今日の運用で見極め、明確な拡張計画を持って初日はSKU単位の可視化に集中する。

内部・外部の参考と拡張の導線

関数の詳細は、XLOOKUPとUNIQUE、LET/LAMBDAの公式ドキュメントを参照すると設計の自由度が上がる³。Power Queryのフォルダ結合は、Microsoft Learnの解説が実践的で役に立つ⁴。さらに踏み込みたい読者には、在庫回転率（在庫の売れ行きの速さ）や安全在庫（需要や供給の揺れに備える保険在庫）の算定法を整理した社内ナレッジの整備を勧めたい。社内ポータルにExcelテンプレートと操作動画、よくある質問をまとめ、更新履歴を残すだけでも、属人化のスピードが大幅に下がる。関連記事として、関数の深掘りやデータクレンジングの手順、在庫KPI（重要指標）設計の解説といった読み物を揃えておくと、チームの学習コストは確実に下がる⁵。仮にWMSに移行する場合も、このExcel版は要件定義の叩き台として機能し、フィールドの定義や運用フローの議論を具体化してくれる。

まとめ：明日を止めないための1日投資

完璧な在庫システムを待って現場を止めるより、いま使える仕組みで差異と欠品を減らすほうが現実的だ。Excelであれば1日、6〜8時間の集中で、SKUマスター、入出庫ログ、在庫残とアラート、日次の見える化まで形にできる。今月の削減効果が人件費換算で20万円前後になるケースも珍しくない。今日の要件で最小構成を作り、明日の要件に合わせて拡張する。この小さな前進が、将来のWMS移行やデータ基盤の整備にも直結する。あなたの現場では、どのSKUから在庫の見える化を始めるだろうか。ファイルを一つ用意し、最初の10件を入力するだけで、チームの次の一歩が見えてくるはずだ。

参考文献

  1. PlanGuru. 88% Of Spreadsheets Have Errors — Here’s How To Avoid Them. https://www.planguru.com/blog/88-spreadsheets-errors/
  2. Phys.org. Business spreadsheets often have critical errors, study finds (2024-08). https://phys.org/news/2024-08-business-spreadsheets-critical-errors.html
  3. Microsoft Learn. Excel performance: Tips for optimizing performance by using formula calculation. https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance
  4. Microsoft Learn. Combine files (Power Query). https://learn.microsoft.com/en-us/power-query/combine-files-csv
  5. Intuit QuickBooks. 7 tips for basic inventory management using Excel. https://quickbooks.intuit.com/r/inventory/7-tips-for-basic-inventory-management-using-excel/
  6. 朝日新聞社SMBIZ. エクセルで在庫管理を行う利点. https://smbiz.asahi.com/article/14362409
  7. Microsoft Learn（日本語）. Excel の計算パフォーマンスの改善. https://learn.microsoft.com/ja-jp/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance