データの並び替え VBA新旧のSortを実データで実証

こんにちは　じゅんぱ店長　(＠junpa33)　です。

エクセルでのセルデータを操作する一つとして「ソート（並び替え）」があります。

セルのデータ並び替えを行う方法としては、リボンの項目の「並び替え」をクリックすることで行うことが出来ます。これはエクセルに設置された標準機能です。

ではエクセルVBAを使って、セルに入力されたデータを、ソート（並び替え）を行うにはどうしたらよいか、が今回のテーマです。

実例を使ったデータの並び替え 新旧Sortを実証

中々、架空例ではイメージが湧きにくいので、今回は実例を使って新旧の並び替え（Sort）を説明していきます。

よくご存じのテレビ番組雑誌。実は全国各地域版で発売されているので、メチャクチャ種類があります。（内容はそんなに変わらないのですが・・・）

これは某日のTV誌の発売リストです。

まずは、このTV誌発売リストを出版社別に並び替えた上で、雑誌コード順に並べます。並び替えが分かりやすいようにセルを黄色にしています。

Sortメソッドで並び替える

旧来のSortメソッドで並び替えた場合です。記述コードは以下の通りです。

並び替え内容もそれほど複雑ではないので、簡単なコード編成になりました。

全4行のコードに見えますが、全てひとつながりの全1文の長いコードになっています。

Sub Sortメソッド()
    Range("A1").Sort _
        Key1:=Range("H1"), Order1:=xlAscending, _
        Key2:=Range("D1"), Order2:=xlAscending, _
        Header:=xlYes
End Sub

このSortメソッドは従来（Excel2003）までの並び替え方法になっています。（今のバージョンのExcelでも使えます。）

書き出しのRange(“A1”)は「検索範囲」に当たる部分です。

何も「A1セルだけを検索せよ」というのではなくExcelの「対象範囲の自動拡張機能」が働くため、

代表的なセルとしてA1を指定すればよいということになります。

並び替えの対象キーとしては、Key1のH1セル項目が最優先で、その次にKey2のD1セルを指定したということです。

Header:=xlYesは、先頭行はヘッダー（項目行）なので並び替え対象から外すという意味になります。

xlAscendingは、並び順が「昇順に」という意味です。「降順」の場合はxlDescendingという指定になります。

Sortオブジェクトで並び替える

Excel2007から利用できるSortオブジェクトを使った並び替えのコードです。

基本、Withステートメントを使った記述方法になります。

Sub Sortオブジェクト()
    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("H1"), Order:=xlAscending
        .Sort.SortFields.Add Key:=.Range("D1"), Order:=xlAscending
        .Sort.SetRange .Range("A1:J35")
        .Sort.Header = xlYes
        .Sort.Apply
    End With
End Sub

まず最初にSortFieldsをクリア（設定なし状態にリセット）しておきます。

SortFieldsでの並び替えのKey項目をAddで設定します。Sortオブジェクトでは、Keyの個数は限定されていませんので、単に「Key」として指定します。

SetRangeで並び替えの範囲を指定します。Sortメソッドの時の様にExcelの「対象範囲の自動拡張機能」は働きませんので、しっかり範囲指定を行います。

Headerは先頭行を項目行に指定します。

Applyは「並び替えを実行」という意味です。

Sortメソッドとオブジェクトの明らかな違い

Sortメソッドはコードの記述として1行文になります。今回の例示のような場合だとそれほど長文になっていませんが、1行文の長文だと、コードメンテナンスの場合には非常に作業がしづらいくなります。

Sortオブジェクトでは、Withステートメントを使っているので、指定項目の一部修正も非常にわかりやすくなりました。

Sortメソッドでは、並び替えを優先する列が第3位までの指定になっていましたが、Sortオブジェクトでは、特に制限がなくなりました。

Sortメソッドでは、Excelの仕様の「並び替えのルールの記憶」を変更や解除するために、コード上でも（念のため）そのオプション指定を記述しなければいけませんでした。（記述しない場合は、期待する並び替えが出来ない場合も）

Sortオブジェクトになって、「Sort.SortFields.Clear」と最初に記述しておくことで、その余分なコード記述を行わなくてよくなりました。

Excel2007のSortオブジェクトから、データ値以外にセルの色、フォントの色やセルのアイコンを基準にして並び替えが出来るようになりました。

