Excelを使う際には、シートの並び替えを自動でしたいこともあるかもしれません。エクセルでの並べ替えが自動でできるようになると、思い通りの表にすぐになるだけではなく、データの値が変わった時にもすぐに反映できて便利ですよね。Excelでデータを自動で並び替えるには、プログラムやマクロは使わずに関数を使って連動させることで可能です。今回は、エクセルシートの並び替えを自動で行う方法と、Excelのシート並べ替えに連動させて同じ行の値を自動ソートするやり方を、作業画像や動画を交えながらご紹介します。（手動で行う方法はこちら）
◎エクセルの自動並べ替えで関数ソートを使う方法
Excelで自動で並べ替えをする方法としてまず考えられるのが、SORT関数を使うことです。Excelシートの範囲や配列の内容を並び替える機能があり、特定の列の値を基準に昇順(小さい順)または降順(大きい順)に並べ替えられます。エクセルシートの並び替えが自動でできるように入力する式は、『=SORT (配列,[並べ替えインデックス], [並べ替え順序], [並べ替え基準]) 』です。[配列]はExcelで自動で並び替えをしたいセル範囲、[並べ替えインデックス]は基準にしたい列の範囲の中での左からの番号、[並べ替え順序]には降順なら-1・昇順なら1(規定値のため昇順のみ省略可)を入力しましょう。[並べ替え基準]は行で並べ替えるときに使うため省略またはFALSEとすれば問題ありません。
たとえば、以下のExcelでシートの並び替えを自動で行いたいとしましょう。対象範囲をB列からK列全体として「ポイント」を基準に降順に並べ替える場合、[並べ替えインデックス]は7(H列はB列から数えて7番目)になるため、式は『=SORT (B3:K28,1) 』となります。
場合によっては、基準とする値が全く同じもののこともあるかもしれません。値が重複している場合は、エクセルで元の配列が上になっているものが並べ替え後も上に来ます。自動で想定しているような並びにするには、『=SORT (SORT (B3:K28,3,1),1) 』などと配列にさらにソート関数を加えましょう。ここでは、B3:K28の3列目つまりD列の[ID]を基準に昇順にしたものを7列目つまりH列の[ポイント]を基準に降順に並び替えるという意味です。
◎Excelでsort関数が使えない！エクセルにsort関数がない原因と並び替えで連動させる関数の名前は？
この方法で実際にしようとすると、「エクセルにsort関数がない！」と困ることがあるかもしれません。実は、Excel2016やExcel2019ではsort関数が使えない仕様なのです。sort関数を使えるエクセルのバージョンとしてはExcel365というOffice365で購入したものが挙げられます。Office365(Microsoft365)とOffice2019の大きな違いは、クラウド版の商品であるかです。Googleと同じようにクラウドにアクセスできれば同じものを使えるため、PCに限らずタブレットやスマートフォンからも編集や閲覧の操作ができます。
そして、もう一つの大きな違いは、前者がサブスクリプション型の契約であることです。このOffice2019やOffice2016といった4桁の数値が書かれた商品は、その年に発売されたもので、買い切り型のため更新されません。一方で、Office365(Microsoft365)は月々あるいは年間で使用料を支払う必要がありますが、常にアップロードされます。そのため、Excelのバージョンによってsort関数のように使えないものが出てくるのです。
エクセルの並び替えで連動させるものには関数sort以外に、LARGE関数・SMALL関数、INDEX関数とMATCH関数などの名前が挙げられます。並び替えでどのように使っていくのかを次の項目で見ていきましょう。
◎エクセルの表やシートの並び替えを自動で行う方法[動画付き]
エクセルの表のうち並び替えを自動で行う際に順番の基準となる列に、式を入力します。昇順に自動で並べたい場合はLARGE関数を、降順に並べたい場合はSMALL関数を使いましょう。ここでは、LARGE関数を使って、ポイントの大きなものからエクセル内に書かれている記事のタイトルを自動的に並べ替える作業を例に考えます。
LARGE関数の書式は、『=LARGE (配列:調べるセル範囲の絶対参照,順位:数値またはセル参照)』です。[数値またはセル参照]に代入された値と同じ順番の、数値あるいは項目を[調べるセル範囲]の中から取り出します。後々に数式をコピーすることを考えると、調べるセル範囲は固定ですから、絶対参照$を付けておくと良いでしょう。
また、2つ目の引数の順位は、数値で入力もできますが、コピーをしやすくするために参照するのがおすすめです。もし縦向きに昇順が書かれている列があればそのセルを参照し、なければCOUNT関数などを使って順位を書き加えましょう。書式の例としては、1行目を1・2行目を[=すぐ上のセル参照+1]にしてコピーするほか、『=COUNT(同じ行で調べるセル範囲と同じ列のセル:1行目のセル)』なども考えられます。
あとは、式を下の行にもコピーするだけです。順位のみが変わっていくため、対象範囲の中の数値が大きいものから順番に取り出されるようになりました。
●Excelのシート並べ替えに連動させて同じ行の値を自動ソートする方法
次は、エクセルで同じ行に書かれている項目をいかに連動して抽出するかです。Excelのシートで並べ替えた値と同じ行のデータを自動ソートするには、INDEX関数とMATCH関数を組み合わせます。INDEX関数はエクセルの指定された行と列が交差するセルにある値または参照を返す機能があり、書式は『=INDEX (配列,行番号,[列番号]) 』あるいは『=INDEX (参照,行番号,[列番号], [領域番号]) 』です。また、MATCH関数は『=MATCH (検査値,検査範囲,[照合の種類]) 』の書式で表され、指定された種類で範囲内を検索し、一致する要素の、配列内での相対的な位置を返します。これらを組み合わせ、『=INDEX (参照,MATCH(検査値,検査範囲,[照合の種類])) 』と行番号をMATCH関数で求めることで、エクセルで並べ替えた値の元の位置と同じ行の項目を自動的に抽出するのです。
実際の例(Q3への入力)でみていきましょう。今回、取り出したい値はB列のどこかにあるため、配列は[B: B]または[$B$3: $B$28]とします。行番号は、エクセルで並べ替えたT列の値が元々G列のどこにあるかで決まりますから、[検査値]を[T3] 、[検査範囲]を[I: I]または[$I$3: $I$28]とすれば適切です。[照合の種類]は0なら完全一致・1なら検査値以下の最大値・-1なら検査値以上の最小値をを取り出します。今回は全く同じ数値を抽出しようとしていますから、忘れずに[0]を入力しましょう。
●Excelで自動で並べ替えができるとどう見えるのか？
式を入力してコピーもできたら、最後に問題なく反映されているかを確かめます。今回の例では、Excelのシートで取り出した値に応じて並び替えが降順におこなれていることがわかるでしょう。自動で変更されるように各要素が連動していますから、基準となる値(ここではポイント)を変更するとそれに合わせて順番と対応する他の項目も入れ替わります。
Excelでの並び替えでは関数VLOOKUPを使ってデータを抽出することも可能です。ブイルックアップ関数は、調べる要素と同じ行にある特定の列の値を取り出せるため、INDEX関数とMATCH関数の組み合わせと似たような機能を持ちます。ただし、取り出せる値は調べる値よりも右側の列であることが条件です。今回の例のように、記事タイトルや公開日といった、左側にあるセルの値は直接は取り出せません。エクセルでの並び替えでVLOOKUP関数を使うのであれば、自動で抽出する値を予めLARGE/SMALL関数で並べ替えた値よりも右側に移動させてから式を入力しましょう。
◎エクセルの別シートに自動で並び替えする方法
エクセルで別シートへ自動で並び替えするには、セル参照をする際に、直前に元のデータがあるシート名を入力すれば問題ありません。今回の例では、『=LARGE (並べ替え!$H$3: $H$28,並べ替え!P3) 』『=INDEX (並べ替え!$B$3: $B$28, MATCH (K3,並べ替え!$H$3: $H$28,0)) 』とすれば別シートにも反映できます。先に一連の流れを把握して、別シートの場合は直前に[シート名!]だと押さえておくとわかりやすいでしょう。
◎まとめ
今回は、Excelで自動で並べ替えを行う方法をご紹介しました。エクセルのsort関数が使えると便利ですが、ない場合にはLARGEまたはSMALL関数で並べ替えを自動で行ってから、INDEX関数とMATCH関数の組み合わせなどで他の値を連動させるのがおすすめです。式が比較的に長いので大変かもしれませんが、エクセルの表に一旦入力すると並べ替えが自動でできるようになりますから、習得して使えるようになると作業を効率よく進められるでしょう。最後までお読みいただきありがとうございました。
コメント