初心者でもすぐに実践できる！在庫管理をエクセルでおこなう方法

エクセルって本当に便利なツールですよね。使い慣れてくるとたいていの事はエクセルでできてしまいます。

エクセルに勝てる神ツールなんてないのでは？と思うくらい、誰もが認める万能選手。ですから在庫管理にエクセルを使っている事業者も多いことでしょう。

一方、最近はネット販売を始める方が増えているので、在庫管理に頭を悩ませている方も多いはず。

「また在庫切れ。ショップの評判が落ちたらどうしよう。」

「在庫数が合わなくて毎日数えてばかり。」

「仕入れのタイミングがつかめない。」

こんなふうにお悩みのあなた、在庫管理をエクセルでやってみると、コストをかけずに自分仕様の在庫管理表を作ることができます。

今回は難しい関数を使わずに、初心者でもすぐに作れる在庫管理表の作り方をご紹介。「文字入力はできるけど、関数はよくわからない」という方でも簡単に作れますので、ぜひ一度試してみてください。

後半では在庫管理表をエクセルで作る時、知っておくと便利な関数を一覧にしました。失敗しがちな点についても触れていますので「転ばぬ先の杖」としてご活用ください。

膨大な量の在庫管理には「在庫管理システム」を検討しよう

在庫管理表をエクセルで作る場合、気をつけておきたいことがあります。

エクセルは複数のメンバーで運用すると、同期のタイミングで不具合が起こりがち。またデータ数が増えてくると処理に時間がかかり、画面がフリーズしたり最悪ファイルが壊れたりしてしまいます。

マクロを使うと実用的にはなりますが、作った人がいなくなるとメンテナンスに困りますよね。

ですから事業規模が拡大してきてデータ数が膨大になってきたら、早めに「在庫管理システム」の導入をおすすめします。面倒な業務が減って効率化が進むと、収益アップにつながる戦略も立てやすくなるでしょう。

当メディア（AIZINE）の運営会社「お多福ラボ」でも、在庫管理システムを開発し業務の効率化を図りました。

お多福ラボの実績「属人化した在庫管理／発注判断作業を自動化」 | お多福ラボ

多くの在庫を取り扱うお客様の会社では、未だエクセルによる商品管理に依存しているなど多くの問題を抱えていました。それを受けて在庫管理業務を行う各担当者様とヒアリングと議論を重ね「あったらいいな」をシステムに落とし込みながら、在庫管理システムを開発。社内ルールの整理や見直しにより多くの業務改善が実現した事例について解説しま…

otafuku-lab.co

そして在庫管理システム導入に関するご相談を絶賛受付中です。お客様に合わせて柔軟な対応が可能ですので、気になっている方はぜひ一度、ご相談ください。

次は在庫管理表をエクセルで作る方法をご紹介。

在庫管理表の作り方

ではお待ちかね、在庫管理表をエクセルで簡単に作成する方法をご紹介します。

主要項目の入力

【テーブルの作成】

まずは項目欄に文字入力から。

1. エクセルで上の図①のように文字を入力します。（セルの位置に注意）
2. 次に、表をテーブルにします。図②のように範囲指定して「Ctrl+t」→OKを選択。（または、ホーム→「テーブルとして書式設定」→好きなパターンを選択→OKを選択）

【日付】
次は横軸の日付を月末まで自動で入力します。（図③）

1. 「3月1日」のセルをアクティブ（セルをクリック）にして右下角にポインターを合わせます。
2. すると「＋マーク」が現れるので、それを右へドラッグ。
3. 「3月31日」になるまでずーっと右へドラッグしてください。

このように、「＋マーク」をドラッグして連続入力することを「オートフィル」と言います。

【品番など縦軸の項目】
次は縦軸をオートフィルでコピーしていきます。（図③）オートフィルは、最初に複数セルを選択することも可能。下記の手順で商品数に応じてコピーしてください。

1. A列のセル3つ分を範囲指定し、右下角にポインターを合わせると現れる「＋マーク」を下へドラッグ。
2. 同じくC列のセル3つ分を範囲指定し、「＋マーク」を下へドラッグ。

これで表の大枠が完成しました。次で関数を入力していきます。

たなべ

あと少しで完成！ここでは簡単な関数しか使わないのでご安心ください。

関数の入力

ここで、在庫数を毎日計算しなくてもいいように、関数を入力しておきます。在庫数を求める数式は次のとおり。

1. この数式を3月1日の在庫数のセル、[E6]に入力します。（図④）
2. そして先ほどと同じように、数式もドラッグしてコピーしましょう。

ウィンドウ枠の固定

このままでは、月末になると項目が隠れて使いづらいため、ウィンドウ枠を固定します。ウィンドウ枠を固定すると、上下左右にスクロールしても項目が隠れません。

そして品名など入力してシートに名前をつければ完成。手順は以下のとおりです。

1. セル[D4]をアクティブ（セルをクリック）にする。
2. 「表示→ウィンドウ枠の固定→ウィンドウ枠の固定」を選択。
3. 品番や品名、数量を入力。
4. シート名を「〇〇年〇月」に変更。
5. 月が替わったらシートをコピーして、数量を変更する。

