■ 並べ替え（ソート）をマスターしよう！
ExcelVBAには、組み込みソート関数が存在しません。その代りに強力なRangeオブジェクトのSortメソッドが用意されています。これほど強力なソートメソッド（ソート機能）が用意されているプログラミング言語は他にはないでしょう。
このSortメソッドを使えば、2次元の配列をいとも簡単に複数Key指定し、望み通りの順番に並べ替えることが可能です。しかも、そのスピードはVBAではありえないほど高速です。このSortメソッドは本来、エクセルのネイティブな機能として用意されている「並べ替え」をVBAから利用するために用意されているメソッドです。つまり、Excelのもつ並べ替えの機能自体が驚くほど高速であるといえます。
ただしこのSortメソッドの並べ替えを利用する場合、シート上にあるデータ以外については、いったんワークシートにデータを格納する必要があります。さらに並べ替えの結果をワークシートから取得し、元の配列に格納し直す必要があります。そのハンデを考慮しても、このSortメソッドを利用する価値は十分にあります。
そしてせっかくVBAを利用しているので、一般的なソート関数（ユーザー関数）やADOレコードセットオブジェクトを利用した並べ替えなど、さまざまなテクニックを知っておいたほうが、いろいろなケースで応用できて便利でしょう。
さっそく、ソート（並べ替え）テクニックを解説していきましょう。
■RangeオブジェクトのSortメソッドによる並べ替え
（例）
Public Sub test()
Dim i As Long
Dim MyLng() As Long
Dim MyRngSet As Range
Dim MyRngGet As Variant
Const CstLng As Long = 10
ReDim MyLng(1 To CstLng, 0)
For i = 1 To UBound(MyLng)
MyLng(i, 0) = Int(Rnd * UBound(MyLng))
Next i
Set MyRngSet = Range("A1:A" & UBound(MyLng))
MyRngSet = MyLng
MyRngSet.Sort Key1:=Range("A1")
MyRngGet = MyRngSet
For i = 1 To UBound(MyLng)
MyLng(i, 0) = MyRngGet(i, 1)
Next i
End Sub
この例では、配列MyLngに10個の要素の乱数を格納し、アクティブシートのセル範囲A1:A10にセットします。MyRngSet.Sort Key1:=Range("A1") の部分でセル範囲をソートした後、元の配列MyLngに格納しなおしています。
※ CstLngの値を変えると、並べ替える配列の要素数の上限を変更できます。
Sortメソッドには、たくさんの引数があります。ヘルプでは全ての引数は省略可能となっていますが、Key1の引数を省略すると実行時エラー'1004' RangeクラスのSortメソッドが失敗しました。のエラーが発生します。
MyRngSet.Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, _
DataOption1:=xlSortNormal
各引数はこのようになっています。2次元配列の場合、3つまでKeyを指定することが可能です。その場合は、Key2:= 、Order2:= のように記述を追加します。
| Key |
Key項目として利用するフィールドのテキストまたはRangeオブジェクトを指定します。 |
| Order |
xlAscending (既定値) で昇順に、xlDescending で降順で並べ替えます。 |
| Header |
xlNo (既定値) でヘッダなし、xlGuess でヘッダがあるかどうかチェック、xlYes でヘッダありとして並べ替えます。 |
| OrderCustom |
ユーザー定義の並べ替えリスト内の番号を示す1から始まる整数です。省略すると、通常の並べ替えが使用されます。 |
| MatchCase |
Trueの場合、大文字と小文字を区別して、Falseの場合、大文字と小文字を区別しないで並べ替えを行います。 |
| Orientation |
xlSortRows (既定値) で行単位の並べ替え、xlSortColumns で列単位の並べ替えを行います。 |
| SortMethod |
xlPinYin (既定値)で中国語の発音表記の順、xlStroke で各文字の総画数の順に並べ替えます。 |
| DataOption |
xlSortNormal (既定値) で数値とテキストを別々に、xlSortTextAsNumbers でテキストを数値データとして並べ替えます。 |
Header、Order、OrderCustom、Orientation の設定は、並べ替え機能を使用するまたは、Sortメソッドを利用するごとに、エクセル内部にパラメータが保存されます。これらの引数を省略した場合、前回保存されている値が使用されるため注意が必要です。
Sortメソッドで確実な並べ替えを行うためには、これらの引数を明示的に設定する必要があります。
※ 要素数の上限値に注意してください。Excel2003までは65536行、Excel2007は1048576行を超える並べ替えはエラーになります。
※ シートを利用する上で"1-2"や"01/01"などのデータは日付として、"-"や"="で始まる文字列は数式として扱われます。注意してください。
■Quickソート（クイックソート）による並べ替え
（例）
Public Sub test()
Dim i As Long
Dim MyLng() As Long
Dim LngMin As Long
Dim LngMax As Long
Const CstLng As Long = 10
ReDim MyLng(1 To CstLng, 0)
LngMin = LBound(MyLng)
LngMax = UBound(MyLng)
For i = 1 To UBound(MyLng)
MyLng(i, 0) = Int(Rnd * UBound(MyLng))
Next i
Call DoQuickSort(MyLng, LngMin, LngMax)
End Sub
Public Sub DoQuickSort(ByRef pMyLng As Variant, ByVal pLngMin As Long, ByVal pLngMax As Long)
Dim i As Long
Dim j As Long
Dim Base As Long
Dim Buf As Long
Base = pMyLng((pLngMin + pLngMax) \ 2, 0)
i = pLngMin
j = pLngMax
Do
Do While pMyLng(i, 0) < Base
i = i + 1
Loop
Do While pMyLng(j, 0) > Base
j = j - 1
Loop
If i >= j Then
Exit Do
Else
Buf = pMyLng(i, 0)
pMyLng(i, 0) = pMyLng(j, 0)
pMyLng(j, 0) = Buf
i = i + 1
j = j - 1
End If
Loop
If pLngMin < i - 1 Then
Call DoQuickSort(pMyLng, pLngMin, i - 1)
End If
If pLngMax > j + 1 Then
Call DoQuickSort(pMyLng, j + 1, pLngMax)
End If
End Sub
この例では、配列MyLngに10個の要素の乱数を格納し、DoQuickSort関数で並べ替えを行います。引数を参照渡ししているのでDoQuickSortの処理が終わった時点で、配列MyLngの各要素も並べ替えが終了しています。
クイックソートは汎用的な並べ替え方法で他言語でもよく利用されます。DoQuickSort関数が本体のプロシージャです。この中で再帰的に呼び出しを行い並べ替えを行います。
※ 再起処理に関してはこちらでも解説しています。いろいろな繰り返し方法を知ろう！
クイックソートは非常に高速です。それはすなわち、クイックソートのアルゴリズムが非常によく考えられたソートアルゴリズムであるといえるでしょう。
クイックソートのアルゴリズムは簡単に説明すると、ベースになる基準値（配列の真ん中の要素）を定め、左から（配列の先頭から）さらに、右から（配列の後方から）基準値以下の値、また以上の値を調べ、見つかるごとに値を入れ替えていきます。これを繰り返し、さらに再起処理で要素の前半部分、後半部分と並べ替えを行わせ、最終的に正しい昇順（もしくは降順）に並べ替えるというものです。
本体の部分は次のような記述もできます。
Public Sub DoQuickSort(ByRef pMyLng As Variant, ByVal pLngMin As Long, ByVal pLngMax As Long)
Dim i As Long
Dim j As Long
Dim BaseNum As Long
Dim BaseVal As Long
Dim Buf As Long
If pLngMin >= pLngMax Then
Exit Sub
Else
BaseNum = (pLngMin + pLngMax) \ 2
BaseVal = pMyLng(BaseNum, 0)
pMyLng(BaseNum, 0) = pMyLng(pLngMin, 0)
i = pLngMin
For j = pLngMin + 1 To pLngMax
If pMyLng(j, 0) < BaseVal Then
i = i + 1
Buf = pMyLng(i, 0)
pMyLng(i, 0) = pMyLng(j, 0)
pMyLng(j, 0) = Buf
End If
Next
pMyLng(pLngMin, 0) = pMyLng(i, 0)
pMyLng(i, 0) = BaseVal
Call DoQuickSort(pMyLng, pLngMin, i - 1)
Call DoQuickSort(pMyLng, i + 1, pLngMax)
End If
End Sub
こちらでも、同じくクイックソートを行います。アルゴリズムが若干違いますが、考え方は同じです。
コードは記述しませんが、クイックソートのほかにも有名なソート法がいくつかあります。名前だけでも紹介しておきます。
| バブルソート |
最も古くからあるアルゴリズムで、隣り合う要素を比較、入替を繰り返して行う方法です。 |
| 挿入ソート |
バブルソートの発展型です。要素を入れ替えるのではなく正しい位置に挿入していく方法です。 |
| 選択ソート |
挿入ソートとバブルソートの中間にあたり、要素の位置を交換することで正しい配置にします。 |
| ヒープソート |
予備ソートで親子ツリーを作成、次にツリーを使って全体をソートする2段階ソートです。 |
| シェルソート |
ある間隔で要素を取り出した部分を整列、更に間隔をつめた部分を取り出し整列します。 |
ソートアルゴリズムに関してはこの他にも数多くの手法があり、とてもここでは解説しきれませんが、このようなものがあるということはぜひ知っておいてください。
■ADOオブジェクトのSortプロパティによる並べ替え
ExcelVBAからでも、ADOレコードセットオブジェクトが利用できます。レコードセットオブジェクトのSortプロパティを用いることでも、簡単に並べ替えが可能です。
※ ADOレコードセットの詳しい解説は、こちらを参照してください。ADOレコードセットを操作しよう1、2
※ 参照設定で「Microsoft ActiveX Data ObjectX.X Library」にチェックを入れる必要があります。（X.Xはバージョン名）
（例）
Public Sub test()
Dim i As Long
Dim MyLng() As Long
Dim MyRngSet As Range
Dim SQL As String
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Const CstLng As Long = 10
ReDim MyLng(1 To CstLng, 0)
For i = 1 To UBound(MyLng)
MyLng(i, 0) = Int(Rnd * UBound(MyLng))
Next i
Set MyRngSet = Range("A1:A" & UBound(MyLng))
MyRngSet = MyLng
Set CN = New ADODB.Connection
CN.Provider = "Microsoft.Jet.OLEDB.4.0"
CN.Properties("Extended Properties") = "Excel 8.0"
CN.Open ThisWorkbook.FullName
SQL = "SELECT * FROM [" & ActiveSheet.Name & "$]"
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient
RS.Open SQL, CN, adOpenStatic, adLockReadOnly
RS.Sort = RS.Fields(0).Name
For i = 1 To UBound(MyLng)
MyLng(i, 0) = RS.Fields(0)
RS.MoveNext
Next i
End Sub
この例では、配列MyLngをアクティブシートのセル範囲A1:A10にセットした後、自ブックのアクティブシートにADO接続し、レコードセットを作成、RS.Sort = RS.Fields(0).Name で並べ替えたのち、配列MyLngに格納しなおしています。
CN.Open ThisWorkbook.FullName は自ブックへフルパスで接続を行っています。
SQL = "SELECT * FROM [" & ActiveSheet.Name & "$]" は現在アクティブなシートをSQL文字列に指定しています。RS.CursorLocation = adUseClient はSortプロパティを使用するためのCursorLocationプロパティの値として指定が必要です。
Sortプロパティの構文は次のようになります。
［ Recordsetオブジェクト.Sort = "フィールド名 並び順の指定" ］
・フィールド名 レコードセットのフィールド名（項目名）を指定します。
・並び順 ASC で昇順に、DESC で降順に並べ替えます。
（例）
RS.Sort = "ID ASC,DATE DESC"
この例では、"ID"フィールドを昇順に、"DATE"フィールドを降順に並べ替えます。
サンプルコードでは1次元要素の並べ替えを行っているだけですが、複数のフィールドがあるシートならKey項目を複数指定できます。また、RangeオブジェクトのSortメソッドのように一度に3Keyまでというような制限はありません。
また次のようにすれば、シートにデータを転写しなくてもレコードセットオブジェクト単体で並べ替えを行うことも可能です。
（例）
Public Sub test()
Dim i As Long
Dim MyLng() As Long
Dim MyRngSet As Range
Dim RS As ADODB.Recordset
Const CstLng As Long = 10
ReDim MyLng(1 To CstLng, 0)
For i = 1 To UBound(MyLng)
MyLng(i, 0) = Int(Rnd * UBound(MyLng))
Next i
Set RS = New ADODB.Recordset
RS.Fields.Append "TMP", adInteger
RS.CursorLocation = adUseClient
RS.Open
For i = 1 To UBound(MyLng)
RS.AddNew
RS.Fields(0) = MyLng(i, 0)
RS.Update
Next i
RS.Sort = RS.Fields(0).Name
For i = 1 To UBound(MyLng)
MyLng(i, 0) = RS.Fields(0)
RS.MoveNext
Next i
End Sub
この例では、レコードセットを作成したら、RS.Fields.Append "TMP", adInteger の部分で"TMP"というフィールドを追加しています。その後、Forループで配列MyLngの中身をレコードセットに転記、RS.Sort = RS.Fields(0).Name の部分で並べ替えた後、再び配列MyLngに格納しなおしています。
この例も、追加するフィールドを増やすことで複数のフィールドをもつ2次元配列を複数のKey項目で並べ替えることが可能です。
最後に、参考までに各ソート法ごとの処理時間を計測してみます。（1万個の乱数値による並べ替え）
| ソート方法（単位ミリ秒） |
計測1回目 |
計測2回目 |
計測3回目 |
| RangeオブジェクトのSortメソッド |
110 |
110 |
94 |
| クイックソート（上サンプル） |
47 |
62 |
62 |
| クイックソート（下サンプル） |
62 |
63 |
79 |
| ADOオブジェクト（上サンプル） |
907 |
906 |
954 |
| ADOオブジェクト（下サンプル） |
266 |
265 |
266 |
こうしてみると、クイックソートのスピードには目を見張るものがあります。また、エクセル本来のSortメソッドもシートへの読み書きというハンデを負ってなおこのスピードは特筆すべきです。
以上、さまざまなソート（並べ替え）について解説しました。
どの並べ替えが優れているというのではなく、どの方法もケースバイケースで利用できたほうが様々な開発シーンで役立つと思います。
ぜひ、参考にしてください。
|
