先日、こちらの記事で、SUMIF関数でスピルをする場合において、第三引数を計算式にすると、なぜかエラーになるということをお話ししましたが、気づきがありましたのでそれをお話しします。
スピルする場合に、とても重要な考え方が含まれています。
スピルで求められる答えの種類の違い
まず、次の計算式は、スピルを使って範囲をそのまま返すことができる計算式です。
D1からD3に入力されている内容が求められます。
=D1:D3
次に同様に範囲に1を足す計算式です。
D1からD3に入力されている数値に1足した答えが求められます。
=D1:D3+1
驚くことに、実はこの2つは答えの種類が違うのです。
前者は「範囲」です。それに対し後者は「配列」です。
範囲をそのまま指定すれば「範囲」、そうではなく計算されている場合は「配列」になるというのです。
この2種類の違いで、関数によっては受け付けてくれる場合とそうではない場合があるようなのです。
配列とは
Excelの関数を扱っていると、範囲を指定する引数はすべて「範囲」であると思っていました。しかしそうではなく「配列」という考え方が出てきました。
では、そもそも配列とはなんでしょうか。
実は従来から、Excelでは、関数内で範囲を指定する部分にセルではなく配列を指定できる場合がありました。セルではなく「配列」とはなんなのでしょうか。
例えば、INDEX関数で説明するとINDEX関数の第一関数はセル範囲を指定します。第二引数は何番目かを表す番号を指定するようになっています。
しあｋし、第一引数をセルではなく、下記のように値を直接指定できるのです。
=INDEX({10,20,30,40,50},3)
このように「{ }」を使って、その中に「,」の区切り文字で区切って複数の値を指定できるのです。この答えは、10、20、30、40、50のうち、3番目の値を求めるので30になります。
この「{ }」で囲んだものが「配列」です。
区切り文字に関しては「,」で横方向のセルに入っているのと同じ区切りで、「;」で縦方向の区切りです。
つまり、以下の表の範囲であれば「{1,10;2,20;3,30}」と表現できます。
合わせて、上記の通り、セル範囲に何らかの計算されているものも範囲ではなく配列になります。
範囲を計算したとたんに配列に変換されてしまうように感じます。
関数で使えるのは範囲？配列？
では、どの関数のどの引数で配列が使えないのでしょうか。
それは実際に関数に配列を指定してみるとわかるようです。
例えばSUMIF関数のどこで配列が使えるか、というのを調べるには、３つある引数に対し、一つずつ配列を入れて確認してみます。動作するかどうかの確認なので、セルに値を入れておく必要はありませんが、例えば第一引数と第三引数の数は最低限合わせておかなければなりません。
=SUMIF({1;2;3},A1:A3,B1:B3)
=SUMIF(A1:A3,{1;2;3},B1:B3)
=SUMIF(A1:A3,A1:A3,{1;2;3})
I以上の3種類を試してみて、動作するパターンを調べます。
この場合、実際に動作できるのは、2番目の第二引数だけという結果になります。
では今度は、範囲に対し計算を行う3パターンで見てみましょう。
=SUMIF(A1:A3+1,A1:A3,B1:B3)
=SUMIF(A1:A3,A1:A3+1,B1:B3)
=SUMIF(A1:A3,A1:A3,B1:B3+1)
この結果も第二引数だけ動作可能なので、先ほどの配列を入れた結果と同じになります。
このことからも、範囲に何らも計算を入れたものが配列になる、という考えが補強されます。
この実験での一致は、SUMIF関数のほかにもVLOOKUP関数の第二引数、ほか、たくさんの関数で確認したところ一致していました。
範囲を求める関数、配列を求める関数
ここで一つ疑問があります。
関数には範囲を求める関数がいくつかあります。いや、範囲を求めると思っている関数です。
それらは本当に範囲を求めているのでしょうか。もしかしたら配列を求めているということはないのでしょうか。
上記と同じ方法で、引数ひとつひとつで、OFFEST関数、INDIRECT関数、INDEX関数、FILTER関数、VSTACK関数について調べてみました。
結果、FILTER関数、VSTACK関数以外は全部計算式が求まりました。
ただ、INDEX関数において「INDEX({1;2;3},0)」のように配列を基にしたものを使うとVALUEエラーになりました。
すべてを試したわけではないのですが、ということは、スピル以前に搭載された、範囲を求める関数では範囲、それ以降の範囲に答えを求める関数が配列ということになっていそうですね。
では、どうすればいいのか
この結果から、我々はどうしていけばいいのでしょうか。
どの関数のどの引数が配列を入れられるかはわかりません。
まず、単価と数量を掛けた値を集計するなどの場合かどうかを判断します。
もし、その場合は、使う関数において、どの引数で配列が無効になるのかがわかるのであれば、そこに対し、別の作戦を立てて計算する必要があるということです。
もし、どの引数に配列を入れていけないのかわからない場合は、すべての引数に３つのセル範囲を指定し、次に引数に順番に「{1,2,3}」を入れて、どこの引数が配列を受け付けないのか、調べます。結果が出たら、できるだけその結果を覚えておくようにしましょう。
そして、もし配列を指定出来ない引数に範囲に計算を行ったものを指定しなければならない場合、それはできないので、別の作戦を考えるということになります。
まとめ
今回、Excelには「範囲」と「配列」という違いがあることがわかりました。それは関数を使うとき、特にスピルの考え方が入ってくると、影響を受けることがわかりました。
また、最近おかしいなと思っていた現象は、範囲に何らかの計算をしたスピル範囲は配列になるということが原因で、とても衝撃的でした。
例えばSUMIF関数でこの現象が起きますが、それは将来までずっとこのままなのか、範囲に何か計算したものはいったん配列として出力されても何かしらの関数で範囲に戻せるようになるのかもしれません。
ではなぜ範囲と配列があるのか、これは私見なのですが、スピルが入った計算式、もしくはスピル以降の関数では、VBAで処理するように、値を配列として処理されているのではないかと思っています。VBAで範囲のまま処理するとプログラムの分岐処理やループ処理ができないので、いったん配列にして演算し、その結果を最後にセルに流し込むというプロセスを取っています。それと同じことを内部でしている、という動作をしているようにも見えます。となるとスピルを奥深くまで知るにはVBAで配列を使ったプログラムを理解するところまで必要になるのかもしれません。
コメント