Excel関数完全ガイド

LET・XMATCH・INDEX・2次元スピルの仕組みを理解する

はじめに

給与所得控除の計算を例に、Excelの最新関数であるLET、XMATCH、INDEX、そして2次元スピル機能についてこのガイドでは詳しく解説します。これらの関数を組み合わせることで、従来のINDEX+MATCH関数よりも読みやすく、メンテナンスしやすい数式を作成できます。

1. LET関数とは

1.1 基本的な仕組み

LET関数は、数式の中で「変数」を定義できる画期的な関数です。プログラミング言語の変数と同じように、計算結果や値に名前を付けて、数式の中で何度も使い回すことができます。

=LET(変数名1, 値1, 変数名2, 値2, 最終的な計算式)

1.2 LET関数のメリット

• 数式が読みやすくなる：変数名で何を計算しているかが一目瞭然
• 計算効率が向上：同じ計算を何度も繰り返さない
• デバッグが簡単：変数ごとに動作を確認できる
• 複合参照（記号）を減らせる：変数に値を格納すればが不要に
• 同じ範囲を何度も書かない：繰り返し使う範囲も変数にできる

1.3 具体例

以下のテーブル（A1:C10）があるとします：

従来の数式との比較を見てみましょう。

従来の方法（複雑で読みにくい）：

=INDEX($A$2:$C$10, MATCH($E$2, $A$2:$A$10, 1), MATCH($F$1, $A$1:$C$1, 0))

この数式では $A$2:$A$10 や $A$1:$C$1 などの範囲が複数回出てきて、変更が大変です。

LET関数を使った方法（読みやすい）：

=LET(
  tbl, $A$2:$C$10,
  header, $A$1:$C$1,
  商品列, $A$2:$A$10,
  検索値, $E$2,
  列名, $F$1,
  行位置, MATCH(検索値, 商品列, 1),
  列位置, MATCH(列名, header, 0),
  INDEX(tbl, 行位置, 列位置)
)

変数 商品列 を定義することで、同じ範囲を何度も書く必要がなくなります。

2. XMATCH関数とは

2.1 基本的な仕組み

XMATCH関数は、MATCH関数の進化版です。指定した値が範囲の中で何番目にあるかを返します。MATCH関数よりも柔軟な検索ができ、スピルにも対応しています。

=XMATCH(検索値, 検索範囲, [一致モード], [検索モード])

2.2 引数の詳細

2.3 MATCH関数との違い

• より柔軟な検索オプション（ワイルドカード、二分探索など）
• スピルに対応（配列を渡すと複数の結果を返す）
• エラー処理がより明確

3. INDEX関数とは

3.1 基本的な仕組み

INDEX関数は、指定した範囲の中から、行番号と列番号を指定して値を取り出す関数です。データベースから特定の値を取得するような動作をします。

=INDEX(範囲, 行番号, [列番号])

3.2 引数の詳細

3.3 具体例

以下のような表があるとします：

この表から値を取り出す例：

=INDEX(A2:C4, 2, 2)  → 「100円」（2行目2列目）
=INDEX(A2:C4, 3, 1)  → 「バナナ」（3行目1列目）

4. スピル（Spill）とは

4.1 基本的な仕組み

スピルは、1つのセルに入力した数式が、必要に応じて自動的に複数のセルに結果を「こぼれる（spill）」ように表示する機能です。従来は配列数式を使って複雑な操作が必要でしたが、スピルを使えば自然に複数の結果を得られます。

4.2 スピルの種類

縦方向のスピル（1次元）

配列を1つ渡すと、縦方向または横方向にスピルします。

=XMATCH(D8:D9, header, 0)  → 2つの結果が縦にスピル

2次元スピル（縦と横）

配列を2つ（縦方向と横方向）渡すと、格子状にスピルします。これにより、複数の行と列にわたる計算結果を1つの数式で得られます。

=INDEX(範囲, {行1;行2}, {列1,列2})  → 2×2の結果が格子状にスピル

4.3 スピルのメリット

• 数式のコピー不要：1つのセルに入力するだけで複数の結果が自動表示
• 複合参照（$）が不要：スピルは自動的に適切な範囲に展開される
• メンテナンスが簡単：元の数式を変更すれば、スピルした全ての結果も自動更新
• 動的な範囲：データの増減に自動対応

