Excelで出荷・在庫管理の方法

質問

仕事でExcelで商品の出荷と在庫の管理表を作るように言われたのですが、次のような場合の関数の書き方を教えてください。

賞味期限がある商品で、出荷指示が出た場合に賞味期限の古い方から自動的に在庫を減らしたいです。

例えば、2011/12/10賞味期限のオレンジジュースが200個、2011/12/25賞味期限のが500個あったとして、300個出荷する時に12/10のから200個、足りない100個を12/25賞味期限のから自動的に差し引かれるようにしたいのです。

[セルA1]オレンジジュース、[B1]2011/12/10(賞味期限)、[C1]200(在庫)
[B2]2011/12/25、[C2]500

IF関数とか考えてみたのですがなかなかうまくいかなくて…

ちなみにExcel2007です。

よろしくお願いしますm(__)m

回答No.1

keithin · ベストアンサー

いまご質問でヤリタイ事：
＞賞味期限がある商品で、出荷指示が出た場合に賞味期限の古い方から自動的に在庫を減らしたい

【大前提】今作成するのは「実際に入庫・出庫が発生した分」の記録です。
　つまり「将来の入庫・出庫予定」は記入しません。

添付図参照：
Ｃ：Ｆ列，Ｉ：Ｋ列は生データを記入する欄です
Ｇ３：
=IF(F3="","",MIN(MAX(0,SUMIF(E:E,"<"&E3,F:F)+SUMIF($E$3:E3,E3,$F$3:F3)-SUM(K:K)),F3))
以下コピー

Ａ４：
=SUM(G:G)
Ａ６：
=SUM(F:F)-SUM(K:K)

ご質問に書いてないオマケ：
　同じ日付で期限がある場合，リストの上から順に減らしていきます

＃お願い
このタイプのご相談で非常に多く見かけますが，「実はこれもしたいあれもしたい」を後出しで追加質問しないでください。たとえば「どの品を出した・出せばいいのか計算で並べたい」とか。
もしもそういう欲が出たときは，まずはこのご質問の内容を解決した上で一度ご相談を解決で閉じ，改めて「次はこういう状況で今度はこういう結果を並べたい」とヤリタイ事をキチンと説明して，次のご相談を遠慮無く投稿してみてください。

お礼

丁寧にありがとうございました！
教えていただいた内容に手を加えながらやってみます！

回答No.2

imogasi

>自動的に差し引かれるようにしたいのです
そのことは世間的に常識なのだが、シートの表の上でどう形にしていくのか、例を挙げてみること。
＞[セルA1]オレンジジュース、[B1]2011/12/10(賞味期限)、[C1]200(在庫)
[B2]2011/12/25、[C2]500
では判らない。これで書き方は完全か？
ーーー
在庫管理を関数でやろうとすると、式が複雑になるように思う。個性の無い腐らない商品ならまだしも。
ーーー
仕事でこんなことをやるなら、ＶＢＡを勉強して、ある程度、熟達してからやることだと思うよ。
ＶＢＡなら表品別＋賞味期限別に並べ、、上の行から取り崩し、残りの在るロット（同一賞味期限分）をどう表示するか。
そのロットが尽きるまでの扱いをどういう表の行・列に表現するか、難しい。都度現在庫に書き換えてしまうのは簡単だが
他での利用を狭めないかなと思う。

お礼

(´・ω・`)