答え一発！ 配列数式 Excelの「スピル」とは？：Tech TIPS

Excel 2021／365で追加された新機能「スピル」の使い方を紹介しよう。セル範囲を指定して、一括で計算して、セルに入力できるため、数式をコピーする手間を省くことができる。従来の配列数式に比べて分かりやすく、使いやすくなっている。

[小林章彦，デジタルアドバンテージ]

連載目次

対象：Excel 2021／365

Excelの新機能「スピル」の使い方
Excel 2021／365で追加された配列数式の一種「スピル」を使うと、セル範囲に対して計算が行えるようになる。小計や合計の計算が楽になる。この「スピル」の基本的な使い方を紹介しよう。

　見積表などで合計を計算する際、単価と個数を掛けた値を小計として別の列に計算しておき、それを足すといった方法を使うことが多いだろう。その際、小計を計算するために、「小計」のセルに「=単価*個数」という式を入力して、それを列全体にコピーをするという手間が必要になる。小さな表であれば、それでもいいが、行が多い表となると結構面倒な作業となる。

　このような場合、「配列数式」を使うと簡単に合計が計算できた（配列数式については、Tech TIPS「Excelの配列数式で合計を一発で計算する方法」を参照のこと）。

　ただ、配列数式は、数式を確定する際に［Ctrl］＋［Shift］＋［Enter］キーを押す必要があり、意外とこの操作を忘れがちだ（［Ctrl］＋［Shift］＋［Enter］キーで確定するため、このキーの頭文字を取って「CSE」と呼ばれることもある）。

　Excel 2021／365では、この配列数式をより簡単に利用可能にした「スピル」という新機能が実装されている。新しい機能のため、知らない／利用していない人も多いかもしれない。そこで、この「スピル」とはどういった機能なのか、使い方や注意点などをまとめてみた。

スピルとは

　「スピル（spill）」とは「こぼれる」という意味で、配列数式の一種である。数式の結果が複数ある場合、入力した数式の結果が他のセルに「こぼれて」表示されることから名付けられたようだ。

　A列に単価とB列に個数が並んだ表の小計をC列に計算する場合、「=A2 * B2」を「C2」セルに入力し、C列のセル範囲にオートフィルを使って入力することが多いだろう。スピルを使えば「C2」セルに「= A2:A5 * B2:B5」とすれば、C列のセル範囲（C2:C5）に小計が入力される。数式が1つで済むため、作業効率を大幅に向上できる。

オートフィルを使った小計の計算
「D2」セルに小計を計算する「=B2*C2」を入力し、これを「D3」から「D5」セルに対してオートフィルでコピーする。オートフィルを使っても、行数が多いような場合は少し面倒だ。

スピルを使った小計の計算
スピルでは、「D2」セルに「=B2:B5*C2:C5」とセル範囲同士の掛け算を入力する。行数が多い場合でも、行番号を見ることでセル範囲の指定が行えるので、それほど面倒ではない。この1つの数式だけで、指定したセル範囲の小計の計算が行える。

　値が確定したら、セル範囲をコピーして、数値として貼り付けることで、スピルによる数式から数値に変換することも可能だ。

配列数式とスピルの違い

　配列数式を使っても、同様の計算は可能だ。この場合、先にC列のセル範囲（C2:C5）を選択した状態で、「= A2:A5 * B2:B5」を入力、［Ctrl］＋［Shift］＋［Enter］キーで配列数式として確定すればよい。

　配列数式もスピルも、C列に入力された数式の1つだけを修正するといったことはできない。スピルの場合は、入力したC列の「C2」セルのみに数式が入力され、他の「C3」から「C5」セルは「ゴースト」と呼ばれる編集不可能（数式バーではグレーで表示される）なセルとなっているので、修正は「C2」セルの数式で行う。

　配列数式の場合は、同じ数式がセル範囲（C2:C5）に入力されているので、いずれかを修正して［Ctrl］＋［Shift］＋［Enter］キーで配列数式として確定すれば、セル範囲全てに修正が反映される。

配列数式を使った小計の計算
スピルは配列数式の一種なので、数式の指定方法は似ている。ただ、配列数式の場合、入力したいセル範囲を選択した上で、「D2」セルに「=B2:B5*C2:C5」と入力し、さらに［Ctrl］＋［Shift］＋［Enter］キーを押して、配列数式として確定する必要がある。

スピルを使ったXLSXファイルの互換性は？

