レッスン7：動的配列と新世代関数——FILTER・SORT・UNIQUE・XLOOKUP の世界

このレッスンで学ぶこと

動的配列とスピル（はみ出し）の発想を理解する
FILTER で条件抽出を行う使い方を整理する
SORT・SORTBY で並べ替えを関数で行う発想を持つ
UNIQUE で重複削除を関数化する使い方を理解する
XLOOKUP と動的配列の組み合わせを把握する
SEQUENCE で連番を生成する使い方を整理する
LET で式を読みやすくするテクニックを理解する
2026 年 6 月時点の Microsoft 365・Google スプレッドシートでの利用環境を把握する

前のレッスンでは、ピボットテーブルで大量データを 1 分で要約する発想を学びました。今回のレッスンでは、Microsoft 365 が 2020 年に導入した「動的配列」と、その上に成り立つ新世代関数を扱います。FILTER・SORT・UNIQUE などは、ピボットでは難しかった「動的な集計表」を関数だけで実現できる革新的な機能です。

動的配列とスピル——「はみ出す」発想

動的配列とは

これまでの Excel では、1 つの数式は 1 つのセルにだけ結果を返していました。動的配列（Dynamic Array）の導入で、1 つの数式が複数のセルに結果を「はみ出させて」表示できるようになりました。

スピル（spill）——はみ出し

スピル（spill：あふれ出る、はみ出す）は、動的配列で 1 つの数式が複数のセルに値を返す機能の呼び名です。

例：

A1 セルに：
=SEQUENCE(5)

すると A1:A5 に：
A1: 1
A2: 2
A3: 3
A4: 4
A5: 5

と「はみ出して」値が並ぶ

A1 セルだけに式を入力したのに、A1 から A5 までの 5 セルに値が表示されます。これがスピルです。

スピルした範囲の参照

スピルした範囲全体を参照するには、A1# のように # を付けます。

