LoginSignup
36
30

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ssc-ksaitou(斎藤 研史)inSSC R&D

いつのまにかExcelに生えていた謎機能スピルについて調べてみた

Last updated at Posted at 2025-09-19

スピル（動的配列）とは？

セル上で行列状の複数の値(動的配列)が指定された場合、隣接するセルに値を広げる機能です。動的配列自身のこともスピルと呼んだりします。この記事ではスピルで呼称を統一します。

最近のExcel (2019〜 厳密には2021以降) で利用可能です。

たとえば、スピルを指定する ={1,2,3; 4,5,6} という式を A1 に入れた場合、以下のように長方形状に展開されます。

image.png

行列を指定する式のうち、 , (カンマ) が列方向、 ; (セミコロン) が行方向の区切りです。通常はセルの行列参照(=A1:B3 のような参照形式)としてスピルを用いるでしょうが、この記事では基本的に説明のためにこの表記で進めていくのでぜひ覚えてください。

スピルの表記イメージ
={
    1,2,3;
    4,5,6
}

スピルを展開する先のセルに既に何らかの値がある場合はエラーになります。

image.png

従来の配列数式といった機能はこちらのスピルに統合されたと考えられます。

スピルを扱う数式

通常の演算子

スピル同士は単一の値と同じように二項演算子で結ぶことが可能です。同じ位置の要素について単一の値と同じように二項演算を行うだけのもので、特に行列の掛け算のような難しいものではありません。

image.png

四則演算や ^ (べき乗) や > (大なり比較) など確認した限り全ての二項演算子を適用できるようです。（比較演算子の場合は真偽値の動的配列になります）

ちなみに片方がスカラ（単一）値だったり動的配列のサイズが違う場合は、生成される結果は、大きい方の行数・列数になるようです。

={1,2,3,4,5; 6,7,8,9,10} < 6
// => {TRUE,TRUE,TRUE,TRUE,TRUE; FALSE,FALSE,FALSE,FALSE,FALSE}

# 後置演算子

スピルされた範囲全体を参照するには、# 後置演算子を使用します。使用しない場合はセル部分のみを参照します。

// A1 に ={1,2,3; 4,5,6} が入っている場合

=A1  // => 1
=A1# // => {1,2,3; 4,5,6}

