種類の違うKeyの同時指定はどちらが優先？VBAでは記述順

vbanarabihukugoukieyecatch

エクセルVBA 並び替え 列と背景色の種類の違うキーの時は優先順に注意です。

どちらを優先するかで並び替えが全く異なります。
優先順はキーのコード記述が実行順になります。

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

VBAを使って、エクセルのセルデータの並び替えを行うツールを作成しています。

エクセルVBAでは、バージョン2007以降、並び替えがSortオブジェクトを使用することになりましたので、それ以前では不可能であった種類の違うキーで並び替えを行なえるようになりました。

今までの列をキーとした値による並び替えに加えて、セルの背景色、文字の色、アイコンでの並び替えを絡めさせての並び替えが可能になったということです。

この作成しているエクセル並び替えマクロツールでは、新しい並び替え3種の内、最も使用頻度の高い「セルの背景色」についての種類の違うキーの並び替えが可能になっています。

並び替えマクロ　記事階層

この記事の番号は8番です。

並び替えを異種のキーで行う時の優先順位の取り方

並び替えが列キーのみの場合や背景色キーのみの場合は、普通にキーの指定順に処理が進行しますので、実行される優先順位は、キーを指定した順番ということになります。

では、列キーと背景色キーが混在した場合はどちらが優先されるでしょうか？

それは「列キーが優先される！」と考えられそうですが、あくまでもキー指定（コード記述）の順番に従って優先度が決定しています。

例題を示して説明します。

エクセルの表で、スーパーでの売値一覧。背景色が黄色の商品が特売品です。

単価列が優先キーで次に背景色キーとする場合（昇順）

（つまり、最初に単価列を並び替えるようにレベルをセットします。）

最優先キーは単価キー

単価が昇順に並び、同単価の場合背景色黄色のセル（行）が上位表示される。

背景色キーが優先キーで次に単価列キーとする場合（昇順）

（つまり、最初に背景色を並び替えるようにレベルをセットします。）

最優先キーは背景色キー

背景色が黄色のキーが上位行にまとめられて、次に単価も昇順で並べられる。その他の背景色がないセルは、それだけでまとまって昇順並びとなる。

エクセルの並び替えツールでは、一つの並び替えキーをセットするのに「レベル」という項目を作成して優先順位付けを行っていきます。

このエクセル並び替えマクロツールでは、予め列キー3種類、背景色キー3種類の設定枠を作成しています。

どちらのキーを優先するかは、優先順位切り替えのオプションボタンで設定できるようにしています。

目次へ戻る

異種のキーで並べ替えが出来るVBAコードの組み立て方法

種類の違うキーで並び替えを行なうためには、並び替えキーを設定するVBAコードの記述順を考慮する必要があります。

並び替えマクロツールの利用者が、どちらの種類のキーを選択するかに合わせて、自動的に記述コードが変更されるというプログラムを作るのが理想ですが、少々手間暇・思考力がかかりそうなので止めておきます。

もっと簡便な、理解しやすい方法として、並び替えのプログラムコードを「列キー優先型」と「背景色優先型」に分けて作成し、利用者の選択でどちらかに割り振りをするという方法です。

2種類のキーの優先切り替えは、ユーザーフォームのオプションボタンで、「異種キーの並び替え」を選択の後、Visualになったオプションボタンから「列」か「色」を選択できるようになります。

列キーの設定についてのVBAコードの説明は先記事のこちらを参照してください。

vbanarabiretukieyecatch 複数列に自由にキーを設定するVBA。Sort条件設定のコード

背景色キーの設定についてのVBAコードの説明はこちらの記事を参考にしてください。

vbanarabiirokieyecatch 複数の背景色キーを指定できるVBAコードの組み立て

列キー

列キー優先型の複合キーVBAコード

列キー3個分の設定コードを上位記述し、その後、背景色キーの設定コードを3個分、設定キー合計6個をセットすることが出来ます。

