MOS365の出題範囲が発表になり、Excelの科目では新たにSORTBY関数が出題範囲に入ることになりました。SORTBY関数は並べ替えをする関数です。並べ替えをする関数としてはSORT関数があります。
SORT関数の解説
SORTBY関数はSORT関数の機能強化版です。
今回はMOSの出題される範囲から、こういう使い方で使われるだろうという範囲で説明しています。
SORTBY関数とは
Excelで並べ替えをする機能はいくつかありましたが、それは機能なので、元データが変更されたらもう一度並べ替えをしなければなりませんでしたが、SORTBY関数は数式なので元データを変更した途端に並べ変わります。
SORT関数との違いは、SORT関数ではスタンダードな方法では並べ替え基準を1列しか指定できないのに対し、複数の並べ替え基準で並べ替えることができます。そのため、SORT関数とはまた違った引数の指定方法となっています。
SORTBY関数の使い方
SORTBY関数の書式は次の通りです。
=SORTBY(並べ替える一覧表,並べ替え基準の範囲1,昇順か降順か1, 並べ替え基準の範囲2 ,昇順か降順か2…)
SORT関数では並べ替え基準が列番号で指定するのに対し、SORTBY関数では、並べ替えの基準は範囲で指定するので、若干面倒です。ただ、指定方法は、初めに一覧表範囲を指定したら、基準の範囲と並べ替え順序を交互に入力するだけなので、把握はしやすいです。
並べ替え基準は昇順が「1」で降順が「-1」なのですが、これは入力時にガイドしてくれるので覚えていなくてもよいでしょう。
注意しなければならないのは、並べ替え基準を範囲で指定するので、並べ替える一覧表の行のセル数と、並べ替え基準の行のセル数は同じにしないといけません。これが間違っていると「#VALUE!」エラーになります。
SORT関数では行方向の並べ替えか列方向の並べ替えか指定できますが、SORTBY関数も一見できなそうに見えるのですが、並べ替え基準範囲を横方向に指定すれば横方向にも指定できます。しかしこれはとても特殊な例だと思いますのでMOSでは出題される可能性は薄いと思います。
次の販売結果一覧表において、販売履歴を商店ごとに販売額の高い順に並べ替えた結果を求めるには次の計算式です。
=SORTBY(A2:C11,B2:B11,1,C2:C11,-1)
SORTBY関数は、ひとつのセルに計算式を入力すると、結果が複数のセルに求められるExcelの新しい機能、スピルを使った関数です。スピルはテーブルの設定された範囲には設定できませんので、SORTBY関数もテーブルの中では使えません。しかし、ここが重要なのですが、テーブル範囲を元データに指定することはできます。むしろSORTBY関数の元データはテーブルでなければ魅力がないとも言えます。そのくらいSORTBY関数はテーブルと相性がよいと思います。
SORTBY関数で考えられる問題例
MOSの出題範囲を見る限りではテーブルを使うことを推奨していると考えられます。SORTBY関数のテーブルととても相性のよい関数ですので、テーブルを元にした出題がある可能は非常に高いと思います。
上記のSORTBY関数の利用例をテーブルに置き換えると次のような問題文になるでしょう。
- セルE2に販売記録テーブルにおいて、販売先の昇順で並べ替え、同じ販売先なら販売額の降順で並べ替える計算式を関数を使って作成してください。
=SORTBY(販売記録,販売記録[販売先],1,販売記録[販売額],-1)
元データがテーブルだと、構造化参照が使えるので、セル範囲をテーブル名やテーブル名[項目名]という指定の方法で指定できるので、とても分かりやすくなり、データの増減や列の順序の変更などがあっても計算式が崩れないという特徴があります。また関数の入力もとてもやりやすくなります。
- セルH2に成績テーブルで、合計の点数の降順で、同じ合計の点数なら国語の点数の降順、同じ国語の点数なら、数学の点数の降順、同じ数学の点数なら、英語の点数の降順に並べ替えた、学籍番号の一覧表を、関数を使って求めて下さい。
=SORTBY(成績[生徒番号],成績[合計],-1,成績[国語],-1,成績[数学],-1,成績[英語],-1)
この問題のポイントは2つで、並べ替え範囲は、一覧表範囲の中になくてもよいということです。一覧表範囲というと表全部を考えてしまいますが、1列だけでもよいのです。このあたりを知っているかどうかというのは、試験の出題者としては審査したくなるところなので、わかっておくとよいと思います。セルH1に生徒番号とだけあるので、一覧表全部ではないとそこからも判断できるでしょう。
もう一つのポイントは、気を付けなければならないところで、テーブルの下部に平均値を求める集計行もありますが、ここは一覧表の範囲ではないので、テーブル機能の常識的に、引数の指定に含めてはいけません。これは1行目のタイトルにも言えることです。もし含めた場合、計算式に指定する範囲は「成績[生徒番号]」となるところが「成績[[#データ],[#集計],[生徒番号]]」となり、成績テーブルの範囲+集計行の範囲になってしまい間違いとなります。テーブルを基とする出題の時には注意すべき点です。
SORT関数が出題される試験
SORT関数が出題されるのはMOS365ExcelExpert（上級）です。MOS365ExcelAssociate（一般）では出題範囲に入っていません。またMOS365以前のバージョンでも当然出題されません。
まとめ
複数の項目で並べ替えができるSORTBY関数について解説しました。
複数項目で並べ替えができる以上、出題は1項目ではなく、もし成績一覧表だとしたら5科目ある可能性もあるので、合計を含めて6列での並べ替えになるかもしれません。そうなると入力する引数の順番を途中から逆に入力してしまうといった混乱がないように、しっかりひとつずつ入力していきたいところです。
関連リンク
- MOS公式サイトのMOS365ページ
- MicrosoftのSORTBY関数の解説
- MOS365出題範囲「SORT関数」解説
コメント