[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBA 多次元配列→1次元配列への変換コードの高速化』（初心者X）
下記コードにて要約に記載した内容の処理を行っています。
あるプログラム内でこのファンクションの使用回数が多いため
この部分の処理高速化が全体の処理高速化になるかもと思い、
お聞きしています。
調べたコードをほぼそのまま使っているため、別の処理方法や
高速化へのヒントなど有識者の方からご意見いただきたいです。
以上、よろしくお願いします。
Function getSplit(ByVal arr As Variant)
'多次元配列を1次元配列に変換している
Dim i As Long
Dim v As String
If UBound(arr, 1) - LBound(arr, 1) > 0 Then
v = Join(WorksheetFunction.Index(arr, 1), ",")
For i = (LBound(arr, 1) + 1) To UBound(arr, 1)
v = v & "," & Join(WorksheetFunction.Index(arr, i), ",")
Next
Else
v = Join(WorksheetFunction.Index(arr, 0), ",")
End If
getSplit = Split(v, ",")
End Function
＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞
私の環境では、 単純に1次元配列を準備し、入れ替えた方が、 速かったです。
Sub sample()
Dim src(999, 0) As Long
Dim temp As Variant
ReDim temp(UBound(src, 1))
Dim i&
For i = LBound(src, 1) To UBound(src, 1)
temp(i) = src(i, 0)
Next
End Sub
（tkit） 2023/02/17(金) 17:12:36
Function Two2One(ByVal vv As Variant) As Variant
Dim v As Variant
Dim r() As Variant
Dim i As Long
ReDim r(UBound(vv, 1) * UBound(vv, 2))
For Each v In vv
r(i) = v
i = i + 1
Next
Two2One = r
End Function
Sub test()
Dim v
v = Two2One(Range("A1:D10").Value)
Range("A20").Resize(, UBound(v)).Value = v
End Sub
直感でたぶんこんな感じとか。。。
場合によっては微調整必要な気がします。
あ、縦優先か横優先かでも変わりますね。
（まっつわん） 2023/02/17(金) 17:26:34
v = v & "," & ...
↑こういう文字列連結は、 伸びれば伸びるほどめちゃくちゃ遅くなります。
びっくりするほど長くならないんだったらあんまり気にしないでよいでしょうけど、 高速化を目指す局面では、割と真っ先に避けて通る手法じゃないかなーと思います。
（白茶） 2023/02/17(金) 17:49:44
計測してみましょう ちょっと前に 配列を引数にするときのByRefとByValの話があった気がしましたが
Sub test()
Dim ary()
With Range("A1:Z2000")
.Formula = "=ROW() & ""="" & COLUMN()"
ary = .Value
End With
t = Timer
ret = getSplit(ary)
Debug.Print "getSplit"; Timer - t
t = Timer
ret = D2AryToVector(ary)
Debug.Print "D2AryToVector"; Timer - t
Stop
End Sub
Function D2AryToVector(ByRef ary() As Variant) As Variant()
Dim n1 As Long, n2 As Long, ret() As Variant
Dim i As Integer, j As Integer
n1 = (UBound(ary, 1) - LBound(ary, 1) + 1)
n2 = (UBound(ary, 2) - LBound(ary, 2) + 1)
ReDim ret(1 To n1 * n2)
For i = LBound(ary, 1) To UBound(ary, 1)
For j = LBound(ary, 2) To UBound(ary, 2)
ret((i - 1) * n2 + j) = ary(i, j)
Next
Next
D2AryToVector = ret
End Function
Function getSplit(ByVal arr As Variant)
'多次元配列を1次元配列に変換している
Dim i As Long
Dim v As String
If UBound(arr, 1) - LBound(arr, 1) > 0 Then
v = Join(WorksheetFunction.Index(arr, 1), ",")
For i = (LBound(arr, 1) + 1) To UBound(arr, 1)
v = v & "," & Join(WorksheetFunction.Index(arr, i), ",")
Next
Else
v = Join(WorksheetFunction.Index(arr, 0), ",")
End If
getSplit = Split(v, ",")
End Function
（´・ω・`） 2023/02/17(金) 17:53:15
これですね！！
（稲葉） 2023/02/17(金) 18:19:12
元のGetSplitを変更して、 Indexを使う・使わない Midステートメントを使う・使わない で試したところ、こんな感じでした。 文字の結合より、Index（またはJoinか？）のほうが重要って感じですね。 0秒 Indexを使わず、Midステートメントを使う 4秒 Indexを使わず、Midステートメントも使わない 17秒 Indexを使って、Midステートメントを使う
'IndexとMidステートメントでxに文字列を格納する
Function getSplit2(ByRef arr As Variant)
Dim i As Long
Dim x As String
Dim buf As String
Dim cnt As Long
x = String(10000000, " ")
cnt = 1
For i = (LBound(arr, 1) + 1) To UBound(arr, 1)
buf = Join(WorksheetFunction.Index(arr, i), ",")
Mid(x, cnt, Len(buf) + 1) = "," & buf
cnt = cnt + Len(buf) + 2
Next
getSplit2 = Split(x, ",")
End Function
'Indexを使わず、Midステートメントを使ってxに文字列を格納する
Function getSplit3(ByRef arr As Variant)
Dim i As Long
Dim j As Long
Dim x As String
Dim cnt As Long
x = String(10000000, " ")
cnt = 1
For i = (LBound(arr, 1) + 1) To UBound(arr, 1)
For j = (LBound(arr, 2) + 1) To UBound(arr, 2)
Mid(x, cnt, Len(arr(i, j)) + 1) = "," & arr(i, j)
cnt = cnt + Len(arr(i, j)) + 2
Next j
Next
getSplit3 = Split(x, ",")
End Function
'Indexを使わず、配列を変数xに入れてカンマで結合する
Function getSplit4(ByRef arr As Variant)
Dim i As Long
Dim j As Long
Dim x As String
For i = (LBound(arr, 1) + 1) To UBound(arr, 1)
For j = (LBound(arr, 2) + 1) To UBound(arr, 2)
x = x & "," & arr(i, j)
Next j
Next
getSplit4 = Split(x, ",")
End Function
（稲葉） 2023/02/17(金) 19:21:26
＞元の配列か,を∈文字列だったら成り立たないコードですよね んまーその通りだけど、そこは論点ではないからいいんでないかい？ Chr(2)あたりに置き換えてしまえば使えるし、そもそも使わない。
すでにまっつわんさんの回答で結論出てるから、白茶さんと（´・ω・`）さんの見て じゃー比較してみようかーって思い立っただけなんだけど、私に対してのご意見だったのかしら？ （稲葉） 2023/02/17(金) 20:13:45
早とちりでしたm(_ _)m 勉強になったので、良い質問でしたね！！ （稲葉） 2023/02/17(金) 20:46:24
| 文字の結合より、Index（またはJoinか？）のほうが重要って感じですね。 Indexの使い方によってはかなり差が出ます。
以下の実験結果ですと、 getSplit 16.56 秒 getSplitVER2 0.28 秒 という感じです。 参考になれば幸いです。
Sub test()
Dim ary()
Dim ret
Dim t
With Range("A1:Z2000")
.Formula = "=ROW() & ""="" & COLUMN()"
ary = .Value
End With
t = Timer
ret = getSplit(ary)
Debug.Print "getSplit"; Timer - t
t = Timer
ret = getSplitVER2(Range("A1:Z2000"))
Debug.Print "getSplitVER2"; Timer - t
End Sub
Function getSplit(ByVal arr As Variant)
Dim i As Long
Dim v As String
For i = LBound(arr, 1) To UBound(arr, 1)
v = v & "," & Join(WorksheetFunction.Index(arr, i), ",")
Next
getSplit = Split(Mid(v, 2), ",")
End Function
Function getSplitVER2(rng As Range)
Dim i As Long
Dim v As String
For i = 1 To rng.Rows.Count
v = v & "," & Join(Application.Index(rng.Rows.Item(i).Value2, 0#), ",")
Next
getSplitVER2 = Split(Mid(v, 2), ",")
End Function
誤解の無いように追記しておきます。 ・質疑の本論は既に提示されているとおりです。配列にそのまま書き込んだほうが早いです。 ・また、v = v & "," & Join(WorksheetFunction.Index(arr, i), ",") のような書き方は内部でコピーが何度も行われるので、速度低下の要因になることも明らかです。 ・上記のコメントは、Indexの使い方に絞ったコメントです。(一部修正・追加) （γ） 2023/02/18(土) 01:18:11
γさんありがとうございます もう少し噛み砕いて説明いただいてもよろしいですか？ ・ApplicationのIndexがよいのか、Rangeオブジェクトなのが良いのか ・v = v & "," & の処理において、なぜ配列のループより高速になるのか 比較対象→4秒 Indexを使わず、Midステートメントも使わない 回数が多いため？ （稲葉） 2023/02/18(土) 07:28:39
私もよくわかりませんが、 | ・v = v & "," & の処理において、なぜ配列のループより高速になるのか | 比較対象→4秒 Indexを使わず、Midステートメントも使わない | 回数が多いため？ ■４秒との比較という点では、ご指摘のとおり、 横方向の連結を一セルごとに文字列連結している点が ブレーキになっているのではないでしょうか。
■Indexの使い方という点で言うと、 Rangeオブジェクトでもそうですが、 ss = Join(Application.Index(r.Value2, i)) よりも ss = Join(Application.Index(r.Rows.Item(i).Value2, 0#)) という書き方のほうが断然早いということです。
これも理由を詳細承知しているわけではありませんが、 広い範囲を対象にSliceする（i行目を取り出す）よりも、 Rows.Item(i)を利用することでSpeedUpが図られているんじゃないかと思います。 （γ） 2023/02/18(土) 09:47:40
回答頂いた内容の反映と処理速度結果につきましてはこれからですので
取り合えずお礼申し上げます。
したいことが実装できるが遅くなる。
処理速度については、初心者にとって難しい課題となっていきそうです。
以上、ありがとうございました。
（初心者X） 2023/02/20(月) 08:52:10
ちょっと追加でテストしてみましたが、 getSplitVER3(スライス型なのか、クロス参照型と考えるのか分かりませんが）の方が 微差ながら速いように感じました。
※Value2プロパティを使わないからなのでしょうかねぇ・・
Sub test()
Dim ary()
Dim ret
Dim t
With Range("A1:Z2000")
.Formula = "=ROW() & ""="" & COLUMN()"
ary = .Value
End With
t = Timer
ret = getSplitVER2(Range("A1:Z2000"))
Debug.Print "getSplitVER2"; Timer - t
ret = Empty
Dim a()
a = [column(a1:z1)]
t = Timer
ret = getSplitVER3(Range("A1:Z2000"), a)
Debug.Print "getSplitVER3"; Timer - t
ret = Empty
End Sub
Function getSplitVER2(rng As Range)
Dim i As Long
Dim v As String
For i = 1 To rng.Rows.Count
v = v & "," & Join(Application.Index(rng.Rows.Item(i).Value2, 0#), ",")
Next
getSplitVER2 = Split(Mid(v, 2), ",")
End Function
Function getSplitVER3(rng As Range, a())
Dim i As Long
Dim v As String
For i = 1 To rng.Rows.Count
v = v & "," & Join(Application.Index(rng, i, a), ",")
Next
getSplitVER3 = Split(Mid(v, 2), ",")
End Function
（半平太） 2023/02/20(月) 23:18:42
なるほど、そういう方法もありましたか。さすがです。
なお、このほか、rng.Rows(i)は二次元配列ですが、
Transposeを二回掛けて、これを一次元化する方法もあります。
With Application
ss = Join(.Transpose(.Transpose(rng.Rows(i))))
End With
Value2とIndexを使用したのは、この方式のほうが、Transposeを二回方式よりも、 わずかながら早かった、ということのようです。 （10数年前の議論でした。改めて試してみましたが、さほどの差は無いように見受けました。 環境によって変わってくるのかもしれません。）
Index関数には、 ・第一引数を配列とするものと、 ・第一引数をセル参照とするもの とがありますが、配列のほうが実行速度は格段に低下するということなんでしょうか。 これは内部事情なので、外部からは理由はわかりません。 そうした事実を承知して使うということだけなんでしょう。
質問者さんは、多次元配列と一般化していますが、 ワークシート上の領域がもともとのデータで、それを二次元配列にしたものなんでしょうか。 また、それを使って何をされようとしているのかも併せて示されるとよかったかもしれません。 （現在の方式は、配列を対象にIndexを使っているので、大分遅いでしょうね。）
既に皆さんから指摘がありますように、
・単に一次元配列にするのであれば、直接、1次元配列に要素ごとに転記するのが最速でしょう。
ロジックもさほど難しいことはありません。
・一行ごとにカンマ区切りで文字列化して連結し、それをSplitするのであれば、
・Rangeオブジェクトを使って一行取り出しを行うほうが早いです
・Midステートメントを使って、予め書き込み先の領域を確保したうえで処理する方式を適宜利用すると高速化できます。
単純に文字列連結を繰り返すのは速度上不利になることは、よく知られた事実です。
（最近は処理能力が高まったり、そうした大量の文字列処理はExcelでは行われないこともあって、
余り議論になることは少ないようです。（昔は、MidじゃなくてMid$としなさい、などと言われたものです））
参考になれば幸いです。 （γ） 2023/02/21(火) 08:55:11
回答を拝見していて気づきましたが、
配列に対するEachは列優先、Rangeに対するEachは行優先
となるので、注意が必要です。（詳細は下記で説明しています）
（回答者さんも気づかれているようですが、念のため明記しておきます）
【設例】
A列 B
1行 1 2
2 3 4
3 5 6
というデータがあったとき、
【For Each の列挙の順序の差異】
(1)For Each v In Range("A1:B3").Value と配列に対して走査すると、
1,3,5,2,4,6 の順に列挙されますが（いわゆる列優先）
(2)For Each v In Range("A1:B3") と Rangeオブジェクトに対して走査すると、
1,2,3,4,5,6 の順に列挙されます（いわゆる行優先）。
実際に確かめるには、下記のコードを実行してみてください。
なお、渡した配列に対して、行優先の列挙をするには、For Eachではなく、
Dim j&, k&
For j = 1 To UBound(vv, 1)
For k = 1 To UBound(vv, 2)
r(i) = vv(j, k)
i = i + 1
Next
Next
のようなループ処理を使うことになります。
なぜこういう違いが発生するのか、これも詳細不明ですが、 気を付ける必要があります。
【参考コード】
Rem 配列に対するEachは列優先、Rangeに対するEachは行優先
Sub test()
Dim v1, v2
'数値サンプルのセット
[A1] = 1: [B1] = 2
[A2] = 3: [B2] = 4
[A3] = 5: [B3] = 6
'配列を渡してFor each
v1 = 配列渡し(Range("A1:B3").Value)
Range("A5").Resize(, UBound(v1)).Value = v1
'Rangeを渡してFor Each
v2 = Range渡し(Range("A1:B3"))
Range("A6").Resize(, UBound(v2)).Value = v2
End Sub
Function 配列渡し(vv As Variant) As Variant
Dim v As Variant
Dim r() As Variant
Dim i As Long
ReDim r(1 To UBound(vv, 1) * UBound(vv, 2))
i = 1
For Each v In vv
r(i) = v
i = i + 1
Next
配列渡し = r
End Function
Function Range渡し(rng As Range) As Variant
Dim v As Range
Dim r() As Variant
Dim i As Long
ReDim r(1 To rng.Rows.Count * rng.Columns.Count)
i = 1
For Each v In rng
r(i) = v
i = i + 1
Next
Range渡し = r
End Function
（γ） 2023/02/21(火) 13:07:49
γさん 返答が遅くなってすみません。また、解説ありがとうございました。 あれから色々試したのですが、配列に対して各次元を切り取るのはやっぱり遅いですね・・・ 半平太さんのクロス参照型？を配列で試しても速度に影響はなかったので、行または列を取得するうえでは Range型−Indexが一番早そうですねぇ・・・。
まっつわんさんのコードと比較していたら、For-ForよりもForEachのほうが速度早かったので
γさんのコードお借りして、行の優先を指定してもいいかもですね。
Function 配列渡し(vv As Variant, Optional 行優先 As Boolean = False) As Variant
Dim v As Variant
Dim r() As Variant
Dim i As Long
ReDim r(1 To UBound(vv, 1) * UBound(vv, 2))
If 行優先 = True Then vv = Application.Transpose(vv) '★
i = 1
For Each v In vv
r(i) = v
i = i + 1
Next
配列渡し = r
End Function
（稲葉） 2023/02/21(火) 13:46:36
>なぜこういう違いが発生するのか、これも詳細不明ですが、 ホントのところは、マイクロソフトの中の人しらわからないでしょうから 全くの想像ですが
昔からアプリケーションを作るプログラミング言語は速度的に有利なCでしたよね CでつくるとCの言語使用上、配列のメモリ格納が行優先になるのが自然で・・・
で、マクロ言語としてBasicを選んだわけですが、 Basicの言語仕様は配列のメモリ格納は列優先なので、 しょうがなくそうなっている感じなんでしょうね
FortranからCで作られたライブラリを呼ぶとき苦労しました... つい昔話をしたくてお邪魔してしまいました （´・ω・`） 2023/02/21(火) 13:59:00
稲葉さん、ありがとうございます。 | 半平太さんのクロス参照型？を配列で試しても速度に影響はなかったので、 | 行または列を取得するうえでは Range型−Indexが一番早そうですねぇ・・・。 そうですそうです。書き洩らしました。私も実験していました。
´・ω・`さん、ありがとうございます。 C/C++は行優先ですね。Pythonもそうですか。 一方、Fortran,R,Juliaなど数値計算系は列優先ですね。MATLAB(デフォルトの場合)も。 数値計算効率も列優先のほうがいいようですね。 （私のお気に入りのJuliaはC/C++,Fortranに匹敵する速度でありながら、動的言語です。）
なお、 [[20220313003116]]の終わりに、余談として、 行優先、列優先に関する速度実験結果を書いたことがあるので、 興味あるかたはご笑覧ください。 （γ） 2023/02/21(火) 15:27:56
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.