Office TANAKA - Excel VBA Tips[独自の基準で並べ替える]

先日セミナーで並べ替えのポイントを解説したとき、受講された方から「独自の基準で並べ替えるにはどうしたらいいの？」と質問されました。ああ、そういえば、そのやり方って、ここには書いていなかったなぁ～と。なので書きます。なお、独自の基準による並べ替えは、手動操作でも可能です。手動でやる方法は「Excel Tips[独自の基準で並べ替える]」をご覧ください。

Excel 2007以降の簡単な方法

ここでは、次のような表をA列で並べ替えます。B列とC列は関係ありません。表が1列だけだと寂しいのでオマケです。

まずは、普通に50音順の"昇順"と"降順"で並べ替えたときの結果をご覧ください。なお、Excel 2007以降の並べ替えに関しては「Excel 2007のSortオブジェクト」をご覧ください。

Sub Macro1()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1"), Order:=xlAscending    ''昇順
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .Apply
    End With
End Sub

Sub Macro2()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1"), Order:=xlDescending    ''降順
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .Apply
    End With
End Sub

これを、"北から南"の順番で並べ替えてみましょう。つまり「札幌→東京→大阪→広島→鹿児島」みたいに。実は、この「独自の基準による並べ替え」は、Excel 2007以降のVBAで簡単になった機能のひとつです。独自の基準で並べ替えるときは、Add2メソッドの引数CustomOrderに、並べ替えたい基準をカンマで区切って指定します。

Sub Macro3()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1"), CustomOrder:="札幌,東京,大阪,広島,鹿児島"
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .Apply
    End With
End Sub

これだけです。拍子抜けするほど簡単ですね。では、これを"逆順"で並べ替えるにはどうしたらいいでしょう。もちろん、"鹿児島,広島,大阪,東京,札幌"という逆順の文字列を指定してもいいのですけど、もっと簡単なやり方があります。普通に昇順や降順で並べ替えるときには、引数Orderに、定数xlAscendingか定数xlDescendingを指定します。上記のコードは、その引数Orderの代わりに引数CustomOrderを指定しました。でも実は、両者は共存できるんです。上記コードでは、引数Orderを省略しました。だから「定数xlAscending(昇順)が指定されたもの」として並べ替えが実行されました。省略したから"昇順"になったんです。だから省略しないで、次のように指定すれば逆順で並べ変わります。

Sub Macro4()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range("A1"), Order:=xlDescending, CustomOrder:="札幌,東京,大阪,広島,鹿児島"
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .Apply
    End With
End Sub

Excel 2003方式だとすっげぇ面倒くさい

ただ普通に数値や文字列を並べ替えるだけでしたら、新しく(そして難しく)なったExcel 2007方式(Sortオブジェクト)ではなく、Excel 2003までのSortメソッドを使うのが簡単です。ちなみに、A列を普通の昇順で並べ替えるのでしたら、次の1行で済みます。

Sub Macro5()
    Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub

ですが、今回のように「独自の基準で並べ替え」は、Excel 2003までは面倒くさいやり方が必須でした。独自の並べ替え基準をカンマで区切って指定できるようになったのは、Excel 2007からです。それまでは「Excel Tips[独自の基準で並べ替える]」で書いたように、独自の並べ替え基準を、一度ユーザー設定リストに登録しておかなければなりませんでした。現在のExcelでも、手動でやるのならユーザー設定リストを使うしかありませんが、それがVBAだと簡単になったわけです。なので、一応書きます。Excel 2003方式の面倒くさいやり方を。もちろん、ユーザー設定リストを事前に登録しておくなどという無粋なことはしません。そこもマクロでやります。

Excel 2003方式でやるのなら、次の手順が必要です。

ユーザー設定リストに新しいリストを登録する
登録したリストの番号を取得する
Sortメソッドの引数に取得した番号を指定する
登録したユーザー設定リストを削除する

まずは、ユーザー設定リストの登録です。新しくユーザー設定リストを登録するには、ApplicationオブジェクトのAddCustomListメソッドを実行します。引数には、登録したいリストの項目を配列形式で指定します。

Sub Macro6()
    Application.AddCustomList Array("札幌", "東京", "大阪", "広島", "鹿児島")
End Sub

次に、今登録したリストが何番目かの数値を取得します。新しいユーザー設定リストを登録すると、必ず末尾に登録されますので、現在登録されているユーザー設定リストの個数が、今登録したリストです。ユーザー設定リストの個数を取得するには、ApplicationオブジェクトのCustomListCountプロパティを調べます。

Sub Macro7()
    Dim N As Long
    Application.AddCustomList Array("札幌", "東京", "大阪", "広島", "鹿児島")
    N = Application.CustomListCount
    MsgBox N
End Sub

この数値を、Sortメソッドの引数OrderCustomに指定します。ちなみに、カンマで区切って指定できるようになったExcel 2007以降のSortオブジェクトではCustomOrderです。昔、Excel 2007が出てすぐのとき、私はこれにハマりました。"オーダーカスタム"と"カスタムオーダー"って…なんで変えるかなぁ。

Sub Macro8()
    Dim N As Long
    Application.AddCustomList Array("札幌", "東京", "大阪", "広島", "鹿児島")
    N = Application.CustomListCount
    Range("A1").Sort Key1:=Range("A1"), OrderCustom:=N, Header:=xlYes
End Sub

はい、完成…とはいきません。やってみれば分かりますが、これだと並べ変わりません。エラーにもなりません。考え方は合っているはずですが、エラーにもならず実行もされないと。たぶんこれ、Excel 2003時代からマクロ書いていた人じゃないと気づかないかもしれません。実はさっきの、CustomListCountの数値をMsgBoxで出してみて、ほんの少し違和感がありました。「あれ？ここって確か 13 じゃなかったかな？昔よく 13 って決め打ちしたような記憶が、あるような～ないような～」そうなんです。実は、次のような落とし穴があります。

なので、次のようにすると、正しく並べ変わります。

Sub Macro9()
    Dim N As Long
    Application.AddCustomList Array("札幌", "東京", "大阪", "広島", "鹿児島")
    N = Application.CustomListCount
    Range("A1").Sort Key1:=Range("A1"), OrderCustom:=N + 1, Header:=xlYes
End Sub

さて、並べ替えが終わったら、このために登録したユーザー設定リストを削除します。削除するには、ApplicationオブジェクトのDeleteCustomListメソッドを実行します。DeleteCustomListメソッドの引数には「何番目のリストを削除するか」という、削除したいリストの番号を指定します。このときの番号は「新しいリスト」を含めない上からの番号です。つまり今回でしたら、12です。

Sub Macro10()
    Dim N As Long
    Application.AddCustomList Array("札幌", "東京", "大阪", "広島", "鹿児島")
    N = Application.CustomListCount
    Range("A1").Sort Key1:=Range("A1"), OrderCustom:=N + 1, Header:=xlYes
    Application.DeleteCustomList N
End Sub

面倒くさいでしょ？なので、もし「独自の基準で並べ替え」をやりたいのでしたら、Excel 2007以降のSortオブジェクトを使う方が簡単だし、おそらく汎用的で応用も利くのではないかと。