2014年2月24日月曜日

Google スプレッドシートで SUM 関数の繰り返しを「行列の積」で置きかえる

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 で置きかえることができなかった

SnapCrab_No-0793では、これまでと同じように、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 の「行ベクトル」を生成していることに相当する。

行列の積の計算については、以下のサイトを参考にすると良い。

式の内容について、関数を1つずつ確認しておく。

arrayformula(
	column(A:C)^0)))

最初に COLUMUN 関数。COLUMN - Drive Help によると、

COLUMN(セル参照)

指定したセルの列番号を返します(A は 1 となります)。

例えば、column 関数に範囲を与えると、

column(A:C))

結果は 1 とのみ表示される。配列が返されるわけではない。 column 関数に範囲を与え、配列を返して欲しい場合、arrayformula 関数を利用する。

=arrayformula(column(A:C))

これにより、対応する結果が表示される。

SnapCrab_No-0795

これは VLOOKUP 関数を ARRAYFORMULA 関数に渡したときの、第 3 引数と同様。ROW, COLUMN 関数に範囲を与え、配列で返してもらうには、ARRAYFORMULA 関数と組み合わせる。

0 乗しているのは、全ての値を 1 にするための工夫。

arrayformula(column(A:C)^0))

SnapCrab_No-0797

冪乗 – Wikipedia によると、

正負を伴う累乗では、整数 x に対して、指数が正の整数 n であれば xn となり、指数が 0 であれば 1 となり、指数が負の整数 −n であれば 1 / xn となる。

 

b. TRANSPOSE 関数で行ベクトルを列ベクトルに変換する

次に、行列の積を行うために、上記で生成した要素1の行ベクトルを列ベクトルに変換する。行と列の入れ替えを行う関数は TRANSPOSE 。

TRANSPOSE - Drive Help によると、

TRANSPOSE(配列または範囲)

配列またはセルの範囲の行と列を入れ替えます。

transpose(
		arrayformula(
			column(A:C)^0)))

これにより、すべての要素が1の列ベクトができる。

SnapCrab_No-0798

最後に行列の積を計算する関数を適用する。

 

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 行まで数式数制限が表示されなかった。

SnapCrab_No-0799

行列の積を用いた方法では、12,000 行までエラーが表示されなかった。上記のカスタム関数を使った場合も同じくらい。

SnapCrab_No-0800

コピーを繰り返すよりも、行列の計算の方がより多く計算ができるようだ。

 

新しい Google スプレッドシートでは相当計算ができる

2013年12月以降の新しいGoogle スプレッドシートでは、コピーを繰り返した場合でも、200,000 行計算しても数式制限は表示されなかった。

SnapCrab_No-0802