在庫管理をエクセルで簡単に行う方法
「在庫管理に手間がかかっている」
「在庫管理をエクセルでやりたいが方法が分からない」
「エクセルで在庫管理する上で便利な関数が知りたい」
そんな疑問や悩みを持ってはいませんか？
エクセルは高機能で非常に便利です。ネットビジネスに重要な役割を担う「在庫管理」もエクセルなら簡単に出来ます。
この記事では、
- 在庫管理表をエクセルで作る方法
- 在庫管理を効率化するエクセル関数
- グラフを使って更に分かりやすく管理する方法
をまとめました。しっかりと習得していきましょう。
在庫管理表をエクセルで作る方法
在庫管理表とは、
- 在庫している商品情報
- 個数
- 入出庫日
などを管理するための表です。
在庫管理表を使うことにより、商品を受注してからの物の流れを把握でき、例えば、在庫が減ってきたことに気づけて在庫切れを防げたり、使用期限なども管理することも可能です。
エクセルを使って在庫管理表を作るのであれば、「単表タイプ」と「在庫移動表タイプ」の2つの形式で作ることができます。
それぞれ、詳しくご説明します。
1．単表タイプ
単表タイプとは、「吊り下げタイプ」とも呼ばれます。実際に在庫を保管する棚に吊り下げている在庫管理表をエクセルにしたものです。
商品名や品番などの基本情報を表の上に入力し、縦軸に「日付」を、横軸には「入庫数」「出庫数」「残高」などを入力します。そして、その表に数字を入力していきます。
今まで紙で行っていた管理に近く、エクセルが苦手な人でも入力がしやすいのが特徴です。
2．在庫移動表タイプ
在庫移動表タイプは、縦軸に品番や商品名を商品の数だけ入力します。そして、横軸に日付を入力します。
単表タイプと異なるのは、1つのエクセルシートで複数商品を一度に管理できる点です。
一覧できるというメリットがある一方、単表タイプに比べると細かい情報が管理できないというデメリットもあります。
在庫管理表のデメリットを回避する3つのポイントとは
在庫管理表に限らず、複雑すぎると作るのにも確認するのにも苦労します。管理表が複雑だと、仕事にも悪影響があることは間違いありません。一般的によくある失敗と言えば、次のようなものがあります。
・「在庫管理表が複雑で分かりにくい」
・「作った本人だけが理解しているが、複雑すぎて共有できない」
多くの商品を管理している会社にありがちなのですが、在庫管理表が複雑すぎて確認したい部分をすぐに確認できないのです。それだけならまだしも、間違いがあっても気づかないままになってしまうので、混乱したり、あげくはクレームにもつながりかねません。
これは在庫管理表を作る際に大事なポイントをおさえていないことが原因です。たった3つのポイントをおさえるだけで回避できるのですから、必ず覚えておきましょう。
①在庫管理表に必須な基本項目以外にとらわれすぎない
在庫管理表ですから「どの商品が、いくつ存在するのか」という基本的な情報があってこそ利用価値があります。極端な話、これだけ分かっていさえすれば在庫管理表としては成り立ちます。大抵の場合、この基本情報にプラスして便利に使えるようにしています。「品目コード」「商品リンク」「Amazonランキング」「仕入れ先」など、自社が知りたい情報が一緒に載せられていると便利な時がありますよね。
しかし詳しければ良いというわけではないのです。より便利なものをと、項目を増やしたり、複雑化してしまうのはあまりおすすめできません。「どの商品が、いくつ存在するのか」という必須の情報を基本に、他の項目にとらわれすぎないようにしたいですね。
②在庫管理表の入庫と出庫を間違えないようにする
「いくつ入庫して、いくつ出庫したか」。こんなに単純な在庫管理ではありますが、1つ勘違いや入力ミスなどを犯してしまえば、相違したままで進んでしまいます。1つでも相違していると在庫管理の意味がなくなってしまうのです。
①仕入れにより入庫する
②受注まで保管する
③受注したら出庫する
このうちの②の部分が在庫になるのですから「いくつ入庫したか」「いくつ出庫したか」がいかに重要なのが分かるはずです。単純なことではありますが、商品数が多いほど在庫との照らし合わせは大変です。在庫管理表が意味を成すように、間違いなく入力するようにしましょう。
③管理レベルによって在庫管理表の項目数を考える
「いくつ入庫して、いくつ出庫したか」が重要だとお伝えしましたが、これを正確におさえた上で、「どこにあるか」の項目を追加することで、より分かりやすい在庫管理が実現します。どこに何がいくつあるかがすぐ分かるなら、在庫管理しやすいですね。
しかし「いくつ入庫して、いくつ出庫したか」という基本が確実に守れていないうちは、項目を増やすことはおすすめできません。より便利に分かりやすくと、項目を増やしたいのであれば、基本項目を確実に正確に管理できるようにしたいですね。
在庫管理システムをエクセルやアクセスで作る方法
エクセルやアクセスを使えば、より本格的な在庫管理システムを作ることができ、在庫管理を効率的に行うこともできます。
システムを作る方法は、大きく分けると、
- 既にあるテンプレートをそのまま使う
- 既にあるテンプレートをカスタマイズする
- イチからシステムを自作する
の3通りです。
それぞれ、ご説明します。
既にあるテンプレートをそのまま使う方法
インターネットには、既に在庫管理を効率的に行うためのテンプレートが配布されており、無料で使えるものもあります。
そのテンプレートをダウンロードすれば、そのまま使うことができ、エクセルやアクセスの操作を普段行わない人でも利用可能です。
ただし、業務が特殊な場合など、テンプレートをそのまま使うことができない場合もあるので、事前に確認が必要です。
テンプレートをカスタマイズする方法
関数やマクロに関する知識があれば、既にあるテンプレートをカスタマイズすることが可能です。マイクロソフト製品用のプログラム言語であるVBAを使えば、エクセルのシートに画面を作成することができます。
エクセルやアクセスに詳しい人であれば、テンプレートをカスタマイズして在庫管理システムを作るのも選択肢の一つです。
イチからシステムを自作する方法
既にあるテンプレートを利用せず、イチからエクセルやアクセスで在庫管理システムを作成することもできます。
カスタマイズに比べて関数・マクロ・VBAの知識が更に必要となり、難易度も上がり、時間や手間もかかりますが、その分業務内容に合わせたシステムを自由に作ることができます。
在庫管理も効率化できる！エクセル関数8つを覚えよう
エクセルは便利なものですが、何だか難しいから敬遠してしまっているという人も多いようです。確かに関数も多種多様に用意されていて、簡単でよく使うものから、なかなか普段使用しないようなものまであります。高機能なところが「難しい」というイメージをつけてしまっているようですが、実際難しいわけではありません。
個人事業主が使うエクセル表は、そこまで複雑なものでなくて良い場合が多いですね。「使うと便利」「頻繁に使う」関数のみを使用していれば、便利に簡単に誰でも扱うことが出来るものなのです。
在庫管理表作成の前に知っておきたい関数の3つの基本
①参照
参照とは「照らし合わせて参考にする」という意味がありますが、エクセルで考えると「特定のセル情報をその他のセルで同じように表示する」ことになります。
①上記画面で「A1」セルに「キャベツ」と入力されている
②「D1」セルに関数で「=A1」と入力する
③「A1」セルのとおり「D1」セルでも「キャベツ」と表示される
商品名を2回入力する手間が省けて非常に便利です。
②四則演算（しそくえんざん）
・加算→＋
・減算→ー
・乗算→＊
・除算→／
もし「B1」セルと「D1」セルの値を掛け算をしたい場合は次のような関数を入力してください。
「＝B1*D1」（記号は必ず半角で入力しましょう）
③比較演算子（ひかくえんざんし）
・「A＝B」→AとBが等しい
・「AB」→AとBが等しくない
・「A＞B」→AがBよりも大きい
・「A＜B」→AがBよりも小さい
・「A＞＝B」→AがB以上
・「A＜＝B」→AがB以下
もし試験の合否を400点以上と400点未満で分けたい場合があるとします。その場合は次のように関数を入力してください。
試験の合計点を表す「B列」、合否を表す「C列」に注目してください。
「=IF(B2＞＝400,”合格”,”不合格”)」
C列にこの関数をオートフィルでコピーすれば、400点以上の人が「合格」400点未満の人が「不合格」と表示されます。
比較演算子と聞くと難しそうですが、簡単な関数を入力するだけでこんなに便利なのです。
エクセルでは「文字」と「数字」は別データ？
エクセルは数字を自由自在に操れるソフトですが「文字」と「数字」では違う種類のデータとして識別するようになっています。
・文字…セル内に左寄せで表示される
・数字…セル内に右寄せで表示される
設定を修正したいのなら、セル上で右クリック→セルの書式設定→表示形式のタグから該当するものをクリックします。通常は「標準」を設定することが多いです。ちなみに関数として使用できるのは「半角数字」のみですので注意してください。
在庫管理表を作るにあたって重要な8つの関数とは？
①SUM関数
SUMとは範囲のことで、指定した範囲のセル内の数値を合計することが出来ます。
・A列全体を指定→「=SUM(A:A)」
・A1からA10までを指定→「=SUM(A1:A10)」
指定した範囲を瞬時に合計してくれるので、かなり効率化がはかれます。
②AVERAGE関数
AVERAGEとは平均のことで、指定した範囲のセル内の数値の平均を算出することが出来ます。
・A1からA10までを指定→「=AVERAGE(A1:A10)」とします。
③MIN関数・MAX関数
「MIN」とは最小値、「MAX」とは最大値を表示する関数です。指定された範囲の中で1番小さい数値・1番大きい数値を表示することが出来るのです。
・A1からA10を指定した時の最小値→=MIN(A1:A10)
・A1からA10を指定した時の最大値→=MAX(A1:A10)
④COUNT関数
指定された範囲の中で、数値のみを識別して計算する関数です。
・指定されたA1からA10までのセルの中で、数値のみを識別して個数を数える→「=COUNT(A1:A10)」
⑤COUNTA関数
COUNTAとは、指定している範囲の中で「数値」を合計するのではなく、セルの数を合計します。この場合空白ではないセルの数をカウントします。
・指定されたA1からA10までの範囲の中で、空白ではないセルの数を数える→「=COUNTA(A1:A10)」
⑥COUNTIF関数
COUNTIF関数は、条件を満たすセルのみの数を数えます。
指定された範囲の中で「キャベツ」と入力されているセルのみを数える→「=COUNTIF(A1:A10, “キャベツ”)」
⑦IF関数
IF関数は条件によって表示方法を変えたりすることが出来る関数です。「=IF(論理式, 真の場合, 偽の場合)」となり、真の場合の表示方法と偽の場合の表示方法が異なります。
指定された範囲の中で、5を正解、それ以外は不正解と表示したい→「=IF(A1=5,”正解”,”不正解”)」
⑧LARGE関数
LARGE関数は、指定された範囲の中で特定の値を検索して探し「〇番目に大きい値」などを見つけることが出来ます。
魚釣り大会でより大きな魚を釣った人順に順位をつけたいのですが、画像のようにA列に氏名、B列に魚の長さが入力されています。
指定したB列の中から1位を表示したい→「=LARGE(B1:B10,1)」
同じように2位を表示したい→「=LARGE(B1:B10,2)」
とても複雑なことをしているように感じますが、どれも簡単な関数を入力しているだけです。この8つはとても便利でよく使われるものですから、よく理解しておきたいですね。
在庫管理表（エクセル）の作り方
作り方を学んでも、実践しなければなかなか身に付くものではありません。実際に在庫管理表を作り使用する中で、項目数を減らす・増やすといった作業を考えていきましょう。
事務作業が激減すれば、仕事もスムーズになり、リサーチなど他の作業に集中することが出来ます。また顧客対応もスムーズですから、クレームにつながることもありません。エクセルを上手く利用して仕事が円滑に進むように工夫してみましょう。
在庫の流れに沿って在庫管理表を作成する
在庫管理表を作るポイントとしては、在庫の流れに沿って作ることです。入庫してから出庫するまでの流れですね。次のような表になります。
①入庫した年月日
②商品名（あれば商品コード）
③入庫数
④出庫数
⑤在庫数
これに保管場所という項目を追加しても分かりやすいですね。
小売である事業主が在庫管理表を作る上で注意したいこと
在庫は出来るだけ多くを確保すれば良いわけではありません。
・多すぎると不良在庫となり、赤字になることもあり得る
・少ないと販売機会を逃してしまう
在庫はしっかり確保したいのですが、在庫数は適切な量でないといけません。
①適正な在庫数の見極め方を身につけ在庫を確保する
②不良在庫を避けられるようにする
③多すぎて棚卸の負担にならないように適量を目指す
小売業の在庫管理表ですが、先ほど紹介した在庫管理表よりも項目数が増えています。
・取引先名
・単価
・備考
備考は何か覚えておきたい事柄をメモするのに便利です。あまり使用しない項目でもありますが、あれば大変便利ですので作るようおすすめします。また、そうは言っても人が操作する表ですから、間違っていないとは言えません。定期的に棚卸を実施して、データと合致しているか確認することも大事ですね。
在庫管理と同じく重要な「棚卸」とは？
在庫管理は、在庫数を把握しながら適切な在庫数を確保するためにも必要なものです。在庫管理表は表の中での管理になりますが、実際に商品の数を直接数える在庫管理として「棚卸」というものがあります。
①売れない商品や損傷などがある商品などの「不良在庫」がないか調べる
②在庫と在庫管理表が合致しているか確認する
③在庫としての資産を把握する
実在庫と実際に照らし合わせて行う棚卸は、このようなメリットがあります。多くは1ヶ月に1回行っているようですが、自分でルールを作って定期的に行いましょう。
在庫管理はグラフを使用してさらに分かりやすくしよう！
エクセルでは棒グラフ、円グラフ、折れ線グラフなど様々なグラフを作ることが出来ます。データによっては分かりやすさが格段に違ってきますので、より適切なグラフを選択するようにしましょう。そうすることで、在庫管理がもっと分かりやすいものになるはずです。
棒グラフ・折れ線グラフを同時に表示して在庫管理をより明確に！
①A商品の売り上げ
②B商品の売り上げ
③C商品の売り上げ
④3つの商品の売り上げ合計
この4つのデータを表示したい時、まず棒グラフを使用してみましょう。
A～C商品の3つを比較するのには、十分役割を果たしており、円グラフなど他のグラフよりは非常に比較しやすいです。しかし売り上げ合計は比較する必要はありません。売り上げ合計は推移さえ分かれば良いのです。この場合、①～③を棒グラフ、④を折れ線グラフで表示してみましょう。
変更方法は次のとおりです。
①売り上げ合計の棒グラフの上で右クリック
②「系列グラフの種類の変更」をクリック
③「合計」の値だけ折れ線グラフを選択してOKにする
この方がずっと分かりやすく、見やすいですね。このように、データによってグラフを使い分けるようにしてください。
さらに在庫管理表のグラフを見やすくする「第2軸」とは？
1つの商品の売り上げと合計の売り上げでは、当然売り上げ合計の数値が大きくなります。棒グラフと折れ線グラフの間が空きすぎると、それはそれで見づらいですね。そこで注目したいのが「第2軸」です。
①A～Cの商品の売上の棒グラフは主軸である「左側の軸」に数値を表示
②売り上げ合計である折れ線グラフは第2軸である「右側の軸」に数値を表示
変更方法ですが、次のとおりに実施してください。
①売り上げ合計の折れ線グラフの上で右クリック
②「データ系列の書式設定」をクリック
③「系列のオプション」タグにある「第2軸」をクリックして［閉じる］
さらに分かりやすいグラフとなりました。
エクセルで将来の売り上げを予想？散布図と近似曲線とは
A～Cの商品を毎月どれだけ売り上げているのか分かりやすく表示されたところで、次は「売り上げ予想」を出していきます。エクセルにはこれまでの売り上げデータを分析し「将来の売り上げ」のおおまかな予想する方法があり、それが「散布図」と「近似曲線」なのです。
①A商品のデータを指定し、エクセル画面の「挿入」から散布図をクリック
②「グラフ要素を追加」より、「近似曲線」を選び、「線形」をクリック
③散布図にあるすべての点の平均である「線形近似曲線」が1本の線で表示されます。
④線形近似曲線の上で右クリックして「近似曲線の書式設定」をクリック
⑤「切片」と「グラフに数式を表示する」にチェックし閉じる
⑥グラフの中に「y=○○x」という式が表示されていますが、縦軸（y）が売上、横軸（x）が回数として方程式に当てはめてみましょう。これがA商品の売り上げの予想額となります。
在庫管理表（エクセル）で簡単に集計・分析する方法
これまで便利な関数の機能についてお伝えしてきましたが、今度は、数式や関数を使用せずに、マウス操作だけで膨大なデータを簡単に集計・分析が出来る「ピボットテーブル」についてご紹介していきます。
在庫管理表作成のためにピボットテーブルで使用できるデータの条件とは？
ピボットテーブルを使用するデータの条件は次のとおりです。
①空白セルを作らない（数値欄は0と入力）
②「項目名」は必須であり、長すぎないシンプルな項目をつける
③商品名は半角・全角、漢字・英字・かなが混在せず統一されているか確認する
④コードだけではなくコードに対する名前を設定する
上記の内容を確認し、修正するところはしておいてください。
ピボットテーブルで在庫管理するにあたって簡単に文字を統一する方法とは？
上記③のように、文字の種類が混在している場合、大量の文字の中から見つけ出して修正する方法では時間がかかります。簡単に文字を統一する方法を身につけておきましょう。
例えば「油性マーカー」「油性ﾏｰｶｰ」など、同じ商品名でも、全角と半角でバラバラだと「油性マーカー」として正しくすべてを集計することが出来ません。
①エクセル画面のホームタグから「検索と選択」→「置換」を開く
②「検索する文字列」に「油性ﾏｰｶｰ」と入力
③「置換後の文字列」に「油性マーカー」と入力
④「すべて置き換え」ボタンをクリック
これで簡単に「油性マーカー」という全角文字に変更されました。簡単で、さらに修正漏れもなく統一出来るので便利です。
早速ピボットテーブルで在庫管理表を作成してみよう
すべてのデータを条件通りに修正できたら、早速ピボットテーブルを作成していきましょう。データ全てを選択します。
【データ全てを選択する方法】
①エクセル表の左にあるセル番号1と、表の上に表示される列Aとが交差する空白欄をクリックする
②Ctrl + A
①か②どちらかの方法でデータ全てを選択します。
選択後「挿入タグ」から「テーブル」を選んだあと、「ピボットテーブル」をクリックし、問題なくすべて範囲指定出来ていたらOKボタンをクリックします。
ピボットテーブルは次のように表示されます。
行に表示させたい項目を「行ラベル」の欄へドラッグします。
売り上げなどを表示させたい場合は「値」の欄へドラッグします。
列に表示させたい項目を「列ラベル」の欄へドラッグします。
自分で分かりやすい表になるように、考えながらドラッグして表を完成させましょう。思った表にならないなら「戻る」ボタンで修正できますので、何度でも納得がいくまで作り直してみて下さい。
ピボットテーブル以外にも！エクセル在庫管理表の無料テンプレート2選！
自分で作成したくない、今すぐ使いたいと思っている人に、是非ご紹介したいのが「無料テンプレート」です。
無料でダウンロード出来、ダウンロード直後から使えるので、非常に便利です。おすすめの2つをご紹介していきますので、自分が1番使いやすそうだと感じるものを見つけて下さいね。
【在庫管理表無料テンプレート①】クラウドERP実践ポータル
無料テンプレートなのに、項目数も多く本格的な仕上がりです。
「JANコード」「ブランド」「ブランドコード」「 アイテム」「アイテムコード」「色」「色コード」「サイズ」「サイズコード」「商品コード」「製品名」「価格（税別）」「価格（税込）」「在庫数」「状況」などがあります。
必要な個人情報を入力しなければダウンロードは出来ませんが、無料なのは魅力です。
【在庫管理表無料テンプレート②】bizocean
bizoceanのテンプレートの魅力は種類の多さ！商品管理表だけで90近く無料テンプレートが存在します。単純なものから複雑なもの、小売店向けなど、それぞれの業界によって使いやすいテンプレートを用意しているため、自分が探しているものを見つけやすいです。
参考：bizocean
ピボットテーブル等の便利機能を使ってエクセルで正確な在庫管理表を作ろう！
エクセルは高機能がゆえ「難しい」イメージがつきまといますが、便利機能を覚えてしまえば簡単です。必要なエクセル機能だけでも習得して、在庫管理を簡単に、かつ正確に行っていきましょう。在庫管理がスムーズに出来るようになれば、仕事の効率化がはかれます。快適な仕事が出来るように工夫してくださいね。
