エクセル在庫数量の更新について

質問

エクセル在庫数量の更新について

(1)生産日ファイルと(2)集計表ファイルを利用して商品の在庫管理をしています。

(1)生産日ファイルには、Ｄ列に倉庫・Ｅ列に商品・Ｆ列に日付・Ｇ列に朝の時点での
在庫数量の情報が入っています。
(2)集計表ファイルには、翌日に出荷する予定が入っています。
Ａ列に商品・Ｂ列に商品名称・C列～Ｈ列は倉庫Ａ～Ｆからの出荷予定・Ｉ列は商品ごとの
合計が入っています。

この(2)集計表ファイルの情報をもとに、製造年月日が古いものから無くなるように
(1)生産日ファイルの情報を更新したいと思っています。

画像でいうと、
(2)集計表ファイル「あ」の商品が「Ａ」の倉庫で10個出荷される予定になり、
(1)生産日ファイル「Ａ」の倉庫に「あ」の商品は 20100501生産品 25個を15個にする。

(2)集計表ファイル「う」の商品が「Ａ」の倉庫で30個出荷される予定になり、
(1)製造年月日ファイル「Ａ」の倉庫に「う」の商品は「 20100401生産品 5個と 20100420生産品
15個と 20100425生産品 30個 の 計 50個」から「20100425生産品 20個 の 計 20個」にする。

といったようにしたいと思っています。

マクロでも関数でも良いのですが、何か良い方法を教えていただけないでしょうか？

回答No.2

nattocurry · ベストアンサー

生産日ファイルでは、同じ倉庫で同じ商品の場合、日付の古い順に並んでいるのを前提としています。

計算の結果、生産日ファイルの数量が0になった行は削除するようになっています。
削除したくない場合は、コード中の該当箇所を削除してください。

計算の結果、集計表ファイルの値も変更しています。
変更したくない場合は、コード中の該当箇所を削除してください。

このコードは、同じファイルに、「生産日」というシートと「集計表」というシートがある、という条件で作りました。
別々のファイルであれば、
冒頭部分を
Set Sh0 = Workbooks("生産日ファイルのファイル名").Worksheets("シート名")
Set Sh1 = Workbooks("集計表ファイルのファイル名").Worksheets("シート名")
というように変更する必要があります。

WEB上で見栄えを良くするために、半角スペース2個を全角スペース1個に置換しています。
実際に使う場合は、元に戻してください。元に戻さないとエラーになります。

Sub test()
　　Dim Sh0 As Worksheet, Sh1 As Worksheet
　　Set Sh0 = Worksheets("生産日")
　　Set Sh1 = Worksheets("集計表")

　　Dim t As Range
　　Dim 商品 As String, 倉庫 As String, 出荷 As Long
　　Dim r As Long
　　With Sh1
　　　　For Each t In .Range(.Range("C6"), .Range("H10"))
　　　　　　出荷 = t.Value
　　　　　　商品 = t.EntireRow.Cells(1).Value
　　　　　　倉庫 = t.EntireColumn.Cells(5).Value

　　　　　　With Sh0
　　　　　　　　r = 3
　　　　　　　　Do Until .Cells(r, 1).Value = "" Or 出荷 = 0
　　　　　　　　　　If .Cells(r, 1).Value = 倉庫 And _
　　　　　　　　　　 .Cells(r, 2).Value = 商品 Then
　　　　　　　　　　　　If .Cells(r, 4).Value >= 出荷 Then
　　　　　　　　　　　　　　.Cells(r, 4).Value = .Cells(r, 4).Value - 出荷
　　　　　　　　　　　　　　出荷 = 0
　　　　　　　　　　　　Else
　　　　　　　　　　　　　　出荷 = 出荷 - .Cells(r, 4).Value
　　　　　　　　　　　　　　.Cells(r, 4).Value = 0
　　　　　　　　　　　　End If
　　　　　　　　　　End If
　　　　　　　　　　r = r + 1
　　　　　　　　Loop
　　　　　　End With

　　　　　　'集計表の値を変えたくなかったら、この下のコード1行を削除してください
　　　　　　t.Value = 出荷
　　　　Next t
　　End With

　　'数量が0の行を削除
　　'削除したくない場合はこの下のコード5行を削除してください
　　With Sh0
　　　　For r = .Cells(.Rows.Count, 4).End(xlUp).Row To 3 Step -1
　　　　　　If .Cells(r, 4).Value = 0 Then .Rows(r).Delete
　　　　Next r
　　End With

End Sub

お礼

思っていた通りのことが出来ました。
教えていただいてありがとうごいざいます。

マクロをもっともっと勉強していきます！

回答No.3

layy

生産日ファイルにて、出荷予定数Ｈ列、残在庫数Ｉ列、出荷日Ｊ列など変動を計算させるための情報列を用意し、業務終了でＩ列からＧ列のマクロ更新。
まずは、必要となる値はセルへＰＵＴする。計算で出来るかはその次、この考え方のが無難と思います。

ほかの回答がとんな結果かは検証してないのでわかりません。解説をもらって下さい。

更新の更新で２重に引かれたりしないようにしないと。

回答No.1

layy

例えば、
出荷予定量＝１０に対し、実際は＝２０や＝５となる可能性ありますか？。
現在在庫２５→出庫３０→入庫２０、となるとき最終値１５だけ管理していますか？。
（同じ生産日であるとき、一時的にマイナス在庫も考慮必要）
実際の出荷作業はエクセルの入力した後ですか、前ですか？。

これらを考えると
生産日ファイル、集計表ファイル、は元になるデータ群ですので、
経緯も残していない状態でリアルタイムでこの２シートを更新するのはどうかと思います。
基本的には１日１回更新、とした方が良いです。
シートをもう一つ準備し随時入力しリアルタイムの在庫状況を管理する、
その日の業務が終わったら、
それらを生産日ファイルと集計表ファイルに反映する運用をおススメします。

シート１、シート２からデータを取得、算出しシート３に結果を表示する
という機能は可能です。

補足

＞出荷予定量＝１０に対し、実際は＝２０や＝５となる可能性ありますか？。
あります。その際には関数やマクロを利用しないで手で訂正入力するつもりです。

＞現在在庫２５→出庫３０→入庫２０、となるとき最終値１５だけ管理していますか？。
入庫は毎日ではないので手で訂正入力しています。

＞実際の出荷作業はエクセルの入力した後ですか、前ですか？
実際に出荷されるのは、エクセルに入力された後です。
出荷数量が確定してから個人的に持っている生産日情報を更新しています。