いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Gスプレッドシート)SORT関数とSORTN関数の比較

 Googleスプレッドシートでデータの並べ替えを行うための関数であるSORT関数とSORTN関数の機能についてです。

  • SORT関数とSORTN関数はともに、複数の並べ替え基準列を設定して並べ替えを行うことができます。
  • SORTN関数では抽出する行数を設定でき、さらに同順位のレコード(行)をどのように抽出するかについて3種類の設定ができます。

手順

機能比較

 2つの関数の引数と機能は次の通りです。
 並べ替え基準列を複数設けられる、という基本的な機能は同じですが、SORTN関数は第2引数と第3引数に特徴があり、第2引数で抽出行数を、第3引数で同順位のレコードに対する抽出方法を指定することができます。
 具体的なはたらきについては下記の実例をご覧ください。

関数名引数抽出行数指定同順位抽出モード
SORT(並べ替え範囲[,並べ替え基準列1,昇順降順,並べ替え基準列2,昇順降順……])

※並べ替え基準列には列番号(1,2,3...)を指定可
不可
SORTN(並べ替え範囲[,抽出行数,同順位抽出モード,並べ替え基準列1,昇順降順,並べ替え基準列2,昇順降順……])以下から選択可
(0)指定行数の範囲内で最下位レコード(行)を抽出
(1)指定行数を超えてでも最下位と同順位の全レコードを抽出
(2)各順位から1レコードずつ抽出

SORT関数

f:id:accs2014:20200105140712p:plain:right:w650

 画像は結果です。
 並べ替えの対象となるデータがB3:D10に記録されています。
 これをSORT関数により、C列の昇順(優先順位1)、さらにD列の降順(優先順位2)で並べ替えています。

 F3セルには次のように入力されています。

=SORT(B3:D10,C3:C10,TRUE,D3:D10,FALSE)

 SORT関数の第2引数は優先順位1の並べ替え基準列を、第3引数は昇順/降順(TRUE/FALSE)を表します。
 同様に第4引数は優先順位2の並べ替え基準列を、第5引数は昇順/降順(TRUE/FALSE)を表します。
 同じように3つ以上の並べ替え基準列を設けることも可能です。
 

f:id:accs2014:20200105140716p:plain:right:w650

 これも全く同じ内容の並べ替えを行った結果ですが、並べ替え基準列の指定に列番号(2と3)を用いています。
 このように、並べ替え基準列が並べ替え範囲に含まれる場合は、並べ替え基準列を番号(1,2,3……)で指定して並べ替えを行うこともできます。

 F3セルの式は次のようになっています。

=SORT(B3:D10,2,TRUE,3,FALSE)

 列番号を用いると並べ替え基準列の行数を気にしなくてよいことと、画面に表示されていないデータ(他の表から関数で導出した表など)も並べ替えの対象にできることがメリットです。

SORTN関数

f:id:accs2014:20200105140720p:plain:right:w650

 SORTN関数でもSORT関数と同等の並べ替えができ、さらに抽出する行数の設定と、同順位の行の抽出基準の選択ができます。
 まずはB3:D10のデータをC列(優先順位1)の昇順、さらにD列(優先順位2)の降順で並べ替え(ここまでは上記のSORT関数の例と同様)、さらに抽出する行数を6と指定した例です。

 F3には次のように入力されています。

=SORTN(B3:D10,6,0,C3:C10,TRUE,D3:D10,FALSE)

 第2引数(6)が抽出する行数です。大きい数(並べ替え範囲の行数以上)を指定するとすべての行が抽出されますが、それだとこの関数を使う意味があまりありません。
 第3引数は同順位の行の扱いを表すパラメータで、0は「最下位の行と同順位のものがいくつあっても、必ず指定した行数だけを抽出する」という意味です(実例は改めて後述します)。
 第4引数以降はSORT関数の第2引数以降と同じで、並べ替え基準列と昇順/降順を順次指定しています。もちろん3つ以上の並べ替え基準列を設けることも可能です。
 

f:id:accs2014:20200105140725p:plain:right:w650

 SORTN関数でも並べ替え基準列を列番号で指定することが可能です。
 上記と全く同じ並べ替えを、列番号を用いて行った結果です。
 
 F3セルの式は次のようになっています。

=SORTN(B3:D10,6,0,2,TRUE,3,FALSE)

同順位抽出モード

f:id:accs2014:20200105140728p:plain:right:w550

 さて、ここからはSORTN関数の第3引数のはたらきをみるためにデータを少し単純にしています(B3:C10)。
 そしてE3セルには次のように入力しています。

=SORTN(B3:C10,5,0,C3:C10,TRUE)

 第2引数により抽出する行数を5とし、第4引数と第5引数によりC列の昇順で並べ替えるものとしています。
 そして第3引数を「0」としています。これは「最下位の行と同順位のものがいくつあっても、指定した行数に収まる数だけ抽出する」という意味です。C列の小さい順から5番目の値は11で、該当する者が2人いますが、2人とも抽出してしまうと合計6人になってしまいます。そこで1人だけ抽出し(同順位の中では上方にあるものが抽出されます)、結果的に指定した行数が抽出されています。
 

f:id:accs2014:20200105140732p:plain:right:w550

 次に第3引数を1とした(それ以外全く同じ)結果です。

 念のため、式は次のようになってます。

=SORTN(B3:C10,5,1,C3:C10,TRUE)

 第3引数の「1」は「最下位の行と同順位になるものがあったら、それらもすべて抽出する」という意味です。C列の小さい順から5番目の値は11ですが、該当する者が2人いますので2人とも抽出されており、結果的に指定した行数を超える行数が抽出されています。
 

f:id:accs2014:20200105140738p:plain:right:w550

 そして最後に、第3引数を2とした(それ以外全く同じ)結果です。

 式は次のようになってます。

=SORTN(B3:C10,5,2,C3:C10,TRUE)

 第3引数の「2」は「同順位のものがいくつあっても、それぞれの順位から1つずつだけ抽出する」という意味です。C列の値を小さい順にみると8,9,10,10,11,11,12,12となっており、10,11,12に該当するものは2人ずついますが、それぞれから1人ずつ抽出され(同順位の中では上方にあるものが抽出されます)、結果的に指定した行数が抽出されています。