Excel (VBA)

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

 
(Windows 10 Pro : Excel 2013)
Transposeの処理時間
投稿日時: 18/09/27 08:21:53
投稿者: ハッタリくん

お世話になります。
 
300列3000行の横長データを縦に変換したく
transpose関数を使ったのですが、処理時間が
長く、時々フリーズしてしまいます。
関数以外に早く処理できる方法はありますでしょうか?
よろしくお願いします。

回答
投稿日時: 18/09/27 09:05:02
投稿者: George

こんにちわ。
 

引用:
300列3000行の横長データを縦に変換したく
transpose関数を使ったのですが、処理時間が
長く、時々フリーズしてしまいます。

どのようなコードなのか提示していただけるとコメントが付くと思います。
 
ちなみに私も300列3000行のデータをコピーして別のシートに行列反転して
貼り付けても2秒ぐらいで終わりました。

回答
投稿日時: 18/09/27 11:28:25
投稿者: WinArrow
投稿者のウェブサイトに移動

こちらでは、1秒もかからいけど
  
参考コード
  
Sub test()
 Dim data1, data2, sttime
     sttime = Timer
     data1 = Sheets("sheet1").UsedRange.Value
     Debug.Print UBound(data1) & " x " & UBound(data1, 2)
     data2 = WorksheetFunction.Transpose(data1)
     Debug.Print UBound(data2) & " x " & UBound(data2, 2)
     Debug.Print Timer - sttime
 End Sub
   
結果
3000 x 300
 300 x 3000
  0.515625
 
 
推察するに
セルの中に配列お扱う数式が、い〜〜っぱい入っているのでは?

投稿日時: 18/09/27 12:08:47
投稿者: ハッタリくん

説明不足で申し訳ありません。
一行ずつ変換して下へ繋げていくので
最終的には40万行10列位になります。

回答
投稿日時: 18/09/27 12:35:29
投稿者: George

ハッタリくん さんの引用:

一行ずつ変換して下へ繋げていくので

なぜ1行ずつ処理をする必要があるのでしょうか?
WinArrow さんが書かれているものもそうですが、
範囲全体を指定するわけにはいかないのでしょうか?

回答
投稿日時: 18/09/27 13:57:03
投稿者: sk

引用:
300列3000行の横長データを縦に変換したく

300 * 3,000 = 900,000 セル。
 
引用:
一行ずつ変換して下へ繋げていくので
最終的には40万行10列位になります。

400,000 * 10 = 4,000,000 セル。
 
明らかにセルの総数の計算が合わないのですが、
そもそも「横長データを縦に変換」とは具体的に
どのようなデータからどのような結果を得ることを
意味しているでしょうか。
 
まずそれを明示されない限り、その目的を達成するために
( TRANSPOSE 関数等によって)「セルの行列変換」という操作が
本当に必要となるのかどうかも評価出来ないでしょう。

回答
投稿日時: 18/09/27 17:23:23
投稿者: WinArrow
投稿者のウェブサイトに移動

ハッタリくん さんの引用:
説明不足で申し訳ありません。
一行ずつ変換して下へ繋げていくので
最終的には40万行10列位になります。

 
skさんの質問と重複しますが、
>300列3000行の横長データ

40万行10列位
にするってことですか?
(300 x 3000 → 10 x 400000)
どのようにすると成立するの?
 
これも説明不足です。

投稿日時: 18/09/27 20:37:09
投稿者: ハッタリくん

皆様、いろいろご意見ありがとうございます。
 
そんなに、ややこしいことではなくて
横に長いデータを縦に変換して
それを全部数珠つなぎにしたいだけです。
何故そんなことが必用か?
はこちらの都合なので割愛させてください。
上記をVBAで処理すると(付随して他にも処理があり上記はほんの一部です)
処理時間がかかるので(単純にトランスポーズだけを切り出しても)
他にいい方法があればアドバイスをお願いしたかった次第です。
コードの提示は必用ありません、こんな方法もあるよ、などの
ご意見を頂きたかったしだいです。
引き続きよろしくお願いいたします。
 

回答
投稿日時: 18/09/27 23:06:31
投稿者: WinArrow
投稿者のウェブサイトに移動

>そんなに、ややこしいことではなくて
 
ややこしくしているのは、あなたですよ。
 
3000行 x 300列を
行列変換すると
10列 x 400,000行
になるという、理論的におかしい説明をするからです。
 
変換後は、10列にしたいということですか?
それだったら、1行ごとのTransposeを10行纏めて実行すればよいでしょう。
セルには数式が入っているのでしたら、再計算を手動に切り替えれば、早くなります。

回答
投稿日時: 18/09/28 10:46:04
投稿者: sk

引用:
そんなに、ややこしいことではなくて
横に長いデータを縦に変換して
それを全部数珠つなぎにしたいだけです。

「 1 行 300 列のセル範囲を 300 行 1 列のセル範囲に
行列変換する処理を、1 つの列を出力先として 3000 行分繰り返す」
(結果、900000 行 1 列のセル範囲が出来上がる)
という例を挙げられるとよいのではないでしょうか。
 
引用:
上記をVBAで処理すると(付随して他にも処理があり上記はほんの一部です)
処理時間がかかるので(単純にトランスポーズだけを切り出しても)
他にいい方法があればアドバイスをお願いしたかった次第です。

コードを例示されないまま代案を求められましても、
実際にパフォーマンスの低下を招いている要因が
Transpose 関数だけにあるのかどうかまでは
第三者には判りませんので、具体的な改善点の
挙げようもありません。
 
例えば「非効率なフロー制御を行なっている」可能性や
「各セルへの値の代入方法に問題がある」可能性なども
考えられるわけですが、実際にそうであるかどうかまでは
具体的なコードが明示されない限り、推測の域を出ません。
 
少なくとも、「 900,000 個のセルを 1 つずつ参照しながら
別のセルに値の代入を繰り返す」ような処理よりは、
「 Transpose 関数による行列変換」と「配列ごとセル範囲に代入」を
組み合わせた方が遥かに高速であるはずです。

投稿日時: 18/09/28 21:03:42
投稿者: ハッタリくん

皆様、つたない質問にご回答ありがとうございました。
 
「 Transpose 関数による行列変換」と「配列ごとセル範囲に代入」
まさに、これで解決できました。
一行ずつ縦横変換して貼り付け、をループで処理していたので。