Excel。範囲または配列に対応する範囲または配列の値に基づいて並べ替るのがSORTBY関数【sortby】

Excel。範囲または配列に対応する範囲または配列の値に基づいて並べ替るのがSORTBY関数

＜関数辞典：SORTBY関数＞

SORTBY関数

読み方： ソートバイ　　

分類： 検索/行列 

SORTBY(配列,基準配列1,[並べ替え順序1],…)

範囲または配列を、対応する範囲または配列の値に基づいて並べ替えます　

Excel。重複なしのランダム数値を1つの数式だけで作る方法【rand】

Excel。重複なしのランダム数値を1つの数式だけで作る方法

＜SORTBY関数+SEQUENCE関数+RANDARRAY関数＞

重複なしのランダム数値を、RAND関数とRANK.EQ関数を組み合わせた方法を、以前紹介しました。

今回は、もっと簡単に、数式1つだけで、重複梨のランダム数値を求めてみましょう。

使う関数は、SORTBY関数とSEQUENCE関数とRANDARRAY関数だけで求めることができます。

A1に数式を設定します。

=SORTBY(SEQUENCE(9), RANDARRAY(9))

確定するだけで、1～9までの数値が重複することなく、ランダムで求めることができました。

では、数式を確認します。

先に、引数から確認しておきましょう。

SEQUENCE(9)は、1から9までを連番で求めることができます。

なので100までの数値をランダムにしたい場合には、SEQUENCE(100)とします。

RANDARRAY(9)は、ランダムの値（小数）を9個求めることができます。

C1には、

=SEQUENCE(9)

で、連番が、

D1には、

=RANDARRAY(9)

で、ランダムの数値を求めています。

この2つをつかって、SORTBY関数で並べ替えをします。

SORTBY関数ではなくて、SORT関数だとエラーが表示されてしまうので、注意が必要です。

SORT関数の最初の引数は、列番号しか受け取れないのでエラーがでます。

SORT関数は、数字（1列目）で、SORTBY関数は、範囲（A1:A10）を設定します。

そのため、SORTBY関数をつかいます。

SORTBY関数の最初の引数は、「範囲」。ここにSEQUENCE関数で1から9の値を作ります。

2つ目の引数は、「基準配列1」。

並べ替えの基準になる範囲です。

RANDARRAY関数で求めた数値を昇順で並べ替えています。

これで、ランダムの数値を重複することなく作ることができるというわけです。

【Excel時短】元データはそのまま！複数条件で「自動並べ替え別表」を作る革新関数【Multi-column】

【Excel時短】元データはそのまま！複数条件で「自動並べ替え別表」を作る革新関数

＜SORTBY関数＞

次の店舗別の販売金額一覧表があります。

この表を地域別でかつ、販売金額を降順で並べ替えをしたい場合、どのようにしたいいのでしょうか。

そして、単純に並べ替えをするのではなく、この表はそのままで、別表で並べ替えた表を用意したい。

このような場合、A1:D8のデータを別のところにコピーして、コピーした表を使って、データタブにある並べ替えをおこなうというのが、普通だと思います。

ただ、作業としては簡単でも、作業数が多くて、面倒です。

そこで、SORTBY関数をつかうと、手早く、地域別でかつ、販売金額を降順で並べ替えをした表をつくることができます。

F2に次の数式を設定します。

=SORTBY(A2:D8,C2:C8,1,D2:D8,-1)

これで、地域ごとの販売金額降順で別表をつくることができました。

SORTBY関数は、直接セル範囲を指定して並べ替えを行うことができます。

では、このSORTBY関数を確認しておきましょう。

最初の引数は、配列。

範囲選択なので、A2:D8を設定します。

2番目の引数は、基準1。

最初の並べ替えをしたい条件列なので、C2:C8。

3番目の引数は、2番目の引数で設定した基準１。

これを昇順で並べ替えをしたいので、「1」を設定します。

昇順が「1」で降順が「-1」です。

あとは、繰り返して設定するだけです。

これで、元表は残ったままで、複数列を基準にした別表をつくることができます。

なお、並べ替えをするSORT関数というのもありますが、単純な並べ替えだけです。

