今回は、複数のキーによるデータの並べ替えを素早くやる方法がないか調べます。
古きよきダイアログ
複数列のキーを切り替えながらソート（並べ替え）したいことはよくあります。
データをざっとチェックしたいときなど、組織や期間、カテゴリといった階層的なグルーピングに有用です。
Excel なら「並べ替え」ダイアログを使えばいいのですが、これがどうも古いというか使い勝手が悪いというか、今風の言葉で言えば UI/UX としてイマイチな感じなのです。
複雑な並べ替え条件を作れるのは確かに便利なのですが、逆に単純な条件で次々と試したいときに、いちいちダイアログを開いては条件設定をチマチマと変更する操作がダルすぎて、今のデータ時代のスピード感には合いません。
Excel で複数キーによるソートをもう少し手早くパッパとこなす方法はないのでしょうか。
複数キーで並べ替えをするには
色々調べたところで結局 Excel に用意されている並べ替え機能を使う以外にはありません。 ありものの機能をいかに効率的に使いこなせるかがキモです。
Excel のソート機能である「並べ替え」ダイアログ、「並べ替え」コマンド、SORT/SORTBY 関数の活用法を調べます。
「並べ替え」ダイアログのキー操作を使い倒す
まずは基本である「並べ替え」ダイアログです。
できるだけ素早く操作するため、マウスを使わずに、キー操作だけで「並べ替え」ダイアログを使ってみます。
ダイアログを開くキー操作
「並べ替え」ダイアログを直接開くショートカットキー自体は存在しないようです。
ただこれを開くメニューの経路はいくつかあるので、アクセスキーなら何通りもあります。
それぞれの叩き具合からして、中でも最も素早くタイプできそうなのは以下でしょうか。
Alt+A⇒S⇒S
データ範囲やテーブルの中でこのアクセスキーを叩けば「並べ替え」ダイアログがすぐに開きます。 覚えるのも簡単そうです。
しかしながらこのアクセスキー、本ブロクの読者のようにお上品な方々には少々お指に合わないかもしれません。
お気に召さなければ代わりに次のアクセスキーをお勧めします。
Alt+D⇒S
これは古代 Excel から伝わる隠しコマンドのひとつです。 タイプ数も少なめでおトクすね。 Data Sort と覚えましょう。 ただ将来的になくなる可能性があります。
「並べ替え」ダイアログは「OK」ボタンを押すと閉じてしまうので、ソートしなおす度にいちいちダイアログを開き直さなければならなかったのがけっこう面倒でしたが、これで多少は軽減されるでしょう。
本来なら「並べ替え」ダイアログに、閉じずにソートを再実行できる「適用」ボタンなどがあればいいだけなのですが、ないものは言っても仕方ありません。
ダイアログ設定のキー操作
次に、「並べ替え」ダイアログの設定もキータイプだけで操作できるようになりたいところなのですが、これがなかなか結構クセがあって慣れるのが大変です。
以下に「並べ替え」ダイアログのキー操作を図にまとめてみましたので参考にして下さい。
基本的にボタンや入力項目の選択は Tab キーで移動できます。逆方向は Shift + Tab です。
ダイアログを開いた直後は替え条件リストが遠くて Tab を何度も連打しないとたどり着けませんが、Shift + Tab で逆から行けばすぐです。
始めに必要な数だけ ‘A‘ をタイプしてレベルを追加した後、Shift + Tab するのが最速です。
レベル行間の移動は ↑/↓ （矢印）キー でできるのですが、レベルが青色で選択状態になっているときにしか移動できません。
一度レベルのドロップダウンを選択すると、矢印キーが取られてレベル選択状態に抜け出せなくなります。
レベル選択状態に戻すには Shift + Tab で行が青くなるまで選択を移動します。
条件入力のプルダウンは ↓ キーで選択し Enter で確定します。
Enter のタイミングを間違えるとダイアログ自体が閉じてしまいますので気を付けて。
レベルの優先度の入れ替えは Ctrl キーを押しながら ↑/↓ （矢印）キーを押します。
今時ならマウスドラックで移動できても良さそうなものですが、できません、今のところ。
下位のキーから並べ替えをする
Excel は１つの列だけをキーとした並べ替えならダイアログなしで簡単に行えます。
実はこの単列の並べ替え機能でも、適用する列の順番を工夫すれば複数キーによるソートを実現できます。
単純に、優先度の低い方から並べ替えをすればいいのです。
たとえば、「店舗/昇順」⇒「商品/昇順」⇒「売上/降順」の優先度でソートしたい場合、「売上/降順」⇒「商品/昇順」⇒「店舗/昇順」の順番で並べ替えをすれば望む結果が得られます。
後からの並べ替えで前のソート結果が崩されてしまうような気がしますが、実際にやって見ると位置は変わっても順序に入れ替わりないのが確認できます。
これでそうなるのは Excel の並べ替え機能が「安定ソート」だからです。
安定ソートとはソートアルゴリズムの分類のひとつで、同じ値の間では決して順序の入れ替えが起きないという特徴を持ちます。 Excel の採用しているソートアルゴリズムが何なのかは公開されていないので分りませんが、その動作から安定ソートに属するのは確かでしょう。
見出しにオートフィルタのついたリスト表や Excel テーブルなら、マウスでフィルタのプルダウンを気軽にポチポチと選択していくだけです。 キー列や優先度の組み合わせを変更したい場合には、下位から並べ替えしなおす必要があります。
単なるセル範囲だとマウスでのメニューの「並べ替え」を実行することになりますが、ちょっと面倒です。 できればキー操作のみでソートしたいです。
キー操作による並べ替え
単列の並べ替えをキー操作だけでやってみます。
もしデータ表が Excel テーブル（青いシマシマの）になっているのなら、次のショートカットでフィルターのプルダウンを開けるので、データのどこにいてもその場の列での並べ替えが可能です。
Alt+Shift+↓（下矢印）
ただのセル範囲はもちろんオートフィルタが付いたリストでも残念ながらこのショートカットは使えません。
その場合には、コンテキストメニューにある並べ替えを使うのが手っ取り早いです。
アクセスキーは以下の通りです。アプリケーションキー（App）を使います。
昇順：
App+O⇒S降順：
App+O⇒O
アプリケーションキー（メニューキーとも）はキーボードの右下の方にある四角っぽいマークのあるキーで、右クリックの代わりにキー操作でコンテキストメニューを開くのに使われます。
古いキーボードやノートPCなどのレイアウトによってはアプリケーションキーが無いかもしれません。
代わりに Shift + F10 を使ってもできるのですがかなり機動性に劣ります。
ちなみにセル範囲を指定したデータの並べ替えで先頭行を見出しとみなすかどうかは、「並べ替え」ダイアログの「先頭行をデータの見出しとして使用する」チェックの状態に依存します。 デフォルトではオンになっているようです。
複数列を選択してコンテキストメニューからの並べ替えを実行するとエラーになりますが、 複数列でも一括で並べ替えができたら便利だったのにと思います。
SORTBY 関数を使う
Microsoft 365 版の Excel をご使用なら、SORTBY 関数が活用できるかもしれません。
SORTBY 関数は、セル範囲をソートしたデータを動的配列として返す関数であり、複数キーによる並べ替え結果のデータをスピル（SPILL）により展開させることができます。 最近 Microsoft 365 版の Excel に追加された関数の一つで、ほかにより簡易的な SORT 関数というのも追加されています。
SORTBY 関数を使ってみる
SORTBY 関数の基本的な使い方は以下のようになります。
SORTBY(データ範囲, キー1の範囲, キー1の順序, キー2の範囲, キー2の順序, ・・・)
詳しい使い方は、すでに分かり易い解説記事が Web 上にたくさんあるのでそちらを参照していただくとして、さっそく試しに使ってみたいと思います。
見出しを含めないデータのセル範囲を指定してみる。
=SORTBY(A2:E100, B2:B100, 1, C2:C100, 1, D2:D100, -1)
テーブル名を指定してみる。
=SORTBY(テーブル1, テーブル1[店舗], 1, テーブル1[商品], 1, テーブル1[売上], -1)
いや、めんどくさ。
キー列の指定には列のセル範囲の参照アドレスや構造化参照名で入力する必要があって、並べ替え条件の変更を考えると全然お手軽ではありませんね。 「並べ替え」ダイアログを使った方が断然ましです。
なのでここでは発想を変えて、その都度数式を書く代わりに、使い回しのきく汎用的な「並べ替えシート」を作成することを考えることにします。
イメージとしては以下のように、対象データのセル範囲の参照アドレスやテーブル名を入力するだけで、自動的に並べ替え条件のドロップダウンが構成され、結果がスピルされるようなシートになります。
並べ替えシート
並べ替えキー列を３つ用意した並べ替えシートを作成してみます。
新規ワークシートを開き、以下のように数式を配置します。
各数式は以下を所定のセルでコピペしてください
- A1
=OFFSET(INDIRECT(I2), -1, 0, 1)
- A2
=LET(
data, INDIRECT(I2),
head, A1#,
a, "昇順",
d, "降順",
SORTBY(data,
INDEX(data, 0, XMATCH(I5, head)), SWITCH(J5, a, 1, d, -1),
INDEX(data, 0, XMATCH(I6, head)), SWITCH(J6, a, 1, d, -1),
INDEX(data, 0, XMATCH(I7, head)), SWITCH(J7, a, 1, d, -1)
)
)
- I5:I7 (「データの入力規則」のリスト)
=$A$1#
- J5:J7 (「データの入力規則」のリスト)
昇順,降順
使い方は、データ範囲の入力セル（I2）に元データのセル範囲（見出しを含まないデータ範囲）の参照アドレスを文字列で入力しするだけです。テーブル名でも OK です。
入力例
| シート名!セル範囲 | Sheet1!A2:E1000 |
| シート名!テーブル名 | Sheet2!テーブル1 |
| [ブック名]シート名!セル範囲 | [C:\book1.xls]Sheet3!A2:E1000 |
あとはソートキーとソート順のドロップダウンで条件を変更すると、スピル結果に動的に反映されます。
ただ、やってみるとデータ範囲の参照を文字列で入力するのは思いのほか面倒です。 元データを別シートや別ブックから取ってくることになるため、参照にはシート名やファイル名も含める必要があるからです。
対処法としては、入力セルにいったん = をタイプし、データ範囲やテーブルをマウスドラッグ等で選択することでシート名やファイル名付きの参照を数式として入力させます。
その後、セル範囲をダブルクォート（"）で囲むか、あるいは先頭の = を削除して文字列とします。
=Sheet1!A2:E100 ↓ ダブルクォート ="Sheet1!A2:E100" あるいは = を削除 Sheet1!A2:E100
注意点としてはシート名がシングルクォート（'）で囲まれている場合、先頭にもう一つ ' を重ねないとうまくいかないかもしれません。
'2001年 売上'!A2:E100 ↓ 先頭のシングルクォートを追加 ''2001年 売上'!A2:E100
ソートキーは３個指定できるようになっていますが、同じキーを複数回指定していると優先順位の低い方は無視されます。 キーが３個も不要で、下位の入力をあえて指定なしにしたい場合はキーを重複させてください。
このシートを「並べ替えシート.xlsx」などとして保存して取っておけば、いつでも再利用できます。
複数キーによるソートを支援するマクロ
「並べ替え」ダイアログへの複数キー入力操作を支援するためのマクロを作成しました。
本マクロは並べ替えの実行をするものではなく、あくまで「並べ替え」ダイアログに以下の２つの操作をサポートするものです。
1. 選択セルからのキー設定
本マクロは「並べ替え」ダイアログを開きますが、マクロの実行時に選択されていたセルの列がキーとしてあらかじめ設定されます。
表内で複数セルを選択しておけば、各セルの列が複数キーとしてあらかじめレベルに追加された状態でダイアログが開きます。
また同一列内で選択されているセルの個数によって並べ替え順を指定できます。 １個だけ（あるいは奇数個）セルが選択されている列なら昇順に、２個（あるいは偶数個）なら降順になります。
2. ダイアログ表示の維持
並べ替えダイアログの「OK」ボタンを押すと、並べ替え実行後に閉じられたダイアログがそのまま自動で開きなおされます。
これは「適用」ボタンの挙動を疑似的に実現したもので、ソート結果を見ながら条件設定の変更を繰り返すような使い方を想定しています。
ダイアログを本当に閉じて終了させるには「キャンセル」ボタンを押します。
＊ ＊ ＊
値の比較方法によって二つのマクロを用意しました。
- 「並べ替えダイアログ表示」マクロ
- 「並べ替えダイアログ表示_厳密比較」マクロ
Excel の並べ替えは基本的に英字の大文字と小文字を区別せず、日本語では「ふりがな」を使った比較を行いますが、それでは困る場合、「並べ替えダイアログ表示_厳密比較」マクロの方を使用してください。 並べ替えオプションが以下のように設定された状態でダイアログを開きます。
Option Explicit Sub 並べ替えダイアログ表示() If TypeName(Selection) <> "Range" Then Beep: Exit Sub On Error GoTo catch Call showSortDialog(Selection) Exit Sub catch: MsgBox Err.Description, vbExclamation End Sub Sub 並べ替えダイアログ表示_厳密比較() ' 英字の大小文字を区別し、ふりがなを使わない If TypeName(Selection) <> "Range" Then Beep: Exit Sub On Error GoTo catch Call showSortDialog(Selection, ignoreCase:=False, useFurigana:=False) Exit Sub catch: MsgBox Err.Description, vbExclamation End Sub Private Sub showSortDialog(ByVal sortKeys As Range, Optional ignoreCase As Boolean = True, Optional useFurigana As Boolean = True) Dim sortObject As Sort Dim sortTable As Range Dim keyOrderMap As Object Set keyOrderMap = CreateObject("Scripting.Dictionary") Dim sht As Worksheet Set sht = sortKeys.Worksheet Set sortObject = sht.Sort Set sortTable = sortKeys.CurrentRegion If Not sortKeys.ListObject Is Nothing Then Set sortObject = sortKeys.ListObject.Sort Set sortTable = sortKeys.ListObject.Range ElseIf sht.AutoFilterMode Then If Not Intersect(sht.AutoFilter.Range, sortKeys) Is Nothing Then Set sortObject = sht.AutoFilter.Sort Set sortTable = sht.AutoFilter.Range End If ElseIf Not rangePivotTable(sortKeys) Is Nothing Then GoTo no_data End If If sortTable.Rows.Count = 1 Then GoTo no_data Set sortKeys = Intersect(sortTable, sortKeys) If sortKeys Is Nothing Then GoTo no_data Dim c As Range For Each c In sortKeys.Cells Dim sortKey As Long Dim sortOrder As XlSortOrder sortKey = c.Column - sortTable.Column + 1 sortOrder = IIf(keyOrderMap(sortKey) = xlAscending, xlDescending, xlAscending) ' 同列セルを奇数個選択なら昇順、偶数個なら降順 keyOrderMap(sortKey) = sortOrder Next Call setupSortDialog(sortObject, sortTable, keyOrderMap, ignoreCase, useFurigana) On Error GoTo dlg_err sortTable.Cells(1).Select Do While Application.Dialogs(xlDialogSort).Show Loop sortKeys.Select Exit Sub no_data: Err.Raise 9999, "showSortDialog", "選んだ範囲内にこの操作を適用できません。範囲内の 1 つのセルを選んでから、もう一度お試しください。" dlg_err: Err.Raise Err.Number End Sub Private Sub setupSortDialog(sortObject As Sort, sortTable As Range, keyOrderMap As Object, ignoreCase As Boolean, useFurigana As Boolean) With sortObject .SortFields.Clear .Apply ' おまじない Dim sortKey As Variant For Each sortKey In keyOrderMap.Keys .SortFields.Add Key:=sortTable.Columns(sortKey), Order:=keyOrderMap(sortKey) Next If TypeName(.Parent) = "Worksheet" Then .SetRange sortTable End If .Header = xlYes .MatchCase = Not ignoreCase .Orientation = xlTopToBottom .SortMethod = IIf(useFurigana, xlPinYin, xlStroke) End With End Sub Private Function rangePivotTable(rng As Range) As PivotTable On Error Resume Next Set rangePivotTable = rng.PivotTable End Function
【使い方】
- 新規標準モジュールに上記 VBA プログラムをコピー＆ペーストします
- データとは別に新規ワークブックで用意することをお勧めします
- データのセル範囲のソートしたい列にあるセルを１個以上選択します
- セルを複数選択するにはセル範囲をドラッグするか、
Ctrlキーを押しながらセルをクリックしていきます - 選択順が並べ替えの優先度になります
- 同一列内のセル選択が奇数個なら昇順、偶数個なら降順になります
- セルを複数選択するにはセル範囲をドラッグするか、
- マクロを実行します
- マクロにショートカットキーを割り当てると使い勝手が良くなります
- 選択したセルの列がキーとして追加済みの状態で「並べ替え」ダイアログが開かれます
- 「OK」ボタンを押して、ソートを実行します
- 自動的に「並べ替え」ダイアログが再表示されるので、必要に応じて条件を変更し再度「OK」ボタンを押します
- 必要に応じて繰り返します
- 「キャンセル」ボタンを押してダイアログを閉じます
ソート対象となるデータ範囲は選択セルから連続した範囲として自動で判断されます。
先頭行は見出しであるものします。
ソート可能なデータ形式は以下の通りです。
- 表形式（つまり見出し行のある）のセル範囲
- オートフィルターが有効になっているリスト
- テーブル（青いシマシマの表）
ピボットテーブルは対象外です。
複数の表に渡ってセル選択されたら、最初の選択セルを持つ表のデータのみをソート対象とします。
まとめ
複数キーによる並べ替えについて効率化できないかいくつか考えてみました。
やってみて使えそうな方法はあったでしょうか。
結局はクセのある「並べ替え」ダイアログの操作に慣れればいいだけの話なのですが、それでもちょっと徒労感は否めません。
「並べ替え」はもう改善されないのでしょうか。
「クイック分析」や「スライサー」のようなお便利機能もあるのだから、並べ替えにもそのノリでイカした UI がサポートされる可能性はないとは言えません。
エクセルさんは最近次々と新たな機能を取り入れてきていますので、旧来機能も改善されていくのではと期待しています。
関連記事
並べ替えでふりがなが使われると生じる不都合については、以下の記事で触れています。
