1. SUM 関数の繰り返しを ARRAYFORMULA 関数で置きかえることはできるのか？
ARRAYFORMULA 関数は、単一のセルに値を返す（配列を返さない）「組込みの関数の繰り返し」を、配列を返す形に置きかえることできる。これまで ARRAYFORMUAL 関数の使い方をいくつか試した。
四則演算などの「演算子」、単一のセルに適用する「組み込み関数」、引数が複数ある少し複雑な VLOOKUP 関数に対して ARRAYFORMULA 関数を適用した。
次に、「引数に範囲を与える」組込み関数に対して、ARRAYFORMULA が使えるのか考えたい。
引数に範囲を与えることができる関数はいくつかある。例えば、SUM 関数は、範囲を与えると合計を返す。
=sum(A1:C10)
この関数を ARRAYFORMULA に渡すことはできるのだろうか？
2. ARRAYFORMUAL の使い方を復習
ARRAYFORMULA の使い方を簡単に復習しておく。
これまでの例では、ARRAYFORMULA 関数を適用する「関数の引数」が「単一のセル」を対象にしていた。このような関数の繰り返しを ARRAYFORMULA で置きかえるには「単一のセル」を「範囲」に変更すればよかった。
例えば、あるセルに
A1 * 2
という式があり、下方向に式を繰り返しコピーしたとする。これを ARRAYFORMUAL で置きかえるには、
=ARRAYFORMULA(A:A * 2)
という書き方をした。元の計算は、A1 という単一のセルを対象にしている。ARRAYFORMUAL で置きかえるには、「単一のセル」ではなく、「範囲」を指定する。
組込み関数の場合も同じ。例えば、
POWER(A1, 2)
の式があり、下方向に繰り返しコピーされているものを、ARRAYFORMULA で置きかえるには、「単一のセル」を与えていた部分を「範囲」に変更した。
=ARRAYFORMULA( POWER(A:A, 2))
VLOOKUP 関数では、引数が多いのでやや複雑になった。
=vlookup(
weekday(A2),
曜日,
match("曜日名",曜日_属性, 0),
false)
第3引数のインデックスの配列を得るために工夫が必要。
=arrayformula(
vlookup(
weekday(
offset(A2,0,0,rows(A:A)-1,1)),
曜日,
match("曜日名",曜日_属性, 0)
* sign(row(
offset(A2,0,0,rows(A:A)-1,1))),
false))
まとめると、「計算の繰り返し」を ARRAYFORMUAL で置きかえるためには、元の式で「単一のセル」を対象にしていた部分を「範囲」に変更するということ。
3. SUM 関数の繰り返しを ARRAYFOMULA で置きかえることができなかった
では、これまでと同じように、SUM 関数の繰り返しを ARRAYFORMUALA で置きかえることができるか試してみる。
例えば、A ~ C 列の各行に対して、SUM 関数を用いて D 列に合計を表示したいとする。
D1 セルには以下の式を入力し、下方向へコピーを繰り返す。
=sum(A1:C1)
この「計算の繰り返し」を ARRAYFORMULA で置き換えるために、次のように記述してみた。
=arrayformula(sum(A:C))
しかし、残念ながら、結果は 1 つのセルに全てのセルの合計が表示されただけだった。
4. ARRAYFOMULA で置きかえるのではなく「行列の積」で考える
`google spreadsheets - How can I use ARRAYFORMULA and SUM together to get a column with the sum of the proceeding rows - Web Applications Stack Exchange’ には、SUM 関数の繰り返しを「行列の積」として計算する方法が述べられている。
上例の場合、次のように書くことができる。
=mmult(A:C, transpose( arrayformula( column(A:C)^0)))
ただし、これは行列の積を用いた方法であり、これまでのように SUM 関数の繰り返しを ARRAYFORMULA で置き換えるパターンとは違う。そのため、範囲を引数として受け取る MAX, AVERAGE のような関数に対して、同じような方法で対処できない。
a. ARRAYFOMULA を用いて、要素が全て1 の「行ベクトル」を求める
上記で ARRAYFORMULA を適用している部分は、行列の積において、要素が全て 1 の「行ベクトル」を生成していることに相当する。
行列の積の計算については、以下のサイトを参考にすると良い。
式の内容について、関数を１つずつ確認しておく。
arrayformula( column(A:C)^0)))
最初に COLUMUN 関数。COLUMN - Drive Help によると、
COLUMN(セル参照)指定したセルの列番号を返します（A は 1 となります）。
例えば、column 関数に範囲を与えると、
column(A:C))
結果は 1 とのみ表示される。配列が返されるわけではない。 column 関数に範囲を与え、配列を返して欲しい場合、arrayformula 関数を利用する。
=arrayformula(column(A:C))
これにより、対応する結果が表示される。
これは VLOOKUP 関数を ARRAYFORMULA 関数に渡したときの、第 3 引数と同様。ROW, COLUMN 関数に範囲を与え、配列で返してもらうには、ARRAYFORMULA 関数と組み合わせる。
0 乗しているのは、全ての値を 1 にするための工夫。
arrayformula(column(A:C)^0))
冪乗 – Wikipedia によると、
正負を伴う累乗では、整数 x に対して、指数が正の整数 n であれば xn となり、指数が 0 であれば 1 となり、指数が負の整数 −n であれば 1 / xn となる。
b. TRANSPOSE 関数で行ベクトルを列ベクトルに変換する
次に、行列の積を行うために、上記で生成した要素１の行ベクトルを列ベクトルに変換する。行と列の入れ替えを行う関数は TRANSPOSE 。
TRANSPOSE - Drive Help によると、
TRANSPOSE(配列または範囲)配列またはセルの範囲の行と列を入れ替えます。
transpose( arrayformula( column(A:C)^0)))
これにより、すべての要素が１の列ベクトができる。
最後に行列の積を計算する関数を適用する。
c. 行列の積を求める
行列の積を求める関数は MMULT 。
MMULT - Drive Help によると、
MMULT(行列1, 行列2)配列または範囲として指定した 2 つの行列の行列積を計算します。
d. SIGN 関数でも、全ての要素が 1 の配列を求めることができる
ところで、0 乗の計算を行なっている部分は、VLOOKUP 関数を ARRAYFORMULA に渡したときと目的は同じ。全ての要素を 1 にすること。よって、0 乗の代わりに、SIGN 関数を用いても良い。
=mmult(A:C, transpose( arrayformula( sign(column(A:C))))
5. SUM 関数の繰り返しを「カスタム関数」で置きかえる
SUM 関数の繰り返しを ARRAYFORMULA で置きかえるのを諦めた。カスタム関数で置き換えるには、以下の関数を定義する。
function sumAry(ary1, ary2, ary3){
var i, result = [];
for (i = 0; i < ary1.length; i++){
result[i] = [Number(ary1[i]) + Number(ary2[i]) + Number(ary3[i])];
}
return result;
}
D1 セルには、次のように記入すれば良い。
=sumAry(A:A,B:B,C:C)
6. 数式のコピーを繰り返す場合と、行列の積で計算を行うときの比較
2013年12月以前の Google スプレッドシートを用いて、SUM 関数の繰り返しと、行列の積を用いた方法でどちらが多く計算できるか比較してみる。
コピーを繰り返す方法では、9,000 行まで数式数制限が表示されなかった。
行列の積を用いた方法では、12,000 行までエラーが表示されなかった。上記のカスタム関数を使った場合も同じくらい。
コピーを繰り返すよりも、行列の計算の方がより多く計算ができるようだ。
新しい Google スプレッドシートでは相当計算ができる
2013年12月以降の新しいGoogle スプレッドシートでは、コピーを繰り返した場合でも、200,000 行計算しても数式制限は表示されなかった。
0 件のコメント:
コメントを投稿