入力した列見出しの順序で列を並べ替える…SORTBY＋XMATCH関数

［Excel 2021以降］

元の表とは別のセルに列見出しだけを入力しておき、その列見出しの順序で元の表の列を並べ替える方法を紹介します。XMATCH関数を使用して列の並び順を求め、SORTBY関数を使用して並べ替えを行います。どちらもExcel 2021以降で使用できる関数です。

1 SORTBY関数の基本的な使い方
2 方法1　作業セルを使って名簿を列見出し順に並べ替える
3 方法2　作業セルを使わずに名簿を列見出し順に並べ替える
4 StepUp　同時に行も並べ替えるには

SORTBY関数の基本的な使い方

まずはSORTBY関数の使い方をおさらいしておきましょう。SORTBY関数は、指定した［基準配列］をもとに［配列］を並べ替える関数です。昇順の並べ替えを行う場合は［順序1］を省略できます。

データを指定した順序で並べ替える
=SORTBY(配列, 基準配列1, ［順序1］, ［基準配列2, 順序2］…)

図1の名簿を「コース、No、会員名、カナ、年齢」の順に並べ替えてみましょう。準備として元表の下（実際には1行5列のセル範囲であれば場所はどこでもOK）に並べ替えの基準となる列番号を入力しておきます。「No」は2列目に配置したいので「2」、「会員名」は3列目に配置したいので「3」……という具合に「2、3、4、1、5」と入力します。

図1　名簿を「コース、No、会員名、カナ、年齢」の順に並べ替えたい。準備として作業セルに並び順を入力しておく。

並べ替えた結果を表示するセル（ここではセルG3）を選択して、SORTBY関数を入力します。引数［配列］に名簿のセルA3～E9、［基準配列1］に並び順のセルA11～E11を指定します。

セルG3 | =SORTBY(A3:E9,A11:E11)

［Enter］キーを押すと、セルG3～K9の範囲に数式がスピル（隣接するセルに数式が自動拡張すること）し、［基準配列1］の昇順（小さい順）で並べ替えが行われます。結果として［基準配列1］が「1」の「コース」が1列目、「2」の「No」が2列目、……と、名簿が「コース、No、会員名、カナ、年齢」の順に表示されます。

図2　セルG3にSORTBY関数を入力して、［Enter］キーを押す。すると、名簿が「コース、No、会員名、カナ、年齢」の順に並べ替えられて表示される。

なお、SORTBY関数の詳しい使い方は「SORTBY関数 ● データを指定した順序で並べ替える」を参照してください。

メモ

配列定数も使用可能

作業セルに「2、3、4、1、5」と入力する代わりに、SORTBY関数の引数［基準配列］に配列定数「{2,3,4,1,5}」を指定することもできます。
=SORTBY(A3:E9,{2,3,4,1,5})

方法1　作業セルを使って名簿を列見出し順に並べ替える

本題に移ります。まず、XMATCH関数を使用して列の並び順を求めます。XMATCH関数は、［検索値］が［検索範囲］の中で何番目の位置にあるかを求める関数です。

指定した範囲の中から検索値の位置を求める
=XMATCH(検索値, 検索範囲, ［一致モード］, ［検索モード］)

作業用のセルA11を選択し、XMATCH関数を入力します。［検索値］に元の名簿の列見出しのセルA2～E2、［検索範囲］に出力先の列見出しのセルG2～K2を指定します。［Enter］キーを押すと、セルA11～E11の範囲に数式がスピルし、並び順の番号が表示されます。

セルA11 | =XMATCH(A2:E2,G2:K2)

図3　作業用のセルA11を選択し、XMATCH関数を入力して［Enter］キーを押すと、並び順の番号が表示される。

最後にSORTBY関数を使用して並べ替えを行います。引数［配列］に名簿のセルA3～E9、［基準配列1］に並び順のセルA11～E11を指定します。数式を入力する際にセルA11～E11をドラッグすると「A11#」が自動入力されます。この「#」は「スピル範囲演算子」と呼ばれるものです。「A11#」は、セルA11に入力した数式がスピルした範囲、すなわちA11～E11を表します。［Enter］キーを押すと、セルG3～K9の範囲に数式がスピルし、列見出しの通りに名簿が並べ替えられます。