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完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。