　前述の通り、スピルはExcel 2021／365の機能で、Excel 2019以前では対応していない。そのため、スピルを使うと「Excel 2019以前ではデータがおかしくなってしまうのでは？」と思い、使いにくいかもしれない。

　しかし編集部で試したところ、Excel 365でスピルを使った表を、Excel 2013（既にサポートが切れているが）で開いたところ、スピルの部分は配列数式に変換されて表示できた。完全に後方互換性が保証されているわけではないが、スピルを使ってもExcel 2019以前で表が開けなくなるようなことはないようだ。

スピルの後方互換性は？
スピルは、Excel 2021／365で導入された機能のため、それ以前のExcelではエラーになってしまうのが気になるところだ。編集部で試したところ、以前のExcelではスピルで入力したセルは、配列数式として認識され、互換性に大きな問題は生じないようだ。

スピルで合計を計算する

　見積表などで合計を計算する場合、各項目の小計を計算してから、それを合計するという方法がよく使われる。ただ、合計だけを算出したいのであれば、小計を計算しなくても、スピルを使って一発で計算できる。前述の表であれば、「= SUM(A2:A5 * B2:B5)」とすればよい。

スピルで合計を計算する
スピルを使うと、小計を計算し、それを合計することなく、一度に合計が計算可能だ。

スピルを使うと絶対参照が楽になる？

　「割引率」や「消費税率」をセルに入力した表の場合、オートフィルでコピーすると、セル参照が相対参照となってしまい、「割引率」や「消費税率」が正しく反映されない、といったミスを起こしがちだ。

　スピルを使うと、「割引率」や「消費税率」が入力されたセルを相対参照しても、指定範囲内のセルから正しく参照されるように自動設定されるため、絶対参照する必要がなくなる。ただし、表の外側にあるセルを相対参照しているスピルの数式を別の表にコピー&ペーストした場合、参照先がズレてしまうので注意が必要だ。

　また、スピルの結果のセル範囲を参照すると、「D2#」といったように、数式を入力したセル名にスピル範囲演算子「#」が付いたものに変わる。スピルのセル範囲は、このような形式で表現できるため、マウスでセル範囲を選択しなくても、簡単に指定可能だ。

スピルを使うと絶対参照が楽になる
セル参照を行う際、オートフィルなどでコピーすると、参照セルがずれてしまうことがあるため絶対参照にする必要がある。スピルの場合、セルを絶対参照する必要がないため、参照に対するミスを減らすことができる。

九九の表を作成する

　ここまで列方向に対するスピルで説明してきたが、行方向や行と列の2方向に対してもスピルは可能だ。

　例えば、掛け算の九九の表を作成する場合、スピルを使わないと、以下のような数式を入力して、これを九九の表内にコピーする必要があった。

従来の方法による九九表の作成
「B2」セルに絶対参照と相対参照を組み合わせた掛け算を入力し、それを表全体にオートフィルでコピーする。

　スピルを使えば、以下のような数式を「B2」セルに入力するだけで、九九の表ができる。

スピルによる九九表の作成
「B2」セルにセル範囲同士の掛け算を入力すると、九九表が作成できる。

　このように行と列の2方向に対してもスピルは利用できるので、マトリックス表などの計算にも適用可能だ。

スピルを使って日付から曜日を自動入力する

　スピルは、数値だけでなく、TEXT関数やRIGHT関数、LEFT関数などの文字列操作関数でも利用可能だ。

　例えば、アルバイト従業員の出勤簿などのように、日付の隣に曜日を入力しておきたい場合、「B2」セルに「=TEXT(A2:A31,"aaa")」と入力すれば、日付の隣に曜日が入力できる（日付から曜日を入力する方法は、Tech TIPS「Excel（エクセル）で日付から自動的に曜日を入力する」参照のこと）。

スピルを使って日付から曜日を自動入力する
スピルを使うと、日付から曜日を自動入力することも可能だ。「B2」セルに「=TEXT(A2:A31,”aaa”)」と入力すれば、11月1日から11月30日までの曜日が入力できる。

　同様に、Tech TIPS「構造化Excelテク：小難しい関数を使わず、住所を都道府県とそれ以下に分離する方法」で取り上げた住所から都道府県を分けるような文字列操作も、関数内でスピルを利用できる。

スピルを使って住所から都道府県を分ける
スピルを使うことで、住所から都道府県名を除いた住所を入力することも可能だ。行数が分かっているのであれば、オートフィルでコピーするよりも手っ取り早く入力できる。なお、画面で使用した住所は、「テストデータ・ジェネレータ」で自動生成したものである。