在庫管理はエクセルでも可能！作り方や運用する際のポイントを解説！
目次
- エクセルで作れる在庫管理表は2種類！
- 単票タイプ
- 在庫移動表タイプ
- 在庫管理表をエクセルで作る方法
- テンプレートを取得する
- 計算式を組み込む
- マクロを組み込む
- 見やすくなるように装飾をする
- クラフトバンクが作成した在庫管理エクセルツール
- ①マスタ登録をする
- ②初回在庫を登録する
- ③入庫履歴・出庫履歴・在庫調整の入力
- ④カテゴリ集計で金額確認
- ⑤月次在庫・日次在庫
- 在庫管理をエクセルで行うメリット
- 導入コストが少ない
- 紙に比べて管理がしやすい
- 難しい操作が必要ない
- 在庫管理をエクセルで行うデメリット
- 管理規模が大きい現場には不向き
- 人為的ミスは防ぎにくい
- 容量が大きくなると動作が遅くなる
- 誤ったデータに上書きされるリスクがある
- 在庫管理をエクセルで運用する際のポイント
- 管理に必要な項目を網羅している管理表を利用する
- 管理ルールを明確にしておく
- 定期的にバックアップをとる
- 更新時は履歴を残しておく
- まとめ
紙で行う場合、煩雑さやリスクがある在庫管理。
手計算や複数人で共有できないなどの課題は、在庫管理をエクセルで行うことにより解決できます。
エクセルによる在庫管理には業務改善効果が期待できますが、注意しなければ不要なトラブルや手間の増加につながる可能性も。
そこで今回は、
- 在庫管理表の種類や作り方
- エクセルで在庫管理をするメリット・デメリット
- 運用のポイント
上記3つの項目について詳しく解説します。
【PR】在庫を効率的に＆正確に管理してコスト削減したいなら、クラフトバンクオフィスがおすすめです。
毎日誰かが在庫を見に行って個数をチェックしたり、現場で使った資材の管理ができずにどんぶり勘定になっていませんか？
クラフトバンクオフィスでは、職人や監督の在庫報告とマスタが連動するので、現場単位でスムーズに在庫管理や発注管理ができます。
クラフトバンクオフィスの詳細は、サービス資料をダウンロードしてご覧ください。
エクセルで作れる在庫管理表は2種類！
在庫管理表とは、仕入れた商品や材料などが現在どのぐらいあるか、いつ・入出庫したかを正確に把握・管理するための表です。
在庫管理表を使うことにより、在庫切れなどを防ぐことができるでしょう。
ここからは、エクセルで作れる在庫管理表を2種類解説します。
単票タイプ
単票タイプは、1つの商品に対し1つの在庫管理表を作成するかたちで「吊り下げタイプ」とも言われます。
入庫数や出庫数、在庫数、担当者、備考などさまざまな情報を記入できるため、1つの商品の詳細な情報が記入・把握できます。
また場合によっては、写真や図なども挿入できるでしょう。
シンプルであり、誰でも作りやすく見やすいことが特徴です。
ただし、商品数が多い場合は表が増え、管理が大変になります。
在庫移動表タイプ
在庫移動表タイプは、横軸に日付、縦軸に商品番号や商品名などを並べ、一覧で管理するかたちです。
複数商品の在庫数や入出庫数を1つの表で管理でき、日々の在庫変動をまとめて確認できます。
また、入力事項が少なく更新の手間もかからないでしょう。
ただし、単票タイプと違い、商品ごとの詳細な情報の入力や確認、管理はできません。
在庫管理表をエクセルで作る方法
エクセルで作れる2つの管理表のタイプを紹介しました。
ここからは、在庫管理を行うエクセルの表の作り方について解説します。
テンプレートを取得する
エクセル操作に慣れていない場合、管理表を一から作るのは一苦労。
そんな方には、テンプレートを取得する方法がおすすめです。
Web上に無料公開されている在庫管理表テンプレートをダウンロードすると良いでしょう。
ただし、自社に合った内容のテンプレートとは限らないため、多少自社用にカスタマイズする必要があります。
また、特殊な項目が多い場合は、一から作成した方が早いでしょう。
計算式を組み込む
テンプレートが自社に合わなかったり、エクセル操作に慣れていたりする場合は、エクセルの関数を使用して在庫管理表を作る方法もあります。
エクセルには便利な関数が400種類以上あり、一度設定すれば自動で集計してくれます。
そのため、在庫管理表を作る際に利用するとよいでしょう。
在庫管理表を作る際に便利な関数をいくつか紹介します。
- VLOOKUP関数
検索条件で指定したデータを取り出す関数です。
入庫管理や出庫管理などを別シートから、数値を在庫管理表に抽出し、その差で在庫数を算出するときなどに役立ちます。
計算ミスやシートをまたいだ入力ミスが減り、作業効率が上がるでしょう。
- SUMIF関数・SUMIFS関数
指定した条件に合ったものだけを足し算する関数です。
指定する条件が1つの場合はSUMIF関数、複数の場合はSUMIFS関数を使います。
- IF関数
設定した条件で処理をする関数です。例えば、在庫数が5以下になったときに、自動で「要発注」と表示させることができます。
- CEILING関数
基準値を決め、その倍数で最も近い値に切り上げる関数です。
例えば、箱単位や決められた数量単位でしか発注できない商品の在庫を管理するときに役立ちます。
- PRODUCT関数
複数の数値を掛け算する関数です。
｢単価×卸率×数量｣｢定価×卸率×数量｣など複数項目の掛け算をしたいときに役立ちます。
マクロを組み込む
関数だけでなく、エクセルのマクロを使い在庫管理表を作る方法もあります。
マクロを使うことで、日々行っているエクセルの定型作業を自動化できます。関数よりも自由度が高く、多くの作業を効率化できるでしょう。
マクロを使ったことがない方は「マクロの記録」機能から始めるのがおすすめ。
慣れたら「VBE（Visual Basic Editor）」も活用するとよいでしょう。
VBEはマクロをコンピューター言語で表現したもので難易度は上がりますが、マクロの記録内容を微調整することができます。
また、後ほど紹介するクラフトバンク総研の「在庫管理エクセルテンプレート」もマクロを使って作ってあります。
見やすくなるように装飾をする
注意点などを一目で把握するために装飾するのもおすすめです。
特に、在庫移動表タイプを使い商品数が増えれば、注意しても見落としが起こってしまう場合も。
商品を一つひとつ確認すると、時間がかかってしまいます。
関数やマクロだけでなく、エクセルには在庫管理で使える便利機能が存在。
「条件付き書式」機能を使えば、指定した条件を満たしたときにセルが色付けされます。
例えば「在庫数が5個以下になった場合、セルを赤くする」など指定すれば、発注のし忘れによる欠品を防止できるでしょう。
クラフトバンクが作成した在庫管理エクセルツール
クラフトバンク総研でも在庫管理エクセルツールを作成しました。
- マスタ登録
- 初回在庫登録
- 入庫履歴・出庫履歴・在庫調整の入力
- カテゴリ集計
- 月次在庫・日次在庫
のシートで在庫管理できるツールになります。
それぞれのシートでやることを紹介していきます。
①マスタ登録をする
まずは、在庫が発生する商品を「マスタ」シートに入力していきます。
ここで入力する項目は
- カテゴリ
- 品番
- 品名
- 単価
です。
マスタシート入力し終わったら、必ず左上のマスタ更新をクリックしてください。
更新することで、他のシートにマスタが反映されます。
②初回在庫を登録する
まずは基準とするために、初回の在庫を登録します。
マスタに入力したものがプルダウンで反映されていますので、プルダウンから入力してください。
③入庫履歴・出庫履歴・在庫調整の入力
実際に運営していく上では、入庫履歴・出庫履歴・在庫調整に入力していきましょう。
ここで入力した数字が
- カテゴリ集計
- 月次集計
- 日次在庫
に反映されます。
④カテゴリ集計で金額確認
カテゴリ集計シートでは、在庫の金額合計がカテゴリ別に集計されます。
⑤月次在庫・日次在庫
月次在庫・日次在庫シートでは、それぞれのシートに入力した数字が反映されて一覧で見える化されます。
商品ごとの在庫がひと目で分かるようになるので、「在庫があるのに発注しちゃってもったいなかったな…」といった思いをすることなども減ることでしょう。
在庫管理をエクセルで行うメリット
在庫管理をエクセルで行うメリットは、さまざまです。
ここからは、在庫管理をエクセルで行うメリットを3つ紹介します。
導入コストが少ない
1つ目は、導入コストが少ないことです。
専門の在庫管理システムもありますが、利用すると初期費用や運用費用がかかるため、導入しにくい会社もあるでしょう。
その点エクセルであれば、すでに利用している会社も多く追加で費用がかかることは少ないでしょう。
例え新規で購入したとしても、在庫管理システムよりは安く、他にもさまざまなシーンで活用できます。
また、紙で在庫管理するより手間がかからず、人件費の削減も期待できます。
紙に比べて管理がしやすい
2つ目は、紙に比べ管理しやすいことです。
エクセルの在庫管理表であれば更新がしやすく、紙と違い数値を手計算する必要もありません。
また、保管場所をとらず紛失などのリスクを抑えられるでしょう。
Googleドライブなどクラウド上で保管すれば、バックアップを取ったり、複数人で共有しやすい点も魅力的です。
難しい操作が必要ない
3つ目は、難しい操作が必要ないことです。
エクセルは日常業務で使用する方も多く、親しみやすいでしょう。
関数やマクロは在庫管理表を作る段階で一度設定してしまえば、毎回難しい操作は必要ありません。
エクセル操作に慣れていない場合、始めは戸惑うかもしれませんが、使っていれば業務スピードが上がるでしょう。
在庫管理をエクセルで行うデメリット
エクセルによる在庫管理には、メリットがある一方でデメリットも存在します。
ここからは、在庫管理をエクセルで行うデメリットを解説します。
管理規模が大きい現場には不向き
1つ目のデメリットは、規模の大きい現場に不向きなことです。
エクセルでの在庫管理は、商品数や部署、拠点数が多い会社にはあまりおすすめできません。
クラウドへの保存や「ブックの共有」機能を使えば、複数人・複数拠点で共有し更新が可能です。
ただ、セルの結合やシートの削除、ピボットテーブルなどの一部機能の利用が制限されます。
また、商品数が多い場合は、該当商品を見つけるのに時間がかかるでしょう。
一目で全体を把握することが難しく、操作性や検索性、見読性が低下します。
人為的ミスは防ぎにくい
2つ目のデメリットは、人為的ミスを防ぎにくいことです。
在庫管理をエクセルで行う場合、入庫数や出庫数が自動入力されるわけではなく、人が行います。
どんなに注意しても、人が行う以上ミスが出てしまうもの。
もしかすると、50個入庫したときに500個と桁数を間違えて入力したり、検品数を間違えたりする可能性もあるでしょう。
このようなミスをエクセルの機能で防ぐのは困難であるため、ダブルチェックなど運用でカバーする方法を考えましょう。
容量が大きくなると動作が遅くなる
3つ目のデメリットは、容量が大きくなると動作が遅くなることです。
商品数や保存データが多くなった場合、エクセルの処理や操作が遅くなります。
ファイルを開くのに時間がかかったり、パソコンがフリーズしたりすると業務効率が下がりストレスを感じることもあるでしょう。
そのため、商品数や在庫管理表のエクセルを利用する部署・拠点が多い場合は、注意が必要。また、保存データ量が多くなる場合、期間まどで区切り、エクセルをわけるとよいでしょう。
誤ったデータに上書きされるリスクがある
4つ目のデメリットは、誤ったデータに上書きされるリスクがあることです。
エクセルの在庫管理表は、手軽に操作・更新できることがメリット。
その反面、間違ったデータも入力しやすく、消去もしやすいと言えます。
上書きをすれば編集前のデータは残らず、在庫トラブルや再確認の手間につながるでしょう。
エクセルには「変更履歴を残す」機能があるので「どのユーザーが・いつ・どのように内容を変更したのか」記録できますが、編集前に戻すことは不可能です。
また、抜本的な問題解決を図るために原因を追究するのも難しいでしょう。
在庫管理をエクセルで運用する際のポイント
在庫管理をエクセルで行う際には、注意点があります。
正しく運用しなければ在庫トラブルや不要な手間につながるでしょう。
ここからは、在庫管理をエクセルで運用する際のポイントを解説します。
管理に必要な項目を網羅している管理表を利用する
自社で在庫管理に必要な項目を、エクセルの管理表に網羅する必要があります。
よく使われる項目は、下記の通りです。
- 何が（商品名、商品コード）
- いくつ（数量）
- いつ（在庫がある日、入庫日、出庫日）
- どこに（保管場所）
これ以外にも、自社の運用に必要な項目を検討する必要があります。
例えば、商品に利用期限などがある場合は、その期限も管理する必要があるでしょう。
在庫管理表を作った後に改修すると、関数やマクロを組み直す手間が発生するので、作成前にしっかり検討するのがおすすめです。
管理ルールを明確にしておく
「誰が・いつ・どのように更新するか」などの管理ルールを明確にしましょう。
ルールが明確でなければ、人によって更新がバラけてしまいます。
また、人任せになり更新する方がいないという事態に陥る場合も。
その結果、在庫管理表に間違いがあり、トラブルにつながる可能性があります。
「商品を検品した人が、その直後に更新する」「受発注書の取り交わしを行った営業担当者が、書類の作成と同時に更新する」など、明確なルールを決めましょう。
日付や数字などの書き方や、備考に残さなければいけないことなども、ルール化しましょう。
あまり複雑なルールにすると手間が増えるため、正確性と利便性のバランスを考えることも大切です。
定期的にバックアップをとる
在庫管理をするエクセルの定期的なバックアップをとることも大切です。
エクセルを保存しているパソコンやエクセル自体が破損したり、なくなってしまったりする可能性があります。
普及できたとしても時間がかかるでしょう。
定期的なバックアップデータがあれば、被害を抑えられます。
バックアップも「誰が・いつ取るのか」をルール化するのがおすすめ。
いつバックアップしたデータかわかるように、ファイル名に日付を入れるなどの工夫もしましょう。
更新時は履歴を残しておく
エクセルで在庫管理表を更新する際は、履歴を残すようにしましょう。
履歴があれば、在庫管理表を更新したのか否かが判断できます。
わからなくなってしまった場合は、一から数え直す手間が発生します。
また、誰が・いつ・何を更新したかわかれば、管理もしやすいでしょう。
まとめ
今回は、在庫管理表の種類や作り方、エクセルで在庫管理をするメリット・デメリット、運用のポイントについて解説しました。
在庫管理をエクセルでする場合、単票タイプと在庫移動票タイプの2種類があります。
作る際は、テンプレートを取得するか、関数やマクロを使い作成し、わかりやすくなる装飾などの工夫をするとよいでしょう。
導入コストが少なく、管理や操作がしやすい反面、規模が大きい場合は不向きで、人為的なミスが起こる場合もあります。
そのため、管理ルールを明確にし、定期的なバックアップや変更履歴を残しながらの運用がおすすめです。
在庫を効率的に＆正確に管理してコスト削減したいなら、クラフトバンクオフィスがおすすめです。
毎日誰かが在庫を見に行って個数をチェックしたり、現場で使った資材の管理ができずにどんぶり勘定になっていませんか？
クラフトバンクオフィスでは、職人や監督の在庫報告とマスタが連動するので、現場単位でスムーズに在庫管理や発注管理ができます。
クラフトバンクオフィスの詳細は、サービス資料をダウンロードしてご覧ください。