エクセル関数を使った在庫管理表の作り方と運用方法

在庫管理表の作り方

在庫管理表でできることと、エクセル関数を用いた基本の在庫管理表の作り方について解説します。

在庫管理表でできること

在庫管理表を用いることで、例えば以下のような項目を瞬時に把握できるようになります。

（1）月ごとの在庫数
（2）現在の在庫状況
（3）発注入庫が必要な商品

エクセル在庫管理表の作り方

今回は1ヵ月分の在庫を管理するという想定で、エクセルで在庫管理表を作成してみます。

(1)表の項目を決める

表を作り込む前に、まずは在庫管理表で管理すべき「項目」を決めましょう。今回は、品目ごとに入出庫数と在庫数を管理するため、縦軸・横軸にそれぞれ下記の項目を設定します。

■設定項目
縦軸①→品番、品目
縦軸②→入庫数、出庫数、在庫数
横軸→日付

(2)横軸を設定する

次に、1行目に横軸を設定します。「在庫」は前月分が繰り越されるため、日付軸の最初の列は「前月繰越」にしておきます。前月繰越の次の列から6月分の入力欄として使用します。

「6月1日」まで入力したら、「フィルハンドル」を使って、6月2日以降の日付を自動入力しましょう。フィルハンドルとは、入力内容の「規則性」を読み取って連続したセルを自動で埋めてくれる便利な機能です。

「6月1日」と入力された[D1]のセルを選択し、セルの右下の角にポインターを合わせ、表示が「＋」に変わったら、そのまま右にドラッグすると連続した日付が自動で表示されます。

これで、6月30日までの日付が入力されました。

(3)縦軸①を設定する

続いて、「品番」と「品目」を入力する「縦軸①」を準備しましょう。「品番」と「品目」を選択して右クリックし、「コピー」を選択。選択範囲をコピーします。

[A5]のセルを選択して右クリックし、さらに「貼り付け」を選んで、先ほどコピーした「品番」と「品目」の項目を貼り付けます。

管理したい品目数に応じてこの作業を繰り返してください。

（4）縦軸②を設定する

入庫数・出庫数・在庫数について（3）と同じ手順で設定をします。

これで（1）で設定した項目の入力が完了しました。

（5）テーブルの作成

（1）～（4）の作業が終わったら、「テーブルの作成」を行います。テーブルを作っておくとエクセルのワークシート内の表が「ひとまとまりのデータ」として扱いやすくなります。

（1）～（3）で作成した範囲を全て選択してください。この時、[A1]セル～[A10]セルまでを選択し、[shift]キーと[ctrl]キーを同時に押しながら、カーソルキー[→]を2回押すと、文字列が入力されたセルの先端（6月30日）まで瞬時に選択することができます。

「挿入タブ」から「テーブル」をクリック。下記のようなウィンドウが表示されたら、選択範囲に間違いがないことを確認して「OK」をクリックします。

これでテーブルが作成されました。

（6）関数を入力する

最後に、日々の在庫数を自動で算出するために関数を入力します。

ですので、6月1日の在庫数(セル[D5])を求める関数は

となります。
この関数を[D5]に入力すると、6月1日の在庫数が自動で計算され、表示されます。

日付の入力の際に使った「フィルハンドル」は、関数にも使うことができます。フィルハンドルを使って[D5]のセルから右にドラッグすれば、手動で関数を入力しなくても自動で日毎の在庫数を求める関数が入力されます。

これでエクセル関数を利用した在庫管理表の完成です。

在庫管理表の項目は企業によって異なりますので、必要に応じて項目を加減して、用途に合った表にカスタマイズしてください。

エクセルで在庫管理を行う際の注意点

エクセルで在庫管理をする際は、どのような点に注意したらよいのでしょうか。

商品・備品の管理ルールを作る

商品や備品の在庫管理においては、事前に管理ルールを明確しておくことが大切です。
在庫の保管場所の棚・列・段に番号を振り、「〇〇は棚Aの3列2段目」など商品毎に保管場所を決め、誰でも場所の把握や的確な指示出しができるような管理体制を整えておくと良いでしょう。

■在庫のロケーション管理の例

備品管理の場合は、共有備品の置き場所を明確にすることで、備品の私物化を防ぐことができます。
また、次のような備品利用方法のルールを定めることで、備品の所在が把握しやすくなるだけでなく、紛失防止にも役立ちます。

• 特定の備品を借りるには上長の承認が必要
• ◯個までしか利用できない
• ◯時までに返却しなければならない

在庫管理表には適切な項目を設定する

在庫管理表には用途に合わせて適切な項目設定を行うことが重要です。一言で「在庫」と言っても、製造業務に使用する部品なのか、小売業務で扱う商品なのか、返却が必要な社内の共有備品なのかでは、在庫管理表に設けるべき項目は異なります。

■設定項目の例

• 製造業で現場スタッフが在庫管理する場合
  →「品番」「仕損数」「ロット数」「保管場所」など
• 返却が必要な共有備品の場合
  →「利用者」「利用数量」「返却日」など

在庫管理表の運用ルールを作る

正確な数字を管理するために、運用ルールを設けることは大切です。適切な設定項目を設けた管理表ができたとしても、表の管理・運用に不備があれば意味をなしません。まずは管理表の運用マニュアルを作成し、管理に携わる関係各所で共有することが大切です。

RPA導入で在庫管理をよりスムーズに！

日々管理すべき在庫の種類や数が大規模な会社の場合、RPAを導入すればよりスムーズに在庫管理を行うことができます。RPAとは（Robotic Process Automation）の略で、ロボットによる業務の自動化を指します。データ入力のような定型作業を得意とし、在庫の報告業務や一定数を下回った商品の発注業務などを自動化することも可能です。これらの業務を人間に代わって行うことで、ヒューマンエラーや業務にかかる時間を大幅に削減することが可能になります。

イレギュラーな事案に対して適切に判断を下し、行動することは人間にしかできないことですが、定形業務の自動化は大幅な時間や人的コストの削減につながります。RPAをうまく取り入れて業務の効率化を図ってみてはいかがでしょうか。

まとめ

在庫管理においては、自社に合わせた最適な在庫管理表を管理・運用していくことが大切です。こちらでご紹介した在庫管理表の作成や運用方法を参考にしてみてください。
とはいえ大規模な在庫管理となるとエクセルで作成した在庫管理表では管理しきれない可能性があります。
その場合はRPAでの管理業務そもそもを簡略化するのがおすすめです。下記からRPAでどういったことができるのか確認してみてはいかがでしょうか？