menu

誰でも簡単!エクセルの在庫管理表の作り方【無料テンプレートあり】

現場で使いやすい!今回作成するエクセル在庫管理表の特長

在庫管理は欠品や過剰発注を起こさないために、非常に大切なものです。

工場の担当者様にお話をお伺いすると、在庫管理は行っているけれど、経理システムなどで入荷と出荷の数値で在庫管理を行っている、という企業をよく見かけます。

しかし、この在庫の数値は理論在庫と呼ばれ、現場で破損してしまった商品や不良品が含まれておらず、現場の実在庫とは差異が出てしまうことがほとんどです。

そのため、現場用に別途、手書きやエクセルの在庫管理表を使っている、という企業も多いのではないでしょうか?

そこで今回は手軽にわかりやすく実在庫の在庫管理ができる『エクセルを使った在庫管理表』の作り方をご説明いたします。

工場の在庫管理システム作成に長年関わってきた当社が、在庫管理をする際に必要だと思われる機能を入れ込み、できる限りシンプルに使いやすいエクセル在庫管理表を作成しました。

【今回作成する在庫管理表の特長】

  • シンプルで簡単
    ⇒品番と商品名(商品金額)を入れるだけで、誰でも簡単に在庫管理ができます。
  • 在庫金額が分かる
    ⇒在庫量から在庫金額が分かるようになっています。
  • 在庫調整ができる
    ⇒どうしても発生してしまう在庫差異は、入出庫とは別の欄で管理できます。
  • 平均入出庫数がわかる
    ⇒商品ごとの物量が分かり、適正な発注が可能になります。

尚、自分で作るのは面倒だ!という方はテンプレートを用意しておりますので、ダウンロードしてお使いください。

テンプレートダウンロード

入力頂いたメールアドレスに自動送信されます。
メールが届かない場合は、再度入力をお願いします。

在庫金額対応!エクセル在庫管理表の作り方

1.各種項目を入力する

下の画像を参考に、各項目名を入力してください。

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

2.日付を入力する

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

黄色のセルに棚卸日を入力すれば自動的に棚卸日以降の年間の日付が自動入力されるようにしていきます。

まず、赤枠のH5のセルの書式を日付に設定します。H5のセルを右クリックし『セルの書式設定』を選択します。

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

日付を選択して、お好みの日付の種類を選んだら『OK』をクリックします。

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

次にH5のセルに棚卸日プラス1日するために『=G5+1』と入力します。

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

これで黄色のセルに棚卸日を入力すれば、赤枠のセルに棚卸日の翌日が表示されるようになります。
試しに黄色の棚卸日に日付を入力してみてください。隣のセルに翌日が表示されるようになっていると思います。

さらに分かりやすくするために日付の上に曜日を表示していきます。

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

H4のセルを右クリックし、先程と同様にセルの書式設定の画面を表示します。
『ユーザー定義』をクリックして「種類」の欄に『aaa』と入力してOKをクリックします。

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

「aaa」はエクセルの書式設定で曜日を表示してくれるようになる記号です。

その後、H4のセルに先程棚卸日の翌日が表示されるように数式を入力したH5のセルを参照できるように『=H5』と入力します。

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

これで日付を入力すると自動的に曜日が入力できるようになりました。

翌日の日付と曜日が入力できれば、1年分の日付も自動入力できるように、オートフィルで1年分コピーをしてください。
※もしも半年ごとに棚卸を実施する場合は1年分ではなく半年分にするなど、ご自身の状況に合わせてご利用ください。

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

H4とH5のセルを選択し、青色の部分にカーソルを合わせて横にドラッグしてコピー(オートフィル)すればOKです。

3.日々の在庫数を自動計算する

日々の「入庫」「出庫」「調整数」を入力すれば、その日の在庫数が分かるようにしていきます。

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

赤枠のH9のセルに計算式を入力します。
日々の在庫は「前日の在庫」+「入庫」-「出庫」+「(+-)調整」となりますので、H9のセルに『=G9+H6-H7+H8』と入力します。

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

その後、先程と同じようにH9のセルを選択し、日付の最後の列までオートフィルで横にドラッグしてコピーします。

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

これで日々の「入庫」「出庫」「調整」を入力すると在庫が表示されるようになりました。

さらに現在の在庫をより分かりやすくするために、C列の在庫の欄に表示するようにします。
まず、見やすくするためにC6~C9セルを選択し、セルの書式設定より結合します。

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

結合したセルに、先程入力した最後の日付の在庫を参照できるようにします。
以下の場合ですと最後の在庫がNI9のセルですので『=NI9』と入力します。

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

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

