エクセルで在庫管理システムを自作する方法！問題点と構築時のポイントを解説
こんにちは。Wakka Inc.メディア編集部です。
在庫管理システムの必要性を感じているものの、導入コストがネックになるケースが多いでしょう。なかには、使い慣れたエクセルで自作したいとお考えの方も多いはず。
そこで本記事では、エクセルで在庫管理システムを自作する方法とその問題点について解説します。
なお、記事の後半では在庫管理システムを自作する際の注意点や要点をまとめています。
これからシステムの自作に取り組もうとお考えの方は、ぜひ最後までご覧ください。
「システム開発ハンドブックvol.2 在庫管理システム」では、コストと販売機会の効率化をはかるための在庫管理について、システムの選び方や導入時の注意点などを解説しています。ぜひ、あわせてご確認ください。
自社の在庫管理を最適化したい、在庫管理についてシステム化を計画している方
ぜひ下記の資料から最適なシステムの選び方もご確認ください。
【基礎知識】在庫管理システムとは？
在庫管理システムとは、商品の入出庫など、倉庫や店舗における在庫の出入りを記録し、商品の在庫状況を正確に把握するためのシステムを指します。
自社で商品を製造・販売するためには、欠品や余剰在庫が発生しないよう適正な在庫量を常に維持管理しておくことが重要です。
しかし、人が手作業で在庫を管理していると、作業ミスや在庫数のズレが発生し、業務負担が大きくなりがちです。
在庫管理システムを導入すれば業務の自動化が可能になり、作業ミスも防止できます。
そのため、業務負担の軽減や業務効率化の手段として注目されています。
エクセルを使った在庫管理システムの自作方法
在庫管理システムを構築する手軽な方法として、エクセルを利用した在庫管理表を作成する方法があります。
本章では、エクセルを使って在庫管理システムを自作する方法について見ていきましょう。
無料のテンプレートを使う
エクセルは多くの人が日常的に業務で使用しているツールなので、簡易的な在庫管理システムであれば手軽に自作できます。
しかし、手軽とはいっても1からすべて自作するとなると大変でしょう。
そこで、無料で使える在庫管理表のテンプレートを使うのがおすすめです。
テンプレートファイルは、インターネット上で様々な種類が無料で公開されているので、使いやすそうなものをダウンロードしてみましょう。
ダウンロードしたテンプレートをベースに、自社の業務に合うようにカスタマイズすれば、時間と労力をあまりかけずに自作できます。
無料のテンプレートを利用することで、導入にかかるコストも削減できるでしょう。
エクセルの関数を使う
テンプレートをダウンロードしたら、エクセルの関数を活用して、自社の業務に適した在庫管理ができるようにテンプレートをカスタマイズしましょう。
在庫管理システムというと大がかりなイメージになってしまいますが、基本的には在庫量や金額を計算するのが中心です。
そのため、在庫管理の仕組みを作るには、簡単な演算や集計関数が使えれば十分でしょう。
エクセルでよく使用するのは、次のような関数です。
- 演算（加減乗算）関数
- IF関数
- SUMIF関数
- VLOOKUP関数
1つずつ簡単に見ていきましょう。
演算（加減乗算）関数
在庫管理表に入力された数値をもとに計算処理をするには、演算関数が多く使われます。
例えば、以下の場合に演算関数が効果的です。
- 入庫欄に入力された数値を在庫数に加算し、入庫後の在庫数を求める
- 出庫欄に入力された数値を在庫数から減算し、出庫後の在庫数を求める
- 在庫量を金額で評価するために、商品の原価と在庫数を乗算し、在庫金額を求める
在庫管理に必要なほとんどの計算は、簡単な演算関数だけで実装できるでしょう。
IF関数
IF関数は条件式を記述する関数です。
条件式を記述し、条件を満たしたときと満たしていないときで、実行する処理を分岐させることができます。
例えば、安全在庫量の基準値を設定しておき、在庫数が安全在庫数を下回った場合に警告メッセージを出すといったケースで使えるでしょう。
SUMIF関数
SUMIF関数は、指定した条件を満たした場合のみ、選択範囲内の数値を合計する関数です。
例えば、入庫と出庫の区分を設けて数量項目を同じ列に配置している場合、SUMIF関数を使えば入庫数の合計、出庫数の合計をそれぞれ算出できます。
VLOOKUP関数
VLOOKUP関数では、検索条件を指定し、設定された検索範囲の中から条件に該当するデータを抽出できます。
例えば、商品検索画面を別シートに作成し、画面から指定された商品コードをもとに在庫管理表から商品情報を呼び出して表示するといったことが可能です。
エクセルのマクロを使う
関数では複雑になってしまったり、実現が難しかったりする処理も、エクセルのマクロを使用することでシンプルに実現できることがあります。
関数はエクセルシートのセルに埋め込まれ、関数が参照しているセルの値が更新されるたびに再計算されます。
そのため、関数を駆使した複雑な処理を大量に記述していると、エクセル自体が重くなって動きが悪くなることも珍しくありません。
しかし、マクロの場合は呼び出す操作をしたときだけ処理を実行するため、エクセル自体が重くなるといった影響はありません。
マクロは、簡単な処理ならレコーダーの機能を使って、操作を記録する方法で誰でも手軽に作成できます。
複雑な処理をプログラミングすることも可能ですが、その場合はVBAというプログラミング言語のスキルが必要です。
エクセルで在庫管理システムを自作するメリット
本章では、エクセルを使って在庫管理システムを自作するメリットについてお伝えします。
コストがかからない
自社でWindowsパソコンを導入していれば、Microsoft社のOffice製品がインストールされており、日常業務で利用されている可能性が高いでしょう。
その場合は、すでにパソコンにインストールされているエクセルで在庫管理システムを自作できるため、追加の導入コストがかかりません。
また、日常的にエクセルを業務で使用している人であれば、基本的な操作方法は知っています。
そのため、教育コストをかけることなく在庫管理システムを自作できるでしょう。
使い慣れたソフトのため定着しやすい
前述したように、多くの企業ではWindowsパソコンを導入しており、ほとんどの人が日常業務でエクセルを利用しています。
そのため、エクセルを使い慣れている人も多く、在庫管理システムを導入する際の心理的なハードルが低いでしょう。
使い慣れたソフトであれば使用するのに抵抗も少ないため、管理表の導入・運用が現場に定着しやすいです。
手早く作れて使いながら手軽に修正できる
エクセルを使用した在庫管理システムなら、高いITスキルがなくても手早く構築できます。
とりあえず作ってみて、使いながら改善していくことも可能です。
仕様変更や改修をするのにも高度なスキルは必要ないため、運用しながら課題を見つけ、徐々にブラッシュアップしていけるのもメリットです。
エクセルで自作した在庫管理システムには限界がある！
エクセルで自作した在庫管理システムは、手早く作れてすぐに運用できるのがメリットですが、使い続けているとやがて限界がやってきます。
では、どのようなところで限界が訪れるのでしょうか。
本章では、エクセルで自作した在庫管理システムの限界について解説します。
データの保存量に限界がある
在庫管理システムとしてデータを蓄積するのであれば、データは一元管理されているべきです。
しかし、エクセルでは1シートに保存できるデータ量が限られています。
エクセルの1シートに保存できる行数は1,048,576行（エクセル2003以下のバージョンでは65,536行）です。
保存できる行数の上限に達してしまうと、別のシートに保存しなければなりません。
シートが分かれているのでは、データが一元管理されている状態とは言えません。
また、関数やマクロの内容もそのままでは使えない可能性があるため、1シートに保存できる行数の上限が事実上の限界と言ってもいいでしょう。
リアルタイムに更新できない
エクセルでは、同じファイルを複数の担当者が同時に更新できません。
そのため、在庫データをリアルタイムで反映させるのが困難です。
ファイルを共有設定すれば同時に更新できなくはないのですが、共有設定にすると制限される機能が出てきます。
機能が制限されることで、不便に感じることも出てくるでしょう。
また、複数人で同時編集していると、
- 編集内容が正しく反映されないことがある
- ファイルが破損しやすい
といったリスクも出てきます。
複数の拠点で利用する場合などは、そもそも同じファイルを同時編集するのが難しいでしょう。
在庫データがリアルタイムに更新できないと、受注時に在庫を照会して引き当てるといった業務がリアルタイムに実行できません。
そうなってくると、業務の効率は著しく低下してしまいます。
負荷が高くなると処理が遅くなる
そもそもエクセルは、在庫管理専用のデータベースシステムではありません。
通常の利用時でも処理が速いとは言えませんが、データ量が増えたり同時に作業する人が増えたりすると、負荷が高くなってさらに処理が遅くなります。
また、大量のデータに対して関数を埋め込むと、何か操作をするたびに関数が再計算されます。
そのため、データの更新に非常に時間がかかってしまうこともあるでしょう。
エクセルを在庫管理に利用するなら
- データが大量になりすぎない
- 多くの関数を埋め込みすぎない
といった点に注意して、快適に操作できる範囲にとどめておくのが良いでしょう。
そうしないと、処理が重くて業務の効率が一気に低下することになりかねません。
複雑になるとメンテナンスが困難
エクセルを利用した在庫管理システムは、手早く自作できて運用しながら手軽に修正できるのがメリットでした。
しかし、手軽に修正できるために突貫工事でどんどん改修されていくケースが多いようです。
はじめから一貫した設計の考え方のもとに改修されていれば問題ありません。
しかし、修正が必要になったときにその場の思いつきで手軽に修正を重ねてしまうと、当初の設計の考え方から徐々にずれて、処理の整合性がとれない複雑な状態になっていきます。
何度も修正や機能追加を重ねてきたシステムは、あとから見ても修正した当時の意図が理解できないことも多く、次第にメンテナンスが困難になっていきます。
メンテナンスが困難な状態では、万が一トラブルが発生して修正が必要になったときに、誰も対応できないといったことになりかねません。
ファイルが破損するリスクがある
エクセルで管理するデータ量が増え、同時に編集することが多くなってくると、ファイルが破損するリスクも高くなります。
個人のパソコンに保存しているファイルではなく、ネットワーク上で複数人が利用しているファイルなので、なおさら破損リスクは高いでしょう。
エクセルで作成した在庫管理システムは、1つのエクセルファイルにすべての機能やデータが保存されています。
そのため、ファイルが破損してしまうと業務に多大な影響が出るでしょう。
エクセルの在庫管理にはこうした限界が存在するため、取り扱いには注意が必要です。
より本格的に在庫を管理する場合は、エクセル以外の方法でシステムを構築することをおすすめします。
エクセル以外で在庫管理システムを自作する方法
エクセルを利用した在庫管理システムのメリットと限界について解説してきました。
実際のところ、エクセルの在庫管理システムが快適に利用できるのは、
- 1シートのデータ量（商品点数）1万件程度
- 利用人数5名以下
- 営業拠点1か所
といったところが目安でしょう。
では、エクセルの在庫管理システムを快適に利用するのが難しい場合は、どうすれば良いのでしょうか。
本章では、エクセル以外で在庫管理システムを自作する方法について解説します。
Accessで自作する
Accessは、エクセルと同じくMicrosoft社のOffice製品のラインナップに含まれるソフトウェアです。
簡易版ではありますがデータベースソフトに分類されます。
表計算ソフトのエクセルと比べれば、データベースソフトであるAccessの方が、より強固なシステムが構築できるでしょう。
データベースソフトは、データを保存して取り扱うのが目的です。
そのため、データが大量になってもデータを扱う操作が遅くなることなく、安全に利用できるように設計されています。
Accessはデータベースの機能がメインですが、他にもデータベースを扱うための便利な機能がついています。
そのため、エクセルよりも本格的で使い勝手の良い仕組みづくりが可能です。
Accessに搭載されている主な機能は次のとおり。
- テーブル（表）
- クエリー
- フォーム
- レポート
- マクロ
それぞれ簡単に紹介しておきましょう。
テーブル
テーブルとは、データを保存する器のことを指します。
データベースソフトの本体にあたるメイン機能と言えるのがテーブルです。
在庫管理システムでは、商品情報や在庫情報をテーブルとして定義し、さらにテーブルそれぞれに持つ項目も定義します。
クエリー
クエリーとは、データベースに定義されたテーブルに対して、問い合わせを行う機能です。
商品コードを指定して、在庫情報から指定した商品の在庫データを取り出すといった問い合わせ機能を作成できます。
フォーム
フォームは、データ登録や検索などの操作を行う画面を構築する機能です。
画面から登録や検索などの処理を実行すると、入力されたデータがクエリーを通して処理され、テーブルに格納したり、検索条件に合ったデータをテーブルから取得して画面に出力したりできます。
レポート
レポートは、帳票を作成する機能です。
実行するとクエリーを通して必要なデータが取得され、定義したレイアウトにしたがって帳票が出力されます。
必要なデータをテーブルに格納する仕組みがあれば、レポート機能を使って入出庫管理表、出荷伝票、見積書などの帳票が簡単に作成できます。
マクロ
より複雑な処理を構築したい場合は、マクロを使ってプログラミングも可能です。
マクロは、エクセルにも搭載されているものと共通の仕様で、VBAという言語で作成できます。
データベースの知識があればより質の高いシステム作りが可能
Accessは、エクセルと比べて大量データでも容易に扱えます。
そのため、在庫データを一元管理でき、リアルタイムな在庫状況の把握が可能になるでしょう。
ただし、効率よくデータを保存し、保存したデータを高速に取り出すなど、快適に利用できるようにするにはデータベースに関する知識が必要です。
もちろん、エクセルのように直感的に利用するのも可能ですが、エクセルと比較すると操作難易度は上がるでしょう。
また、エクセルと同様に、Accessでも在庫管理システムのテンプレートが無料で公開されています。
まずは既存のテンプレートを入手して利用することで、自作のハードルも下げられるでしょう。
PHP・Pythonでプログラミング
プログラミング言語を使って、在庫管理システムのプログラムを自作する方法もあります。
PHPやPythonは、Webアプリケーションの開発によく使われるプログラミング言語で、近年は技術者の間で人気が高いようです。
Webアプリケーションとして在庫管理システムを自作するためには、PHPやPythonの知識はもちろんですが、
- SQL
- HTML
- JavaScript
なども含めた、総合的なプログラミングのスキルが必要です。
SQL
Webアプリケーションを開発するときは通常、データを保存するためのデータベースと組み合わせて利用します。
データベースにアクセスするためには、SQLという専用の言語を使用しなければなりません。
SQLを使えば、データベースへのデータ登録、更新、削除、検索などの操作が可能です。
HTML
入力画面や検索画面など、システムに必要な画面機能を構築するには、HTMLというプログラミング言語も組み合わせて使う必要があります。
ブラウザに表示されるWebアプリケーションの画面は、主にHTMLで作成されています。
JavaScript
JavaScriptはWeb画面に動きをつけるもので、HTMLの中で合わせて利用されます。
HTMLだけだと動きのない静的な画面ですが、JavaScriptを使えば画面の入力内容をチェックしてエラーメッセージを返すというように、画面に動きをつけられます。
プログラミングには多くの知識とスキルが必要です
本章で紹介してきたように、プログラミング言語を使用してプログラムを自作するには多くの知識とスキルが必要になってきます。
これらの技術スキルを持った人材が自社にいれば、在庫管理システムの自作は可能でしょう。
プログラミング言語を駆使すれば、エクセルやAccessで自作するよりも使い勝手が良く安定したシステムが構築できます。
しかし、自社にプログラミングのスキルを持った人材がいない場合や、いても業務が忙しくて開発にリソースを割けない場合もあるでしょう。
その場合は、システム開発会社に依頼して在庫管理システムを開発してもらう選択肢もあります。
システムのイメージが具体的に固まっていなくても相談に乗ってもらえるので、気軽に問い合わせてみるのがおすすめです。
在庫管理システムを自作する際に押さえておきたい3つのポイント
在庫管理システムを導入するなら、自社の業務が改善されるように、効果的な方法をとりたいものです。
せっかく自作したのに使われないシステムになってしまう、といった失敗は避けましょう。
本章では、在庫管理システムを自作する際に、しっかり効果を出すために押さえておきたい3つのポイントを解説します。
在庫管理システムを導入する目的を明確にする
自作してでも在庫管理の仕組みを導入しようと考えているのであれば、解消したい問題を認識しているでしょう。
どのような問題を解消したいのか、目的を明確にしてブレないようにするのが大切です。
いざ、システムを導入しようとなると、あれもこれもと欲張りがちです。
しかし、機能要件を過度に膨らませると、当初の目的がブレてしまい、十分な導入効果を得られなくなっていまいます。
例えば、入出庫時の記録ミスをなくしたいのであれば、その目的を明確にしておくことで記録ミスを防ぐための仕組み作りに集中するため、アイデアが出やすくなるでしょう。
また、目的が明確になっていれば、在庫管理システムの導入効果を評価する基準も明確です。
業務フローや管理要素を可視化する
在庫管理システムを導入する目的が明確になったら次に、現状がどうなっているかを把握します。
どのような問題点があるかを把握するために、まずは詳細な業務フローの可視化が必要でしょう。
業務フローを可視化するとともに、さらに業務の中で具体的に何を確認してどのような判断をしているかといった、管理要素も明らかにしておきます。
先ほどの、入出庫時の記録ミスを防ぐ例であれば、
- 入出庫時に何を確認しているか
- 商品や数量が正しいことをどのようにチェックしているか
といった具体的な要素を確認しておきましょう。
在庫管理システムに必要な機能を見極める
現状の業務フローや管理要素を明らかにしたら、目的を達成するためにどの部分を改善するべきかを検討します。
つまり、現在の問題を解消するために、在庫管理システムでどのような機能を採り入れるかを見極めることです。
例えば、入出庫時の記録ミスを改善するのであれば、バーコードやRFIDを活用した在庫商品の管理を実施し、数え間違いや保管場所の間違いを防ぐなどが挙げられます。
達成すべき目的を最初に明確にしておくことで、必要な機能も正しく見極められるでしょう。
自社の要件にあった在庫管理システムを構築しよう
在庫管理システムは、簡易的に導入しようと思えばエクセルで手早く構築できますし、本格的な機能を備えたシステムも導入できます。
しかし、数ある選択肢の中でどの手法を選ぶかはやはり、
- 自社で抱えている問題を解消し
- 自社のビジネスをいかに発展させるか
この点をしっかり押さえることに尽きるのではないかと思います。
自社のビジネスが目指すゴールを見据えて、最適な在庫管理システムを構築するために本記事が参考になれば幸いです。
自社の在庫管理を最適化したい、在庫管理についてシステム化を計画している方
ぜひ下記の資料から最適なシステムの選び方もご確認ください。
