【ExcelVBA】Powerクエリーでいうピボット解除と同じ処理をVBAで

Office2019を使っています。
下記の様なデータがあります。
ID、商品名1、型番1、商品名2、型番2　～　商品名10、型番10

1レコードが、1つのIDに対して最大10個までの商品名と型番が入っており、
20万行以上あり、毎日増え続けている状況です。

やりたい事は、
ID、商品名1、型番1
ID、商品名2、型番2
ID、商品名3、型番3
～
ID、商品名10、型番10
の様に、商品名と型番を縦に並べる作業です。
Powerクエリーでいう「ピボットの解除」と同じ処理でしょうか？
現在は何とか手作業でやっています。

ここで問題点があり、すぐにお気づきになられると思いますが、
20万行もあると商品名5個を縦に並べた段階で100万行に達してしまいます。
しかし、商品名2以降にはデータが入ってないレコードが結構あり、
空行を削除するとぐっと縮まるんです。
なのでギリギリまで縦につなげて、空行を削除してまたつなげるを繰り返して実現しています。

上記を踏まえて相談したい内容は、
空データを無視(又は削除)しながら、ID、商品名、型番を縦に並べるにはどうしたら良いでしょうか？

質問者からの補足コメント

• 

  皆さま、回答ありがとうございます。
  
  現在、以下の方法で解決できるようにはなりました（まだ満足ではないのですが）
  https://hamachan.info/excel2019-powerquery-seikei/
  
  簡単にいうと、元CSVをPowerQueryエディタに取り込んでから以下の手順です。
  (1)商品名と型番を、「カンマ区切りでマージ」※1商品を1列化できる
  (2)ピボット解除で、全行にIDが付いた状態で縦並びに出来る
  (3)カンマで列分割※これで(1)のように1商品2列形式に戻る。
  (4)フィルタで空白を無くしてからシートに読み込む※これで100万行以内に収まる。
  
  実際には、80万行位になりました。
  しかし新しいCSVで更新をかけると約50分かかるのだけが不安要素です。
  待てば完成するというという意味では解決ですが、もっと早くする方法があったら嬉しいです。

    補足日時：2022/07/11 18:55

No.4 回答者： KenKen_SP 回答日時： 2022/07/11 20:22

> 新しいCSVで更新をかけると約50分かかる

リンクテーブルになってないですかね？
リンクテーブルは遅いので、インポートしちゃって下さい。

＞解決できるようにはなりました

それは何よりです。

＞もっと早くする方法があったら嬉しいです。

最低限しかしてないですけど、#3 はご質問文の仕様に従い、25万件のサンプルデータを４秒弱で CSV に書き出してます。80万件以下になるなら CSVを Excel で開き直しても１分がいいとこでしょう。

試す価値もないでしたかね？

この回答へのお礼

いえいえ、とにかく時間内に終わらせなければいけなく、
新しい試みにトライする余裕がありませんでした。
そんなに効率良く済む可能性があるなら早く試すべきでした。
時間を見て試したいと思います。
まずセル位置とか読み解いて実際のフォーマットに当てはめなきゃいけないんですよね。。。
頑張ります

お礼日時：2022/07/12 00:47

No.3 回答者： KenKen_SP 回答日時： 2022/07/11 18:07

こんにちは。

既に100万行超えがわかっているなら、最早 Excel の範疇ではありません。Access などのデータベースを使った方が良いでしょう。

とりま、100万件は超えるのだろうから CSV に書き出してみました。
件数多いので

配列で処理すべき？
テキスト書き出しは ADO STREAMが速い？

とか色々考えましたが、途中で面倒になってダレてます☆

そこそこ速いと思いますが、凝らずに普通に書いても十分だったかもしれない。ご参考程度に。

