Excel VBAでデータをソートする

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

masahiro yoshida

Last updated on 5月 17, 2022

In this Article

エクセルVBAによるデータのソート

エクセルVBAによるデータのソート

Excelには、Excelフロントエンドのソート機能はとても優秀です。そして、VBAコードの中でこの機能を使いたいと思うこともあるでしょう。幸いなことに、これを利用するのはとても簡単です。

フロントエンドのダイアログボックスは、Excelリボンの「データ」タブの「並べ替えとフィルター」グループにある「並べ替え」アイコンをクリックすることで表示されます。その際にはまず、表形式のデータの範囲を選択する必要があります。

なお、Alt-A-S-Sでカスタムソートのダイアログボックスを表示させることもできます。

Excelの最近のバージョンでは、ソートの方法が大幅に改善されました。以前はソートが3階層に制限されていましたが、必要な数だけ階層を入力できるようになり、これはVBA内でも適用されます。

PIC 01 並び替えソート

Excelの並べ替えダイアログで提供されているすべての並べ替え機能をVBAのコードに組み込むことができます。Excelのソート機能は高速で、VBAで自分で書くよりも速いので、その機能を活用してください。

VBAでソートを行う場合、ソートの条件はフロントエンドのソートダイアログボックスで同じままであることに注意してください。また、ワークブックが保存される際にも、条件は保存されます。

ユーザーが同じ範囲の表形式データを選択し、並べ替えアイコンをクリックすると、VBAコードによって設定された条件が表示されます。もし、ユーザーが自分でソートの条件を設定したい場合、最初にすべてのソートレベルを削除しなければならず、非常に面倒なことになります。

また、コード内のパラメータを変更せず、デフォルト値に依存している場合、ユーザーが変更した内容がVBAのソートに反映され、予期しない結果をもたらす可能性があり、デバッグが非常に困難になることがあります。

幸いなことに、VBAのClearメソッドを使えば、すべてのソートパラメータを再設定して、きれいなソートダイアログボックスを表示させることができます。

Worksheets("Sheet1").Sort.SortFields.Clear

ソートが完了する前と後に、VBAでソート条件をクリアするのを習慣にすると良いでしょう。

VBAでのSortメソッドの実践的な使い方

表形式のデータをExcelに取り込むと、非常にランダムな順序になっていることがよくあります。CSVファイルからインポートされたり、データベースやウェブページへのリンクからインポートされたりすることもあります。インポートするたびに、決まった順番になることはありません。

このデータをワークシートでユーザーに見せる場合、ユーザーは、順序がバラバラな膨大な量のデータを見て理解するのは難しいと感じるかもしれません。ユーザーは、データをグループ化したり、データの特定の部分をカットして別のアプリケーションに貼り付けたりしたいと思うかもしれません。

また、例えば、最も給料の高い従業員や、最も勤続年数の長い従業員を見たいと思うかもしれません。

VBAのSortメソッドを使用すれば、ユーザーが簡単にソートできるようなオプションを提供することができます。

VBAでExcelのソートを実演するためのサンプルデータ

VBAで使用できるすべての機能をコードで示すために、まずワークシートに入力されるサンプルデータが必要です。

このデータを、図のようにワークシート（’Sheet1’）にコピーしてください。

セルの背景色とフォントの色は、並べ替えのパラメータとして使用することができるので、異なる色を使用していることに注意してください。セルの色とフォントの色を使った並べ替えは、この記事の後半で説明します。また、セルE3では、部署名がすべて小文字になっていることに注意してください。

セルとフォントの色によるソートを行わない場合は、セルとフォントの色は必要ありません。

VBAで並べ替えを行うマクロを記録する

ソートのVBAコードは非常に複雑になる可能性があり、Excelのフロントエンドでソートを行い、コードがどのように動作するかを示すためにマクロを記録することが良いアイデアである場合があります。

しかし残念ながら、マクロの記録機能は膨大な量のコードを生成することがあります。

それでも、VBAのソートコードを書く上で何が必要かを知ることができますし、記録されたコードは常にあなたのために動作するという利点があります。あなた自身のコードが正しく動作するためには、テストやデバッグが必要かもしれません。

VBAで行った操作には取り消し機能がないので、ソートコードを書き始める前に、表形式のデータを別のワークシートにコピーしておくとよいでしょう。