さあ、これで完成です。試しに前月繰越在庫数や、入庫数、出庫数を入力してみてください。自動で計算されることがわかります。

たなべ

お疲れさまでした。簡単なのがうれしいですよね。

この在庫管理表のメリットとデメリット

今回作成した在庫管理表のメリットとデメリットを確認しておきましょう。

今回作成した在庫管理表には入出庫表がないため、入出庫数をあらかじめ集計しておかなければいけません。するとリアルタイムで在庫数を反映させるのが難しいという欠点があります。

そこで、もう少し利便性の良い在庫管理表を作るために、知っておくと便利なエクセルの関数を次でご紹介。

在庫管理をエクセルでおこなう場合使える関数一覧

ここでは、在庫管理表をエクセルで作る場合に知っておくと便利な関数と、そのうち3つの関数を使った在庫管理表をご紹介します。

在庫管理に便利な関数一覧

在庫管理表をエクセルで作る場合に便利な関数は以下のとおり。

これらの関数を理解しておくと、自社のニーズに合った在庫管理表をエクセルで作ることができます。例えば商品を発注する際、発注ロットを考慮にいれなければいけませんよね。そんな時はCEILING関数を使うと便利です。

これらの関数は組み合わせて使う場合も多いので、すべてを使いこなすには慣れている人でないと難しいかもしれません。関数の他、「データの入力規則」や「条件付き書式」の知識もあった方が便利でしょう。

上記の関数を使った在庫管理表

最初にご紹介した在庫管理表には、入出庫表がないためにリアルタイムで在庫数を把握することができませんでした。そこで作ってみたのが上の表。この在庫管理表なら、入出庫のたび入力すればリアルタイムで在庫数が反映されます。

普通、商品数があとから増減する場合に備えてOFFSET関数とCOUNTA関数をよく使用するのですが、ここではそれらを使用せず、「名前の定義」を使ってできるだけシンプルな数式になるように工夫しました。

関数に慣れていない人は、このくらいの長さの数式が限界ですよね。上記の在庫管理表は、下記が理解できれば簡単に作ることができます。

• VLOOKUP
• SUMIFS
• IF
• IFERROR
• プルダウンリスト（データの入力規則→リスト）
• 名前の定義

「名前の定義」を使うと、数式が見やすくなり、商品数が増減しても自動で対応してくれます。

しかし、欲を言うと入庫表と出庫表は別々の方が管理しやすいし、発注ロットにも対応していません。使いながら順次改良していくことが必要になるでしょう。

では在庫管理表をエクセルで作る場合、どんなことに注意すれば良いのでしょうか。次にまとめてみました。

在庫管理をエクセルでおこなう場合の注意点

在庫管理表をエクセルで作る場合の注意点は次のとおり。

この中でも「入出庫の履歴」は特に重要。在庫数を上書きしていくだけだと、重複入力や入力漏れがあった場合に確認することができません。

たなべ

入力したかどうかわからなくなることって、よくあります。

いざという場合に備えて、定期的なバックアップは必須。取引が多くなるほど頻繁にしておかないと、事故があった場合取り返しがつかなくなります。

また、複数人で運用するとエクセルに慣れていない人がうっかり数式を崩してしまう可能性があります。タイミングによっては同期がうまくいかない場合も出てくるでしょう。

顧客に「在庫あり」と回答したのに、実は完売していた

商談中にまさかのフリーズ！ありえない！！

行を増やした時に関数が壊れてしまった

これらは在庫管理をエクセルでしている場合によくあるトラブルです。

在庫管理をエクセルですると、コストがかからないという大きなメリットがあります。しかし、事業規模が大きくなってきたら、大きなトラブルが発生する前に早めに「在庫管理システム」を導入するのがおすすめ。

たなべ

ビジネスは信用第一。トラブルは未然に防ぎましょう。

ここでは初心者でもすぐにできる、在庫管理表をエクセルで作る方法をご紹介しました。慣れてきたら一覧に挙げた関数を使って、より使いやすくなるように工夫してください。

エクセルは奥が深く、勉強すればするほど簡単な入力方法が見つかるもの。そんな時は「今までのムダな努力」と「技を発見した喜び」のハザマで、どう感情表現すればいいのか困ってしまいますよね。

とはいえ、自分仕様の在庫管理表ができたら達成感で気分良くなれます。毎日の面倒な入力作業がきっと楽しくなるでしょう。

そして今回は神ツールと呼ばれるエクセルにも、在庫管理に使う場合はデメリットがある点を解説しました。順調に売上が伸びて事業が軌道に乗り始めたら、そろそろ在庫管理システムの導入を検討するタイミング。

本格的な在庫管理システムは「ムダな在庫の削減」「業務効率アップ」「受注発注ミスの削減」「顧客満足度のアップ」とさまざまな効果が期待できます。ご検討中の方はぜひ「お多福ラボ」まで。ご相談お待ちしております。