[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『在庫リストと注文リストから、出庫指示票を作りたい。』（ちなつ）
■下記のような在庫リストがあります。(sheet1)
■下記のような注文データがあります。(sheet2)
■やりたいこと。
注文データから、下記のようなデータを作りたいです。
AAA-1のように、入荷日が複数あるものは、古いものから先出しで、優先出庫指示を行いたい。
AAA-3のように、在庫が無いものは、別シート(sheet4)で製作指示を行いたい。
(出庫指示書:sheet3,製作指示書:sheet4)
製作指示書
品番 数量 注文年月日 指定納期 AAA-3 10 20230317 20230406
■補足のご説明
在庫(sheet1)と注文書(sheet2)は、各部署で、別エクセルで管理していますので、手動で都度コピーします。(列の配置は変わりません。)
出荷指示、製作指示を出力後でも、sheet1とsheet2は、変動(出荷後の在庫数に変更)させなくてよいです。(本当は、やりたいですが。)
IDは、現場が複数あるので、社内のバーコード管理番号です。品番とも一致しません。
箱番号、棚番号は現場の材料置き場のアドレスです。棚が空いているところに入れるので、品番ごとに毎回同じアドレスとは限りません。
注文書のデータ(納品先、ロットナンバー)などは、今回関係ありませんが、データとして入っています。(注文書データを、コピペしたいので、残る。)
現場の部材のアドレスを統一したり、在庫リストの中で在庫を纏めなさい。というお声はあると思いますが、現状、出来上がった品物をエクセルの下に足していくのが精いっぱいとなっています。
マクロになると思いますが、どなたか、教えていただけませんでしょうか。
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
（とこなつ） 2023/03/21(火) 16:54:46
行列は下記の指定でよろしいでしょうか？
■Sheet1 IDがA1 品番がB1・・・ 10001がA2 AAA-1がB2です。
ID 品番 数量 箱番号 棚番号 入荷日
10001 AAA-1 20 1 A1 2023/3/13
10002 AAA-2 300 2 A1 2023/3/13
20001 AAA-1 100 3 A1 2023/3/14
20002 AAA-4 30 4 A1 2023/3/10
30001 AAA-5 50 5 A2 2023/3/15
■Sheet2 納入指定場所がA1 納入場所名称がB1・・・ ゲート１がA2 九州１がB2です。
納入指定場所 納入場所名称 ロケーション ロケーション名称 注文年月日 ロットＮＯ 品番 指定納期 指定数量
ゲート１ 九州１ 51 〇〇営業所 20230317 3315740 AAA-1 20230403 50
ゲート２ 九州１ 51 〇〇営業所 20230317 3315740 AAA-2 20230404 10
ゲート３ 九州１ 51 △△営業所 20230317 3315741 AAA-3 20230405 10
ゲート１ 九州２ 51 △△営業所 20230317 3315741 AAA-4 20230406 20
ゲート２ 九州３ 53 △△営業所 20230317 3315742 AAA-5 20230407 20
■Sheet3 IDがA1 品番がB1・・・ 10001がA2 AAA-1がB2です。
ID 品番 数量 箱番号 棚番号 入荷日 注文年月日 指定納期 出庫数 残
10001 AAA-1 20 1 A1 2023/3/13 20230317 20230403 20 0
10002 AAA-2 300 2 A1 2023/3/13 20230317 20230404 10 290
20001 AAA-1 100 3 A1 2023/3/14 20230317 20230403 30 70
20002 AAA-4 30 4 A1 2023/3/10 20230317 20230406 20 10
30001 AAA-5 50 5 A2 2023/3/15 20230317 20230407 20 30
■Sheet4 品番がA1 数量がB1・・・ AAA-3がA2 10がB2です。
品番 数量 注文年月日 指定納期
AAA-3 10 20230317 20230406
よろしくお願いいたします。
（ちなつ） 2023/03/21(火) 17:14:06
＞AAA-1のように、入荷日が複数あるものは、古いものから先出しで、優先出庫指示を行いたい。
「古いものから」と言っても、在庫表の上から割り当てればいいですよね？ ※つまり、品番が同じなら、上の方が古い順になっていますね？
注文データのサンプルでは、品番に重複がないですが、実際も重複はないですか？ 注文データのサンプルでは、注文年月日が全て同じですが、実際もそうなんですか？ ※注文日付が同じとは限らない場合、日付は昇順に並んでいますね？
（半平太） 2023/03/21(火) 20:13:47
コメント、ありがとうございます。
在庫表、日付でソートされておらず、古いものが上段にあるときもあれば、下段にあるときもあります。
ですので、可能であれば、品番検索と日付の古いものとの両方で検索したいです。
しかも、お恥ずかしい話ですが、社内の制作場所が違うため、同じ品番で同じ入荷日のものがあります。
(ＩＤと箱番号、棚番号、数量が違います。)
その場合は、どちらから出庫しても問題ございません。
注文データは、品番重複はございます。指定納期と納入先による違いです。
■ですので、注文シートの上から順に、在庫引き当て(出庫指示書 or 製作指示書)を行いたい。イメージです。
注文シートの日付ソートは不要で、上から順で問題ございません。
(指定納期が近いものは、同梱するなどの調整が別で客先と必要なためです。)
(下記最下段に、２行追加いたしました。)
■Sheet2 注文データ
納入指定場所 納入場所名称 ロケーション ロケーション名称 注文年月日 ロットＮＯ 品番 指定納期 指定数量
ゲート１ 九州１ 51 〇〇営業所 20230317 3315740 AAA-1 20230403 50
ゲート２ 九州１ 51 〇〇営業所 20230317 3315740 AAA-2 20230404 10
ゲート３ 九州１ 51 △△営業所 20230317 3315741 AAA-3 20230405 10
ゲート１ 九州２ 51 △△営業所 20230317 3315741 AAA-4 20230406 20
ゲート２ 九州３ 53 △△営業所 20230317 3315742 AAA-5 20230407 20
ゲート１ 九州１ 51 △△営業所 20230317 3315741 AAA-1 20230408 10 ←追記
ゲート３ 九州１ 51 △△営業所 20230317 3315741 AAA-1 20230408 10 ←追記
よろしくお願いいたします。
（ちなつ） 2023/03/22(水) 08:45:13
>■Sheet4 品番がA1 数量がB1・・・ AAA-3がA2 10がB2です。 >品番 数量 注文年月日 指定納期 >AAA-3 10 20230317 20230406 手を抜かないで
A B C D
1 品番 数量 注文年月日 指定納期
2 AAA-3 10 20230317 20230406
と書いたらどうですか。 この様に書くと行列が一目瞭然ですよね。
思案中です。
（とこなつ） 2023/03/22(水) 15:00:13
注文が2行追加になりましたが、 その結果、出庫指示書はどう変わることになるのですか？
（半平太） 2023/03/22(水) 15:04:10
■Sheet1(在庫シート)
A B C D E F 1 ID 品番 数量 箱番号 棚番号 入荷日 2 10001 AAA-1 20 1 A1 2023/3/13 3 10002 AAA-2 300 2 A1 2023/3/13 4 20001 AAA-1 100 3 A1 2023/3/14 5 20002 AAA-4 30 4 A1 2023/3/10 6 30001 AAA-5 50 5 A2 2023/3/15
■Sheet2(注文シート)
A B C D E F G H I J 1 納入指定場所 納入場所名称 ロケーション ロケーション名称 注文年月日 ロットＮＯ 品番 指定納期 指定数量 2 ゲート１ 九州１ 51 〇〇営業所 20230317 3315740 AAA-1 20230403 50 3 ゲート２ 九州１ 51 〇〇営業所 20230317 3315740 AAA-2 20230404 10 4 ゲート３ 九州１ 51 △△営業所 20230317 3315741 AAA-3 20230405 10 5 ゲート１ 九州２ 51 △△営業所 20230317 3315741 AAA-4 20230406 20 6 ゲート２ 九州３ 53 △△営業所 20230317 3315742 AAA-5 20230407 20 7 ゲート１ 九州１ 51 △△営業所 20230317 3315741 AAA-1 20230408 10 8 ゲート３ 九州１ 51 △△営業所 20230317 3315741 AAA-1 20230408 10
■Sheet3(出庫指示シート)
A B C D E F G H I J 1 ID 品番 数量 箱番号 棚番号 入荷日 注文年月日 指定納期 出庫数 残 2 10001 AAA-1 20 1 A1 2023/3/13 20230317 20230403 20 0 3 10002 AAA-2 300 2 A1 2023/3/13 20230317 20230404 10 290 4 20001 AAA-1 100 3 A1 2023/3/14 20230317 20230403 30 70 5 20002 AAA-4 30 4 A1 2023/3/10 20230317 20230406 20 10 6 30001 AAA-5 50 5 A2 2023/3/15 20230317 20230407 20 630 7 20001 AAA-1 70 3 A1 2023/3/14 20230317 20230408 10 60 ←追記 8 20001 AAA-1 60 3 A1 2023/3/14 20230317 20230408 10 50 ←追記
■Sheet4(製作指示シート)
A B C D 1 品番 数量 注文年月日 指定納期 2 AAA-3 10 20230317 20230406
半平太さま
コメント、誠にありがとうございます。
上記、sheet3に2行追記いたしました。
もし、AAA-1のように、複数注文があり、残がマイナスになれば、以降のAAA-1は、sheet4の製作指示シートに製作指示を出したいです。
（ちなつ） 2023/03/23(木) 08:41:21
＞<出庫指示書> ＞行 __A__ __B__ __C__ ___D___ ___E___ ____F____ _____G_____ ____H____ ___I___ _J_ ＞ 1 ID 品番 数量 箱番号 棚番号 入荷日 注文年月日 指定納期 出庫数 残 ＞ 2 10001 AAA-1 20 1 A1 2023/3/13 20230317 20230403 20 0 ＞ 3 10002 AAA-2 300 2 A1 2023/3/13 20230317 20230404 10 290 ＞ 4 20001 AAA-1 100 3 A1 2023/3/14 20230317 20230403 30 70 ＞ 5 20002 AAA-4 30 4 A1 2023/3/10 20230317 20230406 20 10 ＞ 6 30001 AAA-5 50 5 A2 2023/3/15 20230317 20230407 20 30 ＞ 7 20001 AAA-1 70 3 A1 2023/3/14 20230317 20230408 10 60 ＞ 8 20001 AAA-1 60 3 A1 2023/3/14 20230317 20230408 10 50 ↑ その並び方にするんですね？ どう言う基準なのか私はちょっと分からないです。
なぜ、下の(1) または(2)にしないのですか？（こっちの方が出庫もしやすいような気がするんですが・・）
<出庫指示書>(1) 行 __A__ __B__ __C__ ___D___ ___E___ ____F____ _____G_____ ____H____ ___I___ _J_ 1 ID 品番 数量 箱番号 棚番号 入荷日 注文年月日 指定納期 出庫数 残 2 10001 AAA-1 20 1 A1 2023/3/13 20230317 20230403 20 0 3 10002 AAA-2 300 2 A1 2023/3/13 20230317 20230404 10 290 4 20001 AAA-1 100 3 A1 2023/3/14 20230317 20230403 30 70 5 20001 AAA-1 70 3 A1 2023/3/14 20230317 20230408 10 60 6 20001 AAA-1 60 3 A1 2023/3/14 20230317 20230408 10 50 7 20002 AAA-4 30 4 A1 2023/3/10 20230317 20230406 20 10 8 30001 AAA-5 50 5 A2 2023/3/15 20230317 20230407 20 30
<出庫指示書>(2) 行 __A__ __B__ __C__ ___D___ ___E___ ____F____ _____G_____ ____H____ ___I___ _J_ 1 ID 品番 数量 箱番号 棚番号 入荷日 注文年月日 指定納期 出庫数 残 2 10001 AAA-1 20 1 A1 2023/3/13 20230317 20230403 20 0 3 20001 AAA-1 100 3 A1 2023/3/14 20230317 20230403 30 70 4 20001 AAA-1 70 3 A1 2023/3/14 20230317 20230408 10 60 5 20001 AAA-1 60 3 A1 2023/3/14 20230317 20230408 10 50 6 10002 AAA-2 300 2 A1 2023/3/13 20230317 20230404 10 290 7 20002 AAA-4 30 4 A1 2023/3/10 20230317 20230406 20 10 8 30001 AAA-5 50 5 A2 2023/3/15 20230317 20230407 20 30
（半平太） 2023/03/23(木) 17:06:17
＞（こっちの方が出庫もしやすいような気がするんですが・・）
そうは言えなかった・・ 済みません
（半平太） 2023/03/23(木) 17:18:10
＞ どう言う基準なのか私はちょっと分からないです。
基準が分かったかも知れない。(合理的とも思えないけど・・）
Enum Pos
ID = 1
品番
数量
箱番号
棚番号
入荷日
注文年月日
指定納期
出庫数
残
連番
End Enum
Sub OffsetMatching()
Dim lineOut(1 To 11)
Dim vWholeStock
Dim i As Long, k As Long, mSend As Long, mExSend As Long, mMake As Long, odrRW As Long
Dim stockNetRows As Long
Dim balOrdered As Double, qntyToDrop As Double
Dim itmCode
Dim wsSend As Worksheet
Application.ScreenUpdating = False
With Sheets("Sheet4") '製作指示書
mMake = 1
.UsedRange.ClearContents
.Range("A1:D1") = Array("品番", "数量", "注文年月日", "指定納期")
End With
Set wsSend = Sheets("Sheet3") '出庫指示書
With wsSend
mSend = 1
.UsedRange.ClearContents
Sheets("Sheet1").Range("A1").CurrentRegion.Copy .Range("A1") '在庫を転記
stockNetRows = Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count - 1
mExSend = stockNetRows
.Range("G1:K1") = Array("注文年月日", "指定納期", "出庫数", "残", "連番")
.Range("C2").Resize(stockNetRows).Copy .Range("J2")
With .Range("K2") '仮連番付与
.Value = 1
.AutoFill Destination:=.Resize(.CurrentRegion.Rows.Count - 1), Type:=xlFillSeries
End With
Call reSort(wsSend, False)
With .Range("A1").CurrentRegion.Offset(1)
vWholeStock = .Value '在庫データを格納(最下行+１）
.ClearContents
End With
End With
With Sheets("Sheet2") '注文
'注文を順番に処理して行く
For odrRW = 2 To .Cells(.Rows.Count, "G").End(xlUp).Row
'未発送数量
itmCode = .Cells(odrRW, "G")
balOrdered = .Cells(odrRW, "I")
For i = 1 To stockNetRows '最下行まで
If vWholeStock(i, 残) > 0 Then
'在庫に該当する品番があり、且つ在庫があれば出庫する
If itmCode = vWholeStock(i, 2) Then
qntyToDrop = Application.Min(balOrdered, vWholeStock(i, 残))
vWholeStock(i, 残) = vWholeStock(i, 残) - qntyToDrop
balOrdered = balOrdered - qntyToDrop '残高更新
For k = 1 To 6
lineOut(k) = vWholeStock(i, k)
Next k
lineOut(注文年月日) = .Cells(odrRW, "E")
lineOut(指定納期) = .Cells(odrRW, "H")
lineOut(出庫数) = qntyToDrop
lineOut(残) = vWholeStock(i, 残)
If IsEmpty(vWholeStock(i, 連番)) Then
mExSend = mExSend + 1
lineOut(連番) = mExSend
Else
lineOut(連番) = vWholeStock(i, 連番)
vWholeStock(i, 連番) = Empty '連番クリア
End If
mSend = mSend + 1
wsSend.Cells(mSend, "A").Resize(1, 11) = lineOut
vWholeStock(i, 数量) = vWholeStock(i, 残) '数量データをUPDATE
If balOrdered <= 0 Then
Exit For
ElseIf itmCode <> vWholeStock(i + 1, 2) Then
Exit For
End If
End If
End If
Next i
If balOrdered > 0 Then
mMake = mMake + 1
With Sheets("Sheet2") '製作指示書に追記
Sheets("Sheet4").Cells(mMake, "A").Resize(1, 4) = _
Array(.Cells(odrRW, "G").Value, balOrdered, .Cells(odrRW, "E").Value, .Cells(odrRW, "H").Value)
End With
End If
Next odrRW
End With
Call reSort(wsSend, True)
wsSend.Select
wsSend.Columns("K").Clear
wsSend.Range("A1").Select
Application.ScreenUpdating = True
End Sub
Sub reSort(ws As Worksheet, isResult As Boolean)
With ws.Sort
With .SortFields
.Clear
If isResult Then
.Add Key:=Range("K2"), SortOn:=xlSortOnValues, Order:=xlAscending
Else
.Add Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending
.Add Key:=Range("F2"), SortOn:=xlSortOnValues, Order:=xlAscending
.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending
End If
End With
.SetRange ws.Range("A:K")
.Header = xlYes
.Apply
End With
End Sub
（半平太） 2023/03/23(木) 20:20:27
多数コメント、ありがとうございます。
ご返答が遅れまして、大変申し訳ございません。
出荷指示や在庫管理など改善点は多数あると思いますが、
現状、現場を大きく変更すると、混乱となってしまうため、今あるデータでやりくりするしかない状況です。
現状、人力(目と検索機能)で、出庫指示シートを作成しており、少しでも改善できないか。と画策しております。
頂きましたマクロ、いくつか過去の在庫と注文書シートで動かしてみます。
お時間ください。よろしくお願いいたします。
（ちなつ） 2023/03/24(金) 10:17:40
＞今あるデータでやりくりするしかない状況です。
今あるデータって、在庫と注文だけですよね。 それらには何の影響もない話です。
出庫指示書と製作指示書は、新規に作成するものであり、 使い易くすれば喜ばれると思いますけど・・
(まぁ、私にとっては他人事なので、これ以上お節介を言う心算はないです）
（半平太） 2023/03/24(金) 10:49:59
(製造と営業が管理している、それぞれのシートを、日付でソートや在庫は品番で合算など
改善点エクセル上も現場でも、たくさんあると思いますが、改善(変更)すると、現場の混乱を招き、逆にミスが発生するため、)
今あるデータで(を変更せずに、)やりくりするしかない状況です。
という、意味でした。
分かりにくく大変申し訳ございません。
午前いっぱい、いくつか、過去の在庫と注文書で、動かしてみましたら、ちゃんと出庫指示と製作指示がでました。
本当に、ありがとうございます。
教えていただいたマクロを少しずつ理解し、メンテナンスできるようにしたいと思います。
また、どうしても分からないところがあったら、ご質問させていただくかもしれません。
ありがとうございます。
（ちなつ） 2023/03/24(金) 12:33:14
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.