概要
Excelのワークシート関数を使って、簡単な在庫管理システムを作ってみます。
やること
プリンタのインクの在庫を管理する
■ インクの種類
青, 赤, 黄, 黒
仕組み
インクを購入したり、インクを交換したりすると、在庫が変動します。
この在庫の変動を履歴として残します。
■ インク在庫変動履歴
種類 | イベント | 在庫 | 日付 |
---|---|---|---|
赤 | 購入 | +1 | 2017/10/07 |
青 | 交換 | -1 | 2017/11/23 |
黒 | 交換 | -1 | 2017/12/05 |
この履歴によって在庫の増減が分かりますが、現在の在庫がいくつなのかは分かりません。
それは、最初の在庫がいくつだったのか、どこにも情報がないからです。
そこで、在庫変動履歴の最初に、初期在庫のデータを入れておきます。
■ インク在庫変動履歴(初期在庫数含む)
種類 | イベント | 在庫 | 日付 |
---|---|---|---|
青 | 初期在庫 | +3 | - |
赤 | 初期在庫 | +1 | - |
黄 | 初期在庫 | +2 | - |
黒 | 初期在庫 | +4 | - |
赤 | 購入 | +1 | 2017/10/07 |
青 | 交換 | -1 | 2017/11/23 |
黒 | 交換 | -1 | 2017/12/05 |
これで、現在の在庫が分かる表が出来上がりました。
現在の在庫を表示する表は、以下でいいでしょう。
■ インク在庫
種類 | 在庫 |
---|---|
青 | 2 |
赤 | 2 |
黄 | 2 |
黒 | 3 |
実装の検討
インク在庫が増減するたびに、インク在庫変動履歴を手で追加する事にします。
すると、プログラミングする必要があるのは一か所だけです。
それは、在庫変動履歴を初期在庫から全て合計する処理です。
この処理は、インクが一種類しかないなら、SUM関数を使って合計すればうまくいきます。
しかし、今回の例のように、複数の種類のインクがある場合、SUM関数ではうまくいきません。
SUM関数では、インクの種類に関わらず、全ての在庫変動を合計してしまうためです。
今回必要なのは、青インクなら青インクの変動履歴だけを、赤インクなら赤インクの変動履歴だけを合計する処理です。
Excelには、ちょうどぴったりのワークシート関数があります。
- SUMIF関数
指定された検索条件に一致するセルの範囲を合計します。
=SUMIF(範囲,検索条件,[合計範囲])
SUMIF関数を使って、インクの種類ごとに在庫変動履歴を全て足すことにします。
実装
インク在庫一覧の「在庫」列のセルには、以下のワークシート関数を記述します。
=SUMIF(F4:F999,"青",H4:H999) // 在庫一覧「青」
=SUMIF(F4:F999,"赤",H4:H999) // 在庫一覧「赤」
=SUMIF(F4:F999,"黄",H4:H999) // 在庫一覧「黄」
=SUMIF(F4:F999,"黒",H4:H999) // 在庫一覧「黒」
SUMIF関数で参照する範囲は、現在の「在庫変動履歴」表よりずっと広く取っています。
それは、これからインクが増減するたびに「インク在庫変動履歴」に履歴が追加されるため、追加される履歴の範囲を見込んで、参照範囲を広くとったのです。
テスト
インク在庫一覧の黄色の在庫数が増えている事が確認できます。
環境
Excel online