「同じデータが何度も出てくるリストから、ユニークな項目だけ取り出したい」「条件に合う行だけ別シートに抽出したい」。こんなとき、ピボットや手作業のフィルタコピペで対応していませんか。
Excel 2021 / Microsoft 365 ではスピル（動的配列）が使えます。スピルを活用すると、これらの作業を数式1本で自動化できます。元データを更新すれば、抽出結果も自動で追随します。
この記事ではスピルの基本概念と、入門で押さえたい4関数の使い方を解説します。UNIQUE・FILTER・SORT・SEQUENCEの基本と、実務で使える組み合わせパターンを紹介します。
Excelのスピル（動的配列）とは？従来の数式との違い
スピル（spill）は「あふれ出す」という意味の英語です。Excelの数式が複数の値を返すとき、その結果が周辺セルへ自動的にあふれ出す動作を指します。
従来のExcelでは、配列を返す数式に Ctrl + Shift + Enter で「配列数式」として確定する必要がありました。スピル対応のExcelなら、普通に Enter するだけで結果が複数セルに展開されます。
スピルの特徴
- 数式は左上の1セルにだけ入力する
- 結果が展開された範囲を「スピル範囲」と呼ぶ
- スピル範囲全体を参照したいときは
A2#のように#（スピル範囲演算子）を付ける - 元データの行数が変わっても、スピル範囲は自動で伸び縮みする
=UNIQUE(A2:A100)この数式を D2 に入れると、結果が D2 から下方向に自動展開されます。これがスピルです。
NOTE
スピル機能はMicrosoft 365 / Excel 2021 / Excel for the web / Excel 2024で利用できます。Excel 2019・2016・2013以前では動作しません。詳しくは記事末尾の互換性セクションを参照してください。
# 演算子（スピル範囲演算子）の使い方
スピル範囲を別の数式で参照するときは、左上セルの後ろに # を付けます。
=COUNTA(D2#)これで D2 から始まるスピル範囲全体の個数をカウントできます。元データが増減してスピル範囲が変わっても、自動的に追随するのが便利なポイントです。
ExcelのUNIQUE関数で重複を一発削除する
UNIQUE関数は、指定範囲から重複を取り除いた一意のリストを返します。「データ→重複の削除」と違い、元データを変更せずに集計用の一覧を作れます。
UNIQUE関数の構文
=UNIQUE(配列, [列の比較], [回数指定])- 配列（必須）: 一意の値を取り出したい範囲
- 列の比較（省略可）: TRUE で列方向の比較、FALSE/省略で行方向の比較
- 回数指定（省略可）: TRUE で「1回しか出現しない値」だけ返す、FALSE/省略で重複を除いた全種類
基本的な使い方
A列に部署名がランダムに並んでいるとき、ユニークな部署名のリストを取り出します。
=UNIQUE(A2:A100)部署名が「営業」「開発」「総務」「営業」「開発」…と並んでいても、「営業」「開発」「総務」の3つだけが返ります。
「1回しか出現しない値」だけ取り出す
第3引数を TRUE にすると、複数回登場する値は除外されます。1回しか登場しない値のみが返ります。
=UNIQUE(A2:A100, FALSE, TRUE)「異常値が1件だけ混入していないか」「ユニークな顧客だけ抽出したい」といった用途で重宝します。
TIP
UNIQUE関数の詳しい使い方や応用例は、個別記事「ExcelのUNIQUE関数で重複データを除外する方法」も参考にしてください。
ExcelのFILTER関数で条件に合う行だけ抽出する
FILTER関数は、条件に合致する行だけを抽出してスピル展開する関数です。オートフィルタの「数式版」と考えると分かりやすいです。
FILTER関数の構文
=FILTER(配列, 含む, [空の場合])- 配列（必須）: フィルタしたい元データの範囲
- 含む（必須）: 「配列」と同じ高さのTRUE/FALSE配列。条件式
(範囲=値)で自動生成される - 空の場合（省略可）: 該当行が0件の場合に返す代替値
基本的な使い方
A〜C列の表から、B列が「営業」の行だけ抽出します。
=FILTER(A2:C100, B2:B100="営業", "該当なし")第3引数に "該当なし" を入れておくと、ヒットがゼロでも #CALC! エラーになりません。代わりに指定した文字列が表示されます。
AND条件とOR条件
複数条件を組み合わせるときは、AND は *、OR は + で連結します。
=FILTER(A2:C100, (B2:B100="営業")*(C2:C100>=100), "該当なし")「営業部署」かつ「数値が100以上」の行を抽出するAND条件の例です。
=FILTER(A2:C100, (B2:B100="営業")+(B2:B100="開発"), "該当なし")「営業」または「開発」を抽出するOR条件の例です。* と + の使い分けがFILTER関数の肝になります。
NOTE
SUMIFSやCOUNTIFSと違い、FILTER関数は条件をかけ算・足し算で表現します。慣れないうちは違和感がありますが、TRUE=1、FALSE=0として扱われるルールから来ています。
TIP
FILTER関数の応用パターンは、個別記事「ExcelのFILTER関数で条件に合うデータだけ抽出する方法」で詳しく解説しています。
ExcelのSORT関数で並び替えを数式化する
SORT関数は、並び替え結果を数式で取得する関数です。元データを並び替えずに、別の場所にソート済みリストを作れるのが強みです。
SORT関数の構文
=SORT(配列, [並び替えインデックス], [並び替え順序], [並び替え基準])- 配列（必須）: 並び替えたい範囲
- 並び替えインデックス（省略可）: 並び替えに使う列番号（1から開始、省略時は1列目）
- 並び替え順序（省略可）: 1=昇順（既定）、-1=降順
- 並び替え基準（省略可）: FALSE/省略で行方向、TRUE で列方向
基本的な使い方
A〜C列の表全体を、C列（売上）の降順で並び替えます。
=SORT(A2:C100, 3, -1)数式を入れた場所にC列降順で並んだ表が展開されます。元データはそのままです。
SORTBY関数で別範囲を基準に並び替える
似た関数に SORTBY関数 があります。並び替え対象と並び替え基準を別範囲にしたいときに便利です。
=SORTBY(A2:B100, C2:C100, -1)A〜B列の範囲を、C列の値を基準に降順で並び替えます。「表示したい列」と「並び替えに使う列」を分けられるのがポイントです。
TIP
SORT関数とSORTBY関数の使い分けは、個別記事「ExcelのSORT関数の使い方」で詳しく扱っています。
ExcelのSEQUENCE関数で連番・行番号を自動生成する
SEQUENCE関数は、連番や行番号を自動生成する関数です。一見地味ですが、他のスピル関数と組み合わせるととても強力になります。
SEQUENCE関数の構文
=SEQUENCE(行, [列], [開始値], [増分])- 行（必須）: 生成する行数
- 列（省略可）: 生成する列数（既定は1）
- 開始値（省略可）: 開始する数値（既定は1）
- 増分（省略可）: 増加する間隔（既定は1）
基本的な使い方
=SEQUENCE(10)1から10まで縦に並んだ連番が展開されます。
=SEQUENCE(5, 3, 100, 10)5行3列の数値表が展開されます。100始まり10刻みなので、1行目は100・110・120、2行目は130・140・150…と続きます。
日付の連番を作る
開始値に日付を渡せば、日付の連番も作れます。
=SEQUENCE(7, 1, TODAY(), 1)今日から7日分の日付がスピル展開されます。表示形式を「日付」に変えれば、週次の予定表のひな形が一瞬で完成します。
TIP
SEQUENCE関数の活用パターンは、個別記事「ExcelのSEQUENCE関数で連続した数値を生成する方法」も参考にしてください。
UNIQUE・FILTER・SORT・SEQUENCEを組み合わせた実務テクニック
4関数の真価は組み合わせにあります。実務でよく使うパターンを4つ紹介します。
パターン1: 重複削除＋並び替え
ユニークな部署一覧を昇順で取得します。
=SORT(UNIQUE(A2:A100))UNIQUEで重複を除いた結果を、SORTでそのまま並び替えています。スピル関数同士はネストできるのが特徴です。
パターン2: 条件抽出＋並び替え＋上位N件
営業部署の行だけ抽出し、売上（C列）の降順で並べます。
=SORT(FILTER(A2:C100, B2:B100="営業", "該当なし"), 3, -1)さらに「上位5件だけ」に絞りたい場合は、Microsoft 365 の TAKE 関数が便利です。
=TAKE(SORT(FILTER(A2:C100, B2:B100="営業"), 3, -1), 5)NOTE
TAKE関数はMicrosoft 365で利用できる比較的新しい関数です。Excel 2021では未対応のため、上位N件の抽出はINDEXとSEQUENCEで代替してください。
パターン3: 動的に伸び縮みするドロップダウンリスト
データ追加で自動拡張するプルダウンを作るには、入力規則のリスト元にスピル範囲演算子 # を使います。
- 任意の作業セル（例:
Z2）に=UNIQUE(A2:A100)を入力 - 入力規則のリスト元に
=$Z$2#と指定
A列にデータを追加すると、プルダウンの選択肢も自動で増えます。OFFSET と COUNTA で組んでいた動的リストが、スピルなら一発で実現できます。
パターン4: ピボット代替のクロス集計ひな形
UNIQUE で集計軸を作り、SUMIFS で値を埋めるとピボット不要のクロス集計が作れます。
=UNIQUE(A2:A100) ' D2 に入力（部署一覧がスピル展開）
=SUMIFS(C:C, A:A, D2#) ' E2 に入力（D2のスピル範囲に対して集計がスピル展開）D2 にUNIQUEの結果を出し、E2 にSUMIFSを入れます。D2# のスピル範囲に対してSUMIFSもスピル展開されるため、元データが増えても集計表が自動で伸び縮みします。
スピル特有のエラー（#SPILL!・#CALC!）と対処法
スピル関数を使い始めると、見慣れないエラーに遭遇することがあります。代表的な2つを覚えておきましょう。
#SPILL! エラー
スピル範囲に何かが入っているためにあふれ出せないときに発生します。原因と対処は次のとおりです。
| 原因 | 対処 |
|---|---|
| スピル範囲のセルに値・数式が入っている | 妨害している値を削除する |
| スピル範囲に結合セルがある | セル結合を解除する |
| Excelの「テーブル」内にスピル数式を入れた | テーブル外で使うか、テーブルを通常の範囲に変換する |
| スピル範囲がワークシートの端を超えている | 行・列の参照範囲を絞る |
TIP
#SPILL!セルを選択すると、エラー三角マークの横に「障害物のあるセルを選択」リンクが表示されます。クリックすると原因セルを特定できます。
#CALC! エラー
FILTER関数で該当データが0件のときに最もよく見ます。第3引数（空の場合）を指定すれば回避できます。
=FILTER(A2:C100, B2:B100="営業", "データなし")ほかに、スピル関数同士のサイズ不整合や、空の配列を返す関数を渡したときにも発生します。FILTERの第3引数を習慣的に書く癖をつけると安全です。
UNIQUE・FILTER・SORT・SEQUENCE関数の対応バージョンと互換性
スピル関数は新しい機能のため、環境によって動作が大きく変わります。
Excelバージョンごとの対応
| バージョン | 対応状況 |
|---|---|
| Microsoft 365 | 完全対応 |
| Excel 2024 | 完全対応 |
| Excel 2021 | UNIQUE/FILTER/SORT/SEQUENCE 対応 |
| Excel for the web | 完全対応 |
| Excel 2019・2016・2013 | 未対応（#NAME? または暗黙的交差で動かない） |
Excel 2019以前で開いたときの挙動
スピル数式を含むxlsxファイルをExcel 2019以前で開くと、数式は自動変換されます。@ 演算子付きの「暗黙的な交差」形式になり、結果は1セルだけでスピル展開しません。複数人で共有するファイルでは、開く側の環境を事前に確認しておきましょう。
Googleスプレッドシートとの互換性
GoogleスプレッドシートにはUNIQUE / FILTER / SORT / SEQUENCEがすべて存在します。ただし引数の仕様が一部異なります。特にFILTER関数のAND/OR表現が違うため、Excel数式をそのままコピペしても動かないケースがあります。両方使うなら、関数ごとに動作確認をおすすめします。
LibreOffice Calcとの互換性
LibreOffice Calcは7.x以降でスピル関連関数の対応を進めています。古いバージョン（6.x以前）では未対応です。
まとめ
ExcelのUNIQUE・FILTER・SORT・SEQUENCE関数とスピル（動的配列）を使えば、集計・抽出作業を数式1本で自動化できます。ピボットや手作業でやっていた工程を一気に省けます。
- UNIQUE: 重複削除でユニークなリストを作る
- FILTER: 条件に合う行だけ抽出する
- SORT: 並び替えを数式化する（元データを汚さない）
- SEQUENCE: 連番・行番号・日付連番を自動生成する
4関数は単独でも便利ですが、組み合わせることで真価を発揮します。「UNIQUE→SORT」「FILTER→SORT」のネストや、# 演算子を使った動的範囲参照をぜひ試してみてください。毎日のExcel作業が一段速くなります。
ピボットを使うほどではないけれど集計したい。フィルタを毎回設定し直すのが面倒。そんなときはスピル関数を試してみてください。