VBA
Sub Sort列から背景色()
        On Error Resume Next
        ws.Sort.SortFields.Clear
            If CusOdr1 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddA), _
                Order:=Stream1, CustomOrder:="""" & CusOdr1V & """", _
                        DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddA), _
                    Order:=Stream1
            End If
            If CusOdr2 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddB), _
                Order:=Stream2, CustomOrder:="""" & CusOdr2V & """", _
                    DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddB), _
                    Order:=Stream2
            End If
            If CusOdr3 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddC), _
                Order:=Stream3, CustomOrder:="""" & CusOdr3V & """", _
                    DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddC), _
                    Order:=Stream3
            End If
            ws.Sort.SortFields.Add(Cells(1, ClrNoACellc), _
                    xlSortOnCellColor, Stream4, , xlSortNormal). _
                        SortOnValue.Color = ClrNoA
            ws.Sort.SortFields.Add(Cells(1, ClrNoBCellc), _
                    xlSortOnCellColor, Stream5, , xlSortNormal). _
                        SortOnValue.Color = ClrNoB
            ws.Sort.SortFields.Add(Cells(1, ClrNoCCellc), _
                    xlSortOnCellColor, Stream6, , xlSortNormal). _
                        SortOnValue.Color = ClrNoC
        With ws.Sort
            .SetRange SSRange
            .Header = Head
            .Apply
        End With
        On Error GoTo 0
        Cells(SSRange.Rows.Count, SSRange.Columns.Count). _
            Offset(1, 1).Select
        MsgBox "並び替えが完了しました。"
        With ソートナビゲーター
            .並び替え列1セル.Value = ""
            .並び替え列2セル.Value = ""
            .並び替え列3セル.Value = ""
            .背景色選択1.Value = ""
            .背景色選択2.Value = ""
            .背景色選択3.Value = ""
        End With
End Sub

背景色キー

背景色優先型の複合キーVBAコード

背景色キー3個分の設定コードを上位記述し、その後、列キーの設定コードを3個分、設定キー合計6個をセットすることが出来ます。

VBA
Sub Sort背景色から列()
        On Error Resume Next
            ws.Sort.SortFields.Clear
            ws.Sort.SortFields.Add(Cells(1, ClrNoACellc), _
                    xlSortOnCellColor, Stream4, , xlSortNormal). _
                        SortOnValue.Color = ClrNoA
            ws.Sort.SortFields.Add(Cells(1, ClrNoBCellc), _
                    xlSortOnCellColor, Stream5, , xlSortNormal). _
                        SortOnValue.Color = ClrNoB
            ws.Sort.SortFields.Add(Cells(1, ClrNoCCellc), _
                    xlSortOnCellColor, Stream6, , xlSortNormal). _
                        SortOnValue.Color = ClrNoC
            If CusOdr1 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddA), _
                Order:=Stream1, CustomOrder:="""" & CusOdr1V & """", _
                    DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddA), _
                    Order:=Stream1
            End If
            If CusOdr2 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddB), _
                Order:=Stream2, CustomOrder:="""" & CusOdr2V & """", _
                    DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddB), _
                    Order:=Stream2
            End If
            If CusOdr3 = "ON" Then
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddC), _
                Order:=Stream3, CustomOrder:="""" & CusOdr3V & """", _
                    DataOption:=xlSortNormal
            Else
                ws.Sort.SortFields.Add Key:=ws.Range(KeyAddC), _
                    Order:=Stream3
            End If
        With ws.Sort
            .SetRange SSRange
            .Header = Head
            .Apply
        End With
        On Error GoTo 0
        Cells(SSRange.Rows.Count, SSRange.Columns.Count). _
            Offset(1, 1).Select
        MsgBox "並び替えが完了しました。"
        With ソートナビゲーター
            .並び替え列1セル.Value = ""
            .並び替え列2セル.Value = ""
            .並び替え列3セル.Value = ""
            .背景色選択1.Value = ""
            .背景色選択2.Value = ""
            .背景色選択3.Value = ""
        End With
End Sub
目次へ戻る

並び替えで種類の違うキーを実行するためのVBAのまとめ

Sortオブジェクトで並び替えが背景色でも可能になりましたので、データ分析活用でより利便性が上がりました。

このSortオブジェクトを利用して、並び替えをより使い易くカスタマイズしようとした時には、

異種キーを設定する場合のVBAコードの組み立て方法が、重要なカスタマイズポイントになってきます。

背景色のほかにも文字の色やアイコンでの並べ替えもコード化してカスタマイズできますが、

あれこれ付け加えた場合の、操作の煩雑さのマイナス部分を考えた時に、

並び替えのメインになる背景色に絞ってマクロツールに取り入れることにしました。

やってみようという方は、これを参考に文字の色の並び替えコードにもチャレンジしてみてください。

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

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

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

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

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

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

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min

前の記事

vbanarabiirokieyecatch
複数の背景色キーを指定できるVBAコードの組み立て

次の記事

共有できない問題も解決！ユーザー設定リストの外部ファイル導入法
vbanarabiyuzaseteyecatch