【Excel】在庫管理表の作成方法

今回は、この様な在庫管理表を作成します。一見すると、難しい形に見えるかも知れませんが、Excelの基本操作の活用でこの様な在庫表を作成することが出来ますので、ぜひ、皆さんも一緒に作っていきましょう。

日付列の黄色セルに、日々の入庫数・出庫数・調整数を入力します。「現在庫数」「在庫金額」「ステータス」列で状況を確認できます。

在庫が発注点〜発注点の1.5倍の範囲です。発注を検討してください。

在庫が発注点の1.5倍以上あり、十分な在庫があります。

在庫が発注点〜発注点の1.5倍の範囲です。発注を検討してください。

調整：棚卸差異や破損等による調整数量です。

この様な在庫表を作成していきましょう。

在庫管理表の完成版は、下記のメルマガで配布しています。

＼ 無料メルマガに登録してプレゼントの受け取りはここから／

【Excel】在庫管理表の作成

テキストと装飾

まずは、この様な形でテキストをセルに入力します。

そして【ホーム】タブから【配置グループ】の【セルを結合して中央揃え】を選択します。

すると、この様に、在庫管理表の入ったセルが結合され、中央揃えになります。

一度この操作をしたら、F4キーで他のセルも結合していきます。

この様に、1行目・3行目・4行目のセルを結合させます。又、1行目・2行目・4行目を広く取って数値が入力しやすい様にしておきます。

この状態で【ホーム】タブから【格子】を選択します。

すると、この様に罫線を付ける事が出来ます。

【ホーム】タブから【罫線▼】の【その他の罫線】を選択します。

すると、【セルの書式設定】ダイアルボックスが表示されますので、【色】は緑にして、【外枠】と【内枠】の罫線を付けます。

【塗りつぶし】タブから【薄い緑】を選択して【OK】ボタンを押します。

すると、この様な罫線になります。

上の【G】列と【H】列も罫線で装飾します。

今月の日付を入力して【キーボード】の【Ctrl】+【1】を押します。

【セルの書式設定】から【yyyy/m】と入力して【OK】ボタンを押します。

するとこの様なカタチになります。

棚卸実施日を入力しておきます。

【No】～【ステータス】を入力して、下のセルとセルの結合をします。

【塗りつぶし】を紺にして、【フォント】を白にします。

下にこの様な罫線を付けます。

ウィンドウの固定

セルを選択して【表示】タブから【ウィンドウの固定】から【ウィンドウ枠の固定】をクリックして選択します。

すると、ウィンドウが固定されます。

ステータスの隣のセルを【セルの結合】をして、横3つのセルを結合します。又、セルの幅を狭くします。

日付の欄を装飾して、テキストを入力して装飾します。

装飾したら、この様になります。

ここでは、【No】～【発注点】のダミーのテキストと数値を入力しておきます。

【合計入庫】【合計出庫】【合計総数】を入力して装飾します。

数式の入力

この様な計算式を入力します。

$B$2:$CS$2 (検索条件範囲): 「入」「出」「調」が入力されている範囲です。下にオートフィルでコピーしてもずれないように、絶対参照（$マーク）で固定しています。

“入” (検索条件): 2行目の中から、どの文字を探すかを指定します。

$B3:$CS3 (合計範囲): 実際に数値を足し算する範囲です。下にコピーしたときに「4行目、5行目…」と自動で切り替わるよう、行番号には$をつけていません（列のみ複合参照で固定すると安全です）。

現在個数

現在の個数を入力します。

在庫金額

単価×現在在庫になります。

【単価】と【金額】の欄は分かりやすくコンマを付けておきましょう。

ステータス

数式の処理ステップ
第1の条件：G9<=F9

G9の数値が、F9の数値以下かどうかを判定します。
当てはまる場合（在庫が基準値以下）：「要発注」と表示して計算を終了します。

当てはまらない場合（在庫が基準値より多い）：次の第2の条件に進みます。

第2の条件：G9<=F9*1.5
第1の条件をすり抜けたものの中で、G9の数値がF9の1.5倍以下かどうかを判定します。
当てはまる場合（在庫は基準より多いが、1.5倍までの範囲内）：少し減ってきているため「注意」と表示します。

それ以外（偽の場合）：”適正”
第1の条件にも第2の条件にも当てはまらない場合（つまり、在庫が基準値の1.5倍よりも十分に多い場合）は、「適正」と表示します。

するとこの様になります。

条件付き書式

現在在庫を範囲選択して、【ホーム】タブから【条件付き書式▼】から【データーバー】にマウスカーソルを当てると、【データーバー】が表示されます。

【青のデーターバー】をクリックして選択します。

すると、この様になり、どの在庫数が多いのか？分かりやすいです。

在庫金額も同じように設定します。

ステータスの条件付き書式

【ホーム】タブから【条件付き書式▼】から【新しいルール】を選択してクリックします。

すると【新しいルール】ダイアルボックスが表示されます。ココでは、【数式を使用して、書式設定するセルを決定】をクリックして、【次の数式を満たす場合に値を書式設定】に【=I9=”要発注”】と入力し、【書式】をクリックします。

【フォント】タブからテキストの色を【赤】にして、【塗りつぶし】タブから【塗りつぶしの色】を設定します。

すると、ステータスがこの様になります。

注意も黄色系にしておきます。

集計表

累計在庫数を求めます。

登録品目数は、COUNTA関数で求めます。指定した範囲内の「空白ではないセル」の個数を数える統計関数です。

在庫補充率

すると、この様になりますので、【累計在庫数】と【在庫補充率】の表示形式を、コンマと、％に変更します。

フォントの大きさなどを調整します。

すると、この様な在庫管理表が作成出来ます。

在庫管理の4原則とは？

在庫管理の4原則は、過剰・不足なく適正な在庫を保つための「所在」「数量」「先入先出」「発注点」の管理基準です。これらを徹底することで、欠品防止、コスト削減、業務効率化を実現できます。

管理表とは何ですか？

管理表（かんりひょう）とは、業務、プロジェクト、在庫、生産などの進捗や情報を一覧形式で整理・可視化し、円滑に進めるための帳票です。タスクの抜け漏れ防止、担当者・期限の明確化、チームでの情報共有に活用され、一般的にエクセル（Excel）や専用ツールで作成します。

まとめ