エクセル 先出先入 (最古日付を表示)

質問

エクセルでの在庫管理について教えて下さい。
シート内容として、商品名・出荷数・入庫数・在庫数があります。
毎月の、出荷数・入庫数・在庫数が列に追加で記載されていきます。
先入れ先出しを行いたい為、当月末(M列)の在庫数から、その在庫の一番古い入庫月を求める計算式をお教えください。

入庫は、在庫が0になってから補充する場合と、在庫が0になる前に補充する場合がございます。
宜しくお願いします。

回答No.1

kkkkkm · ベストアンサー

出荷数の合計から最初の月(1月)の入庫数を引く
マイナスでなければ上記の差から翌月の入庫数を引く
それを続けてマイナスになった月が一番古い入庫月

(在庫が0になった後の月が一番古い入庫月と思ったのですが前月がない1月が対応できない気がしたので)

と考えた場合、計算式は分からないのでマクロでしたら
N3を選択した状態で実行するとN5(最後の行)まで計算します。毎月表示したい一番上のセル(画像だと3行目)を選択して実行します。

Sub Test()
Dim S As Range, c As Range, d As Range
Dim TotalS As Long

If Selection.Value <> "" Then
If MsgBox("既にデータがあります。続けますか？", vbYesNo + vbInformation) = vbNo Then
Exit Sub
End If
End If

Set S = Selection

For Each d In Range(Cells(3, "A"), Cells(Rows.Count, "A").End(xlUp))
TotalS = WorksheetFunction.SumIf(Range(Cells(2, "B"), Cells(2, S.Column - 1)), "出荷数", Range(Cells(d.Row, "B"), Cells(d.Row, S.Column - 1)))
Cells(d.Row, "P").Value = TotalS
For Each c In Range(Cells(d.Row, "B"), Cells(d.Row, S.Column - 1))
If Cells(2, c.Column).Value = "入庫数" Then
If TotalS - Cells(d.Row, c.Column).Value < 0 Then
Cells(d.Row, S.Column).Value = Cells(1, c.Column).Value
Exit For
Else
TotalS = TotalS - Cells(d.Row, c.Column).Value
End If
End If
Next
Next
End Sub

補足

kkkkkm様

度々すみません。
先程のものですと、N列4行目(みかん)の一番古い入庫月は、1月と表示されるのですが、正しくは、3月になります。

宜しくお願い致します。

回答No.4

kkkkkm

補足です。

1月と表示されるのがどこのセルを表示しているのか確認のために以下のコードを追加してみてください。

Cells(d.Row, S.Column).Value = Cells(1, c.Column).Value
の後に
Cells(d.Row, S.Column + 1).Value = Cells(1, c.Column).Address(0, 0)
Cells(d.Row, S.Column + 2).Value = Abs(TotalS - Cells(d.Row, c.Column).Value)
の2行を入れてください。

N列の横
O列に参照したセル番地が入ります。そこに記載されているデータをN列に転記してます。
P列には今ある在庫のうち上記で出た最古月としたときの在庫分で現在在庫にある数量が出ます。ただし、計算が合っていればですが…。

お礼

kkkkkm様

申し訳ございません、こちらのアイテムの表記に誤りがあった為、上手く作動しなかったようです。
先程、修正したところ、正しく表示されました。

ご丁寧に教示くださりありがとうございました。
大変助かりました。

回答No.3

kkkkkm

> N列4行目(みかん)の一番古い入庫月は、1月と表示されるのですが、正しくは、3月になります。

こちらでは3月になります。入庫数の上の月を表示していますが、3月の所が1月になっていることはないですよね。

> 当月末の在庫がゼロになる月(今回だとM列の7行目)の、一番古い入庫月は、表示無しにしたいのですが、可能でしょうか。

画像にM列7行目は無いのですが…。
在庫が0になるという事は出荷数のトータルから入庫数のトータルを引くと0になると思います。
出荷数のトータルから毎月の入庫数を引いていき、0未満になったときに該当月を記載しますので、上記の状態(結果が0未満にならない状態)だと何も表示されないはずですが、念のために0であれば何もしない状態に変更したコードとこちらで実行した結果の画像を出しておきます。

3月表示の件については、こちらで1月にならないので残念ながら今のところ手を付けられません。

Sub Test()
Dim S As Range, c As Range, d As Range
Dim TotalS As Long

If Selection.Value <> "" Then
If MsgBox("既にデータがあります。続けますか？", vbYesNo + vbInformation) = vbNo Then
Exit Sub
End If
End If

Set S = Selection

For Each d In Range(Cells(3, "A"), Cells(Rows.Count, "A").End(xlUp))
If Cells(d.Row, S.Column - 1).Value <> 0 Then
TotalS = WorksheetFunction.SumIf(Range(Cells(2, "B"), Cells(2, S.Column - 1)), "出荷数", Range(Cells(d.Row, "B"), Cells(d.Row, S.Column - 1)))
For Each c In Range(Cells(d.Row, "B"), Cells(d.Row, S.Column - 1))
If Cells(2, c.Column).Value = "入庫数" Then
If TotalS - Cells(d.Row, c.Column).Value < 0 Then
Cells(d.Row, S.Column).Value = Cells(1, c.Column).Value
Exit For
Else
TotalS = TotalS - Cells(d.Row, c.Column).Value
End If
End If
Next
End If
Next
End Sub

回答No.2

kkkkkm

No1　一部訂正です。
Cells(d.Row, "P").Value = TotalS
は不要ですので消しておいてください。
また、結果がおかしいとか考え方がおかしい場合、そのあたりを教えてただくと、マクロでよろしければお手伝いできると思います。

補足

kkkkkm様

ご教示頂きありがとうございます。
お教え頂いたマクロをコピペしたのですが、結果が一部正しくありません。
何が影響しているのでしょうか。
また、当月末の在庫がゼロになる月(今回だとM列の7行目)の、一番古い入庫月は、表示無しにしたいのですが、可能でしょうか。

宜しくお願い致します。