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

vbasorteyecatch

Excelのセルのデータを並び替えるには、VBAではSortメソッドかSortオブジェクトを使用します。

範囲、優先Key、昇順・降順を指定すれば自動で並び替えを行います。

さらに、Sortオブジェクトではセル背景色でも並び替えが可能になりました。

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

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

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

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

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

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

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

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

某日のTV誌発売リスト

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

並び替え後のTV誌発売リスト

Sortメソッドで並び替える

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

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

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

Sortメソッド
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ステートメントを使った記述方法になります。

HTML
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ステートメントを使っているので、指定項目の一部修正も非常にわかりやすくなりました。

  • 　並び替えの「Keyが3つまで」から「特に制限なし」に変わった。

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

  • 　前回の並び替えの設定を引き継ぐ「記憶」の削除処理方法が違う。

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

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

  • 　並び替え対象が「背景色」「文字色」「アイコン」でも可能になった。

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

  • 　並び替えコードの記述面で、範囲指定やKey指定を変数化させる記述が分かりやすい。

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

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

目次へ戻る

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

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

Sortメソッドの文法

ザックリ言って、

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

Sortメソッドの構文

オブジェクト.Sort( [Key1, Order1, Key2, Type, Order2, Key3, Order3,Header, OrderCustom, MatchCase, Orientation, SortMethod,DataOption1, DateOption2, DateOption3] )

引数 定数 説明
Key　1～3   セル範囲の指定
Order　1～3 xlAscending 昇順の並び替え
xlDescending 降順の並び替え
Header xlGuess 自動判定
xiNo 先頭行はタイトル行ではない
xlYes 先頭行はタイトル行
MatchCase True 大文字小文字を区分
False 大文字小文字を区分しない
Orientation xlSortRows 行方向の並び替え
xlSortColumn 列方向の並び替え
OrderCustom   ユーザー設定の並び替え順
SortMethod xlPinYin 日本語をフリガナで並べ替え
xlStroke 日本語を文字コードで並べ替え

Sortオブジェクトの文法

Sortオブジェクトは、Worksheetオブジェクトの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オブジェクトメンバー

メソッド 説明
SetRange （Rngプロパティで）並び替えの範囲を指定します。
Apply 並び替えの実行
プロパティ 説明
SortFields SortFieldsオブジェクトを取得します。並び替えのフィールドを指定します。
Header 先頭行の扱いを指定します。（Sortメソッド参照）
MatchCase 大文字込み時の扱いを指定します。（Sortメソッド参照）
Orientation 並び替え方向を行方向か列方向かを指定します。（Sortメソッド参照）
SortMethod 日本語の並び替え方法を指定します。（Sortメソッド参照）
Rng 並べ替えが行われる値の範囲を指定します。

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

SortFieldsの構文

Worksheetオブジェクト.Sort.SortFields

メソッド 説明
Add 並び替えフィールドを追加します。
Clear SortFieldsの設定された状態を全てクリアします。

Addの引数

Addの記述

Worksheetオブジェクト.Sort.SortFields.Add(引数)

引数 説明
Key 並べ替えのキー値をセル範囲で指定します。
SortOn 並べ替えの定数を指定します。
Order 昇順、降順を指定します。
DataOption テキストを数値データで並び替えるかを指定します。

SortOnの定数

SortOnの指定する定数 説明
xlSortOnValues 値
xlSortOnCellColor セルの色
xlSortOnFontColor フォントの色
xlSortOnIcon セルのアイコン
目次へ戻る

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

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

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

関東圏首都圏、関西圏のセル背景色を着色
セルの背景色による並べ替え後

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

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

<strong>sortオブジェクトセル背景色</strong>
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を独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

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

vbabgcoloreyecatchセルの背景色を2クリックで他範囲にも展開できるマクロvbasorteyecatchデータの並び替え VBA新旧のSortを実データで実証vbacellsprotecteyecatch「セルの保護」の設定VBAを最速理解vbacellspaint_gradteyecatch「グラデーションの塗りつぶし」のセルを設定するcellspaintpatterneyecatch「塗りつぶしの網掛け」VBAを最速理解vbacellspaintbackeyecatch「塗りつぶし」背景色をVBAで記述するvbacellsbordereyecatch「罫線」のVBAを最速理解vbacellsfonteyecatch「フォント」の操作を最速理解する エクセルVBAvbacellsplacementeyecatch「配置」を最速理解する エクセルVBAvbacellssyoshikieyecatch「表示形式」をVBAコード的に最速理解するVBAcellareaeyecatchセルの範囲選択と設定は14のツールで対応するvbaendpropertyeyecatchEndプロパティで上下左右の最終セルを取得vbaentirecolumneyecatchEntireColumnとColumnの使い方vbaentireroweyecatchEntireRowとRowプロパティの違いvbacolumnseyecatchColumnsプロパティの列指定で参照するvbaunioneyecatchUnionとRangeの一括選択の働きを比較vbaresizeeyecatchResizeプロパティでセル範囲をサイズ変更vbarangevscellseyecatchRange VS Cells ！VBAで使えるのはどっちvbausedrangeeyecatchUsedRangeプロパティの使い方のコツvbaborder1eyecatchBordersの/位置/線種/太さ/色/と<外枠だけの罫線>の設置vbarowseyecatchRowsプロパティの働きと関連のメソッドvbadeleteeyecatchセルの編集 DeleteとClearの使い分け 削除とクリアの違いvbacurrentregioneyecatchCurrentRegionは連続データをまとめて掴むSpecialCellseyecatchSpecialCellsメソッドはセル検索し選択するvbacharactereyecatchaCharactersで文字列中の一部の文字を加工するvbafontsyseyecatchaFontプロパティで文字装飾操作をするvbajyufukueyecatchエクセルVBA 同じ項目をまとめる（重複データの整理）コード作成vbafindeyecatch001Findメソッドでの検索の方法とコード組み立てのコツvbalastcelleyecatchデータ入力済セルの最終行番号を取得するvbaoffseteyecatchOffsetプロパティは指定範囲を移動させる

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

前の記事

vbacellsprotecteyecatch
「セルの保護」の設定VBAを最速理解

次の記事

条件設定ボタンとボックスを配置するユーザーフォームのデザイン
vbanarabikaeeyecatch