5. 実例：給与所得控除の計算

5.1 元のデータ構造

今回の例では、以下のようなデータ構造になっています：

シート「夫_給与収入額面と手取り_sum」：

• E7セル：給与額面（例：5,000,000円）
• F7セル：給与額面（例：6,000,000円）※追加された場合
• D8セル：項目名「収入金額に乗じる率」
• D9セル：項目名「控除/加算金額」

シート「table_給与所得控除」のデータ（D3:F10）：

データの範囲：

• ヘッダー行：D3:F3
• データ部分：D4:F10（最終行D10は空）
• 収入金額列：D4:D10

5.2 従来の数式（INDEX + MATCH）

従来の方法では、E8、E9、F8、F9の4つのセルに、それぞれ別の数式が必要でした：

E8セル：

=INDEX(table_給与所得控除!$D$4:$F$10, MATCH(E$7, table_給与所得控除!$D$4:$D$10, 1), MATCH($D8, table_給与所得控除!$D$3:$F$3, 0))

E9セル：

=INDEX(table_給与所得控除!$D$4:$F$10, MATCH(E$7, table_給与所得控除!$D$4:$D$10, 1), MATCH($D9, table_給与所得控除!$D$3:$F$3, 0))

F8セル：

=INDEX(table_給与所得控除!$D$4:$F$10, MATCH(F$7, table_給与所得控除!$D$4:$D$10, 1), MATCH($D8, table_給与所得控除!$D$3:$F$3, 0))

F9セル：

=INDEX(table_給与所得控除!$D$4:$F$10, MATCH(F$7, table_給与所得控除!$D$4:$D$10, 1), MATCH($D9, table_給与所得控除!$D$3:$F$3, 0))

問題点：

• 合計4つの数式が必要
• 複合参照（$記号）が多い
• table_給与所得控除!$D$4:$D$10 など同じ範囲を何度も書いている
• メンテナンスが大変

5.3 LET + XMATCH + 2次元スピルを使った新しい数式

ステップ1：まず1列だけの場合

E8セルに以下の数式を入力すると、E8とE9の2つのセルに自動的にスピルします：

=LET(
  tbl, table_給与所得控除!$D$4:$F$10,
  header, table_給与所得控除!$D$3:$F$3,
  収入金額列, table_給与所得控除!$D$4:$D$10,
  給与額面, E7,
  項目リスト, D8:D9,
  rowMatch, XMATCH(給与額面, 収入金額列, -1),
  colMatch, XMATCH(項目リスト, header, 0),
  INDEX(tbl, rowMatch, colMatch)
)

改善点：

• table_給与所得控除!$D$4:$D$10 を変数 収入金額列 に格納
• 同じ範囲を2回以上書く必要がない

ステップ2：2列に拡張する場合

たった1箇所を変更するだけで、E8:F9の4つのセル全てに自動的にスピルします！

=LET(
  tbl, table_給与所得控除!$D$4:$F$10,
  header, table_給与所得控除!$D$3:$F$3,
  収入金額列, table_給与所得控除!$D$4:$D$10,
  給与額面リスト, E7:F7,
  項目リスト, D8:D9,
  rowMatch, XMATCH(給与額面リスト, 収入金額列, -1),
  colMatch, XMATCH(項目リスト, header, 0),
  INDEX(tbl, rowMatch, colMatch)
)

変更点：「給与額面, E7,」を「給与額面リスト, E7:F7,」に変更しただけ！

5.4 さらなる拡張性

横方向の拡張（列を増やす）

G7、H7にデータを追加したい場合：

給与額面リスト, E7:H7,

縦方向の拡張（行を増やす）

D10に新しい項目を追加したい場合：

項目リスト, D8:D10,

両方向の拡張

列も行も増やす場合は、両方を変更するだけ：

給与額面リスト, E7:H7,
項目リスト, D8:D10,

このように、範囲を変更するだけで自動的にスピル範囲が拡張されます！

5.5 変数定義の詳しい解説

LET関数で定義している各変数の意味を理解しましょう：

変数1：tbl（テーブルのデータ範囲）

tbl, table_給与所得控除!$D$4:$F$10,

意味： 給与所得控除テーブルのデータ部分全体（3列×7行）を格納

対応するExcelデータ（D4:F10）：

変数2：header（ヘッダー行）

