Excelで在庫表を作ろうと思っています

Excelで在庫の管理表を作っていようと思っています。

在庫の管理をする上で日毎の使用数があるのですが、
使用した数を古い日付の在庫から順々に引いてく関数はありませんか？

添付画像のように上から

ロット（製造日　１日から月末まで横並びに並んでいます）
在庫　（その日その日の出来高を入力する　B3セルで言うと10/1に「1」モノが出来た）
総在庫（その日までの合計数が表示される　D6セルで言うと10/1の出来高「1」+10/2の出来高「2」+10/3の出来高「3」＝「6」になります）
使用数（その日に使用した数量を入力する　D6セル、10/3に「4」モノを使用した）

となっていて、
お聞きしたい事は

10/4に「4」の使用数があって
その「4」を一番古い在庫、添付画像で言うと10/1の「1」　から引き算をはじめ
次に　10/2の「2」、10/3の「3」　のように順々に引き算をして、
その際　添付画像3列目の　在庫　の値は計算の結果の値が表示させるようにしたいです。
（計算の結果が　「0」　の場合は　「0」　として在庫が無いとわかるようにして、
　使用数が在庫の数を上回る場合は　－表記がされるようにしたいです。）
古い日付、あるいは「0」及び「マイナスの値」から引き算がスタートされるような感じ　と言えばいいのでしょうか…

更に、使用日当日の在庫は計算には組み込まれないようにしたいです。
（10/4に「4」使用する→　10/1－10/2－10/3＝答え　のように）

仕組み上使用したモノの日付が分かるようになっていればここまで悩むことも無いのですが、
日付が分からないので”一番古い日付”の在庫から引き算を出来るようになれば…と思い質問させていただきました。

説明が分かりづらい上に長々とした文章で申し訳ありませんが、
分かる方教えていただけますでしょうか。

よろしくお願いいたします。

質問者からの補足コメント

• 入庫にあたるのが　在庫　で
  出庫にあたるのが　使用数　です。
  
  3列目の　総在庫　は　該当セルの日を含めた
  その日以前の在庫数の合計数を表示するセルになります。

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/10/29 10:07

• 古い日付の在庫が残っているかどうか　を
  一目見てわかる様にしたいので、
  使用数が入力された場合は古い日付の在庫から引き算をする様にしたいんです。
  
  当日の在庫　を計算する場合その数式でやれることは理解ができました。
  
  ですが私がやりたいことは　使用があった場合
  古い日付から引き算をしていき
  どの日の在庫が残っているか　を一目でわかるようにしたいのです。
  
  あー、、、
  それは質問用に別シートにコピペした際に、
  値のみの貼り付けでやったんで関数が生きていませんでした…
  
  申し訳ございません。

  No.2の回答に寄せられた補足コメントです。 補足日時：2018/10/30 13:28

No.3 ベストアンサー 回答者： 銀鱗 回答日時： 2018/10/30 16:13

＞どの日の在庫が残っているか　を一目でわかるようにしたいのです。

それを先に言わなきゃダメでしょ。
質問文からはどうやっても読み取ることのできない情報です。

そして、これ、結構めんどくさいですよ。
難しくはありません。面倒です。
説明が非常に長くなりますが、必要な情報をすべて伝えるために必要な事です。
数式だけをコピペしても良いですが、それでは条件が変わったときに対処できずまたここで質問することになります。
それって、無駄でしょ？
この場で覚えてしまいましょう。


・・・本題・・・

考え方は、
「総在庫」数の最新データと
最新ロットから過去に向かってロット毎に合計を求め、
その各合計をと比較。
そんだけです。

ただし、使うのは
　COLUMN関数
　COUNT関数
　INDEX関数
　OFFSET関数
　SUM関数
と結構な種類を用います。
各関数の使い方を確実に理解してください。

関数の説明はしませんので、条件が変わったらどこを変えたらいいかなんて質問は絶対にしないでください。
（理解してないから代わりに作ってくださいという質問をしないでね…ということです）

