Excel スピル(動的配列数式)の使い方とメリット・デメリット
スピルは2019年に追加された機能で、特に数式が多いブックを改善できます。
スピルは配列数式のアップデート版で、そのデメリットが解消されています。
使用例と従来の方法との比較
例として価格と数量と税率から金額を算出するサンプルで紹介します。
通常の数式
もっとも一般的な方法は一つのセルに数式を作成して、それを別セルにコピーする方法です。
メリット
手軽で一般に知られた方法であることがメリットです。
小さなシートでは何も問題はありません。
デメリット
同じ数式が大量に存在すると、次のような危険があります。
- 数式を変更した時に、別セルへのコピーを忘れる。
- 数式の破壊をしてしまう。
この間違いは気付きにくいため、致命的です。
また税率や手数料率など、固定のセル参照がある場合は苦手な人がそれなりに存在し、
付与するのを忘れがちな$(絶対参照)が必要です。
配列数式
通常の数式のデメリットを解消するのが配列数式です。
まずセル範囲を選択します。通常の数式ではコピーしていたセル範囲です。
そして数式を入力し、価格と数量の部分はセル範囲で指定して、入力の確定はCtrl+Shift+Enterを押します。
下の例ではG5からG7を選択、G5セルに「=C5:C7*D5:D7*(1+C1)」と入力、Ctrl+Shift+Enterで確定します。
するとセル範囲全てに数式が適用されます。配列数式は{}で囲まれて表示されます。
メリット
このG5～7の何れか一つの数式を変更すると全てのセルの数式が変わるため、コピーの漏れが発生しません。
また、この方法では$(絶対参照)が不要です。
デメリット
入力確定のCtrl+Shift+Enterは数式変更の際にも必要です。（Enterだけだと確定できない）
数式を変更していなくても、Enterだけではエラーメッセージが発生します。
更に大きい問題は配列数式の範囲が変わるような列・行の追加操作が不可能になることです。
サンプルの場合、配列数式がある限り5～7行目の行追加や削除が出来なくなります。
この配列数式のデメリットはスピルでは解消されています。
マイクロソフトも配列数式からスピルへ移行していく方針です。
スピル(動的配列数式)
配列数式を改善したのがスピル(動的配列数式)です。
1つのセル(I5)だけに「=C5:C7*D5:D7*(1+C1)」と入力します。
そのままEnterのみで確定するとI5～7に自動的に数式がコピーされます。
このコピーはゴーストと呼ばれる状態で灰色の文字で表示されオリジナルのセル以外では編集も不可能です。
ゴーストの部分は数式が存在しないことになるため
ISFORMULA関数やFORMULATEXT関数はオリジナルのセルにしか使えません。
またスピル範囲内の何れかのセルを選択すると所属するスピルの範囲が薄く強調されます。
もしゴーストのセルに何らかの値を入力した場合、オリジナルのセルが#SPILLエラーになります。
メリット
配列数式のメリットを引継ぎながら、そのデメリットを解消しています。
- 入力確定がEnterキーだけでよい
- 行や列の追加・削除が防がれず、変更した数式が他のセルにも自動反映される
またスピルの数式があるセルに#を付けるとスピル全体の範囲になり関数に組み込むことが可能です。
これにより終端セルを意識する必要がありません。
デメリット
Excel2019以降とOffice365のみの機能で、古いExcelでは使用できない機能です。
（古いExcelで開くと自動調整されるかエラー）
新しい機能なため知られていないこともデメリットです。
スピルは画期的ですが、これまでExcelのセルの常識から外れているため
スピルを知らないと謎の数式になってしまいます。
無効化はできない
スピルがいらないと感じる方が多いようですが、
アップデートで適用されてしまうとオプション設定で停止したりは出来ません。
スピル関数
| 関数 | 機能 |
|---|---|
| FILTER関数 | 条件に一致するセル範囲を取得 |
| RANDARRAY関数 | ランダムな数を複数セルに設定 |
| UNIQUE関数 | セル範囲内(行・列)の重複を削除 |
| SEQUENCE関数 | セル範囲に連番を作成 |
| SORT関数 | セル範囲を並び替える |
| SORTBY関数 | セル範囲を複数列を基準に並び替える |
Excelを効率的に習得したい方へ
当サイトの情報を電子書籍用に読み易く整理したコンテンツを
買い切り950円またはKindle Unlimited (読み放題) で提供中です。
Word-A4サイズ：1,400ページの情報量で
（実際のページ数はデバイスで変わります）
基本的な使い方、関数の解説(140種類)、
頻出テクニックと実用例(109種類)、
XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。
体系的に学びたい方は是非ご検討ください。
アップデートなどの更新事項があれば随時反映しています。
なお購入後に最新版をダウンロードするには
Amazonへの問い合わせが必要です。