📦 プライム30日無料体験を試す
当ブログはAmazonアソシエイト・プログラムの参加者です。Amazonのアソシエイトとして、適格販売により収入を得ています。
Excelのスピルとは 使い方と動的配列関数まとめ
Excelに実装されたスピルですが、最初のころは気にしていなかったし、なんなら使わないようにしていたぐらいでした
意識して使ってみると結構便利だったので、自分の深掘りついでに仕組み・対応バージョン・主要関数・エラー対処まで1本にまとめてみました
この記事では、Excelのスピル(動的配列)を仕組みから整理していきます
「そもそもスピルって何?」という入口から、FILTERなどの新しい関数、よく出る#SPILL!エラーの直し方まで、実際の画面を見ながら広く触れていきます
ジャベ雄配列がシート上にこぼれるイメージがつかめると一気に楽になりますよ
目次
Excelのスピルとは 動的配列の仕組みをやさしく
結論から言うと、スピルは1つの数式が複数のセルに結果をこぼして表示する挙動のことです
スピル(spill)を直訳すると「こぼれる」「あふれる」で、数式を入れたセルだけに収まりきらない結果が、隣の空きセルへ自動的に広がっていきます
個人的な感覚だと1つのセルに範囲(複数セル)が登録されているような状態です
たとえば10行×2列ぶんの値を返す数式を1セルに入れると、その10行×2列ぶんがシート上にずらっと展開されます
Excelの公式では、このあたりを2つの言葉で説明しています
- 動的配列数式…サイズが変わる配列(複数の値)を返せる数式そのもの
- スピル…その結果が隣のセルへ広がって表示される動作
つまり「複数の値を返す数式=動的配列数式」で、「その結果がシートにこぼれる動き=スピル」という関係です
2つはほぼ一体の概念なので、本記事でもセットで扱っていきます
ゴーストってなに 先頭セル以外は薄く表示される
スピルした結果のうち、数式の実体が入っているのは先頭の1セルだけです
2番目以降のセルを選ぶと、数式バーに薄い文字で数式が表示されますが、その場では編集できません
この薄く表示される状態を、公式はゴースト(ghosted)と呼んでいます
幽霊のように見えるけど触れない、くらいの語感です
編集したいときは先頭のセルに戻って直す、と覚えておけば迷いません
スピルのうれしいところ コピー不要で自動更新
従来のオートフィル(数式を1セルずつコピーして広げるやり方)と比べると、スピルは起点の1セルに数式を入れるだけで必要な範囲に結果が広がります
だから手間が減るポイントが分かりやすいです
- 数式をコピーして引っ張る作業がいらない
- 絶対参照($)を細かく意識しなくて済む
- 元データが増減しても結果が自動で伸び縮みする
ジャベ雄表を作り直す手間が減るのが、地味に一番ありがたいんですよね
スピルの対応バージョン 2019以前は使えない理由
ここが一番つまずきやすいポイントなので先に整理します
スピル(動的配列)を新しく入力して使えるのは Microsoft 365 / Excel 2021 / Excel 2024です
| バージョン | スピルの新規入力 |
|---|---|
| Microsoft 365 | ○ 使える |
| Excel 2024 | ○ 使える |
| Excel 2021 | ○ 使える |
| Excel 2019 以前 | × 新規入力は不可(CSEで代替) |
Excel 2019 以前では、複数セルに結果を出す配列数式をCtrl + Shift + Enter(CSE)で確定する必要がありました
このやり方だと結果を出す全セルに同じ数式が入り、サイズも固定です
スピルのように可変サイズで自動展開する挙動は、2019以前にはありません
公式の対応表に2019が載っている件の注意
少しややこしいのが、Microsoft公式の動的配列の解説ページにある「適用対象(Applies To)」です
ここには Excel 2019 / 2016 も並んでいます
公式の対応表に2019が載っているのは、旧バージョンで動的配列入りのファイルを開いたときの互換挙動(後述の@が自動で付く等)に関わるからで、「2019でスピルを新しく作れる」という意味ではないです
ネット上には「スピルは2019から使える」とする記事も混ざっていますが、新規にスピルを入力できるのは 365 / 2021 / 2024 と考えておくのが安全です
2019以前を使っているなら、CSE(Ctrl + Shift + Enter)で配列数式として代替する道が残されています
提供が始まった正確な日付はソースによって食い違うので、本記事では断定しません
Microsoft 365 では2019年ごろから順次使えるようになった、くらいの理解で十分です
バージョンごとの新機能をもう少し追いたい方は、関連記事もどうぞ
Excel 2021の新機能と新関数とExcel 2024の新機能と新関数で、動的配列まわりの追加関数を整理しています
スピルの基本の使い方 範囲を返すと自動でこぼれる
一番シンプルなのは「=範囲」です
数式に範囲を指定するだけで、その範囲ぶんがスピルで反映されます
下の画像のとおり、F1セルに10行×2列のデータが格納されているイメージで、スピルの範囲は青い枠で囲われた状態になります
スピルの範囲にカーソルをあてると、先頭セル以外は数式がグレーになります
これがさきほど触れたゴーストで、スピルで表現されているセルだと分かる目印です
スピルが展開されるセルに空白以外があると、結果を広げられずエラーになります
展開したい範囲は青い破線で教えてくれるので、スピルをあきらめるか、邪魔なデータを削除するかのどちらかを選びます
こぼれ先のセルが空いていることがスピル成立の条件です
うまく広がらないときは、まず青破線の範囲に何か入っていないかを確認すると早いです
スピルする主要な動的配列関数まとめ
スピルが入ったことで、配列を返すタイプの関数が一気に使いやすくなりました
ここでは Excel 2021 / Microsoft 365 で追加された6つの新関数と、スピルと相性のいい関数を整理します
Excel 2021 / 365 でスピル前提の新関数として追加されたのは、次の6つです
- FILTER…条件に合う行・列だけを抽出する
- UNIQUE…重複を除いて一意の値を返す
- SORT…範囲・配列を並べ替える
- SORTBY…別の範囲を基準に並べ替える
- SEQUENCE…連続した数値の配列を作る
- RANDARRAY…ランダムな数値の配列を作る
FILTER関数 条件に合う行だけを抜き出す
FILTERは、指定した条件に一致する行や列だけを取り出して配列で返す関数です
抽出した結果はそのままスピルでこぼれます
=FILTER(配列, 含む, [空の場合])- 配列…抽出元になる範囲
- 含む…条件(TRUE/FALSEの配列)、たとえば「B2:B10="東京"」のような式
- 空の場合…該当が無いときに返す値(省略可)
第3引数の「空の場合」を省略して該当が0件だと、#CALC!エラーになります
該当なしがあり得るデータでは、第3引数に空文字やメッセージを入れておくと安全です
UNIQUE関数 重複を消して一覧を作る
UNIQUEは、リストや範囲から重複を除いた値だけを返します
これまでピボットテーブルで作っていたような「一意のリスト」を、関数だけでサッと作れます
=UNIQUE(A2:A100)FILTERで条件抽出して、UNIQUEで重複を消して、SORTで並べ替える、という3関数の組み合わせは使いどころが多いです
後半の実演でもこの考え方を使っています
SORT・SORTBY関数 並べ替えを数式で
SORTは範囲や配列を並べ替えて返します
SORTBYは「別の範囲を基準にして並べ替える」関数で、複数の基準を重ねて指定できます
=SORT(配列, [並べ替えインデックス], [並べ替え順序], [並べ替え基準])
=SORTBY(配列, 基準配列1, [並べ替え順序1], [基準配列2, 並べ替え順序2], …)SORTBYは基準のペアを複数並べられるので、「第1キーで並べて、同じなら第2キーで並べる」といった多段の並べ替えに向いています
指定できる条件の上限は公式に明記が無いので、ここでは数を断定しません
SEQUENCE・RANDARRAY関数 配列を生成する
SEQUENCEは、指定した行数×列数で連続した数値(等差数列)の配列を作ります
RANDARRAYは、指定した行数×列数でランダムな数値の配列を作ります
=SEQUENCE(5) → 1〜5を縦に
=SEQUENCE(3, 3) → 3行3列に1〜9
=RANDARRAY(4, 2) → 4行2列にランダムな数値RANDARRAYのようにサイズが状況で変わる関数は、後述する#SPILL!エラー(サイズが不定)の引き金になることもあります
そのあたりはエラーの節でもう一度触れます
XLOOKUP・XMATCHもスピルする
検索系のXLOOKUP・XMATCHもスピルに対応しています
検索値や戻り範囲を複数にすると、結果が配列で展開されます
どちらも Microsoft 365 / Excel 2021 以降で使えます
ここはスピルの便利さが一番伝わるところなので、XLOOKUPで具体的に見ていきます
まず普通のXLOOKUPの使い方
はじめにXLOOKUPの引数を確認します
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])XLOOKUP自体の説明は割愛しますが、一番オーソドックスなVLOOKUP味な使い方を見ます
下の式では、A列の中にある検索値と一致する行で、C列の値が返り値になります
検索範囲を複数行×1列、戻り範囲を複数行×複数列にした場合
検索値と一致した行の全列が、スピルで返ってきます
検索範囲を1行×複数列、戻り範囲を複数行×複数列にした場合
検索値と一致した列の全行が、スピルで返ってきます
検索値を範囲にした場合
相対参照でコピペしたときのように、連続して検索したような結果になります
検索値を範囲にして戻り範囲を複数行×複数列にした場合
複数列で返ってきてほしいところですが、残念ながら1列のときと同じ結果になります
万能ではないんですね
ちょっと上級者向けな余談 VLOOKUPと配列
今度はXLOOKUPの先輩、VLOOKUPを例に少し踏み込んだ余談を
何が踏み込んでいるかと言うと、普段Excelで使うことのない配列を使います
列番号を数字が入力されている範囲にしてみる
それぞれの列番号の値がスピルで返ってくるので、「検索値を範囲にした場合」がVLOOKUPでも起こります
列番号を数字の配列にしてみる
Excelではあまり使う機会が無かった配列が、スピルによって活かされることになりました
「{}」で囲うと配列になるので、セル範囲の代わりに使うこともできます
VSTACK・HSTACK・TEXTSPLITも覚えておくと便利
もう少し新しい関数として、VSTACK・HSTACK・TEXTSPLITもスピルする仲間です
- VSTACK…複数の範囲・配列を縦に積み重ねて結合する
- HSTACK…複数の範囲・配列を横につなげて結合する
- TEXTSPLIT…区切り文字で文字列を分割し、配列として返す
これらは Microsoft 365 で先行して使えるようになり、永続版では Excel 2024 に搭載されています
Excel 2021 で使えるかは関数によって違うので、お使いの環境で確認してから使うのが無難です
これらの追加関数はExcel 2024の新機能と新関数で詳しく扱っています
365でいつ何が入ったかを追いたいならMicrosoft 365のExcel新機能 リリース時期も参考になります
スピル範囲を参照する#演算子(A1#)
スピルした結果を、別の数式から「範囲ごと」参照したいときに使うのがスピル範囲演算子「#」です
動的配列の先頭セルに「#」を後ろから付けると、こぼれた配列全体を指せます
=A1 → スピルの先頭の1つの値だけ
=A1# → A1からこぼれた配列全体うれしいのは、参照元のスピル範囲が増えたり減ったりしても、参照側が自動で追従するところです
たとえば=SUM(A2#)と書いておけば、Aの配列が伸び縮みしても範囲を書き直さずに済みます
従来の=SUM(A2:A11)のように固定範囲で書くと、データが12行目まで増えたときに手で直す必要がありました
「#」を使うと、その手直しから解放されます
もちろん他の関数の引数にも渡せます
=SORT(C2#)や=FILTER(C2#, LEN(C2#)>3)のように、動的配列同士をつないでいけます
「#」は閉じている別ブックへの参照には対応していません
その場合は#REF!になるので、参照元のブックを開けば解決します
スピルを抑える@演算子(暗黙的なインターセクション)
「#」と対になるのが@(暗黙的なインターセクション演算子)です
こちらは配列や範囲を「1つの値」に縮める処理が起きる場所を、はっきり示す記号です
少し難しい言葉ですが、噛み砕くと「複数の値の中から1個だけに絞り込む合図」です
旧バージョンのExcelが裏で黙ってやっていた絞り込みを、動的配列対応版では@として見える形にしています
なぜか勝手に@が付くことがある
旧バージョンで作った数式(範囲や配列を返すINDEX・OFFSETなど)を動的配列対応版で開くと、互換のために@が自動で付くことがあります
「なぜか@が増えてる」と驚くかもしれませんが、この自動で付いた@は見た目の目印なので計算結果は変わりません
@を使ってスピルを止める
逆に、自分からスピルを止めたいときは数式の先頭に@を手で付けます
こちらは目印ではなく意図的な操作で、配列が1つの値に縮約されて従来どおり1セルに収まります
=FILTER(...) → 結果がスピルする
=@FILTER(...) → 先頭の1つの値だけ表示(スピルを抑える)@が導入された具体的な日付やバージョンは公式に明記が無いので、ここでは断定しません
動的配列への対応にあわせて導入された記号、と理解しておけば十分です
#SPILL!エラーの原因と対処法
スピルを使っていると出会うのが#SPILL!エラーです
これは「結果を広げたいのに、こぼれ先で何か引っかかっている」というサインです
Microsoft公式は、#SPILL!の原因を見出しで分類しています
主な原因と対処を表でまとめます
スクロールできます
| 原因(公式分類) | 対処 |
|---|---|
| こぼれ先が空でない | 邪魔なセルのデータを削除するか移動する |
| サイズが不定 | RAND・RANDARRAY・RANDBETWEEN など揮発性関数の使い方を見直す |
| ワークシートの端を超える | 列全体ではなく必要な範囲に絞って参照する |
| テーブル内で使った | 数式をテーブルの外に出すか、テーブルを範囲に変換する |
| メモリ不足 | より小さい配列・範囲を参照する |
| 結合セルにこぼれる | こぼれ先の結合を解除するか、別の範囲へ移す |
| 不明・フォールバック | 数式に必要な引数がそろっているか確認する |
一番多いのは「こぼれ先が空でない」
体感で一番多いのは、こぼれ先のセルに値が残っているパターンです
#SPILL!のセルを選ぶと、こぼれたかった範囲が破線で示されます
エラーチェックの通知から「干渉しているセルを選択」を選ぶと、邪魔しているセルへ一発で移動できます
あとはそのセルの中身を消すか動かせば解決です
テーブル内ではスピルが使えない
意外と知られていないのが、正式なテーブル(Ctrl + Tで作るテーブル)の中ではスピルが使えない点です
正確には、複数セルに広がる(スピルする)動的配列数式を入れると#SPILL!になります(@付きで1セルに収まる式なら入ります)
対処は2つで、数式をテーブルの外に出すか、テーブルを通常の範囲に変換(範囲に変換)します
テーブル機能とスピルは住み分けが必要、と覚えておくと混乱しません
「#SPILL!の原因は循環参照」と説明されることがありますが、循環参照はMicrosoft公式の#SPILL!原因リストには含まれていません
循環参照は別の警告として扱われるので、#SPILL!の対処としては上の表の項目を順に確認します
スピルを解除する・使わない方法
「スピルをオフにしたい」という需要は多いのですが、設定からスピル機能そのものを止める項目はありません
その代わり、止めたいときの手段はいくつかあります
STEP
@で1つの値に抑える
数式の先頭に@を付けて、結果を1セルに縮約します(例 =@FILTER(…))
STEP
CSEで従来の配列数式にする
Ctrl + Shift + Enterで確定すると、固定サイズの従来型(CSE)配列数式になります
STEP
スピル範囲を消す
こぼれた範囲をまるごと選んで削除すれば、スピル自体を取り除けます
従来型のCSE配列数式は後方互換のために今もサポートされています
ただ動的配列が使える環境なら、新規はスピル(動的配列数式)で書くほうが扱いやすいです
スピルをふんだんに使ってみた実例
最後に、スピルありきのUNIQUE関数を使って、これまでピボットテーブルで作っていたような集計表を関数だけで組んでみます
まずは手順を動画でどうぞ
この手順は最適解というより、スピルの機能・特徴をあえて使っている例です
ポイントは次の3つになります
- UNIQUE関数で重複しないIDをスピルで反映する
- XLOOKUPの検索値を「セル#」のスピル範囲にして、分類もスピルで反映する
- SUMIFSの検索値はあえてスピルにしていない(スピルした値もちゃんと検索値として使えることを確認しているので、検索値を範囲にすれば一気に反映できる)
ただ表示されるだけでなく、疑似的な値なのに引数としても使えるのがスピルの強みです
使い道は思ったより広いと感じます
スピルの小ネタ コピペや値貼り付けのクセ
使っていて気づいた細かい挙動もまとめておきます
- スピルで反映されたセルは実体が無いからか、Excel内でコピペしても何も貼り付けできない
- ただし「スピルの範囲外に値貼り付け」をしたときはちゃんと反映するし、メモ帳など外部アプリには貼り付けできる
- スピルになる数式が入ったセルをコピーして値貼り付けするとエラーになる
- スピルの結果を値にしたいときは、スピルで反映した範囲を全部コピーして値貼り付けすればOK
- スピルの結果を範囲として数式で使いたいときは、数式が入ったセルに「#」を付ける(A1# など)
よくある質問(スピルのつまずき)
Excel 2019でスピルは使えますか?
新しく入力して使えるのは Microsoft 365 / Excel 2021 / Excel 2024 です
2019以前は新規入力ができないので、Ctrl + Shift + Enter(CSE)の配列数式で代替します
スピルを解除するには?
設定でオフにする項目はありません
数式の先頭に@を付けて1つの値に縮める、CSEで従来型にする、スピル範囲を削除する、のいずれかで止められます
#SPILL!の原因は循環参照ですか?
違います
Microsoft公式の#SPILL!原因リストに循環参照は含まれていません
こぼれ先のデータ・結合セル・テーブル内・サイズ不定・端を超える・メモリ不足あたりを順に確認します
あとがき
スピルは、配列をExcelのシート上に体現した機能と言えます
VBAやプログラミングをやっている人にはちょっとした感動があると思います
今まで無理やりVBAで作っていた処理も、関数だけで完結することが増えました
Excelツール作成がうねっていく感覚があります
同じ流れで、文字列処理を強化するREGEX系の新関数も便利です
あわせてExcelのREGEX関数3つものぞいてみてください
最後に・・・
PythonとExcelを中心に仕事に役立つ業務ツールや自動化、スクレイピングツールの作成を受注していて、クラウドワークスでは気が付けば100件以上のお仕事を受注してきました！
会社員をやりながらの副業なので時間の捻出は相応ですが、クライアントの方々と近い立場でこちらからも提案しながら活動していますのでお悩みあれば是非ご相談ください
コメント