エクセルのちょっといい話（１６）

配列数式は、複数の配列間の計算をまとめて行うことができる、非常に便利な方法ですが、その一方で書式が分かり難いという問題があります。
また、数式を確定するには、単に「Enter」を押すのではなく、「Ctrl＋Shift＋Enter」を押さなければいけません。

そこで、配列数式を使う代わりにそれを配列関数で置き換えることができれば、それに越したことはありません。
ちなみに配列関数とは、配列同士の演算結果をスカラー量として求めるための関数で、SUMPRODUCT関数やSUMXMY2関数などがあります。

今、A1～A10セルとB1～B10セルにデータが入力されており、C1～C10セルでA1*B1～A10*B10の計算結果を求めているものとします。
そして、C1～C10セルの値を合計することを考えます。例えば、適当なセルに「=SUM(C1:C10)」と入力してやれば、容易に求めることができます。

これは、直接的には「=A1*B1+A2*B2+・・・+A10*B10」（①）を求めることに他なりませんが、そのためには一旦、C列という中間的な列を設けてやる必要があるわけです。
もちろん、この程度の計算であれば、①の数式を直接入力することも不可能ではありませんが、まあ、そんなことをする人はほとんど居ないでしょう。

ワークシートに余裕があれば、別にC列を追加することくらい些細なことですが、何らかの事情で中間列を用いずに直接①を計算したい場合もあるかもしれません。
そんな時に配列数式を用いれば、その要求を叶えることができます。

そのためには、数式を「=SUM((A1:A10)*(B1:B10))」として、「Ctrl＋Shift＋Enter」で確定します。
すると、C列を用いることなく、直接①の計算結果を得ることができます。

実はこの程度であれば、わざわざ配列数式を用いなくても、SUMPRODUCT関数を使って計算できます。
そのためには、「=SUMPRODUCT(A1:A10,B1:B10)」とするだけです。

では、「=A1/B1+A2/B2+・・・+A10/B10」の計算はどうでしょう？

配列数式ならば、特に大きな問題はありません。数式を「=SUM((A1:A10)/(B1:B10))」として、「Ctrl＋Shift＋Enter」で確定するだけです。

では、配列関数で表すにはどうしたらいいのでしょうか。SUMPRODUCT関数は、複数（最大30個）の配列同士の掛け算を行なう関数です。
今回のように配列同士の割り算を行なう配列関数は、他に見当たらないように思えます。

しかし、実は配列同士の割り算を行なう場合でも、SUMPRODUCT関数を用いることができます。そのためには、数式を「=SUMPRODUCT(A1:A10,1/B1:B10)」のように表します。
配列の逆数を取るという、一見奇妙な数式ですが、これできちんと計算できます。

そのように考えていくと、どのような配列数式も配列関数で置き換えることができるのではないかと思えてきます。
しかし、事はそう簡単ではありません。

例えばＫＦシステムクリエイターでは、2つの配列間の差の標準偏差を配列数式で表していますが、これを配列関数に置き換えようとして、敢え無く断念しました。
配列関数にはSUMPRODUCT関数以外にも何種類かありますが、それらを組み合わせてもどうにもなりません。

もし仮に、標準偏差の配列数式を配列関数で置き換えることができたとしても、それは恐らく非常に複雑な計算式になるでしょう。
そうまでして配列数式を排除する理由は、もはや見当たりません。

実は、あるマクロの作成において、あるセルが配列数式かどうかを確認する必要があったのですが、最初はその方法が分からなかったため、何とか配列数式を除外しようと考えました。
しかし、いろいろと調べてみたところ、配列数式を確認するプロパティが分かったため、現状はセルに配列数式が含まれていても問題ありません。

配列数式は、複雑な計算処理をたった一つのセルで実現できる便利な手法です。しかしそれだけに、入念な確認作業を怠ると思わぬ計算ミスを犯してしまいます。
特段の事情がない限りは、無理に配列数式を用いるよりも、中間列を用いて段階的に計算していった方が良いのではないかと思います。

タグ：エクセル 配列数式 配列関数 SUMPRODUCT関数 標準偏差 マクロ