このページではJavaScriptを使用しています。スクリプトが無効に設定されているとメニュー等が表示されません。
「スピル」について
「計算式」の配置を含めた新しい概念ではあります。
何か「無精な方法」のお勧めにも見えますが....
「スピル
(Spill)
」というのは「こぼれる」とか「あふれる」という意味だそうです。
下のサンプルを見ていただくと解りますが、通常であれば計算式を入力した算出結果というものは「そのセルのみ」に表示されるものなのですが、 今回の「動的配列数式」を入力をした場合は計算式を入力したセルだけでなく、それより下や右のセルにも算出結果が反映されるものです。 「なくてはならない」機能ではなくて、何やら「無精な方法」にも見えます。
これを「
365
」だけに実装させているのは、マイクロソフトが従来の
Office
ユーザーを「
365
」に取り込みたいという 思惑でもあるような気がするのは私だけでしょうか。
「
365
」の
Excel
の新機能については「
XLOOKUP
」など新関数に目が行きがちですが、 実際に利用できる環境であれば、まずはこの「スピル」を理解する必要があると思います。
「スピル」は「
Office 365
」と「
Office 2021
」以降でしか動作しません。
このページで説明する「スピル」はいろいろな
Office
のバージョンのうち、「
Office 365(現在はMicrosoft 365)
」と「
Office 2021
」にしか実装されていません。
Office2019
の
Excel
でも動作しませんでした。
当然ながら、複数のバージョンの
Excel
が混在する環境では使用できないことを「前提」として運用させて下さい。
一部のサイトではこれらを「
Office2016
の新機能」のように説明しているものを見かけますが、これは「
Office365
サブスクリプション」の契約が行なわれている場合のみです。 買い切り単体バージョンの「
Office2016
」「
Office2019
」ではご利用いただけないのでお間違えないようにご注意下さい。
簡単な例で「スピル」の動作イメージをつかんで下さい。
簡単な売上伝票のようなイメージだと解りやすいと思います。
通常はこのような売上伝票の横計の「金額」列であれば先頭行
(E2セル)
に「
=C2*D2
」と数量×単価の計算式を入力し、 それを下に向かって10行分までコピーすると思います。 これを「スピル」の場合は先頭行
(E2セル)
だけに動的配列数式の「
=C2:C11*D2:D11
」という数量
(セル範囲)
×単価
(セル範囲)
の計算式を入力します。
すると、
Excel
がこの動的配列数式を感知して下または右または右下に向かって必要なセル範囲に計算結果を反映させてくれるというものです。
そのワークブックもしくはワークシートに対して初めて動的配列数式を入力した時にはこのように「数式がスピルされています」のメッセージが表示されます。
上の「数式がスピルされています」のメッセージにある「詳細を表示」をクリックすると、このような説明が表示されます。 「こぼれた配列動作」というのはなじまないと思いますが、まさしくこのような動作のことを指しています。
さて、数式を入力したのは「金額」の先頭行だけです。それ以外のセルはどうなっているのでしょう。
見ると、先頭行と同じ数式は数式バーに表示されますが、グレー表示になっています。 これで実際に動的配列数式を入力したセルか、動的配列数式の作用セルかを見分けるということなのでしょう。
この「スピル」による数式の作用するセル範囲に既に値が入力されていたり、「スピル」による数式の投入以後に作用範囲のセルに別の値が入力された場合は 「スピル」の関係が壊れるため数式投入及びその参照セルは「
#SPILL!
」というエラーが表示されるようになります。
ご理解いただけたでしょうか
古くから
Excel
を利用されている方にとっては「計算式の結果は計算式を投入したセルのみに表示される」ということがいわば「常識」だったのですが、この「スピル」はこの「常識」を覆してしまいます。 「計算式の結果は計算式を投入したセル及びその右下方向の関連セルに表示される」となってしまうからです。
今のところバージョン依存なので利用可否などには注意が必要です。 どちらかというと「無精」な方法にも見えて、特に「無くてはならない」というわけではないようにも見えます。
この「スピル」を利用した「
XLOOKUP
関数
」のサンプルを
「コードから名称を表示」
の中段くらいに追加してみましたのでご覧下さい。
関数
概略説明
XLOOKUP
関数
範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。 一致するものがない場合、最も近い
(
近似
)
一致を返します。
引数は以下の通りです。
(
④以降はオプション
)
① 検索値
② 検索範囲
(
検索する配列またはセル範囲
)
③ 戻り配列
(
返す配列またはセル範囲
)
④ 見つからない場合
(
初期値は
#N/A)
⑤ 一致モード
(
初期値は
0)
値
モードの説明
0
完全一致 見つからない場合は
#N/A
が返される
-1
完全一致 見つからない場合は次の小さなアイテムが返される
1
完全一致 見つからない場合は次の大きなアイテムが返される
2
ワイルドカード一致
(*、?
等
)
⑥ 検索モード
(
初期値は
1)
値
モードの説明
1
先頭の項目から検索を実行
-1
末尾の項目から逆方向に検索を実行
2
昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行
-2
降順で並べ替えられた検索範囲を使用してバイナリ検索を実行
Excel2019
で開いてみました。
上記
(「365」
で作成された動的配列数式のもの
)
のワークブックを
Office2019
の
Excel
で開いてみました。
結果はこのように従来型の配列数式に置き換えられて表示されました。