在庫管理を行うには、入出庫数・在庫数を記入した在庫管理表が必要です。この在庫管理表を紙ではなく、電子データとして作成することで受ける恩恵は非常に大きく、今から在庫管理を行うならば、PCを用いた電子データによる在庫管理を行うと良いでしょう。
しかし、在庫管理表をPCで作成することに、苦手意識や不安を感じる方が多いのではないでしょうか？
そうした不安を抱える方の強い味方となるのがマイクロソフト社による表計算ソフトのエクセルです。エクセルを使えば、簡単に在庫管理表を作成することができます。
本稿では、エクセルを用いた在庫管理表の作成方法とそのメリットについて解説していきます。ぜひ最後までご覧ください。
エクセルで作成する在庫管理表について
エクセルを用いた在庫管理表の作り方
在庫管理表でできること、使用するメリット
在庫管理表作成の目的は効率的に在庫管理をすることです。在庫管理表を作成することによって以下のことが可能になります。
- 現在の在庫数の把握
- 発注が必要な商品の把握
- 月ごとの在庫数の記録
在庫管理表作成によって以下のメリットがあります。
- 在庫量確認のための人件費が減る
- 不良在庫の購入費、処分費が減る
- 在庫確認に使っていた時間が削減でき、業務に余裕がうまれる
既存の在庫管理ソフトではなく、エクセルを使うことのメリットは、以下が挙げられます。
- 操作が簡単
- 使用者がカスタマイズできる
- インターネット上で多くの情報が公開されている
自分で在庫管理表を作り上げることは難しく感じるかもしれませんが、以下で紹介する手順に従えば簡単に作成することができます。
エクセル在庫管理表の作り方
「商品の種類」、「入庫数」、「出庫数」を記入することで、在庫を表示できるエクセル在庫管理表の作り方を解説します。
表の項目を決める
在庫管理表には目的に合った項目を設定しなければなりません。ここでは商品在庫を管理するために、横軸・縦軸を以下のように設定します。
横軸 → 日付
縦軸① → 品番、品名
縦軸② → 入庫量・出庫量・在庫
横軸を設定する
表の項目が決まったら、1行目に横軸 （日付）を作成します。月ごとに在庫管理のエクセルシートを分ける場合、月の初めの在庫量は前月の繰り越しとなるので、日付の初めの列は「前月繰越」としておきます。前月繰越の次の列から1日ごとに日付を入力します。
セルに「7/1」と入力すれば、エクセルは自動でその数値を日付だと認識し「7月1日」と表示します。「7/1」を入力したら、オートフィル機能を使って1ヶ月分の「7/31」までの日付を作成しましょう。
「7/1」と入力したセルを選択した状態で右下にカーソルを持っていくとカーソルが黒い十字形に変化します。その状態で横方向にドラッグすると、7/2~7/30までの日付を入力できます。
オートフィル機能を詳しく知りたい方は以下をご参照ください。
参考資料 ：エクセルのオートフィルで連続データをサクっと入力
縦軸①を設定する
エクセルシートの1列目に「品番」と「品名」を入力します。
縦軸②を設定する
エクセルシートの2列目に「入庫量」・「出庫量」・「在庫」を記入します。
入庫量・出庫量・在庫を縦に3行入力し終えたら、入力した領域を選択し、「Ctrl + C 」で領域をコピー、「在庫」のすぐ下のセルを選択して、「Ctrl + V」で貼り付けができます。必要な品目の数だけ、貼り付けを繰り返していきます。
テーブルを設定
次にテーブルを設定します。テーブルとは表のことで、作成した在庫管理表を「表である」とエクセルに認識させる作業です。
これまでの作業で文字や数値を入力したすべての範囲を選択した状態で、「挿入」タブ内にある「テーブル」をクリックします。表に変換するための確認画面が表示されるので、選択範囲に問題がなければ、「OK」をクリックします。これでテーブルが完成しました。
テーブルとして設定すると、在庫管理表が見やすくなり、修正が簡単にできます。テーブルの使い方について詳しくは以下をご確認ください。
参考資料 ：Excelのテーブル機能の使い方まとめ | ノンプログラミングWebアプリ作成ツール
式（計算）の入力
最後に、入庫量と出庫量から在庫を計算する式を入力します。
・在庫 = 前月繰越 +入庫量 – 出庫量
です。
例えば、
7/1の在庫 = 前月繰越 + 7/1の入庫量 – 7/1の出庫量
7/2の在庫 = 7/1の在庫 + 7/2の入庫量 – 7/2の出庫量
で、以降同様にして在庫を計算できます。
各日付の在庫を上の式にあてはめて、式を作成しましょう。また、式にもオートフィル機能を適用できるので、在庫の式は1つだけ作れば、残りはオートフィルで簡単に作成できます。
式の作成方法が分からない方は以下を参考にしてください。
以上で、在庫管理表は完成です。
使用する際には、品目ごとに日々の入庫量、出庫量を入力していくと、自動で在庫が計算されます。今回紹介した在庫管理表には、不良在庫の処分などは含まれていません。必要に応じてカスタマイズしましょう。
エクセルテンプレートを利用して在庫管理表を作成する
ゼロから作らずとも、インターネット上で公開されている無料のエクセルテンプレートをダウンロードすることで、より洗練された在庫管理表を使うことができます。ただし、使用者にエクセルに関する知識が全くない場合には、ダウンロードしたテンプレートを編集することができず、問題が発生しても対処することができません。テンプレートを使用する場合にも最低限の知識は必要となります。
無料で使えるエクセル在庫管理表は以下のサイトでダウンロードできます。
参考資料 ：エクセル在庫管理表の作り方の基本 – 在庫管理の改善・生産性向上なら在庫管理110番
VBAでマクロを作る
「マクロ」とは、複雑な操作や連続した操作を、まとめて1つの操作で実行できるようにする機能を指します。
マクロが効果的なのは、入出庫数の入力量が非常に多く、作業量が膨大な場合です。マクロを使えば、入出庫数の入力をより簡単にすることができます。さらに、マクロを使いこなせれば、数回クリックするだけで入出庫数の入力を完了できるため、マクロは非常に便利な機能です。
例えば「出庫数が記録されたデータファイルを開く」 → 「データを読み取る」 → 「読み取ったデータを適切な形に並び替える」 → 「在庫管理表にデータを貼り付ける」 という一連の操作を、数回のクリックにまとめることができます。
エクセルマクロはVisual Basic for Applications (VBA) というコンピュータ言語で記述されており、VBAについての知識がなければ、編集することはできません。知識がなくエクセルマクロのテンプレートを使用すると、問題が生じた際に対応することができないので、注意しましょう。
在庫管理にマクロを使用することを検討しつつ、マクロの勉強をすれば、効率的に進めることができます。これを機にマクロの導入を検討してみてはいかがでしょうか。
以下でマクロありのエクセルテンプレートがダウンロードできるサイトをご紹介します。
参考資料 ：エクセルで在庫管理をする方法 【テンプレート付き】 | デスクワーク ラボ
自社に合った在庫管理表を作る
エクセルの利点は低コストかつ簡単であることだけではなく、使用者自身で編集でき、自社に合う在庫管理表を作成できる点にあります。
在庫管理にはあらゆる場面に応用できる正解はないと言われています。導入した在庫管理システムの効果を検討・修正し、反省を次に生かす、というサイクルを回し続けることによってのみ、最適な在庫管理システムを作り上げることができます。在庫管理表も自社に合った形へと常にアップデートすることを意識しましょう。改善を繰り返すことで、在庫管理に関する自社の問題にも気付きやすくなるはずです。
ただし、在庫管理表を編集する中で、行や列を継ぎ足したり、式を編集したりという作業を続けていると、表が乱雑になり、使いにくい表となってしまうこともあります。
表の作成は可能な限りシンプルにするよう意識し、複雑な表になる場合には、シートを分けることも検討しましょう。
エクセルの在庫管理表を運用する際のポイント
在庫管理表をエクセルで運用する場合は、2つのポイントを守りましょう。
1.運用ルールを決める
まずは、運用ルールを立てておきましょう。
【運用ルール】
・在庫管理表にアクセスできる人は誰か？
・在庫管理表に入力できる人は誰か？
・在庫管理表の表記方法は？
・データ入力の日付や時間は？
ルールを定めることで、誤記入や入力し忘れを防止できます。
2.バックアップを取る
エクセルの在庫管理表は、必ずバックアップを取得しておきましょう。ハードディスクが突然起動しなくなり、アクセスができなくなると一大事です。また、天災などの影響でIT資産がなくなる恐れもあります。このような予想外のリスクに備えて、必ずバックアップを取得しておきましょう。社内で管理が不安な方は、クラウドサーバーで管理するのも1つの方法です。Microsoft社が提供するクラウドサーバーでデータ保管しておけば、セキュリティ面でも安心できるでしょう。
Googleスプレッドシートを使った在庫管理について
Googleスプレッドシートとエクセルとの違い
エクセルと同様に、Googleの無料アプリ「Googleスプレッドシート」を使っても在庫管理表を作成できます。こちらはOfficeソフトが必要なく、Gmailアカウントを作成すれば無料で使用できます。
操作感はOfficeのエクセルとほぼ同じで、エクセルの操作に慣れた方であれば、Googleスプレッドシートは難なく操作できるでしょう。マクロで使われる言語はエクセルとは異なりますが、エクセルでVBAを使いこなせる方にとっては、Googleスプレッドシートのマクロを使うことも容易です。
Googleスプレッドシートの最大の特徴は、「複数人で同時に編集でき、表をリアルタイムに共有できる」ことです。元々オンライン上のアプリケーションなので、URLを伝えるだけで誰でも操作することができます。もちろんアクセスを制限することも可能です。
また、いつ、誰が、操作を行ったかを簡単に記録することができ、打ち間違いなどによるミスのチェックが可能です。
在庫管理用のGoogleスプレッドシートテンプレート
Googleスプレッドシートを使った在庫管理の詳細、テンプレートのダウンロードは以下をご参照ください。
参考資料：【作ってみた】googleスプレッドシートで在庫管理表
現場の負担について
在庫管理精度と現場の負担はトレードオフである
マクロの活用により、入出庫数のデータ入力を容易にすることはできます。しかし、入庫数・出庫数のデータを取得するまでの負担をエクセルなどのソフトウェアで軽減することはできません。
在庫の位置、ロット、正確な個数などを把握するためには、より詳細なデータの取得が必要で、情報の精度を高めるにつれ、現場での負担が増えていきます。在庫管理の精度と現場の負担はトレードオフなのです。
データ取得に要する現場の負担が大きくなりすぎると、データの取得作業が杜撰になり、繁忙期には最低限必要なデータまで取得できない、という事態も起こりえます。
エクセルやGoogleスプレッドシートを利用して在庫管理を始めるならば、最初のうちは、必要最低限のデータ取得に留めるべきでしょう。在庫管理の効果が目に見えて分かるようになり、データ取得の必要性について現場担当者と管理者との間で共通の認識が出来上がれば、詳細なデータ取得へスムーズに移行できます。
バーコード・QRコードやRFIDを活用したエクセルでの在庫管理
実際にデータを取得するのは現場であり、詳細なデータを取得するためには人力だけでは限界があります。根本的に現場の負担を減らすためには、データ取得をサポートするハードウェアやソフトウェアの利用や自動でデータを取得するシステム作りの検討が必要です。
データ取得の手間を減らし、在庫管理の業務効率を改善するために近年導入が進められているのが、バーコード・QRコードやRFIDです。RFIDは無線通信技術を用いた電子タグで、専用のリーダーにより、遠隔から複数同時に商品データの読み取りができます。
バーコード・QRコードやRFIDによるシステムは現場でのデータ取得をサポートし、データの取得精度向上、データの取得に必要な時間の削減に貢献します。在庫管理に必要なデータの取得が大きな負担となる場合には、バーコード・QRコードやRFIDの導入を検討してみてください。
バーコード・QRコードやRFIDを在庫管理へ活用する方法についての詳細は、以下の記事をご覧ください。
10分でバーコード・QRコード・RFIDを利用した在庫管理がわかる
まとめ
在庫管理表の必要性とその作成方法について述べてきました。
自社に合った在庫管理表を作成するには、改善を繰り返すことであり、それは、エクセルでも、Googleスプレッドシートでも、マクロを使った場合でも同じです。
データ入力の労力はエクセルなどで改善が可能ですが、データ取得のための労力はソフトウェアでは解決不可能であることにも注意が必要です。必要なデータが多くなり、データ取得のための現場の負担が大きくなったときには、バーコード・QRコードやRFIDなど、データ取得をサポートする技術の導入をご検討ください。
RFルーカス 代表取締役社長。
京都大学法学部卒。ノースウェスタン大学ケロッグ経営大学院にてMBA取得。丸紅株式会社にて石油・天然ガスのトレードや海外の事業投資などに従事。4年間のカタール駐在経験あり。2017年7月に株式会社トライステージに入社。事業開発部グループマネージャーとしてM&Aをリードすると共に、株式会社日本ヘルスケアアドバイザーズやメールカスタマーセンター株式会社の取締役を兼任。
