[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『主番号の行を』（WANDA）
毎回こちらの学校を見て、参考にさせて頂いています。よろしくお願いします。
ひとつのブックの中に 入荷リスト（約１０万行）と、出荷リスト（約５万行）というシートがあります。
現在、出荷リストのシートには、１行目の項目とＣ列の主番号だけしか入力されていません。
入荷リストの Ｃ列（主番号）と、出荷リストの Ｃ列（主番号）が同じだったら、
入荷リストの行をコピーし、出荷リストに貼り付けして、出荷リストのシートを完成させたいです。
2つのシートの項目は同じですが、
出荷リストの主番号が入荷リストになかった場合、それを記録する列が一番最後に設けられています。”主番号なし”と記録します。
データが膨大なため、マクロでのご指導を、よろしくお願いいたします。
シート【入荷リスト】
[A] [B] [C] [D] [E] [F] [G] [H] ……… [HI]
[1] |稼 |利率|主番号 |伸整|申請日 |記号1|記号2|記号3|………| 出荷日
[2] | | 0.2|GI9-58ｋ| |1975/8/7|H5 | | |………| 2016/1/8
[3] |B | |56-800 | 1|1983/6/2| | 704| 62|………|
[4] | |6.58|3L58-H00| |不明 |P63 | | |………| 未
[5] |C | 3.2|658-NM | 2| |N8 | 66| |………|
・
・
・
・
約10万行（今から更に増え続ける）
シート【出荷リスト】
※実際のデータは、項目とＣ列（主番号）だけしか入力されていません。
[A] [B] [C] [D] [E] [F] [G] [H] ……… [HI] [HJ]
[1] |稼 |利率|主番号 |伸整|申請日 |記号1|記号2|記号3|………|出荷日 |基準
[2] |C | 3.2|658-NM | 2| |N8 | 66| |………| |
[3] | |6.58|3L58-H00| |不明 |P63 | | |………|未 |
[4] | | |MN12-YU0| | | | | | | |主番号なし ・
・
・
・
約5万行 （今から更に増え続ける）
＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞
Sub test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dic As Object
Dim c As Range
Dim i As Long
Dim v
Dim n1 As Long, n2 As Long
Set ws1 = Worksheets("入荷リスト")
Set ws2 = Worksheets("出荷リスト")
Set dic = CreateObject("scripting.dictionary")
For Each c In ws2.Range("a1").CurrentRegion.Columns(3).Cells
If Not dic.exists(c.Value) Then dic(c.Value) = c.Row
Next
With ws1.Range("a1").CurrentRegion
v = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count + 1).Value
End With
n1 = UBound(v, 1)
n2 = UBound(v, 2)
For i = 1 To n1
If dic.exists(v(i, 3)) Then
v(i, n2) = dic(v(i, 3))
Else
v(i, n2) = "主番号なし"
End If
Next
ws2.Range("a2").Resize(n1, n2).Value = v
With ws2.Range("a1").CurrentRegion
.Sort Key1:=Columns("HJ"), Order1:=xlAscending, Header:=xlYes
.AutoFilter Field:=Columns("HJ").Column, Criteria1:="<>主番号なし"
.Offset(1).Columns("HJ").ClearContents
.AutoFilter Field:=Columns("HJ").Column, Criteria1:="主番号なし"
.Offset(1).Columns("A:B").ClearContents
.Offset(1).Columns("D:HI").ClearContents
.AutoFilter
End With
End Sub
（マナ） 2016/01/15(金) 23:48
（マナ） 2016/01/16(土) 11:54 あまり意味ないかもしれませんがちょっとだけ修正
>>約10万行（今から更に増え続ける） >>約5万行 （今から更に増え続ける）
すごいですね。列数も 217行ですから。 大丈夫ですか？ エクセルでいいのでしょうかね。
ちなみに、当方のＰＣ，結構メモリーもふんだんで、性能もいいものなんですが
Sub Test()
Dim w As Variant
w = Sheets("入荷リスト").Range("A1:HI100000").Value
End Sub
こんなコードを動かすと、メモリー不足です！！ と、エクセルから叱られました。
（β） 2016/01/16(土) 21:17
処理の流ればかり考えていました。 確認してみるとこちらでは、10万行では何度か繰り返してもエラーでなかったのですが、 20万行だとあさりメモリー不足でした。
一応、修正してみました。 ただマクロ実行以前に、ファイルのopen、closeだけで大変です。
Sub test2()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dic As Object
Dim c As Range
Dim i As Long
Dim k As String
Dim v
Dim n As Long
Dim col As Long
Set ws1 = Worksheets("入荷リスト")
Set ws2 = Worksheets("出荷リスト")
Set dic = CreateObject("scripting.dictionary")
For Each c In ws2.Range("a1").CurrentRegion.Columns(3).Cells
dic(c.Value) = c.Row
Next
With ws1.Range("a1").CurrentRegion
n = .Rows.Count - 1
v = .Columns(3).Resize(n).Offset(1).Value
.Resize(n).Offset(1).Copy
ws2.Range("a2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
For i = 1 To n
k = v(i, 1)
If dic.exists(k) Then
v(i, 1) = dic(k)
Else
v(i, 1) = "主番号なし"
End If
Next
With ws2.Range("a1").CurrentRegion
col = .Columns.Count
.Resize(n, 1).Offset(1, col - 1).Value = v
.Sort Key1:=.Columns(col), Order1:=xlAscending, Header:=xlYes
.AutoFilter Field:=col, Criteria1:="<>主番号なし"
.Offset(1).Columns(col).ClearContents
.AutoFilter Field:=col, Criteria1:="主番号なし"
.Offset(1).Columns("A:B").ClearContents
.Offset(1).Columns("D").Resize(, col - 4).ClearContents
.AutoFilter
End With
End Sub
（マナ） 2016/01/17(日) 11:48
＞出荷リストの主番号が入荷リストになかった場合、…
（マナ） 2016/01/17(日) 12:48
Sub test3()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim dic As Object
Dim c As Range
Dim i As Long
Dim k As String
Dim v
Dim n As Long
Dim col As Long
Dim r As Long
Set ws1 = Worksheets("入荷リスト")
Set ws2 = Worksheets("出荷リスト")
Set dic = CreateObject("scripting.dictionary")
For Each c In ws2.Range("a1").CurrentRegion.Columns(3).Cells
dic(c.Value) = c.Row
Next
With ws1.Range("a1").CurrentRegion
v = .Columns(3).Value
For i = 1 To .Rows.Count
k = v(i, 1)
If dic.exists(k) Then
v(i, 1) = dic(k)
Else
v(i, 1) = "出荷なし"
End If
Next
.Columns(.Columns.Count + 1).Value = v
End With
With ws1.Range("a1").CurrentRegion
col = .Columns.Count
.Sort Key1:=.Columns(col), Order1:=xlAscending, Header:=xlYes
.AutoFilter Field:=Columns(col).Column, Criteria1:="<>出荷なし"
r = .Columns(3).SpecialCells(xlCellTypeVisible).Count
If r > 1 Then
Intersect(.Cells, .Offset(1)).Copy
ws2.Rows(2).Resize(r, col).Insert xlShiftDown
Application.CutCopyMode = False
End If
.AutoFilter
.Columns(col).ClearContents
End With
ws2.Range("a1").CurrentRegion.RemoveDuplicates Columns:=3, Header:=xlYes
With ws2.Range("a1").CurrentRegion
col = .Columns.Count
.Sort Key1:=.Columns(col), Order1:=xlAscending, Header:=xlYes
.AutoFilter Field:=col, Criteria1:="="
r = .Columns(3).SpecialCells(xlCellTypeVisible).Count
If r > 1 Then
Intersect(.Offset(1), .Columns(col)).Value = "主番号なし"
End If
.AutoFilter Field:=col, Criteria1:="<>主番号なし"
.Offset(1).Columns(col).ClearContents
.AutoFilter
End With
End Sub
（マナ） 2016/01/17(日) 15:22
入荷リストの Ｃ列（主番号）は、重複はありません。
出荷リストの Ｃ列（主番号）は、重複があります。
重複があっても削除をしないので、同じデータが何行もできることになります。
βさん、
＞すごいですね。列数も 217行ですから。 ＞大丈夫ですか？ エクセルでいいのでしょうかね。
この処理をしたあと、さらにデータ入力の処理を加えるため、
現状では、217列のなかで、データが入っているセルは、それほど多くありません。
そのほとんどが、1～3桁の番号か記号です。
よろしくお願いいたします。
マナさんの2番目の回答を解明してきます。
（WANDA） 2016/01/19(火) 18:16
その中で、わたしの説明不足な点がありました。大変失礼いたしました。
※追加の説明です
（１）入荷リスト・出荷リスト 両シートの行の順番は変えません。
（２）出荷リストC列に重複があっても、削除しません。
私の実行のやり方が悪いのか、教えていただいたコードを実行すると、入荷リスト・出荷リストの行の順番が変わってしまいます。
どこを、どのように変えたらいいでしょうか。よろしくお願いいたします。
（WANDA） 2016/02/03(水) 21:59
（マナ） 2016/02/05(金) 22:26
Key:主番号 Item:行番号
・出荷リストの各行を順次作業
主番号が辞書にあれば、入荷リストの該当行をコピー
辞書になければ、HJ列に「主番号なし」と記入
ということでしょうか。
時間はかかるかもしれないですけど、上記でよいのでしょうか？
配列利用（場合によっては、出荷リストをいくつかに分散して処理？）すれば、
時間短縮は可能かも。
＝＝
ところでお聞きしたいのは、データの大きさに関係したことです。
入荷リストなどは"今後も増え続ける"とのことですが、
それはいつまで保持し続けるのですか？
永遠に持ち続けるのですか？
・もしずっと保持し続けるなら、早晩、Excelでは破綻しますよねえ。
・一定の期間が来たら古いデータや、出荷済みになった入荷データは削除していくんですか？
そっちのメカニズムが保証されないと、結局無駄なことになりませんか？
（γ） 2016/02/06(土) 13:19
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.