「エクセルで在庫管理表を作りたいけれどどうやればいいのかわからない」
こんな疑問を解消します。
仕事の中で商品の在庫を管理する機会は多いですよね。
この在庫管理は手作業で行うとミスが発生してしまう恐れがありますが、エクセルを使えば正確かつ迅速な在庫管理をすることが可能です。
今回はエクセルで在庫管理表を作る方法を解説します。
エクセルを使った在庫管理表の作り方
エクセルで在庫管理表を作るには、以下のようにシートをいくつかに分けると便利です。
・入庫管理シート
・出庫管理シート
・入出庫管理シート
それぞれのシートの役割は以下のようになっています。
・入庫管理シート：商品の入庫情報を記録
・出庫管理シート：商品の出庫情報を記録
・入出庫管理シート：入庫管理・出庫管理シートを元に最終的な在庫数を計算
このように役割ごとにシートを分けることで、記録が楽になり、かつ後で見返しやすい在庫管理表を作ることができるようになります。
では作り方をみていきます。
在庫管理表の作り方①入出庫管理シートに管理対象の商品名を入力
まず在庫管理シートから作成していきます。
項目は以下のとおりです。
・商品名
・入庫総数
・出庫総数
・在庫数
・最低確保数
・在庫状態
見出しを入力したら、「商品名」の列の中に在庫管理対象の商品を入力していきます。
ここまでで一旦在庫管理シートの入力は完了です。
「商品名」以外の項目については後ほど作成していきます。
在庫管理表の作り方②入庫管理シートを作成
続いて入庫管理シートの作成をします。
手順は以下の通りです。
見出しの作成
入庫管理シートの項目は「日付」、「商品名」、「入庫数」です。
商品名の入力
「商品名」の入力にはプルダウンリストを活用します。
プルダウンリスト作成の手順は以下の通りです。
「商品名」の列にある最初のセルを選択
データタブを選択→データの入力規則→入力値の種類を「リスト」に設定
続いて「元の値」と書かれたボックスにカーソルを合わせてから、在庫管理シートの商品名を範囲選択してOKを押します
すると在庫管理シートの商品名を元にしたプルダウンリストが作成されます。
このようにプルダウンリストを作成することで、手入力による打ち間違いがなくなるので、集計時のミス削減につながります。
ここまで出来たら【Ctrl】+【T】キーでテーブル化を行います。
OKを押すとテーブルが作成されます。
このようにテーブル化をすることで、以降に入力した行にもプルダウンリストが自動で設定されるようになります。
これで入庫管理シートが完成です。
商品が入庫するごとにこの入庫管理シートに行を追加していきます。
在庫管理表の作り方②出庫管理シートを作成
出庫管理シートは入庫管理シートをコピーして作成します。
コピー後にはシート名が入庫管理(2)となっているので、出庫管理に変更します。
あとは入庫数となっている箇所を出庫数に変更すれば出庫管理シートは完成です。
出庫管理シートは商品を出庫するごとに行を追加していきます。
在庫管理表の作り方③入出庫管理シートを作成
ここまでに作成した入庫管理シートと出庫管理シートを元にして、在庫管理シートを作成します。
手順は以下のとおりです。
入庫総数データの作成
入庫総数を求めるには「入庫管理シートの中から特定の商品の入庫数を合算」する必要があります。
例えば「おにぎり」の入庫総数を求めるには、入庫管理シート内の中で商品名が「おにぎり」の入庫数を合算します。
これを実現するためにはSUMIF関数を使用します。
このSUMIF関数の作成手順は以下の通りです。
第1引数に入庫管理シート内の商品名データを選択
第2引数に在庫管理シート内の商品名の最初のデータを選択
第3引数に入庫管理シート内の入庫数データを選択
最終的な式は以下の通りです。
=SUMIF(テーブル5[商品名],在庫管理!A2,テーブル5[入庫数])
これで確定すると商品名が「おにぎり」の入庫総数を求めることができます。
出庫総数データの作成
出庫総数も入庫総数と同様にSUMIF関数で算出します。
最終的な式は以下になります。
=SUMIF(テーブル57[商品名],在庫管理!A2,テーブル57[出庫数])
在庫数の算出
在庫数の計算は、【入庫総数】-【出庫総数】という計算をすることで求めることが可能です。
最低確保数
最低確保数には好きな値をいれます。
在庫状態
在庫数が最低確保数を下回った場合に色をつけるようにします。
これは条件付き書式を使うことで実現可能です。
手順は以下になります。
ホームタブを選択→条件付き書式をクリック→新しいルールを選択
続いて「数式を使用して、書式設定するセルを決定」から、式の中に、
=D2<E2
と入力します。
ダイアログの中で、書式から条件を満たした場合の塗りつぶしの色を設定します。
あとは在庫数が最低確保数を下回った場合には「要補充」と出力されるようにします。
これはIF文を使って以下のようにセルに入力します。
=IF(D2<E2,"要発注","")
これで在庫数が最低確保数を下回った場合に要発注と表示され、色がつくようになります。
ここまでできたら、【Ctrl】+【T】キーでテーブル化をします。
これで在庫管理表は完成です。
このページをみている人にオススメの商品
