Office TANAKA - Excel VBA Tips[SORT関数を使う]

VBAからWorksheetFunctionを使って「SORT関数」を活用するときのポイントを解説します。なお、SORT関数の詳細な仕様や、動的配列数式などに関しては、下記ページをご覧ください。

Excel 2016レビュー - [Excelの使い方が激変する「スピル」]
VBA Tips - [動的配列数式の操作]
この関数はこう使え - [SORT関数]

まずは、簡単なケースからやってみましょう。

Sub Macro1()
    Dim A, i As Long, B As String
    A = WorksheetFunction.Sort(Range("A2:A6"))
    For i = 1 To UBound(A)
        B = B & A(i, 1) & vbCrLf
    Next i
    MsgBox B
End Sub

意外と簡単です。これはコードを読めば分かるでしょう。解説は不要ですね。ただ、注意すべきポイントは、SORT関数に限りませんが、動的配列数式として使用できるワークシート関数は、1次元配列ではなく2次元配列を返します。しかも、セル範囲を表す2次元配列なので、インデックス(添え字)は1から始まります。よく分からなかったら、上記のような場合、1と決め打ちするのではなく、LBound関数を使ってください。

では、元データが日付だったらどうでしょう。まったく同じコードでやってみます。

ありゃりゃ…って感じですね。これ、セル内でSORT関数などを使った場合もそうなんですけど、書式は引き継がれないんです。まぁ、値を返しているのですから当然ですね。こんなときは、Format関数などで整形します。

Sub Macro2()
    Dim A, i As Long, B As String
    A = WorksheetFunction.Sort(Range("A2:A6"))
    For i = 1 To UBound(A)
        B = B & Format(A(i, 1), "yyyy/m/d") & vbCrLf
    Next i
    MsgBox B
End Sub

これを見て「むむむ！これはもしかして」と閃いた方はいませんか？そう、Format関数ではなくMonth関数を使うと、次のような並びを得られます。

Sub Macro3()
    Dim A, i As Long, B As String
    A = WorksheetFunction.Sort(Range("A2:A6"))
    For i = 1 To UBound(A)
        B = B & Month(A(i, 1)) & vbCrLf
    Next i
    MsgBox B
End Sub

このような、12の次で1に戻るような"月の連番"て、作るのが意外と難しいです。SORT関数だと一発ですね。

やろうと思えば、セル内に入力されている値ではなく、任意の配列をSORT関数で並べ替えることもできます。できますけど、けっこう難しいです。SORT関数の引数に指定するのは"セル範囲"です。セル範囲は"2次元配列"です。対して、VBAでよく使われるSplit関数などが返すのは"1次元配列"です。したがって、たとえばSplit関数が返す配列をSORT関数で並べ替えるには、TRANSPORT関数で2次元配列に変換してやります。

Sub Macro4()
    Dim A, i As Long, B As String, C
    C = Split("300,100,500,200,400", ",")
    C = WorksheetFunction.Transpose(C)
    A = WorksheetFunction.Sort(C)
    For i = 1 To UBound(A)
        B = B & A(i, 1) & vbCrLf
    Next i
    MsgBox B
End Sub

あるいは、1次元配列を"横方向に並んだセル範囲"と見立てて、これを"列方向"に並べ替えるという手もあります。たとえば次のような感じです。でも、今度はこのとき、SORT関数が1次元配列を返しますので、取得するとき「A(i, 1)」はエラーになります。ここでは「A(i)」としなければなりません。

Sub Macro5()
    Dim A, i As Long, B As String, C
    C = Split("300,100,500,200,400", ",")
    A = WorksheetFunction.Sort(C, 1, 1, True)
    For i = 1 To UBound(A)
        B = B & A(i) & vbCrLf
    Next i
    MsgBox B
End Sub

まぁ、いずれにしても、もし配列に対して少しでも"苦手意識"を持っているのなら、やらない方がいいです。

結果をセルに代入する

SORT関数で得られた結果をセルに代入してみます。たとえば、次のような結果にしたいです。

まずは、SORT関数の結果を、セルC2に代入してみましょう。

Sub Macro6()
    Dim A
    A = WorksheetFunction.Sort(Range("A2:A6"))
    Range("C2") = A
End Sub

うまくいきません。これ、要するに"Valueの代入"みたいなものです。コピーじゃありませんから、受け取り側も同じ大きさのセル範囲を指定しなければなりません。

Sub Macro7()
    Dim A
    A = WorksheetFunction.Sort(Range("A2:A6"))
    Range("C2:C6") = A
End Sub

ただ、今回は、元データの範囲を決め打ちしたから簡単でした。元データのセル数が分からなかったら、次のようにするしかないですね。

Sub Macro8()
    Dim A
    A = WorksheetFunction.Sort(Range("A2:A6"))
    Range("C2").Resize(UBound(A)) = A
End Sub

今回の受け取り側である「C2:C6」は、"1列5行"のセル範囲です。この"5行"の"5"とは、SORT関数が返す配列の大きさです。ですから、配列の大きさをUBound関数で調べ、その数値でResizeしてやります。幸いなことに、SORT関数が返す配列は1から始まりますので、1を引いたり足したりせずにそのまま使えます。

では、次のような元データだったらどうでしょう。

A列「数値」を昇順に並べ替えます。代入する左上はセルE2です。このときも考え方は同じです。SORT関数は2次元配列を返します。配列の大きさは"5行3列"です。

配列の大きさを調べるUBound関数は、一般的に1次元配列の大きさを調べることが多いので「UBound(A)」のように使いますが、実は大きさを調べる次元を指定することができます。

なので、この2つの数字でResizeしてやります。分かりやすく変数を使うと、次のような感じです。

Sub Macro9()
    Dim A, 行 As Long, 列 As Long
    A = WorksheetFunction.Sort(Range("A2:C6"))
    行 = UBound(A, 1)
    列 = UBound(A, 2)
    Range("E2").Resize(行, 列) = A
End Sub

元データが可変の場合

ここまでは、並べ替える前の元データを「A2:C6」などと決め打ちしてきました。しかし、実務はそんなに簡単じゃありません。元データの大きさが可変(毎回変わるみたいな)のときは、注意が必要です。Excelの「並べ替え機能」には"先頭行をタイトルと見なして移動させない"というオプションがありますが、そんなものSORT関数には存在しません。SORT関数は、指定されたセル範囲の先頭行も並べ替えます。つまり、SORT関数の引数に指定するセル範囲にはタイトル行を含めてはいけないということです。たとえば、上記の「A2:C6」でしたら、次のように考えます。

Sub Macro10()
    Dim A, 行 As Long, 列 As Long
    A = WorksheetFunction.Sort(Range(Range("A2"), Cells(Rows.Count, 3).End(xlUp)))
    行 = UBound(A, 1)
    列 = UBound(A, 2)
    Range("E2").Resize(行, 列) = A
End Sub

めんどくさいですよね。なので、元データは"テーブル"にしておくことを強くお勧めします。元データがテーブルだったら、次のように書けます。

A = WorksheetFunction.Sort(Range("A1").ListObject.DataBodyRange)

あるいは、もしテーブルの名前が"テーブル1"だったら、次の書き方でもいけます。

A = WorksheetFunction.Sort(Range("テーブル1[#Data]"))

このへんに関しては「VBAでテーブルの操作」をご覧ください。