複数列を基準にする場合には、入れ子にしなければならないので、SORTBY関数のほうが可読性が高いです。

Excel。ダブらないで1から10までをランダムで並べるにはどうしたらいい

＜SORTBY+SEQUENCE+RANDARRAY関数＞

参加者名簿があります。

C列の順番には、1から参加者数の10人分である10までの数値をランダムで並べたい。

しかも、1が2回登場するようなことがないように、つまり重複しないようにしたいわけです。

今回は10名なので、自力で振り分けることもできますが、件数が多くなれば、とても大変な処理になってしまいます。

何かいい方法は、ないのでしょうか。

そこで、C2に次の数式を設定してみます。

=SORTBY(SEQUENCE(10),RANDARRAY(10))

この数式で、１から10までの数値をランダムで並べ替えることができました。

使用している関数は、スピル機能対応なので、絶対参照や、オートフィルで数式をコピーする必要はありません。

この数式がどのように動くことで、1から10までの数値をランダムで並べることができるのか、関数を確認していきます。

SORTBY関数は、並べ替えをする関数です。

最初の引数は、「配列」。

2つ目の引数が「基準配列1」なのですが、それぞれ関数をつかっていますので、最初の引数で使用している

最初の引数を使用している関数は、SEQUENCE関数です。連番を作る関数です。

SEQUENCE(10)　で、1から10までの連番を設定することができます

まず、重複しない1から10の数値を用意することができました。

この数値をランダムで並べ替える必要があるわけです。

そこで、RANDARRAY関数をつかって、乱数を発生させます。

小数の乱数を発生するので、算出結果が重複することは、まずありません。

この2つの関数をつかって、1から10までをランダムで並べ替えた順番をつくることができたというわけです。

なお、セルをアクティブなどするたびに、再計算されます。

説明の都合で、C列の順番を作成した後に、F列をつくっています。

そのため、F列の結果とC列の結果は合致しませんので、ご了承ください。

Excel。列を並べ替えた別表を、手早く作るにはどうしたらいい【SORT】

Excel。列を並べ替えた別表を、手早く作るにはどうしたらいい

＜SORTBY+XMATCH関数＞

得点表があります。

A1:G7の表は、

「英語・国語・数学・理科・社会」という順番になっています。

これを、「数学・理科・英語・国語・社会」という順番で並べ替えをしたい。

自力で、コピペを繰り返して列を並べ替えるのは、とても面倒です。

何か、効率の良い方法で、手早くつくることはできないものでしょうか。

そこで、SORTBY関数とXMATCH関数を組み合わせることで、列を並べ替えた別表をつくることができます。

I2をクリックします。

次の数式を設定します。

=SORTBY(A2:G7,XMATCH(A1:G1,I1:O1))

数式を確定して、並び順を確認してみると、きちんと列方法で並べ替えされていることが確認できます。

この数式をつかうには、事前に、並べ替えをしたい順の見出しを用意する必要があります。

さて、この数式を確認しておきましょう。

SORTBY関数は、並べ替えを行う関数です。

最初の引数は、「配列」。

範囲なので、A2:G7

SORTBY関数は、スピル機能対応の関数なので、絶対参照は不要です。

次の引数は、基準1。

並べ替えをおこなうルールのことです。

ここにXMATCH関数をつかいます。

そのXMATCH関数は、MATCH関数の上位版の関数です。

MATCH関数と同じで、指定したデータが何番目にあるかを求めることができます。

XMATCH関数の最初の引数は、検索値です。

検索値は、A1:G1。

次の引数は、検索範囲で、I1:O1

XMATCH関数の結果は、

{1,2,5,6,3,4,7}

この順番で列を並べ替えることができたというわけです。

Excel。複数列を基準にして、手早く並べ替えた別の表をつくりたい。【SORT】

Excel。複数列を基準にして、手早く並べ替えた別の表をつくりたい。

＜SORTBY関数＞

複数列をつかった条件で並べ替えをしました。

その後、別の表へコピーする場合、作業的には、難しくはありませんが、面倒です。

そこで、SORTBY関数をつかうと、手早く別の表で並べ替えた状態で表示することができます。

