新しいExcelで追加されるSINGLE関数の使用用途・新Excelの互換性の話も含む

※2019/12/15追記:新しいExcelにはSINGLE関数は搭載されなくなりました。

新しいExcelには8つの関数の追加がありました。
その中でも動作はわかるのですが、使用用途がよくわからない、というSINGLE関数というものがあります。
この関数の使用用途がはっきりしたので、解説します。
本記事内での従来ExcelとはExcel2016、新Excelとは先行機能搭載版であるExcel2016のOffiseInsiderで動作確認したものです。
（OfficeInsiderでもまだ記事内の機能が使えない場合もあります。）

配列式

Excelでは、ある時期から、配列式というものが扱えるようになりました。
今まで計算式は=A1のように1セルしか値として入力できませんでしたが、配列式では、=A1:A10のように範囲を指定できるようになりました。
この結果は、A1からA10の範囲の合計値が求まるわけではありません。結果は1つのセルの値です。
どこのセルの値を参照しているか、同じ列、または同じ行のセルの値を参照しています。
つまり、セルB2に=A1:C1という計算式が入っていれば、セルB1の値を求めます。

セルB2に=A1:A3という計算式が入っていれば、セルA2の値を求めます。

これは何のためにあるのでしょうか。
実は絶対参照と相対参照を簡単に考えられるように作られた仕組みなのです。
横の値と縦の値をかけ、それにA1のセルの値を足すという次のような計算式が料金表などでよく使われます。

この計算式を縦横のセルB3からD5の範囲にコピーしたいところですが、Excelの計算式は横にコピーすれば計算式の参照しているセルも横に移動してしまい、また縦方向でも同じことが起きます。
そこで、動かしたくない行、列の前に$を付ける絶対参照をする必要があります。
今回、セルA1は行列どちらもコピーしても絶対変えたくないので$A$1になります。
セルB2は2行目だけは変えたくなく列は変えたいのでB$2になります。
セルA3はA列だけ変えたくなく、行は変えたいので$A3になります。

このように、列だけとか、行だけとかに$を付ける複合参照になると、いちいち考えなければなりませんし、この考え方自体がExcel習得者のハードルになっていました。
そこで、計算式内のセルの参照はそれぞれこの範囲ですと、範囲指定できれば負担も軽くなるのではないかということで生まれたのが配列式です。
配列式は、計算式のセルがこの範囲を示すと範囲指定すればよいです。ただし、行列とも絶対参照で設定します。これなら、これが列でこれが行と悩む必要がありません。

この数式であれば、あまり考えずにコピーするだけでその範囲に正しい行列を反映した結果を求めることができます。

スピル

スピルとは、Excelの新機能です。
実はこのような行列式をいとも簡単に作ってしまう機能です。
詳しくはこちら↓
新しいExcelの一番の変更点は「スピル」？
以下の表のセルB2に絶対参照を意識しない配列式からなる計算式「=A1+A3:A5*B2:D2」を入力します。

すると、B3からD5の範囲に、計算式を入れただけで、しかも絶対参照もせずに、答えが出ます。

ものすごくインパクトのある機能ですね。
ただ、このスピルで求められた範囲に何かが入力されると、エラーになります。

スピルで求める範囲には何もない空のセルである必要があります。

SINGLE関数

SINGLE関数は、範囲の中から同じ行、または同じ列の1つのセルの値を求めます。
あれ？どこかで聞きましたね。
配列式と同じ動作なのです。
実は後述するのですが、ここにこの関数の存在理由のヒントがありました。
セルB3に入力された、=SINGLE(A2:A4)の答えは範囲内の同じ行のセルA3の値です。
セルD2に入力された、=SINGLE(A1:E1)の答えは範囲内の同じ列のセルD1の値です。
このままでは、何に使うのかイマイチわかりません。
絶対参照で=SINGLE($A$2:$A$4)としても、動作がよくわかりません。

答え～SINGLE関数の存在意義～

でもここで、1つ疑問が出てきました。
従来のExcelの配列式で作成したシートを、新しいExcelで読み込んだらどうなってしまうのだろう？
従来から配列式で作られたシートはたくさんあるはず。もし、それらが新しいExcelで読み込まれたら、すべてスピルと判断されるのか？計算式の周りにはたくさん計算式が入ってるからエラーになってしまうのでは？それはまずい！と。もしそうなるのであれば、マイクロソフト様にフィードバックしなければなりません。
早速、従来のExcelで作った上記のシートを新しいExcelで開いてみました。
結果はこうです。

スピルではなくなりましたが、値がきちんと出ています。
果たしてどんな風になっているのか、1セルごとのスピルになっているのか、もしくは計算式ではなく値になってしまったのだろうか。心配は尽きません。
恐る恐るセルB3の計算式を確認しました。

おおお、なるほど！！と。
配列式がSINGLE関数に変換されています。
なるほど、これならSINGLE関数の答えはあくまで1セルの値なので計算が1セルごとにうまくできます。
これがSINGLE関数の存在意義だったのですね。
まとめると、「従来のExcelで作られた配列式をそのままスピルにせずに、1セルごとの計算式で表せるように変換する関数」ということになります。

もう1つの心配

じゃ、今度は、新しいExcelで作られたSINGLE関数を含むシートを従来のExcelで読み込んだら。
エラーになるかも？
それじゃいろいろ問題になるだろうと。
早速、新Excelで作成し、Excel2016で読み込んでみました。
ついでにスピルで作成したシートもどうなるかやってみました。
Excel2016では、SINGLE関数が取れ、絶対参照付きの配列式になりました。
また、スピルで作成された=A1+B2:D2A3:A5は、CSEという、従来よりあったスピルのように範囲に対して1回の操作で計算式を入力する式{=A1+B2:D2A3:A5}に変更になりました。
つまり、ダウングレードにも対応しているということです。
9月28日午前中に従来のOffice2016の方で割と長い時間のアップデートが発生しましたが、そこでこの対応がされたのかもしれません。
さらに、このファイルを共有機能で従来Excelと新Excelで同時に開くと、従来Excelで配列式で入力すると同時に新ExcelではSINGLE関数が入った式に自動で変換されています。
違うバージョン同士での共有時にもシームレスで作業ができるようになっているようです。

まとめ

今回はSINGLE関数の使いかたと利用価値、存在理由について説明したつもりだったのですが、いつの間にかスピルの互換性の話になってしまいました。
スピルの互換性も、スピルの存在に気付いてから気になっている方も多いと思いますが、ひとまずはこれで互換性を保つと思われます。特殊なケースでエラーが発生することはあるかもしれませんが。
今回のSINGLE関数は、動作内容はわかったのですが、何に使うかがどうしてもわからなくて、スピルでいろいろ試している間に、互換性がまずいかもと思ったところから発見に繋がりました。
これは余談ですが、私はテストエンジニアを数年にわたりやっていた時期がありまして、その時に、実際の利用シチュエーションと照らし合わせた観点でのテストや、退行試験を徹底的に行いました。その経験が生きたのだと思います。テストエンジニアは下流の仕事とよく言われますがマニュアル通りではなく製品仕様を把握しエンドユーザーがどのように使うかまでを意識したテストを行うことで将来何かの発見ができるかもしれません。