Sortメソッドの場合では、基本1文の長文になるので、変数の可読性が悪く「入力ミス、間違い探し」がたいへんです。

一方、Sortオブジェクトの場合はWithステートメントでプロパティごとに括ることが出来るようになり、仮にコード入力ミスがあってもエラーの対処がよりしやすくなっています。

SortメソッドとSortオブジェクトの文法

SortメソッドとSortオブジェクトでは大幅に文法が異なります。それぞれに見ていきます。

Sortメソッドの文法

ザックリ言って、

並び替え対象のセル範囲に対して、並び替えのキー項目と並び順、先頭行は見出し行かどうかを記述します。

Sortオブジェクトの文法

Sortオブジェクトは、WorksheetオブジェクトのSortプロパティで取得することが出来ます。

'Sortオブジェクトのベーシックな記述モデル

Sub Sortオブジェクト記述モデル()
　　With Worksheetオブジェクト
　　　　.Sort.SortFields.Clear
　　　　.Sort.SortFields.Add Key:=.Rangeオブジェクト, Order:=並び順
　　　　.Sort.SetRange .Rangeオブジェクト(並び替え範囲)
　　　　.Sort.Header = 先頭行の指定
　　　　.Sort.Apply
　　End With
End Sub

Withステートメントをネストで使うと同じ記述コードが以下の様になります。自分が使い易い方でどうぞ

'SortオブジェクトでWithをネストする記述


Sub Sortオブジェクト記述モデルWithをネスト()
　　Dim ws As Worksheet
　　Set ws = Worksheetオブジェクト
　　　　With ws.Sort
　　　　　　With .SortFields
　　　　　　　　.Clear
　　　　　　　　.Add Key:=ws.Rangeオブジェクト, Order:=並び順
　　　　　　End With
　　　　　　.SetRange ws.Rangeオブジェクト(並び替え範囲)
　　　　　　.Header = 先頭行の指定
　　　　　　.Apply
　　　　End With
End Sub

主なSortオブジェクトメンバー

SortFieldsオブジェクトのメソッド

Addの引数

SortOnの定数

Sortオブジェクトの新機能「背景色」をKeyに並び替え

Sortオブジェクトの新機能で、一番よく使う（だろう）セルの背景色によるマーキングを並び替えします。

先ほどの実例を使って行います。

Sortオブジェクトによる並び替え

並び替えの順位としては、I列でセル背景色　黄色→緑色、H列で出版社名、D列で雑誌コードの順位になります。いずれも昇順で並び替えを行います。

Sub sortオブジェクトセル背景色()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ActiveSheet.Sort
        With .SortFields
            .Clear
            With .Add(Key:=Range("I1"), _
                    SortOn:=xlSortOnCellColor, Order:=xlAscending)
                .SortOnValue.Color = RGB(255, 255, 0)
            End With
            With .Add(Key:=Range("I1"), _
                    SortOn:=xlSortOnCellColor, Order:=xlAscending)
                .SortOnValue.Color = RGB(0, 255, 0)
            End With
            .Add Key:=ws.Range("H1"), Order:=xlAscending
            .Add Key:=ws.Range("D1"), Order:=xlAscending
        End With
        .SetRange ws.Range("A1:J35")
        .Header = xlYes
        .Apply
    End With
End Sub

背景色の並び替えは、SortOnプロパティで引数を「xlSortOnCellColor」で指定します。

今回は背景色が2色でしたので、2色それぞれ黄色と緑色の並び替えのキー指定をしています。3色なら3色それぞれにKey指定することになります。

Sort（並び替え）のまとめ

Sort（並び替え）は、現在SortメソッドかSortオブジェクトの2つの方法を選んで行うことが出来ます。

それぞれに特徴がありますが、新しい手法のSortオブジェクトの方が、並び替えについての選択度合いが増えている分、自由度がより高くなっています。

ただその分、並び替えの選択肢の指定数が多くもなり、そういった使い易さという意味では

ただ単に、並び替えのバリエーションの多さ＝ベストチョイス　ということでもないかもしれません。

まず「自分の普段使いを考えた時の選択は、また別なのかも」の判断をしておいてください。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

2022年1月9日 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

Dstyle web