header, table_給与所得控除!$D$3:$F$3,

意味： 列名の行を格納

対応するExcelデータ（D3:F3）：

変数3：収入金額列（検索に使う列）

収入金額列, table_給与所得控除!$D$4:$D$10,

意味： 給与額面を検索する際に使う列（D列のみ）を格納

対応するExcelデータ（D4:D10）：

なぜ変数にするのか：

• XMATCH でこの範囲は使うため、明確な変数名を付けることで数式が読みやすくなる
• 後で範囲を変更する場合、1箇所修正するだけで済む
• テーブルの構造が分かりやすくなる

変数4：給与額面リスト（横方向の配列）

給与額面リスト, E7:F7,

意味： 検索したい給与額面のリスト（横方向に並んでいる）

例：

• E7 = 5,000,000円
• F7 = 6,000,000円

変数5：項目リスト（縦方向の配列）

項目リスト, D8:D9,

意味： 取得したい項目名のリスト（縦方向に並んでいる）

例：

• D8 = 「収入金額に乗じる率」
• D9 = 「控除/加算金額」

変数6：rowMatch（行の位置）

rowMatch, XMATCH(給与額面リスト, 収入金額列, -1),

処理内容：

1. 給与額面リスト（E7:F7）の各値を、収入金額列（D4:D10）で検索
2. 一致モード -1 = 「完全一致または次に小さい値」で検索
3. 各給与額面が、テーブルの何行目に該当するかを返す

例：

• 5,000,000円 → 収入金額列の4行目（3,600,001 ≤ 5,000,000 < 6,600,001）
• 6,000,000円 → 収入金額列の4行目（3,600,001 ≤ 6,000,000 < 6,600,001）
• 結果：rowMatch = {4, 4}

変数7：colMatch（列の位置）

colMatch, XMATCH(項目リスト, header, 0),

処理内容：

1. 項目リスト（D8:D9）の各値を、header（D3:F3）で検索
2. 一致モード 0 = 「完全一致」で検索
3. 各項目名が、ヘッダーの何列目にあるかを返す

例：

• 「収入金額に乗じる率」→ ヘッダーの2列目（E列）
• 「控除/加算金額」→ ヘッダーの3列目（F列）
• 結果：colMatch = {2; 3}

最終計算：INDEX

INDEX(tbl, rowMatch, colMatch)

処理内容：

1. tbl（D4:F10のテーブル全体）から値を取得
2. rowMatch（横方向の配列）と colMatch（縦方向の配列）を組み合わせて、2次元スピル
3. 2×2 = 4つの値を返す

結果（E8:F9）：

5.6 この数式の優れた点

• 1つの数式で複数の値を取得： E8セルに入力するだけで縦横に自動計算
• **複合参照（）がほぼ不要：** 変数を使うことでほとんどのが不要に
• 同じ範囲を何度も書かない： 収入金額列 を変数にすることで、重複を排除
• 読みやすい： 変数名で何をしているかが一目瞭然
• メンテナンスしやすい： テーブル範囲の変更は1箇所修正するだけ
• 拡張性が非常に高い： 範囲を変更するだけで縦にも横にも自動拡張
• 数式のコピー不要： スピルが全て自動で処理

6. まとめ

LET関数、XMATCH関数、INDEX関数、そして2次元スピル機能を組み合わせることで、従来のExcel数式よりも：

可読性が向上

変数名を使うことで、数式が何をしているかが明確になります。特に 収入金額列 のような意味のある名前を付けることで、テーブルの構造が理解しやすくなります。

保守性が向上

1箇所の修正で複数の結果が自動更新されます。同じ範囲を変数にまとめることで、範囲変更が1箇所で済みます。

効率性が向上

2次元スピル機能により、縦横への数式のコピーが完全に不要になります。

拡張性が飛躍的に向上

データの行や列を追加する場合も、範囲指定を変更するだけで自動的に対応できます。

特に、E7をE7:F7に変更するだけで横方向にも展開できる柔軟性と、table_給与所得控除!$D$4:$D$10 を変数 収入金額列 にまとめることで同じ範囲を何度も書かなくて済む点は、大規模なデータ処理において非常に強力です。

これらの新しい関数を活用することで、より強力で柔軟なExcelシートを作成できます。ぜひ実際のデータで試してみてください！