=SUM(A1#)
… A1 にスピルした範囲全体（A1:A5）の合計を計算

スピル範囲が伸び縮みしても、# 参照が自動で追従します。

Microsoft 365 と古い Excel の違い

動的配列とスピルは、Microsoft 365 と Excel 2021 で利用できます。Excel 2019 以前では使えません。Google スプレッドシートはほぼ標準的に動的配列の概念を持っており、FILTER・SORT・UNIQUE などの関数も使えます。

💡 ポイント 動的配列の発想は、関数の使い方を大きく変えます。「式は 1 つのセルに 1 つの結果」という固定観念を捨てて、「式が複数セルに広がる」発想に移ります。

FILTER——条件抽出を関数で

FILTER は、配列から条件に合う行（または列）だけを抽出します。

FILTER の構造

=FILTER(配列, 条件, [見つからない場合])

例：

（元データ：A2:C100 に注文データ）

=FILTER(A2:C100, A2:A100 = "東京都")
… A 列が「東京都」の行だけを抽出

=FILTER(A2:C100, B2:B100 >= 10000)
… B 列が 10,000 以上の行だけを抽出

=FILTER(A2:C100, (A2:A100 = "東京都") * (B2:B100 >= 10000))
… 「東京都」かつ B 列 10,000 以上（複数条件は `*` で AND）

=FILTER(A2:C100, (A2:A100 = "東京都") + (A2:A100 = "大阪府"))
… 「東京都」または「大阪府」（複数条件は `+` で OR）

=FILTER(A2:C100, A2:A100 = "東京都", "該当なし")
… 該当なしの場合に表示する文字列を指定

FILTER の利点

データ追加で自動的に結果範囲が更新される
元データを並べ替えずに、別シートで絞り込み結果を表示できる
ピボットテーブルより柔軟（カスタムレイアウトに使える）

よくある業務での使い方

月次レポートの「特定支店だけの集計」
「未対応」のステータスだけを抽出してタスクリストにする
「金額 100 万円以上」の取引だけをダッシュボードに表示

SORT・SORTBY——並べ替えを関数で

SORT・SORTBY は、配列を並べ替えた結果を返します。

SORT の構造

=SORT(配列, [並べ替えキー番号], [順序], [行・列])

例：

=SORT(A2:C100)
… A2:C100 を A 列で昇順に並べ替え

=SORT(A2:C100, 2, -1)
… 2 列目を降順（-1）に並べ替え

SORTBY——別の配列を基準に並べ替え

=SORTBY(配列, 基準配列1, [順序1], [基準配列2], [順序2], ...)

例：

=SORTBY(A2:C100, B2:B100, -1)
… A2:C100 を、B 列の値で降順に並べ替え

利点

元データを並べ替えずに、別シートで並べ替え結果を表示できる
並べ替えキーや順序を式で動的に変えられる
データ追加で自動更新

UNIQUE——重複削除を関数で

UNIQUE は、配列から重複を除いた一意な値のリストを返します。

UNIQUE の構造

=UNIQUE(配列, [行・列], [一意の値])

例：

=UNIQUE(A2:A100)
… A 列の値から重複を除いたリストを返す

=UNIQUE(A2:B100)
… A 列と B 列の組み合わせで重複を除いたリストを返す

よくある業務での使い方

「ユニーク顧客リスト」を顧客 ID から抽出
ピボットの行ヘッダ候補を関数で動的に作る
アンケート回答のカテゴリ一覧を抽出

COUNTIF・SUMIF と組み合わせる

=UNIQUE(A2:A100)
… ユニークな店舗リストを取得

その横に：
=COUNTIF(A2:A100, スピル先のセル)
… 各店舗の出現回数を取得

これだけで「店舗別件数」が動的に作れます。ピボットを使わずに、同等の集計が可能になります。

📝 補足 UNIQUE + COUNTIF（または SUMIF）の組み合わせは、簡易ダッシュボードによく使われます。データが増えても自動更新する利点があります。

XLOOKUP と動的配列の組み合わせ

レッスン 3 で扱った XLOOKUP も、動的配列と組み合わせると強力になります。

スピル先全体を XLOOKUP の検索値に

=UNIQUE(A2:A100)
… A1 から店舗リストをスピル

その横に：
=XLOOKUP(A1#, 店舗マスター[店舗ID], 店舗マスター[店舗名])
… A1# はスピル範囲全体を参照、それぞれに対して XLOOKUP を実行

ユニーク店舗リストの横に、店舗マスターから「店舗名」を一気に取得できます。これも、データが増えれば自動更新されます。

SEQUENCE——連番を生成

SEQUENCE は、連番を関数で生成します。

=SEQUENCE(行数, [列数], [開始値], [増分])

例：

=SEQUENCE(10)
… 1 から 10 までの連番（縦に並ぶ）

=SEQUENCE(1, 5)
… 1 から 5 までの連番（横に並ぶ）

=SEQUENCE(10, 1, 100, 10)
… 100 から 10 ずつ増える連番（100, 110, 120, ..., 190）

よくある使い方

「No.」列を関数で自動生成
日付の連続日付を作る：=SEQUENCE(30, 1, DATE(2026, 6, 1))
スピル範囲のサイズを動的に決める

💡 ポイント SEQUENCE で連番を作ると、データの増減に応じて自動で番号が振り直されます。手で連番を入力する作業がなくなります。

LET——式に名前を付けて読みやすく

LET は、式の中で計算結果に名前を付けて、読みやすくする関数です。Microsoft 365 で 2021 年 2 月に提供されました。

LET の構造

=LET(名前1, 値1, [名前2, 値2, ...], 結果の式)

例：

=LET(売上, SUM(B2:B100), 原価, SUM(C2:C100), 利益, 売上 - 原価, 利益 / 売上)
… 売上、原価、利益を計算し、利益率を返す

これは、次の式と同じ意味です。

=(SUM(B2:B100) - SUM(C2:C100)) / SUM(B2:B100)

LET のほうが、変数名が付いていて読みやすいことがわかります。

LET の利点

中間結果に名前が付き、読みやすい
同じ計算を 1 回だけ行う（パフォーマンス向上）
長くて複雑な式を整理できる

LET と組み合わせて使う

=LET(
  ユニーク店舗, UNIQUE(A2:A100),
  各店舗の件数, COUNTIF(A2:A100, ユニーク店舗),
  ユニーク店舗 & " : " & 各店舗の件数 & "件"
)
… 「店舗A : 50件」のような文字列のリストを返す

複雑な処理を、人間が読める形に分解できます。

📝 補足 LET はネスト 4 段以上の式を、ぐっと読みやすくします。「動くけど誰も読めない式」を作らないためのキーツールです。

2026 年 6 月時点の利用環境

新世代関数の利用環境を整理します。

Microsoft 365 / Excel 2021

動的配列（スピル）：◎ 利用可
FILTER・SORT・SORTBY・UNIQUE：◎ 利用可
XLOOKUP：◎ 利用可
SEQUENCE：◎ 利用可
LET：◎ 利用可（Microsoft 365 で 2021 年 2 月から）

Excel 2019 / Excel 2016

動的配列：✕ 利用不可
FILTER・SORT・UNIQUE：✕ 利用不可
XLOOKUP：✕ 利用不可
代替：INDEX/MATCH + 配列数式（古典的な書き方）

Google スプレッドシート

動的配列の概念：標準的に対応
FILTER：◎ 利用可（Excel より早くから提供）
SORT・UNIQUE：◎ 利用可
XLOOKUP：◎ 2022 年から利用可
SEQUENCE：◎ 利用可
LAMBDA：◎ Excel と同様に利用可
関数名は Excel と互換性が高い

「Microsoft 365 専用」と「古い Excel」の併用

組織で Microsoft 365 と Excel 2019 が混在する場合、新世代関数を使ったファイルを Excel 2019 で開くと、関数名が壊れて #NAME? エラーになります。配布相手の環境を確認するのが重要です。

⚠️ 注意 「新しい関数のほうが優れている」のは事実ですが、組織内で配布するファイルでは、相手の環境を考慮する必要があります。社内標準が古い Excel なら、新関数の利用は限定するのが現実的です。

「新世代関数だけで、すべてが解決するか」

新世代関数は強力ですが、すべての場面で最適とは限りません。

新世代関数が向く場面

動的にデータが増減する集計
スピル範囲の自動更新が必要
複数の関数を組み合わせる複雑な計算
関数だけで完結する動的ダッシュボード

古い関数が向く場面

配布相手が古い Excel
ファイルサイズを小さく保ちたい
パフォーマンスが重要（大量データのスピルは重くなることがある）
既存シートのメンテナンス

「新世代を学んだら古いものを捨てる」のではなく、「両方読み書きできて、選べる」状態が業務での理想です。

講師の現場メモ：「FILTER 1 つで月次レポートが半分の時間に」

私（水田）が独立後に支援している中堅小売業の話です。経理部の月次レポート作成に、4 時間かかっていました。30 支店分のデータを支店別・カテゴリ別・売上帯別にフィルタリングして、報告書 5 枚に整える作業です。

担当者は、

元データを Excel で開く
データ → フィルタで支店選択
結果をコピー → 別シートに貼り付け
同じ作業を 30 回繰り返す
……

を 4 時間かけてやっていました。

私はその担当者に、FILTER 関数を 1 つ書いてもらいました。

=FILTER(元データ, 元データ[支店] = "支店A")

これを 30 支店分、ピボット形式の別シートに展開します。FILTER は元データが増えれば自動でスピル範囲が伸びるので、月初の元データ更新だけで全 30 支店のレポートが自動で更新されます。

導入後、月次レポートの作成時間は 4 時間から 2 時間（チェック・整形を含む）に短縮。担当者は浮いた 2 時間で、過去 3 年のトレンド分析という新しい仕事に挑戦し始めました。

私が驚いたのは、その担当者が動的配列・スピル・FILTER という言葉を「初めて聞いた」と言ったことです。Excel を 10 年以上使ってきた方ですが、Microsoft 365 への切り替えがあったのが 2024 年で、新世代関数の存在自体を知らないままだったのです。

このときに痛感したのは、ツールの世代交代は社員 1 人ひとりには届かないことが多い、ということです。Microsoft 365 を導入しても、社員教育がなければ、社員は Excel 2019 と同じ使い方を続けます。本コースで新世代関数を 1 レッスン割いて扱うのは、「自分の組織で動いている Excel が、実は新世代関数に対応している」と気づいてほしいからです。

まとめ

このレッスンでは、以下のことを学びました。

動的配列：1 つの数式が複数のセルに結果を返す仕組み
スピル：動的配列で複数セルに「はみ出す」呼び方
スピル範囲全体を参照するには A1# のように # を付ける
動的配列とスピルは Microsoft 365・Excel 2021・Google スプレッドシートで利用可、Excel 2019 以前では利用不可
FILTER：配列から条件に合う行を抽出。複数条件は *（AND）と +（OR）で
SORT・SORTBY：配列を関数で並べ替え。元データを動かさずに済む
UNIQUE：重複を除いた一意なリストを返す。COUNTIF・SUMIF と組み合わせると簡易ダッシュボード
XLOOKUP：動的配列と組み合わせるとさらに強力
SEQUENCE：連番を関数で生成。自動更新されるリストの土台に
LET：式に名前を付けて読みやすく。複雑な数式の可読性を上げる
2026 年 6 月時点：Microsoft 365・Excel 2021・Google スプレッドシートで新世代関数が標準。Excel 2019 以前と混在する組織では配布相手に注意
「新世代を学んだら古いものを捨てる」ではなく、「両方読み書きできて、選べる」が業務での理想

次のレッスンでは、シート設計の 5 原則と業務への組み込み、Google スプレッドシートとの違い、AI 連携の現在地、修了後の学習方向を案内します。

確認クイズ

このレッスンの理解度をチェックしましょう。