行数の多いデータ3ファイルをVBAで加工し出力したいのですが、仕様が決められません。

前提

今月からVBAを仕事で使うことになりました。
約１０万行ほどのレコードが記載されているCSVファイルが3つあるのですが、そのファイルを組み合わせて1つにまとめたいです（内容としては、FULL OUTER JOINのようなことをしたい）。

使用できる言語はVBAで、MYSQLなどの外部のデータベースやAccessなどは使用できない環境です。

1人で仕事を行っていることとVBA自体が初心者である為、仕様の段階で躓いております。。

実現したいこと

以下のようなデータがファイル毎に3つあります。実際には1つのファイルにデータが約10万件あります。
また、カラム数やカラム名もその時々により変化します。

１ファイル目（wearデータ）

２ファイル目（oralデータ）

3ファイル目（gadgetデータ）

IDはユニークとなります。
この３つのファイルをジョインし、以下のデータに加工したいです。
加工内容としてはFULL JOINにあたります。

相談したいこと

実際にプログラムを作成するにあたり、どのような方法で進めていくのがベストなのか判断できずにいます。
考えつくベストな方法があればご教授いただきたいです。

ちなみに、自分なりに２つの案を考えてみました。

案①：以下URLでの情報を参考にし、シートをデータベース化する方法
【VBA】ADOを使用してExcel表をDB操作する方法のまとめ（範囲指定、書き込みなど）

手順

⑴ 3つのファイルに書かれているテーブルをVBAを使用し１つのファイルまとめる。
Worksheetを3つに分け、1Worksheetにつき1つのテーブルに振り分ける。

⑵ URLに書かれているようにVBAでSQLを操作し、FULL OUTER JOINコマンドを実施。

⑶ SQLで出力した結果を新たなシートを作成し、書き出す。

案②：データベース化せずにVBAのみで処理を行う方法

手順

⑴ 3つのファイルに書かれているテーブルをVBAを使用し１つのファイルまとめる。
Worksheetを3つに分け、1Worksheetにつき1つのテーブルに振り分ける。

⑵それぞれのテーブルのID値を３つのテーブルのIDを配列にし、重複の値を除去する。

⑶新たにWorksheetを用意し、A列にIDの配列をループ処理し出力していく。

⑷wearテーブルのカラム名を取得し、1行目に貼り付ける。

⑸wearテーブルのデータを取得し、セル毎に条件式（IDと合致するデータがあるかどうか）に合致すれば貼り付ける。

⑹上記の⑷⑸をoralテーブル、gadgetテーブル共に実行し、貼り付けていく。

・処理の高速化
・運用しやすい

上記２点を意識した上で私自身が理解しやすい方法としては、
データベース化する案①が有力だと考えています。
案②は１セル毎に条件式で出力するのはあまりにも時間がかかりそう、またコードが複雑になりそうだと考えています。

しかし、実際に案①で実現させることができるのか、また自分が思いつかないだけでもっと他にもベストな方法があるのではないかとも思っています。
事実データベース化するという案は当初考えつかず、ネットでひたすらググったところたまたま発見しました。
自分なりに考えた案①案②ですが、実際には実現不可能な方法なのではないかという心配もあります。

是非、知見のある方に相談とお力添え頂きたいです。

meg_

2022/10/05 12:19

IDを統合後にルックアップ関数では事足りないのでしょうか？

sk.exe

2022/10/06 05:29

VBAよりPowerQuery向きの案件であるような気が。

taku-s

2022/10/07 01:48

meg_様色々調べたところ、以下URLの「VLOOKUP関数の数式をセルに直接入力する方法」が良さそうでした。 https://office-hack.com/excel/vlookup-vba/ 確かに、このサイトを読む限り事足りそうですね。ありがとうございます！

taku-s

2022/10/07 01:52

sk.exe様ネットで色々調べてみるとPowerQueryを使った方法が多くヒットするので、PowerQuery案件ですね。。。今度、会社に確認提案してみます。

