[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『主番号の行を』(WANDA)
毎回こちらの学校を見て、参考にさせて頂いています。よろしくお願いします。
ひとつのブックの中に 入荷リスト(約10万行)と、出荷リスト(約5万行)というシートがあります。
現在、出荷リストのシートには、1行目の項目とC列の主番号だけしか入力されていません。
入荷リストの C列(主番号)と、出荷リストの C列(主番号)が同じだったら、
入荷リストの行をコピーし、出荷リストに貼り付けして、出荷リストのシートを完成させたいです。
2つのシートの項目は同じですが、
出荷リストの主番号が入荷リストになかった場合、それを記録する列が一番最後に設けられています。”主番号なし”と記録します。
データが膨大なため、マクロでのご指導を、よろしくお願いいたします。
シート【入荷リスト】
[A] [B] [C] [D] [E] [F] [G] [H] ……… [HI] [1] |稼 |利率|主番号 |伸整|申請日 |記号1|記号2|記号3|………| 出荷日 [2] | | 0.2|GI9-58k| |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万行(今から更に増え続ける)
シート【出荷リスト】
※実際のデータは、項目とC列(主番号)だけしか入力されていません。
[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行ですから。 大丈夫ですか? エクセルでいいのでしょうかね。
ちなみに、当方のPC,結構メモリーもふんだんで、性能もいいものなんですが
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
入荷リストの C列(主番号)は、重複はありません。
出荷リストの C列(主番号)は、重複があります。
重複があっても削除をしないので、同じデータが何行もできることになります。
βさん、
>すごいですね。列数も 217行ですから。 >大丈夫ですか? エクセルでいいのでしょうかね。
この処理をしたあと、さらにデータ入力の処理を加えるため、
現状では、217列のなかで、データが入っているセルは、それほど多くありません。
そのほとんどが、1〜3桁の番号か記号です。
よろしくお願いいたします。
マナさんの2番目の回答を解明してきます。
(WANDA) 2016/01/19(火) 18:16
その中で、わたしの説明不足な点がありました。大変失礼いたしました。
※追加の説明です
(1)入荷リスト・出荷リスト 両シートの行の順番は変えません。
(2)出荷リスト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.