Excelで在庫管理をするには？表作成の基本とポイントを紹介

ECサイトの運営において、在庫管理は重要な業務です。正確に在庫管理を行わなければ、在庫切れや発注ミスなど業務に大きな支障が出る恐れがあるためです。

スムーズな運営のためにも欠かせない在庫管理ですが、Excelを上手く活用できれば、より在庫管理の効率化が可能です。

この記事では、Excelでの在庫管理表作成の基本とポイントを紹介します。Excelを使うことのメリット・デメリットや注意点もあわせてお伝えいたします。

Excelで在庫管理表を作成する方法

「在庫管理表」とは、保管している商品の数・入荷日などの情報を記録しておくための表です。

ECサイトの在庫管理には、いつでもデータを把握するための在庫管理表の作成が欠かせません。

また、Excelを使って在庫管理表を作成する場合、自分ですべて作成する方法だけでなくテンプレートを活用する方法もあります。

ここからは、在庫管理表を自作する方法、そしてテンプレートを使って作成する方法もみていきましょう。

自分でイチから作成するには

簡単な在庫管理表であれば、自作することも可能です。在庫管理表をExcelで自作する際の手順は下記の通りです。

1.   ヘッダー項目を設定する
2.   商品に関するデータを入力する
3.   数式を設定する
4.   罫線を設定する

まず、1行目にヘッダー項目となる商品番号・商品名・入庫数・在庫数など管理が必要な項目を入力しましょう。

そして設定した各項目の下に、必要なデータを入力していきます。

在庫数など、データに計算が必要な箇所には数式をはめ込みましょう。

最後に、表全体に罫線を設定して準備完了です。

また、在庫管理表として使用する罫線は、格子状がわかりやすいでしょう。

誰が見てもわかりやすいように工夫・調整していくことが重要です。

無料のテンプレートで作成するには

無料で提供されているExcel在庫管理表のテンプレートを活用して、簡単に在庫管理表を作成する方法もあります。

検索すると、多くのサイトでExcelテンプレートが無料提供されていますが、必ず信頼できるところからダウンロードをしましょう。

使用するテンプレートを選ぶ際は、できるだけ厳選して操作が簡単そうなものを見つけてください。

また、テンプレートをそのまま使うだけでなく、より実践的で使いやすくなるようにカスタマイズすることも可能です。

項目・レイアウト・色分けなど、必要に応じて追加・削除・変更して自社に適したスタイルに仕上げていきましょう。

無料でダウンロード可能な在庫管理Excelテンプレートサイトを3つご紹介します。テンプレートを参考に在庫管理表の利用を始めることができます。

• ビズオーシャン　「在庫管理表・棚卸表」 の書式テンプレート・フォーマット一覧はこちら
• ビズ研　在庫管理表テンプレート集（Excel・PDF・スプレッドシート）はこちら
• テンプレデポ-在庫管理表テンプレート(業種別)はこちら

使いやすい在庫管理表3つのコツ

連携ミスを防ぐためにも、在庫管理表は誰もが使いやすいスタイルで完成させることが基本です。

ここでは、Excelで簡単に操作・確認ができる在庫管理表を作成するためのポイントを3つに分けてお伝えします。

1.      記入する項目をわかりやすく

効率的な在庫管理には、表作成時に使う項目をわかりやすく設定する必要があります。

必要な項目は、業種や取り扱う商品をすべて把握した上で調整していきましょう。

基本的には、商品・商品番号（商品コード）・入出庫日・数量の3つが在庫管理において必須の情報です。

• 商品名
• 商品番号（商品コード）
• 入庫日・入庫数
• 出庫日・出庫数
• 在庫数

これらの項目を追加して、毎回忘れずに入力していけばトラブルを最小限に抑えられるでしょう。

また、ほかにあると便利な項目例としては以下が挙げられます。

• 在庫の状況
• 仕入単価
• 販売単価
• 賞味・消費期限(食品の場合)

ただし、項目を多く設けすぎると入力に手間がかかるだけでなく、誰かが重要な部分を見落とす可能性も生じるため、できる限り必要なものだけを厳選していく工夫も必要です。

2.      シンプルなデザイン

在庫管理表は、できるだけシンプルなデザインに統一しておくことがおすすめです。見た目が複雑すぎると見にくくなるため、誰がみても把握しやすいレイアウトを心がけましょう。

