Excelのスピルなら関数をより便利に［SORT関数で簡単に並べ替え］

今回の記事では

並べ替えを関数でやりたいけど「スピル」って機能があるらしいけどそれって何なの？

難しいカタカナで何を言ってるのか良くわからない

並べ替えの設定は地味に面倒だから関数を使って同じことは出来ないの？

関数で並べ替えられたらいちいち設定しなくて良いのにどうにかならない？

こういった疑問に答えます

✔関数で自由に並べ替えをする方法のテーマ

①Excelのスピル機能は数式や関数をより便利にしてくれる機能です

②SORT関数を使えば1箇所で自由に並べ替えた表を表示出来ます

この記事を書いているのはOfficeを約20年間活用しながら

ITを活用したエステの経営マネジメントしている私の経験を基に

「関数で自由に並べ替えをする方法のテーマ」に関して実体験に基づいて解説しています

今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい

Excelのスピル機能は数式や関数をより便利にしてくれる機能です

Excelのスピル機能を使えばより簡単に数式や関数を設定することが出来ます

なぜなら、スピル機能とは対象範囲を全体的に捉えて設定を自動化する機能だからです

Excelにはスピル機能というものがあります

スピル機能はまだ新しい機能で【Excel2021やMicrosoft365】といった最新(執筆時点では)の機能です

それでは、スピル機能の基本を解説していきます

数式や関数の現在と未来

例えばこんな表があります

現在の計算方法だと「D2」のセルのように計算結果は1つのセルに表示されますよね

そして、D2のセルの式を10行目までコピーするのがいままのでやり方です

では、一連の動きを一緒に見てみましょう

「=A2*B2*$G$2」がD2のセルの式です

つまり、「単価×数量×消費税」ですね

これで「税込みの金額」を一瞬で表示出来る訳です

ポイントは消費税のセルである「G2」のセルを絶対参照にしてどのセルでも消費税がズレないように固定していることです

これは、数式や関数を指定する時の王道のやり方ですね

このセル範囲やセルの指定を固定したり自由に変更出来る仕組みが今までのやり方です

このやり方には「相対参照・絶対参照・複合参照」という名前があります

そして、基準になる一つの数式や関数を他のセルにコピーすることで一気に自動計算機能を設定します

これが今までのやり方ですね

では、未来の計算について見ていきましょう

未来の計算つまり、「スピルという機能」ですがスピルは一つのセルに対してではなくセル範囲単位で計算をしてくれる機能です

言葉で言っても分かりづらいのでスピル機能を使った計算式を見てみましょう

今までの数式や関数を知っていれば、そこまで難しくは無いので安心して下さい

E2のセルに「=A2:A10*B2:B10*G2」と入力しました

計算式は比較的シンプルですよね

「単価の範囲×数量の範囲×消費税のセル」です

今までのやり方ではエラーになっていたやり方ですが、スピル機能のおかげで「範囲×範囲」という式が出来るようになりました

範囲を指定しても意味は「A2×B2」「C3×B3」……と言った具合にちゃんと計算してくれます

また、消費税のセルも「A2×B2×G2」「C3×B3×G2」と言った具合に計算してくれます

つまり、今までの3種類の参照方法が必要無くなったということです

コレだけでもすごいですよね

でもまだ、スピル機能の凄さは続きますよ

この状態で「Enter」キーを押してみます

結果はこの通りです

私は一切コピーをしていません

つまり、掛ける範囲に合わせて自動で計算式を設定してくれたということです

おまけに各参照方法を設定していないのに自動で正しい計算をしてくれました

めっちゃすごいです

スピル機能のもう一つの特徴も見てみましょう

「E2のセル」の計算式は普通に黒い色で馴染みがありますね

ところが、「E3のセル」以降は数式ボックスを見ると「数式がグレー」になっています

これは、「ゴースト」と呼ばれ実態がない数式です

試しに「E3のセル」の数式を削除していましょう

「Delete」キーを何度か押してみましたが何度ためしても数式が消えることはありません

まさに「実体のない幽霊(ゴースト)」ですね

ただ、セルに直接入力することは出来て実際にやってみると大元のセルに「#SPILL!」のエラーが表示されます

スピル機能にはこんな特徴がるので、この特徴を覚えておいて下さい

これが、これからの新しい数式の型になっていくでしょう

繰り返しになりますが、Excelのスピル機能を使えばより簡単に数式や関数を設定することが出来ます

SORT関数を使えば1箇所で自由に並べ替えた表を表示出来ます

SORT関数を使えば1箇所設定することで自在に並べ替えた結果を表示してくれます

なぜなら、SORT関数はスピル機能を前提に作られた関数だからです

SORT(ソート)関数はスピル機能があってこその関数です

「ソート」とは「並べ替え」という意味なのでそのままですね

では、ソート関数の使い方について解説していきます

ソート関数の特徴の一つは、元のデータをいじること無く並べ替えた結果だけ表示出来ることです

今回は左の「地域別の釣れる魚の結果一覧」を基に右の表に並べ替えた結果を一瞬で表示させることが目的です

SORT関数は新しいので最初は慣れる必要がありますが、そこまで難しくなく楽しい関数なのでぜひ使えるようになってみましょう

SORT関数の基本的な使い方は

「SORT(配列,並べ替え基準,並べ替え方法,行列の並べ替え)」です

まず「=SORT(配列」は「=SORT(A2:E16」ですね

「配列」とは同じルールに従ったデータのことを言います

簡単に言うと「並べ替えたい表の範囲」のことですね

つまり、「A2～E16の範囲を指定するよ」という意味ですね

次は「,並べ替え基準」の部分ですが「3」となっていますよね

これは「並べ替えたい列数を指定してね」という意味なので配列の左を「1」とするので「3列目」つまり、「魚の名前を並べ替えたいよ」ということです

次は、「並べ替え方法,」ですが「1」になっています「1=昇順」「-1=降順」といういみなので「昇順で並べ替えてね」という意味です

最後は省略出来るので指定しなくても良いですが、「行列の並べ替え」の指定で「行方向に長い表」つまり一般的な表を指定したい時は「FALSE」を指定

横長の表なら「TRUE」を指定します

何も指定しない時は「縦長設定」なので省略する方がシンプルですね

この設定をした結果が

これです

ちゃんと「魚の名前で昇順」に並べ替えられていますよね

もちろん関数を入力したのは「G2のセルのみ」です

スピル機能が働いているのであとは自動的に並べ替えてくれます

元の表は何もしていないのでそのままというのもメリットですね

ちなみに「場所」で並べ替えてみましょう

変更するた対象は「G2のセル」の「列数を4」に変更しただけです

ちゃんと都道府県が昇順で並べ変わりました

こんな風に1つのセルの関数の式を設定(変更)するだけで、面倒な参照方法の調整やコピー作業をする必要が無いので本当に楽で楽しいです

並べ替えた結果を元の表とは別に表示したい時などに便利なのでぜひチャレンジしてみましょう

繰り返しになりますが、SORT関数を使えば1箇所設定することで自在に並べ替えた結果を表示してくれます