本ページには広告が含まれています。

SORT 関数 指定範囲のデータを並べ替えてスピル配列で取り出す

SORT関数で指定範囲のデータを並び替える

SORT 関数の使い方を紹介します。
SORT 関数はセル範囲(配列)の内容を並べ替えて、スピル機能で複数のセルに結果を表示させることができる関数です。※「スピル」についてはこちらで解説していますのでご覧ください。

くるみこ
くるみこ

今回はSORT 関数です。この関数もサブスクリプション版の「Microsoft 365」アプリの「Excel for Microsoft 365」と永続ライセンス版の最新版 Excel 2021で使用できます。残念ながらExcel 2016やExcel 2019では使用できませんが、将来のためにも知っておいていただきたい関数のひとつです。

今までワークシート関数のソートって無かったんですね。この関数も「スピル」機能が使われているから便利そうですね。今回もよろしくお願いします(^^)/

【この記事でわかることは】
・SORT 関数の使い方がわかります

スポンサーリンク

SORT 関数の構文

SORT 関数の「数式オートコンプリート」を確認してみます。

SORT関数入力時に表示されるオートコンプリート

さらに、表示される引数の表示画像がこちらです。

SORT関数の引数入力時に表示されるオートコンプリート

SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
必要なのは「配列」でその他の[]は省略できることはわかりますね。

SORT 関数の構文をもう少しわかりやすくすると次のとおりです。

【構文】

=SORT(範囲(配列), [基準位置], [順序], [方向])
 ※[ ]の引数はオプション(省略可能)です。最初の範囲は必須です(って当然ですね)。

引数内容
範囲または配列【必須】セル範囲(配列)を指定します
[基準位置]並べ替えの基準列または行の位置を指定します
先頭がインデックス 1 です。(デフォルト)
※1は列の場合一番左側(1列目)、行の場合一番上(1行目)
[順序] 1:昇順で並べ替えます(デフォルト)
-1:降順で並べ替えます
[方向]FALSE:行単位で並べ替えます(デフォルト)
TRUE:列単位で並べ替えます
[]表示は省略可能な引数です

引数に範囲だけ指定した場合、一番左の列を基準にして行単位で昇順にソートするということです。

引数にワークシートでソートする時のような「先頭行をデータの見出しとして使用する」というオプション設定はありません。
[範囲] の指定には「見出し行」は含めないようにしてください!
引数の [範囲] に1行目の「見出し行」まで含めてしまうと「見出し」も並べ替えられてしまいますので注意しましょう!

SORT 関数の使用例

SORT 関数はスピル機能を使っているので、一つのセルに式を入力するだけで複数のセルに結果が表示されます。それではさっそく使用例を見ていきましょう。

標高を基準に行を並べ替える

今回も「日本百名山」のリストを使用します。データ量は百名山なので100行あります。

標高の高い順(降順)に並べ替える

G2 セルに入力する数式は、SORT(A2:E101,4,-1,FALSE)
標高はD列 = 4 番目の列,降順にするなら[順序]は -1 ,行単位で並べ替えるので FALSE

SORT関数で日本百名山を標高の高い順に降順ソート

結果、数式を入力したのはG4セルだけなのに見事に標高が高い順に並べ替え出来ました。
デフォルトの部分は省略可能なのでSORT(A2:E101,4,-1)でもOKですね。

標高の低い順(昇順)に並べ替える

今度は昇順(標高が低い順)に並べ替えてみましょう。

SORT関数で日本百名山を標高の低い順に昇順ソート

3つ目の引数[順序]を変更するだけですね。
G2 セルに入力する数式は、SORT(A2:E101,4,1,FALSE)
デフォルトの部分は省略可能なのでSORT(A2:E101,4)と列指定だけでOKですね。

TEXT列の並べ替え例

今度はB列の山の名前を昇順で並べ替えてみます。

漢字の列を昇順で並べ替える

TEXT(漢字)列で並べ替える

右側の結果部分のH列を見てください。予想と違う結果になっています。
実は、文字列をソートする場合は次の注意点があります。

SORT 関数はフリガナには対応していません。
文字列のソートは、読み(ふりがな)順ではなく、文字コード順に並べ替えられます。

文字コードは 「ト」=9544「阿」=12324「伊」=12363「雨」=12587「雲」=12608
文字コードは、CODE 関数で調べることができます。CODE(文字列)

ふりがなで並べ替えるには

ふりがなで並べ替える

今回の一覧にはC列に「よみかた」があったので[基準位置]に設定してソートしています。

このように、補助列を使ってフリガナ列などを作成し[基準位置]に設定してソートする方法を使いましょう。PHONETIC 関数を使えば、フリガナを取り出すことができます。

日付のソートには注意

SORT 関数の日付の並べ替えはシリアル値で行います。

日付をソートしたけど、「文字列の日付」と「シリアル値の日付」が混在していてうまく並べ替えできなかった経験のある方が多いのではないでしょうか。

SORT 関数における日付の並べ替えは、ワークシートで並べ替えを行う場合と同様にシリアル値で行われます。

日付が文字列のままでは、並べ替えはうまくいきませんのでシリアル値に変換してからソートします。

複数の[基準位置]を並べ替えるには

SORT 関数は、1つの[基準位置]しか指定できません。

ワークシートで並べ替えを行う場合は、「優先されるキー」を追加して複数のキーを選択・指定することができます。

1つの[基準位置]しか指定できない SORT 関数で同じことを行う場合は、SORT 関数内で複数の SORT 関数をネストして指定すれば可能です。

【SORT 関数で複数の[基準位置]を指定する方法】
・ソートしたい[基準位置]の優先順を決めます。
・数式は、一番外側が[優先順1]で、内側ほど[優先順]が低くなるようにします。
SORT(SORT(SORT(範囲,[優先順3の位置]),[優先順2の位置]),[優先順1の位置])

実は、ネストせずに複数の指定ができる新関数「SORTBY 関数が用意されています。
このSORTBY 関数」について解説した記事はこのリンク先にありますので是非ご覧ください。

 

まとめ(おわりに)

以上、SORT 関数について使用例を使った解説でした。
すでに使用できるバージョンのExcelユーザーのみなさんは、いろいろ試して使い方に慣れておきましょう。職場のExcelがまだ対応していないバージョンの方も、近い将来には対応するExcelバージョンにアップグレードされるはずですから。

くるみこ
くるみこ

今回は列単位で並べ替える例は取り上げませんでしたけど、最後の引数[方向]にTRUEを指定すれば[基準位置]が行になります。いずれの場合も、スピル機能で表示される領域のセルにデータがあるなど空欄でない場合は#SPILL! スピルエラーになります。

 

スピルで[範囲]指定した全部が表示されちゃうから、前回のFILTER関数と組み合わて必要な部分だけソートして取り出すなんてことができそうですね。試してみよっ(^^♪

※「スピル」の解説についてはこちらをご覧ください。

★★★ ブログランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/

スポンサーリンク

過去記事のサンプルファイルをダウンロードできます

過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください