Googleスプレッドシート SORT関数 超応用例-3（3つの並べ替え方法の比較も）
Googleスプレッドシートの SORT関数シリーズ 第３弾です。
前回は ７つのお題を通じて、複雑な条件での並び替えの SORT関数の応用例をみていきました。（「超」応用といえるかは微妙）
SORT関数は並び替えの為の関数ですが、実は 単純な並び替え以外でも使えるケースが色々あります。今回は ちょっと変わったケースでSORT関数を使う応用例をみていきましょう。
Googleスプレッドシートで並べ替えする3つの方法
SORT関数のお題チャレンジの前に、少しだけ Googleスプレッドシートの 並び替え について掘り下げてみたいと思います。
Googleスプレッドシートで並び替えする方法は、大きく分けると3つの方法があります。
機能で並べ替え
フィルタで並べ替え
関数で並べ替え
SORT関数以外にも方法があるので、ケースによって使い分けが出来ると便利です。
それぞれ見ていきましょう。
1. 機能で並べ替え
一番簡単な並び替え方法は、Googleスプレッドシートの標準機能による並び替えです。
メニューの データ の中に
シートを並べ替え
範囲を並べ替え
この2つがあります。
どっち使えばいいの？
って迷うかもしれませんが、基本的には「範囲を並べ替え」を使うことをおススメします。
「シートを並べ替え」 は、選択中のセルの列を並べ替えキーとして昇順、降順を指定するだけなんで、もっとも簡単に並べ替えができる方法です。
ただし、名前の通りシート全体の並べ替えとなります。
上のGIF動画のように、見出し行や 表の上部の空白行などは考慮されず、また表範囲外の列も 並べ替えの影響を受けてしまいます。
さらに並べ替えのキー列は1つしか指定できず、複数条件での並び替えには対応していません。
一方、「範囲を並べ替え」 は柔軟性があります。
「範囲を並べ替え」の方は、最初に並べ替える範囲（表）を選択する必要がありますが、機能名の通り 選択した以外の部分は並べ替えの影響を受けません。
表が 独立したリージョンになっていれば、 Ctrl +A のショートカットで表全体を一発選択できます。
リージョンの考え方については、チェックボックス一括操作の回で触れています。
選択した範囲内に、見出し行（ヘッダー行）を含んでいなくて、かつ範囲内の一番左の列をキーとした 1つの条件による並べ替え の場合は、メニューに表示される 昇順（または降順）で範囲を並べ替え を選択しても良いです。
でも、基本的には 範囲を並べ替えの詳細オプション から設定した方が誤操作防止にもなりますし、おススメです。
詳細オプションを開くと、このように まず範囲にヘッダー行が含まれる場合はチェックをつけることで、ヘッダー行を上部固定で並べ替え対象から除外し、かつ 条件の列指定が カラム名（項目名）で選択できるようになります。
条件を追加することで 複合条件での並べ替えも可能。
SORT関数と同じように、年齢を降順で並べ替えた上で 血液型ごとにまとめたいといった並べ替えも出来ます。
範囲をランダム化 も並び替え機能の一つ
ちょっと変わった並び替え機能として、「範囲をランダム化」というものがあります。
これは、選択した範囲を 縦方向 に（行単位で）ランダムに並び替える機能です。
前回はこれを SORT関数でやるにはどうすればよいか？ってお題をやりましたね。
選択した範囲だけランダム並び替え となるので くじ引き的な使い方が簡単にできます。
2. フィルタで並べ替え
2つ目の 並べ替え方法は、フィルタ機能を使う方法です。
絞り込みに使うことの多いフィルタ機能ですが、並べ替えでの活用もおススメで、「範囲を並べ替え」と同じように
範囲を指定して並べ替えできる
見出し行（ヘッダー行）を除いて並べ替えできる
複数条件で並べ替えできる
と機能としては十分です。
フィルタ機能による並べ替えの特徴としては、
唯一 色（セル色または文字の色）で並べ替えができる点。
絞り込みで色を使うことの方が多いでしょうが、並べ替えにも色が条件に使えるんです。
ただし、ちょっと挙動が独特です。
色の並べ替えは RGB値の昇順、降順 とかではなくて、
指定した色のセルを上位に表示させるだけの機能
と思ってください。指定した色以外の他の色のセルの並びは変わりません。バラバラのままです。
というわけで、赤 → 青 → 黄 → 白 の順で表を並べ替えたいという場合は、
フィルタで並べ替えを 白 → 黄 → 青 → 赤 と逆順で（下に入るものから）一つずつ作業する必要があります。
ま、色での並び替えはあまり使うケースは多くないと思うので、あまり気にする必要はないですかね。
また、フィルタで 絞り込みをする場合は、カスタム数式が使えるので非常に柔軟な対応が出来ますが、残念ながら 並び替えには カスタム数式は使えません。
フィルタを使った 並び替えは一つ注意点があります。それは
フィルタを解除しても元の並びには戻らない
ということ。
絞り込みは フィルタを解除すれば元に戻りますが、並び替えは フィルタを解除しても 元には戻りません。
もちろん元データに連番などがふってあれば、それをキーに並び替えをすればよいです。ただ、そうじゃない場合は 非常に困ったことなります。
並び替えた後、タブを閉じたり作業をする前であれば 「元に戻す」ボタンで並び替えに戻すことはできます。ただし、タブを閉じちゃったり、並び替え後に色々作業しちゃった後だと簡単には戻せません。
元の並びに戻せないのは、機能を使った並び替えも同様ですが、フィルタを使った並び替えは フィルタ解除で戻せると勘違いしている人もいるんですよね。注意しましょう。
もう1点、機能の「範囲を並び替え」やフィルタでの並びかえは、並び替えを実行した時点でのデータに基づいたものです。
後から並び替えキーとなっている列の値を編集したり、データ（行）を追加したとした場合は、再度並び替えを実行する必要があります。
頻繁にデータが追加、修正される表の場合は、「範囲を並び替え」の機能 や フィルタでの並び替えは、ちょっと厳しいかもしれません。
並び替えは フィルタ表示を使おう！
「並びを元に戻せない」「追加・編集されたデータへの並び替え適用」といった問題を解決できる超絶便利な機能が、以前 noteでも 取り上げた フィルタ表示 です。
フィルタ表示を使うことで 並べ替え条件の保存が出来て、かつ フィルタ表示を解除することで 並びを元に戻すことが可能となります。
さらに 上のgif動画の通り、並び替え後に データの編集があったり、追加があった場合も リアルタイムとはいきませんが、画面更新をすると 最新データでの並びに更新されます。
並び替え後に データの追加や 並び替えのキーとなる列の編集があったり、何パターンかの並び順で見たいって要望があったり、かつ データの編集もやりたい！って場合は、フィルタ表示が最適といえるでしょう。
上のGIF画像でもわかりますが、文字の色や太字等の装飾、セルの背景色など含めて並び変えできるのも、機能やフィルタで並び替えるメリットです。
3. 関数で並び替え
並び替え方法の3番目は、関数による 並び替えです。
関数による 並び替えは
・関数により出力されたデータなので 直接編集はできない
・出力する場所（元データとは別のセル、または別のシート）が必要
・データの装飾（文字の色や太字、セルの塗りつぶし）を含めて並べ替えはできない
とデメリットもありますが
・並び替え対象外をキーとしたり 複雑な条件での並び替えが可能
・元データの更新、追加をリアルタイムで反映し常に最新の並びになる
・並び替えをしても 元データに影響しない
といったメリットも大きいです。
並び替えが出来る関数の代表例が SORT関数（SORTN関数も一応含む）と、QUERY関数です。
SORT関数
QUERY関数
今回はSORT関数が主役なので QUERY関数について細かくは書きませんが、基本的には並び替えならSORT関数を使った方が簡単です。
並び替えでQUERY関数を使った方がよいケースは
見出し行も含めて出力したい
並び替えと合わせて絞り込みやピボット集計などもしたい
こんな場面です。
SORT関数のように、対象範囲外の生成した配列などを使った並び替えもQUERY関数では難しいです。
ちなみに QUERY関数での並び替えは、機能の「範囲を並び替え」やフィルタによる並び替え、SORT関数と違って 唯一 空白も並び替え対象に含みます。
というわけで、Googleスプレッドシート の並び替えは
■並び替えた後のデータは編集不要、複雑な条件での並び替え
→ SORT関数 （条件によっては QUERY関数）
■データ編集あり、今後のデータ追加なし、並びを元に戻す必要なし
→ 範囲を並び替え 機能
■データ編集あり、今後データ追加あり、並びを元に戻す必要あり
→ フィルタ表示
この使い分けがおススメです。
関数の弱点である 「編集できない」の解決策として、元データ（行）へ飛べるリンクを仕込んで並び替える方法も 前回の noteで紹介してますので参考に。
このように並び替えにおいて圧倒的に便利な SORT関数ですが、今回は単純な並び替え以外での活用法をお題形式で学んでいきましょう！
データを〇行開けにしたい時の SORT関数
元データの一つ一つの行の間を 〇行ずつあけたいっていうありがちな要望、これをSORT関数を使った1つの式でやってみようというお題です。
Q1. SORT関数で データを〇行開けに変換したい
このように、A2:C30 の見出し行を除いたデータを E2 セルに式を入れることで、E1セルの数値 分だけ 空白行を挟んで表示させたい。という要望があります。
実はこれを実現する式は何パターンかあるんですが、今回はもちろん 「SORT関数を使った式とする」という条件縛りです。
どうでしょう、式は思いつくでしょうか？チャレンジしてみましょう！
↓↓↓回答
A1. SORT関数で データを〇行開けに変換する
回答です。
=LET(x,A2:C30,num,E1,SORT(IFERROR({x;MAKEARRAY(ROWS(x)*num,COLUMNS(x),LAMBDA(r,c,))},x),TOCOL(SEQUENCE(ROWS(x),num+1),,1),1))
まず、編集しやすいように LETを使って 元データの範囲 A2:C30 を x 、開ける行数を指定する E1 を num と置きます。
=LET(x,A2:C30,num,E1,
各行の間に 〇行分の空白を挟む必要があるので、x と同じ 横幅（列数）で、xの高さ（行数） × num の行数というサイズの 空白配列を用意する必要があります。それが
MAKEARRAY(ROWS(x)*num,COLUMNS(x),LAMBDA(r,c,))
この部分ですね。
同じ要素の指定したサイズの配列生成は幾つか方法がありますが、今回は MAKEARRAYを使ってみました。
これを 元データ xと中カッコ ; で 縦に連結させています。
{x;MAKEARRAY(ROWS(x)*num,COLUMNS(x),LAMBDA(r,c,))}
ただし、num に 0が入るケースを想定する必要があります。
numが 0の時は 間に空白行を挟まず 元データをそのまま返せばよいので、IFERRORで それを記述しておきましょう。
IFERROR({x;MAKEARRAY(ROWS(x)*num,COLUMNS(x),LAMBDA(r,c,))},x)
わかりにくいのでデータを短くして、空白を □□□ として 可視化すると
こんな感じの配列をまず生成しているわけです。
SORT関数で この配列をいい感じに並び替えることで、空白を間に差しこめば良いわけですが、その並び替えキー列となるのが
TOCOL(SEQUENCE(ROWS(x),num+1),,1)
この式です。どんな配列が生成されているのか？
画像一番右の 数値配列が、上の式の出力したものです。
このように 1,2,3 が 飛んで表示されています。これをキーに並び替えれば指定した数だけ空白が間に入りますね！
この特殊な数字の並びは 高さ ROWS(x) , 幅 num+1 の連番配列をSEQUENCE関数で生成したものを、さらにTOCOL で1列にする際に 第3引数の 縦方向にスキャンを true (1指定) することで作りだせます。
いい感じの数字の並びの1列の配列を生成し、それをSORT関数と組み合わせることで、ターゲットの配列の並びを自由に操作することができます。
これは結構使うテクニックなので 覚えておいて損はないです！
ちなみに 空白差し込みは 前にも一度お題にしており、その時は WRAPROWSを使う別の方法を紹介しました。
両方の解法を理解していると 応用の幅が広がりますね。
空白行挿入にSORT関数を使う 例を紹介しました。
セル内の文字列 並び替えをしたい時の SORT関数
続いては、文字列操作とSORT関数の組み合わせです。
行単位の並び替えではなく、セル内で 文字列を並び替える際にも実は SORT関数が使えます。
もちろん 工夫は必要ですし、そもそも1セルに複数データをいれるもんじゃない！って話なんですがｗ こういうニーズも結構あるんですよね。
3つほどお題いってみましょう！
Q2. SORT関数で セル内の改行区切りの文字列を並び替えたい
一発目は こんな感じの比較的簡単なお題です。 A1セルの 改行区切りの文字列を セル内で昇順に並び替えたいって要望なんですが、たまにこんな質問も知恵袋にあったりします。
こちらを実現する式は組めそうでしょうか？チャレンジしてみましょう！
↓↓↓回答
A2. SORT関数で セル内の改行区切りの文字列を並び替える
回答です。
=JOIN(CHAR(10),SORT(TOCOL(SPLIT(A1,CHAR(10)))))
シンプルですが、みっちり詰まった式なんで、ちょっとわかりづらいですよね？以前はこういう書き方が主流でした。（というか、これしかなかった）
この手の複雑な入れ子の多い式が苦手な方は、今は LET関数が使えるので、処理した結果を 変数化して順番に記述すると理解しやすいです。
=LET(a,SPLIT(A1,CHAR(10)),b,TOCOL(a),c,SORT(b),JOIN(CHAR(10),c))
処理の流れは、左から順に読んでいけばよいです。
a,SPLIT(A1,CHAR(10))
改行 CHAR(10) で A1 を分割する（横1行に展開される）
※並べ替えは 配列化した方が簡単なので
↓
b,TOCOL(a)
a を縦1列に変換
※GoogleスプレッドシートのSORTは縦方向の並べ替えしかできないので
↓
c,SORT(b)
SORT関数で並び替え。
※1列データで昇順並び替えなら 第2、第3引数は不要
↓
JOIN(CHAR(10),c)
JOINで 改行を区切り文字として 結合し 文字列化
昇順・降順の並び替えは 文字列のままでは難しいので、配列に変換してから並び替えした後で文字列に戻すという手順を踏んでいます。
このように データ処理に際し、文字列 ⇔ 配列 を切り替えて 処理しやすい形式に変換することは結構多いです。
Q3. SORT関数で文字列を逆転させたい（逆文字にしたい）
これはQ2の応用です。他の方法もあるんですが、今回はSORT関数を使う方法で考えてみましょう。
こんにちは、今日は暑いですね。
↓
。ねすでい暑は日今、はちにんこ
このように 逆文字（後ろから文字を並び替える）としたい場合には、SORT関数を使ってどのような式を作ればよいでしょうか？
考えてみましょう！
↓↓↓回答
A3. SORT関数で文字列を逆転させる（逆文字にする）
回答です。
=CONCATENATE(SORT(MID(A1,SEQUENCE(LEN(A1)),1),SEQUENCE(LEN(A1)),0)
Q2のお題と同じく、並び替えする為にまずは文字列を配列化です。
残念ながらシート関数のSPLITは 区切り文字を指定しないと区切って配列化ができません。
というわけで LEN(A1)で 文字数を取得した上で、 MID関数をSEQUENCE関数と組み合わせて配列処理することで、文字列から順に1文字ずつ取り出して配列に変換しています。
MID(A1,SEQUENCE(LEN(A1)),1)
これを逆さにするんですが、その方法は前回のお題で登場しましたね。
配列なのでROW関数は使えませんが、前回のIMPORTRANGEで取得した配列の並び替えよりはシンプルで、先ほど MID関数と組み合わせたた SEQUENCE(LEN(A1)) がそのまま並び替え用のキー列に使えます。
これを降順で並び替えて 最後に 連結すればよいわけです。
並び替えの際に SORT関数に入れることで、MIDの処理に 配列処理効果が適用されるので Arrayformulaは不要となります。
区切り文字無しの 複数文字の連結は CONCATENATE関数を使います。
↓ 今回はSORT縛りでしたが、SORT使わず MIDで一文字ずつ取り出す際に 後ろから取得して連結って解法もあります。
でも SORTを削ったことで、配列効果を得る為に ARRAYFORMULAが必要になるんで、結局 式の長さは ほぼ変わらないです。
今回は SORT関数のお題ってことで、SORT関数を使った回答としましたが、どちらの方法でやっても良いですね。（そもそも 文字列を逆にしたいって機会はビジネス上はほぼないですが・・・）
【余談】Googleスプレッドシートの CONCATとCONCATENATE
以前にもこの話は書いてるんですが、再度書いておきましょう。
上で配列を連結して文字列化する際に使った CONCATENATEという関数。
Excelに慣れている人だと、なんでCONCATENATE？？ ってなるかもしれません。
なぜなら、Excelでは CONCATENATE関数は Excelのバージョンが2016以前で利用していた 旧関数であり、Excel2019以降のバージョンなら CONCAT関数で同様以上のことが出来るからです。
しかし、Googleスプレッドシートでは CONCAT関数は、2つの値（セル）を連結する関数という、よくわからない位置づけとなっています。
つまり
Excel
CONCATENATE → 上位互換 CONCAT
Googleスプレッドシート
CONCAT → 上位互換 CONCATENATE
とざっくり言うと立ち位置が逆になってるんですよね。。
Googeがなんでこんなことをしたのか、まったく理解できません。
今回は この話をしたかったんで、結合に CONCATENATE関数を使いましたが、Googleスプレッドシートの文字列化（連結）は、JOIN関数 や TEXTJOIN関数を使うのをおススメします。
単一の列（行）で 空白除去等を意識しなくていいなら、関数名の短いJOIN関数が良いですね。 区切り文字を空白にすれば CONCATENATEと同じ使い勝手となります。
ちなみに JOIN関数は Excelにはありません。（なくても不便は無さそうだけど）
Q4. SORT関数で セル内のちゃんとしてない文字列を並び替えて、ついでに綺麗にしたい
今回の最後のお題です。
A1 に生徒の点数が記載された文字列が入っているので、これを点数が高い順に左から並べ替えたい。
ただし以下の留意点があります。
・名前の後ろに点数が入っている 点数以外に半角数字は使われていない
・名前と点数の間は 半角スペースがあったり、全角スペースが入っていたり、スペースが無かったりと統一されていない
・点数の後ろには 「点」が入っていたり、入っていなかったりする
・上記の通り表記はバラバラだが、必ず 名前と点数がセットになっており、「、」で区切られている
・出力後のデータは 名前 (半角スペース) 点数 "点"、．．．と綺麗にして 先頭から点数の高い順としたい
要は A1セルの文字列を A3 のようにしたいってリクエストですが、ひどいデータなのに要望が細かいですねｗ
ぶっちゃけSORT関数以外の部分が難しいお題なんですが、とりあえず最終問題まずは自力で頑張ってみましょう！
↓↓↓回答
A4. SORT関数で セル内のちゃんとしてない文字列を並び替えて、ついでに綺麗にする
回答です。
=LET(x,REGEXREPLACE(A1,"[ ]*(\d+)点*","_$1"),y,SORT(WRAPROWS(SPLIT(x,"_、"),2),2,false),z,BYROW(y,LAMBDA(r,JOIN(" ",r)&"点")),JOIN("、",z))
これは LETで列挙している通り、
x ・・・ データの整理
y ・・・ 分解・配列化、並び替え
z ・・・ 行毎にデータを結合、文字列化
最後 ・・・ 全体を"、"で結合して文字列化
このような4段階の流れで処理をしています。
まず、データの表記のゆれを揃える処理として、正規表現を使った置換処理が出来る REGEXREPLACE関数 を利用しています。
REGEXREPLACE(A1,"[ ]*(\d+)点*","_$1")
検索 [ ]*(\d+)点*
[ ]* ・・・ 半角スペースまたは全角スペースの0回以上の繰り返し
つまり点数の前にスペースがあってもなくてもヒットする
→ これは置換後に削除
(\d+) ・・・ 数字の1回以上の繰り返し = 点数
→ これは置換後に使いたいので、カッコで括ることで
キャプチャグループ化している
点* ・・・ 点の0回以上の繰り返し
つまり点があってもなくてもヒットする
→ これも置換後に削除
置換後 _$1
$1が 点数の数字、その前に 後で分割に使う用の "_"を付与している
一発で綺麗なデータになりました。これをxと置きます。
x,REGEXREPLACE(A1,"[ ]*(\d+)点*","_$1")
この xを 点数で並べ替えできるように
このような 配列形式にしたいわけです。
"_"（アンダーバー）で横方向に区切り
"、" で 縦方向に区切りという処理なので
ExcelだったらTEXTSPLITで
=TEXTSPLIT(A1," _","、")
このように一発で処理出来るケースです。
しかし残念ながら Googleスプレッドシートの SPLIT関数では、縦区切りが出来ません。
これを代替する方法として上のTEXTSPLITの検証にも書いてますが、先に 縦区切りの "、"で SPLITしてから、TOCOL（またはTRANSPOSE）で縦1列に変換して再度 配列処理で "_"で SPLITという ダブルSPLIT方式
=ARRAYFORMULA(SPLIT(TOCOL(SPLIT(x,"、")),"_"))
こんな式で処理してもいいんですが、今回は データが2列の表に確実になることがわかってるので、ちょっと違う方法でやってみましょう。
SPLIT(x,"_、")
このように先に "_” または "、" で 全部区切ってから
WRAPROWS(SPLIT(x,"_、"),2)
WRAPROWSで 2列毎に折り返し とすることで 並べ替え用の表を生成しています。
WRAPROWSは Excelから輸入された配列操作の便利関数です。
これを SORT関数で 2列目をキーとして 降順で並べた表を y と置きます。
ExcelのSORTBYだと、ここで 2という指定は出来ないんですよね。この点はGoogleスプレドシートのSORT関数はいいなと思います。
y,SORT(WRAPROWS(SPLIT(x,"_、"),2),2,false)
この yで 点数の降順で並び替えた表を 行毎に
このようにまとめていきます。
行毎の処理ということで、ここでは BYROW関数を使っています。
BYROW(y,LAMBDA(r,JOIN(" ",r)&"点"))
行毎に 半角スペースで連結して後ろに "点"をつけています。
ちなみに &で連結する配列処理を使えば、新関数BYROW無しでも処理できます。
その方法を使う場合は、配列効果のある SORT関数内で処理しちゃいましょう。（Arrayformula つけなくてよくなるんで）
=LET(x,REGEXREPLACE(A1,"[ ]*(\d+)点*","_$1"),y,WRAPROWS(SPLIT(x,"_、"),2),z,SORT(INDEX(y,,1)&" "&INDEX(y,,2)&"点",INDEX(y,,2),false),z)
この行毎にまとめた結果を zと置きます。
z,BYROW(y,LAMBDA(r,JOIN(" ",r)&"点"))
最後に
JOIN("、",z)
として、行毎のデータを "、"区切りで連結し完成です。
最後は TEXTJOINでもいいんですが、JOINの方が短く記述できるので今回は TEXTJOINの出番は無しでした。
以上、文字列並び替え処理のSORT関数応用例 3つでした。
次回は SORTN関数 登場
SORT関数は今回で終了です。超応用例ってほどではなかったですかねｗ 並び替え がベースとなるので お題も偏ってしまったかも。面白い活用例を発見したら、また別の機会に紹介したいと思います。
さてSORT関数は以上となりますが、まだ SORTN関数が残っています。（いままでチラチラと登場してましたが） この SORTN関数とはいったいなんなのか？使える関数なのか？？
次回、SORT関数シリーズ番外編 SORTN関数編 でその価値に迫ります！
いいなと思ったら応援しよう！
