Excel スピル(動的配列数式)の使い方とメリット・デメリット
スピルは2019年に実装された機能で
数式の量が多いブックに利用するとメリットの大きい機能です。
スピルは配列数式のアップデート版にあたり使いにくさが解消されています。
使用例と従来の方法との比較
例として価格と数量と税率から金額を算出するサンプルで紹介します。
通常の数式
もっとも原始的な方法は一つのセルに数式を作成して
それを別セルにマウス操作でコピーする方法です。
メリット
手軽で一般に知られた方法の数式であることがメリットです。
この方法は小さなシートでは何も問題はありません。
デメリット
シートが大きくなってくると同じ数式が大量に存在することが仇となり
数式を変更した際にコピーを忘れたり、式の破壊が起こりやすくなっていきます。
（間違いに気づきにくいのが致命的）
また税率や手数料など、固定のセル参照がある場合は苦手な人がそれなりに存在し、
付与するのを忘れがちな$(絶対参照)が必要です。
配列数式
この問題を解決するのが配列数式です。
先ほどコピーしたセル範囲を選択した上で価格と数量の部分はセル範囲(:付)で指定します。
ここはキー入力でもマウス操作でも構いません。
この例では「C5:C7*D5*D7*(1+C1)」と入力し、Ctrl+Shift+Enterを押します。
するとセル範囲全てに数式が適用されます。
メリット
このG5～7の何れか一つの数式を変更するとすべてのセルの数式が変わるため
コピー忘れ等のトラブルから解放されます。
また、この方法では$(絶対参照)が不要です。
デメリット
確定のためCtrl+Shift+Enterが厄介で、更新の際にも必要で（Enterだけだと確定できない）
無変更でも離脱できないため面倒が発生します。
最大の問題は配列数式の範囲が変わるような列・行の追加操作が不可能になります。
この場合、配列数式がある限り5～7行目の行追加や削除が出来なくなります。
また範囲内の一部のセルだけは編集が出来ないというデメリットも発生します。
例えばG5とG7はそのままでG6のみ別の数式にするといったことが不可能です。（柔軟性の低下）
この配列数式のデメリットは最後の事項以外はスピルでは解消されています。
マイクロソフトも配列数式からスピルへ移行の方針を出しています。
スピル(動的配列数式)
配列数式を更に便利にしたのがスピル(動的配列数式)です。
1つのセル(I5)だけに配列数式と同様に「C5:C7*D5*D7*(1+C1)」と入力します。
そのままEnterのみで確定するとI5～7に自動的に数式がコピーされます。
このコピーはゴーストと呼ばれる状態で灰色の文字で表示されオリジナルのセル以外では編集も不可能です。
ゴーストの部分は数式が存在しないことになるため
ISFORMULA関数やFORMULATEXT関数はオリジナルのセルにしか使えません。
またスピル範囲内の何れかのセルを選択すると所属するスピルの範囲が薄く強調されます。
もしゴーストのセルに何らかの値を入力した場合、オリジナルのセルが#SPILLエラーになります。
メリット
配列数式のメリットを引継ぎながら、そのデメリットを解消しています。
- Enterキーだけで済む手軽さ(配列数式はCtrl+Shift+Enter)
- 行や列の追加・削除が防がれず、数式も自動調整
またオリジナルのセルに#を付けるとスピル全体の範囲になり関数に組み込むことが可能です。
これにより集計関数側では終端セルを意識する必要がありません。
デメリット
配列数式と同様に一部のセルだけを別の数式に変えるなどの柔軟さは失われています。
（その分、安全性は向上）
またExcel2019以降とOffice365のみの機能で、古いExcelでは使用できない機能です。
（古いExcelで開くと自動調整されるかエラー）
新しい機能なため知られていないデメリットもあります。
画期的ですが、これまでExcelのセルの常識から外れているため
スピルを知らないと何が何だか分からない数式になるのも欠点です。
それを鑑みてもメリットの多い機能なため新しいバージョンのExcelであれば
積極的に利用を検討すると良いでしょう。
無効化はできない
スピルがいらないと感じる方が多いようですが、
アップデートで適用されてしまうとオプション設定で停止したりは出来ません。
スピル関連関数
| 関数 | 機能 |
|---|---|
| FILTER関数 | 条件に一致するデータ一覧を取得 |
| RANDARRAY関数 | 指定範囲内のランダムな数を複数セルに取得 |
| UNIQUE関数 | 指定範囲内(行・列)で重複を削除 |
| SEQUENCE関数 | 連番を複数セルに自動作成 |
| SORT関数 | リストを特定列の昇順・降順で並び替える |
| SORTBY関数 | リストを複数の基準列を指定して並び替える |