実際の在庫と数が合わなくなる」と悩んでいませんか。

エクセルは、多くの企業ですでに導入されており、追加コストをかけずに在庫管理を始められる便利なツールです。一方で、作り方や運用方法を誤ると、入力漏れや集計ミスが発生し、「在庫が合わない」「どれが最新版かわからない」といったトラブルにつながることもあります。

本記事では、Excel業務改善の専門家であるセルネッツが、在庫管理表をエクセルで作る方法を、基本項目やレイアウト例、SUMIFS・VLOOKUP・IFなどの関数を使った自動化の方法とあわせてわかりやすく解説します。

また、「在庫が合わない」を防ぐための運用のコツ、エクセル管理の限界とツール化を検討すべきタイミングについても紹介しますので、ぜひ参考にしてください。

EXCEL開発・VBA開発・EXCELマクロ開発ならセルネッツ

エクセルで在庫管理を行うメリットと向き・不向き

エクセルの在庫管理表は、関数を活用して入出庫数や現在庫、発注タイミングなどを自動で管理できる仕組みです。

エクセルで在庫管理を行う主なメリットは、以下の3つです。

• 追加コストがかからない:多くの企業ですでにExcelが導入されており、専用システムのような初期費用や月額費用が不要
• 現場に浸透しやすい:使い慣れたツールのため、新しいシステムを覚える負担が少ない
• 柔軟にカスタマイズできる:項目や計算式を自社の運用に合わせて自由に設計できる

一方で、商品数や入出庫件数が多い場合や、複数拠点でのリアルタイム共有、多人数での同時編集、他システムとの連携が必要な場合は、エクセルだけでの運用が難しくなることもあります。

そのため、商品数や利用人数が比較的少ない小〜中規模の在庫管理であれば、まずはエクセルから始めるのがおすすめです。

ここからは、実際に在庫管理表を作る方法を見ていきましょう。

在庫管理表をエクセルで作る方法6ステップ

ここからは、実際にエクセルで自動計算できる在庫管理表を作る方法を紹介します

エクセルに詳しくない方でも、手順どおりに進めれば設定できる内容です。コピー&ペーストですぐに使える関数も紹介しますので、ぜひ読み進めながら作成に挑戦してみてください。

STEP1:シートの型を決める

まずは、在庫管理表の土台となるシート構成を決めます。

在庫管理表は、大きく「単票タイプ」(1商品につき1枚の管理表)と「在庫移動表タイプ」(複数商品を一覧管理)の2つに分かれます。

タイプが向いています。

なお、多くの企業では在庫移動表タイプが実務の主役になるため、本記事もこの型を前提に解説します。

STEP2:在庫管理表の項目を決める

次に、在庫管理表の項目を決めます。最低限あると便利な項目は、次のとおりです。

• 商品番号(品番):社内の管理コード
• 商品名:商品の名称
• 入庫数・出庫数:日々の動き
• 在庫数:現在庫
• 発注点:発注が必要になる在庫の下限
• 日付・担当者・備考

特に重要なのが、商品番号を軸に管理することです。商品名だけで管理すると、全角・半角や表記ゆれ(例:「ネジ M4」と「M4ネジ」)が別商品として扱われ、関数で正しく集計できなくなります。

実際の項目を反映させると、以下のような表が作成できます。

【在庫管理表】

ここで作成した在庫管理表の2行目以降に関数を反映し、自動で在庫を集計できるようにします。 

STEP3:商品マスタ・入出庫履歴シートを作成する

次に、STEP2で作った在庫管理表に関数で自動反映させるための、元データとなる2つのシートを用意します。

1つは、商品番号ごとの商品名・単価を登録しておく「商品マスタ」シートです。もう1つは、日々の入庫・出庫を1件ずつ記録していく「入出庫履歴」シートです。

それぞれのシートに溜まったデータをもとに、在庫管理表の入庫合計・出庫合計を自動集計します。

なお、この2つのシートは在庫管理表とは別に用意しておくことで、在庫管理表側は「見る・確認する」ためのシート、商品マスタ・入出庫履歴は「入力する」ためのシートと役割を分けられ、関数も組みやすくなります。

ここでは、例として以下のような形で商品マスタシート・入出庫履歴シートを作成しました。

【商品マスタシート】

【入出庫履歴シート】

STEP4:入出庫を自動集計する数式を入れる

001の入庫合計)に、次の数式を入力してください。

=SUMIFS(入出庫履歴!D:D, 入出庫履歴!B:B, A2, 入出庫履歴!C:C, “入庫”)

この数式は、「入出庫履歴シートのB列(商品番号)が、在庫管理表シートのA2セル(S001)と一致し、かつ入出庫履歴シートのC列(区分)が「入庫」のD列(数量)を合計する」という意味です。設定後はC2セルをコピーして下の行に貼り付ければ、3行目以降にも同じ数式をまとめて反映できます。

同じ要領で、出庫合計(D2セル)には条件を「出庫」に変えた数式を入力しましょう。

=SUMIFS(入出庫履歴!D:D, 入出庫履歴!B:B, A2, 入出庫履歴!C:C, “出庫”)

最後に、在庫数(E2セル)に入庫合計から出庫合計を引く数式を入力すれば完成です。

=C2-D2

STEP5:商品名を自動表示する

次に、VLOOKUP関数を使って商品名を自動表示させましょう。在庫管理表シートのB2セル(商品名)に、次の数式を入力します。

=VLOOKUP(A2, 商品マスタ!A:C, 2, FALSE)

この数式は、「在庫管理表シートのA2セル(商品番号S001)を商品マスタシートのA列から検索し、2列目(B列=商品名)を表示する」という意味です。単価を表示したい場合は、列番号を3列目(C列)に変えるだけです。

=VLOOKUP(A2, 商品マスタ!A:C, 3, FALSE)

なお、存在しない商品番号を入れたときにエラーを表示させないよう、IFERROR関数と組み合わせるのがおすすめです。

=IFERROR(VLOOKUP(A2, 商品マスタ!A:C, 2, FALSE), “商品番号を確認してください”)

これにより、担当者は商品番号と数量を入力するだけで、商品名や在庫数が自動で表示されるようになります。

なお、最新のExcel環境であればVLOOKUP関数の代わりにXLOOKUP関数を使うことも可能です。

STEP6:発注点を下回ったら知らせるアラートを加える

最後に、IF関数を使って発注アラートを設定します。在庫管理表シートのG2セル(要発注)に、次の数式を入力します。

=IF(E2<=F2, “要発注”, “”)

この数式は、「E2セル(在庫数)がF2セル(発注点)以下なら『要発注』と表示し、そうでなければ何も表示しない」という意味です。

上の例でS001の在庫数(E2)が10以下になれば、G2セルに「要発注」と表示されます。条件付き書式と組み合わせて該当セルを色付けすれば、発注が必要な商品を一目で把握できます。

補足として、近年はシート名・列名・セル位置を具体的に伝えれば、生成AIに関数の候補を作らせることもできます。ただし、AIの出力はそのまま使わず、必ず自社の表に合わせて動作確認してください。

在庫管理表の自動計算で使うエクセル関数まとめ

ここまで紹介した関数を、用途ごとにまとめました。どの関数を使えばよいか迷ったときは、以下の一覧を参考にしてください。

関数	用途	数式のイメージ
SUMIF / SUMIFS	商品ごとの入庫・出庫を自動集計する	=SUMIF(商品番号の範囲, 対象の商品番号, 数量の範囲)
VLOOKUP	商品番号から商品名・単価を自動表示する	=VLOOKUP(商品番号, 商品マスタ範囲, 取得列, FALSE)
IF	在庫数が発注点を下回ったら「要発注」と表示する	=IF(在庫数<=発注点, “要発注”, “”)

初めて在庫管理表を作る場合は、まずはSUMIFで入出庫の集計だけでも十分効果があります。

その後、入力作業を効率化したい場合はVLOOKUP、発注漏れを防ぎたい場合はIFを追加すると、より実用的な表にできるでしょう。

関数だけでは解決できないこと

ここまでの関数を使えば、在庫数の自動計算や入力ミス防止はかなり進みます。

ただし、関数だけでは在庫差異は解決しません。入力漏れ、ファイルの上書き、担当者ごとのルールの違いといった「運用」の問題が残っているからです。

関数を正しく組んでも、そもそも入力されていなければ在庫は合いません。以下では、エクセル在庫管理における運用面の対策を解説します。

無料相談はこちらから

無料お見積もりシミュレーターを試す

エクセル在庫管理表で在庫数が合わない主な原因と対処法

エクセル在庫管理で最も多い悩みが、「実際の在庫数と表の数字が合わない」という問題です。

しかし、その原因の多くは、関数ではなく運用にあります。以下では、エクセル在庫管理表で在庫数が合わない主な原因と対処法について、詳しく見ていきましょう。

入力漏れ・二重入力

入出庫が発生したのに記録していなかったり、同じ入庫を二重で入力したりすると、在庫数は合わなくなります。

対処法として、「誰が」「いつ」「どのタイミングで」入力するかを明確に決めましょう。

たとえば、「入荷検品が終わったら担当者が当日中に入力する」「出荷処理後に出庫担当者が入力する」といったルールを決めておくことが重要です。

商品番号の表記ゆれ

商品名や商品番号の入力方法が人によって異なると、同じ商品でも別商品として集計される可能性があります。

商品マスタを作り、商品番号はプルダウンで選択する運用にすると、表記ゆれを防げます。

数式セルへの直接入力

現在庫や入庫合計、出庫合計のセルに直接数字を入力すると、関数が消えて自動計算できなくなります。

入力欄と計算欄を分け、関数セルにはシート保護をかけるのがおすすめです。

ファイルの上書き・最新版不明

複数人がそれぞれのPCにファイルを保存していると、どれが最新版かわからなくなります。

共有フォルダやクラウド上の1ファイルに集約し、各自のPCにコピーを散らさない運用にしましょう。ファイル名の命名規則やバックアップの頻度も決めておくと安心です。

担当者ごとの入力ルール違い

ある担当者は「個」で入力し、別の担当者は「箱」で入力していると、数量が合わなくなります。

単位、入力タイミング、備考の書き方などをルール化し、関係者全員で共有しましょう。

棚卸をしていない

エクセル上の在庫数は、あくまで理論在庫です。

入力ミスや記録漏れがゼロでない以上、定期的に実在庫と照合する棚卸が必要です。月次や四半期など、自社に合った頻度で棚卸を行い、ズレを修正する習慣を作りましょう。

関数や参照範囲のズレ

行を追加したときに関数の参照範囲から漏れていたり、コピーした数式の参照先がずれていたりすると、正しく集計できません。

表をテーブル化する、定期的に集計結果を確認するなどして、関数のズレを防ぎましょう。

在庫管理表の入力ミスを減らすエクセルの設定

在庫管理表の精度を高めるには、運用ルールを決めるだけでなく、入力ミスが起こりにくい仕組みをエクセル上に作ることが大切です。ここでは、プルダウンや入力規則、シート保護など、在庫管理表のミス防止に役立つ設定を紹介します。 

プルダウンで商品番号を選ばせる

商品番号を手入力にすると、入力ミスや表記ゆれが起こります。

たとえば「S001」と入力すべきところを「S01」と入れてしまうと、関数で正しく集計できません。

このようなミスを防ぐには、入力規則を使って商品番号をプルダウンで選べるようにします。

入出庫履歴シートのB列に商品番号を入力する場合、商品マスタのA列に登録されている商品番号から選択できるようにしておくと、存在しない商品番号の入力を防げます。

入力欄と計算欄を分ける

在庫管理表では、入力するセルと関数が入っているセルを明確に分けることが重要です。

たとえば、入出庫履歴シートは手入力用、在庫一覧シートは自動計算用として分けておくと、関数を誤って上書きするリスクを減らせます。

計算欄にはシート保護をかけておくと、関数の破損を防ぎやすくなります。

日付や数量の入力形式を制限する

日付欄には日付だけ、数量欄には数値だけを入力できるように設定すると、入力ミスを防げます。

たとえば、数量欄に文字列やマイナス値が入らないよう制限しておくと、集計結果のズレを減らせます。

エクセル在庫管理のデメリットとよくある課題

ここまで、エクセルで在庫管理表を自動計算する方法を紹介してきました。

しかし、自作の在庫管理表は便利な反面、運用を続けるなかでさまざまな課題が生じることがあります。特に、複数人で利用する場合や管理する在庫が増えてきた場合は、入力ミスや運用トラブルが発生しやすくなるため注意が必要です。

ここでは、自作のエクセル在庫管理表で起こりやすい代表的な課題と、その対策を紹介します。

大量データ・同時編集に弱い

商品数や履歴が膨大になると、ファイルが重くなり、開く・保存するたびに時間がかかります。最悪の場合、ファイルが破損することもあります。また、デスクトップ版Excelは原則、複数人が同時に開いて編集・保存できないため、多人数での運用や多拠点でのリアルタイム共有には向きません。

「ファイルが重くなった」「集計作業に時間がかかる」「複数拠点の在庫をまとめて管理できない」といった状況が増えてきた場合は、エクセルだけで運用するには限界を迎えているサインといえるでしょう。

作成者しか扱えない「属人化」が起こる

エクセル内の自動計算を充実させようとすると、関数やマクロが増え、ファイルの仕組みが複雑になりがちです。その結果、「作成した担当者しか修正方法がわからない」という属人化が起こるケースは少なくありません。

