ExcelVBA(マクロ)でデータを昇順・降順に並び変える方法/Sortメソッド・Sortオブジェクトの使い方

IT

Excelのデータをマクロで並び変えるSortメソッドのご紹介です。
ExcelにはVBAで並び変えする方法がいくつかあって、最初に紹介するSortメソッドは~2003までで主流な、古典的なやりかたです(普通に今でも使えます)。Rangeオブジェクトで範囲を指定し、その枠内で並べ替えします。

Advertisements

Sortメソッドの使い方

基本~優先列の指定

表内のデータを並び変える
範囲.Sort(引数)
sort1
こんな感じの表を、とりあえず一回並べ替えてみたいと思います。
sort2
通常操作であれば「並べ替えとフィルタ」になりますが、これをマクロで記録するとかなり長ったらしいものができます。
あとで見てわかりやすいよう、なるべく簡単に書きたいと思います。
sort3
まずは範囲を指定し、これにSortをつけますが、これだけだとエラーになります。
公式ページだと省略可能になってるので、バージョンによってはいけるのかもしれませんが)
sort4
Sortメソッドの使い方は、まずオブジェクトとして並べ替える表全体を指定し……。
sort5
その中でどの列を優先して並べ替えるかを指定します。
sort6
優先列は、Sortの後にただくっつけるだけでOKです。かつ、列の中のどこかひとつのセルを指定すれば、「列」を指定したことになります。この場合Range(“A1:B9”).Sort Range(“B1”)A1~B9の表の中でB列を優先して並べ替えるというような記述になります。B1はB9でもB1:B9でもいいです。
sort7
実行するとちゃんと並べ替えられます。デフォルトで範囲が拡張され、昇順となります。
sort8
優先列をA列にすれば、A列が昇順となります。
sort10
優先列が指定した並べ替えの対象範囲(Rangeオブジェクト)の中に見つからない場合はエラーになります。
sort11
わざわざそんなことする必要もないですが、範囲を優先列にした場合は一番左の列が対象になります。

複数の優先列(Key1~3)

sort12
ひとつ列を増やします。
sort14
これまで「優先列」と言ってきた引数は、フィールド名でいうと「Key1」となります。
他にも「Key2」「Key3」が指定でき、1から3の順に優先して並べ替えが行われます。
フィールド名を指定して書く場合、フィールド名:=値のような書き方をします。
試しにRange(“A1:C9”).Sort key1:=Range(“A1”), key2:=Range(“C1”)を実行してみましょう。
sort15
Key1が最優先なので、まずはA列が並べ替えされます。
sort16
その後で、Key2に指定されたC列がカテゴリ「A」「B」の中で昇順に並べ替えされます。
sort17
Key1と2を逆にしてみると……。
sort18
今度はC列が優先され、その枠内でA列が並び変えをされます。

昇順/降順の指定(Order1~3)

sort19
先述の通りSortメソッドを実行するとデフォルトで昇順になりますが、引数で指定することもできます。
一度フィールド名を消して、優先列の後にxlと打つと、ふたつ候補が表示されました。
sort20
メソッドにはフィールド名を指定して引数を指定する方法と決められた順番に書いてフィールド名を省略する書き方があり、後者の書き方で言うとSort 優先列1,並べ方1 という具合になります。並べ方1をフィールド名で言うとOrder1となります。
値として入力したxlAscending昇順を意味します。
sort21
実行結果。
昇順=あいうえお順、ABC順、小さい数字が上にくる順番に並べ替えします。
混在している場合数字→アルファベット→日本語の順になります(検証)。
sort22
xlDescendingを入力すると、降順で並べ替えが可能です。
sort23
数字がついていることから想像はつくでしょうが、昇順/降順はKey(優先列)ごとに指定可能です。それぞれOrder1、Order2、Order3となり、同じ数字のKeyに対応します。
Range(“A1:C9”).Sort key1:=Range(“A1”), order1:=xlDescending, key2:=Range(“B1”), order2:=xlAscendingとし、A列を降順、B列を昇順で並べ替えてみます。
sort24
実行するとこんな感じになります。

なお、Order(昇順/降順)は一度設定すると、変更しない限り次のSortでも同じ設定で実行されます。
昇順の場合も指定しておくのが無難です。

Advertisements

その他のSortメソッドの引数

