【Excel初心者向け】簡単に文房具などの在庫をExcelで管理する方法

最近、友人から「上司に『消耗品の在庫数がすぐに分かる管理表を作ってくれ』と言われたんだけど、どうすればいいのか分からない！」と相談を受けました。

こんにちは！　松田軽太です。

確かに普段からExcelで管理表を作っているような仕事をしていれば、すぐに「あー、こうやれば在庫管理できるじゃん！」と思い浮かぶかもしれませんが、Excelを使い始めたばかりだと、なかなか思いつかないかもしれないですね。

というワケでExcelで簡単に文房具などの在庫管理をする方法をご紹介したいと思います。

【こんな感じの在庫管理表】

在庫管理とは

在庫管理の一番の目的は「今、この品物がいくつあるか？」を把握することです。

しかし在庫数が把握できるようになったら「いつこの品物を発注すればいいか？」が知りたくなりますよね。

昔はこういう管理をノートに記帳してやってましたが、イマドキはExcelでチャチャっと管理したいですね。

在庫の管理方法

今回は文房具などの消耗品管理表を作成したいと思います。

では実際に在庫管理する方法を説明していきます。

①.管理したい消耗品の『品目マスタ』を作成する

例えば文房具といってもボールペンとか消しゴムとかシャープペンとか付箋とかいろいろな種類がありますね。

そこでまずは管理したい品物を明確にするために洗い出して品物の一覧表を作成しましょう。

そのような品物の一覧表を『品目マスタ』と言います。

『品目マスタ』の項目は次のようにしましょう。

　１．品目コード番号

　２．品目名称　

　３．サイズ

　４．発注単位

　５．発注基準数

　６．購入先名

　７．備考

②.購入先マスタを作成する

すべての文房具を同じお店から買うのであれば管理こともないですが「消しゴムはこのお店、ボールペンはあのお店」というように購入先が違うことが多いでしょう。

ということで品目ごとにどのお店に注文すればいいのか分かるように『購入先マスタ』を作成します。

『購入先マスタ』が整えば「あれ？この文房具、どこに頼むんだっけ？」と迷うこともないでしょう。

『購入先マスタ』の項目は次のようにしましょう。

　１．購入先コード

　２．購入先名称

　３．住所

　４．電話番号

　５．FAX番号

　６．備考

③.入出庫明細表を作成する

文房具を購入する時（入庫）と使用する時（出庫）に入出庫数を入力して、

いつ何を買って、どのくらい使ったのかが把握できようにします。

『入出庫明細表』の項目は次のようにしましょう。

　１．受払日

　２．品目コード

　３．品目名称

　４．入庫数

　５．出庫数

   ６．使用者　

せっかくなので品目名称は品目マスタから自動表示させたいですよね？

この場合は　VLOOKUP　という関数を使用します。

入出庫明細表のD3のセルに　=VLOOKUP(C3,品目マスタ!B:I,2,0)　と入力してださい。

品目名称が表示されたら、D3のセルをコピーしてD16まで貼り付けてください。

④.在庫マスタを作成する

入出庫明細を品目マスタ単位に　前月残数　＋　入庫数　－　出庫数　＝　現在庫　を算出します。

在庫マスタの項目は次のようにしましょう。

　１．品目コード

　２．品目名称

　３．前月繰り越し数

　４．当月入庫数

　５．当月出庫数

　６．現在庫数

入庫数と出庫数は入出庫明細表の数字を活用すれば、２重入力しなくて済みます。

では品目コードごとに入出庫明細表から入庫数を在庫マスタの当月入庫数に自動集計するように設定してみましょう。

この場合は　SUMIF　という関数を使用します。

在庫マスタのG3のセルに　=SUMIF(入出庫明細表!C:C,B3,入出庫明細表!E:E)　と入力してださい。

当月入庫数が表示されたら、G3のセルをコピーしてG9まで貼り付けてください。

同じように当月出庫数を自動集計してみましょう。

在庫マスタのH3のセルに　=SUMIF(入出庫明細表!C:C,B3,入出庫明細表!F:F)　と入力してださい。

当月出庫数が表示されたら、H3のセルをコピーしてH9まで貼り付けてください。

では次に現在庫数を計算してみます。

在庫マスタのD3のセルに　=F3+G3-H3　と入力してださい。

現在庫数が表示されたら、D3のセルをコピーしてD9まで貼り付けてください。

せっかく現在庫数が分かるようになったので、次は発注有無の判断を自動的にできるようにしてみましょう。

この場合も　VLOOKUP　という関数を使用します。

在庫マスタのI3のセルに　=VLOOKUP(B3,品目マスタ!B:I,5,0)　と入力してださい。

発注基準数が表示されたら、I3のセルをコピーしてI9まで貼り付けてください。

発注基準数が表示されたら現在庫数と比較して　発注判断に「発注そいてください」というコメントを自動的に表示されるようにして、在庫切れを防ぎましょう。

この場合は　IF　という関数を使用します。

F関数で『現在庫数（D列）が発注基準数（I列）よりも小さかったら「発注してください」と表示する、小さくなかったら空白を表示する』という命令をしています。

在庫マスタのE3のセルに　=IF((D3<I3),"発注してください"," ")　と入力してださい。

発注判断が表示されたら、E3のセルをコピーしてE9まで貼り付けてください。

続いて「発注してください」というコメントが目立つように条件によってセルの背景色を変えてみましょう。

「書式」から「条件付き書式」を選択します。

次に条件を設定します。

「セルの値が▼」「次の値に等しい▼」「="発注してください"」と設定します。

次に背景色を設定します。

「書式」ボタンを押すと「フォント」タブが表示されているので「パターン」タブを押します。

たくさんの色が表示されているので、この中から好きな色を選択します。

今回は薄い黄色に設定してみます。

「発注してください」という文字の背景が薄い黄色になりました。

この条件付き書式はE3のセルにしか設定されていません。

しかし毎回、同じ設定をするのは手間なので書式設定をコピーして貼り付けましょう。

まずE3のセルをコピーします。次にE4からE9までを選択し、貼り付けで右クリックして「形式を選択して貼り付け」を選択します。

「書式」をマウスでクリックして「ＯＫ」を押します。

「発注してください」と表示されているセルの背景が薄い黄色になるので分かりやすくなりました。

在庫マスタを運用する注意点は、その月の入出庫の入力が完了したら、月次処理として、現在庫数を前月残を移送（セット）することを忘れずに行います。

現在庫数のD3からD9をコピーしたら、F3からF9を選択して「形式を指定して貼り付け」から　値　を選択し「OK」ボタンを押します。

まとめ

と、こんな感じで作れば文房具に在庫管理が出来るようになります。

簡単なので在庫管理で困っているようでしたら、是非、試してみてください。

今回の例では4つのExcelワザを使用しました。

　・VLOOKUP

　・IF

　・SUMIF

　・条件付き書式

この4つの関数はいろいろな場面で応用できるので、是非、使ってみてください。

このブログではこの記事の他にも便利なExcelの使い方をご紹介していますので併せてお読みください。

もっと便利にExcelを使いこなしたいと思う人にオススメのExcel本を紹介します。

■たった1日で即戦力になるExcelの教科書 

たった1日で即戦力になるExcelの教科書

■Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術 

Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術