[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『数字を簡潔にまとめたい』（初心者女子）
検索表をみてI列に下記のように簡潔にまとめたいです。
毎回手作業でやっているのですが、マクロで出来ないかと…
検索シート
A B C D E F 1 コード アイテム 在庫 ロット ロケ 現品数 2 K971011728 テレビ 出荷 A310020 4D 1 3 K971011728 テレビ 出荷 A310021 4D 1 4 K971011728 テレビ 停止 A310040 YB 1 5 K971011728 テレビ 出荷 A310041 YB 1 6 K971048539 冷蔵庫 出荷 A2X0121 1D 1 7 K971048539 冷蔵庫 停止 A2Y0172 2D 1 8 K971048539 冷蔵庫 出荷 A2Y0173 2D 1 9 K971048539 冷蔵庫 出荷 A2Y0174 2D 1 10 K971064163 パソコン 出荷 A310604 1D 1 11 K971064163 パソコン 出荷 A310605 1D 1 12 K971064163 パソコン 出荷 A310606 1D 1 13 K971064163 パソコン 出荷 A310607 2D 1 14 K971064163 パソコン 出荷 A310608 2D 1 15 K971064163 パソコン 出荷 A311256 2D 1 16 K971M01439 エアコン 出荷 A311002 3D 1 17 K971M01439 エアコン 出荷 A311003 3D 1 18 K971M01439 エアコン 出荷 A311004 3D 1 19 K971M01439 エアコン 出荷 A311005 3D 1 20 K971M01439 エアコン 出荷 A311006 1D 1
Dのロットナンバーも1個です、現品数量は必ず1個です。
毎朝仕事でまとめていますが、件数が多く間違いも出てくるのでマクロで簡潔できればと思いました。
まとめた表がこちらです。
I列のように（-）と（・）で出来ますか？
D E F G H I
コードNO. アイテム 数量 出荷日 受注番号 ロット
5 K971011728 テレビ 2 2023/2/8 A1 A310020-A3100216
6 K971011728 テレビ 1 2023/2/8 A2 A3100417
7 K971048539 冷蔵庫 3 2023/2/8 A3 A2X0121・A2Y0173-A2Y0174
8 K971055736 パソコン 6 2023/2/8 A4 A310604-A310608・A311256
9 K971M68984 エアコン 1 2023/2/8 A5 A311002
10 K971M68999 エアコン 2 2023/2/8 A6 A311003-A311004
11 K971048539 エアコン 1 2023/2/8 A7 A311005
＜ 使用 Excel：Microsoft365、使用 OS：Windows11 ＞
（隠居Z） 2023/02/07(火) 20:17:21
[受注番号]と対応する[数量](要するに注文数)ごとに 「検索シート」のデータを上から順に当てはめて行く感じって事ですよね。たぶん。
↓ ↓
__|_____D______|___E____|__F__|____G_____|___H____|_____________I____________
4|コードNO. |アイテム|数量 | 出荷日 |受注番号|ロット
5|K971011728 |テレビ | 2|2023/2/8 |A1 |A310020-A3100216 ←末尾の「6」と
6|K971011728 |テレビ | 1|2023/2/8 |A2 |A3100417 ←末尾の「7」はどういった法則で付くのでしょう?
7|K971048539 |冷蔵庫 | 3|2023/2/8 |A3 |A2X0121・A2Y0173-A2Y0174
8|K971055736 |パソコン| 6|2023/2/8 |A4 |A310604-A310608・A311256
9|K971M68984 |エアコン| 1|2023/2/8 |A5 |A311002
10|K971M68999 |エアコン| 2|2023/2/8 |A6 |A311003-A311004
11|K971048539 |エアコン| 1|2023/2/8 |A7 |A311005
↑
なんか途中からよく分からない
__|_____A______|___B____|____C_____|___D____|_E__|__F___| 1|コード |アイテム|在庫 |ロット |ロケ|現品数|↓受注番号付けたらこんな感じ って事ですね 2|K971011728 |テレビ |出荷 |A310020 |4D | 1|A1 3|K971011728 |テレビ |出荷 |A310021 |4D | 1|A1 4|K971011728 |テレビ |停止 |A310040 |YB | 1|-- 5|K971011728 |テレビ |出荷 |A310041 |YB | 1|A2 6|K971048539 |冷蔵庫 |出荷 |A2X0121 |1D | 1|A3 7|K971048539 |冷蔵庫 |停止 |A2Y0172 |2D | 1|-- 8|K971048539 |冷蔵庫 |出荷 |A2Y0173 |2D | 1|A3 9|K971048539 |冷蔵庫 |出荷 |A2Y0174 |2D | 1|A3 10|K971064163 |パソコン|出荷 |A310604 |1D | 1|A4 11|K971064163 |パソコン|出荷 |A310605 |1D | 1|A4 12|K971064163 |パソコン|出荷 |A310606 |1D | 1|A4 13|K971064163 |パソコン|出荷 |A310607 |2D | 1|A4 14|K971064163 |パソコン|出荷 |A310608 |2D | 1|A4 15|K971064163 |パソコン|出荷 |A311256 |2D | 1|A4 16|K971M01439 |エアコン|出荷 |A311002 |3D | 1|A5 17|K971M01439 |エアコン|出荷 |A311003 |3D | 1|A6 18|K971M01439 |エアコン|出荷 |A311004 |3D | 1|A6 19|K971M01439 |エアコン|出荷 |A311005 |3D | 1|A7 20|K971M01439 |エアコン|出荷 |A311006 |1D | 1|
（白茶） 2023/02/07(火) 20:32:42
この質問は、エクセルQ&Aサロンのりっちゃんのと同じですね。
当方は、ほとんど完成しているんですが、 コードとアイテム(商品名)の関係が不確かなので解決できないんですよ。
コードとアイテムは、この処理に限っては無関係と思っていいという事なんですか？
※ついでですが、完成図がmougでアップしているのと違ってますよ。 それだと、ここの掲示板の人は、何が何だか分からないです。
（半平太） 2023/02/07(火) 20:47:55
あ、半平太さん‼
こんばんはです。
コードとアイテムは関係ないです。
この掲示板に載せたのが正です。
なかなかわかりづらいので分かりやすくしたつもりです。
コードとアイテムは毎日送られてきて、そのコードを基にFの数量を簡潔にまとめたいのです。
コードは同じ型式なのですが数量によってロット数を抜き出しています。
受注番号も自動割り振りなので特に変更したりとかはないです。
コードを基にフィルターを掛けて上から順に出荷の物を順番に簡潔にまとめたいのです。
コードも同じものもあれば型式の違いで同じテレビであってもコードが違います。
5|K971011728 |テレビ | 2|2023/2/8 |A1 |A310020-A3100216 ←末尾の「6」と 6|K971011728 |テレビ | 1|2023/2/8 |A2 |A3100417 ←末尾の「7」はどういった法則で付くのでしょう?
間違っていましたね。
2個なのでA310020-A310021ですね。
すみません。
ロット数を上から採番しているので、出荷の物が上から採番できれば問題ないです。
検索シートからコードをフィルターを掛けて入力シートのFの数量分、入力シートのロット数分、採番できればいいのです。
説明がなかなか上手く出来なくてすみません。
以前、半平太さんに作成していただいたバラバラにするマクロは大変助かっております。
（初心者女子） 2023/02/07(火) 21:46:23
あれい...?
>コードを基に であれば↓こうじゃないっすか?
__|_____D______|___E____|__F__|____G_____|___H____|_____________I____________ 4|コードNO. |アイテム|数量 | 出荷日 |受注番号|ロット 5|K971011728 |テレビ | 2|2023/2/8 |A1 |A310020-A310021 6|K971011728 |テレビ | 1|2023/2/8 |A2 |A310041 7|K971048539 |冷蔵庫 | 3|2023/2/8 |A3 |A2X0121・A2Y0173-A2Y0174 8|K971055736 |パソコン| 6|2023/2/8 |A4 | (←該当コードなしの為) 9|K971M68984 |エアコン| 1|2023/2/8 |A5 | (←該当コードなしの為) 10|K971M68999 |エアコン| 2|2023/2/8 |A6 | (←該当コードなしの為) 11|K971048539 |エアコン| 1|2023/2/8 |A7 | (←[冷蔵庫]で既に全数引当済みの為)
__|_____A______|___B____|____C_____|___D____|_E__|__F___| 1|コード |アイテム|在庫 |ロット |ロケ|現品数| 2|K971011728 |テレビ |出荷 |A310020 |4D | 1|A1 3|K971011728 |テレビ |出荷 |A310021 |4D | 1|A1 4|K971011728 |テレビ |停止 |A310040 |YB | 1|-- 5|K971011728 |テレビ |出荷 |A310041 |YB | 1|A2 6|K971048539 |冷蔵庫 |出荷 |A2X0121 |1D | 1|A3 7|K971048539 |冷蔵庫 |停止 |A2Y0172 |2D | 1|-- 8|K971048539 |冷蔵庫 |出荷 |A2Y0173 |2D | 1|A3 9|K971048539 |冷蔵庫 |出荷 |A2Y0174 |2D | 1|A3 10|K971064163 |パソコン|出荷 |A310604 |1D | 1| 11|K971064163 |パソコン|出荷 |A310605 |1D | 1| 12|K971064163 |パソコン|出荷 |A310606 |1D | 1| 13|K971064163 |パソコン|出荷 |A310607 |2D | 1| 14|K971064163 |パソコン|出荷 |A310608 |2D | 1| 15|K971064163 |パソコン|出荷 |A311256 |2D | 1| 16|K971M01439 |エアコン|出荷 |A311002 |3D | 1| 17|K971M01439 |エアコン|出荷 |A311003 |3D | 1| 18|K971M01439 |エアコン|出荷 |A311004 |3D | 1| 19|K971M01439 |エアコン|出荷 |A311005 |3D | 1| 20|K971M01439 |エアコン|出荷 |A311006 |1D | 1|
（白茶） 2023/02/07(火) 22:47:51
＞コードとアイテムは関係ないです。
そうなんですか、それを信じるとして。。
Sub ExtrSerialNo()
Dim sortedList As Object, dicT As Object, regE As Object
Dim rSearch As Range, aCell As Range
Dim i As Long, k As Long, m As Long, cnt As Long, bal As Double
Dim vDSP, sortedListKey, brDown, splt, leftStr, stNum, ThisNum
Dim vOut(), msg
Set sortedList = CreateObject("System.Collections.SortedList")
Set dicT = CreateObject("Scripting.Dictionary")
Set regE = CreateObject("VBScript.RegExp")
regE.Pattern = "[0-9]+$"
regE.Global = True
With Worksheets("検索") '検索範囲を取得
Set rSearch = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
End With
For Each aCell In rSearch
With aCell
If .Offset(0, 1) <> "停止" Then
With aCell.Offset(0, 2)
stNum = regE.Execute(.Value)(0)
leftStr = Left(.Value, Len(.Value) - Len(stNum))
stNum = Format(stNum, String(10, "0")) '連番の長さを一定にする(繰上り対策)
End With
'優先引当順に作成した合成キーをSortedeListへ登録
sortedList.Add Format(.Row, "0000♪") & leftStr & _
stNum & "♪" & .Value & "♪" & .Offset(0, 2), Empty
End If
End With
Next
'優先引当順に「コード」をdicTへ登録すると共に複数のシリアルを♪区切りで格納
For i = 0 To sortedList.Count - 1
sortedListKey = Split(sortedList.GetKey(i), "♪")
dicT(sortedListKey(2)) = dicT(sortedListKey(2)) & "♪" & sortedListKey(3)
Next
With Worksheets("Sheet1") '在庫範囲の値を配列で取得
vDSP = .Range("D5", .Cells(.Rows.Count, "H").End(xlUp)).Resize(, 5).Value
End With
ReDim Preserve vDSP(1 To UBound(vDSP), 1 To 7) '出力シリアル用列(6)と数量管理用列(7)を付加
For i = 1 To UBound(vDSP)
If vDSP(i, 3) > 0 And vDSP(i, 5) <> "" Then '数量残>0,受注NO欄に記入有る場合のみ
brDown = Split(dicT(vDSP(i, 2)), "♪")
For k = 1 To UBound(brDown)
bal = IIf(vDSP(i, 7) = Empty, vDSP(i, 3), vDSP(i, 7))
If bal > 0 Then
vDSP(i, 7) = bal - 1
vDSP(i, 6) = vDSP(i, 6) & " " & brDown(k)
dicT(vDSP(i, 2)) = Replace(dicT(vDSP(i, 2)), "♪" & brDown(k), "")
'色付け処理
rSearch.Cells(Application.Match(brDown(k), rSearch.Columns(3), 0), 3) _
.Interior.Color = vbYellow
If vDSP(i, 7) <= 0 Then
Exit For '所要数量を処理したら次の行データの処理に向かう
End If
End If
Next k
End If
Next
sortedList.Clear
dicT.RemoveAll
'結果打ち出し--------------
ReDim vOut(1 To UBound(vDSP), 1 To 1)
For i = 1 To UBound(vOut)
vOut(i, 1) = LTrim(vDSP(i, 6))
If vOut(i, 1) <> "" Then
splt = Split(vOut(i, 1), " ")
cnt = 1
stNum = Val(regE.Execute(splt(0))(0))
For k = 1 To UBound(splt)
ThisNum = Val(regE.Execute(splt(k))(0))
leftStr = Left(splt(k), Len(splt(k)) - Len(stNum))
If Replace(Replace(splt(k - cnt), "-", ""), "・", "") = _
leftStr & (ThisNum - cnt) Then '連番のケース
cnt = cnt + 1
If cnt > 2 Then
splt(k - 1) = Empty
Else
splt(k - cnt + 1) = splt(k - cnt + 1) & "-"
End If
Else
splt(k) = "・" & splt(k)
cnt = 1
stNum = ThisNum
End If
Next k
vOut(i, 1) = Application.Trim(Join(splt, ""))
End If
If vDSP(i, 7) Then
msg = msg & i + 4 & "行目の" & vDSP(i, 2) & "残「" & vDSP(i, 7) & "」" & Chr(10)
End If
Next i
Worksheets("Sheet1").Range("I5").Resize(UBound(vOut)) = vOut
If msg <> "" Then
MsgBox " --出荷量不足--" & Chr(10) & msg
Else
MsgBox "完了"
End If
End Sub
（半平太） 2023/02/07(火) 22:50:42
早々に有難う御座います。
早速試してみました。
伝えたかった通りになりました。
めちゃめちゃ有難うございます。
明日から会社で使わせてもらいます。
ちなみになんですが、VBAはどこで覚えてのでしょうか？
関数のように使っていて身につくものですか？
私も出来るようになりたいです。
（初心者女子） 2023/02/08(水) 20:35:11
こちらを使用しているのですが、歯抜けになってしまうのはなぜでしょうか？
（ｒｙ） 2023/03/20(月) 15:51:10
このトピックは、説明に矛盾がある他、常識では理解しがたい仕様で、 他の人が流用できる案件とは思われません。
新規に質問をお立ていただきたい。
（半平太） 2023/03/20(月) 17:26:18
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.