とりあえず上までの内容を知ってれば大体対応できると思いますが、Sortメソッドには他にも大量に引数があります。
Key(優先列)およびOrder(昇順or降順)については書いたので、それ以外について順次加筆していきます。以下で紹介している引数は全て省略可能です。

Sortメソッドの引数一覧
範囲.Sort(Key1、 Order1、 Key2、 Type、 Order2、 Key3、 Order3、 Header、 OrderCustom、 MatchCase、 Orientation、 SortMethod、 DataOption1、 DataOption2、 DataOption3)

Type(ピボットテーブルにおける並べ替え方法の指定)

st4
Typeはピボットテーブルレコードをソートする時にだけ使う引数で、XlSortType定数で指定します。まず使いません。
説明によると値で並べ替えをする時は、VBAではtype:=xlSortValuesとなり、対してラベルで並べ替えを指定する時は、VBAではtype:=xlSortLabelsとなるそうです。
ふたつ合わせてXlSortType列挙型定数と呼ばれます。
省略しても普通にソートできます。

Header(見出しを並べ替え範囲に含めるか)

st5
引数HeaderにはxlYesNoGuess列挙型定数を指定します。その名の通りYes、No、Guess(推定)のみっつの選択肢があります。
Header:=xlGuessの場合、Excelが見た感じから見出しをソート範囲に含めるべきかどうかを判定します。
st6
普通に降順でソートすると日本語は半角数字の後にきますが、この場合一行目が見出しだと認定されているようなので動きません。
st7
見出しを削除して、昇順でB列をソートすると、今度は一行目が見出しではないと認定されたらしく、数字が小さい順から並びました。
st8
Header:=xlYesを指定すると、一行目が見出しであると指定する=一行目を並べ替えに含めない指定をすることができます。
今回はC列でソートしてますが、数字が大きいにも関わらず、一行目は見出しとして扱われるので元の位置に残ります。
st9
Header:=xlNoを指定すると、一行目が見出しではないと指定する一行目を並べ替えに含める指定をすることができます。

一度変更すると、省略してもその後のソートにも設定が引き継がれるのでご注意ください(2007以降)。

OrderCustom(ユーザー設定リストの適用)

通常の昇順/降順以外に、特殊な並び順を適用したい場合はOrderCustomで指定します。

