品目ごとに目標在庫が違う場合、不足品だけを探すには？
品目ごとに目標在庫と現在庫を比べ、不足しているものだけを一覧にします。箱や包装の単位に合わせた発注数量とは分けて確認します。
全品目を在庫10未満で絞ると、目標24の品目も目標6の品目も同じ扱いになります。JOIN SHEETで各行の目標と現在数量の差を計算すれば、品目ごとの不足一覧を作れます。
現在量と目標量の単位をそろえる
架空の表は1品目1行で、両数量の単位をそろえています。現在量が箱数、目標量が個数のままでは引けません。空欄は在庫0とは限らないため先に確認します。
| 商品 | 現在数量 | 目標数量 |
|---|---|---|
| P1 | 8 | 12 |
| P2 | 30 | 24 |
| P3 | 6 | 6 |
| P4 | 2.5 | 5 |
不足数量を計算し、0より大きい行を残す
- 在庫確認表を結果の基準に選び、「結果を整理」で「計算」を追加します。
- 「計算方法」は「引き算 · A − B」、「最初の値 (A)」は目標数量、「次の値 (B)」は現在数量を選びます。「結果の項目名」に不足数量と入力します。
- 計算の後に「絞り込み」を追加し、不足数量が0より大きい行を残します。
- 大きな不足から確認したい場合は、続けて「並べ替え」を追加し、不足数量を「降順」にします。「適用」を押して結果を確認します。
目標ちょうどの在庫は除かれる
P1は12−8＝4、P4は5−2.5＝2.5で残ります。目標を超えるP2と目標ちょうどのP3は除外されます。0以上にすると不足していないP3まで入るため、境界の条件を確認してください。
| 商品 | 現在数量 | 目標数量 | 不足数量 |
|---|---|---|---|
| P1 | 8 | 12 | 4 |
| P4 | 2.5 | 5 | 2.5 |
参考にした質問には発注単位への切り上げもありますが、この手順で求めるのは不足数量までです。P4の不足2.5を3個や1箱として発注するかは、別に決めてください。入荷予定、取り置き分、納期は反映しておらず、発注書も自動では作りません。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
Excel formula is stock sheetの質問を参考に、独自の架空データで手順を作成しました。
