January 29, 2015

ExcelのCORREL関数等、配列を引数とする関数では

Excelには様々な便利なワークシート関数があるのですが、correl関数をrow()関数、column()関数と合わせて使用したときに詰まったのでメモします。

以下の式はエラーを返します。
=CORREL(A2:A6, OFFSET(A2, 0, column(), 5,1)

上記の式をB1セルに入力した場合、A2:A6の配列と、B2:B6の配列の相関係数が返ってくることを期待するわけですが、エクセルはエラーを返します。原因が分からず散々調べた上で以下のような記事に行き当たりました。

 Re: Dynamic Correl() formula - works with match(), fails with row() and column()
http://www.excelforum.com/excel-formulas-and-functions/1057831-dynamic-correl-formula-works-with-match-fails-with-row-and-column.html
上の記事の参照先:
http://excelxor.com/2014/08/25/row-vs-rows-for-consecutive-integer-generation/

なるほど、row()関数やcolumn()関数の返り値はなんと、配列だったのですね。全く意識せずに使っていたため、今回のようなエラーに遭遇したわけです。通常、A1セルに
=ROW()
と入力すれば1行目ですので1が返ってくるので、返り値は当然のように整数だと思い込んでいたのですが、実際には、エクセルが内部で配列である返り値{1}を数値である1に自動変換した上で1が表示されていた訳です。

したがって解法としては、上記の相関係数の例ですと、
=CORREL(A2:A6, OFFSET(A2, 0, columns($A:B), 5,1)
となります。
columnsは列数を数値として、rowsは行数を数値として返すので、上記のようにrow、column関数を使った場合のエラーが発生しなくなるのですね。

ちなみに、配列が返った時にどうしてエラーになるのかは参照先の記事に詳しく書かれているのですが、要は

=CORREL(A2:A6, OFFSET(A2, 0, column(), 5,1)
=CORREL(A2:A6, OFFSET(A2, 0, {2}, 5,1))

となって、OFFSET関数の第3引数は数値でなければならないのに{2}と配列になっているためにエラーが起きてしまいます。
興味深いのはsum関数の場合は同様な表記でもエラーなく動いてしまうことです。配列も整数も取れるような関数と配列しか取れない関数では内部的な実装が違うのでしょうか。

結論としては、row(),column()は配列が返り値として欲しい場合に限定し、原則としてrows()、columns()を使ったほうが安全です、ということですね。

No comments :

Post a Comment