Excel スピルに関して補足（暗黙的なインターセクション演算子@ など）

スピルに伴ってExcelに導入された暗黙的なインターセクション演算子@（アットマーク）やその他雑記を書きます．

暗黙的なインターセクション演算子@

これは，旧バージョンのExcelの数式の互換性を保つための演算子です．

旧バージョンのExcelファイルを新しいExcelで開いた際に，必要な箇所に自動で挿入されます． （ただし，そのファイルを再び旧バージョンのExcelで開いた際は，@は無くなります．）

ここにも書きました:

Excel スピル（動的配列数式） とバージョン間の互換性 - kamocyc’s blog

細かい挙動

@演算子の挙動（旧Excelの「共通部分の参照」の挙動）は以下のとおりです．

• 単一の値が渡されたならば，その値をそのまま返す
• セル範囲が渡されたならば，同じ行または同じ列のセルの値を返す
• 配列が渡されたならば，一番左上の値を返す

配列に関して，例えば，3×3の配列定数に対して，新Excelで@を適用すると，

スピルさせると，

となります．

旧Excelのこんな感じでちょっと複雑な配列数式は，

新Excelではこんな感じになりました．*1

旧Excelで挙動の分かりづらかった共通部分の参照が，どこで起きているかわかりますね．

Microsoftのページでは以下のようなことも書いてありました．

• なんで@という記号なの？ ➡ テーブルのカラムを参照する時に，=[@Column1]みたいに書ける機能がもともとあったから．

• @を取り除いてもいい？ ➡ 単一の値を返すなら除ける．@を除いた数式を旧Excelで開くと配列数式になる．

• どんな時に@が数式に付く？ ➡ セル範囲や配列を返す関数に付く（例えばINDEX, OFFSET，セル範囲を返すユーザ定義関数）．例外は，範囲や配列を受け取る関数（SUM, AVERAGEなど）に包まれているときなど」．

スピルのエッジケース

このページに，スピルの問題点について書いてありました．

それを受けて私が試してみた結果を記します．

@が旧Excel用に自動変換されない場合

前の記事で書いたように基本的に@演算子は旧Excelで自動的に取り除かれます．

数式の@が取り除かれていますね．

しかし，@の位置によっては，単純に取り除けないことがあります．

今度は，A1:A3+1に対して@演算子が適用されています．これは，A1:A3+1 = {1;2;3}+1 = {2;3;4}に対して@演算子が適用されて，配列の一番左上の要素が返されています．

この数式は，現在(2019年12月)旧Excelで開くと下記のようにエラーになります．

SINGLEという関数が現れましたが，そんな関数は無いので#NAME?エラーになりました．

SINGLE関数？

SINGLE関数というのは，@演算子の初期の名前です．@演算子が変換できない場合は昔の名残でSINGLE関数が現れています．

なお，新Excelで=@(A1:A3+1)のような旧Excelに自動変換できない数式を入力すると，下記のような確認が表示されます．（Excel 365 デスクトップ版の場合)

なお「代わりにこのバリエーションを使用しますか」と提案された数式は，必ずしも元の式と同じ結果を返すわけではないので気をつけましょう．

ただ，このまま続行すると旧Excelでエラーになる数式になってしまうので，避ける方法があれば避けるべきでしょう．

旧Excelでも@演算子使いたいとき

旧Excelでも明示的に@演算子のように配列の一部を取り出したいときは，代わりにINDEX関数が使えそうです．

書式は以下のとおりです．

INDEX(対象範囲,行番号,列番号)

引数の指定の仕方にバリエーションがあります． また，@とは違って参照の行番号は自動的に設定されません．（@のように，「同じ行または列」とはならない）

数式の入力順で計算結果が変わる

先ほど紹介したページの中で次のような例があげられていました．

D4セルには=A1:A2が，C5セルには=A1:B1という式が入っています． ポイントは，2つの数式がスピルする範囲が重複していることです．

上の図では，最初にC5セルの式を，次にD4セルの式を入力した場合の結果です．

一方，数式の入力順を逆にすると下記のようになります．

#スピル!エラーになるセルが変わっています．どうやら，先に入力した数式を優先させる仕様のようです．

そもそもエラーにならないようにすればいいという話なのかもしれませんが，同じ数式なのに結果が異なるのは少し気持ち悪いです．