[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『賞味期限を早い順に処理する在庫管理表を作りたい。』（うめ）
Windows8 Excel2003です。
食品関係で、賞味期限付きの在庫管理表を作るように指示されました。
本やサイトを参考にしたのですがわからず、質問させていただきました。
http://www.excel.studio-kazu.jp/kw/20130228133157.html
過去の質問を見て作成を試みたのですが、うまくいきません。
概ね上記リンク先で納得できる在庫管理表ができたのですが、改良したい点があります。
・sheet3で全ての製品が出庫処理されるので、以前に処理した製品は処理しないようにしたい。
・sheet1で在庫数が0になった在庫数と賞味期限を消去する。
・レイアウトの変更。
上記3点です。
以下、考えているレイアウトです。
sheet1(製品在庫)
B C D E F G H I J K
1 商品名 商品名2 品番 総在庫 在庫 賞味期限 在庫 賞味期限 在庫 賞味期限
2 ｼﾞｭｰｽ 360ml a-1 250 50 20150401 100 20150501 100 20130801
3 牛乳 150ml b-1 150 50 20150409 100 20150801
4 お茶 150ml c-1 100 100 20150610
sheet2(製品入庫)
B C D E F G H I
1 商品名 商品名2 品番 入庫日 仕入れ先 入庫数 賞味期限 備考
sheet3(製品出庫)
B C D E F F H
1 商品名 商品名2 品番 出庫日 納入先 出庫数 備考
Aには商品コードを入力し、B,C,Dに自動入力されるように関数[vlookup]を使用することを考えています。
使用コードは下記の通りです。
Sub 入庫()
Dim c As Range
Dim x As Variant
Dim z As Long
Dim j As Long
Dim k As Long
Dim shZ As Worksheet
Application.ScreenUpdating = False
Set shZ = Sheets("製品在庫") '在庫シート
With Sheets("製品入荷") '入庫シート
For Each c In .Range("D2", .Range("D" & .Rows.Count).End(xlUp))
x = Application.Match(c.Value, shZ.Columns("D"), 0)
If IsNumeric(x) Then '存在
z = shZ.Cells(x, shZ.Columns.Count).End(xlToLeft).Column
For j = 6 To z + 2 Step 2
If shZ.Cells(x, j + 1).Value = c.Offset(, 2).Value Or _
Len(shZ.Cells(x, j + 1).Value) = 0 Then
'在庫数量の足しこみ
shZ.Cells(x, j).Value = shZ.Cells(x, j).Value + c.Offset(, 1).Value
'賞味期限
shZ.Cells(x, j + 1).Value = c.Offset(, 2).Value
shZ.Cells(2, j).Resize(, 2).Value = Array("在庫数", "賞味期限")
Exit For
End If
Next
shZ.Cells(x, "E").Value = calTotal(shZ, CLng(x))
Else '新規商品
With shZ
x = .Range("D" & .Rows.Count).End(xlUp).Row + 1
.Range("B" & z).Resize(, 3).Value = c.EntireRow.Range("B1:D1").Value
.Range("F" & z).Resize(, 2).Value = c.tntirerow.Range("E1:F1").Value
.Range("E" & z).Value = calTotal(shZ, z)
End With
End If
Next
End With
shZ.Select
Application.ScreenUpdating = True
MsgBox "入庫処理がおわりました"
End Sub
Sub 出庫()
Dim c As Range
Dim x As Variant
Dim z As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim n As Long
Dim dt As Long
Dim shZ As Worksheet
Dim dtV() As Long
Dim qtV() As Long
Dim outQ As Long
Dim newV() As Variant
Application.ScreenUpdating = False
Set shZ = Sheets("製品在庫") '在庫シート
With Sheets("製品出荷") '出庫シート
For Each c In .Range("D2", .Range("D" & .Rows.Count).End(xlUp))
outQ = c.Offset(, 1).Value
If outQ = 0 Then
MsgBox c.Value & " の出庫数量がゼロ。スキップします"
Else
'在庫シートの存在チェック
x = Application.Match(c.Value, shZ.Columns("D"), 0)
If Not IsNumeric(x) Then
MsgBox c.Value & " が在庫シートにありません。スキップします"
Else '存在
z = shZ.Cells(x, shZ.Columns.Count).End(xlToLeft).Column
n = (z - 5) / 2 '賞味期限数
ReDim dtV(1 To n)
ReDim qtV(1 To n)
k = 0
For j = 6 To z Step 2
k = k + 1
qtV(k) = shZ.Cells(x, j).Value
dtV(k) = shZ.Cells(x, j + 1).Value
Next
'有効期限の古いものから処理
For i = 1 To n
dt = WorksheetFunction.Small(dtV, i) '★訂正
k = WorksheetFunction.Match(dt, dtV, 0)
If i = n Or outQ <= qtV(k) Then
qtV(k) = qtV(k) - outQ
outQ = 0
Exit For
Else
outQ = outQ - qtV(k)
qtV(k) = 0
End If
Next
'更新結果を行イメージに変換
ReDim newV(1 To n * 2)
k = 0
For i = 1 To n
k = k + 1
If qtV(i) <> 0 Then newV(k) = qtV(i) '在庫数
k = k + 1
newV(k) = dtV(i) '賞味期限
Next
'更新結果の書き込み
shZ.Cells(x, "F").Resize(, n * 2).Value = newV
shZ.Cells(x, "E").Value = calTotal(shZ, CLng(x))
End If
End If
Next
End With
shZ.Select
Application.ScreenUpdating = True
MsgBox "出庫処理がおわりました"
End Sub
Private Function calTotal(sh As Worksheet, i As Long) As Long
Dim x As Long
For x = 6 To sh.Cells(i, sh.Columns.Count).End(xlToLeft).Column Step 2
calTotal = calTotal + sh.Cells(i, x).Value
Next
End Function
以上です。お力添えの程よろしくお願いします。
＜ 使用 Excel：Excel2003、使用 OS：Windows8 ＞
回答ではないんだけど ・最近ぶらっとさん拝見していない ・食品の賞味期限管理で万が一間違いがあっても責任取れない ・有料のものが出ている位めんどくさい作業
の3点からして、回答していくれる人少ないんじゃないかなぁ・・・
少なくとも、自分で変更できない・扱えないレベルなら、やめたほうがいいと思う。 （稲葉） 2015/04/06(月) 11:51
処理済みの案だけ。新しい列を追加し、処理する際に1とか、日付とかをセット。処理済みフラグ、とでもすると良いでしょう。
（処理時は、ここが空欄の行だけ対応する）
（???） 2015/04/06(月) 11:56
sheet1(製品在庫)
B C D E F G H I J K
1 商品名 商品名2 品番 総在庫 在庫 賞味期限
2 ｼﾞｭｰｽ 360ml a-1 50 20150401
3 ｼﾞｭｰｽ 360ml a-1 100 20150501
4 ｼﾞｭｰｽ 360ml a-1 100 20130801
5 牛乳 150ml b-1 50 20150409
6 牛乳 150ml b-1 100 20150801
7 お茶 150ml c-1 100 20150610
の方がいいと思います
（あ） 2015/04/06(月) 16:45
稲葉さん
やはり難しいですよね・・・
有料のものを合わせて検討していきます。
自分で変更し扱えるレベルになるように勉強していきます。
???さん
わからない点は、入庫・出庫したときに前回の入庫・出庫指示が今回の指示と重複しないようにしたいことです。何度かやってみたのですが、重複する時が何度もあるのです。
例
ジュースを20150415に100個入庫します。この時点で在庫が100となります。またジュースを20150420に50個入庫します。この時点で在庫が150になるはずなのですが、前回の分も合わさって在庫が250になります。これを改善したい。
あさん
私の勘違いでしたら申し訳ございません。
H,Ii以降には在庫や賞味期限は書かないほうがよいということでしょうか？
私が考えていたのは、下記のように賞味期限が違うものは列をずらして、賞味期限ごとに商品を管理したいのです。また先入れ先出しの管理をしたいので出荷をした場合は、古い商品から在庫を減らし、在庫がなくなったものは列を左に寄せて、新しく更新していきたいと考えています。
E F G H I E F G
総在庫 在庫 賞味期限 在庫 賞味期限 55出荷後 総在庫 在庫 賞味期限
70 50 20150401 20 20150415 → 15 5 20150415
私の勉強不足なため、皆様にわかりくい質問をしてしまいました。
これからも勉強を続け、わからないところをわかるレベルにまで達したと感じたら、また改めて質問させていただきます。
ありがとうございました。
（うめ） 2015/04/06(月) 21:45
まだ、閉じるのは早計ですよ。
＞食品関係で、賞味期限付きの在庫管理表を作るように指示されました。 と云う事情なんですよね？ そんな簡単に引込めるんですか？
他に出なければ、私がやりますけど、 多分、月光仮面みたいな回答者が登場すると思います。
もうしばらくお待ちを。
※追加回答を待っている旨を書き込んでおいた方がいいですよ。 質問者が見にこないと思えば、回答が無駄になるので誰も案を考えません。
（半平太） 2015/04/06(月) 21:59
過去ログを引き合いに出して質問するよりも、各シートの詳細なレイアウトをアップして、 (決定してなければ話にならないけど) まるっきり新しい質問としてスレを立てた方がいいんじゃないかな？
（seiya） 2015/04/06(月) 23:55
今回のレイアウトは前回の人の影響が強いので、自分が考えているレイアウトをアップして新しくスレを立てたいと考えています。
その際はお力添えをいただければと思います。
seiyaさん
＞過去ログを引き合いに出して質問するよりも、各シートの詳細なレイアウトをアップして、
＞(決定してなければ話にならないけど)
＞まるっきり新しい質問としてスレを立てた方がいいんじゃないかな？
自分の考えをもう少しまとめて、新しくスレを立てます。
皆様アドバイスありがとうございました。
まだ諦めるのは早いと考え、新しい質問として自分なりのレイアウトをアップしたスレを立てます。その際は、皆様のお力添えのほどよろしくお願いします。
初歩的な質問で申し訳ありませんが、このスレは削除など何かしたほうがよろしいのでしょうか？
（うめ） 2015/04/07(火) 08:39
最初に色々書いたけど、（あ）さんが投稿されたように、在庫の表は横に伸びるより縦に伸びたほうが 楽です。
いろんなところで書くけど、見るための表と蓄積するデータは別に考えてください。 で、ぶらっとさんが懸念されているところは考慮しましたか？
実在庫と合わなくなる、取り消ししたい等・・・
私が作るなら マスタシート ・・・ 品番に対する品名などが書かれた表 入出庫履歴シート ・・・ 入出庫の伝票を蓄積していく 入出庫入力シート ・・・ 決められた項目のみ入力できるシートで、入出庫履歴に転記するためのシート 在庫表シート ・・・ 入出庫履歴から差し引きして、品番と賞味期限の組み合わせで在庫を算出する
この4つで管理します。 必要であれば、在庫表から品番ごとに抜き出すシートや、賞味期限が早いほうから表示するシートなどを 別途追加していきます。
何にもわからないならレイアウトの相談 → 構築の相談と順を追って進めたほうがいいと思います。
ちなみにスレを消す必要はございませんよ。 次に新しく立てるときにこのスレのリンクを張れば、どういう経緯で来たのかわかりやすいので。 （稲葉） 2015/04/07(火) 08:48
＞最初に色々書いたけど、（あ）さんが投稿されたように、在庫の表は横に伸びるより縦に伸びたほうが
＞ 楽です
縦のほうが楽なのですね。勘違いしていました。
いろいろとありがとうございます。
現在レイアウトを考えていたので、とても参考になります！
ぶらっとさんの懸念も、今現在の在庫表で一部発生(実在庫と合わなくなる）しています。
これらの懸念を考慮していきます。しかし、今の自分の力では後手後手に対応していく状況になりそうです・・・
＞ちなみにスレを消す必要はございませんよ。
＞次に新しく立てるときにこのスレのリンクを張れば、どういう経緯で来たのかわかりやすいので。
わかりました。何から何までありがとうございます。
（うめ） 2015/04/07(火) 09:18
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.