今回は、Excelのスピル範囲演算子「#」を使って、スピルした結果の範囲をまとめて参照する方法を紹介します。
SORT関数やUNIQUE関数のように複数の値を返す数式を使うと、結果は隣のセルへ自動的に広がります。この広がった範囲を別の数式で使うとき、「A2:A11」のように範囲を手入力していると、データ件数が変わったときに範囲がずれてしまいます。そこで役立つのがスピル範囲演算子です。この記事は、動的配列数式に対応したExcel（Microsoft 365版など）を前提にしています。動的配列に対応していないバージョンでは、ここで紹介する数式は同じようには動作しません。
スピルとスピル範囲演算子の基本
スピルとは
スピルとは、1つの数式が複数の値を返したときに、その値が隣接するセルに自動で配置される動作のことです。たとえば、10件の値を並べ替える数式を左上のセル1つに入力すると、結果が下方向へ10行分広がります。数式を入力したのは1セルだけですが、結果は範囲として表示されます。
このように、結果の大きさが変わる可能性がある数式は動的配列数式と呼ばれます。元データの件数が増えれば、結果の範囲も自動で広がります。
スピル範囲演算子「#」の役割
スピル範囲演算子は、数式を入力したセルの後ろに「#」を付けるだけで、スピルした範囲全体を参照できる記号です。
たとえば、セルA2に「=SEQUENCE(10)」と入力すると、1から10までの数値がA2からA11に表示されます。このとき、別のセルに次の数式を入力します。
- =SUM(A2#)
この「A2#」は「A2の数式がスピルした範囲」を意味するため、結果は「=SUM(A2:A11)」と同じになります。さらに、A2の数式を「=SEQUENCE(20)」に変えてスピル範囲が広がると、SUMの対象範囲も自動で追従します。範囲を手で直す必要がない点が便利です。
スピル範囲演算子の使い方
入力の手順
- 動的配列数式（SORT、UNIQUE、FILTER、SEQUENCEなど）を、結果を表示したい範囲の左上のセルに入力する
- 結果がスピルしたことを確認する
- 別のセルで、参照したい位置に「左上のセル番地＋#」を入力する
セル番地の後ろに「#」を付けるだけなので、範囲の終わりのセル番地を調べる必要はありません。
数式をつなげて使う例
スピル範囲演算子は、複数の動的配列数式を段階的に組み合わせるときに力を発揮します。たとえば、名前の一覧から重複を除いたリストを作り、それを並べ替えたり、条件で絞り込んだりする流れは、次のように書けます。
- セルC2に「=UNIQUE(A2:A20)」と入力し、重複のない名前の一覧を作る
- セルE2に「=SORT(C2#)」と入力し、その一覧を並べ替える
- セルG2に「=FILTER(C2#,LEN(C2#)>3)」と入力し、3文字を超える名前だけを取り出す
どの数式も「C2#」で一覧全体を参照しているため、元の名前が増えたり減ったりしても、後続の数式の範囲は自動で合わせてくれます。途中経過をセルに残しながら処理を組み立てられるので、1つの長い数式にまとめるよりも、内容を確認しやすくなります。
件数や合計を求める
スピルした結果の件数や合計を知りたい場合も、演算子が役立ちます。
- 件数を数える：=COUNTA(C2#)
- 合計を求める：=SUM(E2#)
- 行数を調べる：=ROWS(C2#)
一覧の下に「全○件」と表示しておくと、抽出結果の確認がしやすくなります。
スピル範囲を扱うときの注意点とコツ
編集できるのは左上のセルだけ
スピルした範囲のセルを選ぶと、範囲の周りに枠線が表示されます。範囲内の2つ目以降のセルを選んだ場合、数式バーには数式が薄く表示されますが、そのセルでは編集できません。数式を直したいときは、範囲の左上のセルを選んで修正します。Enterキーで確定すれば、残りの範囲も自動で更新されます。
参照するときも同じで、「#」を付けるのは数式を入力した左上のセルです。範囲の途中のセルに「#」を付けても、意図した参照にはなりません。
#SPILL!エラーが出たとき
スピルする予定の範囲に、すでに値や別のデータが入っていると、結果を配置できずに#SPILL!エラーになります。主な原因と対処は次のとおりです。
- スピル範囲が空ではない：エラーのセルを選ぶと、本来スピルする範囲が破線で示されます。エラーチェックのアイコンから［障害物セルの選択］を選ぶと、邪魔になっているセルへ移動できるので、その内容を削除するか別の場所に移します
- 結合されたセルがある：スピルは結合セルには配置できません。結合を解除するか、数式を別の場所に移します
- テーブルの中に入力している：スピルする数式はExcelのテーブル内ではサポートされていません。テーブルの外に数式を置きます
- ワークシートの端を超える：列全体を対象にした数式などで、結果が多すぎるとシートの端に達してしまいます。必要な範囲だけを参照するように見直します
元データはテーブルにしておくと便利
スピルする数式そのものはテーブルの中に置けませんが、元データをテーブルにして、構造化参照で指定する方法は相性が良い組み合わせです。テーブルに行を追加すると参照範囲が自動で広がり、その結果がスピル範囲に反映され、さらに「#」で参照している数式にも伝わります。データを追加するたびに範囲を直す作業を減らせます。
閉じたブックへの参照には使えない
スピル範囲演算子は、閉じているブックへの参照には対応していません。別のブックのスピル範囲を「#」で参照している場合、そのブックを閉じると#REF!エラーになります。参照元のブックを開けば解消されるので、ブックをまたいで使うときは両方を開いた状態で作業しましょう。
まとめ
Excelのスピル範囲演算子「#」を使うと、動的配列数式がスピルした範囲を、件数の増減に合わせて自動で参照できます。
- 数式を入力した左上のセル番地の後ろに「#」を付けると、スピル範囲全体を参照できる
- SORT、UNIQUE、FILTERなどを段階的につなげると、途中経過を確認しながら処理を組み立てられる
- COUNTAやSUMと組み合わせると、件数や合計を範囲の変化に合わせて求められる
- 数式の修正は左上のセルで行い、#SPILL!エラーは障害物セルや結合セルを確認する
- 元データをテーブルにすると、データ追加から集計までの流れが自動でつながる
範囲を手入力している数式を見直すと、「#」に置き換えられる箇所が見つかるかもしれません。小さな表で試してから、普段の集計表に取り入れてみてください。