不要な枠やデザインは省略して、商品に関する情報を1つのページにまとめるのが基本です。

また、在庫が少なくなった場合には、文字やセルの色を変える設定をしておくと一目で判別可能です。

3.      自動計算などを設定する

在庫管理の効率化には、表の見やすさだけではなく使いやすさも重視しなければいけません。

たとえば入力のたびに合計数を計算するなどの手間が発生すると、計算ミスが生じるだけではなく、担当者によってはつい作業を後回しにしてしまうかもしれません。

Excelは、自動で計算するための様々な数式を設定できる便利なツールです。

ある程度の知識は必要になりますが、適切に数式を活用すれば少ない入力で安定した在庫管理を実現できるでしょう。

在庫管理表作成で使えるExcel関数

Excelで在庫管理表を運用していくには、関数を用いる方法がおすすめです。

在庫管理表の計算に活用できる関数にはどんな種類があるのか、以下でいくつかご紹介します。

SUMIF・SUMIFS関数

まず、選択されたセルの範囲内の合計を一気に出すことのできるSUM関数は基本の関数といえるでしょう。

そしてSUMIF・SUMIFS関数とは、どちらも指定した条件にあった数値のみの合計を計算する数式です。

条件が1つだけの場合はSUMIF、複数の条件をつけたい場合はSUMIFSを使いましょう。

　　=SUMIF(範囲, 条件, 合計範囲)　

例）売上データで「商品A」の売上合計を求める際場合（A列に商品名、B列に売上金額）：=SUMIF(A2:A10, “商品A”, B2:B10)

　　=SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, 条件2, …])

例）「商品A」の売上で、かつ売上が1000円以上の合計を求める場合（A列に商品名、B列に売上金額）：=SUMIFS(B2:B10, A2:A10, “商品A”, B2:B10, “>=1000”)

これによって、特定の条件による商品の売上数・入庫数などの合計数も簡単に算出できます。

IF関数

条件を設定して、処理を変更するための数式がIF関数です。一定の在庫数になると、セル内に表示される文字を変えるなどの条件付き処理が可能になります。

在庫数に応じて発注のタイミングを知らせる設定も可能なため、たとえば「在庫数が100以下」になったら「残り僅か」と表示する設定をしておけば、事前に発注漏れを防げるでしょう。

　　=IF(論理式, 真の場合の値, [偽の場合の値])

例）A1の値が100以上なら「在庫あり」、そうでなければ「残り僅か」と設定する場合：=IF(A1>=100, “在庫あり”, “残り僅か“)

VLOOKUP関数

VLOOKUP関数は、設定した条件に一致したデータを抽出するための数式で、入庫管理表や出荷管理表から、在庫管理表に各データを抽出する場合などに活用可能です。

たとえば別のシートから「エコバッグ」の在庫を引っ張り出したい場合、この関数を設定することで「エコバッグ」のデータを自動的に抽出できます。

常に簡単に必要なデータを把握できれば、入力時のミスや手間の削減につながります。

　　=VLOOKUP(検索値, 範囲, 列番号, [検索方法])

例）エコバックの値を、「商品一覧」シートのA列に商品名、B列に在庫数がある範囲で探し、2列目（B列）の値を取得する場合（完全一致）：=VLOOKUP(“エコバッグ”, 商品一覧!A:B, 2, FALSE)

さらに、A1に商品名を入力し、その商品に対応する在庫を表示する場合は以下のようにすると便利です。エコバッグを「エコバック」のように入力し、エラー（#N/A）になる手入力ミスのケースも防ぐことができます。

　　=VLOOKUP(A1, 商品一覧!A:B, 2, FALSE)

このように設定すると、A1の値を変更するだけで、対応する商品の在庫が自動で表示され、確認の手間も減らせます。

Excelの在庫管理表を使って運営するメリット・デメリット

在庫管理をExcelで運営するにはメリットがある反面、デメリットも考えられます。

以下でそれぞれを解説いたします。

Excel管理のメリット

まず、低コストで手軽にスタートできる点がメリットでしょう。

Excelを使うにはMicrosoftでソフトウェアの購入が必要ですが、パソコンに一度ダウンロードすれば無制限で利用できます。日常業務でパソコンを使用しているなら、すでに備わっているケースがほとんどなので新たな費用はかからないはずです。

