ECサイトの運営において、日々担当者が苦労している大変な作業が在庫管理です。小規模のECサイトであれば、エクセルで独自の在庫管理表を作成して運用されている店舗もあるかと思います。
しかし、日々煩雑な在庫管理に追われている中で、もっとエクセルの便利な機能を使いこなしたいと感じている方も多いのではないでしょうか。
そこでこの記事では、エクセルで行う在庫管理について、そのメリット・デメリットから、在庫管理表作成の基本や在庫管理表をタイプ別にご紹介します。また、エクセルで使用する主な関数についても解説していきますので、この記事を読めば、エクセルの関数にはどんな機能があるのかも理解できるようになります！
エクセルで在庫管理するメリットとデメリット
まずはエクセルで在庫管理をする際のメリットとデメリットについて解説していきます。
メリット
費用がかからない
エクセルで在庫管理する最大のメリットは、やはりコストがかからないことです。一般的な企業では、マイクロソフト社のOffice製品であるエクセルやワードは必ずといって良いほど既に購入されています。また、最近ではGoogle社版のエクセルとも言える無料で使える「Googleスプレッドシート」を活用する企業も増えてきました。
誰でも扱いやすい
2つ目のメリットとしては、「操作性が良いこと」があげられます。就職や転職をする上で、エクセルやワードが使用できることは今や常識となっています。したがって、誰でも簡単に操作ができることもメリットになるのではないでしょうか。
デメリット
大規模な管理には困難
エクセルで在庫管理するデメリットとして第一にあげられるのは、大規模な管理には不向きな点です。複数拠点における膨大な在庫を手作業で管理するのはとても無理があります。また、管理者が複数存在する場合、エクセルは同時での操作及び保存ができないため、在庫の管理が大規模になってしまう場合は運用には不向きです。
データ容量に限界がある
データが膨大になった場合、大容量のデータを管理するのにはエクセルは不向きであると言えます。なぜなら、データが膨大となると動作が重くなり、ファイルを開く際や保存するときにフリーズしてしまい、最悪ファイルが壊れてしまう場合も想定されるからです。
多忙な業務をこなさなければならない時に、なかなかファイルが保存できないのはストレスにもなります。したがって、取り扱うデータが膨大な場合、エクセルでの在庫管理は無理があると言えるでしょう。
商品などを扱う上で欠かせない「在庫管理表」とは？
次に在庫管理に欠かせない在庫管理表の基本を説明します。
在庫管理表とは自社で保管している商品について、その数や入荷日などその商品に関するあらゆる情報を記録しておく表のことです。
ここでは在庫管理表を使用する目的や、その重要性について解説します。
在庫の数量や移動した日時を記録する表のこと
在庫管理表は倉庫内で保管している商品の数量、入荷やロケーション移動などの移動履歴、賞味期限や品質などを記録する表です。
これらの情報の管理をおろそかにしていると大事な商品を紛失してしまったり、いつの間にか商品の在庫が尽きてしまったといったことにもなりかねません。
そのため、在庫管理表の入力と保存はこまめに行い、入力漏れなどで余計な確認作業を発生させないように注意しましょう。
「在庫の適正管理」や「無駄の削減」には在庫管理表が重要
在庫は会社の資産であるため、適正に管理することは資産を適正に管理することに繋がります。
在庫を過不足なく適正に管理することで、欠品での販売機会の損失を防いだり、過剰在庫によるスペースの圧迫を避けたりすることが可能です。
そのため、在庫管理表を用いて正しい在庫数を管理することで、管理における無駄を省けます。
また、在庫管理表を作成しておき、誰でも閲覧できる状況にしておくことで、従業員同士の確認の手間も省くことも可能です。
在庫管理表をエクセルで運用する方法
在庫管理表作成の重要性をお伝えしましたが、実際にエクセルで在庫管理表を作成する場合、どのような形で作ったらいいのでしょうか？
在庫管理表をエクセルで運用する方法は、大きく分けて2つのタイプがあります。
- 単票タイプ
- 在庫移動表タイプ
それぞれ解説していきます。
単票タイプ｜簡単に作れるのがメリット
単票タイプとは、古くから使用されている在庫管理表の形式です。一種類の商品に対して在庫管理表を専用に使用するタイプで、元々在庫を保管する棚の横に紙をぶら下げる在庫管理用紙をそのままエクセルにしていることから、吊り下げタイプとあらわすこともあります。
商品名や品番を表のトップに入力し、残高、担当者、入庫／出庫などの項目を設けることで、誰が、いつ、何を、何個入出庫したかを管理できます。
デメリットとしては、複数種類の製品の在庫を管理できないことです。
紙での吊り下げ票での運用は、行が足りなくなった際は2枚目の表を使えばよいですが、エクセルの場合は行を挿入しなければなりません。
もし、スタッフの中にエクセルの操作に慣れない人がいた場合は、エクセルシートの計算式を消去してしまうなど、運用に問題をきたすケースも想定されます。
単票タイプの作り方
単票タイプの作り方について、画像を交えながら詳しく解説していきます。
まずは、エクセルを開き、A1セルからA列に「品番」「商品名」「日時」「繰越残高」を順番に入力しましょう。
次に、A列に日付を入力します。日付を上から3つほど入力したあとで、日付をハイライトしてマウスで引っ張ると、日付が自動入力されます。
B1セルに品番、B2セルに商品名をそれぞれ入力します。
B3〜F3セルに「入庫」「出庫」「残高」「担当者」「備考」と順番に入力します。
続けて、D4セルに先月末の在庫数を入力しましょう。
D5セルに関数（=D4+B5-C5）（繰越残高+翌日入庫-出庫）を入力します。
D5セルの関数をD6セル以降のD列にコピーし、すべての日付に関数を適用します。
格子をつけて、品番と商品名を中央揃えにして見た目を整えましょう。
在庫管理表が完成したので、あとは、入出庫に応じて入力するだけです。
残高は自動入力されるので、手動で入力しないようにしてください。
在庫移動表タイプ｜在庫を把握しやすいのがメリット
この在庫管理表は、商品名や品番を縦軸に、在庫数を横軸に入力します。単票タイプと違って、複数の製品の在庫を一元管理することが目的なので、全体の在庫の状況を把握するために使用されます。
入出庫による在庫の増減を、在庫数に反映させるための関数を入れることで、自動計算で在庫数を表示できます。しかし、単票タイプのように備考欄や担当者の入力ができないというデメリットもあります。
商品の数が多い場合は、エクセル上で膨大なデータをとり扱うことになります。エクセルは在庫数などの計算は自動でやってくれますが、入力自体は人が操作するため、入力ミスによるトラブルの原因になりやすいなどのリスクがあります。
また、この表だけでは在庫を完全に管理できません。なぜなら、このタイプの管理表は在庫の一貫性を把握することが目的のため、入出庫の在庫数の詳細までを管理することができないからです。
したがって在庫移動表タイプは、在庫管理としての要件を全て満たしている管理表とは言えないこともデメリットのひとつと言えます。
在庫移動票タイプの作り方
在庫移動票タイプの作り方を画像つきで解説します。在庫移動票タイプは、単票タイプと異なり、横長の表になるのが特徴です。
まずは、A1〜C1セルに「品番」「カラー」「サイズ」を順番に入力します。
次に、D1を空白にし、E1とF1には「合計」「繰越残高」を入力しましょう。
G1から同じ行に日付を入力していきます。3つほど入力し、あとは日付をハイライトしてマウスで引っ張り、日付を自動入力しましょう。
品番・カラー・サイズに実際の商品情報を入力します。次の手順のことを考え、1つのサイズで3行分確保して入力してください。同じ品番で複数のカラー・サイズがある場合は、セルを縦に結合して、表を見やすく整えましょう。
D列には、サイズごとにそれぞれ「入庫」「出庫」「残高」を入力します。1セット入力したら、ハイライトしてマウスで引っ張り、自動入力しましょう。
F4のように「残高」と「繰越残高」が交わるセルには、先月の在庫数をそれぞれ入力します。
G列に1日の残高が関数で反映されるよう式（F4+G2-G3）を入力します。
ほかの品番やサイズにも同様に、繰越残高+当日入庫-当日出庫の式を反映させましょう。G4の関数をほかのセルにコピーするだけで、簡単に反映できます。
横スクロールしたときに項目が見やすいよう、「繰越残高」から左のウィンドウ枠を固定します。
これで在庫移動票タイプの作成は完了です。
あとは、入出庫を日々入力すれば、残高が自動で反映されます。
残高を手動で入力しないように注意しながら、在庫管理を行いましょう。
在庫管理表に必要な項目とは？
上記でエクセルでの在庫管理表の作成方法をご覧いただいたので、おおよそのイメージはついたかと思いますが、在庫を管理する上で必要となるおもな項目は以下のとおりです。
- 商品名
- 商品番号
- 入庫日
- 入庫数
- 出庫日
- 出庫数
- 繰越数
- 在庫数
- 在庫区分
- 賞味期限
上記は必要項目の例ですが、扱っている商品や事業形態によって必要項目は異なるため、自社で管理しやすいようカスタマイズしてください。
項目を配置する際は、在庫管理表を閲覧した人が理解しやすいよう項目の順番も工夫する必要があります。
在庫管理表で使うと便利なエクセルの関数は？
エクセルの関数とは、あらかじめ計算式をエクセル上に入力しておくことで、数値を入力すれば自動で計算される仕組みを意味します。エクセル本来の機能を十分に活用するためには、この「関数」をうまく使いこなす必要があります。
関数は、一度設定してしまえばあとは自動で計算を行ってくれるため、まさに在庫管理に使用するにはもってこいの機能です。
数ある関数の中から、必要な関数を選定して在庫管理の運用に使用するためには、どんな関数があるのかを理解する必要があります。
ここでは、在庫管理表の作成に便利な関数を解説していきます。
IF関数
条件を設定することによって処理を変更できる関数です。たとえば、在庫数が100個以上の商品は｢在庫潤沢｣と表示するとします。その場合、IF関数で条件を設定することで100個よりも在庫が少ない場合、商品のセルには｢在庫少｣と表示させることも可能になります。
VLOOKUP関数
検索条件に一致したデータを抽出する際に使用する関数です。入力ミスが減り、作業効率が上がるメリットがあります。在庫管理表、データ入力、一覧表、集計表などの作成に便利な関数です。
SUMIF・SUMIFS関数
足し算の機能を持ったSUM関数が発展した関数です。指定した条件のものだけチョイスして合計できます。
SUMIF関数は、設定できる条件が１つで、SUMIFS関数は複数の条件を設定できます。
ROUND関数
小数点の数値を四捨五入して、指定した条件の桁数に自動で表示できる関数です。ROUND関数は、以下の2つの種類に分けられます。
- ROUNDUP
- ROUNDDOWN
指定した桁数を切り上げるのがROUNDUP関数で、切り捨てるのがROUNDDOWN関数となります。これらは、指定したい桁で切捨てたり切上げたりできるため、セールの価格などを算出する場合に使用できます。
MOD関数
MOD関数は、割り算の余りを計算することができます。たとえば、7の倍数の日は25％OFFなど、イベントを実施する日に合わせてMOD関数をエクセルへ組みこめば、自動でイベントの日を割り出せるようになります。
MID関数
MID関数は、商品番号、製品コードなどの「コードの一部」を抽出し、分析をしたいときに使用します。
LEFT・RIGHT関数
LEFT関数は、入力された文字列の左側（先頭）から、指定した文字数を取り出せます。RIGHT関数は、セルに入力した文字列の右側（最後）から、指定した文字数を取り出せます。この関数は、開始位置を指定しなくても右や左から指定した文字数のみを抽出することが可能です。
PRODUCT関数
複数の数値をまとめて掛け合わせられる関数です。たとえば、｢定価×数量×卸率｣、｢単価×数量×卸率｣等、いくつかの計算をしたい際に有効な関数です。
CEILING関数
在庫管理で利用する際、ロッドが箱単位でのみ発注ができない商品などを管理するときに役立ちます。
FLOOR関数
ケース単位での発注をするときに、端数分を単品で発注する場合の「発注ケース数」を求めたいときに使用する関数です。
QUOTIENT関数
QUOTIENT関数は、商の余り(小数部)を切り捨てる必要があるときに使用する関数です。日ごとの売上単価を算出するときに使用します。
TRIM関数
在庫管理表の単語と単語の間のスペースを1つ残して、不要なスペースをすべて削除する関数です。管理表を見やすくしたいときに使用します。
以上、ご自身で一から在庫管理表を作成する際に知っておくと便利な関数をご紹介しました。
インターネット上に公開されているテンプレートをもとにカスタマイズすることもできるため、関数の入力などが難しいと感じた場合はそちらも試してみてください。
エクセルで作成した在庫管理表の運用ポイント
エクセルで作成した在庫管理表は、運用の方法を間違えると在庫管理の正確性に支障をきたします。そのような事態に陥らないために、運用のポイントを3つご紹介するので、ぜひ取り入れてみてください。
運用ルールを策定する
エクセルで在庫管理表を運用する際に最も大切なポイントは、運用ルールを策定することです。具体的には、以下の項目を明確にしておきましょう。
- 在庫管理表にアクセスできるユーザー
- 入力を行う担当者
- 入力する時間帯や頻度
- 入力方法のマニュアル
とくに、エクセルの在庫管理表を入力する担当者を決めておくことが大切です。担当者を決めずに運用していると「誰かがやってくれるだろう」という甘い考えが出てしまい、結果的に誰も入力していないといった事態にもなりかねません。
また、在庫管理表を誰でも編集できるようにしてしまうのもよくありません。エクセルの操作に慣れていない従業員が、セルに入力していた関数を崩してしまう恐れもあります。エクレスの在庫管理表を運用する際は、運用ルールを策定し、まずは従業員に周知するところから始めましょう。
バックアップを取っておく
エクセルのソフトを利用して、在庫管理表を運用している場合は、バックアップを取っておくことが重要です。エクセルは、ファイルを保存してあるパソコン本体が使えなくなると、ファイルにアクセスできなくなります。
ファイルにアクセスできなくなると、しばらくは在庫管理を行えません。
また、復旧ができないという最悪の事態になれば、倉庫にあるすべての在庫を数えなおす羽目にもなるでしょう。とくに、5年前以上の古いパソコンを使用している場合は、突然電源がつかなくなることもあります。
1週間に1回はバックアップを取るなど、バックアップを取る頻度を定めておき、在庫管理表の運用を円滑に行えるようにしましょう。
Webブラウザ版のエクセルを使う
エクセルには、パソコンにインストールするソフトとは別に、ブラウザ版が存在します。それは、マイクロソフトがWebブラウザ版にて無料で提供している「マイクロソフト・エクセルオンライン」というサービスです。
ブラウザ版のエクセルは、ソフト版のエクセルと基本機能がほとんど変わらないため、在庫管理表の運用をこなすには十分といえるでしょう。
また、エクセルのデータはクラウド上に保存されるため、パソコンの電源がつかなくなってもファイルにアクセスできなくなる心配がありません。
さらに、ブラウザ版のエクセルは、インターネットに接続できる環境さえあれば、どこからでもアクセスできます。社内のパソコンからでなければアクセスできないソフト版のエクセルよりも、運用の自由度は格段に上がることでしょう。
在庫管理を効率化するには？
在庫管理をエクセルで行う場合、費用がかからず使いやすい反面、事業が成長し、店舗数、商品数や受注量が増えていくと、在庫管理も複雑になり、ヒューマンエラーの発生も懸念されます。
在庫管理がうまくいっていないと機会損失や在庫リスクにもつながります。そこでおすすめしたいのが、在庫連携システムの導入です。
在庫連携システムは、複数店舗の在庫を自動連携できたり、属人化のリスクや作業ミスの軽減ができたりといった多くのメリットがあります。様々なシステムがあるため、一度システム導入を検討されることをお勧めします。
なお、在庫リスクについては以下の記事で詳しく解説しています。こちらもご参考ください。
まとめ｜煩雑なエクセル管理から卒業してシステム化をしよう！
エクセルでの在庫管理は、コストがかからなかったり誰でも操作が簡単であったりという良い面もあります。しかし、複数拠点における大規模な在庫管理を必要とする場合、データ量が多い事が原因でエクセルの動作が重くなると、管理するのはとても困難です。
管理する在庫がある程度の規模になったら、在庫連携システムの導入を検討されてはいかがでしょうか。在庫連携システムは、複数店舗の在庫を自動連携し、属人化のリスクや作業ミスの軽減ができます。
エクセルでの在庫管理表の運用に限界を感じている方は、この記事をきっかけにシステム化を検討されてみてはいかがでしょうか。
在庫管理システムを導入するなら「ネクストエンジン」！
在庫管理システムを導入する際は、EC一元管理システム「ネクストエンジン」もぜひご検討ください。
ネクストエンジンなら在庫管理の効率化・自動化を実現でき、できた時間でよりクリエイティブな業務に注力することができます。
実際にネクストエンジンを導入された事業者様からは「以前は欠品ラッシュを起こしてしまっていたが、EC店舗とリアル店舗での在庫のズレがなくなり、余裕ができるようになった」とのお声をいただいています。
在庫管理に関する資料は以下から無料でダウンロードできます。ご興味のある方はぜひチェックしてみてください。
