Excel 先入れ先出し残在庫について

質問

Excelでの在庫管理について教えてください。

シート内容として、Sheet1に区分・商品名・入荷数・出荷数・在庫数があります。
毎月、出荷数・入荷数・在庫数が列に追記されていきます。

先入れ先出しを行い、長期滞留の在庫数を把握したいため、
sheet2に当月末(sheet2のA1セル)の在庫の入庫日が3ヵ月以上前の入庫月と、その在庫残数を表示したく、その計算式をお教え頂きたくお願い致します。(添付、黄色部分が求めたい入庫月と在庫数量となります。)

回答No.2

SI299792 · ベストアンサー

作業領域を１商品１列にできました。これで、Sheet3は廃止できます。

B3: 空白にして結合しないでください。
B4: =Sheet1!B2
B3～B5を下へコピペ。

C3: =B3+(Sheet1!C2>0)*(SUM(Sheet1!$C2:C2)-SUM(Sheet1!3:3)>0)
C4: =IFERROR(OFFSET(Sheet1!$C$1,,MATCH(COLUMN()-2,3:3,0)-3),"")
表示形式、ユーザー定義｢yyyy/m｣
C5: =IFERROR(MIN(OFFSET(Sheet1!$C2,,MATCH(COLUMN()-2,3:3,0)-3),SUM(OFFSET(Sheet1!$C2,,,,MATCH(COLUMN()-2,3:3,0)-2))-SUM(Sheet1!3:3)),"")
纏めて右下へコピペ。

３、６…行が作業領域です。目障りなら非表示にして下さい。
A3、A6…に! 等を入れておけば、フィルターで非表示にできます。

お礼

ご教示頂きありがとうございます。
大変助かりました。

回答No.1

SI299792

これは、逆算しなければならないので難しいです。
Sheet1が１商品３列なのに、Sheet2が１商品２列。これも難しい。ここは、３列に統一させてください。
１商品に作業列が３列必要です。Sheet3を作ります。

Sheet3
N2: =SUM(Sheet1!2:2)-SUM(Sheet1!3:3)
N2～N4を下へコピペ。
C2: =D2-Sheet1!D2
C3: =B3+(Sheet1!C2>0)*(C2>0)
C4: =MIN(Sheet1!C2,C2)
纏めて右下へコピペ。
１年分を想定しています。もっとある場合、N2を右へ移動して下さい。

Sheet2
B3: =Sheet1!B2
B3～D3を下へコピペ。
C3: =IFERROR(INDEX(Sheet1!$1:$1,MATCH(COLUMN()-2,Sheet3!3:3,0)),"")
C4: =IFERROR(INDEX(Sheet3!4:4,MATCH(COLUMN()-2,Sheet3!3:3,0)),"")
C3～C5を右下へコピペ。