そして入力程度であれば普段からExcelを使う人が多いので、基本的には誰でも操作に戸惑わず活用できるでしょう。

また、紙を使った手書きの在庫管理表に比べて紛失のリスクが低く、データの入力や編集も容易に行えます。

Excel管理のデメリット

在庫管理をExcelで行うデメリットは、複雑かつ膨大なデータへの対応が難しい点です。

まず、Excelの扱えるデータ量は、無限ではありません。Excel2019の場合、32ビット版で最大のデータ量は2GBです。複雑な処理を行う際はどうしても重くなりがちで、膨大なデータをもとに在庫管理を安定して行うには不向きといえるでしょう。

複数での同時操作や上書き保存も基本的に難しいため、リアルタイムに数か所で在庫管理を運用したい方、常に最新の情報を把握したい方にも向かないかもしれません。

また、紙に比べれば紛失のリスクは少ないものの、急に動かなくなったりデータが破損してしまったりといったリスクもつきものです。

在庫管理表を使って円滑に運用する4つのポイント

前述のとおり、Excelでの在庫管理にもメリットだけでなくデメリットがあります。上手に運用しなければ正確な在庫管理が叶わなくなるかもしれません。

在庫管理表を円滑に運用するためのポイントを以下でみていきましょう。

1.      運用ルールを策定

Excelでの在庫管理表の運営には、ルール策定が必須になります。まずはデータを入力する時間や頻度を決めて、わかりやすい入力マニュアルを作成していきましょう。

在庫管理表の担当者をあらかじめ決めておくのもポイントです。担当者を決めておかないと、「ほかの誰かがやってくれる」という心理が働いて更新が滞ってしまうかもしれません。

そしてExcelの知識がない人が誤った操作をしてデータが壊れることのないように、在庫管理表の編集権限にアクセスできる担当者も前もって決めるとよいでしょう。

2.      バックアップは必須

Excelを使った在庫管理表は業務効率化の効果が期待できる一方で、データ破損やパソコンそのものが使えなくなるといった万が一のトラブルに注意が必要です。

そのため、在庫管理のデータは必ずバックアップをとりましょう。パソコン本体が使えなくなると在庫管理表にアクセスできなくなるばかりか、データ復旧が困難です。

こまめにバックアップをとれば大きなトラブルを回避できるので、最低でも1週間に1度は心がけていきましょう。

3.      マクロやAIを上手に活用

Excelでの在庫管理は、商品の数が増えて作業量が多くなるとどうしても手間も増えるでしょう。

業務が追いつかなくなる場合、マクロを活用してExcelの操作を自動化してしまうと楽になります。繰り返し行う作業を記録して、常にワンクリックで実行できるような設定が叶います。

また、近年はAIを使った在庫管理表の作成・運営も注目されています。

AIに関数の作成を依頼したり、マクロの作成を依頼したりすることも有効な手段かもしれません。

4.      Excelではない方法でシステム管理を行う

人の手を介さずに、受注から出荷までの工程をシステムで管理する方法も有効です。実績多数で信用のおける在庫管理システムなら、出荷漏れや出荷指示の間違いによる出荷ミスを未然に防ぎ、安定した運用を継続していけるでしょう。

また、履歴の確認もスムーズに行えるため、万が一トラブルがあった際にもすぐに原因を追及し、改善を図れます。

高品質なシステムをお探しの方は、弊社のクラウド型流通統合システム「logiec（ロジーク）」の導入をぜひご検討ください。

まとめ

在庫管理はExcelを使うと、低コストで手軽にはじめられます。無料のテンプレートなら、必要に応じてカスタマイズするだけでも運用可能でしょう。

関数・マクロ・AIを活用することで、在庫管理業務の効率化を目指していくこともポイントです。

しかし、Excelで利用できるデータ量には制限があります。商品の数が増えたり、複雑な作業が必要になったりした場合はシステムによる在庫管理がおすすめでしょう。
また、在庫の増減があるたびに手作業をしなければいけない事に変わりはありません。

はぴロジでは、在庫管理にお悩みの方へ、在庫情報を一括で管理・更新できるシステム導入のお手伝いが可能です。在庫管理業務の効率化を検討している方は、お気軽にご相談ください。