・・・
まず、「総在庫」のデータ数を求めます。
　COUNT($B$4:$AF$4)
など。
次に、最新のデータを求めます。
　INDEX($B$4:$AF$4,0,COUNT($B$4:$AF$4))
ただし「総在庫」は最新ロットに対して必ず入力されるものとします。
これで最新の「総在庫」数は求めることができました。

そしてロット毎の合計を求めるためにSUM関数に対してOFFSET関数で合計範囲を指定します。
範囲の基準は最新ロットのセルです。
「総在庫」数から最新ロットのセルを推測します。
この数からOFFSET関数の基準セルをA3セルにすることで簡単に指定できます。
　OFFSET($A$3,0,COUNT($B$4:$AF$4))
これはまったく難しくありません。

続いてここから範囲を決めます。（この考え方がメンドクサイ）
質問に添付されている画像で、10月4日が最新のロットとするなら、
10月4日のロットに対しては
　E3:E3
10月3日のロットに対しては
　D3:E3
10月2日のロットに対しては
　C3:E3
10月1日のロットに対しては
　B3:E3
の範囲を返す必要がある。
OFFSET関数の5つ目のパラメータにこの範囲の幅を入れてやればいい。
10月4日のロットに対しては
　１
10月3日のロットに対しては
　２
10月2日のロットに対しては
　３
10月1日のロットに対しては
　４
ということ。
面倒なので「総在庫」数のデータ数とそれぞれのセルの列番号を返すCOLUMN関数を使います。
基本は
　COUNT($B$4:$AF$4)-COLUMN()
しかしCOUNT関数が参照する範囲にはA列が含まれないことと、COLUMN関数で得た数字を引いた場合、そのセルを示す値を残すため+1しなければならないため
　COUNT($B$4:$AF$4)-COLUMN()+2
となります。
さらにOFFSET関数で範囲の始点としたセルから右方向ではなく左方向に範囲を広げるため範囲の指定をマイナスにする必要があります。
すると
　OFFSET($A$3,0,COUNT($B$4:$AF$4),1,-1*(COUNT($B$4:$AF$4)-COLUMN()+2))
となります。
これで範囲はOK。SUM関数で合計を求めることができます。
　SUM(OFFSET($A$3,0,COUNT($B$4:$AF$4),1,-1*(COUNT($B$4:$AF$4)-COLUMN()+2)))


あとは、この二つを比較するだけ
　＝　SUM(OFFSET($A$3,0,COUNT($B$4:$AF$4),1,-1*(COUNT($B$4:$AF$4)-COLUMN()+2)))　＜　INDEX($B$4:$AF$4,0,COUNT($B$4:$AF$4))
この数式を「在庫」の範囲のセルに
「条件付き書式」の「数式を使用して、書式設定するセルを決定」
の数式に指定して、セルを赤く塗りつぶすなどセルの修飾方法を設定してください。
在庫が使われている、または使い切ったロットのセルが修飾されます。

この回答へのお礼

詳しく説明していただきありがとうございます。
時間が無くまだ途中までしか試せていない為お礼が遅くなりました。

教えてくださった関数を使い自分の力でやってみますありがとうございました。

お礼日時：2018/11/03 10:28

No.2 回答者： 銀鱗 回答日時： 2018/10/29 17:25

在庫→入庫

総在庫→在庫
仕様数→出庫
…とすべきだよなあ。

でもってどう考えても
　(当日の在庫)＝(前日の在庫)+(当日の入庫)-(当日の出庫)
なんだよなあ。
古い日付の入庫から引き算する必要はない。

考え方を改めるべきと思います。

・・・
てか、質問の例なら、E4セルの値は6でなきゃダメだろ？

No.1 回答者： googoo900 回答日時： 2018/10/28 01:10

入庫と出庫があって、在庫が計算できます。

言い換えると、入庫と出庫がないと在庫はわかりません。
出庫は添付画像の『使用数』のことでしょうか？
また、この添付画像には入庫がありません。

>その際　添付画像3列目の　在庫　の値は計算の結果の値が表示させるようにしたいです。

添付画像の『在庫』が入庫のことかとも推測しましたが、添付画像3列目の『在庫』を式で計算したいということは入庫のことではないということでしょうか？

確認したいのは、入庫と出庫はどれのことかということです。