Googleスプレッドシート SORT関数 超応用例-2（応用例いろいろ）
SORT関数を取り上げた noteの続きです。
前回は Excelの SORT関数、SORTBY関数との比較、そして応用例を少しだけ紹介しました。
今回は 7つのお題にチャレンジしながら、SORT関数の超応用例を色々学んでいきましょう。
いまさら気づきましたが、正しい表記は
「並び替え」じゃなくて
「並べ替え」なんですね。。
Googleスプレッドシート の範囲と空白
お題に行く前に、少し「範囲と空白」に関する Excelとの違いについて触れておきましょう。
前回、ExcelのSORT関数、SORTBY関数の説明の際に テーブル機能にも少し触れました。
確かにExcelでは SORT関数・SORTBY関数は テーブル機能と相性が良いですし、残念ながら Googleスプレッドシートにはテーブル機能がありません。
ただ、Googleスプレッドシートは 範囲の取得方法と空白の扱いが Excelと違って非常に柔軟であるため、個人的にはテーブル機能は不要であると思っています。
範囲の取得、そして空白の扱いがどう違うのか？を見ていきましょう。
Googleスプレッドシートは、始点を決めて終点を決めずに範囲指定できる
Excel も今では A:Aといった 列を丸ごと 範囲指定・参照出来るようになっています。ここはGoogleスプレッドシートと一緒。
でも、残念ながら A2:A（A2から最後まで） や A5:A （A5 から最後まで）といった、始点だけ決めて終点を決めない範囲指定はできません。
A5:A（A列5行目から一番下まで） や B5:5（B5から右端まで）といった指定は、Googleスプレッドシートに慣れた人、もしくは Excelより先にGoogleスプレッドシートに触れた若い世代からすると当たり前に出来ることなので、Excelの方に違和感（使いにくさ）を感じます。
もちろんGoogleスプレッドシートにおいては、どこを始点としても 終点を決めない 範囲指定が可能です。
ただし、唯一 行方向と列方向の両方終点を決めない範囲指定は出来ません。どちらかは、お尻を決める必要があります。
Googleスプレッドシートは 行も列も好きに削除できる
終点を決めなかった時の 計算量にも違いがあります。 Excelは Web版もインストール版も
行は 1048576
列は XFD （16384）
と、とんでもないサイズです。
これは固定されており、途中の行削除や列削除をしても、勝手にまた生えてくるイメージで シートの行数、列数は 変わらない仕様となっています。
終点（お尻）を決めない計算は結構な無駄があるのがわかりますね。
一方で Googleスプレッドシートは 行、列を自由に削除して減らすことができます。
開始時は 行は 1000行、列は Z列まで（26列）となっていますが、増やしたり減らしたりが自由にできます。
初期値からいじらなければ 1000行なので お尻を決めなくても、そこまで計算量に影響はありません。
ちなみに スピル系の自動で展開される関数を使った際に行や列が足りなければ、 自動で 行数、列数は追加されます。（減らす方は自動はない）
必要になったら必要な分だけ追加するという、クラウド的設計ですね。
Googleスプレッドシートは 空白を数式で扱える
これも大きい違いです。
前回も触れましたが、Excelの 0化現象（空白は式を通すと 0になる）も Googleスプレッドシートに慣れると、非常に扱いにくい仕様です。
もちろん Googleスプレッドシートでは 空白は式を通しても空白のままです。
さらに、Excelでは不可能である 関数で空白を返すことができます。
=ARRAYFORMULA(IF(C2:C>=30,"〇",))
この式、C列が30以上なら 〇をつけるという式ですが、30より小さい時は 空白を返しています。
"〇",) ← ,と ) の間にはなにもない = つまり空白
この部分で判別式が TRUEの時には 〇を FALSEの時には空白を返すという処理をしています。
Excelでこんな式を使うと、結局 空白に対して 0化現象が発生するので、このような有様。。
だから Excelでは 空文字"" を返す書き方となります。
=IF(C2:C50>=30,"〇","")
空文字は 見た目上は 空白と一緒なのですが、関数では微妙に扱いが違っており、
このように 空白は SORT関数では 並び替えの対象外（昇順でも降順でも一番下）だったの対して、空文字は 文字として 並び順の対象になり 通常は他の文字より昇順だと上位表示となります。
Googleスプレッドシートであれば、空文字ではなく 空白を返す式にすれば並び替えの影響はうけませんし、意図的に並び替えの対象としたいのであれば、空文字を返す式を作ればよいです。
=ARRAYFORMULA(IFS(C2:C="",,C2:C>=30,"〇",true,""))
IFS関数で C列が空なら 空白として並び替え対象外とした上で、年齢30以上は 〇 をつけて、それ以外（30未満）のは 空文字とすることで、こちらは並び替え対象としています。
Excelと併用している人だと、なんの気なしに 空文字を使いがちですが、Googleスプレッドシートにおいては 空白を返した方がよいケースが多いです。
意識して使い分けできるようになりましょう。
というわけで、Googleスプレッドシートの方は、数式の依存度が高いので好みは分かれるかと思いますが、このように テーブル機能がなくても 始点だけ決めて終点を決めない範囲設定と 空白の利用で、増加するデータにも対応できるわけです。
それでは、本題の応用例いってみましょう。
SORT関数で逆順に並べ替えたい
データ内の列ではなく、単に今の表を下から上への並び順（逆順）で表示させたいってお題です。
Q1. SORT関数で逆順に表示させたい
このように左の表を 下から上へと逆順にデータを並べ替えたい場合、どうのような式をつくればよいでしょうか？
ただし、元データは 今後も下に増えていく予定があるので、並べ替え範囲を A2:Hとしたい。データの途中の行に空白セルはありません。
このような要件です。J2にはどのような 式を入れればしょうか？
これはあるあるネタですし簡単ですかね。考えてみましょう。
↓↓↓回答
A1. SORT関数で逆順に表示させる式
回答です。
=SORT(A2:H,IF(A2:A="",0,ROW(A2:H)),false)
なんとなく逆順で ROW関数を使うんだろうなというのは イメージできた方も多いんじゃないでしょうか？
ただ、そのまま =SORT(A2:H,ROW(A2:H),false) としてしまうと、上にデータのない空白行がズラっと表示されてしまいます。
そこで、IF文で A列にデータがある時だけ ROW関数で行番号を返し、A列が空白だったら 0とした 1列の条件配列を生成した上で、それを降順 false 並び替えとしています。
もう少し短く
=SORT(A2:H,ROW(A2:H)*(A2:A<>""),0)
このように書くとよりクールですね。
A2:A<>"" の箇所は
A列に値がある → TRUE すなわり ×1
A列が空白である → FALSE すなわち ×0
とIF文の代わりを式で代替しています。
また、昇順・降順を指定する TRUE、FALSEも
昇順 TRUE ・・・ 1 としてもよい
降順 FALSE ・・・ 0 としてもよい
このように短く記述できます。
もちろん関数なのでリアルタイム更新。左側の元データの一番下に新しいデータが追加されたら、自動で右側には一番上に新しいデータが表示されます。
Q2. 配列（IMPORTRANGEで取得した）データの逆順 並べ替えの場合は？
では、逆順並べ替えをしたい データが 別のスプレッドシートから IMPORTRANGEで取得したもの。つまり配列だった場合はどうすればよいでしょうか？
上のように一度出力すれば簡単なんですが、あくまでも 直接一つの式で Q1と同じことをやりたいってお題です。
では、応用編考えてみましょう！
↓↓↓回答
A2. SORT関数で逆順に表示させる式（importrange 配列データとの組みあわせ）
回答です。
=LET(x,IMPORTRANGE("スプレッドシートID","元データ!A2:H"),SORT(x,SEQUENCE(ROWS(x))*(INDEX(x,,1)<>""),0))
とりあえず 複数回登場する importrangeで取得したデータは LET関数で 変数（ｘ）に格納します。
これによって記述を短く簡潔に出来ますし、重い処理のIMPORTRANGEを複数回走らせずに済む為、処理の負荷軽減にもなっているかと。
ポイントは
SEQUENCE(ROWS(x))*(INDEX(x,,1)<>"")
ですが、これは Q1の別解 ROW(A2:H)*(A2:A<>"") と意味合いは同じです。
配列なので ROW関数が利用できない部分を
SEQUENCE(ROWS(x))
で仮装連番を振って代替しています。
通常は 別スプレッドシートのデータの並び替えは QUERY関数を使うことが多いですし、今回のケースも QUERY関数で書けなくもないですが
=LET(x,IMPORTRANGE("1QrUmK65Wb_N0NRyTc75DilVaYfL9QS5EZJFXOAJf7W8","元データ!A2:H"),QUERY({SEQUENCE(ROWS(x)),x},"select Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9 where Col2 is not null order by Col1 desc"))
このように、連番と対象データを一度横に結合してから QUERY関数の SELECT句で Col1以外を列挙するという、非常に長い式になってしまいます。（Col2・・・・Col9 の箇所は数式で生成可能）
LET関数が使えるようになったので、逆順並べ替えは SORT関数の方が簡潔に記述できるようになりました。
SORT関数でランダムに並べ替えたい
続いて、SORT関数を使って ランダムに並べ替えたいというお題です。
ちなみに元データをランダム化してもよければ、範囲をランダム化という機能を使うのが簡単です。
Q3.SORT関数でランダムに並べ替えたい
今回は 元データの並びは変えず、関数を使って先ほどと同じように右に ランダムに並び替えたデータを出力したいって要件です。
同じくデータ追加に対応できるようには 範囲は A2:H とします。
これもあるあるネタですね、空白部分の処理は Q1を参考にすれば簡単じゃないでしょうか？やってみましょう！
↓↓↓回答
A3. SORT関数でランダムに並べ替える式
回答です。
=SORT(A2:H,RANDARRAY(ROWS(A2:H))*(A2:A<>""),0)
RANDARRAY関数を組み合わせることで、範囲をランダムに並べ替えて出力することができます。
RANDARRAY（行数, 列数）
RANDARRAYは 指定したサイズの 0以上1以下の乱数配列を生成する関数です。
ROWS(A2:H) は範囲であるA2:Hの行数（高さ）の数値を返すので、これをRANDARRAYと組み合わせることで、A2:Hと同じ行数の1列の乱数配列が生成されます。
あとは逆順処理と同じく A列が空白の時は 0として 乱数配列を条件に降順とすれば、値があるデータだけのランダムな 並び替え完成です。
Q4. SORT関数でランダムに並べ替えた データを固定したい
先ほどの回答でデータをランダムに並び替えは出来ました。ただ、このようにシート操作（編集）する度に、コロコロ並びが変わってしまうという困った現象がおきます。
乱数系関数は、シート内でセルの編集があったり、スプレッドシー値を開きなおすたびに 再計算されるという特徴がある為、このように並びが固定されず 変わってしまうのです。
今の 並びをもとにシート内で 作業をしたい時は、ちょっと困りますね。
「ランダムな並び替えの結果を固定化したい」
こういう要望があると、一般的には GASを使え、もしくはコピーして値貼付けしろという回答になりますが、実はシート関数を使って固定化が出来ます。
これをお題にしてみましょう。
このように J1にチェックボックスを配置し、このチェックボックスで データ のランダムな並び替えを制御したい。
チェックボックスがOFFの時は 空白を返し、チェックボックスを ONにする（チェックをつける）と、ランダムに並び替えられた A2:Hのデータが出力される。一度出力された結果は、チェックを再び外してチェックするまでは 並びは変わらない。
どうでしょう、GASを使わずに出来そうでしょうか？
考えてみましょう。
↓↓↓回答
A4. SORT関数でランダムに並べ替えたデータを固定する
回答です。
mirの noteではお馴染みの 循環参照、反復計算ONを使うテクニックになります。
まずは メニューから
ファイル ＞ 設定 ＞ 計算タブ
を開きます。
反復計算を オンにして、一応 最大反復回数を 1としておきましょう。
これで設定を保存で下準備はOK。
J2セルの式は
=IF(NOT(J1),,IF(J2>0,{J2:Q},SORT(A2:H,RANDARRAY(ROWS(A2:H))*(A2:A<>""),0)))
このように変更し、J2に入れる式で J2自身を参照させます。
処理の中身としては
J1のチェックボックスがオフである？ → TRUE → 空白
↓ FALSE
J2>0 J2セルに計算結果の値が入っている？ → TRUE →{J2:Q} そのまま
↓ FALSE
SORT(A2:H,RANDARRAY(ROWS(A2:H))*(A2:A<>""),0)
このような分岐をしています。
このネタ、実はチェックボックスの番外編で登場した 乱数固定の応用です。
一点だけ注意点があり、このランダムな並びは 実はサーバー側のデータと見えているクライアント側でズレがあります。
その為、セル編集では再計算はされず並びは変わりませんが、リロードするとサーバー側の並びに置き換わってしまいます。
サーバー側とズレがあるということは、同時に他の人が 開いていた場合、他の人の開いている画面での並び順ともズレているということです。
チェック後に 一度開いている人が全員 F5更新をして、サーバー側の結果を表示させれば、あとはチェックボックスを外さない限りは全員が同じ並びで固定化されます。
シートを共有せず 自分だけの利用で、かつ 開いている間だけ表示が固定されていればよいなら問題ありません。
もしシートを複数名や複数端末で共有していたり、閉じた後も並びを保持したい場合は
チェック → ランダムな並び順で固定 → Ctrl+ F5で 画面更新
→ 真のランダムな並び順で固定
と 表示を 先に 更新してから のデータを正として 利用しましょう。
このサーバー側と見えているクライアント側のズレは、最近のGASネタでも登場した、知らないとハマるポイントです。
SORT関数で指定した特殊な順番で並び替えをしたい
目視、手作業で作業をしているデータだと、ロジックが無いというか、その作業している人の知識や経験を基準とした、言語化できていない（他の人に引き継げない）作業があったりします。
本当にそんな会社があるのかわかりませんが、リモート会議において ZOOMの上座（右上？）には偉い人がくるようにするとか、メールの ToやCCに入れる宛先の並び順とか、謎マナー・謎ルールは本当にやめて欲しいです。
そこまではいかなくても、よくあるのがデータを 役職で偉い人が上にくるように並び替えたいって要望です。
Q5. SORT関数で 役職順（偉い人順）にデータを並び替えたい
A2:Cに A列の社員番号順で並べられた社員データがあります。
これをC列の役職順（偉い人が上にくる）で並び替えたいってお題です。
役職でどっちが偉いかは、見りゃわかるだろという人もいるかと思いますが、スプレッドシート（コンピュータ）からはもちろんわかりません。
役職コードみたいなものがあればよいんですが、今回は E2:E10に 上から偉い順で役職名を並べたリストに基づいて並び替えすることにしてみましょう。
右側の 偉い人順に並び替えたデータを出力する為には、SORT関数をどう活用すればよいでしょうか？
考えてみましょう！
↓↓↓回答
A5. SORT関数で指定したリスト順（偉い人順）に並び替える式
回答です。
=SORT(A2:C,XMATCH(C2:C,E2:E10),true)
XMATCH関数で C列 の役職が E2:E10の役職リストの何番目にあるかを取得した配列データをキーとして 並び替えをしています。
このように、偉い人ほど リスト内では上にある為、小さい数字が返るのがわかります。これを昇順で並べることで、偉い人が上にくるリストが生成できるわけです。
なお、空白やリストに無い役職（上の場合は 係長）があると、 #N/Aが返りこれは 昇順の場合は数値よりも下に回されます。
xmatchで見つからなかった時の処理は、特に気にしなくて良いってことですね。
偉い人順に限らず、指定したリストの順番で並べる際は、 XMATCH（MATCH）が活用できることが分かりましたね。
今回のケースはもちろん MATCH関数でもいいんですが、Excelと違って 全員がXMATCHが使えるGoogleスプレッドシートにおいては、MATCH関数は完全に必要ないです。全てXMATCHを使うでOK。
XLOOKUPの場合は、縦横スピルできるVLOOKUPを使ったほうがいいケースや、短い記述で書ける LOOKUPを使うケースもありますが、XMATCHよりMATCHを使った方がいいケースはありません！
XMATCHに関しては 超応用例を過去に取り上げています。
文字列となっている数字を数値化して並べ替える
これもあるあるネタなんで、簡単なんですが入れておきましょう。
Q6. 月で並び替えたいんですが、なんだかうまくいきません
よくない質問例の代表 「なんだかうまくいきません」ですｗ
でも、このケースはだいたい想像がつきます。つまり
こういうことですね。 月の表記が文字列になっている為、
並べ替えた時に 10月 や11月、12月が 2月や3月より 上にきているってケースです。
もちろん、月の部分を2桁月に書き換える、または中身は日付で表示形式で〇〇月とすれば良いって話ですが、元データはどうしてもいじれないという場合、どう対応すれば良いでしょうか？並べ替え範囲は A2:Cとします。
やってみましょう！
↓↓↓回答
A6. 文字列化している 数値で並び替える
回答です。
=SORT(A2:C,SPLIT(C2:C,"月"),1)
もちろん SUBSTITUTEや REGEXEXREPLACEなどを使って月を空白に置換する方法もありますが、 これだと空白セルが 0化して空白行が上位にきてしまいます。
他には REGEXEXTRACTで 数字部分だけ取り出す方法もあります、こちらは 取り出しただけでは文字列状態なので、*1 をする等で 数値化処理が必要。
=SORT(A2:C,REGEXEXTRACT(C2:C,"\d+")*1,1)
比べて SPLITによる数値化は 記述がスマートじゃないでしょうか？SPLITの場合は分割した際に 数字のみだと自動的に数値化されます。
先頭、または末尾に単位等のテキストが付属した文字列化した数字を数値化するなら SPLIT関数が簡単ですね。覚えておいて損はないです！
SORT関数で並べ替えたデータから 参照元データの行に飛びたい
SORT関数の並べ替えは 大変便利なのですが、関数で出力したデータなので当然 編集は出来ません。
でも、SORT関数で並べ替えたデータで修正箇所を見つけても、
これ 元データのどこにあるの？
って探すのが面倒だったりします。
これもありそうな要望ですね。今回のラストのお題いってみましょう。
Q7. SORT関数で 並び替えたデータから 元データ（別シート）の並び替え前の行にジャンプしたい
やりたいことは、同じスプレッドシート内の 元シート というシート名の A2:Hのデータを C列（年齢）の数値を降順で並び替えしたデータを別シートに出力したい。
ただし、データの一番左に 元データの何行目のデータかが わかるように 行番号の数値列を追加して、さらに ハイパーリンクでそのデータの行にクリックで飛べるようにしたい。（とりあえず、そのデータ（行）のA列に飛べればよい）
こんなお題です。
少し面倒ですが、基本の関数を組み合わせていけば出来るお題です。挑戦してみましょう！
↓↓↓回答
A7. 元データに飛べる！リンク付き 並べ替えをするSORT式
回答です。
=SORT({IF('元シート'!A2:A<>"",HYPERLINK("#gid=414848323&range=A"&ROW('元シート'!A2:A),ROW('元シート'!A2:A)),),'元シート'!A2:H},4,false)
ちょっとごちゃごちゃした式ですが、そこまで難解ではないですね。
リンク付き 行番号の 1列の配列を
IF('元シート'!A2:A<>"",
HYPERLINK("#gid=414848323&range=A"&ROW('元シート'!A2:A),ROW('元シート'!A2:A)),)
この部分で生成しています。
リンクラベルは 行番号なんで、ROW('元シート'!A2:A) でよいですね。
同じ スプレッドシート内での特定のシート、特定のセルへのリンクの場合は https://docs.google.com/spreadsheets/・・・ とフルアドレスを書く必要はなく、
"#gid=【シートID】&range=【セル番地 A1表記】"
これで記述できます。同じシート内でも シートIDは指定が必要。
セルへのリンクは、以前 シート名、シートIDを返す GAS関数を作成する回で触れました。
今回はリンク先は 行だけ可変で 列は A列でよいので、Aだけ固定で 行番号だけ ROW関数を使っています。
ピンポイントで指定のセルへ飛ばす場合は、ROWとCOLUMNで行番号、列番号を取得して ADDRESS関数に入れて A1表記を出力って流れで少し手間がかかります。
あとは 中カッコで 横に連結して、1列左に追加したんで 元データの C列（年齢）を条件とする時は 1つズレて 列指定で 4 としてあげる点を注意すればOK。
超応用例という割には簡単だったでしょうか？
【余談】同じスプレッドシート内でのリンクを別タブで開きたい（Alt + Enterが一気に開けて便利）
同じスプレッドシート内でのリンクは、基本的には 今開いているタブ内で画面が遷移する開き方（挙動）となります。
もちろん、この動きを望んでいるケースの方が多いのですが、今回の場合は 画面が切り替わって修正だと、元シートを修正してまた SORTシートに戻ってと動きが煩雑です。
では同じスプレッドシート内へ飛ぶリンクを、別タブで開くにはどうすればよいか？
残念ながら Ctrl押しながらのクリックは効きません。
これには2つ方法があります。
1つはマウスをあてると表示されるリンクを 右クリックして、 新しいタブで開くを選択する方法です。
もう1つはスプレッドシートで ハイパーリンクを別タブで開く ショートカット
Alt + Enter
を使う方法です。
Alt+Enter の強力なところは、一気に複数のセルのリンクを開くことができる点。
たとえば、このように修正チェック列を追加し、修正必要なデータの行だけフィルタで絞り込んでおけば、可視セルのリンクだけ選択して Alt ＋ Enterで一気に開くことが出来ます。
あとは それぞれのタブを確認し、選択されている行のデータを修正して チェックを外せば完了です。効率的ですね。
小ネタを紹介させていただきました。
まだある！ SORT関数の応用例
今回は超応用といえる難易度かは微妙でしたが、並べ替えにおいてあるあるな 7つのお題を解きながら SORT関数の応用例を学びました。
でも、今回のケースはいずれも行の並び替えで SORT関数を使う、いわばオーソドックスな応用例です。
実はSORT関数には 単なる行ごとの並び替えとは違った使い方もあります。
次回 超応用例3では、SORT関数の意外な使い方にチャレンジしてみましょう。
いいなと思ったら応援しよう！
