スピル(Spill)を利用した計算の例:Excel関数

スポンサードリンク

更新:2025/2/12;作成:2020/2/22

マトリックスの計算例	#SPILL! エラーについて
RANK関数を使った例	SUMIF関数を使った例
SUMIFS関数を使った例	VLOOKUP関数、XLOOKUP関数を使った例
COUNTIF関数を使った例	COUNTIFS関数を使った例

はじめに　　topへ

スピルを使った配列を操作するような数式のことを動的配列数式と呼ぶようです。
一方、従来の配列数式は、[Ctr]+[Shift]+[Enter]キーで数式を確定することから、通称「CSE」と呼ばれているようです。
この辺のことは　動的配列数式と、こぼれた配列動作　をご覧ください。

Excel for Microsoft365 (サブスクリプション版) でスピルが利用できるようになりました
Office365は2020/4/22からは Microsoft365となっています。
ちなみに、このスピル機能はExcel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。

	Spill(スピル)
Excel2016,Excel2019	×
Excel2021,Excel2024	○
Excel for Microsoft365	○

スピルを利用した計算の例　　　topへ

マトリックスの計算例

セル範囲とセル範囲を掛け合わせた計算例です。
D3セルに　=B3:B5*C3:C5　と入力しました。
スピルの機能によって、数式を入力したD4セルに加えて、D6セルまで計算結果が表示されます。

↓
その範囲 D4:D6は青色の枠で囲まれ、D4セルを選択すると、薄いグレーで数式が表示されます。
スピルの機能で作成されたセル範囲(D4:D5)では数式が薄いグレーで表示され、ゴーストと呼ぶようです。
- 従来の配列数式では、D3:D5セルを選択して、数式バーで　=B3:B5*C3:C5　と入力しています。
  
  ↓
  [Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
  {=B3:B5*C3:C5}　と表示されます。
  つまり、上記のスピルの機能は数式を入力するセルをあらかじめ選択して、数式を入力せずに、勝手に必要なセル範囲に数式があふれて入力されるイメージかと思います。
掛け算の九九がダイナミックに見えるかもしれません。
C3セルに　=B3:B11*C2:K2　と入力するだけで一気に計算できます。

従来の配列数式では、C3:K11セルを選択します。
=B3:B11*C2:K2　と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
数式バーには　{=B3:B11*C2:K2} と表示されます。

#SPILL! エラーについて

下図のように、D3セルにSpillを利用した数式を入力したところ、#SPILL! とエラーが表示されました。
原因は、Spill(スピル)機能で、数式が拡張(あふれる)されるセル(青色の破線で囲まれた範囲)にすでに値が入力されているときにエラーとなります。
D5セルに「11」と入力されているのがエラーの原因です。
上記の図では、#SPILL! エラーを解除するには、D5セルをクリアします。
D5セルの値「11」を[Delete]キーでクリアします。
エラーが解消されました。

RANK関数を使った例

C4セルに　=RANK(F3:F8,F3F8)　と入力して、降順で順位を求めています。
Spillが使えない(Excel2019以前)場合は、配列数式を利用できます。
C4:C8を選択して、C4セルに =RANK(F3:F8,F3F8)　と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
数式バーには　{=RANK(F3:F8,F3F8)} と表示されます。

SUMIF関数を使った例

F3セルに　=UNIQUE(C3:C17)　と入力して、UNIQUE関数を使って重複のない品種を取り出します。
販売数の合計をSUMIF関数で条件付きで合計を計算する例です。
G3セルには　=SUMIF(C3:C17,F3#,D3:D17)　と入力しています。
従来と異なるのは、引数の検索条件に F3# とセル範囲を指定しています。これはここでは F3:F5 と同じになります
(範囲のうしろにこぼれた範囲演算子 (#) を指定して、スピル範囲全体を参照することができます。)
スピルの機能によって、数式を入力したG3セルに加えて、G5セルまで計算結果が表示されます。
その範囲 G3:G5は青色の枠で囲まれ、G4、G5セルを選択すると、薄いグレーで数式が表示されます。
- ちなみに、従来は重複しない品種を「重複の削除」などの機能を使って書き出します。
  数式の書き方は　G4セルに　=SUMIF(C$3:$C$17,F3,$D$3:$D$17)　と入力します。
  そして、G4セルをオートフィルで、G6セルまで数式をコピーします。
- または、配列数式では G4:G6セルを選択して、=SUMIF($C$3:$C$17,F3:F5,$D$3:$D$17)　と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。

SUMIFS関数を使った例

F9セルには　=UNIQUE(C3:C17)　と入力して、重複のない品種を取り出します。
G8セルには　=TRANSPOSE(UNIQUE(B3:B17))　として、UNIQUE関数で重複のない月を取り出し、TRANSPOSE関数で列方向へ展開します。
SUMIFS関数を使った、複数条件での合計の計算例です。
G9セルに　=SUMIFS(D3:D17,C3:C17,F3#,B3:B17,G2#)　と入力しました。
条件1の　F3#　と条件2の　G2#　は範囲演算子 (#) を指定して、スピル範囲全体を参照しています。
計算結果が G10:I12 セルに表示されました。
テーブルでスピルを使用すると、非常に便利になります。
データリストをテーブルに変換しています。テーブル名は　テーブル1　となっています。
G9セルの数式は　=SUMIFS(テーブル1[販売数],テーブル1[品種],F3#,テーブル1[月],G2#)　とします。