Google スプレッドシートで「数式の繰り返し」を ARRAYFORMULA で置きかえる (2) - VLOOKUP 関数の場合

• Google スプレッドシートで「数式の繰り返し」を ARRAYFORMULA で置きかえる (1) のつづき

1. 引数に「単一のセル」を与える組み込み関数の繰り返しを ARRAYFORMULA で置きかえる

値を 2 乗する POWER 関数の繰り返し

前回、演算子を利用した「数式の繰り返し」を ARRAYFORMULA で置き換えた。今回は「組み込み関数の繰り返し」を置きかえてみる。

例えば、A 列に入力した値を B 列で 2 乗する計算について考える。

べき乗を計算する「組み込み関数」は、POWER 。

A1 セルの値を２乗するには、B1 セルに

=power(A1, 2)

と入力する。 A 列の値に対して、繰り返し計算を行うには、B1 セルの数式を下方向へコピーする。

 

ARRAYFORMULA で置きかえる

上記の「関数の繰り返し」と同じ結果になるように、ARRAYFORMULA を利用して数式を置きかえたい。

そのためには、B1 セルに次のように記述する。

=ARRAYFORMULA(
	POWER(A:A, 2))

演算子のときと同じように、POWER 関数を呼び出すときに与えた「値」を「範囲」に置き換えた。それを ARRAYFORMULA に与えると、A 列にある各々の値に対して POWER 関数を適用したことになる。

 

2. VLOOKUP 関数の繰り返しを ARRAYFORMULA で置きかえる

VLOOKUP 関数の繰り返し

次に、データを参照するための関数 VLOOKUP の繰り返しを ARRAYFORMULA で置きかえる。

ここでは「日付から曜日を返す」例を用いる。書き方は以下を参照。

• VLOOKUP 関数による値の参照
• 表計算で VLOOKUP 関数の第３引数をハードコードしないで MATCH 関数の結果を与える

A 列に「日付」を入力すると、B 列に「曜日」が返る。１行目には「見出し」行がある。

「日付」を入力した列の隣、B2 セルには以下のように入力する。

=vlookup(
	weekday(A2), 
	曜日,
	match("曜日名",曜日_属性, 0), 
	false)

繰り返し計算を行うには、B2 セルの数式を下方向へコピー。

 

ARRAYFORMULA で置きかえる

さて、「関数の繰り返し」と同じ結果になるように、 ARRAYFORMULA を利用して数式を置きかえたい。

数式の書き方は、以下を参考にした。

• How to use VLOOKUP inside arrayFormula? - Google プロダクト フォーラム 
• vlookup - How can I apply a formula to new, form-inserted rows of data in Google Spreadsheets? - Stack Overflow
• How to use VLOOKUP inside arrayFormula? - Google プロダクト フォーラム

B2 セルに、次のように記述する。

=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))

 

VLOOKUP の第 1 引数

ARRAYFORMULA で置きかえた式は、直感的に分かりにくいので、1引数ずつ確認する。

最初に vlookup 関数の第１引数。元の呼び出しは、

weekday(A2),

ARRAYFORMULA に与えるには、「値」を「範囲」に変更する。

weekday(A:A),

ただし、今回の例では「見出し」行があるので、１行目を計算対象から除かなければならない。そのためには、範囲を返す OFFSET 関数を利用する。

• cf. OFFSET 関数で計算対象の「範囲」を得る

よって、以下のように記述する。

weekday(offset(A2, 0, 0, rows(A:A) - 1, 1)),

offset 内の ROWS 関数は、指定された範囲の行数を返す。

rows(A:A) - 1

と書くことにより、「見出し」を除いた高さを求めることができる。

 

VLOOKUP の第 2 引数

第２引数の「曜日」は、VLOOKUP で値を探す範囲。

これは変わらないので、そのまま与える。

 

VLOOKUP の第 3 引数

第３引数の元の呼び出しは、範囲「曜日_属性」から “曜日名” のあるインデックスを得た。

match("曜日名", 曜日_属性, 0),

ここでは具体的に 2 が返ってくる。

やっかいなのはこの引数。 ARRAYFORMUAL に与えるには、以下のように複雑な式となる。

match("曜日名",曜日_属性, 0) * sign(row(offset(A2, 0, 0, rows(A:A) - 1, 1))),

• offset 関数が返すのは、上記の weekday 関数に与えた式と同じく、日付が記入された列の「見出し」を除いた範囲。
• ROW 関数は、セルの行番号を返す。

全体の意味が分かりづらいので、 順を追って計算を確認する。

まず、ROW 関数を ARRAYFORMULA に与えたときの結果を表示してみる。

=arrayformula(row(A:A))

行番号に対応した値が表示された。

次に、SIGN 関数を計算に含める。

=arrayformula(sign(row(A:A)))

この関数は、「正の数なら 1 , 負の数なら –1 , 0 なら 0 」を返す。よって、行番号を与えると、行番号は正の数なので、全て 1 が返り、全体では 1 の配列が得られる。

ここで、ARRAYFORMULA の中で「任意の値を掛ける」計算を含めてみる。例えば、2 を掛ける計算を含めると、2 の配列が返される。

=arrayformula(2 * sign(row(A:A)))

つまり、第 3 引数に与えた計算は、match 関数で得られる値の配列を求めたことになる。

match("曜日名",曜日_属性, 0) * sign(row(offset(A2, 0, 0, rows(A:A) - 1, 1))),

繰り返すが、このように配列が必要なのは、ARRAYFORMULA 関数に与える関数の引数は、「値」ではなく「範囲」であるため。

 

VLOOKUP の第 4 引数

上記の第3引数で行なった計算を考えると、第4引数も同じような計算が必要に思えてくる。

しかし、この引数は元のままで変更する必要はない。なぜだろう？

 

3.  「新しい Google スプレッドシート」（2013/12 以降）

ところで、上記の「日付から曜日を返す」例を、「新しい Google スプレッドシート」（2013/12 以降）で試したところ、第3引数で必要だった

* sign(row(offset(A2, 0, 0, rows(A:A) - 1, 1))),

を付けなくても同じ結果が得られた。

=arrayformula(        
        vlookup(                
                weekday(
                        offset(A2,0,0,rows(A:A)-1,1)),                 
                曜日,                
                match("曜日名",曜日_属性, 0),                
                false))

うーん、なんでだろう。。(+_+)

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