次の表を用意しました。

処理したいことは、A1:D7の表を、クラス順の得点は降順に並べ替えた状態で別の表を作りたいわけです。

複数列の並べ替えなので、データタブの並べ替えをつかい、その後コピーすればいいわけです。

ただ、SORTBY関数をつかうと、並べ替えの作業も、コピーも、まとめて処理してくれます。

それでは、F1に次の数式を設定します。

=SORTBY(A2:D7,C2:C7,1,D2:D7,-1)

たった、この数式だけで、対応することができます。

オートフィルで数式をコピーする必要はありません。

スピル機能により、数式がコピーされます。

SORTBY関数の最初の引数は、「配列」です。

範囲なので、見出し行を除いた、「A2:D7」を設定します。

スピル機能のため、絶対参照は不要です。

2つ目の引数は、「基準配列1」です。

これは、最初の条件の並べ替え範囲のことを指しています。

クラス順にしたいので、「C2:C7」。絶対参照は不要です。

3つ目の引数は、「並べ替え順序1」です。

これは、「1」なら昇順。

「-1」なら降順の設定をおこないます。

クラス順なので、昇順ですから「1」と設定します。

2つ目と3つ目の引数を条件数分繰り返すことで、複数列で並べ替えを設定することができます。

なお、SORT関数という並べ替えをする関数もあります。

SORT関数でも複数列での並べ替えは、可能ですが、SORT関数は、一度に複数列での並べ替えができないので、SORT+SORT関数のネストにしなければ対応することができません。

Excel。最新関数で、手早く重複なく整数をランダムで表示したい。【integer】

Excel。最新関数で、手早く重複なく整数をランダムで表示したい。

＜SORTBY+SEQUENCE+RANDARRAY関数＞

整数の数値をランダムで表示したい。

しかも、重複しないようにしたい場合、Microsoft365やExcel2021で登場した3つの関数を使うことで、手早く算出することができます。

今回は、1～10までの整数とします。

A1に次の数式を設定します。

=SORTBY(SEQUENCE(10),RANDARRAY(10))

これで、重複しないで1～10の数値をランダムで表示することができます。

今までは、RAND関数で一度、ランダムの数値を算出して、その数値の順位を算出する。

その後、RANK.EQ関数をつかって、重複しないランダムの数値をつくっていました。

手間が結構かかったわけですね。

ところが、一つの数式で、算出できるようになったというわけです。

SORTBY関数とSEQUENCE関数とRANDARRAY関数で、算出することができるようになりました。

では、

=SORTBY(SEQUENCE(10),RANDARRAY(10))

の仕組みを確認しておきましょう。

SORTBY関数は、範囲内にある値を並べ替えする関数です。

最初の引数は、「配列」です。SEQUENCE関数をつかって、1～10の値をつくります。

SEQUENCE(10)とすることで、行ごとに1から10の数値を算出してくれます。

これで、1～10の範囲を得ることができます。

2つ目の引数は、「基準配列1」です。並べ替えを行うための基準です。

ここに、RANDARRAY関数をつかって、ランダムの数値をつくり、基準とします。

SORTBY関数とSEQUENCE関数とRANDARRAY関数は、スピル機能に対応した関数なので、オートフィルで数式をコピーする必要はありません。

そのため、A1に数式を設定するだけで済みます。

今回紹介した、SORTBY関数とSEQUENCE関数とRANDARRAY関数をはじめとして、新しい関数が色々追加されています。

確認してみると、可読性の高い数式に修正することができるかもしれませんね。

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、SLN関数～SORTBY関数です。

＜Excel関数辞典：VOL.73＞

今回は、SLN関数～SORTBY関数までをご紹介しております。

SLN関数

読み方： エスエルエヌ

読み方： ストレートライン

分類： 財務 

SLN(取得価額,残存価額,耐用年数)

減価償却費を定額表で算出します　

Straight LiNe depreciationの略

SLOPE関数

読み方： スロープ　　

分類： 統計 

SLOPE(既知のy,既知のx)

回帰直線の傾きを算出する　

SMALL関数

読み方： スモール　　

分類： 統計 

SMALL(配列,順位)

指定した○番目に小さい値を算出します　

SORT関数

読み方： ソート　　

分類： 検索/行列 

SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

範囲または配列を並べ替えます　

SORTBY関数

読み方： ソートバイ　　

分類： 検索/行列 

SORTBY(配列,基準配列1,[並べ替え順序1],…)

範囲または配列を、対応する範囲または配列の値に基づいて並べ替えます　

Excel。範囲外の列を基準として並べ替えることができるのがSORTBY関数です。【SORT】

Excel。範囲外の列を基準として並べ替えることができるのがSORTBY関数です。

＜SORTBY関数＞

並べ替えをするには、当然、その範囲が含まれていなければ、並べ替えをすることはできません。

ところが、SORTBY関数をつかうと、次のようなテーブルから必要な列だけを抽出しつつ、その範囲に含まれない列を基準として並べ替えることができます。

A1:F11のテーブルには、「セット売上表」というテーブル名を設定してあります。

今回は、H列からJ列に店舗名・商品名・売上高をセット売上表テーブルから抽出してあります。

そして、並び順は、この店舗名・商品名・売上高の列に含まれていない、「来店客数」の降順で並べ替えをおこなった表にしたいわけです。

このようなことができるのが、「SORTBY関数」です。

そして、スピル機能があることで、H2にSORTBY関数をつかった数式をつくることで、一発で、算出してくれます。

H2に次の数式を設定します。

=SORTBY(セット売上表[[店舗名]:[売上高]],セット売上表[来店客数],-1)

たったこれだけで、抽出した表を、さらに並べ替えもできちゃうんです。

