g738｜【Excel究極活用術】見出しだけで列順を操作！SORTBY & XMATCHで完全動的レポートを作成

これまでの記事でSORTBY関数を使い、配列定数{2,1,3}などを用いて列の順序を入れ替える裏ワザをご紹介しました。しかし、その方法では列順を変えるたびに数式を書き直す必要があります。

今回はその最終進化形として、XMATCH関数を組み合わせることで、「セルに入力した見出しの順番通りに、列が自動で並び替わる」という、まさに魔法のようなテクニックを解説します。これをマスターすれば、誰でも簡単に操作できるインタラクティブなレポートが作成可能になります。

今回のゴール：入力した見出しの順序通りに、会員名簿の列を自動で並べ替える

F1からH1のセルに見出しを自由な順序で入力します（例：「ランク」「会員名」「年齢」）。その見出しの順序を数式が自動で読み取り、元の名簿から対応する列のデータを抽出し、並べ替えて表示させることが目標です。

サンプルデータ

以下は、上記画像のExcelデータですので、ダウンロードして練習として使用ください。

Excel-g738-1.xlsx  （ダウンロード）

以下のサンプルデータをExcelシートにコピーし、F1からH1に見出しを入力しておきましょう。

	A	B	C	D	E	F	G	H
1	No	会員名	ランク	年齢		ランク	会員名	年齢
2	1	成沢 卓也	A	45		(数式はここに)
3	2	日比野 健	B	29
4	3	大崎 由香	C	53
5	4	水戸 夏美	B	38
6	5	木下 直樹	A	24
7	6	村山 美代	B	40
8	7	野田 亨	C	33

解決策：SORTBY関数とXMATCH関数を組み合わせる

並べ替えたい表の先頭セル（例では F2）に、以下の数式を一度だけ入力します。

※スライドではデータ部分のみを対象にしていますが、今回は見出しも含めて一度に並べ替える、より実践的な数式をご紹介します。その場合は、数式の入力先をF1セルにしてください。

Excel

=SORTBY(B1:D8, XMATCH(B1:D1, F1:H1))

この数式を入力すると、F1:H1の見出しの順序に従って、B1:D8の表が並べ替えられ、結果が自動的に展開（スピル）されます。

数式の解説（内側のXMATCHから理解する）

このテクニックの鍵は、SORTBY関数の2番目の引数（基準配列）を、XMATCH関数を使って動的に生成している点にあります。

ステップ1：XMATCH関数で「優先順位リスト」を自動生成する

まず、内側のXMATCH(B1:D1, F1:H1)が実行されます。

• 動作: 元の表の見出し（B1:D1）が、出力したい先の見出し（F1:H1）の何番目にあるかを順番に調べます。
• 詳細:
  • 元の1列目の見出し「会員名」は、出力先F1:H1の2番目にあります。→ 結果は 2
  • 元の2列目の見出し「ランク」は、出力先F1:H1の1番目にあります。→ 結果は 1
  • 元の3列目の見出し「年齢」は、出力先F1:H1の3番目にあります。→ 結果は 3
• 結果: XMATCH関数は、 {2, 1, 3} という配列を返します。これは、前回の記事で手入力した「配列定数」と全く同じものです。

ステップ2：SORTBY関数が「優先順位リスト」に従って列を並べ替える

XMATCHが生成した配列 {2, 1, 3} を受け取り、SORTBY関数が実行されます。

• 動作: 数式全体が =SORTBY(B1:D8, {2, 1, 3}) となっているのと同じです。
• 結果:
  • 優先順位「1」を持つ元の2列目（ランク）が、新しい表の1列目に来ます。
  • 優先順位「2」を持つ元の1列目（会員名）が、新しい表の2列目に来ます。
  • 優先順意「3」を持つ元の3列目（年齢）が、新しい表の3列目に来ます。

この方法の絶大なメリット

この方法の最大の利点は、数式を一切変更することなく、レポートの列順を自由に変更できる点です。例えば、F1:H1の見出しを「年齢」「会員名」「ランク」と書き換えるだけで、XMATCHが返す配列が自動的に{2, 3, 1}に変わり、SORTBYの結果も瞬時に更新されます。

まとめ

SORTBYとXMATCHの組み合わせは、Excelの動的配列機能がもたらす恩恵を最大限に引き出した、非常に高度で実用的なテクニックです。

• 対話的: 見出しを書き換えるだけで結果が変わる、ユーザーフレンドリーなレポートが作れる。
• メンテナンスが容易: 列の追加や削除も、見出しの範囲を調整するだけで対応できる。
• 汎用性が高い: あらゆる表形式のデータに応用可能。

この究極のテクニックをマスターすれば、あなたの作成するExcelシートは、単なる静的な表から、誰もが直感的に操作できる動的なツールへと進化するでしょう。