エクセルで在庫管理表を作成しよう！バーコード連携についても解説

エクセルテンプレートを利用して在庫管理表を作成する

エクセルの操作に慣れていない人が、一から在庫管理表を作成するには時間がかかるでしょう。ネット上には無料で公開されているエクセルの在庫管理表のテンプレートもあり、これらをダウンロードして使うことができます。

ただし、自社に最適な形式のテンプレートであるとは限りません。大抵の場合は自社向けに手を加えて使うことになります。人が作ったものに手を加えて扱うのは容易ではありません。その場合は、一から作成したほうが使いやすくなるでしょう。

エクセル関数を利用して在庫管理表を作成する

テンプレートでは自社に最適な在庫管理表にならない場合は、以下のような関数を駆使して一から在庫管理表を作成するという方法が良いでしょう。入庫と出庫は別シートで管理する場合が多く、これらの関数を上手く活用することで入庫数と出庫数の差である在庫数を簡単に算出することができます。

SUMIF関数

足し算の合計を求める関数であるSUM関数よりも一歩進んだ関数です。特定の条件を付け加え、その条件に合う数値だけを足し算することができます。

SUMIF関数を用いれば、複数ロケーションに同じ商品を保管していても、すぐに在庫数の合計を求めることができます。また入庫数と出庫数の合計を算出し、その差を見れば現在の在庫数を知ることができます。

VLOOKUP関数

VLOOKUP関数は表を縦方向に検索していき、特定のデータに対応した値を取り出す関数です。例えばある商品コードから商品名や棚番を導くことができます。

在庫管理においては、入庫管理表と出庫管理表という別々のシートから数値データを在庫管理表に抽出し、その差を求めることで在庫数が分かります。

IF関数

IF関数はエクセル関数の中では比較的難易度が低く有名な関数です。設定した条件に対して「真」か「偽」かによって表示する値を変えることができます。

SUMIF関数やVLOOKUP関数で算出した在庫数が一定の数以下になったときに、自動で「要発注」などと表示させることができ欠品防止に役立ちます。

マクロを利用して在庫管理表を作成する

エクセル関数に加えて、エクセルマクロを使って在庫管理表を作成するという方法もあります。エクセルのマクロはプログラミングの１種であるため、敷居が高く感じられがちです。しかし、自由度が高く多くの作業を自動化できるため、ミスと手間を減らすことができるでしょう。

初めてマクロに触れる場合はまず「マクロの記録」機能を使用してみることをおすすめします。ビデオの録画のように、録画開始～録画停止までの間に入力者がエクセルシート上で実行した処理を記録し、後から記録されたコードを確認することもできるのです。

更に記録したコードを Visual Basic Editor (VBE) にコピーし必要な箇所を編集・削除することで、初心者であってもより質の高いマクロを作成することが可能です。

エクセルで在庫管理をする際の４つの注意点

エクセルで在庫管理を行うときの４つの注意点について知っておきましょう。

１．管理の項目が明確になっているか確認する

管理の項目で重要なのは、以下の４つです。

• ●何が（商品名、商品コード）
• ●いくつ（数量）
• ●いつ（入荷日、出荷日）
• ●どこに（棚番）

この４つを把握することが、在庫管理の目的です。特に、「何が」「いくつ」あるのかが最重要でしょう。「いくつ」については、入庫数から出庫数を差し引いた値です。つまり、何がどのくらい入庫され、それがどのくらい出庫したかが最重要事項になります。

そのうえで、消費期限や製造年月日の管理が重要な場合は「いつ」、そして保管場所でトラブルを生じることが多いようであれば、どこに保管しているのかを管理することも大切です。種類と数さえ分かればいいのか、場所や日付も重要なのか、自社の業種と照らし合わせて考えましょう。

２．管理する在庫の優先度を決める

日付や場所まできちんと記録できれば、それに越したことはありません。しかし、すべての商品に対してそれを行うと、多大な手間と労力を必要とします。そのため、商品の優先度を決めましょう。そのためにはABC分析が有効です。ABC分析では商品を以下の３段階に分類して扱います。

• A：全体売上の７割を占める商品
• B：全体売上の１～３割を占める商品
• C：全体売上の１割未満の商品

このように重要度を分類し、より重要度の高いものは細かく管理を行いましょう。逆に重要度の低いものは、最低限の管理で手間と労力を削減できます。

ABC分析について、詳しくはこちらの記事を参照ください。

関連記事

watch_later 2019.11.28

【図解】在庫管理におけるABC分析とは？わかりやすく解説します！

続きを読む ≫

３．データの入力がしやすい在庫管理表にする

データ入力しづらい在庫管理表だと、どうしても入力が億劫になり、入力を先延ばしにすればするほど、データと実際の在庫数がそぐわないことになります。データがこのように信じられない状態になっては、管理しているうちに入りません。きちんと管理するためにも、簡単に入力できる表にしましょう。

４．上書き保存はせずに更新履歴を残す

上書き保存をする方法で記入していると、今の在庫をすでに記入したのか、それとも前のデータなのかがわかりません。そうなると結局一から数え直すことになり、膨大な手間がかかります。履歴も残らないため、対処のしようがありません。

いつ何がいくつ入庫したのか、あるいは出庫したのかを記録し、履歴が残るような工夫をしましょう。

バーコードを活用したエクセルでの在庫管理

最近ではハンディターミナルでバーコードを読み取ることで、在庫管理の効率化に成功した企業が増えてきているようです。ハンディターミナルとバーコードを活用すると、事務所のパソコンから入力を行わずとも、その場でリアルタイムに在庫状況が反映されるのです。エクセルで在庫管理をしている場合も、バーコードでの読み取り結果をシートの適切な箇所に反映させることが可能です。

ハンディターミナルを販売しているメーカーなどでは、バーコードを活用したエクセルでの在庫管理の導入実績を持っている場合も多いため、興味がある場合はメーカーに問い合わせても良いかもしれません。

バーコードによる在庫管理については、以下の記事で詳しく解説しています。

関連記事

watch_later 2019.11.28

バーコードで在庫管理を効率化！システムとの連携も？

続きを読む ≫

在庫管理システム導入でエクセル入力の手間を減らそう

エクセルで在庫管理を適切に行うための注意点などを解説してきましたが、実際に行うのは容易ではありません。ロケーションや消費期限の管理は、エクセルですべて入力しようと思うと大きな負担になります。

専用の在庫管理システムであればラベルやバーコード、ハンディターミナルで扱うことで、手入力する手間を大きく削減できます。

在庫管理システムには無料プランの製品もあるので、この機会に検討してみてはいかがでしょうか。在庫管理システムに興味を持たれた方は、以下の記事をチェックしてみてください。

関連記事

watch_later 2019.12.10

無料で在庫管理を行いたい方必見！フリー在庫管理システムもご紹介

続きを読む ≫