これで日々の入出庫を入力すれば、ひと目で在庫が分かる状態になりました。

4.在庫金額を自動計算する

次に、在庫金額が分かるようにしていきます。
各商品ごとに品番、品名だけでなく、以下の黄色の部分に単価を入力できる欄を設けています。

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

ここに単価を入力すると赤枠の部分に各商品の在庫が表示されるようにします。

まずは見やすくなるように、先程と同じ要領でセルを結合しておきます。
在庫金額は「単価」×「在庫数」で算出できますので、D6セルに『=B9*C6』と入力します。

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

これで、各商品ごとの在庫金額が分かるようになりました。

5.平均を表示する

商品ごとの物量が分かりやすくなるように、平均を表示するように設定します。
「入庫」「出庫」「調整」「在庫」それぞれの平均を求めていきます。

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

まずは入庫の隣のセルF6に平均を求める関数「AVERAGE」を利用して『=AVERAGE(H6:NI6)』と入力します。「H6:NI6」は日付の始まりから終わりまでを参照しています。
ご自身で作成する場合は、ご自身のセルに合わせて参照してください。

「出庫」「調整」「在庫」の平均値も同様に入力します。
※F6のセルに数式を入力後、オートフィルを使ってF9までドラッグしてコピーすれば簡単に数式をコピーすることもできます。

6.商品入力欄を追加する

各商品情報を入力する6~9行をまとめて選択してコピーします。

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

10列以降に必要な商品数の分だけペーストすればOKです。

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

7.累計在庫金額を自動表示する

最後に(画像ではセルを結合してしまっていますが…)C3に作成した赤枠のセルに累計在庫金額を入力していきましょう。

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

累計在庫金額は、各商品の在庫金額の合計です。
現在各商品の在庫金額が入力されているD列の合計が表示されるように『=SUM(D:D)』と入力します。

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

SUM関数は合計を表示できる関数です。

これで累計在庫金額が表示されるようになり、在庫金額の表示もでき、誰でも簡単に入力できる便利な在庫管理表の完成です。

複数人で在庫管理表を扱う場合は、更新日が分かるように更新日の欄も作っておくと良いでしょう。
その際は先程累計在庫金額の計算式に使用しているD列を避けて他の列に更新日を記入するようにお気を付けください。

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

今回作成した在庫管理表は以下のリンクからテンプレートをダウンロードできますので、ぜひダウンロードの上、ご自身の環境に合わせてカスタマイズしてご利用ください。
ダウンロードしたテンプレートは品番や商品名、金額を入力するだけですぐにご利用頂けます。

テンプレートダウンロード

入力頂いたメールアドレスに自動送信されます。
メールが届かない場合は、再度入力をお願いします。

エクセル在庫管理表のメリット・デメリット

エクセル在庫管理表を利用する際はメリットとデメリットもしっかりと理解しておきましょう。

エクセル在庫管理表のメリット

エクセル在庫管理のメリット

エクセル在庫管理表のメリットはなんといっても無料で作成できることです。
今回の手順どおりにつくれば、どなたでも便利な在庫管理表を無料で作成できます。

さらにエクセルは多くの方が使ったことのあるソフトですので、どんな方でも簡単に操作できるのも大きなメリットです。

もちろん、手書きで行うよりは正確に、分かりやすく在庫管理することができます。

エクセル在庫管理表のデメリット

エクセル在庫管理表のデメリットは入力漏れや二重登録がどうしても多く発生してしまうことです。

エクセル在庫管理表を取り入れている多くの現場では、事務所に戻ってから事務所のパソコンで入出庫を入力されることが多く、そうすると、やはり入力ミスが起こってしまうのです。

また、事務所で差異が判明すると、再度倉庫に確認をしに行くなど、無駄な時間が発生することも珍しくありません。

エクセルで入力ミスが多発するなら在庫管理専用システムの検討を!

在庫管理専用システムの検討

このように、エクセルでの在庫管理は無料で簡単にできますが、入力ミスが多く発生してしまいがちです。

入力ミスが多発してお困りの場合は、バーコードリーダーを使った在庫管理専用システムを検討するのも良いでしょう。

商品の入出庫の際にバーコードを読み取るだけで、自動的に在庫管理ができるので、入力ミスが激減し、作業効率もアップします。
実際に当社で在庫管理システムを導入した企業では入力ミスが激減し、作業効率が約80%程度アップしました。

当社では無料で在庫管理システムを貸し出ししておりますので、ご検討中の方はぜひ一度試してみてくださいね。

無料ではじめる在庫管理システムについて

>お問い合わせはコチラ