在庫表|適正在庫と条件付き書式が設定できるExcelテンプレートの作り方

Excelで在庫表を作成する方法を掲載、完成したテンプレートは無料でダウンロードできます。

繰越数・入庫数・出庫数から計算式を入力し、在庫数を計算します。
適正在庫と在庫数から過不足を計算します。
条件付き書式で、在庫数と過不足がマイナスの場合赤色表示するように設定します。
作成手順は下記の通りです。

  1. 表のフォーマットを作成し、サンプルデータを入力する。
  2. 在庫数の計算式を入力する
  3. 過不足の計算式を入力する
  4. セルの書式設定で0を表示しないように設定する
  5. 在庫数と過不足に条件付き書式を設定する。
  6. 条件付き書式で値がマイナスの場合赤色表示にする
  7. 過不足の計算式を変更する

掲載済みの「人口ピラミッド」も参照してください。


在庫表の作成手順

在庫数と過不足の計算式を入力する

在庫数を計算します。

在庫数と過不足の計算式を入力する

在庫数のセル(H11)に下の計算式を入力します。
=IF(OR(D11>0,F11>0),H10+D11-F11,0)
IF関数とOR関数を使用しています。
もし入庫数か出庫数が0より大きければ、「繰越数+入庫数ー出庫数」を計算します。
下行のコピーした場合、「1つ上の行の在庫数+入庫数ー出庫数」になります。

OR関数は引数の条件がTRUEかどうか調べる論理関数です。
構文は、OR(条件1,条件2,条件3,…)です。
ここの場合、入庫数か出庫数が0より大きければTRUEが返ります。

IF関数の構文は、=IF(論理式,真(TRUE)の場合の処理,偽(FALSE)の場合の処理)です。
ここでは論理式はOR関数です。
入庫数か出庫数が0より大きければ真(TRUE)の場合の「H10+D11-F11」が実行されます。
入庫数と出庫数が0の場合、偽(FALSE)の場合の「0」が表示されます。

過不足を計算します。

在庫数を計算する

過不足のセル(K10)に下の計算式を入力します。
=IF(H10<>0,H10-$E$7,””)
在庫数のH10セルが0でなければ、真(TRUE)の場合の「H10-$E$7」が実行されます。
「H10-$E$7」は「在庫数ー適正在庫」になります。
適正在庫のセルは、E7セルのみなので$を付けて絶対番地にしています。
在庫数が0の場合、偽(FALSE)の場合の「””」となり空白になります。

表示が0の場合、非表示にします。

過不足を計算する

入出庫数がない場合、在庫数はIF関数により0が表示されます。
データがない行に0が表示されていると、見にくいので表示しないようにします。
在庫数のセル範囲を選択し、リボンの[ホーム]~数値グループの右下ボタンをクリックします。
セルの書式設定ダイアログボックスが開くので、[表示形式]タブで分類で[ユーザー定義]を選択します。
種類に「0;-0;;@」と入力します。
これで0が非表示になります。

条件付き書式を設定する

在庫数と過不足がマイナスの場合、条件付き書式で赤色表示に設定します。

条件付き書式を設定する

在庫数と過不足のセル範囲を選択します。
リボンの[ホーム]~スタイル グループの[条件付き書式]~[セルの強調表示ルール]~[指定の値より小さい]をクリックします。

マイナスの場合、条件付き書式で赤色表示に設定する

指定の値より小さいダイアログボックスが開きます。
次の値より小さいセルを書式設定に、0を入力します。
書式はデフォルトのままにしていますが、自由に変更してください。

過不足の計算式を変更する

右の表に左の表の式をコピーし、式のみ貼り付けてください。
左表と右表では適正在庫数のセルが違うので、過不足の計算式を変更してください。
=IF(U10<>0,U10-$R$7,””)
「$E$7」を「$R$7」に変更します。
これを下の行にコピーします。
左表と同じように条件付き書式を設定し完了です。

A4サイズ 在庫表のテンプレート

Excelテンプレート無料ダウンロード

在庫表のExcelテンプレート

ダウンロードし使用状況に合わせて、Excelで自由にカスタマイズしご利用ください。

在庫表
部品名:電解コンデンサ 100μF 35V 部品名:カーボン抵抗 10KΩ 1/4W
部品コード:FCDD100 部品コード:KCBD1410
適正在庫:3000 適正在庫:1000
月 日 入庫数 出庫数 在庫数 過不足
繰越数 5000 2000
12 15 300 5300 2300
12 16 5000 300 -2700

Follow me!