Excel：スピル機能の使用例

Microsoft 365でExcelに追加された新機能のスピルを使ってみたのでいくつか使用例を紹介したいと思います。

スピルとは

スピルとは数式から返された配列のセルに入りきらないこぼれた値を隣接するセルに返す機能でイメージは以下のような感じです。

  • 数式で配列（表）を返すことが出来るようになった
  • 数式が入力されているセルから右下に向かって数式で返された配列（表）が表示される

スピルのイメージ

スピル機能の詳細は動的配列数式と、こぼれた配列動作 - Office サポートに記載があります。

スピル機能の使用例

それではスピル機能の使用例をいくつか紹介していきます。

九九の表を作る

以下はスピル機能を利用して九九の表を作成する例です。B2のセルには1～9の9行の配列と1～9の9列の配列を掛け合わせて9行x9列の九九の表の配列を返す数式を入力しています。

スピルの使用例1（セルB2）
={1;2;3;4;5;6;7;8;9}*{1,2,3,4,5,6,7,8,9}

結果は以下のようになります。B2のセルに入力した数式で返された九九の表の配列がB2:J10の範囲に返されます。

スピルの使用例1の結果（B2:J10）
123456789
24681012141618
369121518212427
4812162024283236
51015202530354045
61218243036424854
71421283542495663
81624324048566472
91827364554637281

文字列を分割した表をつくる

次にSEQUENCE関数とMID関数を組み合わせて文字列を1文字ずつ分割する使用例を紹介します。

スピルの使用例2（B3）
エクセル
=MID(B2,SEQUENCE(LEN(B2)), 1)

B3の数式はSEQUENCE関数で1～4の連番の4行×1列の配列を返し、B2に入力されている文字列「エクセル」に対してMID関数を以下のように1～4の数値でそれぞれ適用しています。

  1. MID("エクセル", 1, 1)
  2. MID("エクセル", 2, 1)
  3. MID("エクセル", 3, 1)
  4. MID("エクセル", 4, 1)

このようにしてB3の数式から返された配列がB3:B6の範囲にスピル機能で返された結果は以下です。

スピルの使用例2の結果（B3:B6）
エクセル
エ
ク
セ
ル

これまでExcelで文字列を分割したい場合はVBAで関数を作成したりしていましたが標準で使える関数とスピル機能を組み合わせることで簡単に実現できます。

参考リンク

フィボナッチ数列を作る

最後にフィボナッチ数列の5行×5列の表をLET関数とSEQUENCE関数を組み合わせた数式とスピルで作る使用例を紹介します。

まずフィボナッチ数の一般項は以下です。

$$F_n = \cfrac{\phi^n - (1 - \phi)^n}{\sqrt{5}}$$

フィボナッチ数の一般項に現れる \(\phi\) は黄金数で以下で求められます。

$$\phi = \cfrac{1 + \sqrt{5}}{2}$$

上記のフィボナッチ数の一般項を用いてフィボナッチ数列を出力するエクセルの数式は以下です。

=LET(
  n, SEQUENCE(5,5),
  p, (1+SQRT(5))/2,
  (POWER(p,n)-POWER(1-p,n))/SQRT(5))

上記の数式の内容は以下です。

  1. nに1~25の5行x5列の配列を宣言
  2. pに黄金数を宣言
  3. フィボナッチ数の一般項

SEQUENCE関数で作成された連番1～25の各nの値と黄金数pが最後の引数に指定しているフィボナッチ数の一般項で計算されnが1～25のフィボナッチ数列の配列が返されます。この数式をB2に入力した結果は以下です。

スピルの使用例3の結果（B2:F6）
11235
813213455
89144233377610
9871597258441816765
1094617711286574636875025

数式から返された5行x5列のフィボナッチ数の配列がB2:F6の範囲に返されています。

参考リンク

まとめ

スピルはかなり強力な機能です。またMicrosoft 365で追加されたLET関数やSEQUENCE関数と組み合わせることでさらに強力な使い方ができることが分かりました。