はじめに
Excel は 30 年以上にわたって同じ使い方をされてきました。VLOOKUP を覚え、補助列を作り、数式を下へドラッグし、ピボットテーブルを更新する日常です。ところが Microsoft 365 版の Excel は、その間に静かに、しかし根本的に変わりました。
その中心にあるのが **動的配列(dynamic array)** です。一つの数式が複数のセルへ自動的に「あふれ出し(スピル)」、関数が配列をまるごと返し、補助列なしでも複雑な変換が一行で完結します。ここに `LAMBDA`、`LET`、`GROUPBY`、正規表現関数まで加わり、いまや Excel は軽量な関数型データ処理ツールに近づきました。
この記事では、2026 年時点で Microsoft 365 において使える最新関数を、実務の例を中心に整理します。各関数ごとに実際の数式と結果の説明を併せて示し、旧バージョンとの互換性に関する注意点やダッシュボード・レポートのシナリオまで扱います。
> この記事の関数は、ほとんどが Microsoft 365 サブスクリプション版と Web 版 Excel で動作します。永続ライセンス(Excel 2019/2021/2024)は一部のみ対応しているため、最後の互換性表を必ず確認してください。
1. 動的配列とスピル — パラダイムの転換
スピルとは何か
従来の Excel では、関数は一つのセルに一つの値だけを返していました。動的配列エンジンが導入されたことで、一つの数式が複数の結果を隣接するセルへ **自動的に拡張** します。これをスピルと呼びます。
たとえば A2:A6 に数値があるとき、一つのセルに次を入力してみましょう。
=A2:A6*2
以前なら単一の値しか出ないか、`Ctrl+Shift+Enter` で配列数式を作る必要がありましたが、いまは入力したセルの下に 5 つの結果が自動的にあふれ出します。結果の範囲全体を **スピル範囲** と呼び、開始セルが C2 なら `C2#`(ハッシュ演算子)で範囲全体を参照できます。
=SUM(C2#)
上の数式は C2 から始まるスピル範囲全体の合計を求めます。スピル範囲のサイズが変わっても、参照は自動的に追従します。
#SPILL! エラー
スピルが広がる場所に別の値があると `#SPILL!` エラーになります。解決策は単純です。結果が広がるセルを空けてあげればよいのです。動的配列を使うときは「数式一行が下・右の空間を占有する」という感覚を身につけることが重要です。
A B C(=A2:A6*2)
10 20 <- ここから開始
20 40
30 60
40 80
50 100
補助列が消える
動的配列の最大の実務的利点は **補助列の排除** です。以前は「単価 × 数量」の列を作って合計していましたが、いまは一つの数式の中で配列の積を処理します。
=SUM(unitPriceRange * quantityRange)
数式がすっきりし、行を追加・削除しても補助列を維持・管理する必要がなくなります。
2. XLOOKUP — VLOOKUP の完全な後継
`VLOOKUP` の慢性的な問題は三つでした。左方向に検索できないこと、列番号のハードコーディング、既定値が近似一致であることです。`XLOOKUP` はこれらをすべて解決します。
=XLOOKUP(lookupValue, lookupRange, returnRange, "見つかりません", 0)
- 第 1 引数: 探す値
- 第 2 引数: 探す範囲(どの方向でも可)
- 第 3 引数: 返す範囲
- 第 4 引数: 見つからなかったときに返す値(既定のエラーの代わりにユーザー指定)
- 第 5 引数: 一致モード(0 = 完全一致、既定値)
たとえば社員番号で名前を探す場合、社員番号の列が名前の列の右側にあっても問題ありません。
=XLOOKUP("E1042", employeeIdColumn, nameColumn, "未登録")
返す範囲を複数列で指定すると、一度に複数の値をスピルで取得することもできます。
=XLOOKUP("E1042", employeeIdColumn, nameDeptSalaryRange, "見つかりません")
こうすると、名前・部署・給与が一行にあふれ出します。VLOOKUP を三回使っていた作業が一行になります。
3. FILTER / SORT / UNIQUE / SEQUENCE — データ加工の四天王
FILTER
条件に合う行だけを選び、スピルで返します。
=FILTER(dataRange, deptColumn="開発", "結果なし")
部署が「開発」である行全体をそのまま展開します。条件を積(AND)・和(OR)で組み合わせることもできます。
=FILTER(dataRange, (deptColumn="開発")*(salaryColumn>5000), "なし")
`*` は AND、`+` は OR として動作します。
SORT / SORTBY
=SORT(FILTER(dataRange, deptColumn="開発"), 3, -1)
FILTER の結果を 3 番目の列を基準に降順(-1)で並べ替えます。関数を入れ子にして「フィルター後に並べ替え」を一行で完結させます。別の列を基準に並べ替えるには `SORTBY` が便利です。
=SORTBY(dataRange, salaryColumn, -1)
UNIQUE
重複を取り除きます。
=UNIQUE(deptColumn)
部署の一覧を一意の値だけに絞って展開します。ドロップダウンリストや集計基準の軸を作るときに非常に役立ちます。
SEQUENCE
連続する数値の配列を作ります。
=SEQUENCE(12, 1, 1, 1)
1 から 1 ずつ増える 12 行 1 列の配列(1〜12 月)を生成します。日付軸やインデックスの生成によく使います。
4. LET — 数式の中で変数を宣言する
長い数式は同じ計算を繰り返しがちです。`LET` は数式の内部に **名前付きの変数** を作り、可読性とパフォーマンスを同時に高めます。
=LET(
preTax, unitPrice * quantity,
tax, preTax * 0.1,
preTax + tax
)
`preTax` と `tax` を一度だけ計算して再利用します。最後の引数が最終的な戻り値です。変数が増えるほど効果が大きくなり、複雑なレポート数式を人間が読める形にしてくれます。
5. LAMBDA とその仲間たち — Excel の中の関数型プログラミング
LAMBDA: 自分だけの関数を作る
`LAMBDA` は再利用可能なユーザー定義関数を数式だけで作れるようにしてくれます。名前マネージャーに登録すれば、ワークブック全体から呼び出せます。
=LAMBDA(celsius, celsius * 9 / 5 + 32)(25)
上はその場で呼び出す例で、25 を華氏に変換して 77 を返します。名前マネージャーに `celsiusToFahrenheit` として登録すると、次のように関数として使えます。
=celsiusToFahrenheit(25)
MAP: 配列の各要素に関数を適用
=MAP(scoreRange, LAMBDA(x, IF(x>=60, "合格", "不合格")))
得点の配列の各値にラムダを適用し、合格・不合格の配列を返します。
REDUCE: 累積集計
=REDUCE(0, amountRange, LAMBDA(acc, val, acc + val))
初期値 0 から始めて、すべての値を累積します。累積和、累積積など任意の集計ロジックを表現できます。
SCAN: 累積の過程をすべて返す
`REDUCE` が最終値だけを返すのに対し、`SCAN` は各段階の累積結果を配列として展開します。
=SCAN(0, revenueRange, LAMBDA(acc, val, acc + val))
月別売上の累積合計(YTD)を一行で作ります。
BYROW / BYCOL: 行・列単位の集計
=BYROW(scoreTableRange, LAMBDA(row, AVERAGE(row)))
各行の平均を縦の配列として返します。学生ごとの平均、項目ごとの合計などに適しています。列単位には `BYCOL` を使います。
6. GROUPBY / PIVOTBY — 数式で作るピボット
ピボットテーブルの最大の不便は、データが変わるたびに **更新** が必要なことです。`GROUPBY` と `PIVOTBY` は数式ベースの集計なので、元のデータが変わると結果が自動的に反映されます。更新は不要です。
GROUPBY: グループ別の集計
=GROUPBY(deptColumn, revenueColumn, SUM, , 1)
- 第 1 引数: グループ基準(行フィールド)
- 第 2 引数: 集計する値
- 第 3 引数: 集計関数(SUM、AVERAGE、COUNT など)
- 第 5 引数: ヘッダー表示オプション
部署別の売上合計を自動的に展開します。集計関数の位置にラムダを入れて **カスタム集計** も可能です。
=GROUPBY(deptColumn, revenueColumn, LAMBDA(v, MAX(v) - MIN(v)))
部署別売上の最大-最小の幅(範囲)を自分で定義して計算します。
PIVOTBY: 行・列のクロス集計
=PIVOTBY(deptColumn, quarterColumn, revenueColumn, SUM)
部署を行に、四半期を列にしたクロス表を作ります。従来のピボットテーブルと違い、セルの数式なので、元のデータが更新されると即座に反映されます。ダッシュボードの動的な要約表を作るときに強力です。
7. 正規表現関数 — REGEXTEST / REGEXEXTRACT / REGEXREPLACE
Excel に正規表現が正式な関数として入りました。テキストの検証・抽出・置換がはるかにすっきりします。
REGEXTEST: パターンの一致可否
=REGEXTEST(A2, "^[0-9]{3}-[0-9]{4}$")
A2 が「数字 3 個-数字 4 個」のパターンなら TRUE を返します。電話番号・郵便番号の形式検証に適しています。
REGEXEXTRACT: 一致部分の抽出
=REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+")
文字列からメールアドレスを抜き出します。
REGEXREPLACE: パターンの置換
=REGEXREPLACE(A2, "[^0-9]", "")
数字でないすべての文字を取り除き、純粋な数字だけを残します。電話番号の正規化によく使います。
8. TRIMRANGE と新しい trim 参照
`TRIMRANGE` は範囲の端にある空の行・列を切り落とします。表が大きくなったり小さくなったりするとき、「最後まで捉えつつ空の部分は除く」動的な範囲を作れます。
=TRIMRANGE(A1:A100000)
100,000 行を参照しても実際に値がある部分までしか返さないため、過度に大きい範囲による性能低下を抑えます。
9. テキストの分解 — TEXTSPLIT / TEXTBEFORE / TEXTAFTER
TEXTSPLIT
区切り文字でテキストを分けてスピルします。「区切り位置」ウィザードを数式で置き換えます。
=TEXTSPLIT("ソウル,釜山,大邱", ",")
三つの都市が横に展開されます。行の区切り文字まで指定すれば、2 次元にも分けられます。
TEXTBEFORE / TEXTAFTER
=TEXTBEFORE("user@example.com", "@")
`@` の前の "user" を返します。`TEXTAFTER` は後ろの "example.com" を返します。ファイル名から拡張子を分離したり、パスからファイル名を抽出したりするのに便利です。
10. データの取り込み — IMPORTTEXT / IMPORTCSV
Web 版 Excel と最新のデスクトップに `IMPORTTEXT`、`IMPORTCSV` が追加され、外部のテキスト/CSV を数式で直接読み込めるようになりました。
=IMPORTCSV("https://example.com/data/sales.csv")
指定した CSV をセルにスピルで取り込みます。簡単な自動更新のデータソースとして使えますが、大規模・複雑な変換は次の記事で扱う Power Query のほうが適しています。
11. 実務シナリオ — 動的ダッシュボードを作る
関数を組み合わせて「更新が不要な」部署別売上ダッシュボードを作ってみましょう。
1. 一意の部署一覧を抽出
=UNIQUE(deptColumn)
2. 部署別の売上合計と平均を一度に
=GROUPBY(deptColumn, revenueColumn, HSTACK(SUM, AVERAGE))
3. 上位 3 部署だけをフィルター・並べ替え
=TAKE(SORT(GROUPBY(deptColumn, revenueColumn, SUM), 2, -1), 3)
`SORT` で売上を降順に並べ替えた後、`TAKE` で上位 3 行だけを取得します。
4. 特定期間のクロス表
=PIVOTBY(deptColumn, monthColumn, revenueColumn, SUM)
これらすべてのセルは、元のデータが変わると自動更新されます。入力シートに行を追加するだけで、ダッシュボードが生きているように追従します。
12. 関数まとめ表
| 関数 | 役割 | 重要ポイント |
| --- | --- | --- |
| XLOOKUP | 検索 | 双方向、見つからないとき既定値を指定 |
| FILTER | 条件抽出 | 複数条件を AND/OR で結合 |
| SORT / SORTBY | 並べ替え | 別の列を基準に並べ替え可能 |
| UNIQUE | 重複除去 | 一覧・軸の生成 |
| SEQUENCE | 連続配列 | 日付・インデックスの生成 |
| LET | 変数宣言 | 可読性・性能の向上 |
| LAMBDA | ユーザー関数 | 名前マネージャー登録後に再利用 |
| MAP / REDUCE / SCAN | 関数型集計 | 要素ごと・累積の処理 |
| BYROW / BYCOL | 行・列集計 | 方向別の要約 |
| GROUPBY / PIVOTBY | 数式ピボット | 更新不要、カスタムラムダ |
| REGEXTEST / EXTRACT / REPLACE | 正規表現 | 検証・抽出・置換 |
| TRIMRANGE | 範囲トリム | 空の端を除去 |
| TEXTSPLIT / BEFORE / AFTER | テキスト分解 | 区切り文字ベースの分離 |
| IMPORTTEXT / IMPORTCSV | 取り込み | 外部データを直接読み込み |
13. 旧バージョンの互換性に関する注意
| 機能 | M365 / Web | Excel 2024 | Excel 2021 | Excel 2019 以下 |
| --- | --- | --- | --- | --- |
| 動的配列 / スピル | 対応 | 対応 | 対応 | 非対応 |
| XLOOKUP | 対応 | 対応 | 対応 | 非対応 |
| LET / LAMBDA | 対応 | 一部 | 非対応 | 非対応 |
| GROUPBY / PIVOTBY | 対応 | 非対応 | 非対応 | 非対応 |
| 正規表現関数 | 対応 | 非対応 | 非対応 | 非対応 |
旧バージョンのユーザーとファイルを共有するときは、動的配列の結果が暗黙的なインターセクト演算子で壊れることがあります。値として貼り付け(値のコピー)で静的な結果を残すか、互換関数で置き換えるのが安全です。
おわりに
Excel の新関数は単なる「便利なショートカット」ではなく、**作業のやり方そのものを変えるツール** です。補助列とドラッグを減らし、一行の宣言的な数式でデータを加工すれば、ミスが減り、保守が容易になります。
最初は `XLOOKUP`、`FILTER`、`UNIQUE` から覚え、慣れてきたら `LET` と `LAMBDA` へ進んでください。最後に `GROUPBY`/`PIVOTBY` まで手になじめば、「更新のいらないレポート」という新しい作業の流れが開けます。
次の記事では、マウスを離れてキーボードで Excel を扱うショートカット体系を、その次には Power Query でデータ整形を自動化する方法を扱います。
参考資料
- [XLOOKUP 関数 (Microsoft サポート)](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)
- [FILTER 関数 (Microsoft サポート)](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)
- [LAMBDA 関数 (Microsoft サポート)](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)
- [LET 関数 (Microsoft サポート)](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)
- [GROUPBY 関数 (Microsoft サポート)](https://support.microsoft.com/en-us/office/groupby-function-d58dbc52-9c12-4ec9-bcb3-a37c2c038a9b)
- [PIVOTBY 関数 (Microsoft サポート)](https://support.microsoft.com/en-us/office/pivotby-function-3e90a3d5-2cb6-4b8c-bb8a-2f7286ca665b)
- [動的配列とスピルした配列の動作 (Microsoft サポート)](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531)
- [Excel の正規表現関数 (Microsoft Tech Community)](https://techcommunity.microsoft.com/t5/excel-blog/regular-expressions-regex-in-excel/ba-p/4149144)
- [Excel 関数 (アルファベット順) (Microsoft サポート)](https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188)
현재 단락 (1/141)
Excel は 30 年以上にわたって同じ使い方をされてきました。VLOOKUP を覚え、補助列を作り、数式を下へドラッグし、ピボットテーブルを更新する日常です。ところが Microsoft 365 ...