レイトバインド嫌いなので、VBE で次の参照設定して下さい。
Microsoft ActiveX Data Objects x.x Library
(x.x はお使いの環境によって異なりますが最新のものを）

エラートラップとか保存場所とかは適当にやって下さい。

Sub sampleProc()

　　'スピードも速くならないしエラーの原因になるので大きくし過ぎない
　　Const MAX_BUFFER_SIZE As Long = 20000

　　Dim src_data　 As Range
　　Dim adoStream　As ADODB.Stream
　　Dim srcBuf　　 As Variant
　　Dim outBuf　　 As Variant
　　Dim src_row　　As Long, src_col As Long
　　Dim loop_count As Long
　　Dim i　　　　　As Long
　　Dim pos　　　　As Long
　　
　　'データ範囲
　　Set src_data = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
　　'見出しがあるならデータ範囲から除去
　　Set src_data = src_data.Offset(1).Resize(src_data.Rows.Count - 1)
　　
　　'データ読込回数
　　loop_count = Application.WorksheetFunction.RoundUp(src_data.Rows.Count / MAX_BUFFER_SIZE, 0)
　　
　　'CSVデータ出力準備
　　Set adoStream = New ADODB.Stream
　　adoStream.Charset = "UTF-8"
　　adoStream.Open
　　
　　'データ読込→整形→CSV書き出し
　　ReDim outBuf(1 To 3)
　　pos = 1
　　For i = 1 To loop_count
　　　　srcBuf = Range(src_data.Rows(pos), src_data.Rows(pos + MAX_BUFFER_SIZE - 1)).Value
　　　　'
　　　　For src_row = 1 To UBound(srcBuf)
　　　　　　For src_col = 2 To UBound(srcBuf, 2) Step 2
　　　　　　　　If Len(srcBuf(src_row, src_col)) Then
　　　　　　　　　　outBuf(1) = srcBuf(src_row, 1)
　　　　　　　　　　outBuf(2) = srcBuf(src_row, src_col)
　　　　　　　　　　outBuf(3) = srcBuf(src_row, src_col + 1)
　　　　　　　　　　adoStream.WriteText Join$(outBuf, ",") & vbCrLf
　　　　　　　　End If
　　　　　　Next
　　　　　　
　　　　Next
　　　　'
　　　　pos = pos + MAX_BUFFER_SIZE
　　　　'
　　　　'一応フリーズ対策
　　　　Application.StatusBar = "ただいま処理中... STEP " & CStr(i) & "/" & CStr(loop_count)
　　　　DoEvents
　　Next
　　
　　'保存
　　adoStream.SaveToFile "C:\temp\test.csv", adSaveCreateOverWrite
　　
　　adoStream.Close
　　Set adoStream = Nothing
　　Application.StatusBar = ""
　　
　　MsgBox "C:\temp に CSV 書き出した☆"

End Sub

No.2 回答者： fujillin 回答日時： 2022/07/07 10:53

こんにちは

＞20万行以上あり、毎日増え続けている状況です。
毎回ゼロから同じデータを処理しているのでしょうか？
それとも、毎回20万行が追加されるということ？

大部分が同じデータで、追加分を含めて処理しているのなら、処理済みのデータを何度も処理する手間が無駄と思います。
全体の方法を見直して、追加されたデータだけを処理すれば済むような考え方にすれば良いのではないでしょうか？

毎回、新規のデータが20万行あるような場合は、エクセルで扱うこと自体を考え直した方がよさそうにも思います。
整形したところで、人が目視で扱うようなデータ量とは思えませんので、整形すること自体の意味も疑問です。


「そんなこと聞いてないよ。　どうしてもこのままやりたいんだ！」というのであれば・・

1）どこかの3列分に、ID一つを10行分に単純にコピー参照する関数を設定
　（当然、空行ができます）
2）別の列に、1）を詰めて表示する関数を設定

しておけば、何もしなくても自動で整形できます。
100万行とのことなので、時間はかかる可能性はありますが、手作業よりは速いでしょう。

この回答へのお礼

ありがとうございます。
何となく仰る考え方は分かった気がするのですが以下により誤認と分かるかもしれません・・・

1) は例えば別シートに縦に並べるように参照式を設定したフォーマットを作っておく事でしょうか？
　元の1行目→別シートでは10行目まで消費
　元の2行目→別シートでは20行目まで消費
　これを簡単に設定する方法が思い浮かびません^^;
2)については、詰めて表示する関数が分からないので教えてください。
　しかし1)で20万行が200万行に膨れ上がってしまうから、最後に詰めるのだ　　
　と難しくないですか？
よってマクロで空白判定しながら縦に並べる必要があるのかとぼんやり考えていました。

お礼日時：2022/07/08 17:41

No.1 回答者： Qchan1962 回答日時： 2022/07/07 09:27

＞現在は何とか手作業でやっています。

記録できなかったかな？