=SUM(A1) // => 1
=SUM(A1#) // => 21

// INDIRECT関数でも同様だが文字列に含めても後置演算子として置いてもどちらでもよい
=INDIRECT("A1#") // => {1,2,3; 4,5,6}
=INDIRECT("A1")# // => {1,2,3; 4,5,6}

関数

関数も同様にスピルを取り扱うものがあります。

行方向と列方向の概念があり、関数が分かれていたり、同一関数で両方の取り扱いがあったりするので、それぞれ使い分ける必要があります。

// TEXTSPLIT: 文字列を行および列方向に分割できる
=TEXTSPLIT("a,b,c", ",") // => ={"a","b","c"}
=TEXTSPLIT("a,b,c", , ",") // => ={"a"; "b"; "c"}

// TRANSPOSE: スピルを転置する
=TRANSPOSE({1,2,3; 4,5,6}) // => ={1,4; 2,5; 3,6}

// SEQUENCE: 連続する数値のスピルを生成
=SEQUENCE(2,3) // => ={1,2,3; 4,5,6}

// UNIQUE: 重複を除去
=UNIQUE({1; 1; 3; 3; 2}) // => ={1; 3; 2} ※ 行方向
=UNIQUE({1,1,3,3,2}, TRUE) // => ={1,3,2} ※ 列方向

// HSTACK: 行方向に結合
=HSTACK({1; 2; 3}, {4; 5; 6}) // => ={1,4; 2,5; 3,6}

// VSTACK: 列方向に結合
=VSTACK({1,2,3}, {4,5,6}) // => ={1,2,3; 4,5,6}

// INDEX: スピルから特定の行・列・要素を抽出 (行 → 列 で1ベースインデックスで指定)
=INDEX({1,2,3; 4,5,6}, 2) // => ={4,5,6}
=INDEX({1,2,3; 4,5,6}, , 3) // => ={3; 6}
=INDEX({1,2,3; 4,5,6}, 2, 3) // => 6

今まであった関数もスピルに対応しているケースが多く、透過的にスピルを扱えます。

=MOD({1; 2; 3; 4; 5; 6}, 2) // => ={1; 0; 1; 0; 1; 0}
=MOD({1; 2; 3; 4; 5; 6}, 2) = 0 // => ={FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}

複数のスピルを引数に取り、それらの要素の順番に関連があるとして処理する関数もあります。（引数のスピル1のN番目と、別引数のスピル2のN番目を比較するような関数）

// FILTER: 第1引数〜第2引数を関連あるスピルとし、後者がTRUEの場合に前者の要素を抽出
=LET(
    products,     {"果物"; "果物"; "果物"; "菓子"},
    prices,       {100;   150;    80;    300},
    prices_preds, products="果物", // => {TRUE; TRUE; TRUE; FALSE}
    FILTER(prices, prices_preds))
// => ={100; 150; 80}

// GROUPBY: 第1引数〜第2引数を関連あるスピルとし、前者をキーとして後者を第3引数の集計関数で集約
// ※ 引数指定で合計は消すことは可能
=LET(
    products, {"果物"; "果物"; "果物"; "菓子"},
    prices,   {100;   150;    80;    300},
    GROUPBY(products, prices, SUM))
// => ={{"果物",330}; {"菓子",300}, {"合計",630}}

// PIVOTBY: 第1引数(列ラベル),第2引数(行ラベル),第3引数(集計値)を関連あるスピルとし第4引数の集約関数で集約したピボットテーブルを動的配列で生成
=LET(
    years,    {2024;  2024;   2025;  2025},
    products, {"果物"; "果物"; "果物"; "菓子"},
    prices,   {100;   150;    80;    300},
    PIVOTBY(products, years, prices, SUM))
// => ={{"","果物","菓子","合計"};
//     {"2024",250,"",250};
//     {"2025",80,300,380};
//     {"合計",330,300,630}}

// XLOOKUP: 第2引数〜第3引数を関連あるスピルとし、第2引数を第1引数で検索し対応する第3引数の要素を抽出する
=LET(
    products, {"果物"; "果物"; "果物"; "菓子"},
    prices,   {100;   150;    80;    300},
    XLOOKUP({"果物"; "菓子"}, products, prices))
// => ={100; 300}
=LET(
    products, {"果物"; "果物"; "果物"; "菓子"},
    prices,   {100;   150;    80;    300},
    XLOOKUP({"果物","菓子"}, products, prices))
// => ={100,300}

スピルがいろいろな演算や関数と透過的に組み合わせられることにより強力な操作が可能になっています。今までシートやセルを分けたりしていたようなことも数式一つで済むようになっています。

VBA関数上のスピル

スピルを入出力する関数を自作する

自作でスピルを扱う関数を定義することも可能です。

渡されたスピルを2倍にして返す関数の簡易な実装例を示します。

VBAProject/モジュール/Module1.bas
Function DoubleSpill(ByVal val As Variant) As Variant
    Dim result As Variant
    result = val ' Range オブジェクトの場合を考慮し Variant(,) に一旦変換する
    For i = LBound(result, 1) To UBound(result, 1)
        For j = LBound(result, 2) To UBound(result, 2)
            result(i, j) = result(i, j) * 2
        Next
    Next
    DoubleSpill = result
End Function

=DoubleSpill({1,2,3; 4,5,6}) // => ={2,4,6; 8,10,12}
=DoubleSpill(A1#) // これでも動く

基本的にはこれでいいのですが、渡す引数が場合によって型や次元が異なるので実際には色々考えないといけません。

  • 基本的にセルに由来する場合: Range オブジェクト
    • rng(1, 2) のようにアクセスはできるが LBound などの概念は Range に無いので、Variant多次元配列と同列に扱うために一旦 Variant に代入すれば問題ない
  • SEQUENCE関数などスピル計算値の場合: Variant(,) オブジェクト
    • ただし計算結果が1行にしかならない場合: Variant() オブジェクト

これでどうなるかというと、以下のようになります

=DoubleSpill(SEQUENCE(1,3)) // => エラー (1次元配列を変換できないので)

// A1 に ={1,2,3} が入っている場合
=DoubleSpill(A1#) // => ={2,4,6} (Rangeオブジェクトから2次元配列に変換されるのでOK)
=DoubleSpill(DoubleSpill(A1#)) // エラー (DoubleSpillの結果が1次元配列になるのでエラー いったんセルに代入してそのセルを参照する分には問題なし)

渡される値のエッジケースを漏れなく考慮する場合、 Range, Variant, Variant(,) のいずれか、あるいはスカラ値が渡されることを考えれば良いと思われます。

スピル範囲の検出

VBAからスピル範囲を検出することもできます：

VBAProject/モジュール/Module1.bas
Function CheckSpillRange$(ByVal r As Range)
    If r.HasSpill Then
        CheckSpillRange$ = r.SpillParent.SpillingToRange.Address
    End If
End Function

=CheckSpillRange(B2) // => (例) "$A$1:$C$2"

Excel外のスピルの扱い

Google Spreadsheetで色々試してみたところ、基本的な概念は同じようですが、Excelにあるようなスピル用の関数があったりなかったり、そもそも配列値をセルにスピルさせる際にARRAYFORMULA(配列数式)関数が外側に必要という感じでした。

また、Excelブックを読み書きするライブラリについてもスピルをサポートしているものが少ない印象です。Go言語の excelize や Java の Apache POI にはパッと見たところ、スピルの直接サポート機能は見当たりませんでした。（POIについては旧来の配列数式には対応していると思いますが）

スピルについて、現状スムーズにExcel外と相互運用できると思わないほうがいいでしょう。 Excel上でデータ分析する人用といった印象です。

まとめ

最近のExcelといえば、LAMBDA関数やPython連携などの新機能が話題ですが、 スピルもかなり強力かつ直感的な機能です。

おそらく本格的にクラウド・先進化する前のExcelで知識が止まっている私のような人が多いと思うので、キャッチアップしていきたいところです。

36
30
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
36
30

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?