スピルについて
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。
数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。
今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。
この革新的なスピルとは何か、その概要について説明します。
出題と解答形式でスピルについて徹底解説しています。
スピルとは:スピル基礎から応用までの問題集
以下で、「従来」または「旧」と呼んでいるのはスピルしないエクセルを指しています。
スピルとは
まずは、従来のエクセルでの「配列数式」と「共通部分の参照」について説明します。
従来のエクセルの挙動
Ctrl + Shift + Enterで数式を一括入力することで、{=数式}のように{}で囲まれた配列数式となります。
この配列数式はCSEと略されます。
例えば、
B1:B3を選択し、=A1:A3をCtrl + Shift + Enterで入力すると、
数式は{=A1:A3}となり、B1:B3はA1:A3を参照するようになります。
これは、同一行または同一列の値のみが返される機能です。
B2セルに=A1:A3と入力すると、A2セルが参照されます。
B3セルに=A1:A3と入力すると、A3セルが参照されます。
B2セルに=A1:C1と入力すると、B1セルが参照されます。
C2セルに=A1:C1と入力すると、C1セルが参照されます。
スピルとは
数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。
スピルは数式の結果の複数の値が隣接するセルに自動的に出力されます。
複数の値を返す配列数式を該当セル範囲の先頭(左上セル)に入力すると、結果の複数の値が入力したセルからこぼれ出して隣接するセルに出力されます。
数式をEnter入力することで、該当セル範囲(スピルする範囲)に結果が出力されます。
スピルする範囲は、数式の結果データの縦横(行列)の大きさによって決まります。
したがって、数式が変更されれはスピル範囲も変わりますし、数式が参照しているセル値によっても大きさが動的に変化します。
スピルによって新しく追加された関数
関数名 | 説明 |
FILTER | フィルターは定義した条件に基づいたデータ範囲です。 |
SORT | 範囲または配列の内容を並べ替えます。 |
SORTBY | 範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。 |
UNIQUE | 一覧表または範囲内から重複データを削除した一覧を返します。 |
RANDARRAY | 0から1までのランダムな数値の配列を返します。 |
SEQUENCE | 1、2、3、4など、配列内の連続した数値の一覧を生成します。 |
XLOOKUP | 範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。 一致が存在しない場合、XLOOKUP は最も近い (概算) 一致を返すことができます。 |
XMATCH | 配列またはセル範囲内の項目の相対的な位置を返します。 |
スピルの数式例
従来のスピルしないエクセルで開いたとき
この数式を従来のエクセルで開いたときは、
{=A1:A3}
このように配列数式に変更されます。
スピルするエクセルで開き直すと、配列数式ではなく元のスピル数式で表示されます。
・配列数式(CSE)で入力した数式は新旧どちらでも配列数式
ゴースト
これはゴーストと呼ばれています。
そして、このゴーストのセルを編集状態にすると、数式も値も何も入っていないことが確認できます。
スピル範囲での独特な挙動について
値貼り付け
では、スピル範囲の一部を値貼り付けするとどうなるか・・・
元のスピルした状態に強制的に戻されてしまいます。
スピル範囲全体でしか値貼り付けできません。
範囲の選択ショートカット
または
Ctrl + Shift + Space
スピルのエラー表示
「#スピル!」 ・・・ 以前は「#SPILL!」でした。
・結合されたセルにスピルすることはできません。
・シートの端を超えてスピルすることはできません。
・テーブル内でスピルさせることはできません。
#CALC!
Excelの計算エンジンが配列で指定されていない計算エラーを検出した場合に発生します。
そのほか、配列の配列や空の配列等々が記載されています。
スピル範囲演算子
このスピル範囲の全体を他の数式で参照する場合は、スピルの数式を入れたセル番地に#を付けて参照できます。
この#を「スピル範囲演算子」と呼びます。
スピル範囲を参照するには、数式において C1# とします。
従来のスピルしないエクセルで開いたとき(_xlfn.ANCHORARRAY)
この数式を従来のエクセルで開いたときは、
=SUM(_xlfn.ANCHORARRAY(C1))
このように変更されて表示されます。
当該セルが再計算されると#NAME?のエラーとなります。
かつ、この再計算を元に戻して値を表示し直すこともできません。
ただし、スピルするエクセルで開き直せば正しく再計算されます。
暗黙的なインターセクション演算子
しかし、スピルするエクセルでは、この数式はスピルしてしまいます。
つまり従来では、「共通部分の参照」として同一行や同一列を参照していたものが、勝手にスピルしてしまいます。
そこで、従来のように「共通部分の参照」としてセル範囲指定を使いたい場合は、セル範囲の先頭に@を付けます。
つまり、@をつけることで従来通りの動作となりスピルしなくなります。
この@を「暗黙的なインターセクション演算子」と呼びます。
従来のスピルしないエクセルで開いたとき
この数式を従来のエクセルで開いたときは、
=A1:A3
このように変更されて表示されます。
そして、従来のエクセルで、
=A1:A3
と入力してあるものは、
スピルするエクセルでは、
=@A1:A3
このように変更されます。
つまり、
従来の「共通部分の参照」と@演算子は相互変換されるという事です。
従来のエクセルと互換性が無い使い方の警告
これは、
=@(A1:A3+B1:B3)
このように数式をいれています。
@が無ければスピルします。
それをスピルさせないようにしたものです。
このような場合、ほとんどの場合は「はい」で良いはずです。
従来の「共通部分の参照」では使えない使い方になっていると思われます。
簡単な例としては、上記数式を別の行、例えばD4セルにいれているような場合です。
スピルするエクセルでは、それでも正しく計算されますが、行がずれているので「共通部分の参照」になっていないため従来のエクセルではエラヘとなります。
さすがに、このような使い方は避けたほうが良いと思います。
従来のスピルしないエクセルとの互換性についての注意点
しかし、問題はスピルです。
関数ではないので、特に意識せずにスピルを使ってしまう事はありえます。
そして、旧エクセルでは配列で表示されるため気づかずに運用してしまう事もこれからは発生しうる思われます。
しかし、
スピル範囲演算子#は、スピルしない旧エクセルでは正しく変換されませんし使う事が出来ません。
セルの数式に使っていれば、_xlfn.ANCHORARRAYが見て取れてすぐに気が付きますが、
名前定義、条件付き書式、入力規則、これらで#演算子を使ってしまうと、従来のエクセルで開いたときに原因を特定するのが難しくなってしまうと思われます。
この点は特に気を付けておくべきでしょう。
スピル関連記事
同じテーマ「エクセル入門」の記事
IFS関数(複数条件)
SWITCH関数(複数値での切替)
XLOOKUP関数とスピル入門
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(VLOOKUP関数を拡張した新関数)
XMATCH関数(MATCH関数を拡張した新関数)
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.条件分岐(Select Case)|VBA入門
9.メッセージボックス(MsgBox関数)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。