例として、上記のサンプルデータに対して、Employee でソートする単純なソートを行った場合、記録は以下のようなコードを生成することになります。

Sub Macro1()

Range("A1:E6").Select

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

これは非常に大きなコードの塊で、デフォルトのパラメータが使用されているため、その多くは不要です。しかし、プロジェクトを完了させなければならないというプレッシャーがあり、すぐに動作するコードが必要な場合、これを自分のVBAコードに簡単に貼り付けることができます。

ただし、自分のコードを理解しやすく、よりエレガントなものにしたいのであれば、他の選択肢もあります。

VBAで1階層のソートを行うコード

マクロを記録する際に、先の例のように Employee のみに基づいてサンプルコードをソートしたい場合、コードは非常に簡単です。

Sub SingleLevelSort()

Worksheets("Sheet1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("A1"), Header:=xlYes

End Sub

これは、昇順でソートするなどのデフォルトを受け入れるので、パラメータをデフォルト値に設定する必要がなく、記録したコードよりもはるかに理解しやすいものです。ただしこれは、あらかじめClearステートメントを使用していることが前提です。

Clearメソッドは、最初にそのワークシートのすべてのソートパラメータをデフォルト値に戻します。ユーザーが以前にパラメータを別の値に設定した場合や、VBAでの以前のソートでパラメータが変更された場合があるため、ソートを行う際には、デフォルトの位置から始めることが重要です。そうでなければ、間違った結果になってしまう可能性があります。

ClearメソッドはHeaderパラメータをリセットしないので、コードにこれを含めることをお勧めします。このコードをサンプルデータに対して実行すると、ワークシートは次のように表示されます。

VBAで多階層のソートを行うコード

このコードでは、必要な数だけソートの階層を追加することができます。例えば、最初に部署で、次に開始日でソートしたい場合、部署は昇順、開始日は降順でソートします。

Sub MultiLevelSort()

Worksheets("Sheet1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

End Sub

ソート文のキーが2つ（Key1とKey2）になっていることに注意してください。Key1 (E列:Department) がまず最初にソートされ、次に Key2 (C列:Start Date) が最初のソート結果を基にしてソートされます。

また、順序パラメータは2つあります。Order1 は Key1 (Department) に関連付けられ、Order2 は Key2 (Start Date) に関連付けられます。キーと順序が互いに歩調を合わせるようにすることが重要です。このコードをサンプルデータに対して実行すると、結果は次のようになります。

Department列 (E列) は昇順で、Start Date列 (C列) は降順です。

このソートの効果は、Jane Halfacre (3行目) とJohn Sutherland (4行目) を見たときにその差が現れます。二人ともファイナンス部門に所属していますが、Jane Halfacreの方がJohn Sutherlandより先に開始しており、日付は降順で表示されています。

表形式のデータの範囲が任意の長さになる可能性がある場合、UsedRange オブジェクトを使用してソート範囲を定義することができます。これは、ワークシート上に表形式のデータしかない場合にのみ機能します。データの外側に値があると、行と列の数に対して不正確な結果が得られるからです。

Sub MultiLevelSort()

Worksheets("Sheet1").Sort.SortFields.Clear
Worksheets("Sheet1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending
End Sub

しかしこの方法なら、ソート範囲を定義するために End(xlDown) メソッドを使った場合に発生する問題を防ぐことができます。End(xlDown)を使うと、データの途中に空白セルがある場合は空白セル以降のものは含まれませんが、UsedRangeならば、ワークシートの最後のアクティブなセルまでカバーできるためです。

セルの背景色で並べ替える

Excel 2007以降では、セルの背景色によるソートが可能になったため、VBAでソートコードを設計する際に非常に柔軟性があります。

Sub SingleLevelSortByCellColor()

Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:E6")
    .Apply
End With

End Sub

このコードは、セルの背景色に基づいてサンプルデータの範囲A2:A6をソートします。「SortOn」という追加のパラメータがあり、その値が「xlSortOnCellColor」であることに注目してください。

SortOnパラメータは、Worksheetsオブジェクトでのみ使用可能で、Rangeオブジェクトでは使用できません。

そのため、セルの値を使ったソートに比べて、コードが複雑になります。このコードでは、データの全範囲をカバーするソートのキー値を使用していますが、個々の列を背景色のソートのキーとして指定し、先に示したように複数の階層を使用することができます。

このコードを実行すると、ワークシートは次のようになります。

フォントの色で並べ替える

Excel VBAのソート機能では、フォントの色でソートすることもできるため、さらに柔軟な対応が可能です。

Sub SingleLevelSortByFontColor()

Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("A2:A6"), _
    xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub

フォントの色でソートするコードは、セルの背景色でソートするよりもはるかに複雑です。SortOnパラメータに「xlSortOnFontColor」という値が設定されています。

方向は「xlTopToBottom」として指定し、ソートする色を指定する必要があることに注意してください。これはRGB関数を用いて、赤、緑、青をそれぞれ0から255の値で指定します。このコードをサンプルデータに対して実行すると、ワークシートは次のようになります。

VBAで色を使ったソートは、複数レベルのソートよりもはるかに複雑ですが、ソートコードが動作しない場合（パラメータが不足していたり、コードが正しく入力されていない場合など）には、マクロを記録して、記録したコードをVBAに統合することでいつでも最初からやり直すことができます。

VBAのソートで他のパラメータを使用する

VBAのコードには、ソートをカスタマイズするために使用できるオプションのパラメータがいくつか用意されています。

SortOn

SortOnは、ソートがセルの値、セルの背景色、またはセルのフォント色のいずれを使用するかを選択します。デフォルトはCell Valuesです。

SortOn = xlSortOnValues

Order

Order は、昇順と降順のどちらで並べ替えるかを選択します。デフォルトはAscending（昇順）です。

Order = xlAscending

DataOption

DataOptionは、テキストと数値のソート方法を選択します。xlSortNormalパラメータは、数値とテキストを別々にソートします。xlSortTextAsNumbersパラメータは、テキストを数値データとしてソートします。デフォルトはxlSortNormalです。

DataOption = xlSortNormal

Header

Headerは、表形式データ範囲にヘッダー行があるかどうかを選択します。ヘッダー行がある場合、これをソートに含めないようにします。パラメータ値は、xlYes、xlNo、およびxlYesNoGuessです。xlYesNoGuessは、ヘッダー行があるかどうかをExcelに任せるため、結果に矛盾が生じやすくなります。そのため、この値を使用するのは推奨しません。デフォルト値はxlNo (データ内にヘッダー行がない)です。インポートデータでは、通常ヘッダー行があるため、このパラメータをxlYesに設定してください。

Header = xlYes

MatchCase

このパラメータは、大文字と小文字を区別してソートするかどうかを決定します。オプション値はTrueまたはFalseです。値がFalseの場合、小文字の値は大文字の値と同じとみなされます。値がTrueの場合、ソートは大文字と小文字の値の差をソート内で表示します。デフォルトはFalseです。

MatchCase = False

Orientation

このパラメータは、ソートが行の垂直方向に行われるか、列を横断して水平方向に行われるかを決定します。デフォルト値は、xlTopToBottom（縦方向のソート）です。水平方向にソートしたい場合は、xlLeftToRightを使用することができます。xlRowsやxlColumnsのような値は、このパラメータでは機能しません。

Orientation = xlTopToBottom

SortMethod

このパラメータは、中国語のソートにのみ使用されます。xlPinYinとxlStrokeの2つの値があり、デフォルト値はxlPinYinです。

xlPinYinは中国語の文字の発音順でソートします。xlStrokeは各文字の画数でソートします。

ソートをマクロ記録すると、必ずこのパラメータがコードに含まれており、その意味を不思議に思ったことがあるかもしれませんが、中国語のデータを扱わないならば、ほとんど意味はありません。

SortMethod = xlPinYin

ダブルクリックイベントを使った表形式データの並べ替え

Microsoftは、VBAのSortメソッドに含まれるすべての機能において、列のヘッダーをダブルクリックして、その特定の列に基づいて表形式データ全体をソートする簡単な手段を含んでいませんでした。これは本当に便利な機能で、そのためのコードも簡単に書くことができます。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'ヘッダ行を含むデータがセルA1からあることが前提

'選択されたターゲット列と最大列、最大行を捕捉するために3つの変数を作成する
'表形式データ
Dim Col As Integer, RCol As Long, RRow As Long

'ユーザがダブルクリックした行を確認し、ヘッダ行でなければ終了する
If Target.Row <> 1 Then Exit Sub

'UsedRangeオブジェクトでデータ範囲の最大行を取得する
RCol = ActiveSheet.UsedRange.Columns.Count

'UsedRangeオブジェクトでデータ範囲の最大列を取得する
RRow = ActiveSheet.UsedRange.Rows.Count

'ユーザが表形式データ範囲外の列をダブルクリックしていないことを確認する
If Target.Column > RCol Then Exit Sub

'ユーザがダブルクリックした列番号を取得する
Col = Target.Column

'以前のソートパラメータをクリアする
ActiveSheet.Sort.SortFields.Clear

'UsedRangeオブジェクトの範囲をソートする
'ユーザがダブルクリックした列をソートキーとして表形式データをソートする
ActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Sort Key1:=Cells(1, Col), Header:=xlYes

'セルA1を選択する - ソート完了後、ユーザーが編集モードで残っていないことを確認する
ActiveSheet.Range("A1").Select

End Sub

このコードは、ソートする表形式データを含むシートのダブルクリックイベントに配置する必要があります。これは、プロジェクトエクスプローラーウィンドウ（VBE画面の左上隅）でワークシート名をクリックし、コードウィンドウの最初のドロップダウンで「ワークシート」を選択することで行います。2番目のドロップダウンで「BeforeDoubleClick」を選択すると、コードを入力することができます。

このコードには、コードの最後にセルA1にカーソルを移動する以外、名前、範囲、セル参照はハードコードされていないことに注意してください。このコードは、ユーザーがダブルクリックしたセルの座標と表形式データ範囲のサイズから、必要な情報をすべて取得するようになっています。

表形式のデータ範囲がどれだけ大きいかは問題ではありません。このコードは必要な情報をすべて取得し、値をハードコードすることなく、ワークブック内の任意の場所にあるデータに使用することができます。唯一の前提は、表形式データにヘッダー行があり、データ範囲がセルA1から始まることですが、データ範囲の開始位置はコード内で簡単に変更することができます。この新しいソート機能には、どんなユーザーもそれなりに感心するはずです。

VBAでソート機能を拡張する

Microsoftは、さまざまなパラメータを使用して、非常に柔軟なソート機能を提供しています。しかし、VBAの中では、これをさらに推し進めることができます。

例えば、太字のフォントを持つ値をデータの先頭に並べたいとします。Excelでこれを行う方法はありませんが、VBAを使ってこれを行うコードを記述することができます。

Sub SortByBold()

'表形式データの行数、列数を保持する変数を作成する
Dim RRow As Long, RCol As Long, N As Long

'画面更新をオフにする
Application.ScreenUpdating = False

'データ範囲の列数を取得する
RCol = ActiveSheet.UsedRange.Columns.Count

'データ範囲の行数を取得する
RRow = ActiveSheet.UsedRange.Rows.Count

'データ範囲内のヘッダー行以外の行を繰り返し処理する
For N = 2 To RRow
    'セルが太字の場合、セルの値に対して先頭に0を配置する
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = "0" & ActiveSheet.Cells(N, 1).Value
    End If
Next N

'以前のソートパラメータをクリアする
ActiveSheet.Sort.SortFields.Clear

'データ範囲をソートする
'先頭が0の値はすべて上に移動する
ActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Sort Key1:=Cells(1, 1), Header:=xlYes

'ヘッダー行を無視して表形式データ範囲のすべての行を繰り返し処理する
For N = 2 To RRow
    'セルが太字の場合、セルの値から先頭の0を削除して元の値に戻す
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = Mid(ActiveSheet.Cells(N, 1).Value, 2)
    End If
Next N

'画面の更新をオンに戻す
Application.ScreenUpdating = True

End Sub

このコードでは、UsedRangeオブジェクトを使って表形式データ範囲のサイズを計算し、その範囲内のすべての行を繰り返し処理します。太字のフォントが見つかると、セルの値の前に、一時的に0を置きます。

その後、ソートが行われます。ソートは昇順で行われるので、先頭に0があるものはリストの先頭になります。

その後、このコードはすべての行を繰り返し、先頭の0を削除し、データを元の値に戻します。このコードでは、太字のフォントを基準にしてソートしていますが、イタリック体、テキストのポイントサイズ、アンダースコアのフォント、フォント名など、他のセルの特性も同じように簡単に使用することができます。