スピルができて、配列数式はどこいった【Excel】

ExcelはMicrosoft365を使っています。スピルが便利ですね。あまりにも便利すぎて、配列数式の苦労を忘れそうです。配列数式・・・ご存じですか？Excelで一度に複数のセルに数式を当てはめることができるテクニックなんですが、要はスピルと同じです。そういえば、配列数式はどこにいったのだろう、なくなったのかな？と疑問がわきました。

配列数式は健在

早速試してみました。

こちらが、配列数式を使って、昨日のブログで紹介した文字列から複数の数値を取り出す式です。

数式バーのところに{}が付いた数式が入っているのが分かりますでしょうか。これが配列数式です。

CTRL+SHIFT+ENTERを押さないと{}が挿入されないこと、あらかじめ配列数式を適用したい範囲を選択した状態で入力しないといけないこと、いったん配列数式が適用されたセルは一つの大きな配列と化すので削除や改変ができなくなること等、使い勝手が悪いことこの上ないのが配列数式です。

配列数式を知っている人は少なかったです。アプリ会社でも使っている人はほとんどいなくて、一般の事務仕事をする会社では皆無でした。そもそも使いどころが難しいのでしょうね。

スピルができて、配列数式の使いどころが減った

昔私は配列数式はVBAの戻値として使っていました。

主として1次元配列として使うことが多かったです。

あるINPUTの値を引数として与えると、OUTPUTに複数の値が返るようFunctionを作って、Excelシートで取り出すときは配列数式(CTRL+SHIFT+ENTER)としていました。

Public Function Test1(Rng As Range) As Variant
    Dim ret(1 To 4) As Variant
    Dim a1, b1, c1, d1
    Dim a2, b2, c2
    
   'INPUT
   'Rangeから入力値を取り出す
    a1 = Rng(1, 1).Value
    b1 = Rng(1, 2).Value
    c1 = Rng(1, 3).Value
    
    
    'プログラム
    'C言語のプログラムをVBAに置換
    a2 = a1 + b1
    b2 = b1 + c1
    c2 = c1 + 1
    If b1 > a1 Then
        d1 = 0
    Else
        d1 = 1
    End If
    
    'OUTPUT
    'プログラムの結果を戻値として返すために配列に格納する
    ret(1) = a2
    ret(2) = b2
    ret(3) = c2
    ret(4) = d1
    
    '戻値
    Test1 = ret
End Function

このVBA自体には意味はないです。私が即興で作りました。

一方、ワークシート側ではこのように入力値の列と期待値の列とが用意されていて、配列数式は期待値の列で使います。

これは、C言語の単体テストでよくある形式です。入力値を与えたら、期待値にどんな値が出されるか予想しておいて、実際にテストで確認します。そのときの資料がこんな感じです。

これを手動で作ってもよいのですが、テストデータも自動で作った方が工数が減らせるので、使えるもの(VBA)はじゃんじゃん使っていました。

そこで活躍していたのが配列数式です。

しかしスピルの登場によって、これも不要となりました。

試しにスピルでやってみたら、簡単に実装できてしまいました。

これは便利！

配列数式の使いどころがもう私には見つけられませんね。

使うとしたら、スピル非対応のExcelを使っている顧客とのやり取りがある場合でしょうか？スピル非対応のExcelで開いた場合、スピル部分は配列数式に調整されるそうです。再びスピル対応のExcelで開くとちゃんとスピルとして表示されるそうです。

従来のスピルしないエクセルで開いたとき
=A1:A3
この数式を従来のエクセルで開いたときは、
{=A1:A3}
このように配列数式に変更されます。
スピルするエクセルで開き直すと、配列数式ではなく元のスピル数式で表示されます。

ただし、従来のスピルしないエクセルで配列数式として入れ直してしまうと、スピルするエクセルで開いた時にも配列数式のままとなります。

・スピル数式をスピルしないエクセルで開くと配列数式で表示
・配列数式(CSE)で入力した数式は新旧どちらでも配列数式

https://www.excel-ubara.com/excel1/EXCEL628.html

太字部分は筆者です。

非スピルExcelで配列数式として入れなおすと、スピル対応Excelで開いたときに配列数式のままとなってしまうのであれば、互換性を考えて配列数式で最初から作成しておく方が効率が良いでしょう。

まとめ

スピルができて配列数式の使いどころが減りました。

今後はどんどんスピルが市民権を得ていくでしょうね。

配列数式は互換性として残されていますが、今後はスピルが使えるならスピルで作業した方が便利だと思います。