スピル機能について

ここから LEVEL 6「モダンExcelへの扉」 に入ります。

これまで学んできた Excel は、いわば「伝統的な Excel」。1つのセルに1つの数式を書いて、必要ならコピーして広げる、というのが基本でした。

ここから紹介するのは、現代の Excel だけが持つ新しい機能群です。Microsoft 365 や Excel 2021 以降で使えるようになった、スピル(Spill) という仕組みが、その入り口になります。

「スピル」── 直訳すると「こぼれる」という意味です。何がこぼれるのか、まずは見てもらうのが早いです。

一度に複数のセルを埋める数式

たとえば、こんなシートがあるとします。

:::excel
A1=点数
A2=80
A3=65
A4=92
A5=58
A6=73
:::

ここで、B2 セルに次の数式を入れてみます。

=A2:A6

これまでの感覚なら、「B2 に範囲そのものをセルに入れるってどういうこと？」と戸惑うはずです。実際、古い Excel ではエラーになる書き方でした。

ところが、現代の Excel では、

B2 に式を入れただけなのに、B2〜B6 まで自動で値が並びました。これがスピルです。

数式は B2 に書いただけ。でも結果は、5つのセルに こぼれるように広がって いきます。

B3、B4、B5、B6 には数式は入っていません。B2の数式の結果が、勝手に下のセルにあふれて表示されている ── これがスピルの動きです。

スピル範囲とゴースト

スピルで広がった範囲のことを、スピル範囲 と呼びます。

B2(数式の本体)をクリックすると、B2〜B6 が 青い枠で囲まれて表示 されます。これがスピル範囲の目印です。

一方、B3〜B6 のセルをクリックしてみてください。数式バーには、薄いグレーで =A2:A6 と表示されます。これは「ゴースト数式」と呼ばれる表示で、「ここはスピル範囲の一部ですよ」という印です。

実体は B2 にあって、ここには 影として 結果が映っているだけ、というイメージです。直接 B3〜B6 を編集することはできません。

スピル範囲を変えたいときは、本体のセル(B2)を編集 します。

スピル範囲を参照する「#」

スピル範囲全体を、別の数式から参照したいときがあります。そのときに使うのが、「#(シャープ)」 という記号です。

たとえば、B2 から広がっているスピル範囲を合計したいとき、

=SUM(B2#)

B2# と書くと、「B2 から広がるスピル範囲、全部」を意味します。

いったいどういう意味があるんだろう？　A列をそのまま参照すればいいじゃないか？　と思うかもしれません。

ただ、データをバケツリレーのように「Raw → 一次加工 → 二次加工」のように扱うときもあります。そのとき、スピル範囲を取得するというテクニックは、数式を作成するときにも役に立つときがくるはずです。

スピルの基本として覚えておいてください。

スピルできないとき:#SPILL! エラー

スピルの結果が広がろうとした先のセルに、他の値が入っていた 場合。Excel は「こぼれられない」と判断して、#SPILL! エラーを返します。

B5 に「邪魔なデータ」という文字が入っているせいで、スピルが完了できません。

これは「スピル先のセルを空けてください」というメッセージ。邪魔しているセルを消すか、スピルの開始位置を変えるか、で解決します。

スピル先のセルが何で塞がれているかは、数式バーをクリックすると 点線の枠 で示してくれます。これを目印に、片付けてください。

配列とスピル

ここで少しだけ用語の話をします。

スピルの正体は、Excel が 配列(複数の値のまとまり) を返す機能です。「配列」というと身構えてしまうかもしれませんが、要するに「1つではなく、複数の値が並んだもの」と考えてください。

古い Excel でも配列を扱う仕組みはあったのですが、Ctrl + Shift + Enter を押す必要があったり、扱いが特殊でした。

最頻値を複数出力する「MODE.MULT関数」、度数分布表を作成するときに使う「FREQUENCY関数」、行列を転置して出力する「TRANSPOSE関数」など、｛｝でくくる特殊な数式を使っていました。

スピルは、その配列を 普通の Enter だけで、自然にセルに広げてくれる 機能です。配列という概念を、誰でも使えるレベルに引き下げた、これがモダン Excel の革命でした。

※ 裏事情をお話しすると、Googleスプレッドシートで登場し、それを Excel が追ったという経緯があります。

なぜスピルが革命なのか

スピルが使えるようになったことで、Excel の使い方そのものが変わりました。

これまで:「数式を書いてコピーする」が基本動作

これから:「1つの数式が、必要な範囲に勝手に広がる」

これだけ聞くと小さな違いに思えますが、関数の世界に与えた影響は大きいです。次回から学ぶ UNIQUE関数(重複を除く)、SORT関数(並び替え)、FILTER関数(絞り込み)は、すべて スピル前提で設計された関数 です。

これらは、スピルがあって初めて、その真価を発揮します。

XLOOKUP も、スピルと組み合わせると 一気に複数列の値を取り出せる ようになります。これも後のレッスンで扱います。

まとめ

スピル(Spill) は、数式の結果が 複数セルにこぼれて広がる 機能
数式は本体のセル(左上)にだけ書く。残りはゴースト
スピル範囲を参照するには セル番地# という書き方
スピル先に他の値があると #SPILL! エラー
配列(複数の値のまとまり)を、普通の Enter で扱える革命機能
これ以降の UNIQUE / SORT / FILTER は、スピル前提の関数

次回からは、このスピルを使った 新世代の関数たち に入っていきます。まずは UNIQUE関数(重複を取り除く)から。

ショートカット

※このレッスンに対応するショートカットはありません。