最初の引数「配列」には、テーブル名や範囲選択をするのですが、今回は、3列だけなので、「セット売上表[[店舗名]:[売上高]」と設定します。

2つ目の引数「基準配列1」には、来店客数で並べ替えを行いたいので、「セット売上表[来店客数]」と設定します。

最後の引数「並べ替え順序1」には、降順なので「-1」を設定します。

スピル機能と組み合わせると、効果抜群の関数がありますので、色々試してみると、新しい発見があるかもしれませんね。

Excel。フィールド（列）を並べ替えた表を手早く別シートにつくりたい。【SORT】

Excel。フィールド（列）を並べ替えた表を手早く別シートにつくりたい。

＜SORTBY関数＞

並べ替えをおこなうと、行方向で並べ替えをおこなうだけではなく、並べ替えオプションをつかうことで、列方向での並べ替えができます。

今回は、列方向に並べ替えをしたデータを、別シートにコピーなどして作りたいわけです。

しかも、手早く。

今までならば、並べ替えオプションをつかうしか方法がなかったのですが、最近のExcelに追加された関数に、SORTBY関数というのがあります。

この関数、SORTとつくことから、わかるように、並べ替えを行う関数なのですが、レコード（行）方向を対象にしたデータの並べ替えだけでなく、フィールド（列）方向も対象として並べ替えをすることができます。

関数なので、コピーをしなくても、直接別シートに作ることも出来ます。

次の表を用意しました。

この表を基にして、合計の数値を列方向に降順の表をつくってみましょう。

最初に、A列の見出し列を別シートにコピーします。

B1にSORTBY関数をつかった、数式を設定します。

=SORTBY(元データ!B1:E5,元データ!B5:E5,-1)

合計値を降順とした、表を別シートに作ることができました。

あとは、見出し行を中央揃えにして、セルに色を設定すれば完成です。

また、数式は、スピル機能によって、コピーされますので、Ctrl+Shift+Enterの配列関数の処理は不要です。

SORTBY関数の引数を確認しておきましょう。

最初の引数は、「配列」。

対象の範囲のことなので、B1:E5となります。

次の引数は、「基準配列1」。

並べ替えの基準となる範囲のことです。

今回は、合計行の数値で判断するので、B5:E5です。

3つ目の引数は、「並べ替え順序1」。

昇順か降順かの設定をするところです。

昇順なら1。

降順なら-1。

を設定するだけです。

複数条件の場合は、「基準配列2」「並べ替え順序2」を引き続き設定します。

このSORTBY関数をはじめ、最近新しい関数が色々追加されています。

今まで苦労していたものが、新しい関数をつかうことで、作業効率が改善できることを発見できるかもしれませんので、調べてみるのもいいかもしれませんね。

Excel。SORT関数は、列の並び替えもできるのです！複数条件ならSORTBY関数の登場です。【Sort】

Excel。SORT関数は、列の並び替えもできるのです！複数条件ならSORTBY関数の登場です。

＜SORT関数・SORTBY関数＞

Office365 InsiderのExcelに新しく加わった【SORT関数】というのがあります。

Office365 Insiderなので、Excel2016などには、ありませんが、ちょっとした時に、便利な関数なので、ご紹介していきます。

SORT関数ということからわかるように、並び替えを行うことができる関数です。

まずは、動きを確認しておきましょう。
次の表があります。

注意点というか、通常の並び替えと異なり、関数、つまり数式なので、現在のデータをそのまま並び替えるわけではありません。

あと、見出し行は除いて範囲選択する必要があります。

見出し行を含めると当然データ行とミックスされてしまいます。

SORT関数は、
SORT(配列,並び替えインデックス,並び替え順序,並び替え基準)
という引数を持っています。

なので、SORT関数は、ダイアログボックスを表示するよりも、手入力するほうがわかりやすいので、手入力で設定するといいように思います。

今回は、売上高を降順で並び替えを行うことにします。

E2にSORT関数を設定していきます。

=SORT(A2:C6,3,-1,FALSE)
と設定したら、Office365 InsiderのExcelの新機能である「スピル」によって、オートフィルを使うことなく、必要な範囲に数式が反映されます。

では、数式を確認していきましょう。

=SORT(A2:C6,3,-1,FALSE)

配列は、A2:C6。見出し行を除いて、データ行を選択します。仮に最終行が合計行だった場合は、合計行は除きます。

並び替えインデックスは、3。
配列で選択した範囲の左から何列目を基準として並び替えを行うのかを設定します。

並び替えの順序には、「-1」。「1」が昇順で「-1」が降順です。
今回は、降順で並び替えを行うので、「-1」。

並び替え基準には、「FALSE」を設定します。
「FALSE」は行を対象にした並び替えを行います。
「TRUE」は列を対象にした並び替えを行います。

このように、並び替えを行うことができる関数が登場しました。

そして、このSORT関数は、行だけでなく列の並び替えも簡単に行うことができます。

次の表を用意しました。

【列の並び替えも簡単にできるSORT関数】
見出し列は範囲選択から除外してデータのみを範囲選択することになります。

B12をクリックして、次のように数式を設定します。

=SORT(B9:F10,2,-1,TRUE)
列の場合は、並び替え基準を「TRUE」に設定すればいいだけです。

あとは、スピルによって、自動的に範囲が設定されます。

今まで列の並び替えを行うことは、大変な作業でしたが、SORT関数を使うことで、簡単に列で並び替えを行うことができました。

最後に、複数条件で並び替えを行う場合はどうしたらいいのでしょうか？

【複数条件ならばSORTBY関数】
複数条件で並び替えをするには、SORTBY関数を使います。

基本的に、SORT関数と使い方は似ています。

次の表があります。

F16をクリックして、SORTBY関数ダイアログボックスを表示して次のように設定していきます。

配列には、A16:D20。見出し行を除いたデータをすべて範囲選択
基準配列1は、第一条件で並び替えをしたい範囲を選択しますので、D16:D20
並び替え順序1は、「1」ならば昇順、「-1」ならば降順で並び替えをおこなうことができます。

今回は、昇順にしたいので、「1」で設定します。

基準配列2は、第二条件の並び替えを範囲選択しますので、C16:C20。
並び替え順序2は、金額の高い順にしたいので、「-1」の降順で設定します。

F16の数式は、
=SORTBY(A16:D20,D16:D20,1,C16:C20,-1)

実行して確認してみましょう。

複数条件で並び替えを行うことができました。

Office365 Insiderには、色々新しく追加された関数がありますので、知識をプラスしていくと、さらに色々できるかもしれませんね。