st10
ユーザー設定リストを見るには、データタブ並べ替えをクリックし、
st11
「順序」ユーザー設定リストをクリックします。
st12
この左にあるのがユーザー設定リスト一覧で、ここに設定された状態でオートフィルの連番対象になり、並べ替えの対象としても選ぶことができるようになります。(リストの作成方法
デフォルトで曜日とか、干支なんかが登録されています。
st13
たとえば普通に昇順だとこうなる並びを、
st14
あらかじめ設定したユーザー設定リストでの並べ替えを指定することで、このように並び変えることが可能です。
st15
ユーザー設定リストでの並べ替えをSortメソッドで実行する場合は、引数OrderCustomに何番目のリストかを整数で指定します。
今回使うリストは上から数えていくと、
st16
13番目にあたるようです。「新しいリスト」も数えます。
st17
従って、引数にordercustom:=13を指定すれば、希望のユーザー設定リストで並べ替えができます。

MatchCase(大文字小文字の区別)

st18
大文字、小文字が混在したアルファベットに対して、並べ替えした時にいっしょくたにするか分別するかを指定できます。値はTrueまたはFalseで指定します。
st19
まずはMatchCase:=True(区別する)の場合。
st20
必ず小文字の後に大文字がくる形に並べ替えがされます。
st22
元に戻して、今度はMatchCase:=False(区別しない)で実行すると、
st21
大文字小文字の順番は気にせず、文字順だけ並び変えが行われます。

デフォルトの値はFalseです(確認した記事)。
そうそう変更しない引数だと思いますが、一度変更すると、省略してもその後のソートにも設定が引き継がれます(2007以降)。

Orientation(横方向に並べ替え)

st23
こういう表に対して普通にソートすると、
st24
当然のように縦方向にソートがされていますが、これを横にすることもできます。
st25
縦方向のソートは、引数で言うとOrientation:=xlSortColumnsか、
st28
またはOrientation:=xlTopToBottomが指定された状態です。
このふたつはどちらも同じ意味なので、好きなほうを使ってOKです。
st27
対して横方向にソートする場合はOrientation:=xlSortRowsか、
st29
またはOrientation:=xlLeftToRightを指定します。
このふたつも同じ意味です。
st26
横方向にソートした例。実務で使ったことないですが。

なお、この引数も変更結果が省略しても引き継がれます(2007以降)。

SortMethod(ふりがなを使うかどうか)

st30
SortMethod:=xlPinYinの時、漢字などの並べ替えはふりがなを参照して行われます。こちらが規定値となります。
st31
対してSortMethod:=xlStrokeの時は、ふりがなは無視され漢字の並べ替えは文字コードに従って行われます

SortMethodも省略すると次回に引き継がれます(2007以降)。

DataOption1~3(テキストを数値データとして並べ替えるか)

st32
DataOptionはOrder同様1~3までをKey1~3に割り当てることができます。
値はXlSortTextAsNumbersと、XlSortNormalの二種類です(XlSortDataOption列挙型定数)。規定値はNormalのほうです。
st33
たとえば文字列の「001」と数字の「1」は、文字列(テキスト)を数字として読めば同じ並びにきます。
そういうことをするのがXlSortTextAsNumbersで、
st34
テキストと数字は別のくくりとして並び変えするのがXlSortNormalです。
Advertisements

Sortオブジェクトの使い方

続いて、2007以降に追加されたSortオブジェクトの使い方です。
おおまかなイメージとしては、Sortメソッドが範囲.並べ替え 色々設定という書き方だったのに対し、Sortオブジェクトでは範囲の指定をまず行い、その後で指定した範囲に色々設定しながら実行という二段階の構成になっています。

Sortオブジェクトの取得

st35
Sortメソッドの主体であるRangeもオブジェクトの一種です(このへんの詳しい話はデータ型の記事等参照)。Rangeに対してSortをくっつけた場合、これはメソッドになるというルールがあります。
st36
これに対し、別の特定のオブジェクト、例えばWorkSheetにSortをくっつけた場合は、これはメソッドではなくプロパティになるというルールがあります(この場合、ActiveSheetを代入できるオブジェクト=データ型Objectの変数sorttestは内部データ的にワークシートになります)。
そしてWorkSheet.Sortはまとめて「ワークシートの並べ替え機能」という対象となり、これをSortオブジェクトと呼んでいます。
つまりSortオブジェクト=Sortプロパティのくっついた別のオブジェクトです。

ソートの実行(Apply)

st38
Sortオブジェクトに対して「ソートを実行」と命令するには、うしろにApplyメソッドを付けます。
ただしこの状態だと今いるワークシートをソートするとしか命令してないので、特に何も起きません。もっと詳細にソートする範囲などを指定していく必要があります。

ソート範囲の指定(SetRange)

st39
どこをソートするかを指定するには、SetRangeメソッドを実行します。
ただし、これを実行してもまだダメです。Sortメソッド同様に、Keyを指定してやる必要があります。

ソートの優先順の指定(SortFields.Add、Key)

st40
Sortオブジェクトには「並べ替えるフィールドの集まり」を意味するSortFieldsコレクションがあり、SortFieldsコレクションを構成するオブジェクトひとつひとつはSortFieldオブジェクトと呼ばれます。データ型Objectがデータ型RangeやWorkSheetの集合体なのと似たような関係です。
SortFieldsコレクションに対してAddメソッドを使うことで、SortオブジェクトにSortFieldオブジェクトを追加することができます。
細かいことは抜きにしても、とりあえず定型文として知ってれば使えます。
st41
KeyはKeyはSortFieldオブジェクトの一種で、指定が必須です。
st42
何回も同じオブジェクトを指定するのもなんなので、SortオブジェクトではWithを使ってまとめて記述するのが一般的です。

SortFields.Clearについて

st45
並べ替えの実行時には、SortFields.Clearという命令を一緒に実行しておきます。おまじないみたいなものです。
字面からなんとなくわかる通り、SortFieldsコレクションをクリアしています。AddしたSortFieldオブジェクトは並べ替え実行後も保存されるため、後からAddしたSortFieldオブジェクトはその後に追加され、優先順位が低くなります(よって同じ引数を操作した場合、前回の設定で実行されます)。それを避けるための命令です。

というわけで、まずSortFields.Clearして、SetRangeで範囲を指定し、SortFields.Add Key:=で優先列の指定、Applyでソートの実行……という形がSortオブジェクトの実行最小単位になるかと思います。
※ワークシート全部ソートしていいなら、SetRangeなくても動きます。

' vba Sortオブジェクト基本形
Dim sorttest As Worksheet ' ソート対象のオブジェクトを指定
Set sorttest = ActiveSheet
With sorttest.Sort ' WorkSheetオブジェクトにSortをくっつける=Sortオブジェクト
    .SortFields.Clear' 一度SortFieldsをまっさらにする
    .SortFields.Add Key:=Range("A1") ' ソート優先順指定(上に書いたものほど優先)
    .SortFields.Add Key:=Range("B1") ' ソート優先順指定(Key2相当)
    .SetRange Range("A1:B5") ' ソート範囲指定
    .Apply ' ソート実行
End With
Advertisements

その他のSortオブジェクトのプロパティ

Sortメソッドと共通するものが多いです。Sortオブジェクト(公式)のメソッドはApply(ソートの実行)とSetRange(範囲指定)、プロパティはSortFields、SortMethod、Header、MatchCase、Orientationなどがあり、さらにSortFieldsプロパティを付けた=SortFieldオブジェクト(公式)にAddなどのメソッド、プロパティがあるイメージです。

見出しを並べ替えに含むか(Header)

st46
Sortメソッド同様XlYesNoGuess列挙型定数(xlYesで見出しを並べ替えに含めない、xlNoで含める、xlGuessでExcelが判断)で見出しの扱いを指定できます。
今回はプロパティなので、=の後に値を入力します。プロパティは値を保持するため、省略すると前回の設定になります(規定値:XlNo)。
Sortメソッドの引数で変更した場合も、2007以降では挙動としてSortオブジェクトのHeaderプロパティを変更しているため、値が保持されています。つまりSortオブジェクトのプロパティにあるものが、Sortメソッドの引数の中で値が保持されるものです。

アルファベットの大文字小文字を分別するか(MatchCase)

st47
こちらもSortメソッドと同じ使い方です。Trueで小文字のあとに大文字が来るように並べ替え、Falseで小文字大文字の順序を問わない並べ替えになります。
プロパティなので、省略すると前回の設定になります(規定値:False)。

縦横どちらにソートするか(Orientation)

st48
同じくOrientationです。xlSortColumnsまたはxlTopToBottomで上下、xlSortRowsまたはxlLeftToRightで横向きのソートが可能です。
プロパティなので、省略すると前回の設定になります(規定値:xlSortColumns)。

並べ替えにふりがなを使うか(SortMethod)

st49
同じくSortMethodもプロパティで指定します。
xlPinYinでふりがなに沿って並べ替え、xlStrokeで文字コードに沿って並べ変えとなります。
プロパティなので、省略すると前回の設定になります(規定値:xlPinYin)。

その他のSortFields.Addメソッドの引数(他)

SortFieldsは色んな種類のSortField(並べ替えフィールド)の集まり(コレクション)です。Sortオブジェクト.SortFieldsとすることでSortFieldsオブジェクトという「対象」を取得し、SortFieldsオブジェクトの中にその構成要素であるSortFieldを追加(Add)していくことができます。
ひらたく言えば並べ替え設定の追加です。先述の通り形として覚えてれば問題なく使えます。
Clearする前提なのでいちいち書きませんが、追加したSortFieldオブジェクトは並べ替え後も残るので、下記ひととおり何もしなければ前回の設定が残ります。

昇順/降順(Order)

st43
SortFieldオブジェクトのAddメソッド、の引数の一種です。
Sortメソッドの時と同様、Keyと一緒にOrderで指定します(例ではオブジェクトSort.SortFields.Add Key:=Range(“A1”), Order:=xlAscending)。
xlAscending=昇順で並べ替えます。
st44
xlDescending降順で並べ替えます。昇順、降順はKeyごとに設定可能です。

ユーザー定義の並べ替え(CustomOrder)

st53
ユーザー定義の並べ替えを適用することができます。
画像の曜日指定をしたいとします。上から数えて2番目です。
st51
これをSortメソッドで実行すれば、ordercustom := 2となりますが、
st52
SortFields.Addメソッドの場合は、CustomOrder := 1となります。
CustomとOrderが前後逆になり、数字が-1されます(「新しいリスト」がカウントされなくなる)。
st54
CustomOrderだと、式の中で並べ替えを定義することもできます。
st55
実行結果です。

テキストを数値データで並べ替え(DataOption)

st56
Sortメソッド同様、XlSortDataOption列挙型定数で指定します。
XlSortNormal(規定値)では文字列「001」と数字「1」は別のカテゴリとして並び変えされ、XlSortTextAsNumberでは文字列設定の数値も数値データとして並べ替えます。

色やアイコン基準で並べ替え(SortOn)

st58
Sortオブジェクトで追加された機能です。Sortメソッドではできません。
引数SortOnのあとにxlSortOn列挙型定数で指定します。規定値がxlSortOnValuesとなり、値での並べ替えを意味します。
st59
これに対して背景色で並べ変えはxlSortOnCellColorですが、色はごちゃごちゃになります。色分けする方法は後述します。
st60
文字色はxlSortOnFontColor。これも一応色分けできてますが、黒が先にくるか赤が先にくるかわかりませんし、他の文字色があればやはりごちゃごちゃになります。
st61
アイコンで並べ替えはxlSortOnIconとなります。これも現状アイコンがあるかないかで区分けされているので、昇順でも降順でも同じ結果になります。

SortOnの色優先度設定(SortOnValueプロパティ)

SortOnValueはSortFieldオブジェクトのプロパティで、ここで色の優先度の指定が可能です。

st62
書き方的には、.SortFieldsのあとのAdd~終わりまでを()で囲い、
st63
その後に.SortOnValue.Color = rgb(色)とつなげる形で設定できます。
Keyでは列の優先度を指定していましたが、ここでは色の優先度を指定している形です。
st64
実行するとこうなります。色名がわかれば、rgbyellowのように直接後ろに色名を付けてもいいんですが、
st65
わからない場合は色を選択した状態でカラーパレットを開けば、数値を確認できるので(カラーパレットの開き方)、
st66
その数値をRGB(146, 208, 80)のように入力してあげます。
st67
色なしの場合はxlNoneを指定するか、色のついた文字の並び順を降順にします。これで背景色ごとの並べ替えができました。
st68
同様にSortOnをxlSortOnFontColorに設定し、SortOnValueプロパティをrgbRedに設定することで、赤い文字を優先して並び変えることができました。
' vba 色で並び変え
Dim sorttest As Worksheet
Set sorttest = ActiveSheet
With sorttest.Sort
    .SortFields.Clear
    ' 薄い緑で塗ったセルが上に来るように並び変え(優先)
    .SortFields.Add(Key:=Range("A1"), Order:=xlAscending, _
    SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(146, 208, 80)
    ' フォントが赤いセルが下に来るように並び変え(2番目に優先)
    .SortFields.Add(Key:=Range("A1"), Order:=xlDescending, _
    SortOn:=xlSortOnFontColor).SortOnValue.Color = rgbRed
    .SetRange Range("A1:A16")
    .Apply
End With

SortOnのアイコン優先度設定(SetIconメソッド)

st69
まずアイコンについてですが、ホームタブ条件付き書式アイコンセットその他のルールとたどり、
st70
今使用しているアイコンが、アイコンスタイルの上から何番目に登録されてるかを確認します。この場合1番目です。
st71
SortOnをxlSortOnIconにセットし、
st72
その後にSetIconメソッドを記入します。書き方はSortOnValueと同じ感じですが、Addメソッドの後にまたメソッドを書くという、不思議な状態になります。
st73
引数IconにはActiveWorkbook.IconSets(数字).Item(数字)をセットします。
IconSets(1).Item(2)で実行すると、表のように横の矢印を優先する形で並び返されます。
st74
今使ってるアイコンはアイコンスタイルの上から1番目にあったので、IconSetsは1。その2番目のアイコンを使いたいので、Itemは2、という理屈です。
st75
Itemを1にした場合、感覚的には上向きの矢印がきそうなものですが、実際は下向きの矢印が先にきます。Itemの数字が小さい=下位という形です。
st76
この数字については、アイコンセットで見た時の右から順に数字が割り振られているらしいです。
' vba アイコンで並び変え
Dim sorttest As Worksheet
Set sorttest = ActiveSheet
With sorttest.Sort
    .SortFields.Clear
    ' アイコンリストの1番上のセットのうち、2番目のアイコンを優先して並び変え
    .SortFields.Add(Key:=Range("A1"), Order:=xlAscending, _
    SortOn:=xlSortOnIcon).SetIcon Icon:=ActiveWorkbook.IconSets(1).Item(2)
    .SetRange Range("A1:A16")
    .Apply
End With
やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント