Skip to content
Published on

2026 Excel 新関数マスター — 動的配列から GROUPBY まで

🇰🇷KO🇺🇸EN🇯🇵JA
Split View필사 모드
Authors

はじめに

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)
  1. 部署別の売上合計と平均を一度に
=GROUPBY(deptColumn, revenueColumn, HSTACK(SUM, AVERAGE))
  1. 上位 3 部署だけをフィルター・並べ替え
=TAKE(SORT(GROUPBY(deptColumn, revenueColumn, SUM), 2, -1), 3)

SORT で売上を降順に並べ替えた後、TAKE で上位 3 行だけを取得します。

  1. 特定期間のクロス表
=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 / WebExcel 2024Excel 2021Excel 2019 以下
動的配列 / スピル対応対応対応非対応
XLOOKUP対応対応対応非対応
LET / LAMBDA対応一部非対応非対応
GROUPBY / PIVOTBY対応非対応非対応非対応
正規表現関数対応非対応非対応非対応

旧バージョンのユーザーとファイルを共有するときは、動的配列の結果が暗黙的なインターセクト演算子で壊れることがあります。値として貼り付け(値のコピー)で静的な結果を残すか、互換関数で置き換えるのが安全です。

おわりに

Excel の新関数は単なる「便利なショートカット」ではなく、作業のやり方そのものを変えるツール です。補助列とドラッグを減らし、一行の宣言的な数式でデータを加工すれば、ミスが減り、保守が容易になります。

最初は XLOOKUP、FILTER、UNIQUE から覚え、慣れてきたら LET と LAMBDA へ進んでください。最後に GROUPBY/PIVOTBY まで手になじめば、「更新のいらないレポート」という新しい作業の流れが開けます。

次の記事では、マウスを離れてキーボードで Excel を扱うショートカット体系を、その次には Power Query でデータ整形を自動化する方法を扱います。

参考資料

Discuss on Twitter • View on GitHub