meg_

2022/10/07 06:27

データ数が多いとVLOOKUP関数では動作が重いかもしれません。(PC性能によるので何とも言えませんが) 関数使う場合は処理後に数式を値に変換すると良いかと思います。

taku-s

2022/10/11 03:27

meg_様ありがとうございます。数式を値に変換する方向で考えてみたいと思います！

行動規範の内容に同意します

回答3件

「使用できる言語はVBAで」ということですが、本質的には「Excelを使って」ということだと思われますので、念のためPowerQueryを使った場合の例を挙げておきます。

入力ファイル仕様

C:\FolderName というフォルダに、wear.csv, oral.csv, gadget.csv という 3 つの CSV ファイルが保存されている。
これらのファイルの文字コードは全て Shift-JIS である。
これらのファイルに共通する列（主キー）として、[ID], [性別], [年齢]の 3 つの列が必ず存在する。
主キー以外の列はそのファイル固有の項目であり、他のファイル上には存在しない（ファイル間で競合することはない）。

以上のような前提であると仮定するなら、例えば次のような方法が挙げられます。

設定手順

(1) Excel を起動して空のブックを新規作成する。

(2) [データ]タブ -> [取得と変換]グループ -> [新しいクエリ] -> [クエリの結合] -> [Power Query エディターの起動]をクリックする。

(3) 起動した PowerQuery エディターより[ホーム]タブ -> [新しいクエリ]グループ -> [他のクエリ] -> [空のクエリ]をクリックし、空のクエリを作成する。

(4) 作成したクエリが選択されている状態で[ホーム]タブ -> [クエリ]グループ -> [詳細エディター]を開く。

(5) 以下のクエリを記述して[完了]ボタンをクリックする。

PowerQuery
1let GetRecords = (CsvFilePath as text) => 
2    let
3        Source = Csv.Document(File.Contents(CsvFilePath),[Delimiter=",", Encoding=932, QuoteStyle=QuoteStyle.None]),
4        PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
5        ChangedType = Table.TransformColumnTypes(PromoteHeaders,{{"ID", Int64.Type}, {"性別", type text}, {"年齢", Int64.Type}}),
6        UnpivotedColumns = Table.UnpivotOtherColumns(ChangedType, {"ID", "性別", "年齢"}, "属性", "値")
7    in
8        UnpivotedColumns 
9in 
10    GetRecords
11

(6) 手順 3 ～ 5 によって作成されたクエリ（カスタム関数）の名前を「GetUnpivotedRecordsFromCsv」に変更する。

(7) 再度[ホーム]タブ -> [新しいクエリ]グループ -> [他のクエリ] -> [空のクエリ]をクリックし、空のクエリを作成する。

(8) 作成したクエリが選択されている状態で[ホーム]タブ -> [クエリ]グループ -> [詳細エディター]を開く。

(9) 以下のようなクエリを記述して[完了]ボタンをクリックする（各ファイルパスは実際のものに適宜修正すること）。

PowerQuery
1let
2    WearTable = GetUnpivotedRecordsFromCsv("C:\FolderName/wear.csv"),
3    OralTable = GetUnpivotedRecordsFromCsv("C:\FolderName/oral.csv"),
4    GadgetTable = GetUnpivotedRecordsFromCsv("C:\FolderName/gadget.csv"),
5    CombinedTable = Table.Combine({WearTable, OralTable, GadgetTable}),
6    PivotedColumn = Table.Pivot(CombinedTable, List.Distinct(CombinedTable[属性]), "属性", "値"),
7    SortedRows = Table.Sort(PivotedColumn,{{"ID", Order.Ascending}}) 
8in
9    SortedRows

(10) プレビューにおいて正常な結果が返されていることを確認する。

(11) 手順 7 ～ 10 によって作成されたクエリの名前を任意に変更する。

