Googleスプレッドシート SORT関数 超応用例-1（ExcelのSORT関数とは違うのだよ）
mir の個人的な格付けですが、データテーブルを操作する時に使える 四天王と言える関数があります。
FILTER関数
SORT関数
UNIQUE関数
QUERY関数（最強）
世間的にも便利な関数として評価の高い、この4つ（配列操作関数と言っても良いかも）を四天王に位置付けてるんですが、今回はこの中から並び替え関数と言える SORT関数を取り上げたいと思います。
FILTER関数については、以前3回に分けて たっぷり書きました。
四天王最強は もちろんQUERY関数なんですが、人気が高いんで結構丁寧に解説してるサイトも多いんですよね。
だから、この noteで書いても目新しさが出しにくいってのもあって・・・、なんとなく気分が乗らないので、もう少し先の機会で触れたいと思います。
というわけで、SORT関数の基本～超応用例までを お題形式で理解していくシリーズをいってみましょう！
でも初回の今回は、冒頭部分で長くなっちゃったんで、超応用なお題は次回以降ですｗ
前回の noteは、GASネタでした。なかなか学びがあると思うんで、見てない人は是非前回noteも読んでくださいな。
Excelとは違う！ GoogleスプレッドシートのSORT関数
まず GoogleスプレッドシートのSORT関数を語る上で、とても大事なことを書きます。
それは、GoogleスプレッドシートのSORT関数は、
ExcelのSORT関数とは別モノ
「ザクとは違うのだよ！ ザクとは！」 ってことです。
Googleスプレッドシートには SORTBY関数がない？
Excelユーザーが書いたであろう
「Googleスプレッドシートには Excelの SORTBY関数はないの？」
とか、
「Googleスプレッドシートで SORTBY関数に該当する関数はなんですか？」
といった 投稿や質問をネット上で見かけることがあります。
ひどいものだと
「Googleスプレッドシートは、SORT関数しかなくて、SORTBY関数がないから 使い物になりません。」
こんなことを言う人も。。
「ちょっ、まてよ！」 と言いたくなります。
そもそも Googleスプレッドシートの SORT関数を Excel側が輸入時に 勝手に魔改造して SORT関数と SORTBY関数という 2つに分けてしまったんです。
元祖である Googleスプレッドシートの SORT関数は、Excelの SORT関数とSORTBY関数の両方の機能を 合わせ持った、いわば 17号と18号を吸収した後の 完全体セルのような関数と言えます。（一部 カバーできない機能もあります）
じゃあ、それぞれ何が出来て何が出来ないのか？
Excel側のSORT関数、SORTBY関数と GoogleスプレッドシートのSORT関数の特徴を確認し比較してみましょう。
なお、Googleスプレッドシートには SORTN関数という SORT関数に似た関数もあるのですが、これは挙動がちょっと特殊なので次回以降に別で取り上げます。
Excelの SORT関数
=SORT(array,[sort_index],[sort_order],[by_col])
ExcelのSORT関数は、1つの条件での シンプルな並び替えには便利な関数です。
第2引数が sort_index（並び替え基準とする 行または列 の範囲内の番号）、第3引数が sort_order （昇順が1、降順が-1）、そして第4引数 by_col で 縦方向の並び替えか、横方向の並び替えかを（FALSE / TRUE で）指定することができます。
画像中央の式のように、第1引数の範囲（または配列）以外は省略可能で、この場合は 指定範囲の一番左の列をキーとして 昇順に縦方向に並べ替えとなります。
画像右のケースでは１列目をキーとして降順で並び替えてるんですが、この 昇順、降順の指定が 昇順 1、降順 -1 ってのも Googleスプレッドシート使いとしては慣れない点です。
特徴的なのは 第4引数を TRUE 指定することで、横方向の並び替えもできるという点。
縦横の並び替え指定は
FALSE ・・・ 縦方向（省略時はこっち）
TRUE ・・・ 横方向
こういう指定なんですよね。
なんとなくよく使う 縦方向並べ替えが TRUEっぽいイメージなんで、間違えちゃうことも多いです。
ちなみに 第2引数の sort_index は、あくまでも範囲内の○番目の行（列）かという指定なので、上の画像では シートの3行目が 並び替えの条件行ですが、範囲内では一番目の行なので 1と指定します。
で、この第4引数で縦横切り替えできるのは便利なんですが、これがあるせいで、SORT関数は 並べ替えのキー（条件）として指定できる 列（行）は1つのみに制限されています。
つまり、 ExcelのSORT関数は 複数条件での並び替えが出来ません。
※ SORTを入れ子にすることで一応対応は可能
また、並べ替えのキー列（行）を 番号指定としている為、対象範囲（配列）に無い列（行）や仮装的に生成した配列を 条件として並び替えをすることが出来ません。
シンプルだけど機能的に中途半端で物足りないのが ExcelのSORT関数です。
Excelの SORTBY関数
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],...)
Excelの SORT関数では対応できなかった
複数条件による並び替え
対象の 範囲（配列）に含まれない 列（行）を条件とした並び替え
Excelで これらの処理を実現するのが SORTBY関数です。
たとえば上のように、年齢が高い順に並び替えた上で、さらに血液型でもAが上にくるように 並べたいといった、第1条件、第2条件と複数条件を指定した並び替えは、SORTBY関数なら出来ます。
SORT関数の時と違って番号指定はできないので、どうしても記述が長くなりがちですが、対象範囲がテーブル化されている場合は、条件列の指定も
=SORTBY(テーブル2,テーブル2[年齢],-1,テーブル2[血液型],1)
といった記述が出来て、わかりやすい（可読性は高い）かと思います。
最終行を意識した範囲指定も不要ですし、Excel の FILTER関数や SORTBY関数は テーブル機能と相性がよいですね。
対象の 範囲（配列）に含まれない 列（行）を条件とした並び替え
の方は、以下のようなケースで使えますす。
Noで並び替えをしたいけど、Noは出力データに含めたくない。こんな時にもSORTBY関数が使えます。
もちろん Noの列を含んだまま並べ替えをしてから DROPで1列目を除外するといった方法もありますが、SORTBY一発なら一発で処理できるので便利ですね。
「SORTBYは横方向の並び替えができない」という意味のわからないことを書いてるサイトを見かけましたが、そんなことはありません。
SORT関数と違って縦横を切り替える引数指定はありませんが、SORTBY関数は 条件部分を縦1列で指定すれば縦方向、横1行で指定すれば横方向の並び替えと、縦横どちらにも対応できます。
先ほどの横長表バージョンを同じく 年齢を降順、血液型を昇順に横方向に並び替えたものになります。
SORTBYのは 対象範囲（配列）と条件列（行）のサイズ （行数または列数）が合わないとエラーになります。
シンプルな式で使うなら間違えることはあまりないですが、他の関数と組み合わせた複雑な式だと サイズが合わずハマることもあるので、その点は注意が必要です。
ExcelのSORTとSORTBY
Excelにおいては SORT関数の上位互換が SORTBY関数と言えます。
ただし、上で書いた通り SORTBY関数では 条件列（行）を番号指定が出来ない為、記述が長くなりがちです。
さらに SORT関数で出来た 第2引数以降を省略する記述は
SORTBY関数では式として機能しません。
つまり、対象範囲の左端の列を条件として昇順に並び替えたいってケースだと
=SORT(A2:D11)
=SORTBY(A2:D11,A2:A11,1)
このように、同じ結果をえるのに SORT関数の方が圧倒的にシンプルで簡単に記述できます。
両方を上手に使い分け出来るとよいでしょう。
もう1つ
これはSORT関数、SORTBY関数だからってわけではないですが、Excelあるあるの 空白は数式を通すと 0 になる 0化現象が発生します。
IF文で空文字にするなどで対処しましょう。
Googleスプレッドシートはテーブル機能はありませんが、0化現象に気を使わなくていいのは良い点だと思います。
ちなみに オフィスタナカの田中先生は 「SORTBYって、あまり使われねーんじゃね」って書かれてますが、個人的には 複数条件というよりは バーチャルな配列をキーとした並び替えでの需要は高いと思っています。
Googleスプレッドシートの SORT関数
SORT(範囲, 並べ替える列, 昇順, [並べ替える列2, 昇順2, ...])
で、ようやくメインの Googleスプレッドシートの SORT関数の登場です。式の型としては、ExcelのSORT関数というより SORTBY関数に近いです。
赤塗りつぶしの箇所は
=SORT(A2:D11)
で出力した結果です。ExcelのSORT関数と同じく 第2引数以降を省略することで、対象範囲の一番左の列を条件として 昇順で並び替えた結果を返します。
一番右の緑塗りつぶしの方の式は
=SORT(A2:D11,3,0,D2:D11,1)
範囲 A2:D11 ・・・ 並び替える範囲
条件列1 3 ・・・ A2:D11の3列目（年齢）
order1 0 ・・・ FALSEの意 降順
条件列2 D2:D11 ・・・ 血液型の列
order2 1 ・・・ TRUEの意 昇順
このようになっています。つまり、GoogleスプレッドシートのSORT関数は 条件列を 番号指定、範囲指定どちらでも対応できるってことです。上のように 混在でも記述できます。これは便利ですね。
あと、Excelの order（昇順、降順指定）と違って、GoogleスプレッドシートのSORT関数は 昇順 TRUE、降順 FALSE という指定です。
数値で表すと
昇順 ・・・１ （TRUE）
降順 ・・・０ （FALSE）
※ 0以外の数値は 全てTRUE扱い
このようになります。 Excelのノリで 降順で -1と指定しちゃうと 0ではないのでTRUEとして扱われ
昇順での並び替えとなってしまうので注意です。
Googleスプレッドシートの SORT関数は、まさに Excelの SORT関数、SORTBY関数のいいとこ取り 関数と言えるんですが、残念ながら 唯一弱点があり、
Googleスプレッドシートの SORT関数は 横方向の 並び替えが出来ません。
横方向の並び替えが必要になるケースは 多くはないでしょうが、これだけは Excelの方が便利ですね。残念。
Excel SORT関数、SORTBY関数、Googleスプレッドシート SORT関数 比較まとめ
出来ること、出来ないことをまとめると 以下のようになります。
Googleスプレッドシートの SORT関数は、横方向並び替えは出来ないものの、かなり優秀であることがおわかりいただけたんじゃないでしょうか。
残念ながら Excel側の SORT関数、SORTBY関数、スプレッドシートの SORT関数、いずれも 見出し行を並び替えから除外（見出し行を固定）して、その下のデータだけ並び替えといったことは出来ません。FILTER関数でもそうでしたね。
今回は触れませんが、この見出し行を固定しつつ、データの絞り込み、並び替えが出来るのが 最強の QUERY関数だったりします。うーん、万能感が半端ないですｗ
ExcelのSORT関数、SORTBY関数は、詳しくは他サイトを参考にしてください。Excelの神髄さんも 応用例など詳しく書かれています。
SORT関数は ARRAYFORMULAいらず
スピル対応バージョンのExcelなら 意識する必要はありませんが、Googleスプレッドシートにおいては SORT関数内の処理は 自動で配列処理されるという点も特徴の一つと言えます。
SORT関数内の処理に対しては 自動で ARRAYFORMULA状態になっているので、式にARRAYFORMULAを組み合わせる必要はありません。
上の画像の式は
=SORT(IF(A1:D1="年齢",A2:D11&"歳",A2:D11),1,1)
このようになっています。
IF(A1:D1="年齢",A2:D11&"歳",A2:D11)
この部分は 1行目の見出し行 が"年齢"だったら、その列の2行目以降に 歳をつけ、違う場合はそのまま出力という処理です。
通常はArrayformulaをつけないと機能しませんが、SORT関数内なので Arrayformula無しで配列処理された結果が出力されているのがわかりますね。
注意点として年齢をキーに並び替えした上で 歳をつけたい時に
✖ 加工後の文字列化した 〇〇歳で並び替えてしまう
=SORT(IF(A1:D1="年齢",A2:D11&"歳",A2:D11),3,1)
〇 元データの年齢の数値 で並び替える
=SORT(IF(A1:D1="年齢",A2:D11&"歳",A2:D11),C2:C11,1)
このように番号指定とすると、加工後のデータを使う形になるので注意が必要です。
ちなみに Arrayformulaいらずで配列処理が出来るのは、FILTER関数の特徴でもありました。
微妙に似てるこの２つの関数（イケてる関数 略して イケカン）
FILTER × SORT
は、腐女子ならカップリングを妄想しちゃうじゃないでしょうかｗ （どっちが受けだ？）
SORT関数とFILTER関数の 並び替えと絞り込みは、Excelでは両方を併用して処理するケースも多いです。でも、この2つを同時にできちゃうのが、やっぱり最強の Query関数だったりしますｗ
ほんとチート というか、さっきのカップリングの話の流れだと QUERY関数は 両方ついてる 両性具有、雌雄同体的キャラって感じですね。地獄楽だと 天仙様みたいなｗ
ただし FILTER関数の回でも書きましたが、QUERY関数では対応できない複雑な条件で並び替えを行いたい場合は、やはり並び替え特化型の SORT関数が役立ちます。
これから登場する SORT関数の超応用例は、QUERY関数では対処できない 複雑な条件の並び替えが目白押しです！
GoogleスプレッドシートのSORT関数で 横方向並び替えをする
今回は超応用例まではいけませんが、せめて前半の検証で「出来ない」と書いた項目へのチャレンジだけは、お題形式いってみましょう！
まずは ExcelのSORT関数、SORTBY関数では出来るけど、GoogleスプレッドシートのSORT関数では出来ない 横方向の並び替えです。
Q1. Googleスプレッドシートで 横方向の並び替えがしたい
データ範囲C2:L5 のデータを シート上では 2行目（範囲だと1行目）の No をキーとして横方向に 昇順で並び替えたい。
というお題です。
Excelだったら SORT関数の第4引数でTRUE指定を使うケースですね。これを Googleスプレッドシートで対応するにはどうすればよいでしょうか？
式を入れる箇所は 画像で オレンジで塗りつぶししているセル C9のみです。簡単だと思いますが、いかがでしょうか？
まずは 自力でやってみましょう！
↓↓↓回答
A1. Googleスプレッドシートで 横方向の並び替えを実現する
いきなり回答です。
=TRANSPOSE(SORT(TRANSPOSE(C2:L5)))
並び替え処理は、専用のSORT関数を使わずにやろうとするとかなり面倒なので、データをSORT関数が使える形に成形する方向性で考えると良いです。
① SORT関数では横方向には並び替え出来ないので、まずはデータを縦横変換
② 変換したデータの一番左の列をキーとして昇順で並べればよいので、SORT関数を第2引数以降を省略で利用し並べ替え
③ 再び 縦横変換し 元の表構成に戻す
少し手間はかかりますが、横方向の並び替えが出来ましたね。
縦横変換といえば TRANSPOSE関数です。
SPLITで分割する時なんかも、結構TRANSPOSE関数を使って、配列こねこねコンパイルするケースは多いですねｗ mir的には結構使う関数です。
これは割と知ってる人も多いし 簡単だったんじゃないでしょうか？
Googleスプレッドシートの SORT関数で 見出し行を含めて対応する
もう1つ「出来ない」と書いたのが、この見出し行を含めた時の並び替えです。こちらもSORT関数を少し工夫することで、見出し行を一番上に固定した状態でデータだけ並び替えができます。
Q2. SORT関数で見出し行を固定してデータ部分だけ並び替えたい
では、お題いってみましょう。
上のように 見出し行を含めた A2:D12 という範囲を指定して、G2セルのみに式をいれて 見出し行は一番上に固定して データ範囲を Noをキーに昇順に並び替えるには、どうしたらよいでしょうか？
ちなみに一番簡単な方法は 見出し行と データ部分をそれぞれ取得してデータ部分だけSORTしてから 連結する方法
={A2:D2;SORT(A3:D12)}
これなんですが、今回は 「A2:D12 という範囲を指定して」という縛りを入れています。
あと、当然ですが QUERY関数は使っちゃダメです。SORT関数で並び替えをするというお題ですからｗ
それではチャレンジしてみましょう！
↓↓↓回答
SORT関数で見出し行を固定してデータ部分だけ並び替える の回答の前に
先に Excelだったらどういう処理方法があるか？を触れておきましょう。
=VSTACK(TAKE(A2:D12,1),DROP(A2:D12,1))
Excelは 中カッコによる 範囲の結合は出来ませんが、代わりにVSTACK関数を使って縦連結ができます。
そして Googleスプレッドシートには無い TAKE関数、DROP関数が今回のケースでは活躍します。
範囲の末端から指定した行数、列数を指定するTAKE関数、そして 逆に指定した行数、列数を捨てるDROP関数。
どちらも優秀な配列加工関数で、 Excel14の関数シリーズでも検証しています。
Googleスプレッドシートで同じような処理をする場合は、1行目の見出し行の取得 TAKE(A2:D12,1) の部分は、他の関数で簡単に代替できます。
=TAKE(A2:D12,1)
↓
=INDEX(A2:D12,1,)
または
=CHOOSEROWS(A2:D12,1)
しかし、DROPで取得する 見出し行以外の部分は、なかなか面倒です。
=DROP(A2:D12,1)
↓
=FILTER(A2:D12,SEQUENCE(ROWS(A2:D12))>1)
うーん、これを使って Googleスプレッドシートで式を組むと
=LET(a,A2:D12,{INDEX(a,1,);SORT(FILTER(a,SEQUENCE(ROWS(a))>1))})
こんな感じになります。これはちょっと煩雑すぎるし 無しですね。。
余談ですが、式が長くなると 同じ範囲が何度も登場します。範囲変更があった時に書き換えが箇所が多く面倒なので、上のように LETを使うのがおススメです。
個人的には 3回（長い式とかだったら2回）同じものが出てきたら、LETで変数化しときたいかなって思います。
先に分割する方法以外だと、どうすれば良いか？
SORT関数内で処理する方法があります。
A2. SORT関数で見出し行を固定してデータ部分だけ並び替える
回答です。SORT関数の複数条件を使います。
=SORT(A2:D12,SIGN(ROW(A2:D12)-2),1,1,1)
ポイントは
SIGN(ROW(A2:D12)-2)
ここですね。 -2 の 2は 見出し行のシート上での行番号です。
ROW(A2:D12) で行番号の配列を生成して、そこから-2 したものを SIGN関数 という関数に食わせています。
SIGNを 通すと
0より大きい数値（プラスの数）は 1
0より小さい数値（マイナスの数）は -1
0は そのまま 0
の3パターンに集約されます。
これによって、どのような 条件配列が生成されているか？
このように 見出し行だけ 0で データ部分は1という 配列になっています。
これを1つ目の条件として昇順指定することで、見出し行は一番上に固定することができます。
また データ部分の方は 条件1では すべて 1なので差がないため、
=SORT(A2:D12,SIGN(ROW(A2:D12)-2),1,1,1)
続く条件2の 1列目（No）を条件として 昇順 とする という指定で、No順に並べ替えすることができるのです。
わざわざ見出し行だけ別に用意する必要がなく、これはなかなか便利です。
ただし、これだとROW関数を使ってるのでシート上の範囲にしか使えませんし、見出し行が1行の時だけ使えるという縛りがあります。
A2. 補足 配列の並び替えや見出し行が複数のケースでも使える 式を作る
IMPORTRANGE関数で別スプレッドシートから取得したケースなど、並べ替えの対象が範囲ではなく配列だった場合、さらにあまり多くはないでしょうが 見出し行部分が1行だけでなく2行だったり複数見出し行の場合はどうすればよいでしょうか？
これは補足回答なので、そのまま式にいきましょう。
=LET(x,{A2:D13},y,SEQUENCE(ROWS(x)),SORT(x,IF(y>2,1,y-2),1,1,1))
少し式が煩雑になりますが、このような記述で対応できます。
シート上の範囲ではない 配列の場合は ROW関数が使えないので、
SEQUENCE(ROWS(x))
とすることで、まずバーチャルに配列の行数分の連番を生成します。
これをさらに
IF(y>2,1,y-2)
とすることで、見出し行は 0以下で連番、データ範囲は 1と ナンバリングします。
上の数字2は 見出し行の行数です。今回は2行分見出し行である為 2となります。
注意点として ここで 見出し行部分を 全て 0してしまうと、次の条件2で 見出し行の中で並び替えが起こってしまう可能性がある為、見出し行部分は 0以下で上の方が小さい数字になるように結果を返す必要があります。
それを y-2 で出力しています。
もちろん 一般的な 見出し行が1行で シンプルな並び替えのケースだったら、QUERY関数で行うのが圧倒的に簡単なのですが、見出し行が2行以上ある場合は QUERY関数だと 見出し部分が1行に集約されてしまうという、謎現象が発生します。
このようなケースでは、今回の 配列かつ見出し行が2行以上の並び替えをするSORT関数が役立つかしれません。
次回は SORT関数 さらに応用へ
今回は Excelの SORT関数、SORTBY関数との比較や 基本動作の話が中心になってしまったので、お題も2つと 物足りなかったかもしれません。
次回は、SORT関数以外の機能としての並び替えの紹介もしつつ、さらに応用編へと入っていきたいと思います。
この記事が気に入ったらサポートをしてみませんか？