Excelで数式を入力するたびに、結果を表示したいセルの数だけ数式をコピーしていませんか。スピル(動的配列)とは、1つの数式を入力するだけで結果が自動的に複数のセルへ広がって表示される機能です。
Excel 365やExcel 2021では、UNIQUE関数やFILTER関数などの動的配列関数が使えます。これらをスピルと組み合わせることで、重複のない一覧作成やデータの抽出作業を効率化できます。
一方で、この機能特有の使い方に戸惑う方もいます。数式を入力した直後に#SPILL!というエラーが表示され、原因がわからず困るケースも少なくありません。
この記事では、スピルの基本的な仕組みと使い方を解説します。あわせて、#SPILL!エラーが起こる原因と対処法も初心者向けにやさしく紹介します。スピルの数式もセル参照を使って範囲を指定するため、先に基本を確認しておくとスムーズに理解できます。
スピル（動的配列）とは？関数の結果が自動で複数セルに広がる仕組み
Excelの数式は本来、1つのセルに1つの数式を入力すると、その結果も1つのセルにだけ表示されます。スピル(動的配列)とは、1つの数式が複数の結果を返すとき、周囲の空いたセルへ結果が自動的に広がって表示される機能です。
この自動的に結果が表示される範囲を「スピル範囲」と呼び、薄い青色の枠線で表示されます。
具体的な例で確認してみましょう。次の表は、支店ごとの担当者を記録した一覧です。
| A | B | |
|---|---|---|
| 1 | 支店 | 担当者 |
| 2 | 札幌 | 佐藤 |
| 3 | 東京 | 佐藤 |
| 4 | 札幌 | 鈴木 |
D2セルに次の数式を入力します。
=UNIQUE(B2:B4)
D2セルにこの数式を入力してEnterキーを押すと、D2に「佐藤」、D3に「鈴木」が自動的に表示されます。B2からB4の担当者名から、重複を取り除いた一覧が求められたことになります。
もう一つ、連続した数値を作る例も見てみましょう。A2セルに次の数式を入力します。
=SEQUENCE(3)
A2セルにこの数式を入力すると、A2からA4まで1から3の数値が自動的に表示されます。
| A | |
|---|---|
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
これは、A2セルの数式1つだけで連番を求めた結果です。従来のように1つ目のセルを作ってから下方向にコピーする作業が不要になります。
実務では、重複しない一覧作成や通し番号を振りたいときに役立ちます。数式を1つ管理するだけで済み、ずれるミスも起こりにくくなります。
スピル範囲のセル(前の例ではD3)は、D2の数式が生成した結果の一部であり、個別に内容を書き換えることはできません。
UNIQUE関数の結果セルはなぜ直接編集できないのか
D3セルを選択すると、数式バーにD2と同じ数式が薄い文字で表示されます。これは、D3がD2の数式によって生成された一部であることを示しています。
D3セルに直接値を入力しようとするとエラーになり、変更したい場合はD2の数式自体を修正します。UNIQUE関数に限らず、スピルする数式全般に共通する仕組みです。
連番を振る作業は、従来はオートフィルで下方向にドラッグするのが一般的でした。オートフィルの操作方法や原因は、以下の記事でも紹介しています。
スピルするSORT関数・FILTER関数の使い方はなぜ数式管理を楽にするのか
以前は重複のない一覧や並べ替え結果を作るのに、CSE配列数式や手動の並べ替え・フィルター操作が必要でした。SORT関数やFILTER関数なら数式1つに置き換えられます。
具体的な例で確認します。次の表は、商品ごとの売上を記録した一覧です。
| A | B | |
|---|---|---|
| 1 | 商品 | 売上 |
| 2 | 商品C | 8000 |
| 3 | 商品A | 15000 |
| 4 | 商品B | 11000 |
D2セルに次の数式を入力します。
=SORT(A2:B4,2,-1)
D2セルにこの数式を入力すると、売上の多い順に並べ替えられます。D2からE4に「商品A・15000」「商品B・11000」「商品C・8000」が表示されます。2番目の引数「2」は基準にする列、「-1」は降順を意味します。
元データの売上金額が変わっても、D2の範囲は自動的に並べ替え直されます。手動の並べ替え機能のように、都度やり直す必要はありません。
もう一つ、条件を満たす行だけを抽出する例も確認しましょう。次の表は、商品ごとの売上と担当者を記録した一覧です。
| A | B | C | |
|---|---|---|---|
| 1 | 商品 | 売上 | 担当 |
| 2 | 商品A | 15000 | 佐藤 |
| 3 | 商品B | 9000 | 鈴木 |
| 4 | 商品C | 18000 | 佐藤 |
E2セルに次の数式を入力します。
=FILTER(A2:C4,C2:C4=”佐藤”)
E2セルにこの数式を入力すると、担当が佐藤の行だけがE2からG3に抽出されます。表示されるのは「商品A・15000・佐藤」「商品C・18000・佐藤」の2行です。
実務では、月次データが更新されるたびに並べ替えやフィルターを設定し直す作業が発生しがちです。SORT関数やFILTER関数なら、元データの更新だけで結果が自動的に反映されます。
SORT関数とFILTER関数を使うときの引数の指定順に注意する
SORT関数もFILTER関数も、最初の引数には対象範囲を指定します。この順番を誤ると、意図しない列が結果に含まれます。
FILTER関数の条件式(前の例ではC2:C4=”佐藤”)は、対象範囲と行数を揃える必要があります。行数がずれると、正しく抽出されずエラーになります。
条件式の範囲と抽出したい範囲の行数が同じか、入力前に確認する習慣をつけると安心です。
Excelのフィルター機能を手動で設定する場合の操作手順は、以下の記事で詳しく紹介しています。絞り込みがうまく反応しないときの原因もあわせて確認できます。
スピルの基本構造とやり方｜動的配列関数の入力方法とスピル範囲演算子
スピルを生成する代表的な関数には、UNIQUE関数・FILTER関数・SORT関数・SEQUENCE関数などがあります。いずれも、セルに数式を入力してEnterキーを押すだけで結果が確定します。
以前の配列数式のようにCtrl+Shift+Enterキーを押す必要はなく、この点も扱いやすいとされる理由の一つです。
スピルする数式の結果全体を後から別の数式で参照したいとき、「スピル範囲演算子」と呼ばれる「#」の記号を使います。数式の入力先セルに「#」を付けると、そこから広がった結果全体を1つの範囲として指定できます。
具体的な例で確認します。D2セルに次の数式を入力します。
=SEQUENCE(3)
D2セルにこの数式を入力すると、D2からD4まで1から3の数値が自動的に表示されます。
| D | |
|---|---|
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
続けてF2セルに次の数式を入力します。
=SUM(D2#)
F2セルにこの数式を入力すると、「6」と表示されます。D2#は、D2から広がったスピル範囲(D2からD4)全体を指すため、3つの数値の合計が求められます。
もう一つ、在庫が0の商品だけを抽出する例も確認しましょう。次の表は、商品ごとの在庫数を記録した一覧です。
| A | B | |
|---|---|---|
| 1 | 商品 | 在庫 |
| 2 | 商品A | 0 |
| 3 | 商品B | 12 |
| 4 | 商品C | 0 |
D2セルに次の数式を入力します。
=FILTER(A2:A4,B2:B4=0)
D2セルにこの数式を入力すると、在庫が0の「商品A」と「商品C」がD2とD3に抽出されます。条件に一致する商品名だけが求められたことになります。
実務では、通し番号を振る作業にSEQUENCE関数を使う場面があります。条件に合う商品だけを自動で洗い出す場面でも、こうした関数を組み合わせます。スピル範囲演算子を使えば、行数が増減しても書き直す必要がありません。
スピル範囲演算子(#)を付け忘れるとどうなるか
F2セルの数式を「=SUM(D2#)」ではなく「=SUM(D2)」と入力してしまうことがあります。この場合、D2セル1つの値である「1」しか合計されません。
「#」を付け忘れると、スピル範囲全体ではなく先頭のセルだけが参照され、意図した合計結果にならないことがあります。入力後は「#」の付け忘れがないか一度確認すると安心です。
スピルの実務例と#SPILL!エラーが起こる原因・対処法
ここまで紹介したUNIQUE関数・FILTER関数・SORT関数は、日々の集計作業にそのまま活用できます。手作業で行っていた抽出や並べ替えを、ボタン操作なしで自動化できます。
一方で、スピルする数式を入力した直後に「#SPILL!」エラーが表示され、結果が表示されないことがあります。ここでは代表的な原因と対処法を確認します。
具体的な例で確認します。次の表は、担当者ごとの売上を記録した一覧で、C3セルにはあらかじめ「100」という数値が入力されています。
| A | B | C | |
|---|---|---|---|
| 1 | 担当 | 売上 | 集計 |
| 2 | 佐藤 | 15000 | |
| 3 | 鈴木 | 9000 | 100 |
| 4 | 佐藤 | 18000 |
C2セルに次の数式を入力します。
=UNIQUE(A2:A4)
本来はC2に「佐藤」、C3に「鈴木」が表示されるはずです。C3には「100」が入力済みのため、C2に「#SPILL!」エラーが表示されます。
これは、スピル範囲になるセルが別の値でふさがり、Excelが結果の表示場所を確保できないために起こります。対処法は、C3の「100」を削除するか、数式を別の列に入力し直すことです。
もう一つ、Excelの「テーブル」機能に変換した範囲内では、スピルする数式を使えないという制約もあります。UNIQUE関数やFILTER関数も、テーブル内では同じく「#SPILL!」エラーになります。
対処法は、数式をテーブルの外側のセルに入力することです。テーブル機能自体にも重複除去やフィルターの機能があるため、目的に応じて使い分けるとよいでしょう。
実務では、数式を入力する前に、結果が広がる方向の空白セルを確認しておきましょう。そうすることで、#SPILL!エラーを防ぎやすくなります。共有シートでは特にこの確認を習慣にしておくと安心です。
#SPILL!エラーが起こる2つの原因と対処法の見分け方
#SPILL!エラーが出たときは、まずスピル範囲になるはずのセルに別のデータがないかを確認し、あれば削除か移動で解消します。
見当たらない場合は、数式を入力したセルがテーブル機能の範囲内にないかを確認し、テーブル内であれば外側に入力し直します。
原因を切り分けて確認すれば、初めて#SPILL!エラーに出会っても落ち着いて対処できます。
テーブル機能そのものの使い方は、以下の記事でまとめて紹介しています。構造化参照・集計行・スライサーなど便利な機能もあわせて確認できます。
スピル(動的配列)まとめ｜数式1本で集計作業を自動化できる
スピル(動的配列)は、1つの数式を入力するだけで結果が自動的に複数のセルへ広がる機能です。UNIQUE関数で重複のない一覧作成、FILTER関数で条件に合う行の抽出ができます。SORT関数を使えば並べ替えもでき、手作業や配列数式が必要だった集計をシンプルな数式に置き換えられます。
スピル範囲のセルは直接編集できないことも覚えておきましょう。スピル範囲演算子「#」を使えば、結果全体を後から参照できます。
#SPILL!エラーが出たときは、慌てずにスピル範囲になるはずのセルに別のデータがないか、数式がテーブル機能の範囲内に入っていないかを順番に確認しましょう。原因を切り分けられれば、対処はそれほど難しくありません。
UNIQUE関数やFILTER関数以外にも、Excelには覚えておきたい関数が数多くあります。基本的な関数を振り返りたい場合は、以下の記事もあわせて参考にしてください。