(12) [ホーム]タブ -> [閉じる]グループ -> [閉じて読み込む]をクリックする。

投稿2022/10/07 03:01

sk.exe

総合スコア1070

taku-s

2022/10/11 03:32

ありがとうございます。依頼先に使用できる方法を相談し確定できればPowerQueryも対応できるかもしれません。このような方法も提案として、相手に伝えることができるので非常にうれしいです。 PowerQueyは、確かに今実現したいことに関してはシンプルに書けますね。

行動規範の内容に同意します

マッチング処理してはどうでしょうか？（IDで各CSVがソートされている必要があります。）

「マッチング処理のロジック」
https://cyzennt.co.jp/blog/2019/06/01/%E3%83%9E%E3%83%83%E3%83%81%E3%83%B3%E3%82%B0%E5%87%A6%E7%90%86%E3%81%AE%E3%83%AD%E3%82%B8%E3%83%83%E3%82%AF/

上の例は２本のファイルを突き合わせる方法ですが、３本のファイルを一気に突き合わせることも可能です。

「【MATDV3】３本のファイルのマッチング」
http://www16.plala.or.jp/hiyokogumi/4/442.html
（ちょっといい例が見つかりませんでした。）

複雑になるのが面倒であれば、先に２本のファイルを突き合わせて１本のファイルを作り、残り１本のファイルと突き合わせても良いです。

投稿2022/10/06 01:27

KOZ6.0

総合スコア2721

taku-s

2022/10/07 02:11

KOZ6.0様ご回答ありがとうございます。このような処理もできるのですね、勉強になります。本当は回答を頂いた処理を試してから返答したかったのですが、自分の実力が追い付かない為取り急ぎお礼を言わせてください。 URLも貼っていただきありがとうございます、確認いたします！

行動規範の内容に同意します

ベストアンサー

案①について

ADOで操作する場合、エクセルに読み込まなくても、CSVファイルに直接接続することも可能。
（どちらが速いかは私は試したことがないので不明）

ADOでCSVの読み込み（SQL）｜VBAサンプル集

ADOで接続する場合、Microsoft.ACE.OLEDB.12.0 を使うことになりますが、これは、FULL OUTER JOIN には対応していないので、UNION と LEFT JOIN , RIGHT JOIN を組み合わせて実装することになります。

(SQL)Access(JET)で FULL OUTER JOIN を行う : old_3流プログラマのメモ書き

完全外部結合を実現する - もう一度学ぶMS-Access

上記のリンクをみても分かると思いますが、かなり複雑なSQLになります。

案②について
１０万行のデータをループでセル参照して処理するととんでもなく遅くなるでしょうね。
セル範囲をいったん配列に格納して、配列をループ処理するとかなり改善できますが、やはり、重いでしょう。

関数をセルに埋め込む方法も10万行となるとやはり重くなるでしょう。

Office365なら、スピル関数などの新関数を使えば軽くなるかもしれません。
(Accessが使用できない環境ということなので365ではないですね）

案③として
VBAで連想配列(Dictionary)を使うとさらに高速化できるでしょう。
Dictionaryは下記の質問でも利用した回答をしてます。

キーの重複があった場合にレコードの削除をしたい

自分がするなら、案①と案③の両方を作成して実データで実験して速い方を採用します。

投稿2022/10/05 14:05

hatena19

総合スコア34352

taku-s

2022/10/07 02:09

hatena19様ご回答ありがとうございます。やはり案②は時間がかかってしまいますよね。実は案③は私も考えたのですが、現時点での自分のレベル感では頭が整理できず、いったん案から外しておりました。とはいえVBAを使いこなすには連想配列は使いどころが多そうですよね。学習を続けます。また、回答いただいた内容をもとに色々と試してみます！今回の質問に限らず、他の方がteratailに質問した内容で、hatena19様の回答に助けられることが何度かありました。本当にありがとうございます。

行動規範の内容に同意します

あなたの回答