Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
転記作業を効率的に処理したい。
投稿日時: 19/08/23 19:34:04
投稿者: 真下まゆ

いつもお世話になりますm(_ _)m
 
やりたいことは難しくないとは思うのですが、プログラムを組み立てるのがとても下手で、
私が書こうとすると何度もループさせるような方法しか思い浮かびません(-"-;
注文書を読み取って、受注一覧で一周して、工場別でまたぐるぐるぐるぐる・・・(-"-;
 
書いていて、自分のコードの汚さと冗長さ加減に、誰に読まれるわけでもないのですが
切なくなってきました(T-T)
 
日本語でも、雰囲気だけでも良いので、先生方だったら、どんな風に書かれるのか
教えて頂きたいです。m(_ _)m
 
 
 
 
1.注文書.csv (元データ)ここから、 (注文番号はユニークです)
 
 
注文番号 工場  品名   数量 
0823-1   AA  Item1   5
0823-2   BB  Item1   6
0823-3   AA  Item2   10
0823-4   BB  Item2   3
0823-5   AA  Item1   5
0823-6   CC  Item3   2
 
 
 
↓これらを作りたいです。
 
 
 
2.受注一覧.xlsx に転記(既存ファイル。過去からの累計。一番下の行から書き出し)
 
注文番号 工場  品名  数量 
0822-1   AA  Item5   3  (過去データ)
0822-2   BB  Item4   2  (過去データ) 
0823-1   AA  Item1   5  (この行から追加)
0823-2   BB  Item1   6
0823-3   AA  Item2   10
0823-4   BB  Item2   3
0823-5   AA  Item1   5
0823-6   CC  Item3   2
 
 
3.工場別に納期回答シートを作成「新規ブックを作成」→「それぞれ保存」
 
@AA工場分
 
注文番号 工場  品名  数量 
0823-1   AA  Item1   5
0823-3   AA  Item2   10
0823-5   AA  Item1   5
 
 
 
ABB工場分
 
注文番号 工場  品名  数量 
0822-2   BB  Item4   2
0823-4   BB  Item2   3
 
 
 
BCC工場分
 
注文番号 工場  品名  数量 
0823-6   CC  Item3   2

回答
投稿日時: 19/08/24 10:42:40
投稿者: mattuwan44

>注文書を読み取って、受注一覧で一周して、工場別でまたぐるぐるぐるぐる・・・(-"-;
ロジック的には、それでいいんじゃないですか?
 
エクセルの機能を使ってもよさそうですが、
自分でVBAでループ処理を書いているから、コードとして見えるか、
既存のプログラムを使ったからループ処理をコードで見えないかの違いで、
順繰り見て回らなければ処理出来ないことに変わりはないです。
ですが、同じ処理をするなら、
高級言語のVBAで書いたプログラムより、
低級言語で書かれたプログラム(例えばエクセルの機能とかディクショナリオブジェクト等)を使う方が、
処理速度が速くなるのは、当然の話です。
 
ぱっと、思いつくのは、
ピボットテーブル機能でまとめてやって、
そのフィルター機能で工場別に吐き出してやればいいのでは?
というのが1案。
 
2案目は、
工場でソートして、集計機能で小計行を挿入
小計行で区切られたセル範囲をジャンプ機能で特定し、順にコピペ
 
開発的には、
ピボットテーブルの使い方や、
そのオブジェクト構造を探るのに時間がかかるので、
2案の方が処理の流れが割と直観的にイメージし易いかなと思います。
 
他の方法としては、
ディクショナリオブジェクトなども使えて処理も速いだろうなと思いますが、
個人的に使ったことがほぼないので、上手い使い方を思いつきません^^;;;;
 
とりあえず2案で考えてみると、
 
sub メイン
  新しいデータのインポート
  工場別に新しいブックで出力 データ範囲
End Sub
 
private sub 新しいデータのインポート
  追加するデータがあるファイルを指定
  追記を始めるセルの特定
  特定したせるから、「テキストデータのインポート」機能で指定したファイルのデータをインポート
End sub
 
private sub 工場別に新しいブックで出力(データ範囲)
  データ範囲 = データの並べ替えと区切りを挿入(データ範囲)
  
  データの区切り毎に繰り返し
    新規ブックの作成(テンプレートのコピー)
    データの転記
    新規ブックを名前を付けて保存
    新規ブックを閉じる
  次へ
end sub
 
private function データの並べ替えと区切りを挿入(データ範囲 as range) as range
  データ範囲の並べ替え
  「集計」機能で小計行挿入
 データ範囲の再取得
  数式のある行をクリア
  set データの並べ替えと区切りを挿入 = 「ジャンプ」機能で定数のあるセルを特定
end sub
 
こんな感じで解りますでしょうか?
 
とりあえず、ぱっと思いついた感じで書きましたが、
これをコードに直しながら、微調整してみたり、
動作確認してバグ取りを行いながら、開発して行ってみたらいかがでしょうか?
 
参考URL>>
http://home.att.ne.jp/zeta/gen/excel/c03p06.htm

回答
投稿日時: 19/08/24 15:50:52
投稿者: WinArrow
投稿者のウェブサイトに移動

処理を分割して考えましょう
(1)CSVデータを読み込んで受注一覧.xlsx に追加する
 この処理は、ループ処理不要で一括処理可能です。
 
(2)受注一覧からxx工場別への転記
 ここは、既存の工場別シートに追加するのか?上書きするのか?で考え方が変わってきます。
 要するに、受注一覧の中で、今回CSVから転記したデータが認識できますか?
 元データをCSVにするか・受注一覧にするかが変わるということです。
 
 アドバイスとしては、なるべく、ループ処理をなくすことをお勧めします。
 
 
 

投稿日時: 19/08/24 16:34:19
投稿者: 真下まゆ

mattuwan44様、WinArrow様
 
いつもお世話になります。
ご返信ありがとうございますm(_ _)m
 
お恥ずかしながら、私は配列が凄く苦手で・・・
 
逆に Collection や Dictionary オブジェクトの方が使いやすいというか・・・(^-^;
Key を渡すと、Item を返してくれるというのが凄く便利で、多用してしまいます。
というか、配列にすべきところ、全て Dictionary で回してしまっている感じです(T_T)
 
いつも Dictionary に元データを読み込ませて、
それを Dictionary が保持している Keys のループで転記するようなコードを書いてしまうんです。
1行1行やるわけですから、激遅なんですよね。わかってるんですけど(-"-;
(遅いけど、いつもこのパターンでやるので、直すときに楽なんです。
 どのプログラムもこのパターンなので(^-^; )
 
 
今回も、元データ全部の Dictionary を用意しておいて、
もう一つ工場がいくつあるか調べる為に Collection か Dictionary に
工場を持たせようと思ったんです。
 
工場の Collection(又はDictionary)カウント分だけ
新規ワークシートを用意して、もう一度元データをループさせて仕分けみたいな・・・。
 
 
でも書いていると凄く長くて、自分が読んでいても辟易するくらいでした(-"-;
 
 
 
 
mattuwan44様
 
ご連絡遅くなり申し訳ありませんm(_ _)m
 
私がよく見落としがちなのが、その Excel でいつも使っている機能ですね!!
確かに先に Sort しておくとか、もう1mmも考えていなかったです(^-^;
 
集計機能とかも、Sheet上では死ぬほど使っているのに、
VBA になった途端、計算は全部 VBA にやらせてしまいます(^-^;
 
こういうのは目から鱗ですね(*^-^*)
ありがとうございます。
検討の余地あり!!!です!!
 
 
 
 
WinArrow様
 
> (1)CSVデータを読み込んで受注一覧.xlsx に追加する
> この処理は、ループ処理不要で一括処理可能です。
 
後だしジャンケンみたいで大変申し訳ありませんm(_ _)m
 
ついデータの構造を端折ってしまったのですが、
元データの構造(項目名、並び順)と、受注一覧の構造は異なるのですが、
その場合でも可能でしょうか?
FSO とか使いますか?
Open ステートメントでしょうか?
 
後だしジャンケン部分が無かったとして、
どんな方法だったらループ不要でしたでしょうか。
CSV操作とか苦手ですので、教えて頂けると嬉しいですm(_ _)m
 
 
 
> (2)受注一覧からxx工場別への転記
>  ここは、既存の工場別シートに追加するのか?上書きするのか?で考え方が変わってきます。
>  要するに、受注一覧の中で、今回CSVから転記したデータが認識できますか?
 
工場別シートは、毎回全て「新規作成」になります。
 
受注一覧は過去からの注文を累積する帳票のようなもので、
今回の注文がどの部分かはわかりません。
 
今回の注文は「注文書.csv」で、この注文書に対して各工場へ納期回答を貰う為、
工場別シートを新規で毎回作るイメージです。
 
 
 
> アドバイスとしては、なるべく、ループ処理をなくすことをお勧めします。
 
ですよね・・・。
私も書いていて目が回るほどループしていたので、
あれ?今何のために誰がループしているんだろう????
となって、とりあえず moug で質問書いてから頭を整理させようと思いました。
 
質問を書くことで、ぐるぐるの頭が整理されることもあるので(*^-^*)
 
土日ちょっと考えてみて、また月曜日にチャレンジしてみます(*^-^*)
月曜日にまたコード書いてみますので、それまでスレッド空けっぱなしで失礼しますm(_ _)m
 

回答
投稿日時: 19/08/24 17:22:14
投稿者: simple

オートフィルターをつかってはどうですか?
 
・dictionaryを使って、重複を除く工場名を取得。
・工場ごとにオートフィルターを使って抽出、
・それをシートに転記
・シートを新しいブックに転記、保存。

回答
投稿日時: 19/08/24 20:19:40
投稿者: 半平太

>↓これらを作りたいです。
>2.受注一覧.xlsx に転記(既存ファイル。過去からの累計。一番下の行から書き出し)
これは、よくある単純転記ですよね。
 
>3.工場別に納期回答シートを作成「新規ブックを作成」→「それぞれ保存」
これも、工場名でフィルターを掛けて転記する。よくある処理です。
※オートフィルターかフィルターの詳細設定(昔のフィルターオプション)で実行する
 
残る問題は、重複のない工場名をどう取得するかですね。
(1) Dicrionaryオブジェクトを使う手もあるし、
(2) データツールの重複削除機能を使う手もあります。
(3) 工場名が決まっているなら、そのリストから入手すればいい。
  そう言うことはこちらじゃ分からないのでそちらで決めるしかない。
 
※下案では(2)を使うことにします。
 
以下、初期段階は、注文書.csvと受注一覧.xlsxが開かれた状態とします。
 

Sub 単純転記()
    Workbooks("注文書.csv").Sheets(1).UsedRange.Offset(1).Copy
    
    With Workbooks("受注一覧.xlsx").Sheets(1) 'シート名が分からないので左端のシートとする
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
        .Parent.Close True  '受注一覧は保存して閉じる
    End With
End Sub

Sub 工場別Sheet()
    Dim Wsh As Worksheet
    Dim colFactory As Range
    Dim factoryList
    Dim factory
    
    '重複の無い工場名リストを取得する
    With Workbooks("注文書.csv").Sheets(1)
        .UsedRange.Columns("B").Copy .Range("F1")
        .UsedRange.Columns("F").RemoveDuplicates Columns:=1, Header:=xlYes
        With .Range("F2", .Cells(.Rows.Count, "F").End(xlUp))
            factoryList = .Value
            .ClearContents
        End With
    End With
    
    For Each factory In factoryList '工場名を順に処理
        Set Wsh = Workbooks.Add.Sheets(1)
        
        With Workbooks("注文書.csv").Sheets(1)
            .Range("F2").Value = factory 'フィルター条件を指定する
            .Columns("A:D").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("F1:F2"), _
            CopyToRange:=Wsh.Range("A1"), Unique:=False
        End With
        
        '※工場名が多い場合は、ここでブックを保存して閉じる。
        '(ブック名や保存フォルダをどうするのか、こちらでは分からない)
        
    Next
    
    Workbooks("注文書.csv").Close False '注文書は保存しないで閉じる
End Sub

回答
投稿日時: 19/08/24 21:55:51
投稿者: WinArrow
投稿者のウェブサイトに移動

 

引用:
元データの構造(項目名、並び順)と、受注一覧の構造は異なるのですが、
その場合でも可能でしょうか?

  
構造が違う??
最初の説明は違っているってこと?
ならば、どのように違うのかを説明して貰わないと・・・・回答のしようもない。
 
引用:
SO とか使いますか?
Open ステートメントでしょうか?

 多分、関係ないと思います。

回答
投稿日時: 19/08/24 21:59:27
投稿者: WinArrow
投稿者のウェブサイトに移動

>3.工場別に納期回答シートを作成「新規ブックを作成」→「それぞれ保存」
  
こんな説明があったんですね・・・・見逃していました。
 
受注一覧表から抽出する方法といては、オートフィルタ、
もしくは、フィルタオプションをお薄めします。
勿論、工場分のループは必要かもしれませんが、データのループは不要です。

投稿日時: 19/08/25 07:49:29
投稿者: 真下まゆ

おはようございます。
皆さま、いつもお世話になっておりますm(_ _)m
ご回答ありがとうございますm(_ _)m
 
ご連絡が遅くなり、申し訳ありません。
 
 
simple様
 
あぁ・・・オートフィルタ。。。
すっかり頭から抜け落ちていました。
 
ありがとうございます!!
 
 
 
半平太様
 
データツールの重複削除機能なんていうものを全く存じませんでした。
RemoveDuplicates 見たことも無かったです(^-^;
勉強になりました!!会社に行ったら色々弄ってみます(*^-^*)
コードも書いて下さり、ありがとうございます!!
 
 
 
WinArrow様
 
> ならば、どのように違うのかを説明して貰わないと・・・・回答のしようもない。
 
で・・・ですよね。仰る通りでございますm(_ _)m
時間もなく、説明を端折りすぎてしまいました。
 
 
> 受注一覧表から抽出する方法といては、オートフィルタ、
> もしくは、フィルタオプションをお薄めします。
> 勿論、工場分のループは必要かもしれませんが、データのループは不要です。
 
承知いたしました。
私もオートフィルタ、フィルタオプションに気持ちが傾いております。
 
 
 
 
皆さま
 
たくさんの案を出して頂き、とても参考になりました(*^-^*)
 
自己流で書いていると、自分の書き方って間違っているんじゃないのか?
凄く遠回りをしているんじゃないのか???
時々ものすごく不安になるのですが、皆さまに色々な案を頂き
頭の中もだいぶ整理されてきました(*^-^*)
 
 
simple様ご推薦の Dictionary & オートフィルタで
書いてみようと思います(*^-^*)
 
ご回答ありがとうございましたm(_ _)m