担当者が異動・退職すると、エラーが発生しても原因を特定できず、表を作り直さなければならないこともあります。

こうした事態を防ぐには、誰が見ても理解しやすいファイル構成を意識することが大切です。数式の意味をコメントとして残す、入力欄と商品マスタを分ける、シート名や項目名のルールを統一するなど、引き継ぎしやすい運用を心がけましょう。

また、マクロ(VBA)で自動化している場合は特に注意が必要です。作成者が退職すると誰も保守できなくなるリスクがあるため、既存マクロの解析・改修・保守については、マクロ保守サポートのような外部サービスの活用も選択肢になります。

▶ 改善事例を動画で見る:【総務】前任者が退職…誰も触れないAccess。Excelでも使える?(YouTube)

在庫管理表をエクセルで作るか、ツール化するかの判断軸

在庫管理業務を効率化したい場合、「エクセルのままで十分なのか」「自動化ツールやシステムを導入すべきなのか」と悩む方も多いでしょう。

重要なのは、現在の業務規模や課題に合った方法を選ぶことです。必ずしも高額なシステムを導入する必要はなく、エクセルの改善だけで十分に効率化できるケースも少なくありません。

エクセルで対応するか、ツール化するかの一般的な判断の目安は、次のとおりです。

現在の状況	おすすめの対応
品目数が少なく、担当者が運用・メンテナンスできる	エクセルでの運用を継続
関数やマクロが複雑で、修正できる人が限られている	エクセルファイルの改修・保守を検討
入力や集計を手作業で行っており、工数が多い	VBAなどを活用した自動化を検討
品目数や拠点数が多く、エクセルでは管理しきれない	在庫管理システムなどの導入を検討

業務改善では、「システムを導入すること」が目的ではありません。入力作業や集計作業を減らし、業務を効率化することが本来の目的です。

そのため、既存のエクセルを活用して課題を解決できるのであれば、無理に新しいシステムを導入する必要はないでしょう。

在庫管理の効率化は「システム導入」だけが正解ではない 

在庫管理を効率化したいと考えたとき、「そろそろ在庫管理システムを導入すべきでは?」と考える企業も少なくありません。

しかし、必ずしも高額なシステムやツールを導入する必要はなく、実際はエクセルで工夫するだけで問題が解決するケースも多くあります。

実際に、セルネッツにご依頼いただいた事例のなかには「他社では100万円以上のシステム提案を受けたものの、エクセルの改修だけで課題を解決できた」というケースは少なくありません。

▶改善事例を動画で見る:【総務】その業務、本当にシステム化が必要ですか?|顧客管理・予約管理・見積管理もExcelで実現(YouTube)

私たちセルネッツは、Excel専門特化で約20年、200件を超える開発実績があり、見積もりの8割以上が100万円(税別)以下に収まっています。

これは、既存のExcel資産を活かしながら、必要な部分だけを改善・自動化することで、開発コストを抑えているためです。また、納品物のソースは完全公開(著作権譲渡)しているため、納品後はお客様自身で改修や運用を行っていただけます。

業務効率化で重要なのは、「システムを導入すること」ではなく、「業務課題を解決すること」です。

当社では、現在の業務規模や課題に合わせて、エクセルの改善で十分対応できるのか、それともツール化やシステム化を検討すべき段階なのかを含めて、費用対効果の高い方法をご提案させていただきます。ぜひお気軽にご相談ください。

無料相談はこちらから

無料お見積もりシミュレーターを試す

まとめ|在庫管理エクセルの自動計算化でお困りならセルネッツへご相談を

エクセルの在庫管理表は、あらかじめ関数を設定しておくことで、商品番号と数量を入力するだけで在庫数や発注要否を自動計算できます。基本となる「SUMIFS」による集計に加え、VLOOKUPやIFなどの関数を活用すれば、入力や集計の手間を大幅に削減できるでしょう。

一方で、自作の在庫管理表は、数式の上書きや属人化、品目数・拠点数の増加による運用負荷など、長く使うほど課題が生じやすくなります。シートの保護やファイル構成の見直しなど、壊れにくく・引き継ぎやすい運用を意識することも欠かせません。

「今使っている在庫管理表をもっと効率化したい」「前任者が作成したエクセルの仕組みが複雑で修正できない」といったお悩みがあれば、セルネッツへお気軽にご相談ください。

セルネッツは、Excel業務改善を専門とする会社です。エクセルの改修・自動化はもちろん、「本当にシステム導入が必要なのか」という視点も踏まえ、費用対効果を重視した改善方法をご提案します。まずは無料で、現在の課題をお聞かせください。

無料お見積もりシミュレーターを試す